Question 4: Sensitivity Analysis (13 Points) Excel program Product X 3 Product Y 5 Availability 60 2 48 Material 1 Material 2 Material 3 Profit/unit 3 1 $4 20 1 S6 4 Number produced Profit contribution 13.333333 53.333333 Total 77.333333 24 Excel's Solver tool has been used in the spreadsheet below to solve a linear programming problem with a minimization objective function and all constraints. 1) What are the Binding Constraints and the Optimum amount of Objective Function? 2) What are the slack/surplus values for Material 1, Material 2 and Material 3? 3) Looking at the Shadow prices, how much would the objective function change by if Material 2 is increases by 5 units? 4) What % of available Material 3 can be reduced without it affecting the current (given) optimum solution? 5) Why the amount of reduced costs are zero? Solver Sensitivity Report: Variable Cells Name Reduced Cost Final Value 13.33333333 4 Objective Coefficient 4 Allowable Allowable Increase Decrease 5 0.4 0.666666667 3.333333333 Product X Product Y 0 0 6 Constraints Given results after developing the excel LP and Solver: Constraint R.H. Side Name Material 1 Material 2 Material 3 Final Shadow Value Price 60 1.111111111 48 0.222222222 17.33333333 0 60 48 Allowable Increase 24 12 1E+30 Allowable Decrease 12 24 2.666666667 20 25 minutes on slide Notes Comments d States)