A new client of yours, Ray Marquez, wants to open a small diner selling pizzas, chicken wings, salads, french fries,…

A new client of yours, Ray Marquez, wants to open a small diner selling pizzas, chicken wings, salads, french fries, and sodas.

He is planning to open the pizzeria on the 1^st of October, 2013 to take advantage of the last quarter of the year, the busiest pizza selling season.

You, as an independent consultant, have been hired to develop a cash pro forma budget for his business venture (note: this is a spreadsheet that helps forecast income and expenses over a period of time). It can be used to plan for and manage the business if done correctly.

Assignment:

Using Microsoft Excel, construct a monthly proforma cash budget for your client for the first year of operations.

Use the file called “Diner Template Fall 2013” for your starting point.  This is the file that must be downloaded and used as the basis for your assignment.  Do not make any changes to this pre-designed template other than instructed.

Place the cash pro forma analysis on a worksheet labeled “Cash Pro forma“.

Place all your case assumptions data on a separate worksheet and label the worksheet “Assumptions” (note: each piece of data must appear in its own cell on the Assumption sheet).

Place your startup costs on a third worksheet labeled “Startup Costs“

Create two additional worksheets for your recommendations.  Label one worksheet “Beer Recommendation”, label the other “Entertainment Recommendation”.

Appropriate charts (graphs): You will be creating two separate charts so create and label two additional worksheets for the charts (each chart will be in its own worksheet).

Chart One –”Monthly Product Revenue” – this will show the total monthly revenue

for each individual product for the entire year.

Chart Two– “Total Product Net Income” – This will show the monthly product net income for all the months to determine any trends or projections in product sales.  

Make sure both charts are formatted correctly (i.e. appropriate title, legend where appropriate, data series properly labeled) and they are appropriate for business use.

Information needed to complete assignment:

Product Selling Prices:

Pizzas will sell for $10.25 each

Wings will sell for $4.95 an order (8 wings to an order)
Salads will sell for $3.50 each

Fries sell for $1.15 per order

Sodas sell for $1.25 a bottle

Cost of Goods Sold:

The pizza ingredients (dough, cheese, sauce, etc.) cost $7.15 per pizza.

The frozen chicken wings cost $3.19 per order from the supplier.
Salad ingredients (lettuce, tomatoes, cheese, etc.) cost $1.23 per salad serving

Frozen fries and oil average to $.67 per order

Sodas cost about $.73 per 16 oz. bottle

The building rent is $2750 per month.

Phone will cost about $320 per month.

Electricity should cost about $675 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 (closed on Monday).

The diner will serve lunch and dinner and will be open from 11am – 7pm on weekdays (Tuesday – Friday).  It will need three 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 need four hourly employees and an assistant manager (or manger).

Your client will be the manager and draw a salary of $32,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 $28,500 per year (includes benefits).  The hourly workers will be paid $7.00 an hour.

Tuesday through Fridays the owner expects an average of 17 customers an hour.

Saturdays and Sundays the owner expects an average of 38 customers an hour.

Demand Rate:

On average, three fourths ofall customer will buy a pizza, 1/2 of the customers will buy chicken wings,  3/4 of them will buy a salad, all of them buyfrench fries, and everycustomer 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 6.1% interest.  You are to calculate the monthly loan payment using the appropriate financial function (Payment 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 (IF function).

Assume that sales will grow at an average of 1.50% per month.

Assume that each month contains 4.2 weeks.