FILE NAME: FLOW4
FUNCTION: Scheduling 4 or more work stations in series
OVERVIEW: With 4 or more work stations, there are no optimal scheduling rules. Scheduling is an heuristic procedure based on the priority numbers in column M. These numbers are computed by dividing the number 1 by the minimum of the following sums:
a. time on station 1 + time on station 2
b. time on station 2 + time on station 3
c. time on station 3 + time on station 4
d. time on station 4 + time on station 5
The result is a priority index. If the time on station 1 is greater than or equal to the time on the last station, the index is multiplied by -1. Otherwise the index is positive. When <CNTL> C is pressed, jobs are sorted in ascending order of priority number.
INPUT: List job names and processing times in rows 12-16.
Pressing <CNTL> A sorts by job name, <CNTL> B by random, and <CNTL> C sorts in the priority sequence.
OUTPUT: Beginning and ending hour numbers for each job are shown in rows 21-24. The Gantt chart at the bottom of the worksheet converts the hour numbers to times of day. For each sort, makespan and average flow time are reported in cells J5 and J8.
MODIFYING THE WORKSHEET:
To add jobs, insert new rows at the row for job C in each of seven sections of the worksheet: rows 12-16, 21-25, 34-38, 39-43, 44-47, 49-52, and 54-57. In each section, copy the row of formulas above the inserted rows through the last row in that section. For the first section, just copy the formulas under M..N. The other columns for job names and processing times need to be input for each new job.
You can extend the Gantt chart to show additional work days by copying range O31..O58 to the right. To modify the Gantt chart for different work schedules, edit row 31 to show a sequence of work hour numbers, interrupted by labels for lunch and other breaks, In row 32, enter a sequence of times corresponding to the work hour numbers and labels.
To reduce the number of work stations from 5 to 4, erase the cells pertaining to station 5 (K9..K14, K18..K23, A54..O58).
Edit the priority formulas in M11..M14 and delete references to station 5.
To increase the number of work stations from 5 to 6, move the range L9..N14 two columns to the right. Enter times for station 6 in M11..M14. Edit the priority formulas (now located in O11..O14) to reflect the new station, the formula for the example shown is:
IF(B11>=J11,1,-1)/MIN(B11+D11,D11+F11,F11+H11,H11+J11). 2.
WORK-STATION PRODUCTION MODELS: (cont)
FILE NAME: FLOW4.XLS
We need to edit to reflect the logic explained in the overview, which is if the time on station 1 (C12) is greater than or equal to the time on last station (last station was K12, now it is M12) the index is negative, else it is positive. The index is one divided by the minimum of the pairs of station times, so we need to add a final pair. The edited formula now reads:
IF(C12>=M12,1,-1)/MIN(C12+E12,E12+G2,G12+I12,I12+K12,K12+M12).
Once edited, just copy down through the rest of the columns, the spreadsheet will make the other changes automatically! Next, copyK19..L24 to cell M19. Finally, copy A54..O58 to cell A60.2.
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