Question

You will do this by developing a cash proforma budget for hisbusiness venture (note: a cash proforma is a spreadsheet that helpsforecast income and expenses over a period of time). If donecorrectly, it can be used to plan for and manage the business.

He is planning to open his coffee house on the 1^st ofMarch 2016.

Assignment

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

Use the file called “Starlucks Coffee HouseTemplate” for your starting point. This is the filethat must be downloaded and used as the basis for your assignment.Do not make any changes to this pre-designed template other thaninstructed.

Place the cash proforma analysis on a worksheet labeled“Cash Flow".

Place all your case assumptions data on a separate worksheet andlabel the worksheet "Assumptions” (note:each piece of data must appear in its own cell on the Assumptionsheet).

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

Create an additional worksheet for your scenario onerecommendation. Label the worksheet“Recommendation”.

Charts (graphs)

You will be creating two appropriate charts so create and label twoadditional worksheets for the charts (each chart will be in its ownworksheet).

Chart One – “Monthly Product Revenue” – thischart should show the total monthly

revenue of each product for the entire year.

Chart Two: – “Total Product Net Income” – thischart should show the total net income, by month, for the entireyear. This will be used to determine any trends or projections inproduct sales.

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

Assumptions – provided by yourclient:

Product Selling Prices:

Frappuccino beverages will sell for $5.75 each
Espresso beverages will sell for $4.75 each
Hot chocolate beverages will sell for $3.25 each
Brewed coffee will sell for $2.10 each
Smoothies sell for $4.50 each
Pastries will sell for $3.25 each

Cost of Goods Sold (COGS):

Frappuccino ingredients cost $2.15 per unit

Espresso ingredients cost $2.05 per unit
Hot chocolate ingredients cost $1.23 per unit
Brewed coffee ingredients cost $.95 per unit

Smoothie ingredients cost $1.05 per unit

Pastries ingredients cost $1.35 per unit

The building rent is $2,500 per month.

Phone, Internet and Wi-Fi will cost about $600 per month.

Electricity should average about $800 a month.

Insurance will be $1200 a month.

Advertising and promotion will be $500 a month.

Assume that each month contains 4.2 weeks.

Operating Hours

The coffee house will be open six days a week (closed onMonday).

During the week (Tuesday – Friday), the coffee house will beopen from 7am – 11 pm. It will need three hourly employees and amanager during these hours.

On the weekends (Saturday and Sunday), the coffee house will beopen 10am – 10 pm and will need five hourly employees and amanger.

Your client will be the manager and draw a salary of $40,000 peryear. He will also work during the busiest times, and fill in forthe assistant manager on days off and sick days. The assistantmanager will receive an annual salary of $30K. The hourly workerswill be paid $7.25 an hour.

During the week, your client expects an average of 25 customersan hour. During the weekend, he expects an average of 45 customersan hour.

Demand Rate

Your client believes that on average 1/4 of customers will buy aFrappuccino, half will buy an espresso, a quarter of them will buya Hot chocolate, 25% will purchase brewed coffee, one out of 5 willbuy a smoothie, and 3 out of 4 customer will purchase a pastryitem.

Startup costs for the coffee house include:

Kitchen equipment: $26,275

Sales equipment: $3,500
Coffee house fixtures (chairs, tables etc.): $8,500

Start-up inventory: $1,875

Pre-opening marketing: $2,000

Licenses: $1,900

Security deposit: $5,000

First Insurance Payment: $1200

Your client has $5,000 and plans to borrow the rest from thebank with a five-year loan at 7.1% interest.

(Use the appropriate financial function to calculate the monthlytax payment)

Assume a tax rate of 15% if Income Before Taxes (IBT) is equalto or is greater than $9,000. Assume a tax rate of 10% if IBT isless than $9,000.

(Use the appropriate logical function to calculate the monthlytax payment)

Your client feels that starting with April, monthly revenue forall products, will grow an average of 1.25% per month.

Scenario One: “What if” Analysis

A former school friend of your client is an amateursinger/guitarist and has planted the idea in your client’s head ofhiring him to provide live entertainment on the weekends (Saturdayand Sunday evenings 6 - 11). The friend states he can guarantee 7more customers per hour if your client will hire him at $1,000 amonth.

What is your recommendation: Would it be a profitable idea tohire the musician?

Recommendation:

Show your client how your recommendation would affect his bottomline by recreating the proforma for this scenario, and applying thedata analysis to determine profitability.

(hint) you do not have to start this new proforma from scratch,but note, it is a completely independent proforma. The new proforma(worksheet) must update accordingly from the data worksheets.

Plan on showing your analysis and discussing the proforma changesthat occur under both scenarios and how it affectsprofitability.

Use a formatted text box (not a comment) to explain yourrecommendations. This will be approximately a 2-3 paragraphwrite-up. The recommendation should be complete, concise, andcorrect (use spell check).

Month 1

Month 2

Month 3

Month 4

Month 5

Month 6

Month 7

Month 8

Month 9

Month 10

Month 11

Month 12

Revenue:

Frappuccino

Espresso

Hot chocolate

Brewed coffee

Smoothies

Pastries

Monthly Revenue:

Expenses:

Cost of Goods Sold (COGS)

Rent

Phone

Electricity

Insurance

Advertising

Hourly Wages

Salaries

Loan Payment

Total Monthly Expenses:

Income Before Taxes (IBT)

Tax

Net Income

Cash Flow