Product Mix

1. 	Mini 1	Mini 2	Voice XP	Voice XP10	Waterproof
   Revenue
   Units sold	20,150	17,025	13,530	12,945	10,750
   Price per unit	$ 119.99	$ 129.99	$ 139.99	$ 145.95	$ 135.95
   Total sales	$2,417,798.50	$2,213,079.75	$1,894,064.70	$1,889,322.75	$1,461,462.50
   Cost of goods sold
   Units manufactured	20,150	17,025	13,530	12,945	10,750
   Fixed costs	$ 265,000.00	$ 270,000.00	$ 279,000.00	$ 282,000.00	$ 280,000.00
   Variable cost per unit	$ 95.00	$ 105.00	$ 110.00	$ 115.00	$ 102.00
   Variable costs	$1,914,250.00	$1,787,625.00	$1,488,300.00	$1,488,675.00	$1,096,500.00
   Total costs	$2,179,250.00	$2,057,625.00	$1,767,300.00	$1,770,675.00	$1,376,500.00
   Total profit	$ 238,549	$ 155,455	$ 126,765	$ 118,648	$ 84,962
   Profit per unit sold	$11.84	$9.13	$9.37	$9.17	$7.90

2. Go to the Product Mix worksheet, which calculates the profit from each model of portable speaker. Benicio wants to compare three scenarios: one with the current prices and costs, another with the prices raised $10.00, and a third that reduces the variable costs per unit by $5.00. He has already created the first two scenarios and defined names for cells and ranges. Create a third scenario as follows:
   1. In the Scenario Manager, add a scenario using Reduced Costs as the name.
   2. Accept the same changing cells (nonadjacent ranges B6:F6 and B11:F12) as the other two scenarios.
   3. Reduce each variable cost per unit value (Mini1_Variable_Cost, Mini2_Variable_Cost, VoiceXP_Variable_Cost, VoiceXP10_Variable_Cost, and Waterproof_Variable_Cost) by $5.00.
   4. Create a Scenario Summary report that summarizes the effect of the three scenarios. Use the profit per unit sold (range B17:F17) as the result cells.
3. Return to the Product Mix worksheet. Benicio wants to provide a visual way to compare the scenarios. Use the Scenario Manager as follows to create a PivotTable that compares the profit per unit in each scenario as follows:
   1. Create a Scenario PivotTable report using the profit per unit sold (range B17:F17) as the result cells.
   2. Remove the Filter field from the PivotTable.
   3. Change the number format of the value fields to Currency with 2 decimal places and the $ symbol.