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:

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.
Create the spreadsheet model for this case and use Solver to optimize. Include an Answer Report and a Sensitivity Report.
Summarize what you found in two to three pages, answering the following questions:
1.What is the optimal profit?
2. What is the optimal product mix?
3. What are the binding constraints?
4. Should AB increase the labor hours made available for fabrication, assembly, or shipping? For each, explain why or why not.

Transcribed Image Text:

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