Question
I know what I've put for most of them is wrong, but I can't figure it out. Please help Using Microsoft Excel, construct a monthly
I know what I've put for most of them is wrong, but I can't figure it out. Please help
Using Microsoft Excel, construct a monthly proforma cash budget for your client for the first year of operations. Product Selling Prices: Oysters on half shell will sell for $8.25 each (dozen) Fried Shrimp will sell for $10.25 (dozen) Calamari will sell for $4.95 an order Catfish Sandwich will sell for $5.95 Salads will sell for $4.50 each Fries sell for $1.25 per order Sodas sell for $1.75 a bottle Cost of Goods Sold: The Catfish sandwich ingredients (fried catfish, cheese, sauce, bun etc.) cost $3.15 per sandwich. Calamari cost per order $1.90. The fresh Shrimp cost $7.19 per order from the supplier. Oysters (sauce, lemon, oysters) cost $5.19 per order. Salad ingredients (lettuce, tomatoes, cheese, etc.) cost $1.23 per salad serving Frozen fries and oil average to $.67 per order Sodas cost about $.7 per 16 oz. bottle The building rent is $2775 per month. Phone will cost about $220 per month. Electricity should cost about $775 a month. Insurance will be $850 a month. Advertising and promotion will be $900 a month. Operating Hours: The diner will be open seven days a week. The diner will serve lunch and dinner and will be open from 11am 9pm on weekdays (Monday Friday). It will need two hourly employee and an assistant manager (or manager) during these hours that the diner is open. On Saturdays the restaurant will be open 11am 11pm and will need three hourly employees and an assistant manager (or manger). On Sundays the restaurant is only open dinner 4pm - 8pm. One hourly employee and an assistant manager will be needed during these hours. Your client will be the manager and draw a salary of $48,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 2 salary of $37,500 per year (includes benefits). The hourly workers will be paid $8.25 an hour. Monday through Fridays the owner expects an average of 15 customers an hour. Saturdays and Sundays the owner expects an average of 40 customers an hour. The restaurant is located near the beach so there is alot of foot traffic most days with Sunday being the slowest. Demand Rate: On average, 1/4 of all customer will buy shrimp, 1/4 of the customers will buy oysters, 1/4 will buy Calamari, and 1/2 customers will buy Catfish, 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 clients 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 5.1% interest. You are to calculate the monthly loan payment using the appropriate financial 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. Assume that sales will grow at an average of 2.25% per month. Assume that each month contains 4.2 weeks.
Qisdfnjds AutoSave OFF Excel Assignment DinerTemplatesum19 Saved to my Mac Page Layout Insert Draw Formulas Data Review View Share Home Comments SEInsert General ab Wrap Text Arial A A 12 Deletev Paste A v Merge& Center Conditional ormat Cell Sort & Filter Find & Select Ideas B IU Format Formatting as Table Styles X 015 P A C E G N C R Seafood Diner Total Month 11 Month 1 Month 2 Month 3 Month 4 Month 5 Month 6 Month 7 Month 8 Month 9 Month 10 Month 12 Revenue: Oysters 2894,38 2960.00 3030.00 3090.00 3160.00 3230.00 3300.00 3370.00 3450.00 3530.00 3620.00 3700.00 mp Calamari 10419.75 10642.50 10890.00 11137.50 11385.00 11632.50 11880.00 12127.50 12424.50 12721.50 13018.50 13315.50 Catfish Sandwich 10 Salads 25049.50 25585.00 26180.00 26775.00 27370.00 27965.00 28619.50 29274.00 29928.50 30583.00 31297.00 32011.00 28417.50 29070.00 29745.00 30420.00 31095.00 31815.00 32535.00 33255.00 34020.00 34785.00 35550.00 36360.00 Fes 4735 00 18007 50 15067 50 15400 00 17605 00 18410 00 15750.00 16100.00 16467 50 16835.00 17220 00 18830 00 13 Monthly Revenue 171250.89 103839.15 95923.96 108037.35 119513.99 127232.44 134335.48 141004.14 147344.06 153533.35 159517.22 165401,89 15 Expenses 17 Rent 18 Phone 19 Electricity 2775.00 2775.00 2775.00 2775.00 2775.00 2775.00 2775.00 2775.00 2775.00 2775.00 2775.00 2775.00 220.00 220.00 220.00 220.00 220.00 220.00 220.00 220.00 220.00 220.00 220.00 220.00 775.00 775.00 775.00 775.00 775.00 775.00 775.00 775.00 775.00 775.00 775.00 775.00 21 Advertising 22 Hourly Wages 23 Salaries 900.00 900.00 900.00 900.00 900.00 900.00 900.00 900.00 900.00 900.00 900.00 900 00 4628.40 4628.40 4628.40 4628.40 4628.40 4628.40 4628.40 4628.40 4628.40 4628.40 4628.40 4628.40 86000.00 86000.00 86000.00 86000.00 86000.00 86000.00 86000.00 86000.00 86000.00 86000.00 86000.00 86000.00 Total Expenses 96710 71 96710.71 96710 71 96710.71 96710.71 96710 71 96710.71 96710.71 96710 71 96710.71 96710.71 96710.71 27 Income Before Tax Tax Cash Flow 32 Sheet Sheet 3 Beer Recommendations Grading criteria Sheet 1 Entertainment Recommendations - E +100% Qisdfnjds AutoSave OFF Excel Assignment DinerTemplatesum19 Saved to my Mac Page Layout Insert Draw Formulas Data Review View Share Home Comments SEInsert General ab Wrap Text Arial A A 12 Deletev Paste A v Merge& Center Conditional ormat Cell Sort & Filter Find & Select Ideas B IU Format Formatting as Table Styles X 015 P A C E G N C R Seafood Diner Total Month 11 Month 1 Month 2 Month 3 Month 4 Month 5 Month 6 Month 7 Month 8 Month 9 Month 10 Month 12 Revenue: Oysters 2894,38 2960.00 3030.00 3090.00 3160.00 3230.00 3300.00 3370.00 3450.00 3530.00 3620.00 3700.00 mp Calamari 10419.75 10642.50 10890.00 11137.50 11385.00 11632.50 11880.00 12127.50 12424.50 12721.50 13018.50 13315.50 Catfish Sandwich 10 Salads 25049.50 25585.00 26180.00 26775.00 27370.00 27965.00 28619.50 29274.00 29928.50 30583.00 31297.00 32011.00 28417.50 29070.00 29745.00 30420.00 31095.00 31815.00 32535.00 33255.00 34020.00 34785.00 35550.00 36360.00 Fes 4735 00 18007 50 15067 50 15400 00 17605 00 18410 00 15750.00 16100.00 16467 50 16835.00 17220 00 18830 00 13 Monthly Revenue 171250.89 103839.15 95923.96 108037.35 119513.99 127232.44 134335.48 141004.14 147344.06 153533.35 159517.22 165401,89 15 Expenses 17 Rent 18 Phone 19 Electricity 2775.00 2775.00 2775.00 2775.00 2775.00 2775.00 2775.00 2775.00 2775.00 2775.00 2775.00 2775.00 220.00 220.00 220.00 220.00 220.00 220.00 220.00 220.00 220.00 220.00 220.00 220.00 775.00 775.00 775.00 775.00 775.00 775.00 775.00 775.00 775.00 775.00 775.00 775.00 21 Advertising 22 Hourly Wages 23 Salaries 900.00 900.00 900.00 900.00 900.00 900.00 900.00 900.00 900.00 900.00 900.00 900 00 4628.40 4628.40 4628.40 4628.40 4628.40 4628.40 4628.40 4628.40 4628.40 4628.40 4628.40 4628.40 86000.00 86000.00 86000.00 86000.00 86000.00 86000.00 86000.00 86000.00 86000.00 86000.00 86000.00 86000.00 Total Expenses 96710 71 96710.71 96710 71 96710.71 96710.71 96710 71 96710.71 96710.71 96710 71 96710.71 96710.71 96710.71 27 Income Before Tax Tax Cash Flow 32 Sheet Sheet 3 Beer Recommendations Grading criteria Sheet 1 Entertainment Recommendations - E +100%Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started