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

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

Assignment

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

Use the file called Starlucks Coffee House Template 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 proforma analysis on a worksheet labeled Cash Flow".

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 an additional worksheet for your scenario one recommendation. Label the worksheet Recommendation.

Charts (graphs) You will be creating two appropriate 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 chart should show the total monthly

revenue of each product for the entire year.

Chart Two: Total Product Net Income this chart should show the total net income, by month, for the entire year. This will be used 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, etc.) and they are appropriate for business use.

Assumptions provided by your client:

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 on Monday).

During the week (Tuesday Friday), the coffee house will be open from 7am 11 pm. It will need three hourly employees and a manager during these hours.

On the weekends (Saturday and Sunday), the coffee house will be open 10am 10 pm and will need five hourly employees and a manger. Your client will be the manager and draw a salary of $40,000 per year. He will also work during the busiest times, and fill in for the assistant manager on days off and sick days. The assistant manager will receive an annual salary of $30K. The hourly workers will be paid $7.25 an hour.

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

Demand Rate

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

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 the bank with a five-year loan at 7.1% interest.

(Use the appropriate financial function to calculate the monthly tax payment)

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

(Use the appropriate logical function to calculate the monthly tax payment)

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

Scenario One: What if Analysis A former school friend of your client is an amateur singer/guitarist and has planted the idea in your clients head of hiring him to provide live entertainment on the weekends (Saturday and Sunday evenings 6 - 11). The friend states he can guarantee 7 more customers per hour if your client will hire him at $1,000 a month.

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

Recommendation:

Show your client how your recommendation would affect his bottom line by recreating the proforma for this scenario, and applying the data 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 changes that occur under both scenarios and how it affects profitability. Use a formatted text box (not a comment) to explain your recommendations. This will be approximately a 2-3 paragraph write-up. The recommendation should be complete, concise, and correct (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