Question
Sparkles Dessert Cafe Please read the instructions carefully and provide what is being requested. A new client of yours, Bailey Waters wants to open a
Sparkles Dessert Cafe
Please read the instructions carefully and provide what is being requested.
A new client of yours, Bailey Waters 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, 2020.
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 period of time). The plan will be used to manage the business and apply for a loan.
Assignment:
Using Microsoft Excel, construct a monthly proforma cash budget for your client for the first year of operations.
1. Use the Excel Template file attached Sparkles Dessert Cafe Template as your starting point. Download and use as the basis for your assignment.
2. Do not make any changes to this pre-defined template items.
3. You may add your information to the existing sheets.
4. You may add extra worksheets as needed to the template file.
5. Place the finished cash pro forma on a worksheet labeled Cash ProForma".
6. 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).
7. Place your start-up costs on a third worksheet labeled "Startup Costs".
8. Create two additional worksheets for your recommendations. a. Label one worksheet Flavored Coffee Recommendation
b. Label the other Entertainment Recommendation.
9. 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 Monthly Product Revenue this will show the monthly revenue for each of your five products for the entire year.
b. 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.
c. 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.
Operating Hours:
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 hour.
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.
Demand Rate:
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 cafe 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
Decorations: $500
Licenses: $1,025
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 financial function.
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 he 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 customers?
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 clients 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 determine profitability.
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.
Please show the formula in Excel so I can learn how to do this. Thank you!!!
Excel File Edit Tools Data Window Help A) 100% Mon 11:12 PM E View Insert Format BE SU: .. AutoSave OFF Excel Assignment SparklesDessertCaleTemplatesum20201 a Home Insert Draw Page Layout Formulas Data Review View Tell me Share Comments Ruler Formula Bar Zoom 110% rs Split F Hide Switch Unhide Windows Normal Page Break Page Custom Preview Layout Views Gridlines Headings 0 Zoom to 100% Zoom to Selection New Arrange Freeze Freeze Freeze First Window All Panes Top Row Column Vic Record Usc Relative Macros Macro References C17 C 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 Revenue: Cupcakes Whole Cakes Cake Slices Whole Pies Pie Slicos Custard Yogurt Granola Parfait Ice Cream Cones Soda Monthly Revenue 17 Expenses: COGS Rent Phone Electricity Insurance Advertising Hourly Wages Salaries Loan Payment Total Expenses Income Before Tax Tax Net Income Cash Flow Grading criteria Sheet 1 Sheet 2 Sheet 3 + El 110% w JUN 29Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started