QUESTION 3: Sensitivity Analysis (24 points) The Excel spreadsheet formulation and Solver output to Question 2 is provided below: Trained Trained Trained Trained technicians technicians technicians technicians Trainees Trainees Trainees Trainees available at available at available available trained in trained trained trained start of start of at start of at start of March in April in May in June March April May June SI S2 S3 S4 12 T3 T4 Solution 350.00 346.15 328.85 384.62 13.65 0.00 72.21 0.00 Tatal Cost per person 4000 4000 4000 4000 2000 2000 2000 2000 S 5,810,192.31 LHS RHS Staffing needs in March 130 .90 44271.1540000 Staffing needs in April 130 .90 45000.00 45000 Staffing needs in May 130 .90 36250.96 35000 Staffing needs in June 130 50000.00 50000 Workforce balance in March 350.00 350 Workforce balance in April -0.95 0.00 0 Workforce balance in May -0.95 0.00 0 Workforce balance in June -0.95 0.00 0 Microsoft Excel Answer Report Objective Cell (Min) Original Cell Name Value Final Value .90 $J$6 Cost per person Total $ $5,810,192.31 Variable Cells Original Value 0.00 0.00 0.00 0.00 Final Value Integer 350.00 Contin 346.15 Contin 328.85 Contin 384.62 Contin 13.65 Contin 0.00 Contin 72.21 Contin 0.00 Contin 0.00 0.00 0.00 0.00 Cell Name $B$4 Solution S1 $C$4 Solution S2 $D$4 Solution S3 $E$4 Solution S4 $F$4 Solution T1 $G$4 Solution T2 SH$4 Solution T3 $i$4 Solution T4 Constraints Cell Name Workforce balance in March $J$12 LHS $J$13 Workforce balance in April LHS $J$14 Workforce balance in May LHS $J$15 Workforce balance in June LHS $J$8 Staffing needs in March LHS $J$9 Staffing needs in April LHS $J$10 Staffing needs in May LHS $J$11 Staffing needs in June LHS Cell Value Formula Status Slack 0 0 0 0 350.00 $J$12-$L$12 Binding 0.00 $J$13-$L$13 Binding 0.00 $J$14-$L$14 Binding 0.00 $J$15=$L$15 Binding Not 44271.15 $J$8>$L$8 Binding 45000.00 $J$9>$L$9 Binding Not 36250.96 $J$10>$L$10 Binding 50000.00 $J$11>$L$11 Binding 4271.15 0.00 1250.96 0.00 Microsoft Excel Sensitivity Report Name Cell $B$4 Solution 51 $C$4 Solution S2 $D$4 Solution S3 $E$4 Solution 54 $F$4 Solution T1 $G$4 Solution T2 $H$4 Solution T3 $i$4 Solution T4 Final Value 350.00 346.15 328.85 384.62 13.65 0.00 Reduced Objective Allowable Allowable Cost Coefficient Increase Decrease 0.00 4000 1E+30 1E+30 0.00 4000 1E+30 7995.00 0.00 4000 1E+30 5812.30 0.00 4000 1E+30 6000.00 0.00 2000 1E+30 7995.00 9635.00 2000 9635.00 0.00 2000 6118.21 6000.00 6153.85 2000 6153.85 72.21 0.00 Constraints Shadow Price 2100 -2000 Cell Name $J$12 Workforce balance in March LHS $J$13 Workforce balance in April LHS $J$14 Workforce balance in May LHS $J$15 Workforce balance in June LHS $J$8 Staffing needs in March LHS $J$9 Staffing needs in April LHS $J$10 Staffing needs in May LHS $J$11 Staffing needs in June LHS Final Value 350.00 0.00 0.00 0.00 44271.15 45000.00 36250.96 50000.00 2100 -2000 0 Constraint Allowable Allowable R.H. Side Increase Decrease 350 14.37 19.82 0 13.65 47.46 0 76.01 5.80 0 72.21 13.90 40000 4271.15 1E+30 45000 6169.44 794.36 35000 1250.96 1E+30 50000 1806.94 9387.50 61.50 0 46.15 Answer all of the questions below. For the purpose of these questions, assume that fractional values of decision variables make sense. Two decimal places accuracy is fine. Note that each question below is to be considered independently of all others. (a) What is the optimal value of the objective function, and what are the optimal values of the decision variables? (3 points) (6) If the salary/cost to train a trainee during the month of May has increased to $3000, what would be the impact on the optimal solution and its cost? Justify (5 points) ) What is the allowable increase for the variables T2 and T4, the cost to train a trainee during April and June (I have deleted these number from the table)? Why? (4 points) (d) Power Generation Inc. has underestimated staffing needs in March by 2000 hours, thus staffing needs for March should have been 42,000 hours. What would be the impact on the optimal solution? Justify. (3 points) (e) As a result of successful sales efforts the staffing needs have increased in April to 48,000 hours. However, in June, it is expected to drop by 1000 hours resulting in a staffing needs of 49,000 hours in June. No other changes have occurred in the formulation. If you can determine what the new optimal value of the objective function is then do so, explaining why your approach is correct. If you cannot determine the new optimal value of the objective function, explain why. (6 points) (f) Staffing needs in June have increased by 3000 to a value of 53,000. This is the only change in the formulation. Is the new optimal value of the objective function above or below $5,850,000? Defend your answer. (3 points) QUESTION 3: Sensitivity Analysis (24 points) The Excel spreadsheet formulation and Solver output to Question 2 is provided below: Trained Trained Trained Trained technicians technicians technicians technicians Trainees Trainees Trainees Trainees available at available at available available trained in trained trained trained start of start of at start of at start of March in April in May in June March April May June SI S2 S3 S4 12 T3 T4 Solution 350.00 346.15 328.85 384.62 13.65 0.00 72.21 0.00 Tatal Cost per person 4000 4000 4000 4000 2000 2000 2000 2000 S 5,810,192.31 LHS RHS Staffing needs in March 130 .90 44271.1540000 Staffing needs in April 130 .90 45000.00 45000 Staffing needs in May 130 .90 36250.96 35000 Staffing needs in June 130 50000.00 50000 Workforce balance in March 350.00 350 Workforce balance in April -0.95 0.00 0 Workforce balance in May -0.95 0.00 0 Workforce balance in June -0.95 0.00 0 Microsoft Excel Answer Report Objective Cell (Min) Original Cell Name Value Final Value .90 $J$6 Cost per person Total $ $5,810,192.31 Variable Cells Original Value 0.00 0.00 0.00 0.00 Final Value Integer 350.00 Contin 346.15 Contin 328.85 Contin 384.62 Contin 13.65 Contin 0.00 Contin 72.21 Contin 0.00 Contin 0.00 0.00 0.00 0.00 Cell Name $B$4 Solution S1 $C$4 Solution S2 $D$4 Solution S3 $E$4 Solution S4 $F$4 Solution T1 $G$4 Solution T2 SH$4 Solution T3 $i$4 Solution T4 Constraints Cell Name Workforce balance in March $J$12 LHS $J$13 Workforce balance in April LHS $J$14 Workforce balance in May LHS $J$15 Workforce balance in June LHS $J$8 Staffing needs in March LHS $J$9 Staffing needs in April LHS $J$10 Staffing needs in May LHS $J$11 Staffing needs in June LHS Cell Value Formula Status Slack 0 0 0 0 350.00 $J$12-$L$12 Binding 0.00 $J$13-$L$13 Binding 0.00 $J$14-$L$14 Binding 0.00 $J$15=$L$15 Binding Not 44271.15 $J$8>$L$8 Binding 45000.00 $J$9>$L$9 Binding Not 36250.96 $J$10>$L$10 Binding 50000.00 $J$11>$L$11 Binding 4271.15 0.00 1250.96 0.00 Microsoft Excel Sensitivity Report Name Cell $B$4 Solution 51 $C$4 Solution S2 $D$4 Solution S3 $E$4 Solution 54 $F$4 Solution T1 $G$4 Solution T2 $H$4 Solution T3 $i$4 Solution T4 Final Value 350.00 346.15 328.85 384.62 13.65 0.00 Reduced Objective Allowable Allowable Cost Coefficient Increase Decrease 0.00 4000 1E+30 1E+30 0.00 4000 1E+30 7995.00 0.00 4000 1E+30 5812.30 0.00 4000 1E+30 6000.00 0.00 2000 1E+30 7995.00 9635.00 2000 9635.00 0.00 2000 6118.21 6000.00 6153.85 2000 6153.85 72.21 0.00 Constraints Shadow Price 2100 -2000 Cell Name $J$12 Workforce balance in March LHS $J$13 Workforce balance in April LHS $J$14 Workforce balance in May LHS $J$15 Workforce balance in June LHS $J$8 Staffing needs in March LHS $J$9 Staffing needs in April LHS $J$10 Staffing needs in May LHS $J$11 Staffing needs in June LHS Final Value 350.00 0.00 0.00 0.00 44271.15 45000.00 36250.96 50000.00 2100 -2000 0 Constraint Allowable Allowable R.H. Side Increase Decrease 350 14.37 19.82 0 13.65 47.46 0 76.01 5.80 0 72.21 13.90 40000 4271.15 1E+30 45000 6169.44 794.36 35000 1250.96 1E+30 50000 1806.94 9387.50 61.50 0 46.15 Answer all of the questions below. For the purpose of these questions, assume that fractional values of decision variables make sense. Two decimal places accuracy is fine. Note that each question below is to be considered independently of all others. (a) What is the optimal value of the objective function, and what are the optimal values of the decision variables? (3 points) (6) If the salary/cost to train a trainee during the month of May has increased to $3000, what would be the impact on the optimal solution and its cost? Justify (5 points) ) What is the allowable increase for the variables T2 and T4, the cost to train a trainee during April and June (I have deleted these number from the table)? Why? (4 points) (d) Power Generation Inc. has underestimated staffing needs in March by 2000 hours, thus staffing needs for March should have been 42,000 hours. What would be the impact on the optimal solution? Justify. (3 points) (e) As a result of successful sales efforts the staffing needs have increased in April to 48,000 hours. However, in June, it is expected to drop by 1000 hours resulting in a staffing needs of 49,000 hours in June. No other changes have occurred in the formulation. If you can determine what the new optimal value of the objective function is then do so, explaining why your approach is correct. If you cannot determine the new optimal value of the objective function, explain why. (6 points) (f) Staffing needs in June have increased by 3000 to a value of 53,000. This is the only change in the formulation. Is the new optimal value of the objective function above or below $5,850,000? Defend your answer. (3 points)