Question

Excel Solver please:

P,= number of producers in month i where i = 1, 2, 3 for April, May, June) Ti = number of trainers in month i (where i = 1, 2 for April, May) A = number of apprentices in month i (where i = 2, 3 for May, June) R;= number of recruits in month i (where i = 1, 2 for April, May) 2. Define the Objective Function Minimize total wage cost for producers, trainers, apprentices, and recruits for April, May, and June: Min 3000P, + 3300T1 + 2200R, + 3000P2 + 3300T2 + 260042+2200R2 + 3000P3 + 260043 3. Define the Constraints Total production in Month 1 (April) must equal or exceed contract for Month 1: (1).6P +.37 +.05R > 20 Total production in Months 1-2 (April, May) must equal or exceed total contracts for Months 1-2: (2).6P, +.37 +.05R, +6P2+.37, +.442 +.05R, > 44 Total production in Months 1-3 (April, May, June) must equal or exceed total contracts for Months 1-3: (3).6P,+.371+.05R1+6P2+.372+.4A2+.05R2+.6P3+.443 74 In April there are 100 employees that can be producers or trainers: (4) P1+ T1 = 100 At the end of June, there are to be at least 140 employees: (5) P: + A: > 140 Each trainer can train two recruits: (6) 2TR (7) 272 R2 The number of producers and trainers in a month must equal the number of producers, trainers, and apprentices in the previous month (8) P+T= P2 +T2 (9) P2+ Tz + 12 =P The number of apprentices in a month must equal the number of recruits in the previous month: (10) A2=R (11) A3 = R2 Non-negativity: P1, T1, R1, P2, T2, A2, R2, P3, 43 20

Answer 1

Optimal solution,

Number of producers in Month April = 100

Number of producers in Month May = 80

Number of producers in Month June = 100

Number of Trainers in Month April = 0

Number of Trainers in Month May = 20

Number of Apprentices in Month May = 0

Number of Apprentices in Month June = 40

Number of Recruiters in Month April = 0

Number of Recruiters in Month May = 40

Minimized total wage cost = 1098000

Solver screenshot

Solver formula

C D E F G H I J N B P1T1 R1 P2 T2 A2 R2 P3 80 20 0 40 100 1098000 100 0.6 0.6 0.6 0 0.3 0.3 0.3 1 0 0.05 0.05 0.05 0.6 0.6 0.3 0.3 0.4 0.4 0.05 0.05 Objective function 20 44 74 100 0.6 0.4 1 1 4 1) 5 2) 6 3) 7 8 5) 96) 107) 118) 129) 13 10) 14 15 60 >= 116 >= 192 >= 100 = 140 >= 0 >= -2.8E-14 >= -1.4E-14 = -1.4E-14 = 0 = 0 = -1 1

Solver window

A L M N B C D E F G H I J P1 T1 R1 P2 T2 A2 R2 P3 A3 2 Objective function =3000*B3+3300*C3+2200*D3+3000*E3+3300*F3+2600*G3+2200*H3+3000*13+2600*13 3 4 1) 5 2) 6 3) 7 4) 8 5) 96) 107) 118) 100 0 0 80 20 0 40 100 40 0.6 0.3 0.05 =SUMPRODUCT(B4:14,$B$3:$J$3) >= 20 0.6 0.3 0.05 0.6 0.3 0.4 0.05 =SUMPRODUCT(B5:15,$B$3:$J$3) 0.6 0.3 0.05 0.6 0.3 0.4 0.05 0.6 0.4 =SUMPRODUCT(B6:J6,$B$3:$J$3) >= 74 1 1 =SUMPRODUCT(B7:17,$B$3:$J$3) 1 1 =SUMPRODUCT(B8:18,$B$3:$J$3) 2 -1 =SUMPRODUCT(B9:19, $B$3:$J$3) =SUMPRODUCT(B10:J10,$B$3:$J$3) >= 0 1 1 -1 -1 =SUMPRODUCT(B11:J11, $B$3:$J$3) = 0 1 1 1 =SUMPRODUCT(B12:J12,$B$3:$J$3) = 0 =SUMPRODUCT(B13:J13,$B$3:$J$3) =SUMPRODUCT(B14:J14,$B$3:$J$3) = 0

Solver Parameters Set Objective: 5053 TO: O Max Min value of: By Changing Variable Cells: $B$3:$J$3 Subject to the Constraints: $K$11:$K$14 = $M$11:$M$14 SK$4:$K$6 >= SMS4:SMS6 SK$7 = SM57 SK$8:$K$10 >= $M$8:SM$10 Add Change Delete Reset All Load/Save Make Unconstrained variables Non-Negative Select a Solving Method: Simplex LP Options Solving Method Select the GRG Nonlinear engine for Solver Problems that are smooth nonlinear. Select the LP Simplex engine for linear Solver Problems, and select the Evolutionary engine for Solver problems that are non-smooth. Help Solve close