Question

Work Scheduling: Burger Queen would like to determine the min # of kitchen employees. Each employee works six days a week and takes the seventh day off. Days Mon Tue Wed Thu Fri Sat Sun Min. # of workers 6 9 How do yu forulaic: ihis problem The objective function? The constraints?

Answer 1

Let,

Xi = number of employees NOT working on day i [ i= Mon,Tue,Wed…..Sun]

Objective is to minimize total no of workers = Min X1+X2+X3+X4+X5+X6+X7

Subject to,

X2+X3+X4+X5+X6+X7 >= 8 [ No of minimum workers needed on Monday = 8]

X1+X3+X4+X5+X6+X7 >= 6 [ No of minimum workers needed on Tuesday = 6]

X1+X2+X4+X5+X6+X7 >= 7 [ No of minimum workers needed on Wednesday = 7]

X1+X2+X3+X5+X6+X7 >= 6 [ No of minimum workers needed on Thursday = 6]

X1+X2+X3+X4+X6+X7 >= 9 [ No of minimum workers needed on Friday = 9]

X1+X2+X3+X4+X5+X7 >= 11 [ No of minimum workers needed on Saturday = 11]

X1+X2+X3+X4+X5+X6 >= 9 [ No of minimum workers needed on Sunday = 9]

Xi >= 0 (No of workers can not be less than 0)

Xi = Integer (No of workers is integer)

Solving in excel we get,

Minimum no of total employee = 11

No of employees not working on each day

Day	No of employee not working on the day
Monday	3
Tuesday	5
Wednesday	3
Thursday	0
Friday	0
Saturday	0
Sunday	0

Solver screenshot

2 Decsion variables Obiective function 3 X1 X2 X3 X4 8 (Monday) 6 (Tuesday) 7 (Wednesday) 6 (Thursday) 9 (Friday) 11 (Saturday) 9 (Sunday) 10 12 13

Formulas explained:

1. Cell H8 = Sumproduct (A4:G4,A6:G6) This formula is replicated till H12
2. Objective function Z = Cell N3 = Sum(A4:G4)

Constraint screenshot

Solver Parameters Set Objective: Max O Min Value Of By Changing Variable Cells: SA$4:$G$4 Subject to the Constraints: SAS4 integer B$4 integer $C$4-integer SD$4 - integer sES4 = integer SFS4 = integer SGS4 = integer SH$6:$H$12$J$6:$J$12 Add 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.

Answer report

13 14 Objective Cell (Min) 15 16 $N$3 17 18 19 Variable Cells Cell Name Original Value Final Value Name Original Value Final Value X1 X2 X3 X4 Cell Integer 21 SA$4 22 SB$4 23 $C$4 24 SD$A4 25 SE$A 26 $F$A 27 $G$4 28 29 30 Constraints 31 32 SH$6 33 SH$7 34 SH$8 35 SH$9 36 SHS10 37 $H$11 38 $H$12 39 ŞA$4-Integer 40 ŞB$4 Integer 41 $C$4-Integer 42 $D$4-Integer 43 ŞE$4-Integer 44 ŞF$4-Integer 45 SG$4 Integer 46 3 Integer 5 Integer 3 Integer 0 Integer 0 Integer 0 Integer 0 Integer Cell Name Cell Value Formula Status Slack 8 $H$8>-SU$8 Not Bindin 11 $H$9>-I$9 Not Bindin 11 $H$10>-SI$10 Not Bindin 11 $H$11>-SI$11 Bindin 11 $H$12>-SI$12 Not Bindin 0