FILE NAME: DAYSOFF
FUNCTION: Employee scheduling for 7-days-per-week operations.
OVERVIEW: In businesses that operate 7 days per week, DAYSOFF computes and schedules the minimum workforce necessary to give each employee 2 consecutive days off each week. The daily staff required is shown in J13.... Entries in this range are labeled initial requirements since they may have to be increased to implement a five days-on, two-days-off policy. Total requirements are 86 person-days per week (the sum of C9..I9).
Because each employee must work exactly five days per week, you need 86/5 = 17.2 employees. Therefore, you must add at least 0.8 employees or 4 person-days to initial requirements.
A macro named <CNTL> S adjusts the numbers in D14...J14 to arrive at the final staff assigned to work each day. The model is based on a procedure developed by Gail Monroe in "Scheduling Manpower for Service Operations," Industrial Engineering, August 1970. Because of its simplicity, the procedure is widely used today. While the procedure does not guarantee optimal solutions, it is much easier to use than optimizing models and always gives reasonable schedules. First, the macro locates the minimum daily staff in D13...J13. One person is added to the staff on this day. If a feasible schedule can now be computed, the macro stops. Otherwise, the macro locates the new minimum, adds another person, and keeps going until it finds a solutions.
The macro works from left to right during the week. When there are ties for the minimum, people are added as early in the week as possible. Weekend duty is not assigned unless there is no alternative.
INPUT: Enter the numbers of daily staff required in D13 ...J13 and copy to D14....J14 Enter names of employees in B25..B40 Finally, press <CNTL> S.
OUTPUT: To interpret the final solution, examine rows 13, 14, and 15 The number of people on duty each day is given in row 13, while the number of people not working on each day is given in row 14. Row 15 computes the number of people who get a particular pair of days off (MT, TW, WT, TF, FS, SS, SM). For example, cell D12 shows 15 people at work on Monday, while D13shows that 3 have Monday off. Of these three, one has Monday-Tuesday (or MT) off , while 2 get Sunday-Monday (SM) off . Each of the 18 individual work schedules is called a "line" and the Gantt chart shows the days off for each line.
MODIFYING THE WORKSHEET:
If you have less than 18 people to schedule, erase rows at the bottom of the Gantt chart until the last line number in column agrees with the final workforce size. If you have more than 18 people, copy range C42..J42 one time for each additional person.
1. Get Started
2. Workstation Production Models
3. Scheduling 2 Workstation in Series
4. Scheduling 3 Workstation in Series
5. Scheduling 4 and more Workstation in Series
6. Scheduling 2 Workstation in Parallel
7. Project Management - Daily Schedule
10. Gantt Charting