Sparkles Dessert Cafe
Please read the instructions carefully and provide what
is being requested.
A new client of yours, Cassy Watts wants to open a small dessert cafe selling cupcakes,
pie slices, custard, ice cream cones, yogurt parfaits, cake slices, and sodas. She is
planning to open the cafe on the August 1, 2021.
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 specific time period). The plan will be used to manage
the business and apply for a loan.
Using Microsoft Excel, construct a monthly proforma cash budget for your client for
the first year of operations.
Use the file attached Excel Template - “Sparkles Café Exel” as 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. This means changing
the format. You will need to add your information and any other information needed
for the assignment.
Do not use a template from a previous semester – this is considered academic
dishonesty and will be subject to disciplinary action.
Place the finished cash pro forma on a worksheet labeled “Cash ProForma".
Place all your case assumptions data on a separate worksheet. Label the
worksheet "Assumptions" (note: each piece of data must appear in its own cell on
the Assumption sheet).
Place your start-up costs on a third worksheet labeled "Startup Costs"
Create two additional worksheets for your recommendations. Label one worksheet
“Flavored Coffee Recommendation”, label the other “Entertainment
You may also include a worksheet for the Optional Extra Credit criteria at the
end of this document.
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
Chart One – ”Monthly Product Revenue” – this will show the monthly revenue
for each of your five products for the entire year.
Chart Two – “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.
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:
Cupcakes, pie slices, whole pies, custard, ice cream cones, yogurt parfaits, cake slices,
whole cakes, and sodas.
Product Selling Prices:
Cupcakes for $2.25 each
Whole Pies for $10.25 each
Pie Slice for $2.95 each
Whole Cakes for $14.25 each
Cake Slice for $2.95 each
Custard for $3.95 per cup
Yogurt Granola Parfait for $4.95 each Ice Cream
Cones for $2.25 each one scoop (vanilla chocolate or strawberry)
Sodas $1.75 a bottle
Cost of Goods Sold:
Cupcake cost $1.05 per cupcake.
Pie Slice per slice $1.29.
Whole Pie cost $6.20
Whole Cake cost $7.19
Cake Slice cost $1.59
Custard cost $1.79
Yogurt Granola Parfait cost $2.69
Ice Cream Cones (Ice cream ingredients and cone) cost $1.10 (1 scoop)
Sodas cost about $.7 per 16 oz. bottle
The building rent is $2555 per month.
Phone will cost about $200 per month.
Electricity should cost about $700 a month.
Insurance will be $750 a month.
Advertising and promotion will be $700 a month.
The cafe will be open seven days a week.
Sparkles Dessert Cafe will serve desserts all day and will be open from 11am – 7pm on
weekdays (Monday – Friday). It will need one hourly employee and an assistant
manager or manager durint the hours that the café is open.
On Saturdays and Sundays the store will be open 11am – 11pm and will need two hourly
employees and an assistant manager (or manger).
Your client will be the manager and draw a salary of $38,500 per year (includes
benefits). She 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 $27,500 per year (includes benefits). The hourly workers will be paid $9 an
Monday through Fridays the owner expects an average of 10 customers an hour.
Saturdays and Sundays the owner expects an average of 22 customers an hour.
On average, 1/2 of all customers will buy Cupcakes, 1/4 of the customers will buy
Custard, 1/4 will buy Cake Slices, and 1/2 customers will buy Yogurt Parfaits, 3/4 of
them will buy a Pie Slice, 1/3 of all customers will buy Ice Cream cones, 1/3 of all
customers will buy a Whole Pie, 1/3 of all customers will buy a Whole Cake and every
customer will purchase a soda.
Start-up costs for the diner includes:
Kitchen equipment: $16,250
Cash register and sales equipment: $2,250
Initial inventory: $6,500
Pre-opening marketing: $2,500
Cafe fixtures (chairs, tables etc.): $4,500
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 4.1% interest. You are to calculate the monthly loan payment using the appropriate
Assume a tax rate of 24% if Income Before Taxes (IBT) is equal to or is greater than
$23,500. Assume a tax rate of 14% if IBT is less than $23,500. You are to calculate the
monthly tax payment using the appropriate logical function.
Assume that sales will grow at an average of 2% per month.
Assume that each month contains 4.2 weeks.
Scenario One: “What if” Analysis
Your client is unsure if she should sell flavored coffees at the cafe. She thinks she
can sell a coffee to every second customer and it seems to be lucrative because
the coffee sells for $4.75 each and costs him only $1.60 to purchase.
Unfortunately, your client is afraid that she would cannibalize his soft drink sales
with the coffee customers (one less soda less for every coffee sold). It will cost
him $5,250 to purchase the equipment and insurance costs would rise by another
$155 per month due to the hot equipment needed to make the coffee.
What is your recommendation: Should your client offer flavored coffee to her
Scenario Two: “What if” Analysis
A former school friend of your client is a balloon artist/face painter and has planted
the idea in your client’s head of adding a him to the café to provide face painting
and balloon animals (Saturday and Sunday 2 - 6). The friend states he can
guarantee 5 more customers per hour if your client will hire him at $500 a month.
What is your recommendation: Would it be a profitable idea to hire the balloon
artist/face painter? Recommendations:
Show your client how these recommendations would affect the bottom line by
recreating the pro forma for each scenario and applying the data analysis to
You do not have to start from scratch, but note, these are completely independent
cash proformas. They must update accordingly from the data worksheets.
Plan on showing your analysis and discussing the proforma 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 2-3 paragraph endeavor.
****Bonus Scenario Three: “What if” Analysis - Optional 10pts. Using Pivot
Tables analyze the data in the cash proforma such as which items sell best each day
of the week. What sells the most on the weekends? Based on your analysis of the
data recommendations on which items should be kept on the menu and is there
an item that just shouldn’t be offered?
Start-up costs for the diner includes:
Scenario One: “What if” Analysis
Criteria: Your current score:
Sheets are appropriately labeled 5 points
All information cells on cash proforma are referenced back to the assumption page, calculations are correct, all fields have values, twelve months are presented and an annual totals are given as well as a monthly totals. 35 points
Assumptions and startup worksheets setup and complete. 15 points
Recommendations are based on cash proforma information and what if analysis Use separate worksheet for each recommendation. 15 points
The chart is present and reflects worksheet content. 10 points
Taxes are calculated correctly using boolean logic. 10 points
Loan payment is calculated using a function.. 10 points
Optional Bonus scenario - Pivot Table 10 points
Score (out of 100) 0
Month 1 Month 2 Month 3 Month 4 Month 5 Month 6 Month 7 Month 8 Month 9 Month 10 Month 11 Month 12 Total
Yogurt Granola Parfait
Ice Cream Cones
Income Before Tax
Cupcakes selling price
Whole Pies selling price
Pie Slices selling price
Whole Cakes selling price
Cake Slices selling price
Custard selling price
Yogurt Granola selling price
Ice Cream Cones
Whole Pies COGS
Pie Slices COGS
Whole Cakes COGS
Cake Slices COGS
Yogurt Granola COGS
Ice Cream COGS
# of days open (weekdays)
# of days open (weekends)
Hours open (weekdays)
Hours open (weekends)
Customers per hour weekdays
Customers per hour weekends
Hourly employees/day (weekdays)
Hourly employees/day (weekends)
% of customers purchasing Cupcakes
% of customers purchasing Whole Pies
% of customers purchasing Pie Slices
% of customers purchasing Whole Cakes
% of customers purchasing Cake Slices
% of customers purchasing Custard
% of customers puchasing Yogurt Parfait
% of customers purchasing Ice Cream Cones
% of customers purchasing sodas
Manager Annual Salary
Assistant Manager Annual Salary
Weeks Per Month
Loan period (years)
Loan interest rate
If income before tax is equal or greater than: Tax rate =
If income before tax is less than: Tax rate =
Start Up Costs:
Sales equipment (cash register, etc.)
Cafe fixtures (chairs, tables etc.)
Initial insurance payment
Cash Reserves represent an amount in excess of what is needed to open your business. This reserve fund is borrowed and kept to protect your business against negative cash flow or losses. It replaces what would normally be considered an available line of credit.