USA: +1(669)289-8683 
Sign in
I have attached two excel documents and one of them has the amount that I should have (Assignment Check Sheet). However, I have negative (red values in my actual documents). I need you to look over every sheets of the ‘IS Pizzeria’ Excel document to see if my work is right. I’ve provided a Word Doc for the instructions.
Also, the highlighted part in Word document needs to be completed so I’ll pay for both of these tasks.
>Cash Proforma
) , etc. with the proper names of months – Start with the beginning of your fiscal year
Month 2 Revenue : 4.48
0.30
4
56,749.06 56,749.06 56,749.06 56,749.06 56,749.06 56,749.06 56,749.06 56,749.06 56,749.06 56,749.06 56,749.06 $2,750 $2,750 $2,750 $2,750 $2,750 $2,750 $2,750 $2,750 $2,750 $2,750 $2,750 320.00 320.00 320.00 320.00 320.00 320.00 320.00 320.00 320.00 320.00 675.00 675.00 675.00 675.00 675.00 675.00 675.00 675.00 675.00 675.00 850.00 850.00 850.00 850.00 850.00 850.00 850.00 850.00 850.00 850.00 900.00 900.00 900.00 900.00 900.00 900.00 900.00 900.00 900.00 900.00 5,644.80 5,644.80 5,644.80 5,644.80 5,644.80 5,644.80 5,644.80 5,644.80 5,644.80 5,644.80 5,644.80 5,083.33 5,083.33 5,083.33 5,083.33 5,083.33 5,083.33 5,083.33 5,083.33 5,083.33 5,083.33 5,083.33 576.05 576.05 576.05 576.05 576.05 576.05 576.05 576.05 576.05 576.05 576.05 73,548.24 73,548.24 73,548.24 73,548.24 73,548.24 73,548.24 73,548.24 73,548.24 73,548.24 73,548.24 73,548.24 3
Product s: 3.5 3.19 0
4 2 8 12 7 17 38 3 4 75% 100% 5 $23,500 Tax rate = 4.2 Template for Fall 2013 – 01 3,500 850 39,725 Template for Fall 2013 – 01 9975 119700 275
Month 1
Ursula Meyo: Marquez: (Fall 201
3
Replace Month 1, Month 2
Month 3
Month
4
Month
5
Month 6
Month
7
Month
8
Month 9
Month 10
Month 11
Month
12
Total
Pizza
47,010.60
47,715.76
48,431.50
49,157.97
49,895.34
50,643.77
51,403.42
52,
17
52,957.09
53,751.45
54,557.72
55,376.09
613,075.17
Chicken Wings
15,135.12
15,362.15
15,592.58
15,826.47
16,063.86
16,304.82
16,549.40
16,797.64
17,049.60
17,305.34
17,564.92
17,828.40
197,
38
Salads
16,052.40
16,29
3.19
16,537.58
16,785.65
17,037.43
17,292.99
17,552.39
17,815.67
18,082.91
18,354.15
18,629.47
18,908.91
209,342.74
Fries
7,032.48
7,137.97
7,245.04
7,353.71
7,464.02
7,575.98
7,689.62
7,804.96
7,922.04
8,040.87
8,161.48
8,283.90
91,712.06
Sodas
7,644.00
7,758.66
7,875.04
7,993.17
8,113.06
8,234.76
8,358.28
8,483.65
8,610.91
8,740.07
8,871.17
9,00
4.2
99,687.02
Monthly
Revenue:
92,874.60
94,267.72
95,681.73
97,116.96
98,573.72
100,052.32
101,553.11
103,076.40
104,622.55
106,191.89
107,784.76
109,401.54
1,211,197.30
Expenses:
Cost of Goods Sold (COGS)
56,749.06
680,988.67
Rent
$2,750
33,000.00
Phone
$
320
320.00
3,840.00
Electricity
$
675
675.00
8,100.00
Insurance
$
850
850.00
10,200.00
Advertising
$
900
900.00
10,800.00
Hourly Wages
5,644.80
67,737.60
Salaries
5,083.33
61,000.00
Loan Payment
IBM_USER: Marquez Fall 2013):
Remember to use the proper financial function to calculate Loan Payment.
576.05
6,912.61
Total Monthly Expenses:
73,548.24
882,578.88
Income Before Taxes (IBT)
19,326.36
20,719.48
22,133.49
23,568.72
25,025.48
26,504.08
28,004.87
29,528.16
31,074.31
32,643.65
34,236.52
35,853.30
328,618.41
Tax
IBM_USER: Marquez Fall 2013):
Remember to use the proper logical function to calculate Tax.
2,512.43
2,69
3.5
2,877.35
5,420.81
5,755.86
6,095.94
6,441.12
6,791.48
7,147.09
7,508.04
7,874.40
8,246.26
69,364.30
Net Income
16,813.93
18,025.95
19,256.14
18,147.91
19,269.62
20,408.14
21,563.75
22,736.68
23,927.22
25,135.61
26,362.12
27,607.04
259,254.11
Cash Flow
Elke Leeds: Marquez Fall 2013):
Cash Flow begins with the amount of cash you have available – this is usually your Cash Reserves. It is added to the Net Income at the end of Month 1 to begin your running total.(22,911.07)
(4,885.12)
14,371.02
32,518.94
51,788.55
72,196.69
93,760.44
116,497.12
140,424.34
165,559.95
191,922.07
219,529.11
Template for Fall 2013 – 01
Assumptions
Assumptions Made:
Pizza selling price
10.25
Wings selling price
4.95
Salad selling price
Fries selling price
1.15
Sodas selling price
1.25
Pizza COGS
7.15
Wings COGS
Salad COGS
1.23
Fries COGS
0.67
Soda COGS
0.73
Overhead Costs:
Rent
275
Phone 320
Electricity 675
Insurance 850
Advertising 900
Operating Information:
# of days open (weekdays)
# of days open (weekends)
Hours open (weekdays)
Hours open (weekends)
Hourly wage
Customers per hour weekdays
Customers per hour weekends
Employees per day (weekdays)
Employees per day (weekends)
% of customers purchasing pizzas
75%
% of customers purchasing wings
50%
% of customers purchasing salads
% of customers purchasing fries
100%
% of customers purchasing sodas
Manager annual salary
32500
Assistant manager annual salary
28500
Monthly Growth Rate
1.50%
Loan period (in years)
Interest rate
6.10%
If IBT is greater than or equal to
$2
3,500
Tax rate =
23.00%
If IBT is less than
13.00%
Weeks per month
Template for Fall 2011
Startup Costs
Start Up Costs:
Kitchen equipment
16,250
Cash register and sales equipment
1,250
Initial inventory
5,500
Pre-opening marketing programs
Diner fixtures (chairs, tables etc.)
4,500
Oil painting
350
Licenses
1,025
Security deposit
6,500
First Insurance payment
Total:
39,725
Owner’s equity
10,000
Cash reserves
Elke Leeds: Marquez Fall 2013):
Cash Reserves represent an amount in excess of what is needed to open your business. This reserve fund is borrowed and kept to protect your business against negative cash flow or losses.
Loan amount
29,725
Template for Fall 2011
Beer Recommendation
Monthly
Yearly
Beer positive cash flow:
Beer sales revenue
9975
119700
Total positive
Beer negative cash flow:
Beer COGS
2310
27720
Soft drink lost
2184
26208
Beer license
437.5
5250
Insurance cost rise
3300
Total negative
5206.5
62478
TOTAL
CASH FLOW
4768.5
57222
Answer: The client should offer beer for customers because positive cash flow of this issue is positive on yearly basis
Entertainment Recommendation
Monthly Yearly
Trio positive cash flow:
4168.332
50019.984
Trio compensation:
1850
22200
CASH FLOW
2318.332
27819.984
Answer: The idea to hire the trio would be profitable because this idea has positive cash flow on monthly basis
Monthly Product Revenue
Monthly Product Revenue
Pizza Chicken Wings Salads Fries Sodas Pizza Chicken Wings Salads Fries Sodas 47010.60000000001 15135.12 16052.4 7032.48 7644.0 Product
Revenue
Total Product Net Income
Total Product Net Income
Net Income Month 1 Month 2 Month 3 Month 4 Month 5 Month 6 Month 7 Month 8 Month 9 Month 10 Month 11 Month 12 16813.93315801221 18025.9466880122 192 56.14042096219 18147.91498405511 19269.61588137336 20408.14229215137 21563.74659909106 22736.68497063484 23927.21741775178 25135.60785157547 26362.12414190652 27607.03817659254
Sheet1
| Month 1 | Month 2 | Month 3 | Month 4 | Month 5 | Month 6 | Month 7 | Month 8 | Month 9 | Month 10 | Month 11 | Month 12 | Total | ||
| Revenue: | ||||||||||||||
| Pizza | 47010.60 | 47715.76 | 48431.50 | 49157.97 | 49895.34 | 50643.77 | 51403.42 | 52174.48 | 52957.09 | 53751.45 | 54557.72 | 55376.09 | 613075.17 | |
| Chicken Wings | 15135.12 | 15362.15 | 15592.58 | 15826.47 | 16063.86 | 16304.82 | 16549.40 | 16797.64 | 17049.60 | 17305.34 | 17564.92 | 17828.40 | 197380.30 | |
| Salads | 16052.40 | 16293.19 | 16537.58 | 16785.65 | 17037.43 | 17292.99 | 17552.39 | 17815.67 | 18082.91 | 18354.15 | 18629.47 | 18908.91 | 209342.74 | |
| Fries | 7032.48 | 7137.97 | 7245.04 | 7353.71 | 7464.02 | 7575.98 | 7689.62 | 7804.96 | 7922.04 | 8040.87 | 8161.48 | 8283.90 | 91712.06 | |
| Sodas | 7644.00 | 7758.66 | 7875.04 | 7993.17 | 8113.06 | 8234.76 | 8358.28 | 8483.65 | 8610.91 | 8740.07 | 8871.17 | 9004.24 | 99687.02 | |
| Monthly Revenue | 92874.60 | 94267.72 | 95681.73 | 97116.96 | 98573.72 | 100052.32 | 101553.11 | 103076.40 | 104622.55 | 106191.89 | 107784.76 | 109401.54 | 1211197.30 | |
| Expenses: | ||||||||||||||
| COGS | 56749.06 | 57600.29 | 58464.30 | 59341.26 | 60231.38 | 61134.85 | 62051.87 | 62982.65 | 63927.39 | 64886.30 | 65859.60 | 66847.49 | 740076.44 | |
| Rent | 2750.00 | 33000.00 | ||||||||||||
| Phone | 320.00 | 3840.00 | ||||||||||||
| Electricity | 675.00 | 8100.00 | ||||||||||||
| Insurance | 850.00 | 10200.00 | ||||||||||||
| Advertising | 900.00 | 10800.00 | ||||||||||||
| Hourly Wages | 5644.80 | 67737.60 | ||||||||||||
| Salaries | 5083.33 | 61000.00 | ||||||||||||
| Loan Payment | 968.97 | 11627.60 | ||||||||||||
| Total Expenses | 73941.16 | 74792.39 | 75656.40 | 76533.36 | 77423.48 | 78326.95 | 79243.97 | 80174.75 | 81119.49 | 82078.40 | 83051.70 | 84039.59 | 946381.64 | |
| Income Before | Tax | 18933.44 | 19475.33 | 20025.34 | 20583.60 | 21150.24 | 21725.37 | 22309.13 | 22901.65 | 23503.06 | 24113.48 | 24733.07 | 25361.95 | 264815.66 |
| 2461.35 | 2531.79 | 2603.29 | 2675.87 | 2749.53 | 2824.30 | 2900.19 | 2977.21 | 5405.70 | 5546.10 | 5688.61 | 5833.25 | 44197.19 | ||
| Net Income | 16472.10 | 16943.53 | 17422.04 | 17907.73 | 18400.70 | 18901.07 | 19408.95 | 19924.44 | 18097.35 | 18567.38 | 19044.46 | 19528.70 | 220618.47 | |
| Cash Flow | 36747.10 | 53690.63 | 71112.68 | 89020.41 | 107421.11 | 126322.18 | 145731.13 | 165655.57 | 183752.92 | 202320.30 | 221364.77 | 240893.47 |
Momma’sPizzeria
“pizza so good, makes you wanna slap yo momma”
Warning: Read the instructions carefully and provide what is being requested. To be a quality employee means you can follow directions.
A new client of yours, Ray Marquez, wants to open a small diner selling pizzas, chicken wings, salads, french fries, and sodas.
He is planning to open the pizzeria on the 1st of October, 2013 to take advantage of the last quarter of the year, the busiest pizza selling season.
You, as an independent consultant, have been hired to develop a cash pro forma budget for his business venture (note: this is a spreadsheet that helps forecast income and expenses over a period of time). It can be used to plan for and manage the business if done correctly.
Assignment:
Using Microsoft Excel, construct a monthly proforma cash budget for your client for the first year of operations.
Use the file called
“Diner Template Fall 2013”
for your starting point. This is the file that must be downloaded and used as the basis for your assignment. Do not make any changes to this pre-designed template other than instructed.
Place the cash pro forma analysis on a worksheet labeled “Cash Pro forma“.
Place all your case assumptions data on a separate worksheet and label the worksheet “Assumptions” (note: each piece of data must appear in its own cell on the Assumption sheet).
Place your startup costs on a third worksheet labeled “Startup Costs”
Create two additional worksheets for your recommendations. Label one worksheet “Beer Recommendation”, label the other “Entertainment Recommendation”.
Appropriate charts (graphs): You will be creating two separate charts so create and label two additional worksheets for the charts (each chart will be in its own worksheet).
Chart One –”Monthly Product Revenue” – this will show the total monthly revenue
for each individual product for the entire year.
Chart Two – “Total Product Net Income” – This will show the monthly product net income for all the months to determine any trends or projections in product sales.
Make sure both charts are formatted correctly (i.e. appropriate title, legend where appropriate, data series properly labeled) and they are appropriate for business use.
Information needed to complete assignment:
Product Selling Prices:
Pizzas will sell for $10.25 each
Wings will sell for $4.95 an order (8 wings to an order)
Salads will sell for $3.50 each
Fries sell for $1.15 per order
Sodas sell for $1.25 a bottle
Cost of Goods Sold:
The pizza ingredients (dough, cheese, sauce, etc.) cost $7.15 per pizza.
The frozen chicken wings cost $3.19 per order from the supplier.
Salad ingredients (lettuce, tomatoes, cheese, etc.) cost $1.23 per salad serving
Frozen fries and oil average to $.67 per order
Sodas cost about $.73 per 16 oz. bottle
The building rent is $2750 per month.
Phone will cost about $320 per month.
Electricity should cost about $675 a month.
Insurance will be $850 a month.
Advertising and promotion will be $900 a month.
Operating Hours:
The diner will be open six days a week (closed on Monday).
The diner will serve lunch and dinner and will be open from 11am – 7pm on weekdays (Tuesday – Friday). It will need three hourly employees and an assistant manager (or manager) during these hours that the diner is open.
On Saturdays and Sundays the store will be open 11am – 11pm and will need four hourly employees and an assistant manager (or manger).
Your client will be the manager and draw a salary of $32,500 per year (includes benefits). He will also work in the store during the busiest times, and fill in for the assistant manager on days off and sick days. The assistant manager will receive a salary of $28,500 per year (includes benefits). The hourly workers will be paid $7.00 an hour.
Tuesday through Fridays the owner expects an average of 17 customers an hour.
Saturdays and Sundays the owner expects an average of 38 customers an hour.
Demand Rate:
On average, three fourths of all customer will buy a pizza, 1/2 of the customers will buy chicken wings, 3/4 of them will buy a salad, all of them buy french fries, and every customer will purchase a soda.
Start up costs for the diner includes:
Kitchen equipment: $16,250
Cash register and sales equipment: $1,250
Initial inventory: $5,500
Pre-opening marketing: $3,500
Diner fixtures (chairs, tables etc.): $4,500
Oil painting of your client’s momma to hang on the wall: $350
Licenses: $1,025
Security deposit: $6,500
First Insurance Payment: $850
Your client has $10,000 and plans to borrow the rest from the bank with a five-year loan at 6.1% interest. You are to calculate the monthly loan payment using the appropriate financial function (Payment function).
Assume a tax rate of 23% if Income Before Taxes (IBT) is equal to or is greater than $23,500. Assume a tax rate of 13% if IBT is less than $23,500. You are to calculate the monthly tax payment using the appropriate logical function (IF function).
Assume that sales will grow at an average of 1.50% per month.
Assume that each month contains 4.2 weeks.
Scenario One: “What if” Analysis
Your client has the opportunity to sell beer at the diner; however, he is unsure if he should. He thinks he can sell a beer to every second customer (except on Sundays when no beer can be sold) and it seems to be lucrative because the beer sells for $4.75 each and costs him only $1.10 to purchase. Unfortunately your client is afraid that he would cannibalize his soft drink sales with the beer customers (one soft drink less for every beer sold). It will cost him $5,250 to purchase the beer license from the county and his monthly insurance costs would rise by another $275 per month.
What is your recommendation: Should your client offer beer to his customers?
Scenario Two: “What if” Analysis
A former school friend of your client has a small musical trio and has planted the idea in your client’s head of adding a small musical trio to provide live music on the weekends (Saturday and Sunday). The friend states he can guarantee 7 more customers per hour if your client will hire the trio at $1,850 a month.
What is your recommendation: Would it be a profitable idea to hire the trio?
Recommendations:
Show your client how these recommendations would affect the bottom line by recreating the pro forma for each scenario, and applying the data analysis to determine profitability.
You do not have to start from scratch, but note, these are completely independent pro formas. They must update accordingly from the data worksheets.
Plan on showing your analysis and discussing the proforma changes that occur under each new scenario and how it affects profitability.
Use a formatted text box (not a comment) to explain your recommendations under each new pro forma. This will be approximately a 2-3 paragraph endeavor.
Momma’s Pizzeria
“pizza so good, makes you wanna slap yo momma”
Some interesting developments have occurred since our last contact.
1. After the tax commission’s last meeting, the business tax tables, along with the tax rates, have
changed.
Effective immediately, the following changes have occurred and need to be implemented in your cash proforma analysis:
· the tax rate has increased from 23% to 28% in those situations where income before taxes is greater than or equal to $25,000.
· the tax rate has increased from 13% to 18% in those situations where income before taxes is less than $25,000.
2. Additionally, one of my newly hired employees has “squealed” to the Department of Labor regarding my proposed hourly wage. I have been notified by the Department of Labor that the hourly minimum wage is $7.50 per hour. To pay an employee less constitutes a labor violation with a possible fine of $1,000 per occurrence.
Therefore, please make the appropriate change to the hourly wage rate…..(when I find out who squealed, they’re gone).
3. I haven’t even opened the diner yet and already the price of cheese has increased thereby affecting my cost of goods for both pizza and salads.
Cost of goods for this item has increased by $.07. My new cost of goods for pizza is now $7.22 and $1.30 for salads.
In order to compensate for these changes, I’ll have to increase my proposed selling price for pizza to $10.50 and salads to $3.75.
4. Lastly, I greatly underestimated the number of workers I will need for both the workweek (Tue – Fri) and the weekends (Sat – Sun). I find that I will need a total of 4 x hourly employees for the workweek and 5 x hourly employees for the weekend.
Please incorporate all these changes into your analysis prior to submitting the cash proforma analysis.
Sheet1
| Month 1 | Month 2 | Month 3 | Month 4 | Month 5 | Month 6 | Month 7 | Month 8 | Month 9 | Month 10 | Month 11 | Month 12 | Total | |||||||||||||
| Revenue: | |||||||||||||||||||||||||
| Pizza | 48157 | 48880 | 49613 | 50357 | 51112 | 51879 | 52657 | 53447 | 54249 | 55062 | 55888 | 56727 | 628028 | ||||||||||||
| Chicken Wings | 15135 | 15362 | 15593 | 15826 | 16064 | 16305 | 16549 | 16798 | 17050 | 17305 | 17565 | 17828 | 197380 | ||||||||||||
| Salads | 17199 | 17457 | 17719 | 17985 | 18254 | 18528 | 18806 | 19088 | 19375 | 19665 | 19960 | 20260 | 224296 | ||||||||||||
| Fries | 7032 | 7138 | 7245 | 7354 | 7464 | 7576 | 7690 | 7805 | 7922 | 8041 | 8161 | 8284 | 91712 | ||||||||||||
| Sodas | 7644 | 7759 | 7875 | 7993 | 8113 | 8235 | 8358 | 8484 | 8611 | 8740 | 8871 | 900 | 99687 | ||||||||||||
| Monthly Revenue | 95168 | 96595 | 98044 | 99515 | 101008 | 102523 | 104061 | 105621 | 107206 | 108814 | 110446 | 112103 | 1241103 | ||||||||||||
| Expenses: | |||||||||||||||||||||||||
| COGS | 57391 | 58252 | 59126 | 60013 | 60913 | 61827 | 62754 | 63695 | 64651 | 65620 | 66605 | 67604 | 748450 | ||||||||||||
| Rent | 2750 | 33000 | |||||||||||||||||||||||
| Phone | 320 | 3840 | |||||||||||||||||||||||
| Electricity | 675 | 8100 | |||||||||||||||||||||||
| Insurance | 850 | 10200 | |||||||||||||||||||||||
| Advertising | 10800 | ||||||||||||||||||||||||
| Hourly Wages | 7812 | 93744 | |||||||||||||||||||||||
| Salaries | 5083 | 61000 | |||||||||||||||||||||||
| Loan Payment | 969 | 11628 | |||||||||||||||||||||||
| Total Expenses | 76750 | 77611 | 78485 | 79372 | 80272 | 81186 | 82113 | 83055 | 84010 | 84980 | 85964 | 86963 | 980762 | ||||||||||||
| Income Before | Tax | 18417 | 18984 | 19559 | 20143 | 20735 | 21337 | 21947 | 22567 | 23196 | 23834 | 24482 | 25140 | 260342 | |||||||||||
| 3315 | 3417 | 3521 | 3626 | 3732 | 3841 | 3951 | 4062 | 4175 | 4290 | 4407 | 7039 | 49375 | |||||||||||||
| Net Income | 15102 | 15567 | 16039 | 16517 | 17003 | 17496 | 17997 | 18505 | 19021 | 19544 | 20075 | 18101 | 210966 | ||||||||||||
| Cash Flow | 35377 | 50944 | 66983 | 83500 | 100503 | 117999 | 135996 | 154501 | 173521 | 193065 | 213141 | 231241 |
