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 1st 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).
