Scheduling Optimization Using Excel to slove the scheduling problem below:

Schedule a number of workers to do the warehousing jobs for each day in a week.

Each worker is required to work for 5 consecutive days. For instance, if a worker starts to work in Monday, then he/she works from Monday until Friday.

The warehouse requires a minimum number of workers to work for each day.

The objective is to MINIMIZE the total number of workers in a week. Exercise:

1) The Sheet Scheduling shows the number of workers needed for each day in a week, all possible shifts for one worker to start at one day in order to work 5 consecutive days.

2) The yellow colored region indicates the decision variables, i.e., the number of workers who start to work at each day. It is named as SchedDecVar (which is also shown in the Name Manager tool).

3) Use SUMPRODUCT function to calculate the number of workers that will work at each day and input it in region D17:J17

4) Use the SUM function to calculate the total number of workers who start to work in one day of a week, input it in the cell C22 as the objective.

5) Invoke the Solver tool to solve the problem.

6) Note that besides satisfying the number of workers needed for each day, there is another constraint enforcing the decision variable to be integer.

The Excel file:

L27 X f B C D F F G H J K L M N 0 P Monday 17 Tuesday 13 Wednesday Thursday 15 17 Friday 9 9 Saturday Sunday 9 12 Number needed Employees work 5 consecutive days Number starting Day worker starts Monday Tuesday Wednesday Thursday Monday 1 1 1 1 Tuesday 1 1 1 Wednesday 1 1 Thursday 1 1 Friday 1 1 Saturday 1 1 1 Sunday 1 1 1 1 Friday Saturday Sunday 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 . 1 Scheduling 2 3 Inputs 4 5 6 7 Decision Variables 8 and Constraints 9 10 11 12 13 14 15 16 17 18 19 20 21 Objective Function 22 23 24 25 26 27 28 29 30 31 32 Number working > > Number needed 17 13 15 17 9 9 12 Total 0 O L27 X f B C D F F G H J K L M N 0 P Monday 17 Tuesday 13 Wednesday Thursday 15 17 Friday 9 9 Saturday Sunday 9 12 Number needed Employees work 5 consecutive days Number starting Day worker starts Monday Tuesday Wednesday Thursday Monday 1 1 1 1 Tuesday 1 1 1 Wednesday 1 1 Thursday 1 1 Friday 1 1 Saturday 1 1 1 Sunday 1 1 1 1 Friday Saturday Sunday 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 . 1 Scheduling 2 3 Inputs 4 5 6 7 Decision Variables 8 and Constraints 9 10 11 12 13 14 15 16 17 18 19 20 21 Objective Function 22 23 24 25 26 27 28 29 30 31 32 Number working > > Number needed 17 13 15 17 9 9 12 Total 0 O