Appsam manufactures two types of smartphones, model X and model Y . Model X is a 6 inch spartphone and model Y is a 5 inch smartphone. Model X has a 28 megapixel camera and model Y has a 18 megapixel camera. For the next week, the company has orders of 100 units of model X and 150 units of model Y . Appsam purchases all the components of smartphone except camera. Depending on demand, Appsam occasionally purchases camera for one or both the cameras. Time required to manufacture lens of model X is 6 minutes and time required to manufacture lens of model Y is 8 minutes and the total time available to manufacture lens is 1080 minutes. Different components of camera needs to be assembled for the camera that are manufactured. Each camera for model X requires 4 minutes of assembly time and each camera for model Y requires 3 minutes of assembly time. Total assembly time for the camera available is 600 minutes. The manufacturing cost is $10 for each smartphone of model X and $6 for each smartphone of model Y . The purchse cost is $14 for each smartphone of model X and $9 for each smartphone of model Y . Management wants to determine the number of smartphones of model X and Y that needs to be manufactured in order to minimize the cost. XM := number of camera of model X manufactured YM := number of camera of model Y manufactured XP := number of camera of model X purchased Y P := number of camera of model Y purchased The linear programming model that needs to be solved is as follows: Min 10XM + 6YM + 14XP + 9Y P s.t. 1XM + 0YM + 1XP + 0Y P = 100 Demand for model X 0XM + 1YM + 0XP + 1Y P = 150 Demand for model Y 4XM + 3YM + 0XP + 0Y P 600 Camera assembly time 6XM + 8YM + 0XP + 0Y P 1080 Lens manufacturing time XM, YM,XP, Y P 0 1. Generate sensitivity report for the LP Problem using excel.

I have given the answer below, please show Excel solver steps to show this answer.

FIGURE 8.19 SENSITIVITY REPORT FOR THE DIGITAL CONTROLS, INC., PROBLEM Variable Cells Model Final Value Variable Name AM Models A Manufactured 100.000 BM Models B Manufactured 60.000 AP Models A Purchased 0.000 BP Models B Purchased 90.000 Constraints Final Name Value Demand for model A 100.000 Demand for model B 150.000 Injection molding time 580.000 Assembly time 1080.000 Constraint Number 1 2 3 4 Reduced Objective Allowable Allowable Cost Coefficient Increase Decrease 0.000 10.000 1.750 1E+30 0.000 6.000 3.000 2.333 1.750 14.000 1E+30 1.750 0.000 9.000 2.333 3.000 Shadow Constraint Allowable Allowable Price R.H. Side Increase Decrease 12.250 100.000 11.429 100.000 9.000 150.000 1E+30 90.000 0.000 600.000 1E+30 20.000 -0.375 1080.000 53.333 480.000 FIGURE 8.19 SENSITIVITY REPORT FOR THE DIGITAL CONTROLS, INC., PROBLEM Variable Cells Model Final Value Variable Name AM Models A Manufactured 100.000 BM Models B Manufactured 60.000 AP Models A Purchased 0.000 BP Models B Purchased 90.000 Constraints Final Name Value Demand for model A 100.000 Demand for model B 150.000 Injection molding time 580.000 Assembly time 1080.000 Constraint Number 1 2 3 4 Reduced Objective Allowable Allowable Cost Coefficient Increase Decrease 0.000 10.000 1.750 1E+30 0.000 6.000 3.000 2.333 1.750 14.000 1E+30 1.750 0.000 9.000 2.333 3.000 Shadow Constraint Allowable Allowable Price R.H. Side Increase Decrease 12.250 100.000 11.429 100.000 9.000 150.000 1E+30 90.000 0.000 600.000 1E+30 20.000 -0.375 1080.000 53.333 480.000