QSO 320: Module Four Case Study (Need in Excel only)
AppleBerry MP3 Player: Make vs. Buy

AppleBerry (AB) is a high-tech manufacturer of digital products. Recently, it has been very successful at manufacturing a portable MP3 player, the Curvy. The Curvy comes in two forms: one with 32 GB of storage and one with 64 GB of storage. In doing production planning for the upcoming year, AB wants to determine how many of each type of Curvy to manufacture each month. Monthly demand for the Curvy is estimated to be 20,000 units for the 32 GB and 10,000 units for the 64 GB. AB has a limited number of manufacturing hours available to make these two products, with the hours being divided between fabrication, assembly, and shipping, as follows:

Department Monthly Labor Hours Available Hours/Unit 32 GB Curvy Hours/Unit 64 GB Curvy
Fabrication 12,000 1.5 1.75
Assembly 10,000 1.0 1.0
Shipping 6,000 1.0 1.0

Besides its own manufacturing facility, AB can contract out the manufacture to a subcontractor, who can supply up to 20,000 units per month total. This subcontractor will charge $115 for the 32 GB unit and $175 for the 64 GB unit, and it will ship directly to the distribution center. It costs AB $75 to make the 32GB unit and $125 to make the 64GB unit, if they build it themselves. AB plans on selling the 32 GB unit for $150 and the 64 GB unit for $250. Using its existing capacities, AB wants to determine how many of each model it should build itself and how many of each model it should order from the subcontractor in order to optimize profits. AB then wants to analyze the results to determine the best place to add additional capacity. 

1. Create the spreadsheet model for this case and use Solver to optimize. Include an Answer Report and a Sensitivity Report. 

2. Summarize what you found in two to three pages, answering the following questions: 
a. What is the optimal profit? 
b. What is the optimal product mix? 
c. What are the binding constraints? 
d. Should AB increase the labor hours made available for fabrication, assembly, or shipping? For each, explain why or why not. 
e. If AB were to make 5 more hours of shipping labor available, how much will its profit go up? Base your answer on the Sensitivity Report and explain. 
f. If AB were to make 5 more hours of assembly available, how much will its profit go up? Base your answers on the Sensitivity Report and explain. 
g. The subcontractor has agreed to provide more capacity to AB to make more MP3 players, but it will charge an additional $40/unit to do so. Should AB agree? Why or why not? Base your answer on the Sensitivity Report and explain.