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

Use the file attached Excel Template - SugarFine Dessert Cafe Template 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-defined template items. You may add extra worksheets as needed as well update the template.

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 Recommendation.

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

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 $12.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 Sodas sell for $1.25 a bottle

Cost of Goods Sold: Cupcake cost $1.05 per cupcake. Pie Slice per slice $1.29. Whole Pie cost $5.20 Whole Cake cost $6.19 per order Cake Slice cost $1.59 Custard cost $1.79 Yogurt Granola Parfait cost $2.69 per order. Ice Cream Cones (Ice cream ingredients and cone) cost $1.10 per serving 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 diner will be open seven days a week.

The 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) during the hours that the cafe is open.

2

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). He 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 $7.25 an hour.

Monday through Fridays the owner expects an average of 10 customers an hour. Saturdays and Sundays the owner expects an average of 24 customers an hour.

Demand Rate:

On average, 1/4 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 Slices, 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: $1,250 Initial inventory: $5,500 Pre-opening marketing: $3,500 Cafe fixtures (chairs, tables etc.): $4,500 Oil paintings of your clients momma and grandma to hang on the wall: $350 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 5.1% interest. You are to calculate the monthly loan payment using the appropriate financial function.

Assume a tax rate of 23% if Income Before Taxes (IBT) is equal to or is greater than $23,500. Assume a tax rate of 13% 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.25% per month. Assume that each month contains 4.2 weeks.

3

Scenario One: What if Analysis

Your client is unsure if he should sell flavored coffees at the diner. 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 he would cannibalize his soft drink sales with the coffee customers (one soft drink 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 an amateur Comedian and has planted the idea in your clients head of adding a Comedy act 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 comedian? 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 pro formas. 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.

All information cells are referenced back to the assumption page.,

30 points

Loan payment is correctly calculated using a function..

10 points

Recommendations are based on spreadsheet what if analysis.

10 points

The dollar break-even point is indicated/illustrated on Budget

10 points

Sheets are appropriately labeled

10 points

The two charts are present and reflect worksheet content.

10 points

Taxes are calculated correctly using boolean logic.

10 points

Twelve months are presented and a correct annual total is given.

10 points

Sheet 1

Assumptions
Product Selling Prices:
Cupcake	$2.25
Whole Pie	$10.25
Pie Slice	$2.95
Whole Cake	$12.25
Cake Slice	$2.95
Custard	$3.95
Yogurt Granola Parfait	$4.95
Ice Cream Cone	$2.25
Sodas sell for	$1.25
Cost of Goods Sold:
Cupcake cost	$1.05
Pie Slice per slice	$1.29
Whole Pie	$5.20
Whole Cake	$6.19
Cake Slice cost	$1.59
Custard cost	$1.79
Yogurt Granola Parfait	$2.69
Ice Cream Cone (Ice cream ingredients and cone)	$1.10
Sodas bottle	$0.07
Fixed Costs:
Rent	$2,555
Phone	$200
Electricity	$700
Insurance	$750
Advertising	$700
Operating Information:
# of days open (weekdays)
# of days open (weekends)
Hours open (weekdays)
Hours open (weekends)
Hourly wage
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
Growth rate
Weeks Per Month
Loan period (years)
Loan interest rate
Taxes:
If income before tax is equal or greater than:		Tax rate =
If income before tax is less than:		Tax rate =

Sheet 2

Start Up Costs:
Kitchen equipment:	$16,250.00
Cash register and sales equipment:	$1,250.00
Initial inventory:	$5,500.00
Pre-opening marketing:	$3,500.00
Cafe fixtures (chairs, tables etc.):	$4,500.00
Oil paintings:	$350.00
Licenses:	$1,025.00
Security deposit:	$6,500.00
First Insurance	$850.00
Total
Owner's Equity
Cash Reserves
Loan Amount

Sheet 3

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 Pies

Pie Slices

Whole Cakes

Cake Slices

Custard

Yogurt Granola Parfait

Ice Cream Cones

Soda

Monthly Revenue

Expenses:

COGS

Rent

Phone

Electricity

Insurance

Advertising

Hourly Wages

Salaries

Loan Payment

Total Expenses

Income Before Tax

Tax

Net Income

Cash Flow