Assignment

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

Use the Excel Template posted in D2L as your starting point. This is the file that must be downloaded and used as the basis for your assignment.

Before getting started, please save the template as YourLastName_Excel Assignment

DO NOT make any changes to this pre-designed template AND DO NOT use a template from a previous semester

this is considered academic dishonesty and will be subject to disciplinary action.

Labeling

1. Place the finished cash pro forma on a worksheet labeled "

Cash ProForma".

2. Place all your case assumptions data on a separate worksheet.

a. Label the worksheet "

Assumptions" (note: each piece of data must appear in its own cell on the Assumption sheet).

3. Place your start-up costs on a third worksheet labeled "

Startup Costs"

4. Create two additional worksheets for your recommendations.

a. Label one worksheet "

Beer Rec.", label the other "Entertainment Rec."

5. 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).

a. Chart One "

C1 Monthly Product Revenue" this will show the monthly revenue for each of your products for the entire year.

b. Chart Two "

C2 Total Product Net Income" - You want to track the total product net income for the year to determine any trends or projections in product sales.

Notes: The charts only need to depict information from the

original cash proforma.

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. Business Case: Excel Assignment (100 Points) Janes Bistro

Page

2 of 4

Assumptions

1. Product Selling Prices

a. Chicken fettuccini Alfredo pasta will sell for

$14.25 per order

b. Meatball sandwiches will sell for

$10.50 per order

c. Cannoli will sell for

$5.00 per order (2 cannoli per order)

d. Homemade potato chips sell for

$2.25 per bag

e. Italian coffee sells for

$6.75 per cup (free refills)

2. Cost of Goods Sold

a. Chicken fettuccini Alfredo pasta (chicken, pasta, Alfredo sauce) cost

$5.29 per order.

b. Meatball sandwich (bread, meatballs, sauce) cost

$4.15 per order

c. Cannoli cost per order

$3.90.

d. Potatos and oil average to

$.85 per order

e. Italian coffee w/ cream cost about

$.75 per 16 oz. cup

3. Fixed Costs

a. The building rent is

$2550 per month.

b. Phone will cost about

$250 per month.

c. Electricity should cost about

$725 a month.

d. Insurance will be

$825 a month.

e. Advertising and promotion will be

$2,520 a month.

4. Operating Hours:

a. The diner will be open six days a week (closed on Monday).

b. The diner will serve lunch and dinner and will be open from 11am 7pm on weekdays (Tuesday Friday). It will need

one hourly employee and an assistant manager (or manager) during these hours that the diner is open.

c. On Saturdays and Sundays the store will be open 11am 11pm and will need

two hourly employees and an assistant manager (or manger).

5. Salaries

a. Your client will be the manager and draw a salary of

$45,575 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.

b. The assistant manager will receive a salary of

$30,500 per year (includes benefits).

c. The hourly workers will be paid

$8.15 an hour.

6. Customers

a. Tuesday through Fridays the owner expects an average of

20 customers an hour.

b. Saturdays and Sundays the owner expects an average of

35 customers an hour.

7. Demand Rate:

a. 2 out of 4 customers will buy chicken fettuccini Alfredo

b. 50% of the customers will buy a meatball sandwich

c. 1/4 will buy cannoli

d. All of the customers will buy homemade potato chips

e. Every customer will purchase a coffee

Business Case: Excel Assignment (100 Points) Janes Bistro

Page

3 of 4

8. Sales Assumptions

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

b. Assume that each month contains 4.2 weeks.

Start-up Costs

1. Diner

a. Kitchen equipment:

$12,275

b. Cash register and sales equipment:

$1,500

c. Initial inventory:

$5,575

d. Pre-opening marketing:

$2,500

e. Diner Fixtures (chairs, tables etc.)

: $1,000

f. Oil painting of your clients favorite place in Italy to hang on the wall:

$550

g. Licenses:

$1,725

h. Security Deposit:

$3,500

i. First Insurance Payment:

$1,000

2. Financing

a. Your client has $18,000 and plans to borrow the rest from the bank with a five-year loan at 5.1% interest.

b. You are to calculate the monthly loan payment using the appropriate financial function. Jane is applying for a $45,000 loan from the bank.

c. 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.

Breakeven Point

1. The breakeven point needs to be calculated for the products in the original cash Pro Forma.

a. The calculated breakeven point should be listed on the assumptions page.

2. Create a graph to depict the breakeven point of the 5 products.

a. The graph should be displayed on the same worksheet as chart 1.

NOTE:

As with the other charts, the BEP graph should be formatted correctly (i.e. appropriate title, legend where appropriate, data series properly labeled) and appropriate for business use. See textbook for additional information on formatting charts and graphs. Business Case: Excel Assignment (100 Points) Janes Bistro

Page

4 of 4

"What If" Scenarios

1. Scenario One: "What if" Beer Analysis

Your client is unsure if she should sell beer at the diner. She thinks she can sell a beer to every other customer (except on Sundays when no beer can be sold) and it seems to be lucrative because the beer sells for $4.75 each and costs her only $2.10 to purchase. Unfortunately your client is afraid that she would cannibalize (reduce) her soft drink sales with the beer customers (one soft drink less for every beer sold). It will cost her $6,575 to purchase the beer license from the county and her monthly insurance costs would rise by another $200 per month.

What is your recommendation: Should your client offer beer to her customers?

2. Scenario Two: "What if" Entertainment Analysis

A local opera singer has planted the idea in your clients head of adding a solo singing act to provide live entertainment on the weekends (Saturday and Sunday). The singer states she can guarantee 10 more customers per hour if your client will hire them at $1,570 a month.

What is your recommendation: Would it be a profitable idea to hire the opera singer?

Recommendation Directions

Show your client how both of these recommendations would affect their bottom line by recreating the pro forma for each scenario on each individual recommendations worksheet, and applying the data analysis to determine profitability. You need to update the pro forma worksheets according to the new data included on the assumptions and startup costs worksheets.

You do not have to start from scratch, but note, these are completely independent pro formas. They must update accordingly from the data worksheets. This means you need to include updated information on the assumptions and startup costs worksheets. Please include these under separate columns on each worksheet, labeled Beer Rec. and Entertainment Rec.

Plan on showing your analysis and discussing the pro forma changes that occur under each new scenario and how it affects profitability.

Use a

formatted text box (not a comment) to explain your recommendations under each new pro forma. This will be approximately a 3 paragraph endeavor; each paragraph should be clearly defined and a minimum of 4 sentences long