Question

Developing a workforce schedule (using Linear Programming to model and solve this problem)

A local bank needs the minimum number of employees needed for each day of the week listed in the following table. If a staff is hired, his/her schedule will be working 5 consecutive days and take two days off. The bank operates seven days a week.

Day of the Week	M	T	W	TH	F	Sa	Su
Number of staff needed	4	5	5	3	5	2	3

So how will you help the bank hire the staffs?

Answer 1

Let,

xi = number of employees start working in day i where i = {Monday=1,Tuesday=2,Wednesday=3,Thursday=4,Friday=5,Saturday=6,Sunday=7}

Objective is to minimize number of employees = Min x1+x2+x3+x4+x5+x6+x7

Subject to,

x1+x4+x5+x6+x7 >=4 (Monday)
x1+x2+x5+x6+x7 >=5 (Tuesday)  
x1+x2+x3+x6+x7 >=5 (Wednesday)  
x1+x2+x3+x4+x7 >=3 (Thursday)  
x1+x2+x3+x4+x5 >=5 (Friday)  
x2+x3+x4+x5+x6 >=2 (Saturday)  
x3+x4+x5+x6+x7 >=3 (Sunday)

xi >= 0 (non-negativity constraint)

Solving in solver we get,

Recommendation to the bank about hiring staff

Minimum number of employee required to hire = 6  

and 3 employee should start working on Monday
1 employee should start working on Wednesday
1 employee should start working on Friday
1 employee should start working on Saturday

Solver screenshot

Solver formula  

E А В C G J К L М N 1 2 х1 x2 х3 x4 x5 хб x7 Objective function 3 0 1 0 1 1 0 6 4 5 Monday 6 Tuesday 7 Wednesd 1 1 1 1 1 5 1 1 1 1 1 5 > 5 1 1 1 1 1 5> 5 8 Thursday 9 Friday 10 Saturday 1 1 1 1 1 4= 1 1 1 1 1 5> 5 1 1 1 3 11 Sunday 1 1 1 1 1 3 12 13

А В C D E F G Н J К L M 1 2 х1 x2 х3 х4 x5 хб x7 Objective function =SUM(B3:H3) 0 1 0 1 1 0 5 Monday 6 Tuesday 7 Wednesday 8 Thursday 9 Friday 10 Saturday 11 Sunday -SUMPRODUCT(B5:H5, $B$3:$H$3) =SUMPRODUCT(B6: H6, $B$3:$H$3) -SUMPRODUCT (B7:H7,$B$3:$H$3) 1 1 1 1 1 4 1 1 1 1 1 5 1 1 1 1 1 5 =SUMPRODUCT(B8:H8,$B$3:$H$3) -SUMPRODUCT(B9:H9,$B$3:$H$3) -SUMPRODUCT (B10:H10,$B$3:$H$3)> -SUMPRODUCT(B11:H11,$B$3:$H$3) > 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 2 1 1 1 1 12 13.