Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

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

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Top Accounting And Auditing Issues For 2021 CPE Course

Authors: CCH Tax Law Editors

1st Edition

0808055348, 978-0808055341

More Books

Students also viewed these Accounting questions