Click on the Problem 3 worksheet. Your company ColaKing is introducing two different kinds of soda: Fizzy Pop and Diet Fizz. No more than 10000 units total can be produced of the two products. The VP of Marketing is trying to best determine the product mix that will bring the most profit to the company. An entire advertising campaign will be set up around this mix. The company charges $3.50 for a can of Fizzy Pop and it costs $1.65 to produce. Diet Fizz sells for $3.85 a can and costs $1.80 to produce.

a. Write the formulas in cells B4 and C4 to determine the profit on each can. Dont worry about the #N/As in Columns D and E they will go away once you get the formulas added they just show the formulas you enter using FORMULATEXT.

b. Notice that the initial product mix being considered will be 50/50 50% of the cans will be Fizzy Pop and 50% of the cans will be Diet Fizz. In cells B8 and C8 write the formulas to determine the profit on each product line respectively. Cell B10 totals the two profit values into a grand total (I wrote this formula for you see I help out).

c. Start up the scenario manager and create the following scenarios (the percentage cells will be the ones you want to change):

50/50

40/60

60/40

30/70

70/30

d. Once you have all of the scenarios in, use the scenario manager to create a summary on its own worksheet (remember that the total profit will be the output cell that you want to see when asked).

e. Based on the scenario summary, which is the best mix to maximize profit? Answer this on the Problem 3 sheet (just type in some text). Does this answer make sense to you as to why it is the best? I want more than a Yes or No here why is the best mix, really the best?

	Fizzy Pop	Diet Fizz
Revenue	$3.50	$3.85
Cost	$1.65	$1.80
Profit	$1.85	$2.05	=B2-B3	=C2-C3
Units To Sell	10000
Percentage Sales	50%	50%
Profit per Product Type			#N/A	#N/A
Total Profit	$0.00		=B8+C8