Trained Trained Trained Trained technicians technicians technicians technicians Trainees Trainees Trainee Trainees available at available at avaibble available trained in trained trained trained start of starter at start of at start of March in Aprain May in June March April May June SI S2 S3 S4 TI T2 T3 T4 Sdution 350.000 346.15 328 85 384.62 13.63 0.001 72211 0.001 Total Cost per person 4000 4000 4000 4000 2000 2000 2000 2000 55.810,192 31 LHS RHS Stalling needs in March 1301 -90 44271 1540000 Staffing needs in April 130 -90 45000.00 45000 Stalling needs in May 130 -90 36250 9635000 Stalinseeds in Jand 130 -90 5000000 50000 Waldorce balance in March 350.00 350 Wakforce balance in April 095 0.00 Workforce balance in May -0.93 0.00 0 Workforce balance in Jund -093 000 Microsoft Excel Answer Report Objective Cell (Min) Original Cell Name Value Final Value s SI56 Cost per person Total Variable Cells $5,810,192,31 Cell Name $B$4 Solution S1 SCS4 Solution 52 SOSA Solution S3 SESA Solution S4 SESA Solution 3 $G$4 Solution 12 SHSA Solution T3 SISA Solution T4 Constraints Original Value 0.00 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 22.21 Contin 0.00 Contin 0.00 0.00 0.00 Cell Value Formula Status Slack Cell Name Workforce balance in March $J$12 LHS $I$13 Workforce balance in April LHS $J$14 Workforce balance in May LHS $J$15 Workforce balance in June LHS 0 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>=$L$8 Binding 45000.00 $J$9-$L$9 Binding 0 $J$8 Staffing needs in March LHS $1$9 Staffing needs in April LHS 4271.15 0.00 Not $J$10 Staffing needs in May LHS $1$11 Staffing needs in June LHS 36250.96 $J$10-$1$10 Binding 50000.00 $J$11>=SL$11 Binding 1250.96 0.00 Page 8 ADM2302 Midterm Exam Fall 2021 Microsoft Excel Sensitivity Report Final Reduced Cell Name Value Cost $B$4 Solution S1 350.00 0.00 SC$4 Solution 2 346.15 0.00 SD$4 Solution 53 328.85 0.00 SE$4 Solution S4 384.62 0.00 SFS4 Solution 71 13.65 0.00 SG$4 Solution 72 0.00 9635.00 SH$4 Solution 13 0.00 SIS Solution T4 0.00 6153.85 Objective Coefficient 4000 4000 4000 4000 2000 2000 2000 2000 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 22.21 6153.85 72.21 2000 6118.21 SH$4 Solution T3 $I$4 Solution T4 0.00 6153.85 6000.00 6153.85 0.00 2000 Constraints Shadow Price 2100 -2000 2100 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 $1$9 Staffing needs in April LHS $J$10 Staffing needs in May LHS $J$11 Staffing needs in lune 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 1E+30 50000 1806.94 9387.50 -2000 0 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) (b) 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) (c) 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)