I am trying to get help on QSO-320 Week 4 Midterm. I've attached the question that needs to be answered. AppleBerry Case Study

QSO 320: Module Four Case Study 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 Fabrication Assembly Shipping Monthly Labor Hours Available 12,000 10,000 6,000 Hours/Unit 32 GB Curvy 1.5 1.0 1.0 Hours/Unit 64 GB Curvy 1.75 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. Problem 1: 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. Problem 2: 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. Problem 3: 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. Rubric Requirements of submission: Written parts of the assignment must follow these formatting guidelines: double spacing, 12-point Times New Roman font, one-inch margins, and discipline-appropriate citations. Critical Elements Exemplary Proficient Needs Improvement Not Evident Value Spreadsheet Summarize Findings: Optimal Profit Summarize Findings: Optimal Product Mix Summarize Findings: Binding Constraints Summarize Findings: Labor Hours Includes an optimized spreadsheet with all required elements and spreadsheet is error-free (32-35) Determines accurate optimal profit based on findings, and justifies answer with details from report (9-10) Determines optimal product mix based on findings, and justifies answer with details from report(9-10) Determines binding constraints based on findings, and justifies answer (9-10) Discusses whether labor hours should increase for fabrication, shipping, and assembly and provides a Includes an optimized spreadsheet with all required elements, with limited errors Includes a spreadsheet, but it is not optimized or does not include all required elements (28-31) Determines accurate optimal profit based on findings and provides limited justification (8) (25-27) Determines an optimal profit, but is inaccurate or does not justify answer (7) Determines optimal product mix based on findings and provides limited justification Does not include a spreadsheet. 35 (0-24) Does not determine an optimal profit. (06) 10 Determines an optimal product mix, but is inaccurate or does not justify answer Does not determine an optimal product mix 10 (7) Determines binding constraints, but information is inaccurate or does not justify answer (7) Discusses whether labor hours should increase for fabrication, shipping, and assembly but answer is (0-6) Does not determine binding constraints (8) Determines binding constraints based on findings and provides limited justification (8) Discusses whether labor hours should increase for fabrication, shipping, and assembly and provides a (0-6) Does not discuss whether labor hours should increase 10 10 Problem 1 Problem 2 Problem 3 Writing (Mechanics/Citations) detailed explanation and justification (9-10) Provides calculation, arrives at accurate response based on sensitivity report, and explains answer in detail (5) limited explanation (8) Provides calculation, arrives at accurate response based on sensitivity report, and provides limited explanation Provides calculation, arrives at accurate response based on sensitivity report, and explains answer in detail (5) Provides calculation, arrives at accurate response based on sensitivity report, and provides limited explanation Provides calculation, arrives at accurate response based on sensitivity report, and explains answer in detail (5) Provides calculation, arrives at accurate response based on sensitivity report, and provides limited explanation No errors related to organization, grammar and style, and citations, and report is clear and easy to understand (9-10) inaccurate or an explanation is not provided (7) Provides calculation, however response is inaccurate, not based on sensitivity report, or not explained (4) Does not solve or explain this problem 5 (0-2) (3) Provides calculation, however response is inaccurate, not based on sensitivity report, or not explained (4) Does not solve or explain this problem 5 (0-2) (3) Provides calculation, however response is inaccurate, not based on sensitivity report, or not explained (4) Minor errors related to organization, grammar and style, and citations, but report is clear and easy to understand (8) (0-6) Does not solve or explain this problem 5 (0-2) (3) Some errors related to organization, grammar and style, and citations that make response difficult to understand (7) Major errors related to organization, grammar and style, and citations (0-6) 10 Total 100%