Question
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
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.25 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.
Monday through Fridays the owner expects an average of 17 customers an hour. Saturdays and Sundays the owner expects an average of 45 customers an hour.
Operating Hours:
The diner will be open seven days a week.
The diner will serve lunch and dinner and will be open from 11am 7pm on weekdays (Monday Friday). It will need two 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 needthree hourly employees and an assistant manager (or manger).
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
salary of $37,500 per year (includes benefits). The hourly workers will be paid $7.25 an hour.
Monday through Fridays the owner expects an average of 17 customers an hour. Saturdays and Sundays the owner expects an average of 45 customers an hour.
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.
Basically my question is, how do I calculate each month's revenue for each item per month using excel formulas?
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