0.001 90 Trained Trained Trained Trained technicians technicians technicians technicians Trainees Trainees Trainees Trainees available at available at available available inained in trained trained trained start of starter at start of at start of March in April in May in June March April May June SL S2 S3 S4 TI T2 T3 T4 Sduto 350.001 346.13 328.83 384 62 13.65 7221 0.00 Total Coulet person 4000 4000 4000 1000 2000 2000 2000 2000 $5,10,19231 LHS RS Stalling teeds in March 130 44271.1540000 Suffing needs in April 130 .90 45000 45000 Stalfing needs in May 130 901 362509635000 Staling needs and 130 -90 50000 50000 Waldorce balance in March 1 350.00 330 Workforce balance in April 095 0.00 O Workforce balance in My -0.93 1 1 00 o Workforce balance in und 0.95 0.00 o Microsoft Excel Answer Report Objective Cell (Min) Original Cell Name Value Final Value $ $5,810,192.31 $I$6 Cost per person Total Variable Cells Cell Name $B$4 Solution 51 SCS Solution 52 SOSA Solution 3 SESA Solution 4 SPSA Solution Ti SGS4 Solution 72 SHSA Solution 13 $154 Solution 14 Constraints Original Value 0.00 0.00 0.00 0.00 0.00 0.00 OXOXO 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 Constraints Cell Value Formula Status Slack 0 0 Cell Name Workforce balance in March SJ$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 SJS9 Staffing needs in April Las $I$10 Staffing needs in May LHS $I$11 Staffing needs in June LHS 0 350.00 $J$12=SL$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>-SL$8 Binding 45000.00 $J$9>=$L$9 Binding Not 36250.96 $I$10-$L$10 Binding 50000.00 $I$11) $L$11 Binding 4271.15 0.00 1250.96 0.00 Page 8 ADM2302 Midtenn Exam Fall 2021 Microsoft Excel Sensitivity Report Final Reduced Cell Name Value Cost $B$4 Solution Si 350.00 0.00 SCS4 Solution S2 346.15 0.00 SDS4 Solution 53 328.85 0.00 SE$4 Solution S4 384.62 0.00 SFSA Solution T1 13,65 0.00 SGS4 Solution T2 0.00 9635.00 SHSG Solution 13 72.21 0.00 SIS Solution T4 0.00 6153.85 Objective Allowable Allowable Coefficient Increase Decrease 4000 1E430 1E+30 4000 1E+30 7995.00 1000 1E+30 5812.30 4000 1E+30 6000.00 2000 1E+30 7995.00 2000 9635.00 2000 6118.21 6000.00 2000 6153.85 Microsoft Excel Sensitivity Report Final Value Reduced Cost 0.00 0.00 Objective Coefficient 4000 350.00 346.15 0.00 Cell Name $B$4 Solution S1 $C$4 Solution S2 $D$4 Solution 3 $E$4 Solution S4 $F$4 Solution 11 $G$4 Solution T2 SHS4 Solution T3 $184 Solution T4 328.85 384.62 13.65 0.00 72.21 0.00 Allowable Allowable Increase Decrease 1E+30 1E+30 1E+30 7995.00 1E+30 5812.30 1E+30 6000.00 1E+30 7995.00 9635.00 6118.21 6000.00 6153.85 4000 4000 4000 2000 2000 2000 0.00 0.00 9635.00 0.00 6153.85 2000 Constraints Shadow Price 2100 2000 2100 Cell Name $1$12 Workforce balance in March LHS $J$13 Workforce balance in April LHS $I$14 Workforce balance in May LHS $J$15 Workforce balance in June 145 SI$8 Staffing needs in March LHS SISS Staffing needs in April LHS $I$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 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 1E30 50000 1806.94 9387.50 -2000 0 61.50 0 46.15 (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) (1) 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) 0.001 90 Trained Trained Trained Trained technicians technicians technicians technicians Trainees Trainees Trainees Trainees available at available at available available inained in trained trained trained start of starter at start of at start of March in April in May in June March April May June SL S2 S3 S4 TI T2 T3 T4 Sduto 350.001 346.13 328.83 384 62 13.65 7221 0.00 Total Coulet person 4000 4000 4000 1000 2000 2000 2000 2000 $5,10,19231 LHS RS Stalling teeds in March 130 44271.1540000 Suffing needs in April 130 .90 45000 45000 Stalfing needs in May 130 901 362509635000 Staling needs and 130 -90 50000 50000 Waldorce balance in March 1 350.00 330 Workforce balance in April 095 0.00 O Workforce balance in My -0.93 1 1 00 o Workforce balance in und 0.95 0.00 o Microsoft Excel Answer Report Objective Cell (Min) Original Cell Name Value Final Value $ $5,810,192.31 $I$6 Cost per person Total Variable Cells Cell Name $B$4 Solution 51 SCS Solution 52 SOSA Solution 3 SESA Solution 4 SPSA Solution Ti SGS4 Solution 72 SHSA Solution 13 $154 Solution 14 Constraints Original Value 0.00 0.00 0.00 0.00 0.00 0.00 OXOXO 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 Constraints Cell Value Formula Status Slack 0 0 Cell Name Workforce balance in March SJ$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 SJS9 Staffing needs in April Las $I$10 Staffing needs in May LHS $I$11 Staffing needs in June LHS 0 350.00 $J$12=SL$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>-SL$8 Binding 45000.00 $J$9>=$L$9 Binding Not 36250.96 $I$10-$L$10 Binding 50000.00 $I$11) $L$11 Binding 4271.15 0.00 1250.96 0.00 Page 8 ADM2302 Midtenn Exam Fall 2021 Microsoft Excel Sensitivity Report Final Reduced Cell Name Value Cost $B$4 Solution Si 350.00 0.00 SCS4 Solution S2 346.15 0.00 SDS4 Solution 53 328.85 0.00 SE$4 Solution S4 384.62 0.00 SFSA Solution T1 13,65 0.00 SGS4 Solution T2 0.00 9635.00 SHSG Solution 13 72.21 0.00 SIS Solution T4 0.00 6153.85 Objective Allowable Allowable Coefficient Increase Decrease 4000 1E430 1E+30 4000 1E+30 7995.00 1000 1E+30 5812.30 4000 1E+30 6000.00 2000 1E+30 7995.00 2000 9635.00 2000 6118.21 6000.00 2000 6153.85 Microsoft Excel Sensitivity Report Final Value Reduced Cost 0.00 0.00 Objective Coefficient 4000 350.00 346.15 0.00 Cell Name $B$4 Solution S1 $C$4 Solution S2 $D$4 Solution 3 $E$4 Solution S4 $F$4 Solution 11 $G$4 Solution T2 SHS4 Solution T3 $184 Solution T4 328.85 384.62 13.65 0.00 72.21 0.00 Allowable Allowable Increase Decrease 1E+30 1E+30 1E+30 7995.00 1E+30 5812.30 1E+30 6000.00 1E+30 7995.00 9635.00 6118.21 6000.00 6153.85 4000 4000 4000 2000 2000 2000 0.00 0.00 9635.00 0.00 6153.85 2000 Constraints Shadow Price 2100 2000 2100 Cell Name $1$12 Workforce balance in March LHS $J$13 Workforce balance in April LHS $I$14 Workforce balance in May LHS $J$15 Workforce balance in June 145 SI$8 Staffing needs in March LHS SISS Staffing needs in April LHS $I$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 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 1E30 50000 1806.94 9387.50 -2000 0 61.50 0 46.15 (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) (1) 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)