Business Stats/Information Systems Excel Checking the work I've done!!
tv
is_pizzaria.xlsxCash Proforma
| Month 1 Ursula Meyo: Marquez: (Fall 2013) Replace Month 1, Month 2, etc. with the proper names of months - Start with the beginning of your fiscal year | 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 | 47,010.60 | 47,715.76 | 48,431.50 | 49,157.97 | 49,895.34 | 50,643.77 | 51,403.42 | 52,174.48 | 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,380.30 |
| Salads | 16,052.40 | 16,293.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,004.24 | 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 | 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 | 680,988.67 |
| Rent | $2,750 | $2,750 | $2,750 | $2,750 | $2,750 | $2,750 | $2,750 | $2,750 | $2,750 | $2,750 | $2,750 | $2,750 | 33,000.00 |
| Phone | $320 | 320.00 | 320.00 | 320.00 | 320.00 | 320.00 | 320.00 | 320.00 | 320.00 | 320.00 | 320.00 | 320.00 | 3,840.00 |
| Electricity | $675 | 675.00 | 675.00 | 675.00 | 675.00 | 675.00 | 675.00 | 675.00 | 675.00 | 675.00 | 675.00 | 675.00 | 8,100.00 |
| Insurance | $850 | 850.00 | 850.00 | 850.00 | 850.00 | 850.00 | 850.00 | 850.00 | 850.00 | 850.00 | 850.00 | 850.00 | 10,200.00 |
| Advertising | $900 | 900.00 | 900.00 | 900.00 | 900.00 | 900.00 | 900.00 | 900.00 | 900.00 | 900.00 | 900.00 | 900.00 | 10,800.00 |
| Hourly Wages | 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,644.80 | 67,737.60 |
| Salaries | 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 | 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 | 576.05 | 576.05 | 576.05 | 576.05 | 576.05 | 576.05 | 576.05 | 576.05 | 576.05 | 576.05 | 576.05 | 6,912.61 |
| Total Monthly Expenses: | 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 | 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,693.53 | 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: | |||
| Products: | |||
| Pizza selling price | 10.25 | ||
| Wings selling price | 4.95 | ||
| Salad selling price | 3.5 | ||
| Fries selling price | 1.15 | ||
| Sodas selling price | 1.25 | ||
| Pizza COGS | 7.15 | ||
| Wings COGS | 3.19 | ||
| Salad COGS | 1.23 | ||
| Fries COGS | 0.67 | ||
| Soda COGS | 0.73 | ||
| Overhead Costs: | |||
| Rent | 2750 | ||
| Phone | 320 | ||
| Electricity | 675 | ||
| Insurance | 850 | ||
| Advertising | 900 | ||
| Operating Information: | |||
| # of days open (weekdays) | 4 | ||
| # of days open (weekends) | 2 | ||
| Hours open (weekdays) | 8 | ||
| Hours open (weekends) | 12 | ||
| Hourly wage | 7 | ||
| Customers per hour weekdays | 17 | ||
| Customers per hour weekends | 38 | ||
| Employees per day (weekdays) | 3 | ||
| Employees per day (weekends) | 4 | ||
| % of customers purchasing pizzas | 75% | ||
| % of customers purchasing wings | 50% | ||
| % of customers purchasing salads | 75% | ||
| % of customers purchasing fries | 100% | ||
| % of customers purchasing sodas | 100% | ||
| Manager annual salary | 32500 | ||
| Assistant manager annual salary | 28500 | ||
| Monthly Growth Rate | 1.50% | ||
| Loan period (in years) | 5 | ||
| Interest rate | 6.10% | ||
| If IBT is greater than or equal to | $23,500 | Tax rate = | 23.00% |
| If IBT is less than | $23,500 | Tax rate = | 13.00% |
| Weeks per month | 4.2 | ||
| Template for Fall 2013 - 01 | |||
| 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 | 3,500 |
| Diner fixtures (chairs, tables etc.) | 4,500 |
| Oil painting | 350 |
| Licenses | 1,025 |
| Security deposit | 6,500 |
| First Insurance payment | 850 |
| 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. | 39,725 |
| Loan amount | 29,725 |
| Template for Fall 2013 - 01 | |
| Template for Fall 2011 |
Beer Recommendation
| Monthly | Yearly | |
| Beer positive cash flow: | ||
| Beer sales revenue | 9975 | 119700 |
| Total positive | 9975 | 119700 |
| Beer negative cash flow: | ||
| Beer COGS | 2310 | 27720 |
| Soft drink lost | 2184 | 26208 |
| Beer license | 437.5 | 5250 |
| Insurance cost rise | 275 | 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.0Product
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
