In: Economics
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 six 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 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 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.