20. Go to the Suppliers worksheet, where Benicio wants to determine whether subcontracting could reduce the cost of the new virtual assistant product. Run Solver to solve this problem as follows: Set the objective as minimizing (Min) the total cost cell E10. Use the units produced values range B5:D5 as the changing variable cells. Adjust the number of units produced by each supplier using the following constraints: Set the total number of virtual assistants produced cell E5 as greater than or equal to 11,000, DIG Technology's minimum production goal. Set the total cost cell E10 to be less than or equal to 925,000, the maximum total cost DIG Technology wants to spend. Set the total number of virtual assistants produced by a single supplier range B5:D5 to be less than or equal to 4000 to balance the production among the suppliers. Make sure the values in the range B5:D5 are integers since DIG Technology cannot sell a fraction of a product.
Run Solver, keep the solution, and then return to the Solver Parameters dialog box. Save the model in the range A14:A21.
21. Run Solver again, create an Answer report, and then close the Solver Parameters dialog box.
22. 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:
In the Scenario Manager, add a scenario using Reduced Costs as the name.
Both other two scenarios.
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. 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.
23. 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:
Create a Scenario PivotTable report using the profit per unit sold range B17:F17 as the result cells. Remove the Filter field from the PivotTable. Change the number format of the value fields to Currency with 2 decimal places and the $ symbol.
