Introduction

Who Should Use This Tutorial

Background to Scheduling With a Spreadsheet

Build Your Own System

Interface to Your ERP System

Structured and Disciplined Approach

E-mail Us:

Web Site

Production-Scheduling.com
Thank you for downloading this free tutorial, I hope it will be of use to you.

Take a look at the model system at:

http://www.production-scheduling.com/P-S_Cycles_Flash/viewer.swf
a Flash presentation that runs for 30 minutes

Introduction
For years spreadsheets have been used to design and prototype scheduling systems.
They have now grown up, and are being used to develop serious production scheduling
applications.
Who Should Use This Tutorial
This tutorial is aimed at spreadsheet literate people who are involved in planning and scheduling
production activities. The techniques and formulas set out in this tutorial are being used by manufacturing
companies daily; this is a practical, not an academic, exercise.
Background to Scheduling With a Spreadsheet
We have been designing, building and implementing production scheduling systems for manufacturing
companies since 1990. When PC's and spreadsheets were less capable than they are now, we used
spreadsheets to design and prototype scheduling algorithms, and to train on some of the principles of
scheduling. Prototype designs were then handed over to software developers to write in more resilient and
efficient programming languages.
Often the pressure was on to throw several thousand records of data at the prototypes and use them for
live scheduling, before handing them over to the software developers. So, in order to 'shoehorn' a big
scheduling task into a small PC, we recorded macro's that wrote a formula, copied it down, overwrote the
cells with values, then moved on to the next column, so that no memory consuming live formulas were left
behind. Typically, most of the macro code prepared downloaded data for scheduling, and generated
reports from the schedule, with only a small portion of the macro calculating the schedule itself. We ended
up with big cumbersome macro driven scheduling systems that ordinary, spreadsheet literate people were
locked out of.
Thankfully we now have powerful computers which allow us to apply formulas to large amounts of data,
and we have features such as Excel's PivotTable which will re-arrange and summarise data for
scheduling, and prepare reports without resorting to writing macros. It makes the job, of building a
scheduling system with a spreadsheet, a whole lot easier, and within the capability of the average
speadsheet user.
Build Your Own System
Our offering to our clients has now changed, and we can now give them the option of either building a
scheduling system for them, or teaching their staff to build one for themselves, and providing guidence
while they do it.
Interface to Your ERP System

Our clients include companies such as Shell, Toyota and Unilever, as well as many smaller manufacturing
concerns. They all have ERP or older MRPII systems, and have felt that the functionality of their systems
need to be extended to give them the kind of responsive scheduling they are looking for. Spreadsheet
based scheduling applications have been interfaced to SAP, Baan and many other ERP systems.
Structured and Disciplined Approach
Spreadsheets have earned themselves a bad reputation amongst software purists, because they can, and
often are, used in an unstructured way. Building a scheduling system requires a structured and disciplined
approach. Please resist the trap, that many fall into, by creating a table on a single worksheet that looks
like the report that you want to see. The approach used here is to create lists in the form of databases,
with a heading at the top of each column, and with universal formulas that can be copied and pasted
down a column, and work on every row. If all the calculations are done in a structured database, then
reports, with sub-totals and charts, can easily be created with a PivotTable.
E-mail Us:
[email protected]
Web Site
www.Production-Scheduling.com

SCHEDULING WITH A SPREADSHEET

TABLE OF CONTENTS
Section
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43

Introduction
Capacity Planning vs Finite Scheduling - the essential difference
Time cascades downwards
Re-sequencing the schedule
A Simple Gantt Chart
Setting up a Calendar
Calculation of Job Stop Time Through the Calendar
Julian Dates
Setting up a Julian Calendar
Using the Calendar Formulas
Multiple Machines or Work Centres
Joining Text Together and Indirect References
Separate Calendars for each Work Centre
Repetitive Production and Setups
A Set-up Matrix
Applying the Set-up Matrix Formula
Gantt Chart 2 - Hours per Day
Gantt Chart 3 - Units per Day
Working Hours Between Two Dates (Calendar Formula 2)
Applying Calendar Formula 2
Jobs That Pass Through Multiple Work Centres
Repetitive Production that Passes Through Multiple Work Centres
Transfer Batches
Re-Using Parts of the Calendar Formulas
Repetitive Production Through Multiple Work Centres, With Calendars
A Pull Schedule
Push and Pull Schedule
Working Backwards Through a Calendar (Calendar Formula 3)
Applying Calendar Formula 3 to a Pull Schedule
Multiple Work Centre - Pull Schedule
Repetitive Production Through Multiple Work Centres - Pull Schedule
Repetitive Production, Multiple Work Centres, Pull Schedule, With Calendars
Push Pull Push - 3 Pass Logic
Multiple Work Centre - 3 Pass Schedule
Repetitive Production Through Multiple Work Centres - 3 Pass Schedule
3 Pass Schedule With a Calendar
3 Pass Schedule With Multiple Calendars
Repetitive Production, 3 Pass Schedule With Multiple Calendars
Project Scheduling
Critical Path Analysis
Make-to-Stock (Inventory) Schedule
Make-to-Stock Logic Explained
Make-to-Stock Schedule - Formulas Explained
Inventory Cover Calculation
To Come:
Material Requirements to Support a Schedule

327734556.xls

Contents

Capacity Planning vs Finite Scheduling - the essential difference

Imagine you have 5 jobs to do before this time tomorrow:
Jobs
job A
job B
job C
job D
job E

Hours
7
12
4
5
8

Total

36

You have work totalling an estimated 36 hours to complete in a day (24 hours). You have a problem.
Capacity planning tells you that you have a problem by saying that you are (3624) 150% loaded
for the day, and leaves you to resolve the problem by adding more resources or negotiating
alternative due dates.
Finite scheduling, on the other hand, recognises that the capacity of the resource is finite, and tells
you that some of the jobs are going to be late, and it can also tell you:
- which jobs are going to be late
- how late they are going to be
- are they important jobs, or for important customers
- what revised delivery dates can be promised
- how all these would change if the Jobs were undertaken in a different sequence
I would argue that compared with capacity planning, finite scheduling gives you richer management
information.

327734556.xls

Section 1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

A
B
C
Time cascades downwards

Lets add some additional columns to the list of jobs:

Jobs

Hours

Start

Stop

Due

On time

job A
job B
job C
job D
job E

7
12
4
5
8

0
7
19
23
28

7
19
23
28
36

24
24
24
24
24

1
1
1
0
0

This is now more than a list of jobs, it is a schedule.

Each formula is entered in the top cell of the column and then copied down, take a look at them:
- the first job starts at hour zero
=E7
- the next job starts when the previous one stops
=D8+C8 - the stop is the start plus the hours
- each job is due 24 hours from now
=E8<=F8 - a job is on time if it stops on or before it is due
Double click on a cell containing a formula, and the cells it refers to will be colour coded.
You can see that each job is dependant on the one before it, and time cascades downwards.
You can also see that only three of the five jobs will be on time, but lets see what happens when
we do them in a different sequence.

327734556.xls

Section 2

Re-sequencing the schedule

We will add a sequence column to the left of the table:

Seq

Jobs

Hours

Start

Stop

Due

1
2
3
4
5

job A
job C
job D
job E
job B

7
4
5
8
12

0
7
11
16
24

7
11
16
24
36

24
24
24
24
24

On time
1
1
1
1
0

If we do job B last, four out of the five jobs will be on time. The table was sorted into a different
sequence by entering the numbers 1 5 2 3 4 down the sequence column, and with the pointer in
the sequence column, clicking on the 'sort ascending' icon.

327734556.xls

Section 3

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

A
B
C
D
A Simple Gantt Chart

F G H I J K L M N O P Q R S T U V W X Y Z AAABACADAEAFAGAH AI AJAKALAMANAO

The start and stop times of each job are calculated by formulas that cascade down the columns, but as a visual aid, the
information may also be displayed as a Gantt chart. This is how you set it up:
- to the right of the schedule make narrow columns and head them from hours 1 to 36
Seq Jobs Hours Start Stop
1
2
3
4
5

job A
job C
job D
job E
job B

7
4
5
8
12

0
7
11
16
24

7
11
16
24
36

9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36

1 1 1 1 1 1 1
1 1 1 1
1 1 1 1 1
1 1 1 1 1 1 1 1
1 1 1 1 1 1 1 1 1 1 1 1

- enter this formula

=IF(AND(F$8>$D10,F$8<=$E10),1,"")
It tests the cell to see whether the hour number in the column heading is between the start and stop.
If it is, it returns a 1, if not it returns "" (a blank)
The $'s ensure that when the formula is copied it continues to reference columns D and E for the start and stop,
and row 8 for the hour number.
- set Format|Conditional Formatting|Pattern|Colour if the cell value =1, to emphasize the cell with a colour
- copy the formula in F10, and paste it to the range F10:AO14
Try changing the figures in the Hours column to see how the Gantt chart responds, or change one of the sequence numbers
and sort to re-sequence the schedule.

327734556.xls

Section 4

Setting up a Calendar
So far we have assumed that the work centre works 24 hours a day without a break, as would a continuous process such as
an oil refinery or a paper mill. However, if it is not a continuous process we need to define the working periods during which the
work centre is available. Here is an example of a calendar in hours, and decimals of an hour
(later we will examine dates and times):

Period
Number
1
2
3
4
5
6

Begin
0
8
10.25
13.5
15.75
19

End
0
10
13
15.5
18
22

Working
Hours
0
2
2.75
2
2.25
3

Working
Hours so
far (Cum)
0
2
4.75
6.75
9
12

establishes the beginning of the calendar at zero hour, midnight

work from 8:00 am to 10:00 am
a 15 min break, resume work at 10:15 am and work till 1:00 pm
30 min for lunch, then work till 3:30 pm
a 15 min break in the afternoon, then work till 6:00 pm
an hour for dinner, then work till 10:00 pm

Expressed as 12 hour clock times, the calendar looks like this:

1 12:00 AM 12:00 AM
0
0
2 8:00 AM 10:00 AM
2
2
3 10:15 AM 1:00 PM
2.75
4.75
4 1:30 PM 3:30 PM
2
6.75
5 3:45 PM 6:00 PM
2.25
9
6 7:00 PM 10:00 PM
3
12
We are counting hours into the schedule beginning at midnight.
Each line is a working period, the breaks lie in between.
In this example we begin at 8:00 AM, end at 10:00 PM, work for 12 hours, and have 2 hours of breaks.
Take a look at the formulas, they are very simple.
Now consider this problem:
If you start a 7 hour job at 8:30 AM, at what time will you stop?
You could do this:
Begin
8.5
10.25
13.5
15.75
Total

End
10
13
15.5
16.5

Duration
1.5
2.75
2
0.75
7

With a bit of trial and error you can work out that the answer is at hour 16.5 or 4:30 pm
However, a more comprehensive calculation is set out on the next worksheet.

327734556.xls

Section 5

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49

A
B
C
D
E
Calculation of Job Stop Time Through the Calendar

This gets complex, so you can skip this part if you wish and just accept that the forulae work when you apply them, or you can
persevere and understand how the formulas work. The start of the job could be within a working period, or it could fall between two
working periods. Here is the calendar again:

Period
Number
1
2
3
4
5
6

Begin

End

0
8
10.25
13.5
15.75
19

Working
Working Hours so far
Hours
(Cum)

0
10
13
15.5
18
22
Starting a job at:
Work for:

0
2
2.75
2
2.25
3

0
2
4.75
6.75
9
12
8.5
7

change these figures and work through

the stages of the calculation, to gain
an understanding of the formulas

(8:30 AM)
hours

Stages of the calculation:

8:30 AM is after period 2 begins
8:30 AM is after period 1 ends
8:30 AM lies between the beginning and end of period 2
period 2 begins at 8:00 AM
period 2 ends at 10:00 AM
you can start the job at 8:30 AM because it is within a working period
the number of hours from the start of the job to the next break
cum hours at end of period 2
the job starts at cum hour 0.5 of the calendar
the job stops at cum hour 7.5 of the calendar
the job stops during period 5
period 5 ends 9 working hours into the calendar
period 5 ends at hour 18 (6:00 PM)
the job will stop 1.5 hours before period 5 ends
the job will stop at hour 16.5 (4:30 PM)

2
1
2
8
10
8.5
1.5
2
0.5
7.5
5
9
18
1.5
16.5

=MATCH(E16,B9:B14)
=MATCH(E16,C9:C14)
=G21+(G21=G22)
=INDEX(B9:B14,G23,1)
=INDEX(C9:C14,G23,1)
=MAX(G24,E16)
=G25-G26
=INDEX(E9:E14,G23,1)
=G28-G27
=G29+E17
=MATCH(G30,E9:E14)+1
=INDEX(E9:E14,G31,1)
=INDEX(C9:C14,G31,1)
=G32-G30
=G33-G34

With a series of substitutions, the 15 formulas can be condensed into 4 as follows:

Calc1
Calc2
Calc3
Job Stop

2
0.5
5
16.5

=MATCH(E16,B9:B14)+(MATCH(E16,B9:B14)=MATCH(E16,C9:C14))
=INDEX(E9:E14,B39,1)-(INDEX(C9:C14,B39,1)-MAX(INDEX(B9:B14,B39,1),E16))
=MATCH(B40+E17,E9:E14)+1
=INDEX(C9:C14,B41,1)-INDEX(E9:E14,B41,1)+B40+E17

The meanings of the formulas are:

Calc1
Calc2
Calc3
Job Stop

8:30 AM lies between the beginning and end of period 2

the job starts at cum hour 0.5 of the calendar
the job stops during period 5
the job will stop at hour 16.5 (4:30 PM)

327734556.xls

Section 6

Julian Dates

It was Julius Caeser who first established the calendar based on 365 days per year with leap years, hence
Julian dates. One of the reasons why spreadsheets are so good for scheduling is the way that they handle
dates and times. All spreadsheets start counting time in days, and decimals of a day, from midnight before
1st January 1900, and there are enough decimal places of a day to measure time to the nearest 3
thousanth of a second!
This is the date and time according to the system clock in this computer, using the =NOW() function:
42607.1403649099 days since 1 January 1900

Keep hitting the F9 key to recalculate, and watch the clock change. Add 1 to it and you get the same time
tomorrow. The really neat thing is that we only have one unit for measuring time, a day. We dont have to
worry about seconds, minutes, hours, weeks, fortnights, months, quarters, years, decades and centuries.
However we can display the date and time in different ways with Format Cells|Number|Date and Time.
Here are some different formats of NOW:
3:22:08
3:22:08 AM
3:22 AM
8/25
8/25/16
25-Aug
25-Aug-16
August-16
August 25, 2016
A-16
and by using =TEXT:
Thu
ThursdayThursday
Aug
A08
2016
Take a look also at the date and time functions.
The spreadsheet takes care of the days in each month, and leap years etc.

327734556.xls

Section 7

Setting up a Julian Calendar

So far we have scheduled using hours, rather than days, to measure short periods of time. Somehow "I have a meeting with a client,
it should take about 2 hours" sounds better than "it should take about 0.08333 of a day". We have also pegged "zero hour" at
midnight on some arbitrary day, rather than midnight 1 January 1900. But we will need to get used to these ideas if we are going to
harness the full power of Julian dates.
Here is a calendar using Julian dates:
these two columns are just for information (note the formula - hours are days x 24)

Hours

Day

2
2.75
2
2.25
3
2
2.75
2
2.25
3
2
2.75
2
2.25
3
2
2.75
2
2.25
3
2
2.75
2
2.25
2
2.75
2
2.25
3

Mon
Mon
Mon
Mon
Mon
Tue
Tue
Tue
Tue
Tue
Wed
Wed
Wed
Wed
Wed
Thu
Thu
Thu
Thu
Thu
Fri
Fri
Fri
Fri
Mon
Mon
Mon
Mon
Mon

Begin
3/13/00 8:00 AM
3/13/00 10:15 AM
3/13/00 1:30 PM
3/13/00 3:45 PM
3/13/00 7:00 PM
3/14/00 8:00 AM
3/14/00 10:15 AM
3/14/00 1:30 PM
3/14/00 3:45 PM
3/14/00 7:00 PM
3/15/00 8:00 AM
3/15/00 10:15 AM
3/15/00 1:30 PM
3/15/00 3:45 PM
3/15/00 7:00 PM
3/16/00 8:00 AM
3/16/00 10:15 AM
3/16/00 1:30 PM
3/16/00 3:45 PM
3/16/00 7:00 PM
3/17/00 8:00 AM
3/17/00 10:15 AM
3/17/00 1:30 PM
3/17/00 3:45 PM
3/20/00 8:00 AM
3/20/00 10:15 AM
3/20/00 1:30 PM
3/20/00 3:45 PM
3/20/00 7:00 PM

End
3/13/00 10:00 AM
3/13/00 1:00 PM
3/13/00 3:30 PM
3/13/00 6:00 PM
3/13/00 10:00 PM
3/14/00 10:00 AM
3/14/00 1:00 PM
3/14/00 3:30 PM
3/14/00 6:00 PM
3/14/00 10:00 PM
3/15/00 10:00 AM
3/15/00 1:00 PM
3/15/00 3:30 PM
3/15/00 6:00 PM
3/15/00 10:00 PM
3/16/00 10:00 AM
3/16/00 1:00 PM
3/16/00 3:30 PM
3/16/00 6:00 PM
3/16/00 10:00 PM
3/17/00 10:00 AM
3/17/00 1:00 PM
3/17/00 3:30 PM
3/17/00 6:00 PM
3/20/00 10:00 AM
3/20/00 1:00 PM
3/20/00 3:30 PM
3/20/00 6:00 PM
3/20/00 10:00 PM

Cum
Days
0.08333
0.19792
0.28125
0.375
0.5
0.58333
0.69792
0.78125
0.875
1
1.08333
1.19792
1.28125
1.375
1.5
1.58333
1.69792
1.78125
1.875
2
2.08333
2.19792
2.28125
2.375
2.45833
2.57292
2.65625
2.75
2.875

Establish the first date as follows:

type =NOW() to put in the current date and time
copy, then Edit|Paste Special|Values
Format|Cells|Number|Date and choose a format that
shows both the date and time
edit the date and time
Copy, paste and edit to set up the first day
Add 1 for the other days of the week

We don't plan to work Friday evenings

Add 7 for the other weeks

A calendar for a live scheduling system may be up to several thousand rows long, so you may wish to replace the formulas with
values, after you have set it up, to save on memory and calculation time. You can allow for public holidays by deleting rows, and you
can allow for overtime by inserting rows or by extending the working periods.
After deleting or inserting rows, don't forget to copy down the formula in the Cum Days column.
A quick way of copying a formula down to the bottom of the block of data, is to select the top cell, then point to the bottom
right corner of the cell, and when the solid black cross appears, like this:
double click.

327734556.xls

Section 8

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39

A
B
C
D
Using the Calendar Formulas

The formulas for calculating the job stop time through the calendar, that we developed in section 6, works just as well on days
as it does on hours, so it can be applied to the Julian calendar like this:
Start of first job:
Seq
1
2
3
4
5

Jobs Hours Days

job A
job C
job D
job E
job B

7
4
5
8
12

3/14/00 8:30 AM
Start

0.292 3/14/00 8:30 AM

0.167 3/14/00 4:30 PM
0.208 3/14/00 9:30 PM
0.333 3/15/00 12:45 PM
0.5
3/16/00 8:30 AM

Calc1 Calc2 Calc3

7
10
11
13
17

0.521
0.812
0.979
1.187
1.521

Stop

10 3/14/00 4:30 PM
11 3/14/00 9:30 PM
13 3/15/00 12:45 PM
17 3/16/00 8:30 AM
22 3/17/00 8:30 AM

=C10/24
days are hours 24 - we need to convert to use the Julian calendar
=IF(I9,I9,E$6)
if there is a stop time of the previous job, start then, if not use "Start of first job" in E6
=MATCH(G10+D10,'Section 8'!$E$10:$E$39)+1 the calendar formulas have been explained in Section 6, but note how
the calendar on Section 8 is referenced from from this worksheet
Not only can you reference the calendar if it is on another worksheet, but it can also be in another workbook.
Try this experiment:
- open a new workbook (File|New|Workbook)
- make two windows (Window|Arrage|Horizontal)
- click on the tab of Section 8 and drag the worksheet into the new workbook
- now look at the calendar formulas, and see how they have "followed" the calendar to its new workbook
- save the new workbook as "Calendar" in another folder on your hard drive, or elsewhere on your network, and close it
- the formulas establish a link to the calendar, which includes the full path to the workbook
- you can confirm this with Edit|Links
This can be useful if you want several schedules to share the same calendar, but the calculation speed is quicker if the
calendar is in the same workbook.
As with the schedules in Section3 and 4, you can change the hours, or the start of the first job, or the sequence numbers
and sort to re-sequence, and see how the calculations respond.
If you try and start before the calendar begins, or you drop off the end of the calendar, the formulas will return errors.

327734556.xls

Section 9

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

A
B
C
D
E
Multiple Machines or Work Centres

So far we have only considered one work centre. For multiple work centres we could create a separate schedule for each,
but here is another way of doing it:
Start of first job:
W/C
1
1
1
2
2
2

Seq
1
2
3
1
2
3

3/14/00 8:30 AM

Jobs Hours Days

Start
job A
7
0.2917 3/14/00 8:30 AM
job C
4
0.1667 3/14/00 4:30 PM
job D
5
0.2083 3/14/00 9:30 PM
job E
8
0.3333 3/14/00 8:30 AM
job B
12
0.5
3/14/00 5:30 PM
job F
6
0.25
3/15/00 5:30 PM

=IF(A9=A8,J8,F$6)

Calc1
7
10
11
7
10
15

Calc2 Calc3
Stop
0.5208
10 3/14/00 4:30 PM
0.8125
11 3/14/00 9:30 PM
0.9792
13 3/15/00 12:45 PM
0.5208
10 3/14/00 5:30 PM
0.8542
15 3/15/00 5:30 PM
1.3542
18 3/16/00 10:45 AM

if the work centre is the same as the job above, then start when the previous job stops
else use the "Start of first job" in F6

In this example a job can be assigned to Work Centre 1 OR Work Centre 2, and then assigned a sequence within it.
(We shall look one job passing through multiple work centres later)
Note that the 1's and 2's in the Work Centre column are codes rather than numbers, so they have been formatted as text
Format|Cells|Number|Text.
You can now change both the work centre number and the sequence number, and sort with Data|Sort
- Sort By W/C Ascending
- Then By Seq Ascending

327734556.xls

Section 10

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38

A
B
C
D
Joining Text Together and Indirect References

In Section 12 we will use separate calendars for each work centre, but first we need to understand a couple of techniques:
Joining text together or concatenation
The "+" sign is used to add numbers together, but the "&" sign is used to join text together or join text with numbers, e.g.
John

Smith

JohnSmith

=B9&C9

this may look better with a space in between:

John

Smith

John Smith

=B13&" "&C13

Section 11

=B17&" "&C17

it also works with numbers:

Section

11

Indirect references
999
we can add text together to make something that looks like a cell reference:
B

22

B22

=B26&C26

to make it behave like a cell reference, add the =INDIRECT function

B

22

999

=INDIRECT(B30&C30)

a direct reference to a cell on another worksheet:

A Simple Gantt Chart

='Section 4'!A1

an indirect reference to the same cell:

Section 4

#REF!

=INDIRECT("'"&B38&"'!A1")

327734556.xls

Section 11

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

A
B
C
D
E
Separate Calendars for each Work Centre

The next two worksheets contain calendars, and the following table assignes a calendar to each work centre:
W/C
1
2

Calendar
Calendar 1
Calendar 2
Start of first job:

W/C
1
1
1
2
2
2

Seq
1
2
3
1
2
3

Calendar
Calendar 1
Calendar 1
Calendar 1
Calendar 2
Calendar 2
Calendar 2

Jobs
job A
job C
job D
job E
job B
job F

=VLOOKUP(A12,A$6:B$7,2)

Hours
7
4
5
8
12
6

3/14/00 8:30 AM
Days
Start
0.2917 3/14/00 8:30 AM
0.1667
#REF!
0.2083
#REF!
0.3333 3/14/00 8:30 AM
0.5
#REF!
0.25
#REF!

Calc1
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!

Calc2
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!

Calc3
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!

Stop
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!

look up the work centre in the table above, and return the contents of the 2nd column

=MATCH(I12+F12,INDIRECT("'"&C12&"'!E5:E40"))+1

an example of indirect reference to the calendars

327734556.xls

Section 12

Calendar with an Evening Shift

Hours
2
2.75
2
2.25
3
2
2.75
2
2.25
3
2
2.75
2
2.25
3
2
2.75
2
2.25
3
2
2.75
2
2.25
2
2.75
2
2.25
3
2
2.75
2
2.25
3
2
2.75
2
2.25
3
2
2.75
2
2.25
3
2
2.75
2
2.25

Day

Begin

End

Cum
Days

Mon
Mon
Mon
Mon
Mon
Tue
Tue
Tue
Tue
Tue
Wed
Wed
Wed
Wed
Wed
Thu
Thu
Thu
Thu
Thu
Fri
Fri
Fri
Fri
Mon
Mon
Mon
Mon
Mon
Tue
Tue
Tue
Tue
Tue
Wed
Wed
Wed
Wed
Wed
Thu
Thu
Thu
Thu
Thu
Fri
Fri
Fri
Fri

3/13/00 8:00 AM
3/13/00 10:15 AM
3/13/00 1:30 PM
3/13/00 3:45 PM
3/13/00 7:00 PM
3/14/00 8:00 AM
3/14/00 10:15 AM
3/14/00 1:30 PM
3/14/00 3:45 PM
3/14/00 7:00 PM
3/15/00 8:00 AM
3/15/00 10:15 AM
3/15/00 1:30 PM
3/15/00 3:45 PM
3/15/00 7:00 PM
3/16/00 8:00 AM
3/16/00 10:15 AM
3/16/00 1:30 PM
3/16/00 3:45 PM
3/16/00 7:00 PM
3/17/00 8:00 AM
3/17/00 10:15 AM
3/17/00 1:30 PM
3/17/00 3:45 PM
3/20/00 8:00 AM
3/20/00 10:15 AM
3/20/00 1:30 PM
3/20/00 3:45 PM
3/20/00 7:00 PM
3/21/00 8:00 AM
3/21/00 10:15 AM
3/21/00 1:30 PM
3/21/00 3:45 PM
3/21/00 7:00 PM
3/22/00 8:00 AM
3/22/00 10:15 AM
3/22/00 1:30 PM
3/22/00 3:45 PM
3/22/00 7:00 PM
3/23/00 8:00 AM
3/23/00 10:15 AM
3/23/00 1:30 PM
3/23/00 3:45 PM
3/23/00 7:00 PM
3/24/00 8:00 AM
3/24/00 10:15 AM
3/24/00 1:30 PM
3/24/00 3:45 PM

3/13/00 10:00 AM
3/13/00 1:00 PM
3/13/00 3:30 PM
3/13/00 6:00 PM
3/13/00 10:00 PM
3/14/00 10:00 AM
3/14/00 1:00 PM
3/14/00 3:30 PM
3/14/00 6:00 PM
3/14/00 10:00 PM
3/15/00 10:00 AM
3/15/00 1:00 PM
3/15/00 3:30 PM
3/15/00 6:00 PM
3/15/00 10:00 PM
3/16/00 10:00 AM
3/16/00 1:00 PM
3/16/00 3:30 PM
3/16/00 6:00 PM
3/16/00 10:00 PM
3/17/00 10:00 AM
3/17/00 1:00 PM
3/17/00 3:30 PM
3/17/00 6:00 PM
3/20/00 10:00 AM
3/20/00 1:00 PM
3/20/00 3:30 PM
3/20/00 6:00 PM
3/20/00 10:00 PM
3/21/00 10:00 AM
3/21/00 1:00 PM
3/21/00 3:30 PM
3/21/00 6:00 PM
3/21/00 10:00 PM
3/22/00 10:00 AM
3/22/00 1:00 PM
3/22/00 3:30 PM
3/22/00 6:00 PM
3/22/00 10:00 PM
3/23/00 10:00 AM
3/23/00 1:00 PM
3/23/00 3:30 PM
3/23/00 6:00 PM
3/23/00 10:00 PM
3/24/00 10:00 AM
3/24/00 1:00 PM
3/24/00 3:30 PM
3/24/00 6:00 PM

0.08333
0.19792
0.28125
0.375
0.5
0.58333
0.69792
0.78125
0.875
1
1.08333
1.19792
1.28125
1.375
1.5
1.58333
1.69792
1.78125
1.875
2
2.08333
2.19792
2.28125
2.375
2.45833
2.57292
2.65625
2.75
2.875
2.95833
3.07292
3.15625
3.25
3.375
3.45833
3.57292
3.65625
3.75
3.875
3.95833
4.07292
4.15625
4.25
4.375
4.45833
4.57292
4.65625
4.75

327734556.xls

Calendar 1

2
2.75
2
2.25
3
2
2.75
2
2.25
3
2
2.75
2
2.25
3
2
2.75
2
2.25
3
2
2.75
2
2.25
2
2.75
2
2.25
3
2
2.75
2
2.25
3
2
2.75
2
2.25
3
2
2.75
2
2.25
3
2
2.75
2

Mon
Mon
Mon
Mon
Mon
Tue
Tue
Tue
Tue
Tue
Wed
Wed
Wed
Wed
Wed
Thu
Thu
Thu
Thu
Thu
Fri
Fri
Fri
Fri
Mon
Mon
Mon
Mon
Mon
Tue
Tue
Tue
Tue
Tue
Wed
Wed
Wed
Wed
Wed
Thu
Thu
Thu
Thu
Thu
Fri
Fri
Fri

3/27/00 8:00 AM
3/27/00 10:15 AM
3/27/00 1:30 PM
3/27/00 3:45 PM
3/27/00 7:00 PM
3/28/00 8:00 AM
3/28/00 10:15 AM
3/28/00 1:30 PM
3/28/00 3:45 PM
3/28/00 7:00 PM
3/29/00 8:00 AM
3/29/00 10:15 AM
3/29/00 1:30 PM
3/29/00 3:45 PM
3/29/00 7:00 PM
3/30/00 8:00 AM
3/30/00 10:15 AM
3/30/00 1:30 PM
3/30/00 3:45 PM
3/30/00 7:00 PM
3/31/00 8:00 AM
3/31/00 10:15 AM
3/31/00 1:30 PM
3/31/00 3:45 PM
4/3/00 8:00 AM
4/3/00 10:15 AM
4/3/00 1:30 PM
4/3/00 3:45 PM
4/3/00 7:00 PM
4/4/00 8:00 AM
4/4/00 10:15 AM
4/4/00 1:30 PM
4/4/00 3:45 PM
4/4/00 7:00 PM
4/5/00 8:00 AM
4/5/00 10:15 AM
4/5/00 1:30 PM
4/5/00 3:45 PM
4/5/00 7:00 PM
4/6/00 8:00 AM
4/6/00 10:15 AM
4/6/00 1:30 PM
4/6/00 3:45 PM
4/6/00 7:00 PM
4/7/00 8:00 AM
4/7/00 10:15 AM
4/7/00 1:30 PM

3/27/00 10:00 AM
3/27/00 1:00 PM
3/27/00 3:30 PM
3/27/00 6:00 PM
3/27/00 10:00 PM
3/28/00 10:00 AM
3/28/00 1:00 PM
3/28/00 3:30 PM
3/28/00 6:00 PM
3/28/00 10:00 PM
3/29/00 10:00 AM
3/29/00 1:00 PM
3/29/00 3:30 PM
3/29/00 6:00 PM
3/29/00 10:00 PM
3/30/00 10:00 AM
3/30/00 1:00 PM
3/30/00 3:30 PM
3/30/00 6:00 PM
3/30/00 10:00 PM
3/31/00 10:00 AM
3/31/00 1:00 PM
3/31/00 3:30 PM
3/31/00 6:00 PM
4/3/00 10:00 AM
4/3/00 1:00 PM
4/3/00 3:30 PM
4/3/00 6:00 PM
4/3/00 10:00 PM
4/4/00 10:00 AM
4/4/00 1:00 PM
4/4/00 3:30 PM
4/4/00 6:00 PM
4/4/00 10:00 PM
4/5/00 10:00 AM
4/5/00 1:00 PM
4/5/00 3:30 PM
4/5/00 6:00 PM
4/5/00 10:00 PM
4/6/00 10:00 AM
4/6/00 1:00 PM
4/6/00 3:30 PM
4/6/00 6:00 PM
4/6/00 10:00 PM
4/7/00 10:00 AM
4/7/00 1:00 PM
4/7/00 3:30 PM

4.83333
4.94792
5.03125
5.125
5.25
5.33333
5.44792
5.53125
5.625
5.75
5.83333
5.94792
6.03125
6.125
6.25
6.33333
6.44792
6.53125
6.625
6.75
6.83333
6.94792
7.03125
7.125
7.20833
7.32292
7.40625
7.5
7.625
7.70833
7.82292
7.90625
8
8.125
8.20833
8.32292
8.40625
8.5
8.625
8.70833
8.82292
8.90625
9
9.125
9.20833
9.32292
9.40625

327734556.xls

Calendar 1

Calendar Without an Evening Shift and Early End on a Friday

Hours
2
2.75
2
2.25
2
2.75
2
2.25
2
2.75
2
2.25
2
2.75
2
2.25
2
2.75
2
1.25
2
2.75
2
2.25
2
2.75
2
2.25
2
2.75
2
2.25
2
2.75
2
2.25
2
2.75
2
1.25
2
2.75
2
2.25
2
2.75
2
2.25

Day

Begin

End

Cum
Days

Mon
Mon
Mon
Mon
Tue
Tue
Tue
Tue
Wed
Wed
Wed
Wed
Thu
Thu
Thu
Thu
Fri
Fri
Fri
Fri
Mon
Mon
Mon
Mon
Tue
Tue
Tue
Tue
Wed
Wed
Wed
Wed
Thu
Thu
Thu
Thu
Fri
Fri
Fri
Fri
Mon
Mon
Mon
Mon
Tue
Tue
Tue
Tue

3/13/00 8:00 AM
3/13/00 10:15 AM
3/13/00 1:30 PM
3/13/00 3:45 PM
3/14/00 8:00 AM
3/14/00 10:15 AM
3/14/00 1:30 PM
3/14/00 3:45 PM
3/15/00 8:00 AM
3/15/00 10:15 AM
3/15/00 1:30 PM
3/15/00 3:45 PM
3/16/00 8:00 AM
3/16/00 10:15 AM
3/16/00 1:30 PM
3/16/00 3:45 PM
3/17/00 8:00 AM
3/17/00 10:15 AM
3/17/00 1:30 PM
3/17/00 3:45 PM
3/20/00 8:00 AM
3/20/00 10:15 AM
3/20/00 1:30 PM
3/20/00 3:45 PM
3/21/00 8:00 AM
3/21/00 10:15 AM
3/21/00 1:30 PM
3/21/00 3:45 PM
3/22/00 8:00 AM
3/22/00 10:15 AM
3/22/00 1:30 PM
3/22/00 3:45 PM
3/23/00 8:00 AM
3/23/00 10:15 AM
3/23/00 1:30 PM
3/23/00 3:45 PM
3/24/00 8:00 AM
3/24/00 10:15 AM
3/24/00 1:30 PM
3/24/00 3:45 PM
3/27/00 8:00 AM
3/27/00 10:15 AM
3/27/00 1:30 PM
3/27/00 3:45 PM
3/28/00 8:00 AM
3/28/00 10:15 AM
3/28/00 1:30 PM
3/28/00 3:45 PM

3/13/00 10:00 AM
3/13/00 1:00 PM
3/13/00 3:30 PM
3/13/00 6:00 PM
3/14/00 10:00 AM
3/14/00 1:00 PM
3/14/00 3:30 PM
3/14/00 6:00 PM
3/15/00 10:00 AM
3/15/00 1:00 PM
3/15/00 3:30 PM
3/15/00 6:00 PM
3/16/00 10:00 AM
3/16/00 1:00 PM
3/16/00 3:30 PM
3/16/00 6:00 PM
3/17/00 10:00 AM
3/17/00 1:00 PM
3/17/00 3:30 PM
3/17/00 5:00 PM
3/20/00 10:00 AM
3/20/00 1:00 PM
3/20/00 3:30 PM
3/20/00 6:00 PM
3/21/00 10:00 AM
3/21/00 1:00 PM
3/21/00 3:30 PM
3/21/00 6:00 PM
3/22/00 10:00 AM
3/22/00 1:00 PM
3/22/00 3:30 PM
3/22/00 6:00 PM
3/23/00 10:00 AM
3/23/00 1:00 PM
3/23/00 3:30 PM
3/23/00 6:00 PM
3/24/00 10:00 AM
3/24/00 1:00 PM
3/24/00 3:30 PM
3/24/00 5:00 PM
3/27/00 10:00 AM
3/27/00 1:00 PM
3/27/00 3:30 PM
3/27/00 6:00 PM
3/28/00 10:00 AM
3/28/00 1:00 PM
3/28/00 3:30 PM
3/28/00 6:00 PM

0.08333
0.19792
0.28125
0.375
0.45833
0.57292
0.65625
0.75
0.83333
0.94792
1.03125
1.125
1.20833
1.32292
1.40625
1.5
1.58333
1.69792
1.78125
1.83333
1.91667
2.03125
2.11458
2.20833
2.29167
2.40625
2.48958
2.58333
2.66667
2.78125
2.86458
2.95833
3.04167
3.15625
3.23958
3.33333
3.41667
3.53125
3.61458
3.66667
3.75
3.86458
3.94792
4.04167
4.125
4.23958
4.32292
4.41667

327734556.xls

Calendar 2

2
2.75
2
2.25
2
2.75
2
2.25
2
2.75
2
1.25
2
2.75
2
2.25
2
2.75
2
2.25
2
2.75
2
2.25
2
2.75
2
2.25
2
2.75
2
1.25
2
2.75
2
2.25
2
2.75
2
2.25
2
2.75
2
2.25
2
2.75
2
2.25
2
2.75
2
1.25

Wed
Wed
Wed
Wed
Thu
Thu
Thu
Thu
Fri
Fri
Fri
Fri
Mon
Mon
Mon
Mon
Tue
Tue
Tue
Tue
Wed
Wed
Wed
Wed
Thu
Thu
Thu
Thu
Fri
Fri
Fri
Fri
Mon
Mon
Mon
Mon
Tue
Tue
Tue
Tue
Wed
Wed
Wed
Wed
Thu
Thu
Thu
Thu
Fri
Fri
Fri
Fri

3/29/00 8:00 AM
3/29/00 10:15 AM
3/29/00 1:30 PM
3/29/00 3:45 PM
3/30/00 8:00 AM
3/30/00 10:15 AM
3/30/00 1:30 PM
3/30/00 3:45 PM
3/31/00 8:00 AM
3/31/00 10:15 AM
3/31/00 1:30 PM
3/31/00 3:45 PM
4/3/00 8:00 AM
4/3/00 10:15 AM
4/3/00 1:30 PM
4/3/00 3:45 PM
4/4/00 8:00 AM
4/4/00 10:15 AM
4/4/00 1:30 PM
4/4/00 3:45 PM
4/5/00 8:00 AM
4/5/00 10:15 AM
4/5/00 1:30 PM
4/5/00 3:45 PM
4/6/00 8:00 AM
4/6/00 10:15 AM
4/6/00 1:30 PM
4/6/00 3:45 PM
4/7/00 8:00 AM
4/7/00 10:15 AM
4/7/00 1:30 PM
4/7/00 3:45 PM
4/10/00 8:00 AM
4/10/00 10:15 AM
4/10/00 1:30 PM
4/10/00 3:45 PM
4/11/00 8:00 AM
4/11/00 10:15 AM
4/11/00 1:30 PM
4/11/00 3:45 PM
4/12/00 8:00 AM
4/12/00 10:15 AM
4/12/00 1:30 PM
4/12/00 3:45 PM
4/13/00 8:00 AM
4/13/00 10:15 AM
4/13/00 1:30 PM
4/13/00 3:45 PM
4/14/00 8:00 AM
4/14/00 10:15 AM
4/14/00 1:30 PM
4/14/00 3:45 PM

3/29/00 10:00 AM
3/29/00 1:00 PM
3/29/00 3:30 PM
3/29/00 6:00 PM
3/30/00 10:00 AM
3/30/00 1:00 PM
3/30/00 3:30 PM
3/30/00 6:00 PM
3/31/00 10:00 AM
3/31/00 1:00 PM
3/31/00 3:30 PM
3/31/00 5:00 PM
4/3/00 10:00 AM
4/3/00 1:00 PM
4/3/00 3:30 PM
4/3/00 6:00 PM
4/4/00 10:00 AM
4/4/00 1:00 PM
4/4/00 3:30 PM
4/4/00 6:00 PM
4/5/00 10:00 AM
4/5/00 1:00 PM
4/5/00 3:30 PM
4/5/00 6:00 PM
4/6/00 10:00 AM
4/6/00 1:00 PM
4/6/00 3:30 PM
4/6/00 6:00 PM
4/7/00 10:00 AM
4/7/00 1:00 PM
4/7/00 3:30 PM
4/7/00 5:00 PM
4/10/00 10:00 AM
4/10/00 1:00 PM
4/10/00 3:30 PM
4/10/00 6:00 PM
4/11/00 10:00 AM
4/11/00 1:00 PM
4/11/00 3:30 PM
4/11/00 6:00 PM
4/12/00 10:00 AM
4/12/00 1:00 PM
4/12/00 3:30 PM
4/12/00 6:00 PM
4/13/00 10:00 AM
4/13/00 1:00 PM
4/13/00 3:30 PM
4/13/00 6:00 PM
4/14/00 10:00 AM
4/14/00 1:00 PM
4/14/00 3:30 PM
4/14/00 5:00 PM

4.5
4.61458
4.69792
4.79167
4.875
4.98958
5.07292
5.16667
5.25
5.36458
5.44792
5.5
5.58333
5.69792
5.78125
5.875
5.95833
6.07292
6.15625
6.25
6.33333
6.44792
6.53125
6.625
6.70833
6.82292
6.90625
7
7.08333
7.19792
7.28125
7.33333
7.41667
7.53125
7.61458
7.70833
7.79167
7.90625
7.98958
8.08333
8.16667
8.28125
8.36458
8.45833
8.54167
8.65625
8.73958
8.83333
8.91667
9.03125
9.11458
9.16667

327734556.xls

Calendar 2

1
2

A
B
C
D
Repetitive Production and Setups

18
19
20
21
22
23
24
25
26
27
28
29

Units per hour

17

So far we have accepted that a job has a duration of say, 7 hours, and we haven't worried about
how the 7 hours has been estimated. Let's say the first job is to set up a work centre and then
produce 500 of Product A. Work Centre 1 is an old machine which runs slowly, but is quick to set
up, and Work Centre 2 is a new high speed machine, but the set ups take a long time. We would
describe this in tables like this:

4
5
6

8
9
10
11
12
13
14
15
16

W/C
1
2

Set up
hours
1.25
3.00

Product
Prod A
Prod B
Prod C
Prod D

W/C 1
80
55
72
65

W/C 2
140
105
135
110

The left hand side of the schedule would look like this

W/C
1
1
1
1
2
2

Seq
1
2
3
4
1
2

Product
Prod D
Prod B
Prod A
Prod B
Prod C
Prod D

Qty
200
350
450
300
1500
1100

=VLOOKUP(A18,B$8:C$9,2)
=VLOOKUP(C18,E$8:G$11,A18+1)
=D18/F18
=E18+G18

Set up
hours
1.25
1.25
1.25
1.25
3.00
3.00

Units per
hour
65
55
80
55
135
110

Run
hours
3.08
6.36
5.63
5.45
11.11
10.00

Total
Hours
4.33
7.61
6.88
6.70
14.11
13.00

Days
0.1803
0.3172
0.2865
0.2794
0.588
0.5417

- look up the work centre, 2nd column

- look up the product, 2nd column for W/C 1, and
3rd column for W/C 2
- run hours is quantity divided by units per hour
- total hour is set up plus run

327734556.xls

Section 13

1
2

A
B
A Set-up Matrix

Often the time it takes to set up a work centre is dependant on the previous job. For example to
clean out a mixer to make white paint, after it has been used to make black paint, will take a long
time, but to change to grey paint after making white won't take as long. Similar examples exist in
printing and food processing and with the shut height of presses. In these industries the sequence
of the jobs will effect the amount of time the work centres are being set up, and therefore the
productivity of the work centres.

4
5
6
7
8

The set-up times between products, or product groups is expressed in a matrix as follows:

15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36

1.25
1.00
0.75
0.00

from
Prod A
Prod B
Prod C
Prod D

Prod D

0.50
0.75
0.00
1.50

Prod C

0.50
0.00
2.00
1.75

Prod B

Prod D

0.00
2.00
2.00
2.10

to

Prod A

Prod C

from
Prod A
Prod B
Prod C
Prod D

Prod B

10
11
12
13
14

to

Work Centre 2 - minutes

Prod A

Work Centre 1 - minutes

0.00
4.25
4.25
5.00

1.25
0.00
4.25
4.50

1.25
1.75
0.00
3.50

2.75
2.50
1.75
0.00

In Lotus 123 there is a handy function, XINDEX for referencing a value in a matrix, but in Excel we
will do it with a combination of =MATCH and =VLOOKUP. First we need to name 4 ranges with
Insert|Name|Define:
Top_row_1
Matrix_1

Top_row_2
Matrix_2

Use the name box to the left of the formula bar to see where the named ranges are.
The problem is to retrieve a set-up time given the following values
Work Centre
Previous product
This Product

1
Prod D
Prod B

name of top row

name of matrix
position of this product in top row
look up previous product in matrix

Top_row_1
Matrix_1
3
1.75

="Top_row_"&D23
="Matrix_"&D23
=MATCH(D25,INDIRECT(F27))
=VLOOKUP(D24,INDIRECT(F28),F29)

Put it all together and we get:

=VLOOKUP(D24,INDIRECT("Matrix_"&D23),MATCH(D25,INDIRECT("Top_row_"&D23)))

If we apply this formula to the first product scheduled on the work centre, then it won't work because
there isn't a previous product. We will assume that the first product scheduled on the work centre is
already running, so the set-up time is zero. We will test if it is the first product using the =IF function.

327734556.xls

Section 14

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

A
B
C
D
E
Applying the Set-up Matrix Formula

Here is the schedule from Section 13 again, but this time the set-up times are derived from the
matrixes:

W/C
1
1
1
1
2
2

Seq
1
2
3
4
1
2

Product
Prod D
Prod B
Prod A
Prod B
Prod C
Prod D
Totals:

Qty
200
350
450
300
1500
1100

Set-up
hours
0.00
1.75
2.00
0.50
0.00
1.75

Units per
hour
65
55
80
55
135
110

6.00

Run
hours
3.08
6.36
5.63
5.45
11.11
10.00

Total
Hours
3.08
8.11
7.63
5.95
11.11
11.75

Days
0.12821
0.33807
0.31771
0.24811
0.46296
0.48958

41.63

=IF(A7=A6,VLOOKUP(C6,INDIRECT("Matrix_"&A7),MATCH(C7,INDIRECT("Top_row_"&A7))),0)
Try changing the sequence numbers and sorting to see if you can reduce the total set-up hours

327734556.xls

Section 15

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50

A
B
C
E
Gantt Chart 2 - Hours per Day

79

79

88

AA

AB

In the simple Gantt chart in Section 4 each time bucket is one hour long, and the jobs fall conveniently in or out of a bucket
This Gantt chart shows daily buckets, and the number of hours in a day is determined by the calendar. The start and stop of each
job is compared with the beginning and end of each day, to determine what portion of the job falls within the day, as follows:

=IF(AND($K36>L$30,$K36<M$30),$K36-L$30,L$34)
if the job stops during the day, job stop minus day begin, else hours in the day

Job
Job

=IF(AND($J36>=L$30,$J36<M$30),M$30-$J36,A8)
if the job starts during the day, day end minus job start, else above

Job

=IF(AND($J36>=L$30,$K36<M$30),$C36,A12)
if the job starts and stops within the day, the job hours, else above

Job

9 Job

Day
Day

=IF(OR($K36<=L$30,$J36>=M$30),"",A15)
if the job stops before the day begins, or starts after the day ends, blank, else above
Job
The 4 IF statements above have been "nested" into one
=(VLOOKUP(M32,'Calendar 2'!$C$5:$E$110,3)-$J32)*24
hours into the schedule - cumulative day less 1st cumulative day, converted to hours

=INT(E32)
the midnight before the start of the first job
=VLOOKUP(I32,'Calendar 2'!C6:E110,3)
look up the cumulative days in the calendar
=L32+1 previous day plus 1
0
Start of first job:
Seq Jobs
1
2
3
4
5
6

job A
job B
job C
job D
job E
job F

Hrs
10.5
8.0
6.5
16.0
21.5
23.0

14/3 8:00 AM

14/3 12:00 AM

Start

Stop

14/3 8:00 AM
15/3 9:30 AM
16/3 8:30 AM
16/3 4:00 PM
20/3 2:45 PM
23/3 8:30 AM

15/3 9:30 AM
16/3 8:30 AM
16/3 4:00 PM
20/3 2:45 PM
23/3 8:30 AM
27/3 3:15 PM

18

27

=M30-L30
hours in the day
35

35

35

44

53

62

71

79

97 106

0.375

14/3 15/3 16/3 17/3 18/3 19/3 20/3 21/3 22/3 23/3 24/3 25/3 26/3 27/3 28/3 29/3 30/3
Tue Wed Thu Fri Sat Sun Mon Tue Wed Thu Fri Sat Sun Mon Tue Wed
Start hr Stop hr 9
9
9
8
0
0
9
9
9
9
8
0
0
9
9
9
0.0
10.5
18.5
25.0
41.0
62.5

10.5 9.0 1.5

18.5
7.5 0.5
25.0
6.5
41.0
2.0 8.0 0.0 0.0 6.0
62.5
3.0 9.0 9.0 0.5
85.5
8.5 8.0 0.0 0.0 6.5

Hours into the schedule:

=K35
start hour is the stop hour of the previous job
=J36+C36 stop hour is start hour plus job hours
Note that columns F,G and H containing the calendar formulas, have been hidden
Look at Format|Conditional Formatting to see how the shading is done
Change the hours or the sequence, then sort, to see how the Gantt chart responds
You can extend the Gantt chart by copying a column of formulas and pasting it to the right, and copying a row and pasting it downwards

327734556.xls

Section 16

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

A
B
C
D
Gantt Chart 3 - Units per Day

For repetitive production, you may wish to show the number of units that can be produced in a day, rather than the number
of hours of a job that falls into each day. The Gantt chart in Section 16 can be extended to do this, by multiplying the hours
by the units per hour. Lets take a repetitive production schedule, as in Section 15, but with one work centre:

0
Start of first job:

W/C
2
2
2
2
2

14/3 8:00 AM

Seq Product Qty

1
2
3
4
5

Prod D
Prod B
Prod A
Prod B
Prod C

650
1200
2350
300
4500

14/3 12:00 AM

Set-up Units per

hours
hour
0.00
4.50
4.25
1.25
1.75

110
105
140
105
135

Run
hours
5.91
11.43
16.79
2.86
33.33

0.375
Total
Hours
5.91
15.93
21.04
4.11
35.08

18

27

35

35

35

44

53

62

71

79

79

79

88

14/3 15/3 16/3 17/3 18/3 19/3 20/3 21/3 22/3 23/3 24/3 25/3 26/3 27/3 28/3
Tue Wed Thu Fri Sat Sun Mon Tue Wed Thu Fri Sat Sun Mon

Start
hr

Stop
hr

0.00
10.41
26.09
44.12
48.73

5.91
21.84
42.87
46.98
82.06

797

403
128 1120

650
0

0 1102
300
576 1215 1215 1080

414

The stop and start hours for each job are the hours into the schedule during which items will actually be produced, and exclude
the set-up hours.
=J13+E14
=I14+G14

start hour is the stop hour of the previous job plus the set-up hours
stop hour is start hour plus run hours

=IF(OR($J14<=K$8,$I14>=L$8),"",IF(AND($I14>=K$8,$J14<L$8),$G14,IF(AND($I14>=K$8,$I14<L$8),L$8-$I14,IF(AND($J14>K$8,$J14<L$8),$J14-K$8,K$12)))*$F14)
Same nested IF formula as in Section 16, but multiplied by Units per Hour
This report is useful as a production target to be issued at the beginning of a week, as it is easy to compare actual performance
against it. In setting a production target for a day, it takes into account:
- calendar hours each day (production is lower on a Friday)
- each product runs at a different speed
- the set-up time depends on the previous product

327734556.xls

Section 17

1
2

A
B
C
D
E
Working Hours Between Two Dates (Calendar Formula 2)

The more observant of you may have noticed a problem with the last two Gantt charts. They are fine
as long as the start of the first job coincides with beginning of the day, but if you go to Section 16 and
change the start time to, say 10:00 AM, then there are still 9 hours used on the first day, that can't be
right. If the first job starts at 10:00 AM, then the Start Hr of job A should be 2, the 2nd hour of the
schedule. However, if the first job starts at 2:00 PM, then that is 5.25 hours into the schedule because
according to Calendar 2 there is a 15 minute morning break and a 30 min lunch break in between.

4
5

What we need is a formula that takes two points in time and calculates how many working hours (days
x 24) there are between the two points. The calculation is very similar to that in Section 6:

6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40

Period
Number
1
2
3
4
5
6

Begin

End

0
8
10.25
13.5
15.75
19

0
10
13
15.5
18
22

Working
Working Hours so
Hours far (Cum)
0
2
2.75
2
2.25
3

0
2
4.75
6.75
9
12

The first 9 stages of the calculation locate the start on the calendar, and they are repeated for the stop:
Start
Stop
8.5
18.5
(8:30 AM) (6:30 PM)

Stages of the calculation:

8:30 AM is after period 2 begins, (6:30 PM is after period 5 begins)
8:30 AM is after period 1 ends, (6:30 PM is after period 5 ends)
8:30 AM (6:30 PM) lies between the beginning and end of period 2, (6)
period 2 begins at 8:00 AM, (period 6 begins at 7:00 PM)
period 2 ends at 10:00 AM, (period 6 ends at 10:00 PM)
you can start at 8:30 AM, (stop at 7:00 PM) because it's within a working period
the number of hours from the start to the next break
cum hours at end of period 2, (12)
the start is at cum hour 0.5 of the calendar, (the stop is at cum hour 9)
working hours between the Start and Stop, (9 - 0.5)

2
1
2
8
10
8.5
1.5
2
0.5

5
5
6
19
22
19
3
12
9
8.5

=MATCH(H20,B10:B15)
=MATCH(H20,C10:C15)
=H23+(H23=H24)
=INDEX(B10:B15,H25,1)
=INDEX(C10:C15,H25,1)
=MAX(H26,H20)
=H27-H28
=INDEX(E10:E15,H25,1)
=H30-H29
=H31-G31

With a series of substitutions, the 19 formulas can be condensed into 4 as follows:

Calc 4
Calc 5
Calc 6
Hours

2
0.5
6
8.5

=MATCH(G20,B10:B15)+(MATCH(G20,B10:B15)=MATCH(G20,C10:C15))
=INDEX(E10:E15,B37,1)-INDEX(C10:C15,B37,1)+MAX(INDEX(B10:B15,B37,1),G20)
=MATCH(H20,B10:B15)+(MATCH(H20,B10:B15)=MATCH(H20,C10:C15))
=INDEX(E10:E15,B39,1)-INDEX(C10:C15,B39,1)+MAX(INDEX(B10:B15,B39,1),H20)-B38

327734556.xls

Section 18

Applying Calendar Formula 2

Here is the Gantt Chart from Section 17 again, but this time it recognises that the first job may not start at the beginning of the first day.

Start of first job:

14/3 10:00 AM

14/3 12:00 AM

0.375
0

Calc 4 Calc 5 Calc 6

6
0.375
7

W/C Seq Product

2
2
2
2
2

1
2
3
4
5

Prod D
Prod B
Prod A
Prod B
Prod C

Qty
650
1200
2350
300
4500

Set-up
hours

Units
per
hour

0.00
4.50
4.25
1.25
1.75

110
105
140
105
135

18

27

35

35

35

44

53

62

71

79

79

79

88

14/3 15/3 16/3 17/3 18/3 19/3 20/3 21/3 22/3 23/3 24/3 25/3 26/3 27/3 28/3
Tue Wed Thu Fri Sat Sun Mon Tue Wed Thu Fri Sat Sun Mon
Run
Total Start
hours Hours
hr
5.91
11.43
16.79
2.86
33.33

5.91
15.93
21.04
4.11
35.08

2.00
12.41
28.09
46.12
50.73

Stop
hr
2.00
7.91
23.84
44.87
48.98
84.06

587

613

650
968

0 1260

122
300
306 1215 1215 1080

684

Working hours between 12:00 AM and 10:00 AM

327734556.xls

Section 19

1
2
3
4
5
6
7
8
9
10
11
12
13

A
B
C
D
E
F
Jobs That Pass Through Multiple Work Centres

36

So far we have considered jobs assigned to Work Centre 1 OR Work Centre 2, and now we shall look at jobs that pass through Work Centre 1 AND THEN Work Centre 2. To avoid
confusion lets have Work Centre 6 doing the 1st operation on the job, and Work Centre 7 the 2nd operation.
For the sake of simplicity our example will not refer to a calendar (the work centres operate 24 hours a day, 7 days a week) and we will not allow for set-up times.
Note the use of a 24 hour time format to fit into narrower columns.
size of time bucket in days (8 hours)
0.333 0.333 0.333 0.333 0.333 0.333 0.333 0.333 0.333 0.333 0.333 0.333
Start of first job:

14/3 8:00
14/3

14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

W/C
6
6
6
7
7
7
8
8
8

Jobs
job A
job B
job C
job A
job B
job C
job A
job B
job C

Op
1
1
1
2
2
2
3
3
3

Job/
Op
A/1
B/1
C/1
A/2
B/2
C/2
A/3
B/3
C/3

Duration
Hours
19
22
17
8
14
6
13
11
21

Previous
Op
A/0
B/0
C/0
A/1
B/1
C/1
A/2
B/2
C/2

Previous
Op Row
#N/A
#N/A
#N/A
15
16
17
18
19
20

Stop of
Previous Wait
Op
hours
Start
Stop
31/12 0:00
0
14/3 8:00 15/3 3:00
31/12 0:00
0
15/3 3:00 16/3 1:00
31/12 0:00
0
16/3 1:00 16/3 18:00
15/3 3:00 19
15/3 3:00 15/3 11:00
16/3 1:00 14
16/3 1:00 16/3 15:00
16/3 18:00
3
16/3 18:00 17/3 0:00
15/3 11:00 27
15/3 11:00 16/3 0:00
16/3 15:00 15
16/3 15:00 17/3 2:00
17/3 0:00
0
17/3 2:00 17/3 23:00

0:00

8:00 16:00
8.0
8.0

15/3

0:00
3.0
5.0
5.0

16/3

8:00 16:00
8.0

8.0

0:00

17/3

8:00 16:00

1.0
7.0

8.0

7.0

7.0

0:00

8:00 16:00 0:00

2.0

3.0
6.0
5.0

8.0
1.0

8.0

2.0
6.0

8.0

7.0

=RIGHT(B15,1)&"/"&C15
- make a unique key to each row by taking the rightmost character of the job and concatenating it with the operation e.g. job A/operation 1
=RIGHT(B15,1)&"/"&(C15-1)
- derive the previous operation by subtracting 1 from the operation number
=MATCH(F15,D$1:D$26,FALSE)
- find the row number of the previous operation by looking down column D
=IF(ISERROR(G15),1,INDEX(K$1:K$26,G15,1))- if ther is no previous op, then put day 1 (1 Jan 1900), else get the stop of the previous op from column K
=(J15-IF(A15=A14,K14,$J$12))*24
- the number of hours the work centre has to wait for the previous operation to finish, before it can start
=MAX(IF(A15=A14,K14,$J$12),H15)
- the start is later of: if its the same work centre, the stop of the previous job, else the start of the first job, and the stop of the previous operation
=J15+(E15/24)
- the stop is the start plus hours converted to days
Note the gaps while the work centre waits for the prevoius operation to finish
This is a good point to talk about "The Theory of Constraints", and if you havn't yet read "The Goal" by Eli Goldratt, then you will find it a good introduction to the subject. Production managers
who do not understand the theory of constraints, would become anxious when work centres 7 and 8 are not fully utilised. This kind of schedule will help them to understand that in order to get
the work out of the door, they should only worry about the utilisation of a work centre while it is a constraint, and in this example, they should focus their attention on work centre 6 for the first
two and a half days, and on work centre 8 for the last day and a half.

327734556.xls

Section 20

1
2
3

A
B
C
D
E
F
G
H
Repetitive Production that Passes Through Multiple Work Centres

AA

In the example in Section 20, the 1st operation of a job had to be complete before the second operation could start. However if the job is to manufacture 5000 units, then often you don't have to wait for all of them to have gone
through the 1st operation before you start the 2nd operation. When the first transfer batch (pallet load or bin full) of the product has come off the 1st work centre, it can be passed to the second work centre for the second
operation to start while the product is still going through the 1st work centre.

4
5

A similar principle will apply at the end of the production run, when the last transfer batch (or part batch) of product comes off the first operation it will join a queue at the second operation. However if the second operation is faster
(as in this example), then the first operation will stop, the transfer batch will be passed to the second operation, which will then stop after the transfer batch has been completed.

6
7
8
9
10
11
12

The rule that defines how long after the 1st operation the 2nd operation can start and stop, should be stated as the number of units in the transfer batch, but for the sake of simplicity, we will define it here as the time which the start
and stop, of the next operation, lags behind the previous operation:

0.33 0.33 0.333 0.33 0.33 0.333 0.33 0.33 0.333 0.33 0.33 0.333
The next operation can start

hour after the start of the previous operation (Lag)

Start of first job:

14/3 4:00
14/3

13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

W/C
6
6
6
7
7
7
8
8
8

Produ
ct
Op
prod A 1
prod B 1
prod C 1
prod A 2
prod B 2
prod C 2
prod A 3
prod B 3
prod C 3

Prod/
Op
A/1
B/1
C/1
A/2
B/2
C/2
A/3
B/3
C/3

Qty
1500
2200
1900
1500
2200
1900
1500
2200
1900

DurUnits per ation

Hour
Hours
65
23.1
60
36.7
75
25.3
105
14.3
95
23.2
110
17.3
80
18.8
85
25.9
95
20.0

Previous
Op
A/0
B/0
C/0
A/1
B/1
C/1
A/2
B/2
C/2

Previous
Op Row
#N/A
#N/A
#N/A
14
15
16
17
18
19

Start of
Previous
Op
30/12 0:00
30/12 0:00
30/12 0:00
14/3 5:00
15/3 4:04
16/3 16:44
14/3 6:00
15/3 5:04
16/3 17:44

Stop of
Effective
Previous Wait
Units per
Op
hours
Start
Stop
Hour
30/12 0:00 0.0
14/3 4:00 15/3 3:04
65
30/12 0:00 0.0
15/3 3:04 16/3 15:44
60
30/12 0:00 0.0
16/3 15:44 17/3 17:04
75
15/3 4:04 1.0
14/3 5:00 15/3 4:04
65
16/3 16:44 0.0
15/3 4:04 16/3 16:44
60
17/3 18:04 0.0
16/3 16:44 17/3 18:04
75
15/3 5:04 2.0
14/3 6:00 15/3 5:04
65
16/3 17:44 0.0
15/3 5:04 16/3 17:44
60
17/3 19:04 0.0
16/3 17:44 17/3 19:04
75

15/3

16/3

17/3

0:00 8:00 16:00 0:00 8:00 16:00 0:00 8:00 16:00 0:00 8:00 16:00
260 520 520 200
295 480 480 480 465
19 600 600 600
81
195 520 520 265
235 480 480 480 480
45
544 600 600 156
130 520 520 330
175 480 480 480 480 105
469 600 600 231

=IF(ISERROR(I22),0,INDEX(M$1:M$25,I22,1)+(E$11/24)- find the start of the previous operation in column M, and add 1 hour
=MAX(IF(A22=A21,N21,$M$11),J22)
- the start is later of: if its the same work centre, the stop of the previous job, else the start of the first job, and the start of the previous operation
=MAX(M22+(G22/24),K22)
- the stop is the later of: the start plus the hours, and the stop of the previous operation
=E22/(N22-M22)/24
- quantity divided by the stop minus the start
Although the second operation can start 1 hour after the first, it is faster and will quickly catch up. Work Centre 7 will then keep stopping to wait for products from the slower Work Centre 6,

327734556.xls

Section 21

1
2
3

A
B
C
Transfer Batches

AA

AB

AC

AD

AE AF

In Section 21 we assumed that the next operation starts and stops X hours after the previous operation starts and stops. In this example we use more sophisticated calculations. Each product has a different transfer batch size. The first transfer batch emerges from the previous
operation after a time determined by the units per hour of the previous operation. It then takes X minutes to transfer the batch to the next work centre, so that the next operation can start. If the quantity to be produced is not a multiple of the transfer batch size, then the last batch
will be smaller. The previous operation will stop, and the last batch will take X minutes to be transferred to the next work centre, where is will pass through the next operation after a time determined by the units per hour of the next operation.

4
5
Batch
size
80
50
100

6
7
8
9
10
11
12

prod A
prod B
prod C

0.33 0.33 0.333 0.33 0.33 0.333 0.33 0.33 0.333 0.33 0.33 0.333
Time to transfer a batch between work centres:

minutes

Start of first job:

14/3 4:00
14/3

13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

Try changing the transfer batch sizes here and see what effect is has on the stop time of the last operation.

W/C
6
6
6
7
7
7
8
8
8

Produ
ct
prod A
prod B
prod C
prod A
prod B
prod C
prod A
prod B
prod C

Op
1
1
1
2
2
2
3
3
3

Prod/O
p
A/1
B/1
C/1
A/2
B/2
C/2
A/3
B/3
C/3

Qty
1500
2200
1900
1500
2200
1900
1500
2200
1900

Units
per
Hour
65
60
75
105
95
110
80
85
95

Duration
Hours
23.1
36.7
25.3
14.3
23.2
17.3
18.8
25.9
20.0

Previous
Op
A/0
B/0
C/0
A/1
B/1
C/1
A/2
B/2
C/2

=VLOOKUP(B22,$B$7:$D$9,3)
=IF(ISERROR(I22),0,(J22/INDEX(F$1:F$25,I22,1))+(G$11/60))
=IF(MOD(E22,J22),MOD(E22,J22),J22)
=(G$11/60)+(L22/F22)
=IF(ISERROR(I22),0,INDEX(Q$1:Q$25,I22,1)+(K22/24))
=IF(ISERROR(I22),0,INDEX(R$1:R$25,I22,1)+(M22/24))

Previous
Op Row
#N/A
#N/A
#N/A
14
15
16
17
18
19

Batch
size
80
50
100
80
50
100
80
50
100

1 st
Batch
Lag
0.0
0.0
0.0
1.3
0.9
1.4
0.8
0.6
1.0

Last
Batch
Size
60
50
100
60
50
100
60
50
100

Last
Batch
Lag
1.0
0.9
1.4
0.7
0.6
1.0
0.8
0.7
1.1

Start of
Previous
Op
30/12 0:00
30/12 0:00
30/12 0:00
14/3 5:18
15/3 3:59
16/3 17:09
14/3 6:09
15/3 4:36
16/3 18:09

Stop of
Previous
Wait
Op
hours
30/12 0:00
0.0
30/12 0:00
0.0
30/12 0:00
0.0
15/3 3:43
1.3
16/3 16:21
0.3
17/3 18:04
0.8
15/3 4:33
2.2
16/3 17:01
0.0
17/3 19:12
1.1

Start
14/3 4:00
15/3 3:04
16/3 15:44
14/3 5:18
15/3 3:59
16/3 17:09
14/3 6:09
15/3 4:36
16/3 18:09

Stop
15/3 3:04
16/3 15:44
17/3 17:04
15/3 3:43
16/3 16:21
17/3 18:04
15/3 4:33
16/3 17:01
17/3 19:12

Effective
Units per
Hour
65
60
75
67
61
76
67
60
76

15/3

16/3

17/3

0:00

8:00

16:00

0:00

8:00

16:00

0:00

8:00

16:00

0:00

8:00

16:00 ##

260

520

520

200
295

480

480

480

465
19

600

600

600

81

21
522

610

610

158

62
443

607

607

243

180

123

535

536

535

536

250
242
306
205

484

483

484

483

484

483

484

483

- look up the transfer batch size for the product from the table
- if there is a previous batch, find the speed in column F, and use it to calculate the time to produce the first batch, then add the transfer time, to get the Lag in hours
- the last batch is the remainder after dividing the quantity by the batch size, if there is one, else return the batch size
- the transfer time plus the batch size divided by the units per hour, to get the Lag in hours
- the start of the previous operation plus the time of the first batch
- the stop of the previous operation, plus the time of the last batch

327734556.xls

Section 22

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34

A
B
C
D
E
Re-Using Parts of the Calendar Formulas

In the next Section we will apply the calendar formulas to the schedule in Section 21, but before we do, lets
look more closely at the formulas. You will notice that in Section 21 a duration is added to a start or stop, in
three places:
- in column J, the lag duration is added to the start of the previous operation
- in column K, the lag duration is added to the stop of the previous operation
- in column N, the operation duration is added to the start

Here is an example of the calendar formulas applied to two durations from the same start:
Hours
Days
7
0.29167
1
0.04167

Start
Calc1 Calc2 Calc3
Stop
15/3 9:30
9 0.8125
12 15/3 17:30
15/3 9:30
9 0.8125
10 15/3 10:45

Calc1 and Calc2 locate the start in the calendar and return the same values on each row, whereas Calc3
and Stop use the duration in days. So we could re-use Calc1 and Calc2 as follows:
Hours 1 Days 1
7
0.29167

Start
Calc1 Calc2 Calc3
Stop 1 Hours 2 Days 2 Calc4
15/3 9:30
9 0.8125
12 15/3 17:30
1
0.04167
10

Note how these formulas refer back to Calc2

=MATCH(E19+I19,'Calendar 2'!$E$6:$E$100)+1
=INDEX('Calendar 2'!$D$6:$D$100,J19,1)-INDEX('Calendar 2'!$E$6:$E$100,J19,1)+E19+I19

You can also re-use the formulas when applying Calendar Formulas 2 (Section 18), to calculate the
working hours between two dates, as Calc1 and Calc2 are also common to this calculation:
Hours
Days
Start
Calc1 Calc2 Calc3
Stop
7
0.29167 15/3 9:30
9 0.8125
12 15/3 17:30
1
0.04167 16/3 16:15
16 1.4271
16 16/3 17:15
Working hours between the two start dates:
14.75 =(E31-E30)*24
- the difference between the results of the two Calc2 formulas is the result in days, then multiply by 24 to
get hours

327734556.xls

Section 23

K
1
2

tion 21, but before we do, lets

3
is added to a start
or stop, in

4
5
6
7
8
9
10
11
12
13
14
15
n each row, whereas Calc3
16
lows:

17
Stop 2
18
19 15/3 10:45
20
21
22
23
24
25
26
n 18), to calculate the
is calculation: 27
28
29
30
31
32
33
days, then multiply by 24 to
34

327734556.xls

Section 23

Repetitive Production Through Multiple Work Centres, With Calendars

Here is the schedule from Section 21 with different calendars assigned to the work centres, and the calendar calculations applied in 3 places:
- the 1st calculations take the stop of the previous operation and apply the lag duration, using the calendar of the next operation
- the 2nd calculations apply the operation duration to the start
- the 3rd calculations take the start again, re-use Calc4 and Calc5, and apply the lag duration, the result is then found by the next operation in column L

W/C Calendar
6
Calendar 2
7
Calendar 1
8
Calendar 2
The next operation can start

hour after the start of the previous operation (Lag)

Start of first job:

1st calendar calculations

W/C
6
6
6
7
7
7
8
8
8

Calendar
Calendar 2
Calendar 2
Calendar 2
Calendar 1
Calendar 1
Calendar 1
Calendar 2
Calendar 2
Calendar 2

Product
prod A
prod B
prod C
prod A
prod B
prod C
prod A
prod B
prod C

Op
1
1
1
2
2
2
3
3
3

Prod/
Op
A/1
B/1
C/1
A/2
B/2
C/2
A/3
B/3
C/3

Qty
1500
2200
1900
1500
2200
1900
1500
2200
1900

Units
per
Hour
88
138
238
188
65
190
50
270
106

Duration
Hours
17.0455
15.942
7.98319
7.97872
33.8462
10
30
8.14815
17.9245

Days
0.7102
0.6643
0.3326
0.3324
1.4103
0.4167
1.25
0.3395
0.7469

Prev
Op
A/0
B/0
C/0
A/1
B/1
C/1
A/2
B/2
C/2

Prev
Op
Row
#N/A
#N/A
#N/A
19
20
21
22
23
24

Start of
Stop of
Prev Op
Stop of
Prev Op
+Lag
Prev Op Calc1 Calc2 Calc3
+Lag
30/12 0:00 30/12 0:00 #REF! #REF! #REF! 30/12 0:00
30/12 0:00 30/12 0:00 #REF! #REF! #REF! 30/12 0:00
30/12 0:00 30/12 0:00 #REF! #REF! #REF! 30/12 0:00
#REF!
#REF! #REF! #REF! #REF!
#REF!
#REF!
#REF! #REF! #REF! #REF!
#REF!
#REF!
#REF! #REF! #REF! #REF!
#REF!
#REF!
#REF! #REF! #REF! #REF!
#REF!
#REF!
#REF! #REF! #REF! #REF!
#REF!
#REF!
#REF! #REF! #REF! #REF!
#REF!

327734556.xls

14/3 6:00
3rd calendar
calculations

2nd calendar calculations

Start
14/3 6:00
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!

Calc4
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!

Calc5
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!

Calc6
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!

Stop
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!

Calc7 Start +Lag

#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!

Section 24

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

A
B
A Pull Schedule

Let's go back to the very simple schedule in Section 2. It says:

"If I start now (at hour zero), when will the last job stop?"
To turn it into a pull schedule, it should say:
"If all the jobs are required in 2 days (at hour 48), when do I need to start"
The answer , of course, is hour 12:
Jobs required at hour:

48

Jobs

Hours

Start

Stop

job A
job B
job C
job D
job E

7
5
4
12
8

12
19
24
28
40

19
24
28
40
48

=E20-C20
- the start is the stop minus the hours
=IF(ISBLANK(D21),$E$13,D21) - if there isn't a next job, stop at the required hour, else stop when
the next job starts
You can see that each job is dependant on the one after it, and time cascades upwards.
What if we now changed the question to:
"If all the jobs are required in 1 day (at hour 24), when do I need to start"
Change the "Jobs required at hour" from 48 to 24, and you will see that we would need to have started
at hour -12, half a day ago. In this case the nature of the question changes, and reverts back to the
push question:
"If I start now (at hour zero), when will the last job stop?"
What is needed is a schedule that will handle both push and pull logic, and apply the appropriate one.

327734556.xls

Section 25

I
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
equired hour, else
25 stop when
26
27
28
29
30
31

we would need to have started

32back to the
ges, and reverts

33
34
and apply the appropriate
one.
35

327734556.xls

Section 25

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

A
B
Push and Pull Schedule

Here is the push logic from Section 2 along side the pull logic from Section 25. By testing the 1st start
of the pull, it decides which of the two to apply:

Jobs required at hour:

32
Push

Pull

Apply

Jobs

Hours

Start

Stop

Start

Stop

Start

job A
job B
job C
job D
job E

7
5
4
12
8

0
7
12
16
28

7
12
16
28
36

-4
3
8
12
24

3
8
12
24
32

0
7
12
16
28

=IF(F$12<0,D12,F12)

- if the start of the first job on the pull schedule is -ve, apply the push
schedule, else apply the pull schedule.

You will notice that the push logic adds the hours to get the stop, whereas the pull logic works
backwards, and subtracts the hours to get the start. If we want to work through a calendar, then we
need a formula that will work backwards.

327734556.xls

Section 26

I
1
2
n 25. By testing the 1st start
3
4
5
6
7
8
9Apply
Stop
10
11
7
12
12
13
16
14
28
15
36
16
17
18
-ve, apply the 19
push
20
21
22

s the pull logic works

23 then we
rough a calendar,

327734556.xls

Section 26

1
2
3

A
B
C
D
E
F
Working Backwards Through a Calendar (Calendar Formula 3)

To make a pull schedule that works through a calendar, we will need a formula that works backwards, or
upwards through the calendar. Given a job stop and working hours, when will I need to start. Here is the
development of the formula from Section 6, and it can be modified by changing just one sign.

4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44

Period
Number
1
2
3
4
5
6

Begin

End

0
8
10.25
13.5
15.75
19

Working
Working Hours so far
Hours
(Cum)

0
10
13
15.5
18
22

0
2
2.75
2
2.25
3

0
2
4.75
6.75
9
12

Job stops at:

Go back:

18.5
8.5

(6:30 PM)
hours

Stages of the calculation:

6:30 PM is after period 5 begins
6:30 PM is after period 5 ends
6:30 PM lies between the beginning and end of period 6
period 6 begins at 7:00 PM
period 6 ends at 10:00 PM
you can stop the job at 7:00 PM because it is within a working period
the number of hours from the stop of the job to the next break
cum hours at end of period 12
the job stops at cum hour 9 of the calendar

5
5
6
19
22
19
3
12
9

=MATCH(E16,B9:B14)
=MATCH(E16,C9:C14)
=G21+(G21=G22)
=INDEX(B9:B14,G23,1)
=INDEX(C9:C14,G23,1)
=MAX(G24,E16)
=G25-G26
=INDEX(E9:E14,G23,1)
=G28-G27

the job starts at cum hour 0.5 of the calendar

the job starts during period 2
period 2 ends 2 working hours into the calendar
period 2 ends at hour 10 (10:00 AM)
the job will start 1.5 hours before period 2 ends
the job will start at hour 8.5 (8:30 AM)

0.5
2
2
10
1.5
8.5

=G29-E17
=MATCH(G30,E9:E14)+1
=INDEX(E9:E14,G31,1)
=INDEX(C9:C14,G31,1)
=G32-G30
=G33-G34

CHANGE THIS FROM A + TO A With a series of substitutions, the 15 formulas can be condensed into 4 as follows:
Calc1
Calc2

6
9

=MATCH(E16,B9:B14)+(MATCH(E16,B9:B14)=MATCH(E16,C9:C14))
=INDEX(E9:E14,B41,1)-(INDEX(C9:C14,B41,1)-MAX(INDEX(B9:B14,B41,1),E16))

Calc3

=MATCH(B42-E17,E9:E14)+1

Job Stop

8.5

=INDEX(C9:C14,B43,1)-INDEX(E9:E14,B43,1)+B42 -E17

327734556.xls

Section 27

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

A
B
C
D
E
Applying Calendar Formula 3 to a Pull Schedule

Here is the schedule from Section 9 again, but this time it works backwards from the stop of the last job.

Stop of last Job:

Seq

Jobs

1
2
3
4
5

job A
job C
job D
job E
job B

3/17/00 8:30 AM

Hours Days
7
4
5
8
12

Stop

0.292 3/14/00 4:30 PM

0.167 3/14/00 9:30 PM
0.208 3/15/00 12:45 PM
0.333 3/16/00 8:30 AM
0.5
3/17/00 8:30 AM

Calc1 Calc2 Calc3

10
11
13
17
22

0.812
0.979
1.187
1.521
2.021

7
10
11
13
17

Start
3/14/00 8:30 AM
3/14/00 4:30 PM
3/14/00 9:30 PM
3/15/00 12:45 PM
3/16/00 8:30 AM

=MATCH(G14-D14,'Section 8'!$E$10:$E$39)+1
=INDEX('Section 8'!$D$10:$D$39,H14,1)-INDEX('Section 8'!$E$10:$E$39,H14,1)+G14 -D14
Note that the calendar formulas are the same as the push schedule, except that the days in column D
are subracted instead of added.

327734556.xls

Section 28

J
1
2

3
ds from the stop of the last job.
4
5
6
7
8
9
10
11
12
13
14
15
16
17
9,H14,1)+G14 -D14
18
19
pt that the days in column D
20

327734556.xls

Section 28

1
2
3
4
5
6
7

A
B
C
D
E
Multiple Work Centre - Pull Schedule

19
20
21

PUSH SCHEDULE:

0.33 0.33 0.333 0.33 0.33 0.333 0.33 0.33 0.333 0.33 0.33 0.333
Start of first job:

14/3 8:00
14/3

W/C
6
6
6
7
7
7
8
8
8

Jobs
job A
job B
job C
job A
job B
job C
job A
job B
job C

Op Job/Op Hours
1
A/1
19
1
B/1
22
1
C/1
17
2
A/2
8
2
B/2
14
2
C/2
6
3
A/3
13
3
B/3
11
3
C/3
21

Previous
Op
A/0
B/0
C/0
A/1
B/1
C/1
A/2
B/2
C/2

Previous
Op Row
#N/A
#N/A
#N/A
9
10
11
12
13
14

Stop of
Previous
Wait
Op
hours
31/12 0:00
0
31/12 0:00
0
31/12 0:00
0
15/3 3:00
19
16/3 1:00
14
16/3 18:00
3
15/3 11:00
27
16/3 15:00
15
17/3 0:00
0

Start
14/3 8:00
15/3 3:00
16/3 1:00
15/3 3:00
16/3 1:00
16/3 18:00
15/3 11:00
16/3 15:00
17/3 2:00

Stop
15/3 3:00
16/3 1:00
16/3 18:00
15/3 11:00
16/3 15:00
17/3 0:00
16/3 0:00
17/3 2:00
17/3 23:00

PULL SCHEDULE:

0:00

15/3

Jobs
job A
job B
job C
job A
job B
job C
job A
job B
job C

Op Job/Op Hours
1
A/1
19
1
B/1
22
1
C/1
17
2
A/2
8
2
B/2
14
2
C/2
6
3
A/3
13
3
B/3
11
3
C/3
21

=K31-(E31/24)
=MIN(IF(A31=A32,J32,$J$20),H31)

17/3

8:00

16:00

0:00

8:00

16:00

0:00

8:00

16:00

8.0

8.0

3.0
5.0

8.0

8.0

1.0
7.0

8.0

2.0

5.0

3.0
7.0

7.0

0:00

8:00

16:00

2.0
6.0

8.0

7.0

6.0
5.0

8.0
1.0

8.0

17/3 23:00
14/3

W/C
6
6
6
7
7
7
8
8
8

16/3

0.33 0.33 0.333 0.33 0.33 0.333 0.33 0.33 0.333 0.33 0.33 0.333
Stop of last job:

22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37

To examine pull scheduling logic further we have repeated the push schedule in Section 20, and compared it with a pull version of the same schedule. The pull schedule works backwards
from the stop of the last job, and each job will be dependent on the next job on the work centre, and the next operation on the job.

8
9
10
11
12
13
14
15
16
17
18

Next Op
A/2
B/2
C/2
A/3
B/3
C/3
A/4
B/4
C/4

Next Op
Row
26
27
28
29
30
31
#N/A
#N/A
#N/A

Start of
Wait
Next Op hours
15/3 17:00
0
16/3 1:00
0
16/3 20:00
2
16/3 2:00
0
16/3 15:00
0
17/3 2:00
5
17/3 23:00
0
17/3 23:00
0
17/3 23:00
0

Start
14/3 8:00
15/3 3:00
16/3 3:00
15/3 17:00
16/3 1:00
16/3 20:00
16/3 2:00
16/3 15:00
17/3 2:00

Stop
15/3 3:00
16/3 1:00
16/3 20:00
16/3 1:00
16/3 15:00
17/3 2:00
16/3 15:00
17/3 2:00
17/3 23:00

0:00

15/3

16/3

8:00

16:00

0:00

8:00

16:00

0:00

8.0

8.0

3.0
5.0

8.0

8.0

1.0
5.0
1.0
7.0

7.0

6.0

17/3

8:00

16:00

8.0

4.0

0:00

8:00

16:00

8.0

7.0

7.0
7.0
1.0

4.0

2.0

8.0

2.0
6.0

- the start is the stop minus the hours

- the stop is the earlier of: if its the same work centre, the start of the next job, else the stop of the last job, and the start of the next operation

Although the first start and the last stop is the same on both schedules, the work centres spend less time waiting between jobs on the pull schedule.

327734556.xls

Section 29

1
2
3
4
5
6
7

A
B
C
D
E
F
G
H
Repetitive Production Through Multiple Work Centres - Pull Schedule

PUSH SCHEDULE:

AA

0.33 0.33 0.33 0.33 0.33 0.33 0.33 0.33 0.33 0.33 0.33 0.33

The next operation can start

1 hour after the start of the previous operation (Lag)

Start of first job:

14/3 4:00
14/3

W/C
6
6
6
7
7
7
8
8
8

Product
prod A
prod B
prod C
prod A
prod B
prod C
prod A
prod B
prod C

Op
1
1
1
2
2
2
3
3
3

Prod/Op
A/1
B/1
C/1
A/2
B/2
C/2
A/3
B/3
C/3

Qty
1500
2200
1900
1500
2200
1900
1500
2200
1900

Units
per
Hour
65
60
75
105
95
110
80
85
95

Hours
23.1
36.7
25.3
14.3
23.2
17.3
18.8
25.9
20.0

Previous
Op
A/0
B/0
C/0
A/1
B/1
C/1
A/2
B/2
C/2

Previous
Op Row
#N/A
#N/A
#N/A
9
10
11
12
13
14

Start of
Stop of
Previous Previous
Wait
Op +Lag
Op +Lag hours
30/12 0:00 30/12 0:00
0.0
30/12 0:00 30/12 0:00
0.0
30/12 0:00 30/12 0:00
0.0
14/3 5:00
15/3 4:04
1.0
15/3 4:04 16/3 16:44
0.0
16/3 16:44 17/3 18:04
0.0
14/3 6:00
15/3 5:04
2.0
15/3 5:04 16/3 17:44
0.0
16/3 17:44 17/3 19:04
0.0

Start
14/3 4:00
15/3 3:04
16/3 15:44
14/3 5:00
15/3 4:04
16/3 16:44
14/3 6:00
15/3 5:04
16/3 17:44

Stop
15/3 3:04
16/3 15:44
17/3 17:04
15/3 4:04
16/3 16:44
17/3 18:04
15/3 5:04
16/3 17:44
17/3 19:04

Effective
Units per
Hour
65
60
75
65
60
75
65
60
75

PULL SCHEDULE:

0:00

8:00

260 520

195 520

130 520

15/3

16:00

0:00

520 265
235 480
520 330
175 480

1 hour after the start of the previous operation (Lag)

Stop of last job:

Product
prod A
prod B
prod C
prod A
prod B
prod C
prod A
prod B
prod C

Op
1
1
1
2
2
2
3
3
3

16:00

0:00

8:00

480 480 465

19
480 480 480

480 480 480

17/3

16:00

0:00

8:00

16:00

600 600 600

81

45
544 600 600

156

105
469 600 600

231

17/3 19:04
14/3

W/C
6
6
6
7
7
7
8
8
8

8:00

520 200
295 480

16/3

0.33 0.33 0.33 0.33 0.33 0.33 0.33 0.33 0.33 0.33 0.33 0.33

The next operation can start

22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37

Here is the schedule from Section 21 which introduced the concept of repetitive production, and sequential operations being undertaken, on a product, at the same time. Again we will compare it with a pull version of the same
schedule.

8
9
10
11
12
13
14
15
16
17
18
19
20
21

Prod/Op
A/1
B/1
C/1
A/2
B/2
C/2
A/3
B/3
C/3

Qty
1500
2200
1900
1500
2200
1900
1500
2200
1900

Units
per
Hour
65
60
75
105
95
110
80
85
95

Hours
23.1
36.7
25.3
14.3
23.2
17.3
18.8
25.9
20.0

Next Op
A/2
B/2
C/2
A/3
B/3
C/3
A/4
B/4
C/4

=MIN(N31-(G31/24),J31)
=IF(A31=A32,M32,K31)

Next Op
Row
26
27
28
29
30
31
#N/A
#N/A
#N/A

Start of
Stop of
Next Op
Next Op
Wait
-Lag
-Lag
hours
15/3 0:26 15/3 19:11
0.0
15/3 19:11 16/3 21:04
0.0
16/3 21:04 17/3 17:04
0.0
15/3 1:26 15/3 20:11
0.0
15/3 20:11 16/3 22:04
0.0
16/3 22:04 17/3 18:04
0.0
17/3 19:04 17/3 19:04
0.0
17/3 19:04 17/3 19:04
0.0
17/3 19:04 17/3 19:04
0.0

Start
14/3 4:00
15/3 3:04
16/3 15:44
15/3 1:26
15/3 20:11
16/3 22:04
15/3 2:26
15/3 21:11
16/3 23:04

Stop
15/3 3:04
16/3 15:44
17/3 17:04
15/3 20:11
16/3 22:04
17/3 18:04
15/3 21:11
16/3 23:04
17/3 19:04

Effective
Units per
Hour
65
60
75
80
85
95
80
85
95

0:00

8:00

260 520

15/3

16:00

0:00

8:00

520 200
295 480
524 640

444 640

16/3

16:00

0:00

8:00

480 480 465

19
336
323 680 680
416
238 680 680

17/3

16:00

0:00

8:00

16:00

600 600 600

81

517
183 760 760

197

602
88 760 760

292

- the start is: the earlier of the stop minus the hours, and one hour before the start of the next operation
- the stop is: the start of the next job on the work centre, if there is one, else the stop of the next operation on the job.

Again the entire schedule stops and starts at the same times, but the work centres don't start until they need to, and the effective run rates, in units per hour, are higher.

327734556.xls

Section 30

Repetitive Production, Multiple Work Centres, Pull Schedule, With Calendars

In Sectin 24 we took a push schedule and applied calendar calculations to it. Here is the pull schedule from Section 30 with different calendars assigned to the work centres, and the calendar calculations applied in 3 places:
- the 1st calculations take the start of the next operation and subtract the lag duration, using the calendar of the previous operation
- the 2nd calculations apply the operation duration to the stop, to derive the start
- the 3rd calculations take the stop again, re-use Calc4 and Calc5, and subtract the lag duration, the result is then found by the previous operation in column L

W/C
6
7
8

Calendar
Calendar 2
Calendar 1
Calendar 2

The next operation can start

hour after the start of the previous operation (Lag)

Stop of last job:

1st calendar calculations

W/C
6
6
6
7
7
7
8
8
8

Calendar
Calendar 2
Calendar 2
Calendar 2
Calendar 1
Calendar 1
Calendar 1
Calendar 2
Calendar 2
Calendar 2

Product
prod A
prod B
prod C
prod A
prod B
prod C
prod A
prod B
prod C

Op
1
1
1
2
2
2
3
3
3

Prod/O
p
A/1
B/1
C/1
A/2
B/2
C/2
A/3
B/3
C/3

Qty
1500
2200
1900
1500
2200
1900
1500
2200
1900

Units
per
Hour
65
60
75
105
95
110
80
85
95

Duration
Hours
23.1
36.7
25.3
14.3
23.2
17.3
18.8
25.9
20.0

Days
0.96154
1.52778
1.05556
0.59524
0.96491
0.7197
0.78125
1.07843
0.83333

Next Op Stop of Next

Start of
Op
Next Op
Next Op Row
A/2
22
#REF!
#REF!
B/2
23
#REF!
#REF!
C/2
24
#REF!
#REF!
A/3
25
#REF!
#REF!
B/3
26
#REF!
#REF!
C/3
27
#REF!
#REF!
A/4
#N/A
27/3 19:04 27/3 19:04
B/4
#N/A
27/3 19:04 27/3 19:04
C/4
#N/A
27/3 19:04 27/3 19:04

327734556.xls

Calc1
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!

Calc2
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!

Calc3
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!

27/3 19:04
3rd calendar
calculations

2nd calendar calculations

Start of
Next Op
-Lag
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!

Stop
Calc4
Calc5
Calc6
#REF! #REF! #REF! #REF!
#REF! #REF! #REF! #REF!
#REF! #REF! #REF! #REF!
#REF! #REF! #REF! #REF!
#REF! #REF! #REF! #REF!
#REF! #REF! #REF! #REF!
#REF! #REF! #REF! #REF!
#REF! #REF! #REF! #REF!
27/3 19:04 #REF! #REF! #REF!

Start
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!

Calc7 Stop -Lag

#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!

Section 31

1
2

4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49

A
B
C
D
E
Push Pull Push - 3 Pass Logic

In Section 26 we had a series of jobs that were all required at the same time, but here we consider jobs
with different required times. We will start with a simple example of Job A required in 24 hours, and
Job B required 48 hours from now. The objective is to try and meet the due date of each job, but to
start each job just in time. The logic works in 3 passes, and "shuffles" the jobs taking into consideration
the following:
- the start of the schedule (hour zero)
- the length of the job in hours
- when the job is due
- the stop of the previous job
- the start of the next job

Push 1
Pull
Push 2
Jobs Hours Due Start Stop Start Stop Start Stop

Day 1
0

16

Day 2
24

32

40

48

56

In the first pass job A starts now, and job B starts immediately after that:
job A
job B

32
8

24
48

0
32

32
40

1
1

In the second pass each job stops when it is due, so job A would need to have started 8 hours ago:
job A
job B

32
8

24
48

0
32

32
40

-8
40

24
48

1
1

The third pass pushes job A later so that it starts at hour zero:
job A
job B

32
8

24
48

0
32

32
40

-8
40

24
48

0
40

32
48

1
1

Here are some other examples:

job A
job B

32
24

24
48

0
32

32
56

-8
24

24
48

0
32

32
56

job A
job B

16
16

24
48

0
16

16
32

8
32

24
48

8
32

24
48

job A
job B

8
32

24
48

0
8

8
40

8
16

16
48

8
16

16
48

job A
job B

16
40

24
48

0
16

16
56

-8
8

8
48

0
16

16
56

=MIN(C34,F35)
=MAX(D34,F34,I33)

=H34+B34

1
1

1
1

- pull stop is the earlier of the due time or the start of the next job
- push 2 start is the later of:
the earliest start
the push start
the stop of the previous job
- push 2 stop is the start plus hours

327734556.xls

Section 32

1
2
3

A
B
C
D
E
F
Multiple Work Centre - 3 Pass Schedule

26
27
28
29
30
31
32
33

Z AA AB AC AD AE AF

AG

AH

- the start of the schedule (hour zero)

- the length of the job in hours
- when the job is due
- the stop of the previous job on the work centre
- the start of the next job on the work centre
- the stop of the previous operation on the job
- the start of the next next operation on the job
Push 1

14

25

Here we apply the 3 pass logic set out in Section 31 to jobs that pass through multiple work centres. The logic that applies to the Push 1 part of the schedule is explained in Section 20. The Pull part is in Section 29,
but has been modified to cope with Due time. This example uses hurs rather than Julian dates, simply because we can make the columns narrower this way. The Push 2 logic is the same as in Section 32, except that
the start is subject to an additional constraint, the stop of the previous operation. So now the scheduling of a job takes 7 things into consideration:

4
5
6
7
8
9
10
11
12
13

15
16
17
18
19
20
21
22
23
24

W/C
6
6
6
7
7
7
8
8
8

Jobs
job A
job B
job C
job A
job B
job C
job A
job B
job C

Op
1
1
1
2
2
2
3
3
3

Job/O
p
Hours
A/1
16
B/1
16
C/1
8
A/2
8
B/2
32
C/2
8
A/3
16
B/3
8
C/3
16

Due

24
72
96

Prev.
Op
A/0
B/0
C/0
A/1
B/1
C/1
A/2
B/2
C/2

Prev.
Op
Row
#N/A
#N/A
#N/A
15
16
17
18
19
20

Stop of
Prev. Op
0
0
0
16
32
40
24
64
72

Pull

Start Stop
0
16
16
32
32
40
16
24
32
64
64
72
24
40
64
72
72
88

Next Next Op Start of

Op
Row
Next Op
A/2
18
0
B/2
19
32
C/2
20
72
A/3
21
8
B/3
22
64
C/3
23
80
A/4
#N/A
24
B/4
#N/A
72
C/4
#N/A
96

Push 2

Start
-16
16
64
0
32
72
8
64
80

Stop
0
32
72
8
64
80
24
72
96

Stop of
Prev. Op
0
0
0
16
32
72
24
64
80

Start
0
16
64
16
32
72
24
64
80

Day 1

Stop
16
32
72
24
64
80
40
72
96

Day 2

Day 3

Day 4

8 16 24 32 40 48 56 64 72 80 88 96
1 1
1 1
1
1
1 1 1 1
1
1 1
1
1 1

These are the formulas that are different:

=IF(ISERROR(M23),F23,INDEX(O$1:O$28,M23,1))
=MIN(IF(A23=A24,O24,999),N23)
=MAX(J23,O23,Q23,IF(A23=A22,S22,0))

- if there isn't a next operation put in the time due, else find the start of the next op in coumn O
- the stop is the earlier of: the start of the next job on the work centre if there is one, else the start of the next op (or the time due)
- the start is the later of: earliest (push 1) start
latest (pull) start
stop of the previous operation
stop of the previous job on the work centre, if there is one

Try changing the hours and due figures, and see how the schedule behaves.

327734556.xls

Section 33

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

A
B
C
D
E
F
G
H
I
J
Repetitive Production Through Multiple Work Centres - 3 Pass Schedule

AA

AB

AC

AD

AE

AF

AG

AH

AI

AJ

AK AL

8
Day 4

###
8

Applying the 3 pass logic to this case becomes more complex still. The Push 1 logic is explained in Section 21, and the pull logic from Section 30 has been modified to cope with the time Due. The list of things taken into consideration when scheduling a job now grows to 10:
- the start of the schedule (hour zero)
- the length of the job in hours
- when the job is due
- the stop of the previous job on the work centre
- the start of the next job on the work centre
- the 1 hour that the next operation lags behind the previous operation
- the start of the previous operation on the job
- the stop of the previous operation on the job
- the start of the next next operation on the job
- the stop of the next next operation on the job
The next operation can start

18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

W/C
6
6
6
7
7
7
8
8
8

Product
prod A
prod B
prod C
prod A
prod B
prod C
prod A
prod B
prod C

Op
1
1
1
2
2
2
3
3
3

Prod/
Op
A/1
B/1
C/1
A/2
B/2
C/2
A/3
B/3
C/3

Qty
1500
2200
1900
1500
2200
1900
1500
2200
1900

Due

24
72
96

hour after the start of the previous operation (Lag)

Push 1
Units
per
Hour
88.2
137.5
237.5
187.5
64.7
190.0
50.0
275.0
105.6

Hours
17
16
8
8
34
10
30
8
18

Prev
Op
A/0
B/0
C/0
A/1
B/1
C/1
A/2
B/2
C/2

Prev
Op
Row
#N/A
#N/A
#N/A
19
20
21
22
23
24

Start of
Stop of
Next
Prev Op Prev Op Start Stop
Op
-1
-1
0
17 A/2
-1
-1
17
33 B/2
-1
-1
33
41 C/2
1
18
1
18 A/3
18
34
18
52 B/3
34
42
52
62 C/3
2
19
2
32 A/4
19
53
32
53 B/4
53
63
53
71 C/4

=IF(ISERROR(J27),-F$16,INDEX(X$1:X$30,J27,1)+F$16)
=MAX(M27,S27,U27,IF(A27=A26,X26,0))

=MAX(W27+H27,V27)

8
Pull
Next
Op
Row
22
23
24
25
26
27
#N/A
#N/A
#N/A

Push 2

Start of Stop of
Start of
Stop of
Next Op Next Op Start Stop Prev Op Prev Op Start Stop
-8
22
-8
22
-1
-1
0
17
36
70
36
70
-1
-1
36
52
76
94
76
94
-1
-1
76
84
-7
23
-7
23
1
18
1
18
63
71
37
71
37
53
37
71
77
95
77
95
77
85
77
87
24
24
-6
24
2
19
2
32
72
72
64
72
38
72
64
72
96
96
78
96
78
88
78
96

706

16
88

Day 1
Effective
Units per
Hour
88.2
137.5
237.5
88.2
64.7
190.0
50.0
275.0
105.6

0
706

618

300

706

400

24

8
Day 2

32

40

48

550

1100

550

194

518

518

8
Day 3

56

64

72

80

950

950

570

1330

211

844

88 ##

176

400

518

453

400
2200
844

- if there isn't a previous operation return 1 hour before the start of the schedule, else find the stop of the previous operation in column X
- the start is the later of:
earliest (push 1) start
latest (pull) start
the start of the previous operation (plus 1 hour)
stop of the previous job on the work centre, if there is one
- the stop is the later of, the start plus the hours, and the stop of the previous operation (plus 1 hour)

327734556.xls

Section 34

1
2

A
B
C
D
E
3 Pass Schedule With a Calendar

9
10
11
12
13
14
15
16
17
18
19
20

All the calculations in the 3 pass schedules in Sections 33 and 34 have been done on the basis of
hours into the schedule. If all work centres work 24 hours a day, 7 days a week, then it would be
straightforward to convert the start and stop times to Julian dates. However, if the work centres
follow a calendar, then we need to apply the calendar formulas. We start with the start and stop from
push 2 of Section 34 (columns W and X), and apply the formulas set out in Section 10.

4
5
6
7
8

Start of first job:

W/C
6
6
6
7
7
7
8
8
8

Product
prod A
prod B
prod C
prod A
prod B
prod C
prod A
prod B
prod C

=(D17-C17)/24

3/14/00 8:30 AM

Start Stop
Start Date
Calc1 Calc2
hour hour Days
0
17 0.71 3/14/00 8:30 AM
5
1.10
36
52 0.67 3/15/00 5:30 PM
12
1.77
76
84 0.33 3/17/00 3:15 PM
19
2.10
1
18 0.71 3/14/00 9:30 AM
5
1.15
37
71 1.42 3/16/00 8:30 AM
13
2.56
77
87 0.42 3/21/00 5:30 PM
28
2.98
2
32 1.25 3/14/00 10:30 AM
6
1.72
64
72 0.33 3/17/00 2:00 PM
19
2.05
78
96 0.75 3/20/00 2:00 PM
23
2.80

Calc3
12.00
19.00
23.00
13.00
28.00
33.00
19.00
23.00
31.00

Stop
3/15/00 5:30 PM
3/17/00 3:15 PM
3/20/00 3:15 PM
3/16/00 8:30 AM
3/21/00 5:30 PM
3/23/00 8:30 AM
3/17/00 2:00 PM
3/20/00 2:00 PM
3/22/00 2:00 PM

- the duration of the operation is re-established from the stop and start
hours, as it is not always the same as the hours (Section 30, column H)

327734556.xls

Section 35

3 Pass Schedule With Multiple Calendars

Each work centre may adhere to a different calendar, as would be the case if the constraint work centre was scheduled to work overtime or weekends. In this case, 3 pass scheduling, using hours into the schedule, won't work because
for example, hour 17 on Work Centre 6 will translate into 3/15/00 5:30 PM according to Calendar 2, but if Work Centre 7 works to Calendar 1, hour 17 may translate into a completely different date and time. Every instance were a
duration is added or subtracted from a date and time, to get to another date and time, will have to pass through the calendar formulas. That's an awful lot of formulas, but lets start by adding the calendar formulas to the schedule in
Section 33:
W/C
6
7
8

Calendar
Calendar 2
Calendar 1
Calendar 2
Start of first job:

14/3 6:00
Push 1

W/C
6
6
6
7
7
7
8
8
8

Calendar
Calendar 2
Calendar 2
Calendar 2
Calendar 1
Calendar 1
Calendar 1
Calendar 2
Calendar 2
Calendar 2

Jobs
job A
job B
job C
job A
job B
job C
job A
job B
job C

Prev. Prev. Op
Op Job/Op Hours Days
Due
Op
Row
1
A/1
6
0.25
A/0
#N/A
1
B/1
12
0.5
B/0
#N/A
1
C/1
8
0.3333
C/0
#N/A
2
A/2
8
0.3333
A/1
15
2
B/2
20 0.8333
B/1
16
2
C/2
8
0.3333
C/1
17
3
A/3
16 0.6667 20/3 11:00 A/2
18
3
B/3
8
0.3333 21/3 15:00 B/2
19
3
C/3
16 0.6667 22/3 8:00 C/2
20

Stop of
Prev. Op
30/12 0:00
30/12 0:00
30/12 0:00
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!

Start
Calc1 Calc2 Calc3
14/3 6:00
### #REF!
###
#REF!
### #REF!
###
#REF!
### #REF!
###
#REF!
### #REF!
###
#REF!
### #REF!
###
#REF!
### #REF!
###
#REF!
### #REF!
###
#REF!
### #REF!
###
#REF!
### #REF!
###

Pull
Stop
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!

Next Next Op
Op
Row
A/2
18
B/2
19
C/2
20
A/3
21
B/3
22
C/3
23
A/4
#N/A
B/4
#N/A
C/4
#N/A

327734556.xls

Start of
Next Op
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
20/3 11:00
21/3 15:00
22/3 8:00

Stop
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
22/3 8:00

Push 2
Calc4
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!

Calc5
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!

Calc6
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!

Start
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!

Stop of
Prev. Op
30/12 0:00
30/12 0:00
30/12 0:00
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!

Start
Calc7 Calc8 Calc9
#REF!
###
###
###
#REF!
###
###
###
#REF!
###
###
###
#REF!
###
###
###
#REF!
###
###
###
#REF!
###
###
###
#REF!
###
###
###
#REF!
###
###
###
#REF!
###
###
###

Stop
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!

Section 36

Repetitive Production, 3 Pass Schedule With Multiple Calendars

Here we have taken the schedule in Section 34, and applied calendar calculations to it. You can see that it takes 53 columns to do it! However, we have built up to this in stages, In Section 24 we added the calendar formulas to a push schedule, in Section 31 to
a pull schedule, and here we have put it all together.

W/C Calendar
6
Calendar 2
7
Calendar 1
8
Calendar 2
The next operation can start

hour after the start of the previous operation (Lag)

Start of first job:

14/3 6:00
Push 1

W/C
6
6
6
7
7
7
8
8
8

Calendar
Calendar 2
Calendar 2
Calendar 2
Calendar 1
Calendar 1
Calendar 1
Calendar 2
Calendar 2
Calendar 2

Product
prod A
prod B
prod C
prod A
prod B
prod C
prod A
prod B
prod C

Op
1
1
1
2
2
2
3
3
3

Prod/
Op
A/1
B/1
C/1
A/2
B/2
C/2
A/3
B/3
C/3

Qty
1500
2200
1900
1500
2200
1900
1500
2200
1900

DurUnits per ation

Hour
Hours
88
17.0455
138
15.942
238
7.98319
188
7.97872
65
33.8462
190
10
50
30
270
8.14815
106
17.9245

Days
0.7102
0.6643
0.3326
0.3324
1.4103
0.4167
1.25
0.3395
0.7469

Prev
Op
A/0
B/0
C/0
A/1
B/1
C/1
21/3 11:00 A/2
22/3 15:00 B/2
23/3 8:00 C/2
Due

Prev
Op
Row
#N/A
#N/A
#N/A
19
20
21
22
23
24

Start of
Prev Op
+Lag
30/12 0:00
30/12 0:00
30/12 0:00
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!

Stop of
Prev Op
Calc1
30/12 0:00 #REF!
30/12 0:00 #REF!
30/12 0:00 #REF!
#REF! #REF!
#REF! #REF!
#REF! #REF!
#REF! #REF!
#REF! #REF!
#REF! #REF!

Calc2
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!

327734556.xls

Calc3
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!

Stop of Prev
Op +Lag
30/12 0:00
30/12 0:00
30/12 0:00
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!

Start
14/3 6:00
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!

Calc4
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!

Calc5
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!

Calc6
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!

Stop
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!

Calc7
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!

Start +Lag
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!

Section 37

Pull
Next
Op
A/2
B/2
C/2
A/3
B/3
C/3
A/4
B/4
C/4

Next
Op Stop of Next
Row
Op -Lag
22
#REF!
23
#REF!
24
#REF!
25
#REF!
26
#REF!
27
#REF!
#N/A
21/3 11:00
#N/A
22/3 15:00
#N/A
23/3 8:00

Start of
Next Op
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
21/3 11:00
22/3 15:00
23/3 8:00

Calc8
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!

Calc9 Calc10
#REF! #REF!
#REF! #REF!
#REF! #REF!
#REF! #REF!
#REF! #REF!
#REF! #REF!
#REF! #REF!
#REF! #REF!
#REF! #REF!

Start of
Next Op
-Lag
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!

Push 2

Stop
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
23/3 8:00

Calc11 Calc12 Calc13

#REF! #REF! #REF!
#REF! #REF! #REF!
#REF! #REF! #REF!
#REF! #REF! #REF!
#REF! #REF! #REF!
#REF! #REF! #REF!
#REF! #REF! #REF!
#REF! #REF! #REF!
#REF! #REF! #REF!

Start
Calc14
#REF! #REF!
#REF! #REF!
#REF! #REF!
#REF! #REF!
#REF! #REF!
#REF! #REF!
#REF! #REF!
#REF! #REF!
#REF! #REF!

Stop -Lag
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!

327734556.xls

Start of
Prev Op
+Lag
30/12 0:00
30/12 0:00
30/12 0:00
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!

Stop of
Prev Op
Calc15 Calc16 Calc17
30/12 0:00 #REF! #REF! #REF!
30/12 0:00 #REF! #REF! #REF!
30/12 0:00 #REF! #REF! #REF!
#REF! #REF! #REF! #REF!
#REF! #REF! #REF! #REF!
#REF! #REF! #REF! #REF!
#REF! #REF! #REF! #REF!
#REF! #REF! #REF! #REF!
#REF! #REF! #REF! #REF!

Stop of
Prev Op
+Lag
30/12 0:00
30/12 0:00
30/12 0:00
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!

Start
Calc18 Calc19 Calc20
#REF! #REF! #REF! #REF!
#REF! #REF! #REF! #REF!
#REF! #REF! #REF! #REF!
#REF! #REF! #REF! #REF!
#REF! #REF! #REF! #REF!
#REF! #REF! #REF! #REF!
#REF! #REF! #REF! #REF!
#REF! #REF! #REF! #REF!
#REF! #REF! #REF! #REF!

Stop
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!

Section 37

Calc21 Start +Lag

#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!

327734556.xls

Section 37

1
2
3

A
Project Scheduling

So far our examples have related to production activities, where a job's dependancies are implied, such
as the previous job on a work centre, or a previous operation on a job. With a project the pattern of
dependancies have to be spelled out. Here is an example:

4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38

Activity
wake up
walk to kitchen
fill kettle
boil water
put bread in toaster
toast bread
walk to bathroom
turn on bath taps
fill bath
brush teeth
shave
remove night clothes
get into bath
wash body
shampoo hair
get dried
put on underwear
put on shirt and trousers
put on tie
walk to kitchen
lay table
make tea
spread toast
eat breakfast

Duration Dependsecs.
ency 1
30
15
6
20
7
180
8
20
8
150
10
15
10
10
12
180
13
60
12
80
15
10
16
5
17
90
18
40
19
30
20
10
21
30
22
30
23
15
23
60
24
90
26
60
27
300
27

Total Duration:

=IF(C28,INDIRECT("H"&C28),0)
=MAX(E28,F28)
=G28+B28

0:17:15

Dependency 2

14

25
9
11
28

Dep.
Dep.
Stop 1 Stop 2
0
0
30
0
45
0
65
0
65
0
85
0
85
0
100
0
110
0
100
0
160
0
240
0
250
290
295
0
385
0
425
0
455
0
465
0
495
0
495
0
525
510
585
245
675
235
675
735

Start
0
30
45
65
65
85
85
100
110
100
160
240
290
295
385
425
455
465
495
495
525
585
675
735

h:mm:ss

- find the time that the dependency stops, at its row number
- the activity starts at the later of the two dependencies
- the stop is the start plus the duration

Please excuse the fact that this example is not politically correct and gender neutral. Please note
however, that you brush your teeth whilst turning on the bath taps, but you shouln't have bothered
because you stand there naked waiting for the bath to fill. You also put on your tie while walking to the
kitchen, which saves you 15 seconds.

327734556.xls

Section 38

H
1
2

endancies are implied, such

3
a project the pattern
of
4
5

Stop
30
45
65
245
85
235
100
110
290
160
240
250
295
385
425
455
465
495
525
510
585
675
735
1035

6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
y stops, at its row
34 number
e two dependencies
35
36
37

neutral. Please note

houln't have bothered
38
our tie while walking to the

327734556.xls

Section 38

1
2
3

A
B
Critical Path Analysis

The shaded activities, below, are on the critical path. An increase in duration of these would extend the entire project, whereas non-critical activities have some "float".
Push logic calculates the earliest start of each activity, and the end time of the project, then working back from this, pull logic calculates the latest start. The first step is
to determine for each activity, the next activities that are dependent on it.

4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38

Row
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

Activity
wake up
walk to kitchen
fill kettle
boil water
put bread in toaster
toast bread
walk to bathroom
turn on bath taps
fill bath
brush teeth
shave
remove night clothes
get into bath
wash body
shampoo hair
get dried
put on underwear
put on shirt and trousers
put on tie
walk to kitchen
lay table
make tea
spread toast
eat breakfast

Dur.
Dep.
Secs Dep 1 Dep 2 Stop 1
30
0
15
6
30
20
7
45
180
8
65
20
8
65
150
10
85
15
10
85
10
12
100
180
13
110
60
12
100
80
15
160
10
16
240
5
17
14
250
90
18
295
40
19
385
30
20
425
10
21
455
30
22
465
30
23
495
15
23
495
60
24
25
525
90
26
9
585
60
27
11
675
300
27
28
675

=MATCH(A29,D$1:D$40,FALSE)
=MATCH(A29,INDIRECT("D"&J29+1&":D40"),FALSE)+J29
=MATCH(A29,E$1:E$40,FALSE)
=IF(ISERROR(J29),$I$29,INDIRECT("Q"&J29))
=MIN(M29:O29)
=P29-C29
=Q29-H29

Dep.
Stop 2
0
0
0
0
0
0
0
0
0
0
0
0
290
0
0
0
0
0
0
0
510
245
235
735

Push Push
Next Next Next Pull Pull
Start Stop Next 1 Next 2 Next 3 start 1 start 2 start 3 Stop Start Float
0
30
7 #N/A #N/A
30 1035 1035
30
0
0
30
45
8 #N/A #N/A
45 1035 1035
45
30
0
45
65
9
10 #N/A
405
65 1035
65
45
0
65
245 #N/A #N/A
27 1035 1035
585 585 405 340
65
85
11
12 #N/A
525
85 1035
85
65
0
85
235 #N/A #N/A
28 1035 1035
675 675 525 440
85
100
13
15 #N/A
100
140 1035 100
85
0
100
110
14 #N/A #N/A
110 1035 1035 110 100
0
110
290 #N/A #N/A
18 1035 1035
290 290 110
0
100
160
16 #N/A #N/A
200 1035 1035 200 140
40
160
240
17 #N/A #N/A
280 1035 1035 280 200
40
240
250
18 #N/A #N/A
290 1035 1035 290 280
40
290
295
19 #N/A #N/A
295 1035 1035 295 290
0
295
385
20 #N/A #N/A
385 1035 1035 385 295
0
385
425
21 #N/A #N/A
425 1035 1035 425 385
0
425
455
22 #N/A #N/A
455 1035 1035 455 425
0
455
465
23 #N/A #N/A
465 1035 1035 465 455
0
465
495
24
25 #N/A
495
510 1035 495 465
0
495
525
26 #N/A #N/A
525 1035 1035 525 495
0
495
510 #N/A #N/A
26 1035 1035
525 525 510
15
525
585
27 #N/A #N/A
585 1035 1035 585 525
0
585
675
28
29 #N/A
675
735 1035 675 585
0
675
735 #N/A #N/A
29 1035 1035
735 735 675
0
735 1035 #N/A #N/A #N/A 1035 1035 1035 1035 735
0

- find the row on which the activity is first cited as a dependency, by looking down column D
- find the second citing by looking down the column below the first
- find the row on which the activity is first cited as a second dependency, by looking down column E
- the start of the first activity which is dependant on this one, if there isn't one, return the end of the project
- the stop is the earliest of the starts
- the start of the activity is the stop minus the duration
- the Float is the difference between the Pull Start and the Push Start

327734556.xls

Section 39

Make-to-Stock (Inventory) Schedule

Up to now all our schedules have been make-to-order, and typically demand would come from sales orders or works orders. In traditional MRP logic, Master Production Scheduling (MPS) would be used to review finished inventory and sales
forecasts, and generate manufacturing orders on the factory. The factory would then use the orders to create a schedule. In this example we use MPS and Finite Scheduling logic combined into one. The model is used to re-schedule quickly in
response to changes in inventory levels.
There is one work centre that cycles through a portfolio of 5 products. If the rate of sales of any of the products, exceeds the rate of production, the logic will not work. The first job on the line is the one that is currently running, but after that the
schedule decides four things:
- which product to make next
- the quantity to make
- when to stop
- how long to lie idle for
Each of these can be overridden by entering something in one of the three "Force" columns. Try this, and also change some of the values to see how the schedule responds.
Start of the first job:
Product running now:
Quantity committed:

C
B
Idle
E
Idle
C
D
A
B
Idle
E
Idle
C
D
A
B
E
Idle
C
D
A
B
E
Idle

Force

Days

Product

14/3 8:00
C
3400

Speed - units per hour

Forecast sales - units per week
Min cover - days
Max cover - days
Min cover - units
Max cover - units

Idle
Run
Force hours Force hours
Start
Opening stock:
3400
0.0
12.1 14/3 8:00
17376
0.0
49.6 14/3 20:08
0
33.1
0.0 16/3 21:47
13241
0.0
55.2 18/3 6:51
0
45.9
0.0 20/3 14:01
11748
0.0
42.0 22/3 11:53
16.0
11676
0.0
37.7 24/3 5:50
8413
0.0
28.0 25/3 19:30
14.0
13326
0.0
38.1 26/3 23:33
12.0 0
0.4
0.0 28/3 13:37
13241
0.0
55.2 28/3 14:01
10.0 0
80.6
0.0 30/3 21:12
11748
0.0
42.0
3/4 5:50
8.0
10800
0.0
34.8 4/4 23:48
8296
0.0
27.7 6/4 10:38
6.0
13265
0.0
37.9 7/4 14:17
15379
0.0
64.1
9/4 4:11
4.0
0
75.5
0.0 11/4 20:16
2.0
11748
0.0
42.0 14/4 23:48
10925
0.0
35.2 16/4 17:45
0.0
8415
0.0
28.1 18/4 5:00
5/3 0:00
15/3 38.6
0:00 19/4 9:03
13496
0.0
15127
0.0
63.0 20/4 23:36
0
75.1
0.0 23/4 14:38
Qty

A
B
C
D
E
300
350
280
310
240
5000 8000 7000 6500 9000
4
4
4
4
6
14
14
14
14
14
2857 4571 4000 3714 7714
10000 16000 14000 13000 18000

Stop
Projected Inventory - units
14/3 8:00 10621 1566 8762 11984 12796
14/3 20:08 10260
988 11656 11514 12145
16/3 21:47 8782 16000 9587 9593 9486
18/3 6:51 7798 14425 8210 8314 7714
20/3 14:01 6156 11798 5911 6179 18000
22/3 11:53 4791 9614 4000 4405 15543
24/3 5:50 3542 7616 14000 2782 13296
25/3 19:30 2421 5823 12431 13000 11278
26/3 23:33 10000 4487 11262 11915 9776
28/3 13:37 8867 16000 9676 10442 7736
28/3 14:01 8855 15981 9659 10426 7714
30/3 21:12 7213 13353 7360 8292 18000
3/4 5:50 4813 9513 4000 5171 13680
4/4 23:48 3564 7515 14000 3548 11432
6/4 10:38 2527 5856 12548 13000 9566
7/4 14:17 10000 4540 11396 11930 8084
9/4 4:11 8872 16000 9817 10464 6054
11/4 20:16 6965 12949 7147 7984 18000
14/4 23:48 4717 9352 4000 5062 13954
16/4 17:45 3468 7354 14000 3439 11706
18/4 5:00 2419 5676 12532 13000 9818
19/4 9:03 10000 4340 11363 11915 8315
25/3 0:00
4/4 0:00
20/4 23:36
8852 16000 9756 10423
6250
23/4 14:38 6976 12999 7130 7984 18000
26/4 17:45 4741 9422 4000 5078 13976

Projected Cover - days

14.9 1.4 8.8 12.9 10.0
14.4 0.9 11.7 12.4 9.4
12.3 14.0 9.6 10.3 7.4
10.9 12.6 8.2 9.0 6.0
8.6 10.3 5.9 6.7 14.0
6.7 8.4 4.0 4.7 12.1
5.0 6.7 14.0 3.0 10.3
3.4 5.1 12.4 14.0 8.8
14.0 3.9 11.3 12.8 7.6
12.4 14.0 9.7 11.2 6.0
12.4 14.0 9.7 11.2 6.0
10.1 11.7 7.4 8.9 14.0
6.7 8.3 4.0 5.6 10.6
5.0 6.6 14.0 3.8 8.9
3.5 5.1 12.5 14.0 7.4
14.0 4.0 11.4 12.8 6.3
12.4 14.0 9.8 11.3 4.7
9.8 11.3 7.1 8.6 14.0
6.6 8.2 4.0 5.5 10.9
4.9 6.4 14.0 3.7 9.1
3.4 5.0 12.5 14.0 7.6
14.0 3.8 11.4 12.8 6.5
14/4
0:004.9
12.4 14.0 9.8
11.2
9.8 11.4 7.1 8.6 14.0
6.6 8.2 4.0 5.5 10.9

327734556.xls

Lowest Offs
Cover
et
0.9
7.4
6.0
5.9
4.0
3.0
3.4
3.9
6.0
6.0
7.4
4.0
3.8
3.5
4.0
4.7
7.1
4.0
3.7
3.4
3.8
4.9
7.1
4.0

Next
Prod

Invento
ry
Speed

2B
988
5E
9486
5E
7714
3C
5911
3C
4000
4D
2782
1A
2421
2B
4487
5E
7736
5E
7714
3C
7360
3C
4000
4D
3548
1A
2527
2B
4540
5E
6054
3C
7147
3C
4000
4D
3439
1A
2419
2B
4340
524/4
E 0:00 6250
3C
7130
3C
4000

350
240
240
280
280
310
300
350
240
240
280
280
310
300
350
240
280
280
310
300
350
240
280
280

Forcast

Min
Cover

Min
units

Max
units

8000
4 4571 16000
9000
6 7714 18000
9000
6 7714 18000
7000
4 4000 14000
7000
4 4000 14000
6500
4 3714 13000
5000
4 2857 10000
Projected
Inventory
8000
4 4571 16000
Cover
9000
6 7714 18000
9000
6 7714 18000
7000
4 4000 14000
7000
4 4000 14000
6500
4 3714 13000
Prod10000
A
5000
4 2857
8000
4 4571
Prod16000
B
9000
6 7714 18000
Prod C
7000
4 4000 14000
Prod14000
D
7000
4 4000
6500
4 3714
Prod13000
E
5000
4 2857 10000
8000
4 4571 16000
4/5
0:00
9000
6 7714 18000
7000
4 4000 14000
7000
4 4000 14000

To
make
17376
10961
13241
9504
11748
11676
8413
13326
13213
13241
7801
11748
10800
8296
13265
15379
8051
11748
10925
8415
13496
15127
8071
11748

Section 40

Make-to-Stock Logic Explained

The logic works as follows:
- the first job is the "Product running now"
- it starts at the "Start of the first job"
- and the "Quantity committed" is specified
- the duration of the job is calculated from the speed, and shown in the "Run hours" column
- a separate column for each product records the projected inventory level at the end of each job
- at the end of each job, the inventory level of one product has risen, and the other four products have fallen
- the opening inventory is recorded at the top of the columns, and would typically come from an inventory management system
- the inventory at the end of the job is the opening inventory, less the forecast sales for the duration of the job, plus the production quantity of the job
- another bank of five columns expresses the projected inventory level as days of cover
- the product with the lowest cover at the end of the job is identified as the next product to be made
- the quantity to make is that required to increase the inventory level to the maximum, plus sufficient to replenish the amount sold while the job was running
- the duration of the next job is calculated, and the cycle begins again
- if the projected inventory level of the lowest cover product is above its minimum, then an idle period is scheduled
- the duration of the idle period is the time it will take for forecast sales to reduce the inventory to the minimum and trigger the next run
You will see from the chart of inventory cover, that the model attempts to keep the inventory of each product between the minimum and maximum.

327734556.xls

Section 41

1
2
3
4
5
6
7
8
9
10
11
12
13

A
B
C
D
E
F
G
Make-to-Stock Schedule - Formulas Explained

Start of the first job:

Product running now:
Quantity committed:

14/3 8:00
C
3400

Product Force

Idle
Run
Force hours Force hours
Start
Stop
Projected Inventory - units
Opening stock:
14/3 8:00 10621 1566 8762 11984 12796
0.0
12.1 14/3 8:00 14/3 20:08 10260
988 11656 11514 12145
0.0
49.6 14/3 20:08 16/3 21:47 8782 16000 9587 9593 9486
33.1
0.0 16/3 21:47 18/3 6:51 7798 14425 8210 8314 7714
0.0
55.2 18/3 6:51 20/3 14:01 6156 11798 5911 6179 18000
45.9
0.0 20/3 14:01 22/3 11:53 4791 9614 4000 4405 15543

14
15
16
17
18
19
20
21

C
B
Idle
E
Idle

Qty
3400
17376
0
13241
0

Speed - units per hour

Forecast sales - units per week
Min cover - days
Max cover - days
Min cover - units
Max cover - units

A
B
C
D
E
300
350
280
310
240
5000 8000 7000 6500 9000
4
4
4
4
6
14
14
14
14
14
2857 4571 4000 3714 7714
10000 16000 14000 13000 18000

Projected Cover - days

14.9 1.4 8.8 12.9 10.0
14.4 0.9 11.7 12.4 9.4
12.3 14.0 9.6 10.3 7.4
10.9 12.6 8.2 9.0 6.0
8.6 10.3 5.9 6.7 14.0
6.7 8.4 4.0 4.7 12.1

Lowest
Cover

Offset

Next
Prod

0.9
7.4
6.0
5.9
4.0

2
5
5
3
3

B
E
E
C
C

Invento
Speed
ry
988
9486
7714
5911
4000

350
240
240
280
280

AA

AB

AC

Forcast

Min
Cover

Min
units

Max
units

To
make

8000
9000
9000
7000
7000

4
6
6
4
4

4571
7714
7714
4000
4000

16000
18000
18000
14000
14000

17376
10961
13241
9504
11748

22

Product

=IF(E19,"Idle",IF(ISBLANK(A18),D$6,IF(ISBLANK(B19),V18,B19)))

23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46

C
E
G
H
I
J
O
T
U
V
W
X
Y
Z
AA
AB
AC

Qty
Idle hours
Run hours
Start
Stop
Projected Inventory - units
Projected Cover - days
Lowest Cover
Offset
Next Prod
Inventory
Speed
Forecast
Min Cover
Min units
Max units
To make

=IF(A19="Idle",0,IF(ISBLANK(C18),D$7,IF(ISBLANK(D19),AC18,D19)))
=MAX(IF(ISBLANK(F19),IF(Y18,ROUND((W18-AA18)/Y18,7),0)*168,F19),0)
=C19/HLOOKUP(A19,$J$5:$N$9,2)
=I18
=H19+((G19+E19)/24)
=J18-(J$7*($G19+$E19)/168)+(($A19=J$5)*$C19)
=J19/J$7*7
=MIN(O19:S19)
=MATCH(T19,O19:S19,0)
=INDEX(O$5:S$5,1,U19)
=INDEX(J19:N19,1,U19)
=INDEX(J$6:N$6,1,U19)
=INDEX(J$7:N$7,1,U19)
=INDEX(J$8:N$8,1,U19)
=INDEX(J$10:N$10,1,U19)
=INDEX(J$11:N$11,1,U19)
=AB19-W19+Y19*(AB19-W19)/(X19-Y19/168)/168

if there are idle hours return "Idle", else, if its the first job return Job now running, else, if force is blank return next product
from previous row, else forced value
if its an idle period put zero, else if its the first job return quantity committed, else forced value if there is one, else to make
return the force value if there is one, else no of weeks for stock to reach minimum, times 168 for hours, but 0 if its -ve
quantity divided by the speed for that product, looked up in the horizontal table
stop of the previous job
start plus idle hours plus run hours, divided by 24 to get to days
Inventory at the end of the previous job, less forecast sales for the duration of the job, plus qty if the job is for the product
inventory divided by forecast
lowest inventory cover at the end of the job
find the offset of the product with the lowest inventory cover
for the product, get the: product code
inventory level
speed
forecast sales per week
minimum inventory cover in days
minimum inventory in units
maximum inventory in units
this formula has been created by substituting together the following:

AE
AF
AG
AH
AI

Inventory replenishment qty

forecast units per hour
effective speed
run duration
sales during production
to make

=AB19-W19
=Y19/168
=X19-AF19
=AE19/AG19
=AH19*AF19
=AE19+AI19

maximum inventory minus inventory level

sales forecast in units per week, divided by 168 (7x24), to get to units per hour
speed of building inventory, rate of production minus rate of sales
time it will take to build up the replenishment quantity
units that will be sold while the job is running
Inventory replenishment qty plus sales during production

327734556.xls

AD

Section 42

1
2

4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

A
B
C
D
Inventory Cover Calculation

In Section 40 the sales forecast is expressed as a straight line, e.g.. 100 units per week. Calculating the inventory cover is a
simple division, e.g.. an inventory of 650 will last for 6.5 weeks. However, a sales forecast may be non-linear, to reflect
seasonal sales demand, sales promotions, a new product with an anticipated increase in demand, or a product at the end of
it's life cycle. Calculating the cover with a non-linear forecast, is more complex. Here is a sales forecast for 20 weeks which
falls and then rises again:
Weeks
Sales forecast:
Cum forecast:

10

11

12

13

14

15

16

17

18

19

20

80

76

72

68

64

60

56

52

48

44

40

40

44

48

52

56

60

64

68

72

80 156 228 296 360 420

476 528 576 620 660 700 744 792 844 900 960 1024 1092 1164

Here is an inventory cover calculation:

Inventory:
the week in which the inventory will run out
cumulative forecast at the end of the previous week
inventory left in the last week
forecast for the last week
portion of the week that the inventory will last
cover - weeks that the inventory will last

500
8
476
24
52
0.46
7.46

=MATCH(I13,B9:Z9)
=INDEX(B9:Z9,1,I14)
=I13-I15
=INDEX(C7:Z7,1,I14)
=I16/I17
=I14-1+I18

21

What if the inventory cover is greater then 20 weeks? We could assume that the forecast for the 20th week will continue in a
straight line at 72 per week thereafter. Here are some additional steps to cope with that:

22
23
24
25
26
27
28
29
30

Inventory:
last week
last forecast quantity
last cum forecast
cover after the last week
total cover
test to see if this should be applied

31

Now lets consider a forecast that drops away to zero, and inventory which will last for infinity. Speadsheets don't return a value
for infinity, they return an error, so lets adopt a convention that infinite cover is represented by the value 999.

32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50

Sales forecast:
Cum forecast:

80
0

75

2000
20
72
1164
11.6
31.6
1

70

65

60

55

80 155 225 290 350 405

Inventory:
last week
last forecast quantity
last cum forecast
cover after the last week
total cover

50

=MAX(C5:Z5)
=INDEX(C7:Z7,1,I24)
=MAX(C9:Z9)
=(I23-I26)/I25
=I24+I27
=I23>I26

455 500 540 575 605 630 650 665 675 680 680

680

680

680

2000
20
0
680
###
999

45

40

35

30

25

20

15

10

=IF(I39,I38+I41,999)

The whole lot can be substituted together as follows:

the week in which the inventory will run out
total cover

8
7.46

=IF(I13>MAX(C9:Z9),IF(INDEX(C7:Z7,1,MAX(C5:Z5)),MAX(C5:Z5)+(I13-MAX(C9:Z9))/INDEX(C7:Z7,1,MAX(C5:Z5)),999),
I46-1+(I13-INDEX(B9:Z9,1,I46))/INDEX(C7:Z7,1,I46))

327734556.xls

Section 43