Use the Excel document to calculate the following information. Similar to the homework assignment, only enter the information into the yellow cells (D6:H7) and the formulas will update. Note how the numbers in the financial model change as you as change the price/demand. Enter the information to help you maximize the firms total margin.

What is the ideal price point (and associated demand from the table) to maximize total margin for the firm?

		MARGARITA OPTIONS
		REGULAR	LARGE	PITCHER	SAMPLER	SUPER SAMPLER
PRICE
ANNUAL DEMAND

What is the total margin (cell I23) from margaritas for the firm at the ideal price point for each product line?

__________________________________________

MARGARITA OPTIONS REGULAR LARGE SUPER SAMPLER PRICE ANNUAL DEMAND BREAKEVEN UNITS #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! FIXED COSTS LIQUOR LICENSE REGULAR 25,000 18,000 15,000 12,000 10,000 7,500 7,000 5,550 5,000 0 0 $ 1,800 VARIABLE COSTS STRAWS GLASS (BREAKAGE) PRODUCT COST $ $ $ SAMPLER N/A N/A N/A N/A N/A NA N/A N/A N/A N/A N/A 2,500 1,800 1,600 0 SUPER SAMPLER N/A N/A N/A N/A NA N/A N/A N/A N/A N/A N/A 0.01 $ 0.02 $ 2.50 $ 0.01 0.02 5.00 $ $ $ PRICE $3.00 $3.50 $4.00 $4.50 $5.00 $5.50 $6.00 $6.50 $7.00 $8.00 $9.00 $10.00 $11.00 $12.00 $20.00 $25.00 $30.00 $35.00 $40.00 $45.00 0.03 $ 0.05 $ 15.00 $ 0.03 $ 0.03 $ 5.00 $ LARGE N/A N/A N/A N/A N/A N/A N/A N/A N/A 2,500 2,250 2,000 1,800 1,500 0 0 0 0 0 0 0.12 0.05 20.00 DEMAND PITCHER N/A N/A N/A N/A N/A N/A NA N/A N/A N/A N/A N/A N/A N/A 5,000 2,500 1,500 0 0 0 SSSSSSS N/A $ $ $ $ - #DIV/0! #DIV/0! #DIV/0! #DIV/0! SALES PERCENTAGE OF TOTAL SALES ALLOCATED FIXED COSTS VARIABLE COSTS TOTAL COSTS TOTAL MARGIN - #DIV/0! #DIV/0! #DIV/0! #DIV/0! $ - #DIV/0! #DIV/0! $ #DIV/0! #DIV/0! - #DIV/0! #DIV/0! - #DIV/0! #DIV/0! - #DIV/0! #DIV/0! - #DIV/0! #DIV/0! $ $ $ - $ #DIV/0! #DIV/0! #DIV/0! #DIV/0! O O 0 0 0 0 0 N/A N/A N/A N/A 600 500 375 250 0 0 0 0 MARGARITA OPTIONS REGULAR LARGE SUPER SAMPLER PRICE ANNUAL DEMAND BREAKEVEN UNITS #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! FIXED COSTS LIQUOR LICENSE REGULAR 25,000 18,000 15,000 12,000 10,000 7,500 7,000 5,550 5,000 0 0 $ 1,800 VARIABLE COSTS STRAWS GLASS (BREAKAGE) PRODUCT COST $ $ $ SAMPLER N/A N/A N/A N/A N/A NA N/A N/A N/A N/A N/A 2,500 1,800 1,600 0 SUPER SAMPLER N/A N/A N/A N/A NA N/A N/A N/A N/A N/A N/A 0.01 $ 0.02 $ 2.50 $ 0.01 0.02 5.00 $ $ $ PRICE $3.00 $3.50 $4.00 $4.50 $5.00 $5.50 $6.00 $6.50 $7.00 $8.00 $9.00 $10.00 $11.00 $12.00 $20.00 $25.00 $30.00 $35.00 $40.00 $45.00 0.03 $ 0.05 $ 15.00 $ 0.03 $ 0.03 $ 5.00 $ LARGE N/A N/A N/A N/A N/A N/A N/A N/A N/A 2,500 2,250 2,000 1,800 1,500 0 0 0 0 0 0 0.12 0.05 20.00 DEMAND PITCHER N/A N/A N/A N/A N/A N/A NA N/A N/A N/A N/A N/A N/A N/A 5,000 2,500 1,500 0 0 0 SSSSSSS N/A $ $ $ $ - #DIV/0! #DIV/0! #DIV/0! #DIV/0! SALES PERCENTAGE OF TOTAL SALES ALLOCATED FIXED COSTS VARIABLE COSTS TOTAL COSTS TOTAL MARGIN - #DIV/0! #DIV/0! #DIV/0! #DIV/0! $ - #DIV/0! #DIV/0! $ #DIV/0! #DIV/0! - #DIV/0! #DIV/0! - #DIV/0! #DIV/0! - #DIV/0! #DIV/0! - #DIV/0! #DIV/0! $ $ $ - $ #DIV/0! #DIV/0! #DIV/0! #DIV/0! O O 0 0 0 0 0 N/A N/A N/A N/A 600 500 375 250 0 0 0 0