PLEASE USE EXCEL AND THE SHOW THE FORMULAS YOU USE, THANK YOU!

The case assumes students will open a milkshake shack on the beach of a resort on the big Island of Hawaii. I have studied existing restaurants, read industry reports, and have done some research on expected minimum costs to be incurred in operating the business. A unique feature of my milkshakes is that I will serve them with flavored straws that match the flavor of the chosen milkshakes by customers. My research embeds the following assumptions:

Sales prices of milkshakes ($7.00 for small, and $10.00 for large)

Cost of materials needed to make milkshakes:

DIRECT MATERIAL INGREDIENTS	Small (8 oz. size)	Large (12 oz. size)
Whole Milk ($15 for a 5 gallon=640 oz.)	(need 2 oz. of milk)	(need 3 oz. of milk)
Cream ($20 for 1 gallon=128 oz.)	(need 2 oz. of cream)	(need 3 oz. of cream)
Sugar ($10 for a 15 lb. bag=30 cups)	(need cup of sugar)	(need cup of sugar)
Premium Vanilla Ice Cream ($24 for 600 oz.)	(need 6 oz. of ice cream)	(need 9 oz. of ice cream)
Flavorings	.25 per shake	.40 per shake
Flavored specialty straws	.75 per straw	.75 per straw
Cups (500 8 oz. cups @ a cost of $200)		-------------------------------
Cups (500 12 oz. cups @ a cost of $250)	----------------------------

Fixed costs:

Shack rental: $500 a month

Cleaning and other miscellaneous supplies: $100 a month

Equipment: Industrial Milk Shake Maker: $72 per machine x 10 machines=$720

Equipment: Industrial Refrigerator/freezer: $480

Countertops: $1,200

Tables and benches for customers to sit outside: $108 per bench-set x 10=$1,080

Annual insurance: $600 a year

Sign: use your marketing knowledge to think of a good name= $100

Advertising expenses: $5,000 a month

Accounting and bookkeeping costs: $500 a month

Owners salary: $96,000 a year

Dues and membership fees: $2,000 a year.

Licenses and permit fees: $600 a year.

Maintenance services: $400 a month.

Office supplies: $300 a month.

Employees:

Two part-time employees: each receiving a monthly salary of $800 (including benefits).

Total Start-up Costs = $20,247 for which were owner financed.

Other costs:

10% of gross sales must be given to resort where shack will be located on its premises.

Owners capital will be used to cover direct materials costs.

REQUIREMENTS OF THE FIRST PART OF THE CASE

In order to answer the questions below, you may make additional assumptions, and add/change fixed and variable costs (please clearly indicate all assumptions made).

1. Using the above information, determine the number of milkshakes you will need to sell to break- even. In order to do this, you will need to:

Set a sales price (please use the competitors sales prices first of $10 for Large, and $7 (for small).

Please clearly state which costs are fixed and which are variable. (Hint: keep all costs on a monthly basis throughout your analysis). Clearly indicate what the TOTAL fixed costs are on a monthly basis. Also, please clearly indicate variable cost PER UNIT for both the small and large sizes.

You will also need to vary assumptions about depreciable lives for long-term assets.

The Sales mix you will use is that of 60% Large, and 40% small. You are to present the solution based on that sales mix. Therefore, you will need to calculate a weighted average contribution margin [($10-variable cost per unit for large) * .60] + [($7- variable cost per unit for small) * .40]

2.Finally, make a recommendation as to whether or not you should leave your job to open the milkshake shack?