Question

You are considering leaving your existing job to open a milkshake shack on the beach of a resort on the big island of Hawaii. In your research of restaurants and review of industry reports, you have an idea of what the expected minimum costs to be incurred in operating the business. A unique feature of your milkshakes is that they will be served with flavored straws that match the flavor of the chosen milkshakes by customers. Your research has you to conclude the following:

1. Sales prices of milkshakes $7 for small and $10 for large.
2. Costofdirect materials needed to make milkshakesisasfollows:

Ingredients Required

Direct Material Ingredients Direct Material Prices Small8oz) Large12oz)

Whole Mike $15 for 740oz 2oz 3oz

Cream $20 for 128oz

Sugar $10 30cups 1/2cup 3/4/cup

Premium Vanilla Ice Cream $24 for 600oz 6oz 9oz

Flavorings $0.25 per shake $0.4 per shake

Flavored specialty straw $0.75 per shake $0.75 per shake

Cups 500 8oz cups for $200 X

Cups 500 12oz cups for $250 X

Fixed costs are as follows:

Shack rental $500 per month

Cleaning and other miscellaneous supplies $100 per month

Equipment: Industrial milkshake maker (need 10) $72 each Equipment: Industrial refrigerator/freezer $480

Countertops and other build outs $1,200

Tables and benches for customers $1,080

Annual Insurance $600

Marketing $100 per month

Total fixed cost for the first month's expenses (Shack rental, cleaning supplies and marketing) plus capital expenditures (milkshake makers, refrig/freezer, countertops and tables) plus the first year's insurance is $4,780 for which you will acquire a 2 year loan from a bank at the annual interest rate of 6%. Monthly payments will include both interest and principal and are due on the last day of the month beginning with the first month of operations.

Assume the economic life of the milkshake maker, tables and benches is 3 years while the refrig/freezer, countertops and other build outs will last 10 years.

You will need to hire two part-time employees a monthly salary of $800 each per month including taxes and benefits.

4. Other costs

Ten (10) percent of gross sales must be paid to the resort where the shack is located.

All costs except those funded by the bank loan will be paid out of operating funds.

Requirements - Calculate all solutions using Excel

1. Using the above information, determine the number of milkshakes you will need to sell to break even for one month of operations.

Separate costs into variable and fixed costs

Assume a sales mix of 40% and 60% large milkshakes

2. Using the same information above, now include a monthly salary for you the owner of $5,000 per month. What is the breakeven point in units?

3. How sensitive is the analysis to sales mix (assume you continue to pay yourself)?

4. Should you leave your job and head to the land of milkshakes in Hawaii?