ࡱ>  }}   !"#$%&'()*+,-./0123456789:;<=>?@ABCDEFGHIJKLMNOPQRSTUVWXYZ[\]^_`abcdefghijklmnopqrstuvwxyz{|}~Root EntryFA. y@PerfectOffice_MAIN& V SummaryInformation(0 0U>33  0U>33  0U>33 0U>33  0U>33  0U>33  0U>33  0U>33 0U>33 0U>33 0u>33 0>33  0>33  0>33  0>33  0>33 0>33  0>33  0>33  0>33  0>33 0>33 0>33 0>33  0U>33  0U>33 0]>33 0]>33 0]>33  0]>33  0]>33  0]>33  0]>33 0]>33  0]>33  0]>33  0]>33  0]>33 0]>33 0]>33 0}>33 0U>33 0U>33 0U>33  0U>33  0U>33  0U>33  0U>33 0U>33  0U>33  0U>33  0U>33  0U>33 0U>33 0U>33 0u>33 0>33 0>33 0>33  0>33  0>33  0>33  0>33 0>33  0>33  0>33  0>33  0>33 0>33 0>33 0>33  0U>33  0U>33  0U>33  0U>33  0U>33  0U>33  0U>33  0U>33  0U>33  0U>33  0U>33  0U>33 0]>33 0]>33  0]>33  0]>33  0]>33  0]>33 0]>33  0]>33  0]>33  0]>33  0]>33 0]>33  0]>33  0]>33  0]>33  0]>33 0U>33 0U>33  0U>33  0U>33  0U>33  0U>33 0U>33  0U>33  0U>33  0U>33  0U>33 0U>33  0U>33  0U>33  0U>33  0U>33 0>33 0>33  0>33  0>33  0>33  0>33 0>33  0>33  0>33  0>33  0>33 0>33  0>33  0>33  0>33  0>33 0]>33 0]>33  0]>33  0]>33  0]>33  0]>33 0]>33  0]>33  0]>33  0]>33  0]>33 0]>33  0]>33  0]>33  0]>33  0]>33  0>33 0U>33  0U>33 0U>33  0U>33 0U>33  0U>33  0U>33  0U>33  0]>33  0]>33  0]>33  0]>33  0]>33  0]>33  0U>33  0U>33  0U>33  0U>33  0U>33  0U>33  0>33  0>33  0>33  0>33  0>33  0>33  0U>33  0U>33  0>33 0]>33  0]>33  0]>33  0]>33  0]>33 0]>33  0]>33  0]>33  0]>33  0]>33 0]>33 0]>33 0}>33 0U>33  0U>33  0U>33  0U>33  0U>33 0U>33  0U>33  0U>33  0U>33  0U>33 0U>33 0U>33 0u>33 0>33  0>33 LMG.h/3$ ArialZ3<LZ3$ ArialZ3<LZ3$ArialZ3<LZ3$ArialZ3<LZ3$ArialZ3<LZ3$ArialZ3<LZ3$ ArialZ3<LZ3$ ArialZ3<LZ3$ ArialZ3<LZ3$ ArialZ3<LZ3$ ArialZ3<LZ3$ArialZ3<LZ3@ Normal033 833 833 833 833 833 833 833 833 833 833 0>33 0>33 0E>33  0D>33  0U>33 0 >33 0>33  0>33 0>33  0>33 0U>33 0E>33 0E>33 0T>33 0U>33 0U>33 0>33  0D>33 0U>33 0Q>33  0>33 0U>33 0E>33 0D>33 0E>33 0U>33 0U>33 0>33 0T>33 0>33 0T>33  0P>33  0>33 0Q>33 0>33  0U>33 0>33 0T>33 0U>33 0T>33  0D>33 0T>33 0T>33 0>33  0U>33 0U>33 0_>33 0E>33  0D>33 0U>33 0T>33 0T>33 0]>33 0T>33  0U>33  0>33 0U>33 0E>33 0T>33 0E>33 0E>33 0T>33 0T>33 0T>33 0T>33 0U>33  0>33  0>33 0}>33 0U>33 0E>33 0E>33 0]>33 0]>33 0T>33 0D>33 0>33 0>33 0T>33 0W>33 0_>33 0E>33 0>33 0T>33 0T>33 0}>33 0u>33 0>33 0U>33 0U>33 0E>33 0T>33 0E>33 0T>33 0T>33 0W>33 0>33 0>33 0 >33 0>33 0>33 0 >33 0>33 0 >33 0 >33 0>33 0>33 0 >33 0 >33  0>33 0E>33 0T>33 ` U>33 ` ]>33 ` >33 0}>33 0>33 0>33 0>33 0_>33 0>33 0U>33 ` }>33 0>33 0>33 ` u>33 ` >33 ` _>33 ` W>33 0>33 0>33 ` >33 0>33 0 >33 0<>33 0O>33 0W>33 0u>33 0u>33 0W>33 0O>33 0L>33 0|>33 0U>33 0u>33 0W>33 0E>33 0T>33 ` U>33 ` U>33 ` W>33 ` u>33 0>33 0 >33 0<>33 0>33 0>33 0>33 0>33 0>33 0>33 @U>33 U>33 `U>33 0U>33 0U>33 U>33 U>33 @>33 >33 `>33 >33 >33  0U>33  0>33 033 033 033 033  033  033  033  033  033 0U>33  0U>33 0U>33  0U>33 0U>33  0U>33 0U>33  0U>33 0U>33  0U>33 0U>33  0U>33 0U>33  0U>33 0U>33  0U>33 0U>33  0U>33 0U>33 0U>33 0U>33 0>33 0>33 0U>33 0U>33 0U>33 0U>33 0U>33  0U>33 0U>33 0U>33  0U>33 0U>33  0U>33 0U>33 0U>33 0U>33  0Q>33  0U>33 0U>33 0U>33  0>33  0U>33  0U>33  0]>33  0]>33  0]>33  0]>33  0]>33  0]>33  0U>33  0U>33  0U>33  0U>33  0U>33  0U>33  0>33  0>33  0>33  0>33  0>33  0>33  0>33  0U>33  0U>33  0U>33 0]>33  0]>33  0]>33  0]>33  0]>33 0]>33  0]>33  0]>33  0]>33  0]>33 0]>33 0]>33 0}>33 0U>33  0U>33  0>33  0>33  0>33 0>33  0>33  0>33  0>33  0>33 0>33 0>33 0>33  0U>33  0U>33 0]>33 0]>33  0]>33  0]>33  0]>33  0]>33  0]>33  0]>33  0]>33  0]>33  0]>33  0]>33  0]>33  0]>33 0U>33 0U>33  0U>33  0U>33  0U>33  0U>33  0U>33  0U>33  0U>33  0U>33  0U>33  0U>33  0U>33  0U>33 0>33 0>33  0U>33  0U>33  0U>33  0U>33  0U>33  0U>33  0U>33  0U>33  0U>33  0U>33  0U>33  0U>33 0>33 0>33  0>33  0>33  0>33  0>33  0>33  0>33  0>33  0>33  0>33  0>33  0>33  0>33 0}>33 0u>33 0U>33 0u>33 0U>33 0u>33 0U>33 0u>33 0U>33 0u>33 0>33 0U>33  0U>33 0]>33 0]>33  0]>33 0]>33 0]>33  0]>33 0U>33 0U>33  0U>33 0U>33 0U>33  0U>33 0>33 0>33  0>33 0>33 0>33  0>33  0U>33  0U>33  0]>33  0]>33  0]>33  0]>33  0]>33 0]>33  0]>33  0]>33  0]>33  0]>33  0]>33  0]>33  0]>33  0]>33  0]>33  0]>33  0]>33  0]>33  0]>33  0]>33  0U>33  0U>33  0U>33  0U>33  0U>33 0U>33  0U>33  0U>33  0U>33  0U>33  0U>33  0U>33  0U>33  0U>33  0U>33  0U>33  0U>33  0U>33  0U>33  0U>33  0>33  0>33  0>33  0>33  0>33 0>33  0>33  0>33  0>33  0>33  0>33  0>33  0>33  0>33  0>33  0>33  0>33  0>33  0>33  0>33 0]>33  0]>33  0]>33  0]>33  0]>33  0]>33  0]>33 0]>33  0]>33  0]>33  0]>33  0]>33 0]>33  0]>33  0]>33  0]>33  0]>33  0]>33  0]>33  0]>33  0]>33 0]>33  0]>33  0]>33  0]>33  0]>33 0]>33  0]>33  0]>33  0]>33  0]>33  0]>33  0]>33  0]>33  0]>33 0]>33  0]>33  0]>33  0]>33  0]>33 0]>33  0]>33  0]>33  0]>33  0]>33 0U>33  0U>33  0U>33  0U>33  0U>33  0U>33  0U>33 0U>33  0U>33  0U>33  0U>33  0U>33 0U>33  0U>33  0U>33  0U>33  0U>33  0U>33  0U>33  0U>33  0U>33 0U>33  0U>33  0U>33  0U>33  0U>33 0U>33  0U>33  0U>33  0U>33  0U>33  0U>33  0U>33  0U>33  0U>33 0U>33  0U>33  0U>33  0U>33  0U>33 0U>33  0U>33  0U>33  0U>33  0U>33 0U>33  0U>33  0U>33  0U>33  0U>33  0U>33  0U>33 0U>33  0U>33  0U>33  0>33  0U>33 0U>33  0U>33  0U>33  0U>33  0U>33  0U>33  0U>33  0U>33  0U>33 0U>33  0U>33  0U>33  0U>33  0U>33 0U>33  0U>33  0U>33  0U>33  0U>33  0U>33  0U>33  0U>33  0U>33 0U>33  0U>33  0U>33  0U>33  0U>33 0U>33  0U>33  0U>33  0U>33  0U>33 0>33  0>33  0>33  0>33  0>33  0>33  0>33 0>33  0>33  0>33  0>33  0>33 0>33  0>33  0>33  0>33  0>33  0>33  0>33  0>33  0>33 0>33  0>33  0>33  0>33  0>33 0>33  0>33  0>33  0>33  0>33  0>33  0>33  0>33  0>33 0>33  0>33  0>33  0>33  0>33 0>33  0>33  0>33  0>33  0>33  0U>33  0U>33 @U>33 U>33 `U>33 U>33 U>33  0>33 @>33 >33 `>33 >33 >33 0U>33 0U>33  0U>33  0U>33  0U>33  0U>33 @U>33 U>33 `U>33 U>33 U>33 @U>33 U>33 `U>33 U>33 U>33 0U>33 0U>33 0U>33 0U>33  0U>33  0U>33 @U>33 U>33 `U>33 U>33 U>33  0>33 @>33 >33 `>33 >33 >33 0U>33 0U>33 0U>33  0U>33  0U>33  0U>33  0U>33 @U>33 U>33 `U>33 U>33 U>33 @U>33 U>33 `U>33 U>33 U>33 0U>33 0U>33 0U>33 0U>33 QFUNC1Match DATEINFO XINDEX PUREMIN PUREMAX ISBLANK  MATRIX_1   MATRIX_2 dBY!_d<  ?  t~($$  A   ? ~      wY@W swW H H H (p lwwwwwwwwgt?ww@ H H H (p lwwwwwwwGpt?wwG!pQpvwwwwwwwwwwwwwwwwwwwwwwwgC _pxxpwwww, lwwgxxpwwww lwwwwwwww lwwwwwwwwwwwwwwwww lwwwwwwwG!pQpvwwwwwd2X,ww7!pQpvwwwww7Xpwwww o*?粚5dwwww lww UwUwwwwwՀ*www??w?wwwwwwVUՀդV?wwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwڂ*'""" "    $ $       Tq  d2X, X1Axis?  Ts  d2X, Y1Axis?   Tt  d2X, Y2Axis?  }j  d2X, 1PaneLeftWallRightWallBaseSeriesSeriesLabel?   Ym  d2X,  X1Title?  3?Yo  d2X,  Y1Title?  l?Yp  d2X,  Y2Title?  ?Tl  d2X, Legend?  }Z    Title?  $ArialArial "]ZY !_d<  ? ($$  A   ?       wY@W swW H H H (p lwwwwwwwwgt?ww@ H H H (p lwwwwwwwGpt?wwG!pQpvwwwwwwwwwwwwwwwwwwwwwwwgC _pxxpwwww, lwwgxxpwwww lwwwwwwww lwwwwwwwwwwwwwwwww lwwwwwwwG!pQpvwwwwwd2X,ww7!pQpvwwwww7Xpwwww o*?粚5dwwww lww UwUwwwwwՀ*www??w?wwwwwwVUՀդV?wwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwڂ* '""" "    $ $       Tq  d2X, X1Axis?  Ts  d2X, Y1Axis?   Tt  d2X, Y2Axis?  }j  d2X, 1PaneLeftWallRightWallBaseSeriesSeriesLabel?   Ym  d2X,  X1Title?  7NYo  d2X,  Y1Title?  pNYp  d2X,  Y2Title?  NTl  d2X, Legend?  }Z    Title?  $ArialArial "]ZYCHART 1!_d<  ? l"($$  AX   ?      ww?wxpwgp lwwwwwwwwwg swXpw'!pQpvwwwwwwwww?wxpw'!pQpvwgHpwwwwwwwwwwwwwwwwwwwww>>w7 7 ProjecteOfWFory Cove(wwwGpQpvww7 7 wwwwg!pQpvwwg6wwwww!pQpvwwwwwwwwwwwG?fW&Dayswwww lwwwwwwwG!pQpvwwwwwd2X,p'p lww'WhpwM???p7w7wg# d۞s.YAqwwgsp lww  ???w???gss???WsՀ*???g s???G s????Gs???Gs???G sՀդV?wwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwڂ*'""" "   + +3   ++3 Prod A ++3 Prod B ++3 Prod C ++3 Prod D ++3 Prod E$ $       Tq  d2X, X1Axis?  Ts  d2X, Y1Axis?   Tt  d2X, Y2Axis?  }j  d2X, 1PaneLeftWallRightWallBaseSeriesSeriesLabel?   Ym  d2X,  X1Title?  ;]Yo  d2X,  Y1Title?  t]Yp  d2X,  Y2Title?  ]Tl  d2X, Legend?  }Z    Title?  $ArialArial "]PZ  %|^&'( 0H   $ ArialZLZ$ ArialZLZ  LB  %&'( 0d  R $ ArialZLZ$ ArialZLZ  B           ^  ddư>   ?&  Mb@?  #        f  g    i2       Z-./g  0d    ~BT.6 Contents$u<7~T7,u%  33%^SCHEDULING WITH A SPREADSHEET `q^TABLE OF CONTENTS^Section                                   ! " # $ %! &" '# ($ )% *& +' ,( -) .* /+ 0 1 2 3  ` `3LCapacity Planning vs Finite Scheduling - the essential differencee\t~3"Time cascades downwardssa~3$Re-sequencing the schedulee_~3A Simple Gantt Chartt (~3  Setting up a Calendarr 3< Calculation of Job Stop Time Through the Calendarr t~3 Julian Datess  3& Setting up a Julian Calendarr  3& Using the Calendar Formulass| 3,Multiple Machines or Work Centress 38Joining Text Together and Indirect ReferencessX 32Separate Calendars for each Work CentreeȌ3,Repetitive Production and Set-upssa~3A Set-up Matrixxd3,Applying the Set-up Matrix Formulaa̍3(Gantt Chart 2 - Hours per Dayy03(Gantt Chart 3 - Units per Dayy3>Working Hours Between Two Dates (Calendar Formula 2))3&Applying Calendar Formula 223t36Jobs That Pass Through Multiple Work Centress36Jobs That Pass Through Multiple Work Centress^~3Transfer Batchess32Re-Using Parts of the Calendar Formulass]36Jobs That Pass Through Multiple Work Centressu~3A Pull Scheduleex~3 Push and Pull Scheduleeܑ3<Calculation of Job Stop Time Through the Calendarr T38 Applying Calendar Formula 3 to a Pull Schedulee!Ȓ 3.!Multiple Work Centre - Pull Schedulee"4!3N"Repetitive Production Through Multiple Work Centres - Pull Schedulee]#"3V#Repetitive Production, Multiple Work Centres, Pull Schedule, With Calendarss3$T#3($Push Pull Push - 3 Pass Logicc%$30%Multiple Work Centre - 3 Pass Schedulee&$%3P&Repetitive Production Through Multiple Work Centres - 3 Pass Schedulee'&3*'3 Pass Schedule With a Calendarr('32(3 Pass Schedule With Multiple Calendarss)(3H)Repetitive Production, 3 Pass Schedule With Multiple Calendarss*l\~)3*Project Schedulingg+TU*3 +Critical Path Analysiss,t+3,,Make-to-Stock (Inventory) Schedulee-ܗ,3(-Make-to-Stock Logic Explainedd.@-36.Make-to-Stock Schedule - Formulas Explainedd3/.3&/Inventory Cover Calculationn1'To Come:02'Project scheduling with finite resources33'Material Requirements to Support a Schedule Introduction$u<7~7Y,сϡ  57'Introduction%'Who Should Use This Tutorial 3 'Background to Scheduling With a Spreadsheet!'Build Your Own System$&'Interface to Your ERP System+-'Structured and Disciplined Approach2'E-mail Us:5'Visit our web site at:P'For years spreadsheets have been used to design and prototype schedulingN'systems. They have now grown up, and are being used to develop serious,'production scheduling applications. i'This tutorial is aimed at spreadsheet literate people who are involved in planning and schedulinge'production activities. The techniques and formulas set out in this tutorial are being used byV 'manufacturing companies daily; this is a practical, not an academic, exercise.i 'We have been designing, building and implementing production scheduling systems for manufacturingg 'companies for over 10 years. When PC's and spreadsheets were less capable than they are now, weo'used spreadsheets to design and prototype scheduling algorithms, and to train on some of the principlesp'of scheduling. Prototype designs were then handed over to software developers to write in more resilient,'and efficient programming languages.o'Often the pressure was on to throw several thousand records of data at the prototypes and use them for n'live scheduling, before handing them over to the software developers. So, in order to 'shoehorn' a bigp'scheduling task into a small PC, we recorded macro's that wrote a formula, copied it down, overwrote thep'cells with values, then moved on to the next column, so that no memory consuming live formulas were lefth'behind. Typically, most of the macro code prepared downloaded data for scheduling, and generatedm'reports from the schedule, with only a small portion of the macro calculating the schedule itself. Weh'ended up with big cumbersome macro driven scheduling systems that ordinary, spreadsheet literate"'people were locked out of.p'Thankfully we now have Pentium II's and III's which allow us to apply formulas to large amounts of data,d'and we have features such as Excel's PivotTable which will re-arrange and summarise data forl'scheduling, and prepare reports without resorting to writing macros. It makes the job, of building aj'scheduling system with a spreadsheet, a whole lot easier, and within the capability of the average'speadsheet user.m"'Our offering to our clients has now changed, and we can now give them the option of either building ao#'scheduling system for them, or teaching their staff to build one for themselves, and providing guidence$'while they do it.a''Our clients include companies such as Shell, Toyota and Unilever, as well as many smallerq('manufacturing concerns. They all have ERP or older MRPII systems, and have felt that the functionality ofn)'their systems need to be extended to give them the kind of responsive scheduling they are looking for.f*'Spreadsheet based scheduling applications have been interfaced to SAP, Baan and many other ERP+'systems.m.'Our offering to our clients has now changed, and we can now give them the option of either building ao/'scheduling system for them, or teaching their staff to build one for themselves, and providing guidence0'while they do it.(3'Production-Scheduling@Mweb.co.za%7'www.Production-Scheduling.com Section 1$u<7~87,,  I'Capacity Planning vs Finite Scheduling - the essential difference@'Imagine you have 5 jobs to do before this time tomorrow:j 'You have work totalling an estimated 36 hours to complete in a day (24 hours). You have a problem.f'Capacity planning tells you that you have a problem by saying that you are (3624) 150% loadedb'for the day, and leaves you to resolve the problem by adding more resources or negotiating'alternative due dates.o'Finite scheduling, on the other hand, recognises that the capacity of the resource is finite, and tellsR' you that some of the jobs are going to be late, and it can also tell you:i'I would argue that compared with capacity planning, finite scheduling gives you richer management'information. 9^Jobs ^job A ^job B ^job C ^job D  ^job E    ^Total)'- which jobs are going to be late''- how late they are going to be='- are they important jobs, or for important customers5'- what revised delivery dates can be promisedX'- how all these would change if the Jobs were undertaken in a different sequence 9^Hours        " B@P Section 2$u<7~,7,, [!A r " Arrow1 X {GX ,c? "!A ^& Arrow2 X {GX 1q? "!A ,? Arrow3 X {GX Dq ? "!A Z Arrow4 X {GX Dq*? "!A xArrow5 X {GX GqY'? "\*     'Time cascades downwards='Lets add some additional columns to the list of jobs:? 'This is now more than a list of jobs, it is a schedule.h'Each formula is entered in the top cell of the column and then copied down, take a look at them: '+E7'+D8+C8'+E8<=F8e'Double click on a cell containing a formula, and the cells it refers to will be colour coded.a'You can see that each job is dependant on the one before it, and time cascades downwards.i'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. ^Jobs ^job A ^job B ^job C  ^job D  ^job E+'- the first job starts at hour zero9'- the next job starts when the previous one stops.'- the stop is the start plus the hours+'- each job is due 24 hours from now='- a job is on time if it stops on or before it is due ^Hours        ^Start@3@ 7@ <@ ^Stop$@ $3@ $7@ $ <@ $ B@  ^Due       ^On time$?$?$?$ $  Section 3$u<7~77,, 0       "'Re-sequencing the schedule?'We will add a sequence column to the left of the table:m 'If we do job B last, four out of the five jobs will be on time. The table was sorted into a differentb'sequence by entering the numbers 1 5 2 3 4 down the sequence column,highlighting the table*'and sorting ascending by column B. ^Seq         ^Jobs ^job A ^job C  ^job D  ^job E  ^job B ^Hours         ^Start@ &@ 0@ 8@ ^Stop$@ $&@ $ 0@ $ 8@ $ B@  ^Due        ^On time$?$?$ ?$ ?$  Section 4$u<7~`@7, ,:   hhhh h h h h hhhhhhhhhhhhhhhhhhh h!h"h#h$h%h&h'h(h                               ! " # $ % & ' ( ) 'A Simple Gantt Chart'The start and stop times of each job are calculated by formulas that cascade down the columns, but as a visual aid, the V'information may also be displayed as a Gantt chart. This is how you set it up: ^Seq          '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.['- to the right of the schedule make narrow columns and head them from hours 1 to 36 ^Jobs  ^job A  ^job C  ^job D  ^job E  ^job B'- enter this formulat'- set Format|Conditional Formatting|Pattern|Colour if the cell value =1, to emphasize the cell with a colourE'- copy the formula in F10, and paste it to the range F10:AO14 ^Hours          m'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)x'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. ^Start  @ &@ 0@ 8@ ^Stop$ @ $ &@ $ 0@ $ 8@ $ B@   < 2:(###;3 ####< W(###;3  < W(###;3  < *(###;3  < z(###;3  ('@IF(F$8>$D10#AND#F$8<=$E10,1,"")  < B:(###;3 ####< W(###;3  < W(###;3  < 6(###;3  < (###;3    < R:(###;3 ####< W(###;3  < W(###;3  < B(###;3  < (###;3    < b:(###;3 ####< *W(###;3  < W(###;3  < N(###;3  < (###;3     <  r:(###;3  ####<  6W(###;3  <  W(###;3  <  Z(###;3  <  (###;3     <  :(###;3  ####<  BW(###;3  <  W(###;3  <  f(###;3  <  (###;3     <  :(###;3  ####<  NW(###;3  <  W(###;3  <  r(###;3  <  (###;3     <  W(###;3  <  :(###;3  ####<  W(###;3  <  ~(###;3  <  (###;3      <  W(###;3  <  :(###;3  ####<  (###;3  <  (###;3  <  (###;3     < W(###;3  < :(###;3 ####< "(###;3  < (###;3  < (###;3     < W(###;3  < :(###;3 ####< .(###;3  < (###;3  < (###;3     < W(###;3  < ZW(###;3  < :(###;3 ####< (###;3  < (###;3     < W(###;3  < fW(###;3  < :(###;3 ####< (###;3  <  (###;3    < W(###;3  < rW(###;3  < ;(###;3 ####< (###;3  < (###;3    < W(###;3  < ~W(###;3  < ;(###;3 ####< (###;3  < "(###;3    <  W(###;3  < W(###;3  < ";(###;3 ####< (###;3  < .(###;3    < W(###;3  < W(###;3  < :(###;3  < 2;(###;3 ####< :(###;3    < "W(###;3  < W(###;3  < F(###;3  < B;(###;3 ####< F(###;3    < .W(###;3  < W(###;3  < R(###;3  < R;(###;3 ####< R(###;3    < :W(###;3  < W(###;3  < ^(###;3  < b;(###;3 ####< ^(###;3    < FW(###;3  < W(###;3  < j(###;3  < r;(###;3 ####< j(###;3    < RW(###;3  < W(###;3  < v(###;3  < ;(###;3 ####< v(###;3    < ^W(###;3  < W(###;3  < (###;3  < ;(###;3 ####< (###;3    < jW(###;3  < W(###;3  < (###;3  < ;(###;3 ####< (###;3    < vW(###;3  < W(###;3  < (###;3  < (###;3  < ;(###;3 ####  < W(###;3  < W(###;3  < (###;3  < (###;3  < ;(###;3 ####  < W(###;3  < W(###;3  < (###;3  < (###;3  < ;(###;3 ####   <  W(###;3  <  W(###;3  <  (###;3  <  (###;3  <  ;(###;3  #### ! <! W(###;3 ! <! &W(###;3 ! <! (###;3 ! <! (###;3 ! <! ;(###;3! #### " <" W(###;3 " <" 2W(###;3 " <" (###;3 " <" &(###;3 " <" <(###;3" #### # <# W(###;3 # <# >W(###;3 # <# (###;3 # <# 2(###;3 # <# <(###;3# #### $  <$ W(###;3 $ <$ JW(###;3 $ <$ (###;3 $ <$ >(###;3 $ <$ "<(###;3$ #### % !<% W(###;3 % <% VW(###;3 % <% (###;3 % <% J(###;3 % <% 2<(###;3% #### & "<& W(###;3 & <& bW(###;3 & <& (###;3 & <& V(###;3 & <& B<(###;3& #### ' #<' W(###;3 ' <' nW(###;3 ' <' (###;3 ' <' b(###;3 ' <' R<(###;3' #### ( $<( W(###;3 ( <( zW(###;3 ( <( (###;3 ( <( n(###;3 ( <( b<(###;3( ####) ') ') ') ') ' Section 5$u<7~I7,p !," !$+(&( '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 thed'work centre is available. Here is an example of a calendar in hours, and decimals of an hour0'(later we will examine dates and times):)^Period Number          G'Expressed as 12 hour clock times, the calendar looks like this:      F'We are counting hours into the schedule beginning at midnight.A'Each line is a working period, the breaks lie in between.l'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:I'If you start a 7 hour job at 8:30 AM, at what time will you stop?'You could do this:a*'With a bit of trial and error you can work out that the answer is at hour 16.5 or 4:30 pmS+'However, a more comprehensive calculation is set out on the next worksheet. 1^Begin    $@ +@ /@     UUUUUU?   UUUUUU?   ?   ?   UUUUUU?   !^Begin#!@$$@%+@&/@ ('Total 1^End     /@       ?   UUUUUU?   ?   ?   UUUUUU?   !^End # $ %/@&0@)'Working Hours$  $ @ $ @ $ @ $ @ $@ @@@@@!^Duration$#? $$@ $%@ $&? "(@P")^Working Hours so far (Cum)$  $ @ $ @ $ @ $ "@ $(@ @@@"@(@H 'establishes the beginning of the calendar at zero hour, midnight% 'work from 8:00 am to 10:00 amE 'a 15 min break, resume work at 10:15 am and work till 1:00 pm0 '30 min for lunch, then work till 3:30 pm? 'a 15 min break in the afternoon, then work till 6:00 pm3'an hour for dinner, then work till 10:00 pm Section 6$u<7~|Z7,p 000)  ""9'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 formulas 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 two4'working periods. Here is the calendar again:)^Period Number           "'Stages of the calculation:LtI8, is after period H begins3(8:30 is after period 1 beginssJN6* is after period H ends3&8:30 is after period 0 endss_OK? lies between the beginning and end of period H3>8:30 lies between the beginning and end of period 11PkSperiod H begins at "H: BH B0;3"period 1 begins at 8:000}@~iQperiod H ends at "H: BH B0;3 period 1 ends at 10:000Rtyou can start the job at "H: BH B0; because it is within a working period3Nyou can start the job at 8:30 because it is within a working perioddG'the number of hours from the start of the job to the next breakDlT0*cum hours at end of period H3&cum hours at end of period 22QLU=7the job starts at cum hour H of the calendar3:the job starts at cum hour 0.50 of the calendarrP4V<6the job stops at cum hour H of the calendar38the job stops at cum hour 7.50 of the calendarrE1+the job stops during period HhTHperiod H ends H working hours into the calendarM 9-period H ends at hour Hg!SGthe job will stop H hours before period H ends=")#the job will stop at hour H[$'With a series of substitutions, the 15 formulas can be condensed into 4 as follows: &'Calc1 ''Calc2 ('Calc3)'Job Stop)+'The meanings of the formulas are: -'Calc1 .'Calc2 /'Calc30'Job Stop 1^Begin    $@ +@ /@  `&L u'a+bbbT  9(%  ^)Jbb   -^3>-8:30 lies between the beginning and end of period 11.l_3:.the job starts at cum hour 0.50 of the calendarr/0 1^End     /@    'Starting a job at:'Work for:E&'@MATCH(E16,B9..B14)+(@MATCH(E16,B9..B14)=@MATCH(E16,C9..C14))U''@INDEX(E9..E14,0,B39)-(@INDEX(C9..C14,0,B39)-@MAX(@INDEX(B9..B14,0,B39),E16))!('@MATCH(B40+E17,E9..E14)+1;)'@INDEX(C9..C14,0,B41)-@INDEX(E9..E14,0,B41)+B40+E17)^Working Hours$ $ @ $ @ $ @ $ @ $ @ ")^Working Hours so far (Cum)$ $ @ $ @ $ @ $ "@ $ (@ !@ U=A/"H: BH B0;38:300 ^hours- 'change these figures and work through. 'the stages of the calculation, to gain( 'an understanding of the formulas-? - -? 1 @ b1$@ b%!@T$? 1@ b$? $@ 1  1 b1  b$! $" '@MATCH(E16,B9..B14)'@MATCH(E16,C9..C14)'+G21+(G21=G22)'@INDEX(B9..B14,0,G23)'@INDEX(C9..C14,0,G23)'@MAX(G24,E16)'+G25-G26'@INDEX(E9..E14,0,G23)'+G28-G27'+G29+E17'@MATCH(G30,E9..E14)+1'@INDEX(E9..E14,0,G31) '@INDEX(C9..C14,0,G31)!'+G32-G30"'+G33-G34 Section 7$u<7~d7,T * & 'Julian Datesd'It was Julius Caeser who first established the calendar based on 365 days per year with leapa'years, hence Julian dates. One of the reasons why spreadsheets are so good for scheduling4'is the way that they handle dates and times.a'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!g 'Keep hitting the F9 key to recalculate, and watch the clock change. Add 1 to it and you get thef'same time tomorrow. The really neat thing is that we only have one unit for measuring time, a]'day. We don't 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 Style|Number Format|Dates & Times/'Here are some different formats of NOW: 'and by using @DATEINFO4('Take a look also at the calendar functions.Q*'The spreadsheet takes care of the days in each month, and leap years etc. Kh@7Kh@ Kh@ Kh@ Kh@ Kh@ Kh@  Kh@ Kh@ Kh@ %"=  3"Wedd%#lqW  3#Wednesdayy%$=  3$Jull%%=  3%Julyy6&@"Qtr  H 3&Qtr 33# ' days since 1 January 1900 Section 8$u<7~4p7,W ރ ރGG h h  [!A ::@Arrow6 X {GX ? "!A rw@Arrow7 X {GX ? "!A  ::>@Arrow8 X {GX ? "!A  9 y@@Arrow9 X {GX ? "!B "@Arrow10 X {GX ? "!B d S'@Arrow11 X {GX ? "!B  eZ*@Arrow12 X {GX ? "!B  J ZU@Arrow13 X {GX ? "!B  WK@Arrow14 X {GX ? "!B ( ) @Arrow15 X {GX ? "!B * +@Arrow16 X {GX ? "\B4. .... ... + $ '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 to0 '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) ?   ) @   ) @   ) @   ) @   ) ?   ) @   ) @   ) @   ) @   ) ?   ) @   ) @   ) @   ) @   ) !?   ) "@   ) #@   ) $@   ) %@   ) &?   ) '@   ) (@   ) )@   ) *?   ) +@   ) ,@   ) -@   ) .@    0'A calendar for a live scheduling system may be up to several thousand rows long, so you may wish to replace the formulas with 1'values, after you have set it up, to save on memory and calculation time. You can allow for public holidays by deleting rows, andZ 2'you can allow for overtime by inserting rows or by extending the working periods. g 4'After deleting or inserting rows, don't forget to copy down the formula in the Cum Days column.  ^Day% B> 3 Monn% > 3 Monn% > 3 Monn% 2? 3 Monn%  3 Monn% V 3 Tuee%  3 Tuee%  3 Tuee%  3 Tuee%  3 Tuee% F 3 Wedd% v 3 Wedd%  3 Wedd% ֗ 3 Wedd%  3 Wedd% !6 3 !Thuu% "f 3 "Thuu% # 3 #Thuu% $Ƙ 3 $Thuu% % 3 %Thuu% && 3 &Frii% 'V 3 'Frii% ( 3 (Frii% ) 3 )Frii% * 3 *Monn% + 3 +Monn% ,F 3 ,Monn% -v 3 -Monn% . 3 .Monn@ 'These four columns are where you edit the date and time  ^Begin  ^Date @ @ @ @ @  @    @    @    @    @    @    @    @    @    @    ! @    " @    # @    $ @    % @    &@@    '@@    (@@    )@@    *@    +@    ,@    -@    .@      ^Time  VUUU?  VUUU?  ?  ?  UUUU?  VUUU?  VUUU?  ?  ?  UUUU?  VUUU?  VUUU?  ?  ? UUUU? ! VUUU? " VUUU? # ? $ ? % UUUU? & VUUU? ' VUUU? ( ? ) ? * VUUU? + VUUU? , ? - ? . UUUU?  ^End  ^Date @ @ @ @ @  @    @    @    @    @    @    @    @    @    @    ! @    " @    # @    $ @    % @    &@@    '@@    (@@    )@@    *@    +@    ,@    -@    .@      ^Time  ?  UUUU?  ?  ?  UUUU?  ?  UUUU?  ?  ?  UUUU?  ?  UUUU?  ?  ? UUUU? ! ? " UUUU? # ? $ ? % UUUU? & ? ' UUUU? ( ? ) ? * ? + UUUU? , ? - ? . UUUU?9 'These 3 columns are used by the calendar formulas  ^Begin$ @ $ @ $ @ $ @ $ UUUU@ $ @ $ @ $ @ $ @ $ UUUU@ $  @ $  @ $ @ $ @ $ UUUU@ $ !*@ $ "-@ $ #2@ $ $5@ $ %UUUU9@ $ &J@ $ 'M@ $ (R@ $ )U@ $ *@ $ +@ $ ,@ $ -@ $ .UUUU@   ^End$ UUUU@ $ UUUU@ $ @ $ @ $ UUUU@ $ UUUU@ $ UUUU@ $ @ $ @ $ UUUU@ $ UUUU @ $ UUUU@ $ @ $ @ $ UUUU@ $ !UUUU-@ $ "UUUU1@ $ #4@ $ $8@ $ %UUUU=@ $ &UUUUM@ $ 'UUUUQ@ $ (T@ $ )X@ $ *UUUU@ $ +UUUU@ $ ,@ $ -@ $ .UUUU@  ^Cum Days- PUUU?  - PUUU?  - ?  - ?  - ?  - ?  - SUUU?  - ?  - ?  - ?  - TUUU?  - *?  - ?  - ?  - ?  - !SUUU?  - "*?  - #?  - $?  - %?  - &@  - 'TUU@  - (?@  - )@  - *@@  - +SUU@  - ,?@  - -@  - .@  , 'Establish the first date as follows:, 'Establish the first time as follows:4 '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. 'type @TODAY to put in the current date8 'copy, then Edit|Paste Special|Formulas as values 'Style|Number Format|Date 'edit the date 'enter .25! 'Style|Number Format|Times 'edit the time  Section 9$u<7~Hy7,:u uuރ    ރ  [!B  :Q@Arrow17 X {GX ? "!B  d p@Arrow18 X {GX ? "!B  rb@Arrow19 X {GX ? "\B        # '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 daysS 'as it does on hours, so it can be applied to the Julian calendar like this: ^Seq           'As with the schedules in Section3 and 4, you can change the hours, or the start of the first job, or the sequence numbersF '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. 'Start of first job:  ^Jobs  ^job A  ^job C  ^job D  ^job E  ^job B '+C14/24 '@IF(J13,J13,E$6+F$6)8 '@MATCH(H14+D14,Section 8:I$19..Section 8:I$50)+1  ^Hours            ^Days  ?    UUUUUU?    ?    UUUUUU?    ?   #@  !^Start3 5UUUU@ ;3 5 ;3 5 ;3 5 ;3 5 ;K 'days are hours 24 - we need to convert to use the Julian calendarp 'if there is a stop time of the previous job, start then, if not use date and time of the first job E6+F6 +?  @ =UUUU@ = = = =  ^Calc1` L 111` L 111` L 111` L 111` L 111  ^Calc2u a+bbbT  111u a+bbbT  111u a+bbbT  111u a+bbbT  111u a+bbbT  111  ^Calc39 %  19 %  19 %  19 %  19 %  1C 'the calendar formulas have been explained in Section 6, butB 'note how the calendar on Section 8 is referenced from from 'this worksheet !^Stop^ EJbb   11^ EJbb   11^ EJbb   11^ EJbb   11^ EJbb   11  @ M M M M M  Section 10$u<7~7,:u uuރ     ރ [!B   rq3@Arrow20 X {GX ? "\H         ) '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: ^W/C ^1 ^1 ^1 ^2 ^2 ^2 '@IF(A14=A13,K13,F$6+G$6)x 'In this example a job can be assigned to Work Centre 1 OR Work Centre 2, and then assigned a sequence within it.L '(We shall look one job passing through multiple work centres later) Z 'Note that the 1's and 2's in the Work Centre column are codes rather than numbers.X 'You can now change both the work centre number and the sequence number, and sort  ^Seq             '- Sort By W/C Ascending '- Then By Seq Ascending 'Start of first job:  ^Jobs  ^job A  ^job C  ^job D  ^job E  ^job B  ^job F  ^Hours             ^Days  ?    UUUUUU?    ?    UUUUUU?    ?    ?  _ '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 S@  !^Start; eUUUU@'  ;; e'  ;; e'  ;; eUUUU@'  ;; e'  ;; e'  ; [?  @ mUUUU@ m m mUUUU@ m m  ^Calc1` L 111` L 111` L 111` L 111` L 111` L 111  ^Calc2u a+bbbT  111u a+bbbT  111u a+bbbT  111u a+bbbT  111u a+bbbT  111u a+bbbT  111 ^Calc39 %  19 %  19 %  19 %  19 %  19 %  1 !^Stop^ uJbb   11^ uJbb   11^ uJbb   11^ uJbb   11^ uJbb   11^ uJbb   11  @ } } } } } }  Section 11$u<7~7, %%%%%5 'Joining Text Together and Indirect References   y'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   y'The "+" sign is used to add numbers together, but the "&" sign is used to join text together or join text with numbers, e.g.    x  5 y'this may look better with a space in between:    x  G y'numbers need to be converted to text before thay can be joined:    x  x  x 'Indirect references  x  x  xT y'we can add text together to make something that looks like a cell reference:  x  x  xD y'to make it behave like a cell reference, add the @@ function  x  x  : y'a direct reference to a cell on another worksheet:    !x  "/ #y'an indirect reference to the same cell:  $  %x  x    x    x    x    y'John    x    y'John    x   y'Section  x  x  x  x  z  x  x  x  y'B  x  x  x  y'B    x    !x  "  #x  $ %y'Section 4  x    x    x    x    y'Smith    x    y'Smith    x                        x  x  x      x   !}`3 !yA Simple Gantt Chartt  "  #x  $# %}T} :A1s3 %yA Simple Gantt Chartt  x    x    x    x  $ }qW3 yJohnSmithh    x  ( }qW 3 yJohn Smithh    x  2 }qW H3 ySection 111  x  x  x  x  x  x  x  x. ƛH3 B222      / 8@Hs    x    !x  "  #x  $  %x  x    x    x    x   y'+B9&C9    x   y'+B13&" "&C13    x   y'+B17&" "&@STRING(C17,0)  x  x  x  x  x  x  x  x '+B26&@STRING(C26,0)  x  x  x '@@(B30&@STRING(C30,0))    x   !y'+Section 4:A1  "  #x  $ %y'@@(B38&":A1")  Section 12$u<7~7,   uG   u [!B p E x=@Arrow21 X {GX ? "!B    @Arrow22 X {GX ? "\N       / 'Separate Calendars for each Work Centres 'The next two worksheets contain calendars, and the following table assignes a calendar to each work centre: ^W/C ^1 ^1 ^1 ^2 ^2 ^2  ^W/C  ^1  ^2  ^Seq              '@VLOOKUP(A17,B$6..C$7,1)7 '@MATCH(J17+F17,@@(C17&":I$6.."&C17&":I$100"))+1 ^Calendar ^Calendar 1 ^Calendar 2 ^Calendar+ ^rWU3 Calendar 11+ trWU3 Calendar 11+ VU3 Calendar 11+ lU3 Calendar 22+ U3 Calendar 22+ U3 Calendar 22 'Start of first job:  ^Jobs  ^job A  ^job C  ^job D  ^job E  ^job B  ^job F  ^Hours              ^Days  ?    UUUUUU?    ?    UUUUUU?    ?    ?  _ ' look up the work centre in the table above, and return the contents of the 2nd column @  !^Start; UUUU@'  ;; @'  ;; @'  ;; UUUU@'  ;; VUUU@'  ;; XUUU.@'  ; ?  @ UUUU@ @ @ UUUU@ VUUU@ XUUU.@  ^Calc1 @[:G$6..:G$100s:G$6..:G$100s:H$6..:H$100s  @[:G$6..:G$100s:G$6..:G$100s:H$6..:H$100s  "@[:G$6..:G$100s:G$6..:G$100s:H$6..:H$100s  @[:G$6..:G$100s:G$6..:G$100s:H$6..:H$100s  @[:G$6..:G$100s:G$6..:G$100s:H$6..:H$100s  *@[:G$6..:G$100s:G$6..:G$100s:H$6..:H$100s 9 'an example of indirect reference to the calendars ^Calc2 ?j:I$6..:I$100sb:H$6..:H$100sb:G$6..:G$100sbT   ?j:I$6..:I$100sb:H$6..:H$100sb:G$6..:G$100sbT   SUUU?j:I$6..:I$100sb:H$6..:H$100sb:G$6..:G$100sbT   RUUU?j:I$6..:I$100sb:H$6..:H$100sb:G$6..:G$100sbT   TUUU?j:I$6..:I$100sb:H$6..:H$100sb:G$6..:G$100sbT   ?j:I$6..:I$100sb:H$6..:H$100sb:G$6..:G$100sbT   ^Calc3P @<$ :I$6..:I$100s P "@<$ :I$6..:I$100s P &@<$ :I$6..:I$100s P @<$ :I$6..:I$100s P *@<$ :I$6..:I$100s P .@<$ :I$6..:I$100s  !^Stop @xH:H$6..:H$100sb:I$6..:I$100sb    @xH:H$6..:H$100sb:I$6..:I$100sb    @xH:H$6..:H$100sb:I$6..:I$100sb    VUUU@xH:H$6..:H$100sb:I$6..:I$100sb    XUUU.@xH:H$6..:H$100sb:I$6..:I$100sb    XUUU7@xH:H$6..:H$100sb:I$6..:I$100sb     @ @ @ @ VUUU@ XUUU.@ XUUU7@  Calendar 1$u<7~7, uރuރ6ccccccccc&'Calendar with an Evening Shift ^Hours)?   )@   )@   )@   ) @   ) ?   ) @   ) @   ) @   )@   )?   )@   )@   )@   )@   )?   )@   )@   )@   )@   )?   )@   )@   )@   )?   )@   )@   ) @   )!@   )"?   )#@   )$@   )%@   )&@   )'?   )(@   ))@   )*@   )+@   ),?   )-@   ).@   )/@   )0@   )1?   )2@   )3@   )4@   )5?   )6@   )7@   )8@   )9@   ):?   );@   )<@   )=@   )>@   )??   )@@   )A@   )B@   )C@   )D?   )E@   )F@   )G@   )H@   )I?   )J@   )K@   )L@   )M?   )N@   )O@   )P@   )Q@   )R?   )S@   )T@   )U@   )V@   )W?   )X@   )Y@   )Z@   )[@   )\?   )]@   )^@   )_@   )`@   )a?   )b@   )c@    ^Day%Z 3Monn% 3Monn% 3Monn%J 3Monn%  3 Monn%  3 Tuee% : 3 Tuee%  3 Tuee%  3 Tuee%* 3Tuee%z 3Wedd% 3Wedd% 3Wedd%j 3Wedd% 3Wedd%  3Thuu%Z 3Thuu% 3Thuu% 3Thuu%b 3Thuu% 3Frii% 3Frii%R 3Frii% 3Frii% 3Monn%B 3Monn% 3Monn%  3 Monn%!2 3!Monn%" 3"Tuee%# 3#Tuee%$" 3$Tuee%%r 3%Tuee%& 3&Tuee%' 3'Wedd%(b 3(Wedd%) 3)Wedd%* 3*Wedd%+ 3+Wedd%, 3,Thuu%-j 3-Thuu%. 3.Thuu%/  3/Thuu%0Z 30Thuu%1 31Frii%2 32Frii%3J 33Frii%4 34Frii%5 35Monn%6: 36Monn%7 37Monn%8 38Monn%9* 39Monn%:z 3:Tuee%; 3;Tuee%< 3<Tuee%=j 3=Tuee%> 3>Tuee%? 3?Wedd%@f 3@Wedd%A 3AWedd%B 3BWedd%CV 3CWedd%D 3DThuu%E 3EThuu%FF 3FThuu%G 3GThuu%H 3HThuu%I6 3IFrii%J 3JFrii%K֏ 3KFrii%L& 3LFrii%Mv 3MMonn%NƐ 3NMonn%O 3OMonn%Pf 3PMonn%Q 3QMonn%R 3RTuee%S 3STuee%TF 3TTuee%U 3UTuee%V 3VTuee%W6 3WWedd%X 3XWedd%Y 3YWedd%Z& 3ZWedd%[v 3[Wedd%\ 3\Thuu%] 3]Thuu%^f 3^Thuu%_ 3_Thuu%` 3`Thuu%aV 3aFrii%b 3bFrii%c 3cFrii ^Begin ^Date@@@@ @ @ @ @ @@@@@@@ @ @ @ @ @@@@@@@@@@@@ @!@"@#@$@%@&@'@(@)@*@+@,@-@.@/@0@1 @2 @3 @4 @5@6@7@8@9@:@;@<@=@>@?@@@A@B@C@D@E@F@G@H@I@J@K@L@M`@N`@O`@P`@Q`@R@S@T@U@V@W@X@Y@Z@[@\@]@^@_@`@a@b@c@  ^Time VUUU? VUUU? ? ? UUUU? VUUU? VUUU? ? ? UUUU? VUUU? VUUU? ? ? UUUU? VUUU? VUUU? ? ? UUUU? VUUU? VUUU? ? ? VUUU? VUUU? ? ?! UUUU?" VUUU?# VUUU?$ ?% ?& UUUU?' VUUU?( VUUU?) ?* ?+ UUUU?, VUUU?- VUUU?. ?/ ?0 UUUU?1 VUUU?2 VUUU?3 ?4 ?5 VUUU?6 VUUU?7 ?8 ?9 UUUU?: VUUU?; VUUU?< ?= ?> UUUU?? VUUU?@ VUUU?A ?B ?C UUUU?D VUUU?E VUUU?F ?G ?H UUUU?I VUUU?J VUUU?K ?L ?M VUUU?N VUUU?O ?P ?Q UUUU?R VUUU?S VUUU?T ?U ?V UUUU?W VUUU?X VUUU?Y ?Z ?[ UUUU?\ VUUU?] VUUU?^ ?_ ?` UUUU?a VUUU?b VUUU?c ? ^End ^Date@@@@ @ @ @ @ @@@@@@@ @ @ @ @ @@@@@@@@@@@@ @!@"@#@$@%@&@'@(@)@*@+@,@-@.@/@0@1 @2 @3 @4 @5@6@7@8@9@:@;@<@=@>@?@@@A@B@C@D@E@F@G@H@I@J@K@L@M`@N`@O`@P`@Q`@R@S@T@U@V@W@X@Y@Z@[@\@]@^@_@`@a@b@c@  ^Time ? UUUU? ? ? UUUU? ? UUUU? ? ? UUUU? ? UUUU? ? ? UUUU? ? UUUU? ? ? UUUU? ? UUUU? ? ? ? UUUU? ? ?! UUUU?" ?# UUUU?$ ?% ?& UUUU?' ?( UUUU?) ?* ?+ UUUU?, ?- UUUU?. ?/ ?0 UUUU?1 ?2 UUUU?3 ?4 ?5 ?6 UUUU?7 ?8 ?9 UUUU?: ?; UUUU?< ?= ?> UUUU?? ?@ UUUU?A ?B ?C UUUU?D ?E UUUU?F ?G ?H UUUU?I ?J UUUU?K ?L ?M ?N UUUU?O ?P ?Q UUUU?R ?S UUUU?T ?U ?V UUUU?W ?X UUUU?Y ?Z ?[ UUUU?\ ?] UUUU?^ ?_ ?` UUUU?a ?b UUUU?c ? ^Begin$@ $@ $@ $@ $ UUUU@ $ @ $ @ $ @ $ @ $UUUU@ $ @ $ @ $@ $@ $UUUU@ $*@ $-@ $2@ $5@ $UUUU9@ $J@ $M@ $R@ $U@ $@ $@ $@ $ @ $!UUUU@ $"@ $#@ $$@ $%@ $&UUUU@ $'@ $(@ $)@ $*@ $+UUUU@ $, @ $- @ $.@ $/@ $0UUUU@ $1*@ $2-@ $32@ $45@ $5@ $6@ $7@ $8@ $9UUUU@ $:@ $;@ $<@ $=@ $>UUUU@ $?@ $@@ $A@ $B@ $CUUUU@ $D@ $E@ $F@ $G@ $HUUUU@ $I @ $J @ $K@ $L@ $Mj@ $Nm@ $Or@ $Pu@ $QUUUUy@ $R@ $S@ $T@ $U@ $VUUUU@ $W@ $X@ $Y@ $Z@ $[UUUU@ $\@ $]@ $^@ $_@ $`UUUU@ $a@ $b@ $c@  ^End$UUUU@ $UUUU@ $@ $@ $ UUUU@ $ UUUU@ $ UUUU@ $ @ $ @ $UUUU@ $UUUU @ $UUUU@ $@ $@ $UUUU@ $UUUU-@ $UUUU1@ $4@ $8@ $UUUU=@ $UUUUM@ $UUUUQ@ $T@ $X@ $UUUU@ $UUUU@ $@ $ @ $!UUUU@ $"UUUU@ $#UUUU@ $$@ $%@ $&UUUU@ $'UUUU@ $(UUUU@ $)@ $*@ $+UUUU@ $,UUUU @ $-UUUU@ $.@ $/@ $0UUUU@ $1UUUU-@ $2UUUU1@ $34@ $48@ $5UUUU@ $6UUUU@ $7@ $8@ $9UUUU@ $:UUUU@ $;UUUU@ $<@ $=@ $>UUUU@ $?UUUU@ $@UUUU@ $A@ $B@ $CUUUU@ $DUUUU@ $EUUUU@ $F@ $G@ $HUUUU@ $IUUUU @ $JUUUU@ $K@ $L@ $MUUUUm@ $NUUUUq@ $Ot@ $Px@ $QUUUU}@ $RUUUU@ $SUUUU@ $T@ $U@ $VUUUU@ $WUUUU@ $XUUUU@ $Y@ $Z@ $[UUUU@ $\UUUU@ $]UUUU@ $^@ $_@ $`UUUU@ $aUUUU@ $bUUUU@ $c@       !"#$%&'()*+,-./0123456789:;<=>?@ABCDEFGHIJKLMNOPQRSTUVWXYZ[\]^_`abcdefghijklmnopqrstuvwxyz{|}~^Cum Days-PUUU?  -PUUU?  -?  -?  - ?  - ?  - SUUU?  - ?  - ?  -?  -TUUU?  -*?  -?  -?  -?  -SUUU?  -*?  -?  -?  -?  -@  -TUU@  -?@  -@  -@@  -SUU@  -?@  - @  -!@  -"@  -#SUU@  -$@? @  -%@ @  -&@ @  -' @  -(@SUU @  -)? @  -* @  -+@  -,@  --J@  -.@  -/@  -0@  -1 TUU@  -2`J@  -3@  -4@  -5TUUU@  -6@@  -7@  -8@  -9@  -:SUUU@  -; @  -<@  -=@  ->@  -?SUUU@  -@@  -A`@  -B`@  -C`@  -DSUUU@  -Eਪ@  -F@@  -G@@  -H@@  -ISUUU@  -J@  -K @  -L @  -M`SUU@  -NJ@  -O@  -P@  -Q@  -R@SUU@  -SJ@  -T@  -U@  -V? @  -Wj @  -X0TUU @  -Y @  -Z @  -[?!@  -\j!@  -] TUU!@  -^!@  -_!@  -`?"@  -apj"@  -bTUU"@  -c"@   Calendar 2$u<7~̨7, uuރ ރރ6hhhhhhhhhC'Calendar Without an Evening Shift and Early End on a Friday ^Hours)?   @@@ ? @ @ @ ?@@@?@@@?@@??@@@?@@ @!?"@#@$@%?&@'@(@)?*@+@,?-?.@/@0@1?2@3@4@5?6@7@8@9?:@;@<@=?>@?@@?A?B@C@D@E?F@G@H@I?J@K@L@M?N@O@P@Q?R@S@T?U?V@W@X@Y?Z@[@\@]?^@_@`@a?b@c@d@e?f@g@h? ^Day% 3Monn% 3Monn% 3Monn% 3Monn% ~ 3 Tuee%  3 Tuee% ^ 3 Tuee%  3 Tuee% > 3 Wedd% 3Wedd% 3Wedd% 3Wedd% 3Thuu%n 3Thuu% 3Thuu%N 3Thuu% 3Frii% 3Frii% 3Frii% 3Frii%n 3Monn% 3Monn%N 3Monn% 3Monn%. 3Tuee% 3Tuee% 3Tuee% ~ 3 Tuee%! 3!Wedd%"^ 3"Wedd%#H 3#Wedd%$H 3$Wedd%%jI 3%Thuu%&I 3&Thuu%'JJ 3'Thuu%(J 3(Thuu%)*K 3)Frii%*K 3*Frii%+ L 3+Frii%,zL 3,Frii%-L 3-Monn%.ZM 3.Monn%/M 3/Monn%0:N 30Monn%1N 31Tuee%2 32Tuee%3& 33Tuee%4 34Tuee%5 35Wedd%6v 36Wedd%7 37Wedd%8V 38Wedd%9ƃ 39Thuu%:6 3:Thuu%; 3;Thuu%< 3<Thuu%= 3=Frii%> 3>Frii%?f 3?Frii%@ 3@Frii%A 3AMonn%Bb 3BMonn%CҊ 3CMonn%DB 3DMonn%E 3ETuee%F" 3FTuee%G 3GTuee%H 3HTuee%Ir 3IWedd%J 3JWedd%KR 3KWedd%LŽ 3LWedd%M2 3MThuu%N 3NThuu%O 3OThuu%Pn 3PThuu%Q 3QFrii%RN 3RFrii%S 3SFrii%T. 3TFrii%U 3UMonn%V 3VMonn%W~ 3WMonn%X 3XMonn%Y^ 3YTuee%Z 3ZTuee%[> 3[Tuee%\ 3\Tuee%] 3]Wedd%^f 3^Wedd%_ 3_Wedd%`F 3`Wedd%a 3aThuu%b& 3bThuu%c 3cThuu%d 3dThuu%ev 3eFrii%f 3fFrii%gV 3gFrii%h 3hFrii ^Begin ^Date@@@@ @ @ @ @ @@@@ @ @ @ @@@@@@@@@@@@@@@@ @!@"@#@$@%@&@'@(@) @* @+ @, @-@.@/@0@1@2@3@4@5@6@7@8@9@:@;@<@=@>@?@@@A`@B`@C`@D`@E@F@G@H@I@J@K@L@M@N@O@P@Q@R@S@T@U@@V@@W@@X@@Y`@Z`@[`@\`@]@^@_@`@a@b@c@d@e@f@g@h@  ^Time VUUU? VUUU? ? ? VUUU? VUUU? ? ? VUUU? VUUU? ? ? VUUU? VUUU? ? ? VUUU? VUUU? ? ? VUUU? VUUU? ? ? VUUU? VUUU? ? ?! VUUU?" VUUU?# ?$ ?% VUUU?& VUUU?' ?( ?) VUUU?* VUUU?+ ?, ?- VUUU?. VUUU?/ ?0 ?1 VUUU?2 VUUU?3 ?4 ?5 VUUU?6 VUUU?7 ?8 ?9 VUUU?: VUUU?; ?< ?= VUUU?> VUUU?? ?@ ?A VUUU?B VUUU?C ?D ?E VUUU?F VUUU?G ?H ?I VUUU?J VUUU?K ?L ?M VUUU?N VUUU?O ?P ?Q VUUU?R VUUU?S ?T ?U VUUU?V VUUU?W ?X ?Y VUUU?Z VUUU?[ ?\ ?] VUUU?^ VUUU?_ ?` ?a VUUU?b VUUU?c ?d ?e VUUU?f VUUU?g ?h ? ^End ^Date@@@@ @ @ @ @ @@@@ @ @ @ @@@@@@@@@@@@@@@@ @!@"@#@$@%@&@'@(@) @* @+ @, @-@.@/@0@1@2@3@4@5@6@7@8@9@:@;@<@=@>@?@@@A`@B`@C`@D`@E@F@G@H@I@J@K@L@M@N@O@P@Q@R@S@T@U@@V@@W@@X@@Y`@Z`@[`@\`@]@^@_@`@a@b@c@d@e@f@g@h@  ^Time ? UUUU? ? ? ? UUUU? ? ? ? UUUU? ? ? ? UUUU? ? ? ? UUUU? ? ? ? UUUU? ? ? ? UUUU? ? ?! ?" UUUU?# ?$ ?% ?& UUUU?' ?( ?) ?* UUUU?+ ?, ?- ?. UUUU?/ ?0 ?1 ?2 UUUU?3 ?4 ?5 ?6 UUUU?7 ?8 ?9 ?: UUUU?; ?< ?= ?> UUUU?? ?@ ?A ?B UUUU?C ?D ?E ?F UUUU?G ?H ?I ?J UUUU?K ?L ?M ?N UUUU?O ?P ?Q ?R UUUU?S ?T ?U ?V UUUU?W ?X ?Y ?Z UUUU?[ ?\ ?] ?^ UUUU?_ ?` ?a ?b UUUU?c ?d ?e ?f UUUU?g ?h ? ^Begin$@ $@ $@ $@ $ @ $ @ $ @ $ @ $  @ $ @ $@ $@ $*@ $-@ $2@ $5@ $J@ $M@ $R@ $U@ $@ $@ $@ $@ $@ $@ $@ $ @ $!@ $"@ $#@ $$@ $% @ $& @ $'@ $(@ $)*@ $*-@ $+2@ $,5@ $-@ $.@ $/@ $0@ $1@ $2@ $3@ $4@ $5@ $6@ $7@ $8@ $9@ $:@ $;@ $<@ $= @ $> @ $?@ $@@ $Aj@ $Bm@ $Cr@ $Du@ $E@ $F@ $G@ $H@ $I@ $J@ $K@ $L@ $M@ $N@ $O@ $P@ $Q@ $R@ $S@ $T@ $UJ@ $VM@ $WR@ $XU@ $Yj@ $Zm@ $[r@ $\u@ $]@ $^@ $_@ $`@ $a@ $b@ $c@ $d@ $e@ $f@ $g@ $h@  ^End$UUUU@ $UUUU@ $@ $@ $ UUUU@ $ UUUU@ $ @ $ @ $ UUUU @ $UUUU@ $@ $@ $UUUU-@ $UUUU1@ $4@ $8@ $UUUUM@ $UUUUQ@ $T@ $V@ $UUUU@ $UUUU@ $@ $@ $UUUU@ $UUUU@ $@ $ @ $!UUUU@ $"UUUU@ $#@ $$@ $%UUUU @ $&UUUU@ $'@ $(@ $)UUUU-@ $*UUUU1@ $+4@ $,6@ $-UUUU@ $.UUUU@ $/@ $0@ $1UUUU@ $2UUUU@ $3@ $4@ $5UUUU@ $6UUUU@ $7@ $8@ $9UUUU@ $:UUUU@ $;@ $<@ $=UUUU @ $>UUUU@ $?@ $@@ $AUUUUm@ $BUUUUq@ $Ct@ $Dx@ $EUUUU@ $FUUUU@ $G@ $H@ $IUUUU@ $JUUUU@ $K@ $L@ $MUUUU@ $NUUUU@ $O@ $P@ $QUUUU@ $RUUUU@ $S@ $T@ $UUUUUM@ $VUUUUQ@ $WT@ $XX@ $YUUUUm@ $ZUUUUq@ $[t@ $\x@ $]UUUU@ $^UUUU@ $_@ $`@ $aUUUU@ $bUUUU@ $c@ $d@ $eUUUU@ $fUUUU@ $g@ $h@ ^Cum Days-PUUU?  PUUU??? RUUU? SUUU? ? ? ?RUUU???SUUU?*???SUUU?*??SUUU???@@@@@SUUU@@?@@ @!SUUU@"?@#@$@%@SUUU@&? @' @( @)SUUU @*? @+@ @,SUUU @- @.@/RUU@0`*@1@2SUU@3@J@4@@5@6SUUu@7 @8 *@9`@:SUU@;J@<@=@@>SUUu@?ਪ@@@@ASUUU@B@C @D @E`SUU@FJ@G@H@I@SUUU@J@K@L@M SUU@N`J@O@P@QSUUU@R@@S@TSUUU@U@@V@WRUUu@XRUU@Y *@Z`@[RUU@\`* @]TUUU @^ @_P @`P @aSUU!@bO!@c@z!@d@!@eSUU!@f"@g0:"@hSUUU"@ Section 13$u<7~7, : W uuuuu u  6     )'Repetitive Production and Set-upsg'So far we have accepted that a job has a duration of say, 7 hours, and we haven't worried abouth'how the 7 hours has been estimated. Let's say the first job is to set up a work centre and thenh'produce 500 of Product A. Work Centre 1 is an old machine which runs slowly, but is quick to sete'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:@'The left hand side of the schedule would look like this: ^W/C       "'@VLOOKUP(A26,B$11..C$12,1)$'@VLOOKUP(C26,E$11..G$14,A26)'+D26/F26 '+E26+G26  ^W/C     ^Seq        ^Set up hours ?  ^Product'Prod D'Prod B'Prod A'Prod B'Prod C'Prod D ^Qty  ^  ,  L ^Product 'Prod A 'Prod B 'Prod C 'Prod D^Set up hours+?U +?U +?U +?U +@U +@U ^Units per hour  ^W/C 1  P  7  H  A^Units per hour/@P@U  /K@U  /T@U  /K@U  /`@U  /[@U  +'- look up the work centre, offset 16'- look up the product, offset 1 for W/C 1, and' 2 for W/C 29'- run hours is quantity divided by units per hour' '- total hour is set up plus run   ^W/C 2   i   n^Run hours$ى؉@ $tE]t@ $@ $E]t@ $88&@ $$@  ^Total Hours$NN@ $tE]t@ $@ $E]t@ $88,@ $*@   ^Days ;;?   M6dM?   UUUUUU?   >?    %?   UUUUUU?    Section 14$u<7~,7,  W:u::::u: : : : [!@  u@Line1 7 ZG7 ? "!@  u@Line2 7 ZG7 ? "\B+ %  %       'A Set-up Matrixe'Often the time it takes to set up a work centre is dependant on the previous job. For exampleg'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. Similard'examples exist in printing and food processing and with the shut height of presses. In thesee'industries the sequence of the jobs will effect the amount of time the work centres are beingC'set up, and therefore the productivity of the work centres.a 'The set-up times between products, or product groups is expressed in a matrix as follows:7'We have created 2 named ranges with Range|Name:_'Use the navigator box to the left of the formula bar to see where the named ranges are.K'The problem is to retrieve a set-up time given the following values'Work Centre'Previous product'This Product'Here is the formula:9#'and now with an indirect reference to the matrix:f('If we apply this formula to the first product scheduled on the work centre, then it won't workb)'because there isn't a previous product. We will assume that the first product scheduled oni*'the work centre is already running, so the set-up time is zero. We will test to see if it is the -+'first product using the @IF function. 'Work Centre 1 - minutes" ' to from'Prod A'Prod B'Prod C'Prod D'MATRIX_1'MATRIX_24!? E%?1MATRIX_Hs ^Prod A   @ ^Prod B?  ? 'Prod D'Prod B!!'@XINDEX(MATRIX_1,D30,D29)5%'@XINDEX(@@("MATRIX_"&@STRING(D28,0)),D30,D29) ^Prod C?? ? ^Prod D? ?  'Work Centre 2 - minutes" ' to from'Prod A'Prod B'Prod C'Prod D ^Prod A @@   ^Prod B ?   @ @  ^Prod C ? ?    @  ^Prod D @ @ ?   Section 15$u<7~ 7,W uuuu u [!B  X W X@Arrow23 X {GX ? "\6        *'Applying the Set-up Matrix Formulag'Here is the schedule from Section 13 again, but this time the set-up times are derived from the'matrixes: ^W/C         D'@IF(A12=A11,@XINDEX(@@("MATRIX_"&@STRING(A12,0)),C12,C11),0)e'Try changing the sequence numbers and sorting to see if you can reduce the total set-up hours ^Seq         ^Product'Prod D'Prod B'Prod A 'Prod B 'Prod C 'Prod D 'Totals: ^Qty  ^   ,    L^Set up hours^J,MATRIX_Hs;^?J,MATRIX_Hs;^@J,MATRIX_Hs;^ ?J,MATRIX_Hs;^ J,MATRIX_Hs;^ ?J,MATRIX_Hs;" @P^Units per hour/@P@U  /K@U  /T@U  / K@U  / `@U  / [@U  ^Run hours$ى؉@ $tE]t@ $@ $ E]t@ $ 88&@ $ $@ " jD@P^Total Hours$ى؉@ $袋.: @ $@ $ E]t@ $ 88&@ $ '@  ^Days ii?   .?   UUUUUU?    |?    Kh/?    UUUUUU?   Section 16$u<7~7U,h:uރ  ރ ރ  :::::::::::::::::: [!@  K 6@Text1 L o`L ? Day Arial"!@  K )@Text2 L o`L ? Day Arial"!@   > (@Text3 L o`L ? Day Arial"!@ Q @Text4 L o`L ? Job Arial"!@  }!@Text5 L o`L ? Job Arial"!@ E  !@Text6 L o`L ? Job Arial"!@   !@Text7 L o`L ? Job Arial"!@ 2 (@Text8 L o`L ? Day Arial"!@ j&"@Text9 L o`L ? Job Arial"!A 8@Text10 L o`L ? Day Arial"!A 8@Text11 L o`L ? Day Arial"!A  !@Text12 L o`L ? Job Arial"!B  dt@Arrow24 X {GX ? "!B pZ@Arrow25 X {GX ? "!B w dy @Arrow26 X {GX ? "!B !E @Arrow27 X {GX ? "!B ( +} M@Arrow28 X {GX ? "!B ( , s@Arrow29 X {GX ? "\0(!+#(!(,(!(!( !( ( !( ( ((((((((((((((((((%'Gantt Chart 2 - Hours per Day'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:K!@7  ;   !K !@7  ;   C!@/ ;  >!@* ;  'Start of first job: !^Seq # $ % & ' ( *'Hours into the schedule: +'+N40,'+M41+C41Z.'Note that columns D,E,H,I and J containing the calendar formulas, have been hiddenX/'Change the hours or the sequence, then sort, to see how the Gantt chart responds0'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:'@VLOOKUP(F32,Calendar 2:$G$6..Calendar 2:$I$110,2) !^Jobs #^job A $^job B %^job C &^job D '^job E (^job F !^Hrs# %@ $ % @ & ' 5@ ( 7+'start hour is the stop hour of the previous job#^Calendar 2$^Calendar 2%^Calendar 2&^Calendar 2'^Calendar 2(^Calendar 2 !^Days #?   $UUUUUU?   %UUUUUU?   &UUUUUU?   '?   (?  @ !!^Start+#@;+$ @;+%XUUU+@;+&YUUU5@;+'@;+(]UUU @;.,'stop hour is start hour plus job hours3'@IF($N36>O$30#AND#$N36=O$30#AND#$M36=O$30#AND#$N36=P$30,"",A15)_'if the job stops before the day begins, or starts after the day ends, blank, else above='The 4 IF statements above have been "nested" into oneUUUUUU? !@#@$ @%XUUU+@&YUUU5@'@(]UUU @ !^Calc1#@[:G$6..:G$100s:G$6..:G$100s:H$6..:H$100s $ @[:G$6..:G$100s:G$6..:G$100s:H$6..:H$100s %(@[:G$6..:G$100s:G$6..:G$100s:H$6..:H$100s &.@[:G$6..:G$100s:G$6..:G$100s:H$6..:H$100s '6@[:G$6..:G$100s:G$6..:G$100s:H$6..:H$100s (@@[:G$6..:G$100s:G$6..:G$100s:H$6..:H$100s  !^Calc2#?j:I$6..:I$100sb:H$6..:H$100sb:G$6..:G$100sbT  $?j:I$6..:I$100sb:H$6..:H$100sb:G$6..:G$100sbT  %UUUU?j:I$6..:I$100sb:H$6..:H$100sb:G$6..:G$100sbT  &?j:I$6..:I$100sb:H$6..:H$100sb:G$6..:G$100sbT  '@j:I$6..:I$100sb:H$6..:H$100sb:G$6..:G$100sbT  (@UUU@j:I$6..:I$100sb:H$6..:H$100sb:G$6..:G$100sbT   !^Calc3P # @<$ :I$6..:I$100s P $(@<$ :I$6..:I$100s P %.@<$ :I$6..:I$100s P &6@<$ :I$6..:I$100s P '@@<$ :I$6..:I$100s P (E@<$ :I$6..:I$100s    !!^Stop #% @xH:H$6..:H$100sb:I$6..:I$100sb    $%XUUU+@xH:H$6..:H$100sb:I$6..:I$100sb    %%YUUU5@xH:H$6..:H$100sb:I$6..:I$100sb    &%@xH:H$6..:H$100sb:I$6..:I$100sb    '%]UUU @xH:H$6..:H$100sb:I$6..:I$100sb    (%`UUU@xH:H$6..:H$100sb:I$6..:I$100sb     !@ #- @ $-XUUU+@ %-YUUU5@ &-@ '-]UUU @ (-`UUU@D '(@VLOOKUP(Q32,Calendar 2:$G$5..Calendar 2:$I$110,2)-$M32)*24\ 'hours into the schedule - cumulative day less 1st cumulative day, converted to hours '+O32+1+ ?Um !^Start hr #5 $5%@ %52@ &59@ '5D@ (5@O@3 'look up the cumulative days in the calendar 'previous day plus 1 !^Stop hr$ #5%@ $ $52@ $ %59@ $ &5D@ $ '5@O@ $ (5`U@ 8$U  m u@%  3 Tuee$!!@ #!@+  ;;;;         !$N+  ;;;;         !3 $%Z+  ;;;;         !3 %&+  ;;;;         !3 &'+  ;;;;         !3 '(F+  ;;;;         !3 (8!@$U  m  u@  %  3 Wedd$!!@ # ?+  ;;;;         !$@+  ;;;;         !%f+  ;;;;         !3 %&&+  ;;;;         !3 &'+  ;;;;         !3 '(R+  ;;;;         !3 (81@$U  m  u @  %  3 Thuu$!!@ #+  ;;;;         !3 #$0?+  ;;;;         !%@+  ;;;;         !&?+  ;;;;         !'+  ;;;;         !3 '(^+  ;;;;         !3 (8:@$U  m  u@@  %  3 Frii$!@ #+  ;;;;         !3 #$Z+  ;;;;         !3 $%r+  ;;;;         !3 %&@+  ;;;;         !'+  ;;;;         !3 '(j+  ;;;;         !3 (8A@$U  m  u`@  % & 3 Satt$! #+  ;;;;         !3 #$f+  ;;;;         !3 $%~+  ;;;;         !3 %&+  ;;;;         !'+  ;;;;         !3 '(v+  ;;;;         !3 (8A@$U  m  u@  % 6 3 Sunn$! #+  ;;;;         !3 #$r+  ;;;;         !3 $%+  ;;;;         !3 %&+  ;;;;         !'+  ;;;;         !3 '(+  ;;;;         !3 (8A@$U  m  u@  % F 3 Monn$!!@ #+  ;;;;         !3 #$~+  ;;;;         !3 $%+  ;;;;         !3 %& @+  ;;;;         !'@+  ;;;;         !(+  ;;;;         !3 ('+S30-R30'hours in the day8@E@$U  m  u@  % V 3 Tuee$!!@ #+  ;;;;         !3 #$+  ;;;;         !3 $%+  ;;;;         !3 %&2+  ;;;;         !3 &'!@+  ;;;;         !(+  ;;;;         !3 (8J@$U  m  u@  % f 3 Wedd$!!@ #+  ;;;;         !3 #$+  ;;;;         !3 $%+  ;;;;         !3 %&>+  ;;;;         !3 &'!@+  ;;;;         !(+  ;;;;         !3 (8N@$U  m  u@  % v 3 Thuu$!!@ #+  ;;;;         !3 #$+  ;;;;         !3 $%+  ;;;;         !3 %&J+  ;;;;         !3 &'?+  ;;;;         !( @+  ;;;;         !8Q@$U  m  u @  %  3 Frii$!@ #+  ;;;;         !3 #$+  ;;;;         !3 $%+  ;;;;         !3 %&V+  ;;;;         !3 &'+  ;;;;         !3 '(@+  ;;;;         !8pS@$U  m  u@@  %  3 Satt$! #+  ;;;;         !3 #$+  ;;;;         !3 $%+  ;;;;         !3 %&b+  ;;;;         !3 &'+  ;;;;         !3 '(+  ;;;;         !8pS@$U  m  u`@  %  3 Sunn$! #+  ;;;;         !3 #$+  ;;;;         !3 $%+  ;;;;         !3 %&n+  ;;;;         !3 &' +  ;;;;         !3 '(+  ;;;;         !8pS@$U  m  u@  %  3 Monn$!!@ #+  ;;;;         !3 #$*+  ;;;;         !3 $%+  ;;;;         !3 %&z+  ;;;;         !3 &'+  ;;;;         !3 '(@+  ;;;;         !8@U@$U  m  u@  %  3 Tuee$!!@ #*+  ;;;;         !3 #$6+  ;;;;         !3 $%+  ;;;;         !3 %&+  ;;;;         !3 &'"+  ;;;;         !3 '(+  ;;;;         !3 (8?X@$U  m  u@  %  3 Wedd$!!@ #6+  ;;;;         !3 #$B+  ;;;;         !3 $%+  ;;;;         !3 %&+  ;;;;         !3 &'.+  ;;;;         !3 '(+  ;;;;         !3 (8Z@$U  m  }@  #B+  ;;;;         !3 #$N+  ;;;;         !3 $%+  ;;;;         !3 %&+  ;;;;         !3 &':+  ;;;;         !3 '(+  ;;;;         !3 ( Section 17$u<7~7,  X :: ރ      [!B w %^@Arrow30 X {GX ? "!B  j @Arrow31 X {GX ? "!B  #%@Arrow32 X {GX ? "\ "             %'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 hoursv'by the units per hour. Lets take a repetitive production schedule, as in Section 15, but with one work centre: 'Start of first job:  ^W/C      '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.'+J17+E18'+I18+G18'@IF($J14<=K$8#OR#$I14>=L$8,"",@IF($I14>=K$8#AND#$J14=K$8#AND#$I14K$8#AND#$J14-  ;;; ;      3 -  ;;; ;      3 ߐ@-  ;;; ;      8pS@$U  m  e@@  % F 3 Satt$ m  N-  ;;; ;      3  -  ;;; ;      3 J-  ;;; ;      3 -  ;;; ;      3 -  ;;; ;      8pS@$U  m  e`@  % V 3 Sunn$ m  Z-  ;;; ;      3  -  ;;; ;      3 V-  ;;; ;      3 -  ;;; ;      3 -  ;;; ;      8pS@$U  m  e@  % f 3 Monn$ m!@  f-  ;;; ;      3  -  ;;; ;      3 b-  ;;; ;      3 -  ;;; ;      3 3 y@-  ;;; ;      8@U@$U  m  @   Section 18$u<7~< 7,   uu   6- - -  $%%<'Working Hours Between Two Dates (Calendar Formula 2)u'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,z' 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 PMu' 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. w 'What we need is a formula that takes two points in time and calculates how many working hours (days x 24) thereY 'are between the two points. The calculation is very similar to that in Section 6: )^Period Number      s'The first 9 stages of the calculation locate the start on the calendar, and they are repeated for the stop:"'Stages of the calculation:L|8, is after period H begins3(8:30 is after period 1 beginssJ6* is after period H ends3&8:30 is after period 0 endss_K? lies between the beginning and end of period H3>8:30 lies between the beginning and end of period 110kSperiod H begins at "H: BH B0;3"period 1 begins at 8:000} |iQperiod H ends at "H: BH B0;3  period 1 ends at 10:000!tyou can start the job at "H: BH B0; because it is within a working period3N!you can start the job at 8:30 because it is within a working perioddG"'the number of hours from the start of the job to the next breakD#0*cum hours at end of period H3&#cum hours at end of period 22Q$|=7the job starts at cum hour H of the calendar3:$the job starts at cum hour 0.50 of the calendarr0%'working hours between the Start and Stop[('With a series of substitutions, the 19 formulas can be condensed into 4 as follows:*'Calc 4+'Calc 5,'Calc 6 -'Hours  1^Begin  $@+@/@ `*L u+a+bbbT  `,L }-i-bbbT     1^End    /@  H*'@MATCH(G26,B15..B20)+(@MATCH(G26,B15..B20)=@MATCH(G26,C15..C20))X+'@INDEX(E15..E20,0,B43)-(@INDEX(C15..C20,0,B43)-@MAX(@INDEX(B15..B20,0,B43),G26))H,'@MATCH(H26,B15..B20)+(@MATCH(H26,B15..B20)=@MATCH(H26,C15..C20))\-'@INDEX(E15..E20,0,B45)-(@INDEX(C15..C20,0,B45)-@MAX(@INDEX(B15..B20,0,B45),H26))-B44 )^Working Hours$ $@ $@ $@ $@ $@ " )^Working Hours so far (Cum)$ $@ $@ $@ $"@ $(@     q^Start!@UMfA/"H: BH B0;3I8:300-? - -? 1 @ b1 $@ b%!!@T$"? 1#@ b$$?  I^Stop2@U]rA/"H: BH B0;3Y18:300-@ -@ -@ 13@ b1 6@ b%!3@T$"@ 1#(@ b$$"@ $%!@ '@MATCH(H26,B15..B20)'@MATCH(H26,C15..C20)'+H29+(H29=H30)'@INDEX(B15..B20,0,H31) '@INDEX(C15..C20,0,H31)!'@MAX(H32,H26)"'+H33-H34#'@INDEX(E15..E20,0,H31)$'+H36-H35%'+H37-G37 Section 19$u<7~7,  W :: ރu       [!B   P @Arrow33 X {GX ? "\ "         #'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:  ^W/C      wm_cQWorking hours between midnight and  "H:BHB0;32iWorking hours between midnight and 10:000Q'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 performanceT'against it. In setting a production target for a day, it takes into account:  ^Seq      C '- calendar hours each day (production is lower on a Friday)0!'- each product runs at a different speed9"'- the set-up time depends on the previous product ^Product 'Prod D'Prod B'Prod A'Prod B'Prod CS@^Calc 4` L ccc  ^Qty    . , {?^Calc 5u a+bbbT  ccc ^Set-up hours  Z[@[@[?[? ^Calc 6x d"    ccc ^Units per hour  7 [@#   U  7@Z@#   U  7a@#   U  7@Z@#   U  7`@#   U   ^Run hours$ e.@ $em۶m&@ $e$I$0@ $em۶m@ $e@@  ^Total Hours$ e.@ $em۶m/@ $e$I$ 5@ $eܶm۶m@ $eA@ +?Um ^Start hr$ e $e $e $e $e   ^Stop hr  w5bbb T    ccc$  e $ e $ e $ e $ e 8 $U  m  e@%   3  Tuee$  m!@   -  ;;; ;       -  ;;; ;       -  ;;; ;       -  ;;; ;       -  ;;; ;      8 !@$U  m  e@  %   3  Wedd$  m!@   -  ;;; ;       -  ;;; ;       -  ;;; ;       -  ;;; ;       -  ;;; ;      8 1@$U  m  e @  %   3  Thuu$  m!@   -  ;;; ;       -  ;;; ;       -  ;;; ;       -  ;;; ;       -  ;;; ;      8 :@$U  m  e@@  %   3  Frii$  m@   -  ;;; ;       -  ;;; ;       -  ;;; ;       -  ;;; ;       -  ;;; ;      8A@$U  m  e`@  %  3 Satt$ m  -  ;;; ;      -  ;;; ;      -  ;;; ;      -  ;;; ;      -  ;;; ;      8A@$U  m  e@  %  3 Sunn$ m  -  ;;; ;      -  ;;; ;      -  ;;; ;      -  ;;; ;      -  ;;; ;      8A@$U  m  e@  %  3 Monn$ m!@  -  ;;; ;      -  ;;; ;      -  ;;; ;      -  ;;; ;      -  ;;; ;      8@E@$U  m  e@  %  3 Tuee$ m!@  -  ;;; ;      -  ;;; ;      -  ;;; ;      -  ;;; ;      -  ;;; ;      8J@$U  m  e@  %  3 Wedd$ m!@  -  ;;; ;      -  ;;; ;      -  ;;; ;      -  ;;; ;      -  ;;; ;      8N@$U  m  e@  % & 3 Thuu$ m!@  -  ;;; ;      -  ;;; ;      -  ;;; ;      -  ;;; ;      -  ;;; ;      8Q@$U  m  e @  % 6 3 Frii$ m@  -  ;;; ;      -  ;;; ;      -  ;;; ;      -  ;;; ;      -  ;;; ;      8pS@$U  m  e@@  % F 3 Satt$ m  -  ;;; ;      -  ;;; ;      -  ;;; ;      -  ;;; ;      -  ;;; ;      8pS@$U  m  e`@  % V 3 Sunn$ m  -  ;;; ;      -  ;;; ;      -  ;;; ;      -  ;;; ;      -  ;;; ;      8pS@$U  m  e@  % f 3 Monn$ m!@  -  ;;; ;      -  ;;; ;      -  ;;; ;      -  ;;; ;      -  ;;; ;      8@U@$U  m  @   Section 20$u<7~<-7,  t:с::u     ::::::::::::: [!B  G,K@Arrow34 X {GX ? "!B  d$o@Arrow35 X {GX ? "!B  eO@Arrow36 X {GX ? "!B  @Arrow37 X {GX ? "!B   @Arrow38 X {GX ? "!B   w@Arrow39 X {GX ? "!B   dS @Arrow40 X {GX ? "!@ Vv @Line3 7 ZG7 ? "!B  W+6@Arrow41 X {GX ? "!B ! @Arrow42 X {GX ? "!B !,T @Arrow43 X {GX ? "\&           !              4'Jobs That Pass Through Multiple Work Centres'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.K'Note the use of a 24 hour time format to fit into narrower columns.  ^W/C ^6 ^6 ^6 ^7 ^7 ^7 ^8 ^8 ^8('@RIGHT(B23,1)&"/"&@STRING(C23,0)*'@RIGHT(B23,1)&"/"&@STRING(C23-1,0)!'@MATCH(F23,$D$1..$D$25,0)2'@IF(@ISERR(G23),1,@INDEX(M$1..M$26,0,G23))+'(K23-@IF(A23=A22,M22,K$12+L$12))*24,'@MAX(@IF(A23=A22,M22,K$12+L$12),H23)'+K23+(E23/24)#'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.  ^Jobs ^job A ^job B ^job C ^job A ^job B ^job C ^job A ^job B ^job C  ^Op               !"#$%&'()*+,-./0123456789:;<=>?@ABCDEFGHIJKLMNOPQRSTUVWXYZ[\]^_`abcdefghijklmnopqrstuvwxyz{|}~ ^Job/Op6:L"i/H3A/116ZL"i/H3B/116zL"i/H3C/116L"i/H3A/226L"i/H3B/226L"i/H3C/226L"i/H3A/336M"i/H3B/336:M"i/H3C/33 ^Dur- ation Hours          ^Previous Op:JL&i/ H3A/00:jL&i/ H3B/00:L&i/ H3C/00:L&i/ H3A/11:L&i/ H3B/11:L&i/ H3C/11: M&i/ H3A/22:*M&i/ H3B/22:JM&i/ H3C/22 ^Previous Op Offset6"6"6"6,@"6.@"60@"61@"62@"63@"-'size of time bucket in days (8 hours) 'Start of first job: ^Stop of Previous Op=? )2b;=? )2b;=? )2b;=@ )2b;=UUUU!@ )2b;=8@ )2b;=@ )2b;=4@ )2b;=@@ )2b;  X? ? ? @ UUUU!@ 8@ @ 4@ @@   ^Wait hoursH  4 ;    H  4 ;    H  4 ;    H 2@ 4 ;    H +@ 4 ;    H @ 4 ;    H ;@ 4 ;    H .@ 4 ;    H  4 ;     '- 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 1R '- derive the previous operation by subtracting 1 from the operation numberL '- find the offset of the previous operation by looking down column Dt '- if ther is no previous op, then put day 1 (1 Jan 1900), else get the stop of the previous op from column Ks '- the number of hours the work centre has to wait for the previous operation to finish, before it can start '- the start is later of: if it's the same work centre, the stop of the previous job, else the start of the first job, and the stop of the previous operation< '- the stop is the start plus hours converted to days  s@  A^StartD @ 0  ;T  D @ 0  ;T  D UUUU!@ 0  ;T  D @ 0  ;T  D UUUU!@ 0  ;T  D 8@ 0  ;T  D @ 0  ;T  D 4@ 0  ;T  D B@ 0  ;T    {UUUUUU?   H @  @  UUUU!@  @  UUUU!@  8@  @  4@  B@   A^Stop) @    ) UUUU!@    ) 8@    ) @    ) 4@    ) @@    )  @    ) B@    ) ^@    V !'Note the gaps while the work centre waits for the prevoius operation to finish   H @  UUUU!@   8@  @  4@   @@   @  B@   ^@  UUUUUU?    @ 3   ;;; ;       3 . 3   ;;; ;       3  3   ;;; ;       3  3   ;;; ;       3 J 3   ;;; ;       3  3   ;;; ;       3 F 3   ;;; ;       3  3   ;;; ;       3 * 3   ;;; ;       3  UUUUUU?  @$ @  @ 3   ;;; ;       : 3   ;;; ;       3  3   ;;; ;       3  3   ;;; ;       3 V 3   ;;; ;       3  3   ;;; ;       3 R 3   ;;; ;       3  3   ;;; ;       3 6 3   ;;; ;       3  UUUUUU?   . VUUU@ -q=  @ 3   ;;; ;       F 3   ;;; ;       3  3   ;;; ;       3  3   ;;; ;       3 b 3   ;;; ;       3  3   ;;; ;       3 ^ 3   ;;; ;       3  3   ;;; ;       3 B 3   ;;; ;       3  UUUUUU?   $ @ @ 3   ;;; ;       @ 3   ;;; ;        3   ;;; ;       3 @ 3   ;;; ;       n 3   ;;; ;       3  3   ;;; ;       3 j 3   ;;; ;       3  3   ;;; ;       3 N 3   ;;; ;       3  UUUUUU?  @$ @  3   ;;; ;       3  @ 3   ;;; ;        3   ;;; ;       3 @ 3   ;;; ;       z 3   ;;; ;       3  3   ;;; ;       3 @ 3   ;;; ;        3   ;;; ;       3 Z 3   ;;; ;       3  UUUUUU?   $ WUUU@  3   ;;; ;       3  @ 3   ;;; ;        3   ;;; ;       3  3   ;;; ;       3  3   ;;; ;       3  3   ;;; ;       3  @ 3   ;;; ;        3   ;;; ;       3 f 3   ;;; ;       3  UUUUUU?   $  @  3   ;;; ;       3 ? 3   ;;; ;       @ 3   ;;; ;       * 3   ;;; ;       3 @ 3   ;;; ;         3   ;;; ;       3 v 3   ;;; ;       3  3   ;;; ;       3 r 3   ;;; ;       3  UUUUUU?   @$ *@  3   ;;; ;       3 R 3   ;;; ;       3  @ 3   ;;; ;       6 3   ;;; ;       3 @ 3   ;;; ;        3   ;;; ;       3  3   ;;; ;       3  ? 3   ;;; ;       ~ 3   ;;; ;       3  UUUUUU?   $ XUUU5@  3   ;;; ;       3 ^ 3   ;;; ;       3 ? 3   ;;; ;       B 3   ;;; ;       3  3   ;;; ;       3  @ 3   ;;; ;        3   ;;; ;       3  @ 3   ;;; ;        3   ;;; ;       3  UUUUUU?   $ @@   3   ;;; ;       3 j 3   ;;; ;       3  3   ;;; ;       3 N 3   ;;; ;       3  3   ;;; ;       3 " 3   ;;; ;       3  3   ;;; ;       3 ? 3   ;;; ;        @ 3   ;;; ;        UUUUUU?  @@$ J@  3   ;;; ;       3 v 3   ;;; ;       3  3   ;;; ;       3 2 3   ;;; ;       3  3   ;;; ;       3 . 3   ;;; ;       3  3   ;;; ;       3  3   ;;; ;       3  @ 3   ;;; ;        UUUUUU?   $ YUUUU@  " 3   ;;; ;       3   3   ;;; ;       3  3   ;;; ;       3  > 3   ;;; ;       3   3   ;;; ;       3 : 3   ;;; ;       3   3   ;;; ;       3   3   ;;; ;       3 @ 3   ;;; ;       $ `@  Section 21$u<7~:7,ԃ::: uރ         ރ [!B  f #M@Arrow44 X {GX ? "!B ; r@Arrow45 X {GX ? "!B ; s@Arrow46 X {GX ? "!B X Q@Arrow47 X {GX ? "\!                     4'Jobs That Pass Through Multiple Work Centres'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. '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. '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 e 'the time which the start and stop, of the next operation, lags behind the previous operation:% 'The next operation can start  !^W/C ^6 ^6 ^6 ^7 ^7 ^7 ^8 ^8 ^8<'@IF(@ISERR(I25),1,@INDEX(O$1..O$27,0,I25)+(F$13/24)),'@MAX(@IF(A25=A24,Q24,O$14+P$14),J25)'@MAX(O25+(G25/24),L25)'+E25/(Q25-O25)/24 '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,!'and that is why the effective speed in units per hour is so much less than the rated speed. This is a typical "PUSH" schedule, but we discuss an alternative approach in later sections. !^Jobs ^job A ^job B ^job C ^job A ^job B ^job C ^job A ^job B ^job C !^Op         )^Job/Op6M"i/H3A/116M"i/H3B/116M"i/H3C/116N"i/H3A/226:N"i/H3B/226ZN"i/H3C/226zN"i/H3A/336N"i/H3B/336N"i/H3C/33 )^Qty   l   l   l  )^Units per Hour A < K i _ n P U _< 'hour after the start of the previous operation (Lag))^Dur- ation Hours$5;;7@ $UUUUUUB@ $ UUUUUU9@ $5%I$I,@ $k(7@ $ F]tE1@ $52@ $9@ $ 4@ )^Previous Op:M&i/ H3A/00:M&i/ H3B/00: N&i/ H3C/00:*N&i/ H3A/11:JN&i/ H3B/11:jN&i/ H3C/11:N&i/ H3A/22:N&i/ H3B/22:N&i/ H3C/22)^Previous Op Offset6"6"6"60@"61@"62@"63@"64@"65@" 1^Start of Previous OpJ =? 62b  ; J ? 62b  ; J  ? 62b  ; J =@ 62b  ; J oo@ 62b  ; J  S2%S6@ 62b  ; J =@ 62b  ; J N@ 62b  ; J  z7@ 62b  ; N '- find the start of the previous operation in column O, and add 1 hour '- 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 operationd '- the stop is the later of: the start plus the hours, and the stop of the previous operation5 '-quantity divided by the stop minus the start  8 E?  ?   ?  E@  oo@   S2%S6@  E@  N@   z7@   'Start of first job: 1^Stop of Previous OpJ M? 62b  ; J ? 62b  ; J  ? 62b  ; J Moo@ 62b  ; J S2%S6@ 62b  ; J  AX@ 62b  ; J MN@ 62b  ; J z7@ 62b  ; J  ooY@ 62b  ;  8 U?  ?  % ?  Uoo@  S2%S6@  % AX@  UN@  z7@  % ooY@  )^Wait hoursH ] 4 ;    H  4 ;    H - 4 ;    H ]? 4 ;    H  4 ;    H - 4 ;    H ]? 4 ;    H  4 ;    H - 4 ;     @ A^StartDeUUUU@ 0  ;T  DA@ 0  ;T  D5 4@ 0  ;T  De@ 0  ;T  Doo@ 0  ;T  D5 S2%S6@ 0  ;T  De@ 0  ;T  DN@ 0  ;T  D5 z7@ 0  ;T   #UUUUUU? HmUUUU@ A@ = 4@ m@ oo@ = S2%S6@ m@ N@ = z7@  A^Stop2uA@    T24@    T2E NV@    T2uoo@    T2S2%S6@    T2E AX@    T2uN@    T2z7@    T2E ooY@    T H}A@ 4@ M NV@ }oo@ S2%S6@ M AX@ }N@ z7@ M ooY@  Q^Effective Units per Hour1?P@     1/M@     1U R@     1?P@     1/M@     1U R@     1?P@     1/M@     1U R@      UUUUUU?   -@@p@ 5   ;;;  ;  5   ;;;  ; 3 ] J 5   ;;;  ; 3 Y f`h@ 5   ;;;  ;  5   ;;;  ; 3 ] F 5   ;;;  ; 3 Y @`@ 5   ;;;  ;  5   ;;;  ; 3 ]  5   ;;;  ; 3 Y  UUUUUU? @$-@ ?@ 5   ;;;  ;  5   ;;;  ; 3 ] V 5   ;;;  ; 3 Y ?@ 5   ;;;  ;   5   ;;;  ; 3 ] R 5   ;;;  ; 3 Y ?@ 5   ;;;  ;  5   ;;;  ; 3 ]  5   ;;;  ; 3 Y  UUUUUU?  .-VUUU@ -q= ?@ 5   ;;;  ;  5   ;;;  ; 3 ] b 5   ;;;  ; 3 Y ?@ 5   ;;;  ;  5   ;;;  ; 3 ] ^ 5   ;;;  ; 3 Y ?@ 5   ;;;  ;  5   ;;;  ; 3 ]  5   ;;;  ; 3 Y  UUUUUU?   $-@ Gh@ 5   ;;;  ;  uzb'vr@ 5   ;;;  ; ] n 5   ;;;  ; 3 Y fp@ 5   ;;;  ; Nlm@ 5   ;;;  ; ] j 5   ;;;  ; 3 Y t@ 5   ;;;  ; Ne@ 5   ;;;  ; ]  5   ;;;  ; 3 Y  UUUUUU? @$- @  5   ;;;  ; 3 /}@ 5   ;;;  ; ] z 5   ;;;  ; 3 Y  5   ;;;  ; 3 /}@ 5   ;;;  ; ] v 5   ;;;  ; 3 Y  5   ;;;  ; 3 /}@ 5   ;;;  ; ]  5   ;;;  ; 3 Y  UUUUUU?  @$-WUUU@  5   ;;;  ; 3 /}@ 5   ;;;  ; ]  5   ;;;  ; 3 Y  5   ;;;  ; 3 /}@ 5   ;;;  ; ]  5   ;;;  ; 3 Y  5   ;;;  ; 3 /}@ 5   ;;;  ; ]  5   ;;;  ; 3 Y  UUUUUU?   $- @  5   ;;;  ; 3 /}@ 5   ;;;  ; ]  5   ;;;  ; 3 Y  5   ;;;  ; 3 /}@ 5   ;;;  ; ]  5   ;;;  ; 3 Y  5   ;;;  ; 3 /}@ 5   ;;;  ; ] * 5   ;;;  ; 3 Y  UUUUUU?  @$-*@  5   ;;;  ; 3 l }@ 5   ;;;  ; ] 2Ʊ;3@ 5   ;;;  ;  5   ;;;  ; 3 /}@ 5   ;;;  ; ]  5   ;;;  ; 3 Y  5   ;;;  ; 3 /}@ 5   ;;;  ; ] 6 5   ;;;  ; 3 Y  UUUUUU?  @$-XUUU5@  5   ;;;  ; 3  5   ;;;  ; 3 ] @ 5   ;;;  ;  5   ;;;  ; 3 LNF@ 5   ;;;  ; ] Rn@ 5   ;;;  ;  5   ;;;  ; 3 "vb'Z@ 5   ;;;  ; ]  ;S}@ 5   ;;;  ;  UUUUUU?   $-@@  5   ;;;  ; 3 & 5   ;;;  ; 3 ] @ 5   ;;;  ;  5   ;;;  ; 3 " 5   ;;;  ; 3 ] @ 5   ;;;  ; v 5   ;;;  ; 3  5   ;;;  ; 3 ] @ 5   ;;;  ;  UUUUUU? @@$-J@  5   ;;;  ; 3 2 5   ;;;  ; 3 ] @ 5   ;;;  ;  5   ;;;  ; 3 . 5   ;;;  ; 3 ] @ 5   ;;;  ;  5   ;;;  ; 3  5   ;;;  ; 3 ] @ 5   ;;;  ;  UUUUUU?  @$-YUUUU@  5   ;;;  ; 3 > 5   ;;;  ; 3 e ~;1T@ 5   ;;;  ;  5   ;;;  ; 3 : 5   ;;;  ; 3 e exc@ 5   ;;;  ;  5   ;;;  ; 3  5   ;;;  ; 3 e JȺl@ 5   ;;;  ; $`@  Section 22$u<7~7,:   uu      ރ::::::::: :!:":#: [!B   X@Arrow48 X {GX ? "!B  V r(q@Arrow49 X {GX ? "!B  + |@Arrow50 X {GX ? "!B  + @Arrow51 X {GX ? "!B   @Arrow52 X {GX ? "!B   [@Arrow53 X {GX ? "!B @Arrow54 X {GX ? "!B  S@Arrow55 X {GX ? "\                     ! " #'Transfer Batches'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.6 'Time to transfer a batch between work centres: !^W/C ^6 ^6 ^6 ^7 ^7 ^7 ^8 ^8 ^8#'@VLOOKUP(B25,$B$9..$D$11,2)B'@IF(@ISERR(I25),0,(J25/@INDEX(F$1..F$28,0,I25))+(H$14/60)),'@IF(@MOD(E25,J25),@MOD(E25,J25),J25)'(H$14/60)+(L25/F25);'@IF(@ISERR(I25),1,@INDEX(S$1..S$28,0,I25)+(K25/24)); '@IF(@ISERR(I25),1,@INDEX(U$1..U$28,0,I25)+(M25/24))'prod A 'prod B 'prod C !^Jobs^prod A^prod B^prod C^prod A^prod B^prod C^prod A^prod B^prod C     !^Op         )^Batch size P  2  d)^Job /Op6:O"i/H3A/116ZO"i/H3B/116zO"i/H3C/116O"i/H3A/226O"i/H3B/226O"i/H3C/226O"i/H3A/336P"i/H3B/336:P"i/H3C/33 )^Qty   l   l   l)^Units per Hour A < K i _ n P U _v'Try changing the transfer batch sizes here and see what effect is has on the stop time of the last operation. )^Dur- ation Hours$} ;;7@ $ UUUUUUB@ $m UUUUUU9@ $} %I$I,@ $ k(7@ $m F]tE1@ $} 2@ $ 9@ $m 4@   )^Previous Op:JO&i/ H3A/00:jO&i/ H3B/00:O&i/ H3C/00:O&i/ H3A/11:O&i/ H3B/11:O&i/ H3C/11: P&i/ H3A/22:*P&i/ H3B/22:JP&i/ H3C/22 'minutes)^Previous Op Offset6"6"6"60@"61@"62@"63@"64@"65@" )'Batch size+ T@U + I@U + Y@U + T@U + I@U + Y@U + T@U + I@U + Y@U  )'1 st Batch LagY  E#2b <  ; Y  E#2b <  ; Y u E#2b <  ; Y  ii?E#2b <  ; Y  VUUUUU?E#2b <  ; Y u ?E#2b <  ; Y  0 0 ?E#2b <  ; Y  $>?E#2b <  ; Y u |?E#2b <  ; H '- 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 sizef '- the transfer time plus the batch size divided by the units per hour, to get the Lag in hoursN '- the start of the previous operation plus the time of the first batchM  '- the stop of the previous operation, plus the time of the last batch )'Last Batch Size; N@' //;; I@' //;; Y@' //;; N@' //;; I@' //;; Y@' //;; N@' //;; I@' //;; Y@' //; )'Last Batch Lag2  AA? <    2  VUUUUU? <    2 } ? <    2  =? <    2 } |? <    2  ? <    2  ~}}}}}? <    2 } b:,? <     )^Start of Previous OpJ  ? 62b  ;J  ? 62b  ;J  ? 62b  ;J  ^@ 62b  ;J  S2%S@ 62b  ;J  ^6@ 62b  ;J  7fc6@ 62b  ;J  Y&=#@ 62b  ;J  cp48@ 62b  ; 0 ?  ?  ?  ^@  S2%S@  ^6@  7fc6@  Y&=#@  cp48@  'Start of first job:)^Stop of Previous OpJ ? 62b  ;J ? 62b  ;J ? 62b  ;J @ 62b  ;J 5@ 62b  ;J FX@ 62b  ;J c1@ 62b  ;J `"6@ 62b  ;J 6iY@ 62b  ; 0 ? % ?  ?  @ % 5@  FX@  c1@ % `"6@  6iY@ )^Wait hoursH 4 ;    H- 4 ;    H 4 ;    H i? 4 ;    H- 0 ? 4 ;    H t? 4 ;    H LeTF@ 4 ;    H- 椑? 4 ;    H ? 4 ;     k @ 9^StartD UUUU@ 0  ;T  D5 A@ 0  ;T  D 4@ 0  ;T  D ^@ 0  ;T  D5 S2%S@ 0  ;T  D ^6@ 0  ;T  D 7fc6@ 0  ;T  D5 Y&=#@ 0  ;T  D cp48@ 0  ;T   s UUUUUU? P UUUU@ = A@  4@  ^@ = S2%S@  ^6@  7fc6@ = Y&=#@  cp48@  9^Stop2 A@    T2E 4@    T2 NV@    T2 @    T2E 5@    T2 FX@    T2 c1@    T2E `"6@    T2 6iY@    T P A@ M 4@  NV@  @ M 5@  FX@  c1@ M `"6@  6iY@  a^Effective Units per Hour1 ?P@     1U /M@     1 R@     1 ZZZP@     1U 6f@N@     1 GؗwS@     1 [֠P@     1U D3N@     1 5R@      UUUUUU?  @ @p@ 5   ;;;  ; ]  5   ;;;  ; 3 Y   5   ;;;  ; 3   &vf@ 5   ;;;  ; ]  5   ;;;  ; 3 Y   5   ;;;  ; 3   =^@ 5   ;;;  ; ]  5   ;;;  ; 3 Y   5   ;;;  ; 3   UUUUUU? @$@  ?@ 5   ;;;  ; ]  5   ;;;  ; 3 Y   5   ;;;  ; 3   ZZZ@ 5   ;;;  ; ]  5   ;;;  ; 3 Y   5   ;;;  ; 3   [֠@ 5   ;;;  ; ]  5   ;;;  ; 3 Y   5   ;;;  ; 3   UUUUUU?  .VUUU@ -q=  ?@ 5   ;;;  ; ]  5   ;;;  ; 3 Y   5   ;;;  ; 3   ZZZ@ 5   ;;;  ; ]  5   ;;;  ; 3 Y   5   ;;;  ; 3   [֠@ 5   ;;;  ; ]  5   ;;;  ; 3 Y   5   ;;;  ; 3   UUUUUU?  $@  Gh@ 5   ;;;  ; ] uzb'vr@ 5   ;;;  ;   5   ;;;  ; 3   l"(226o@ 5   ;;;  ; ] D BMn@ 5   ;;;  ;   5   ;;;  ; 3   ys@ 5   ;;;  ; ] j!gi@ 5   ;;;  ;   5   ;;;  ; 3   UUUUUU? @$ @  B 5   ;;;  ; 3  ] /}@ 5   ;;;  ;  & 5   ;;;  ; 3   J 5   ;;;  ; 3  ] 6f@~@ 5   ;;;  ;  " 5   ;;;  ; 3   R 5   ;;;  ; 3  ] D3~@ 5   ;;;  ;  * 5   ;;;  ; 3   UUUUUU?  $WUUU@  N 5   ;;;  ; 3  ] /}@ 5   ;;;  ;  2 5   ;;;  ; 3   V 5   ;;;  ; 3  ] 6f@~@ 5   ;;;  ;  . 5   ;;;  ; 3   ^ 5   ;;;  ; 3  ] D3~@ 5   ;;;  ;  6 5   ;;;  ; 3   UUUUUU?  $ @  Z 5   ;;;  ; 3  ] /}@ 5   ;;;  ;  > 5   ;;;  ; 3   b 5   ;;;  ; 3  ] 6f@~@ 5   ;;;  ;  : 5   ;;;  ; 3   j 5   ;;;  ; 3  ] D3~@ 5   ;;;  ;  B 5   ;;;  ; 3   UUUUUU?  @$*@  f 5   ;;;  ; 3  ] l }@ 5   ;;;  ;  2Ʊ;3@ 5   ;;;  ;  n 5   ;;;  ; 3  ] 6f@~@ 5   ;;;  ;  F 5   ;;;  ; 3   v 5   ;;;  ; 3  ] D3~@ 5   ;;;  ;  N 5   ;;;  ; 3   UUUUUU?  $XUUU5@  r 5   ;;;  ; 3  ]  5   ;;;  ; 3 Y  @ 5   ;;;  ;  z 5   ;;;  ; 3  ] |B_5@ 5   ;;;  ;  ȽM@ 5   ;;;  ;   5   ;;;  ; 3  ] Sl|N@ 5   ;;;  ;  Nc:g{@ 5   ;;;  ;   UUUUUU?   $ @@   ~ 5   ;;;  ; 3   ]  5   ;;;  ; 3 Y   @ 5   ;;;  ;    5   ;;;  ; 3   ]  5   ;;;  ; 3 Y   Gؗw@ 5   ;;;  ;    5   ;;;  ; 3   ]  5   ;;;  ; 3 Y   5@ 5   ;;;  ; ! UUUUUU? !@@$!J@ !  5   ;;;  ; 3 ! !]  5   ;;;  ; 3 !Y ! @ 5   ;;;  ; !  5   ;;;  ; 3 ! !]  5   ;;;  ; 3 !Y ! Gؗw@ 5   ;;;  ; !  5   ;;;  ; 3 ! !]  5   ;;;  ; 3 !Y ! 5@ 5   ;;;  ; " UUUUUU?  "$"YUUUU@ "  5   ;;;  ; 3 " "e  5   ;;;  ; 3 "a " ~;1T@ 5   ;;;  ; "  5   ;;;  ; 3 " "e  5   ;;;  ; 3 "a " T c@ 5   ;;;  ; "  5   ;;;  ; 3 " "e  5   ;;;  ; 3 "a " oObn@ 5   ;;;  ; $#`@  Section 23$u<7~7,  ,, ,! u       u u      [!B  ,s r@Arrow56 X {GX ? "!B   6s@Arrow57 X {GX ? "\T&" " " " " $ $ "     /'Re-Using Parts of the Calendar Formulasu'In the next Section we will apply the calendar formulas to the schedule in Section 21, but before we do, letsu'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:a 'Here is an example of the calendar formulas applied to two durations from the same start:  ^Hours  r'Calc1 and Calc2 locate the start in the calendar and return the same values on each row, whereas Calc3 andU'Stop use the duration in days. So we could re-use Calc1 and Calc2 as follows:^Hours 1 3'Note how these formulas refer back to Calc2:'@MATCH(F22+K22,Calendar 2:I$6..Calendar 2:I$100)+1m'@INDEX(Calendar 2:H$6..Calendar 2:H$100,0,L22)-@INDEX(Calendar 2:I$6..Calendar 2:I$100,0,L22)+F22+K22q'You can also re-use the formulas when applying Calendar Formulas 2 (Section 18), to calculate the workingX'hours between two dates, as Calc1 and Calc2 are also common to this calculation:  ^Hours ! "2$'Working hours between the two start dates:t%'- the difference between the results of the two Calc2 formulas is the result in days, then multiply by 24 to&'get hoursW'- in column J, the lag duration is added to the start of the previous operationV'- in column L, the lag duration is added to the stop of the previous operationC'- in column O, the operation duration is added to the start  ^Days ?   UUUUUU?  ^Days 1 ?    ^Days !?   "UUUUUU?    ^Start @ @ ^Start @  ^Start! @" 5@   @ @  @  ! @" 5@  ^Calc1`L ccc`L ccc ^Calc1`L ccc  ^Calc1`!L ccc`"L ccc  ^Calc2ua+bbbT  cccua+bbbT  ccc ^Calc2ua+bbbT  ccc  ^Calc2u!a+bbbT  cccu"a+bbbT  ccc  ^Calc39%  c9%  c ^Calc39%  c  ^Calc39!%  c9"%  c)$     'Stop^ Jbb   cc^ Jbb   cc'Stop 1^ Jbb   cc  'Stop^! Jbb   cc^" Jbb   cc$'(F35-F34)*24        ! "  ^Hours 2   ^Days 2 UUUUUU?   ^Calc49 %  c 'Stop 2^ % Jbb   cc   -  Section 24$u<7~P_7, , X:с uu  ރ ރ                 4'Jobs That Pass Through Multiple Work Centres'Here is the schedule from Section 21 with different calendars assigned to the work centres, and the calendar calculations applied in 3 places:  ^W/C  '6  '7  '8%'The next operation can start  !^W/C ^6 ^6 ^6 ^7 ^7 ^7 ^8 ^8 ^8 '- the 1st calculations take the stop of the previous operation and apply the lag duration, using the calendar of the next operationH'- 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 ^Calendar ^Calendar 2 ^Calendar 1 ^Calendar 2!^Calendar+U  3Calendar 22+JU  3Calendar 22+`U  3Calendar 22+vU  3Calendar 11+U  3Calendar 11+U  3Calendar 11+U  3Calendar 22+΄U  3Calendar 22+U  3Calendar 22 !^Jobs ^job A ^job B ^job C ^job A ^job B ^job C ^job A ^job B ^job C !^Op         )^Job/Op6jQ"i/H3A/116Q"i/H3B/116Q"i/H3C/116Q"i/H3A/226Q"i/H3B/226 R"i/H3C/226^Q"i/H3A/336~Q"i/H3B/336Q"i/H3C/33 )^Qty   l   l   l )^Units per Hour X    A  2  j<'hour after the start of the previous operation (Lag))^Dur- ation Hours$E . 1@ $ ~FQ/@ $E 87@ $ lw6@ $ NN@@ $E $@ $ >@ $ K @ $E 1@  )^Days M 袋.?    ٰA?   M %I%I?    F?    ii?   M ?    ?    Hx?   M _.=?   )^Previous Op: zQ&i/ H3 A/00: Q&i/ H3 B/00: Q&i/ H3 C/00: Q&i/ H3 A/11: Q&i/ H3 B/11: NQ&i/ H3 C/11: nQ&i/ H3 A/22: Q&i/ H3 B/22: Q&i/ H3 C/22 )^Previous Op Offset6 "6 "6 "6 2@"6 3@"6 4@"6 5@"6 6@"6 7@"" )^Start of Previous Op + Lag= U ? )2b;=  ? )2b;= U ? )2b;=  @ )2b;=  .*@ )2b;= U QU@ )2b;=  @ )2b;=  ,@ )2b;= U U0@ )2b;  0 ] ?   ?  ] ?   @   .*@  ] QU@   @   ,@  ] U0@ " ^1 st calendar calculations )^Stop of Previous Op= e ? )2b; =  ? )2b; = e ? )2b; =  .@ )2b; =  dS@ )2b; = e ]@ )2b; =  O6d@ )2b; =  ɘ@ )2b; = e ɘ@ )2b;   0m ?  ? m ?  .@  dS@ m ]@  O6d@  ɘ@ m ɘ@   'Calc1?0U:G5..:G200s:G5..:G200s:H5..:H200s ?0U:G5..:G200s:G5..:G200s:H5..:H200s ?0U:G5..:G200s:G5..:G200s:H5..:H200s ,@0U:G5..:G200s:G5..:G200s:H5..:H200s 7@0U:G5..:G200s:G5..:G200s:H5..:H200s ;@0U:G5..:G200s:G5..:G200s:H5..:H200s *@0U:G5..:G200s:G5..:G200s:H5..:H200s 9@0U:G5..:G200s:G5..:G200s:H5..:H200s =@0U:G5..:G200s:G5..:G200s:H5..:H200s 'Start of first job:  'Calc2u 0d:I5..:I200sb:H5..:H200sb:G5..:G200sbT   0d:I5..:I200sb:H5..:H200sb:G5..:G200sbT  u 0d:I5..:I200sb:H5..:H200sb:G5..:G200sbT   E]?0d:I5..:I200sb:H5..:H200sb:G5..:G200sbT   Ԓ@0d:I5..:I200sb:H5..:H200sb:G5..:G200sbT  u @l@0d:I5..:I200sb:H5..:H200sb:G5..:G200sbT   ?0d:I5..:I200sb:H5..:H200sb:G5..:G200sbT   0&@0d:I5..:I200sb:H5..:H200sb:G5..:G200sbT  u @0d:I5..:I200sb:H5..:H200sb:G5..:G200sbT    'Calc3S?0?'  :I5..:I200s S?0?'  :I5..:I200s S?0?'  :I5..:I200s S.@0?'  :I5..:I200s S8@0?'  :I5..:I200s S<@0?'  :I5..:I200s S*@0?'  :I5..:I200s S9@0?'  :I5..:I200s S=@0?'  :I5..:I200s  !)^Stop of Previous Op + Lag} ?0O2:H5..:H200sb:I5..:I200sb    ; ?0O2:H5..:H200sb:I5..:I200sb    ;} ?0O2:H5..:H200sb:I5..:I200sb    ; O6d@0O2:H5..:H200sb:I5..:I200sb    ; QU@0O2:H5..:H200sb:I5..:I200sb    ;} SK@0O2:H5..:H200sb:I5..:I200sb    ; ,@0O2:H5..:H200sb:I5..:I200sb    ; U0@0O2:H5..:H200sb:I5..:I200sb    ;} @0O2:H5..:H200sb:I5..:I200sb    ; P 0 ?  ?  ?  O6d@  QU@  SK@  ,@  U0@  @ 3 @"^2 nd calendar calculations 9^StartD @ 0  ;TD .@ 0  ;TD dS@ 0  ;TD @ 0  ;TD .*@ 0  ;TD ɘ@ 0  ;TD @ 0  ;TD YUUUR@ 0  ;TD U0@ 0  ;T; ?  P @  .@  dS@  @  .*@  ɘ@  @  YUUUR@  U0@   'Calc4@0U:G5..:G200s:G5..:G200s:H5..:H200s (@0U:G5..:G200s:G5..:G200s:H5..:H200s 3@0U:G5..:G200s:G5..:G200s:H5..:H200s @0U:G5..:G200s:G5..:G200s:H5..:H200s 0@0U:G5..:G200s:G5..:G200s:H5..:H200s >@0U:G5..:G200s:G5..:G200s:H5..:H200s       !"#$%&'()*+,-./0123456789:;<=>?@ABCDEFGHIJKLMNOPQRSTUVWXYZ[\]^_`abcdefghijklmnopqrstuvwxyz{|}~@0U:G5..:G200s:G5..:G200s:H5..:H200s 3@0U:G5..:G200s:G5..:G200s:H5..:H200s 9@0U:G5..:G200s:G5..:G200s:H5..:H200s   'Calc5 ?0d:I5..:I200sb:H5..:H200sb:G5..:G200sbT   E]?0d:I5..:I200sb:H5..:H200sb:G5..:G200sbT   %?0d:I5..:I200sb:H5..:H200sb:G5..:G200sbT   TUUU?0d:I5..:I200sb:H5..:H200sb:G5..:G200sbT   {?0d:I5..:I200sb:H5..:H200sb:G5..:G200sbT   {L@0d:I5..:I200sb:H5..:H200sb:G5..:G200sbT   RUUU?0d:I5..:I200sb:H5..:H200sb:G5..:G200sbT   TUUU?0d:I5..:I200sb:H5..:H200sb:G5..:G200sbT   {L@0d:I5..:I200sb:H5..:H200sb:G5..:G200sbT    'Calc6N(@0:" :I5..:I200s N3@0:" :I5..:I200s N7@0:" :I5..:I200s N"@0:" :I5..:I200s N>@0:" :I5..:I200s NA@0:" :I5..:I200s N3@0:" :I5..:I200s N7@0:" :I5..:I200s N@@0:" :I5..:I200s   9^Stop .@0}G:H5..:H200sb:I5..:I200sb   T% dS@0}G:H5..:H200sb:I5..:I200sb   T ]@0}G:H5..:H200sb:I5..:I200sb   T% O6d@0}G:H5..:H200sb:I5..:I200sb   T% ɘ@0}G:H5..:H200sb:I5..:I200sb   T ɘ@0}G:H5..:H200sb:I5..:I200sb   T% YUUUR@0}G:H5..:H200sb:I5..:I200sb   T% U0@0}G:H5..:H200sb:I5..:I200sb   T  @0}G:H5..:H200sb:I5..:I200sb   T P P .@ - dS@  ]@ - O6d@ - ɘ@  ɘ@ - YUUUR@ - U0@   @ "^3 rd calendar calculations 'Calc7S@0?'  :I5..:I200s S*@0?'  :I5..:I200s S4@0?'  :I5..:I200s S@0?'  :I5..:I200s S0@0?'  :I5..:I200s S>@0?'  :I5..:I200s S@0?'  :I5..:I200s S3@0?'  :I5..:I200s S:@0?'  :I5..:I200s  9'Start + Lag @0yI:H5..:H200sb:I5..:I200sb    5 .*@0yI:H5..:H200sb:I5..:I200sb     QU@0yI:H5..:H200sb:I5..:I200sb    5 @0yI:H5..:H200sb:I5..:I200sb    5 ,@0yI:H5..:H200sb:I5..:I200sb     U0@0yI:H5..:H200sb:I5..:I200sb    5 @0yI:H5..:H200sb:I5..:I200sb    5 S@0yI:H5..:H200sb:I5..:I200sb     ɘ@0yI:H5..:H200sb:I5..:I200sb      P @ = .*@  QU@ = @ = ,@  U0@ = @ = S@  ɘ@  Section 25$u<7~j7, , [!B ^wY U@Arrow58 X {GX ? "!B qx r@Arrow59 X {GX ? "\"  'A Pull ScheduleH'Let's go back to the very simple schedule in Section 2. It says:E'"If I start now (at hour zero), when will the last job stop?"7'To turn it into a pull schedule, it should say:V'"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:'+E21-C21'@IF(D22,D22,E$13)^'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:U'"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:E '"If I start now (at hour zero), when will the last job stop?"n"'What is needed is a schedule that will handle both push and pull logic, and apply the appropriate one. 'Jobs required at hour: ^Jobs ^job A ^job B ^job C ^job D ^job E ^Hours      ^Start$(@ $3@ $8@ $<@ $D@ /'- the start is the stop minus the hoursU'- if there is a next job, stop when it starts, else stop at the required hour  0 ^Stop+3@; +8@; +<@; +D@; +H@;  Section 26$u<7~Py7, , ރރރރރރރރ  [!B  j@Arrow60 X {GX ? "\6 '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:'@IF(F$12<0,D12,F12)s'You will notice that the push logic adds the hours to get the stop, whereas the pull logic works backwards,t'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.'Jobs required at hour:  ^Jobs  ^job A  ^job B  ^job C ^job D ^job E  ^Hours         ^Push  ^Start  @ (@0@<@S'- if the start of the first job on the pull schedule is -ve, apply the push1' schedule, else apply the pull schedule.    ^Stop$ @ $ (@ $ 0@ $<@ $B@  ^Pull  ^Start$  $ @ $ @ $(@ $8@    ^Stop+ @;+ @;+ (@;+8@;+@@; ^Apply  ^Start/  ; / @ ; / (@ ; /0@ ; /<@ ;   ^Stop5 @!; 5 (@!; 5 0@!; 5<@!; 5B@!;  Section 27$u<7~7,Y [!B $  $ @Arrow61 X {GX ? "\0,,, ""9'Calculation of Job Stop Time Through the Calendaro'To make a pull schedule that works through a calendar, we will need a formula that works backwards, or q'upwards through the calendar. Given a job stop and working hours, when will I need to start. Here is thed'development of the formula from Section 6, and it can be modified by changing just one sign.)^Period Number           "'Stages of the calculation:L݌8, is after period H begins3(18:30 is after period 4 beginssJ\ތ6* is after period H ends3&18:30 is after period 4 endss_HߌK? lies between the beginning and end of period H3>18:30 lies between the beginning and end of period 55lkSperiod H begins at "H: BH B0;3"period 5 begins at 19:000}}iQperiod H ends at "H: BH B0;3 period 5 ends at 22:000ptyou can start the job at "H: BH B0; because it is within a working period3Nyou can start the job at 19:00 because it is within a working perioddG'the number of hours from the start of the job to the next breakD0*cum hours at end of period H3(cum hours at end of period 122Q=7the job starts at cum hour H of the calendar3:the job starts at cum hour 9.00 of the calendarrP<6the job stops at cum hour H of the calendar38the job stops at cum hour 0.50 of the calendarrE1+the job stops during period HhTHperiod H ends H working hours into the calendarM 9-period H ends at hour Hg!SGthe job will stop H hours before period H ends=")#the job will stop at hour H#$'CHANGE THIS FROM A + TO A -[''With a series of substitutions, the 15 formulas can be condensed into 4 as follows: )'Calc1 *'Calc2 +'Calc3,'Job Stop 1^Begin    $@ +@ /@  `)L u*a+bbbT  9+%  ^,Jbb    1^End     /@    'Starting a job at:'Work for:E)'@MATCH(E16,B9..B14)+(@MATCH(E16,B9..B14)=@MATCH(E16,C9..C14))U*'@INDEX(E9..E14,0,B39)-(@INDEX(C9..C14,0,B39)-@MAX(@INDEX(B9..B14,0,B39),E16))!+'@MATCH(B40-E17,E9..E14)+1;,'@INDEX(C9..C14,0,B41)-@INDEX(E9..E14,0,B41)+B40-E17)^Working Hours$ $ @ $ @ $ @ $ @ $ @ ")^Working Hours so far (Cum)$ $ @ $ @ $ @ $ "@ $ (@ 2@!@UE ͌A/"H: BH B0;3A 18:300 ^hours-@ -@ -@ 13@ b16@ b%3@T$@ 1(@ b$"@ $? 1  1 b1  b$! $" '@MATCH(E16,B9..B14)'@MATCH(E16,C9..C14)'+G21+(G21=G22)'@INDEX(B9..B14,0,G23)'@INDEX(C9..C14,0,G23)'@MAX(G24,E16)'+G25-G26'@INDEX(E9..E14,0,G23)i'+G28-G27'+G29+E17'@MATCH(G30,E9..E14)+1'@INDEX(E9..E14,0,G31) '@INDEX(C9..C14,0,G31)!'+G32-G30"'+G33-G34 Section 28$u<7~7, u   G     G [!B   Q@Arrow62 X {GX ? "!B   *g@Arrow63 X {GX ? "\B         6'Applying Calendar Formula 3 to a Pull Scheduler'Here is the schedule from Section 9 again, but this time it works backwards from the stop of the last job. ^Seq          8'@MATCH(H14-D14,Section 8:I$19..Section 8:I$50)+1i'@INDEX(Section 8:H$19..Section 8:H$50,0,I14)-@INDEX(Section 8:I$19..Section 8:I$50,0,I14)+H14-D14k'Note that the calendar formulas are the same as the push schedule, except that the days in column D''are subracted instead of added.'Stop of last Job: ^Jobs  ^job A  ^job C  ^job D  ^job E  ^job B  ^Hours           ^Days  ?    UUUUUU?    ?    UUUUUU?    ?  K @@ ^Stop3 ]  ;3 ]  ;3 ]  ;3 ]  ;3 ] UUUUK@ ;S ?  e  e  e  e  e UUUUK@ ^Calc1` L 111` L 111` L 111` L 111` L 111 ^Calc2u a+bbbT  111u a+bbbT  111u a+bbbT  111u a+bbbT  111u a+bbbT  111 ^Calc39 %  19 %  19 %  19 %  19 %  1 ^Start^  m Jbb   11^  m Jbb   11^  m Jbb   11^  m Jbb   11^  m Jbb   11    u   u   u   u   u  Section 29$u<7~L7,,p , :: ރރ      ::::::::::::: [!B  "rVrV@Arrow64 X {GX ? "!B  # Vkq@Arrow65 X {GX ? "\%#     , 'Multiple Work Centre - Pull Schedule '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 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. 'PUSH SCHEDULE: !^W/C ^6 ^6 ^6 ^7 ^7 ^7 ^8 ^8 ^8 'PULL SCHEDULE: !^W/C ^6 ^6 ^6 ^7 ^7 ^7 ^8 ^8 ^8 ' "'+M32-(E32/24)( #'@MIN(@IF(A32=A33,K33,$K$21),H32) %'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.  !^Jobs  ^job A  ^job B  ^job C  ^job A  ^job B  ^job C  ^job A  ^job B  ^job C  !^Jobs  ^job A  ^job B  ^job C  ^job A  ^job B  ^job C  ^job A  ^job B  ^job C  !^Op                    !^Op                   )^Job/Op6 ^S"i/H3 A/116 ~S"i/H3 B/116 S"i/H3 C/116 S"i/H3 A/226 S"i/H3 B/226 S"i/H3 C/226 T"i/H3 A/336 >T"i/H3 B/336 ^T"i/H3 C/33 )^Job/Op6 T"i/H3 A/116 T"i/H3 B/116 T"i/H3 C/116 U"i/H3 A/226 .U"i/H3 B/226 NU"i/H3 C/226 nU"i/H3 A/336 U"i/H3 B/336 U"i/H3 C/33 )^Dur- ation Hours                   )^Dur- ation Hours                   )^Previous Op: nS&i/ H3 A/00: S&i/ H3 B/00: S&i/ H3 C/00: S&i/ H3 A/11: S&i/ H3 B/11: T&i/ H3 C/11: .T&i/ H3 A/22: NT&i/ H3 B/22: nT&i/ H3 C/22 )^Next Op: T&i/ H3 A/22: T&i/ H3 B/22: T&i/ H3 C/22: U&i/ H3 A/33: >U&i/ H3 B/33: ^U&i/ H3 C/33: ~U&i/ H3 A/44: U&i/ H3 B/44: U&i/ H3 C/44 )^Previous Op Offset6 "  6 "  6 "  6 "@"  6 $@"  6 &@"  6 (@"  6 *@"  6 ,@"   )^Next Op Offset: :@&   ": ;@&   ": <@&   ": =@&   ": >@&   ": ?@&   ": &   ": &   ": &   " 'Start of first job: 1^Stop of Previous Op= ? )2b;= ? )2b;= ? )2b;= @ )2b;= UUUU!@ )2b;=  8@ )2b;=  @ )2b;=  4@ )2b;=  @@ )2b; 'Stop of last job: 1^Start of Next OpA  @ -2b;"A  UUUU!@ -2b;"A  :@ -2b;"A  "@ -2b;"A  4@ -2b;"A  B@ -2b;"A  ^@ -2b;"A  ^@ -2b;"A  ^@ -2b;"/ "'- 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  8 ?  ?  ?  @  UUUU!@   8@   @   4@   @@   8  @   UUUU!@   :@   "@   4@   B@   ^@   ^@   ^@  )^Wait hoursH  4 ;  H 4 ;  H  4 ;  H 2@ 4 ;  H +@ 4 ;  H @ 4 ;  H ;@ 4 ;  H .@ 4 ;  H  4 ;   )^Wait hoursA  - ; A  - ; A @ - ; A  - ; A  - ; A @ - ; A  - ; A  - ; A  - ;  { @ A^StartD @ 0  ;TD @ 0  ;TD UUUU!@ 0  ;TD @ 0  ;TD UUUU!@ 0  ;TD  8@ 0  ;TD  @ 0  ;TD  4@ 0  ;TD  B@ 0  ;T } ^@  A^Start)  @    )  @    )  $@    )  @    )  UUUU!@    )  :@    )  "@    )  4@    )  B@      UUUUUU?  H @  @  UUUU!@  @  UUUU!@  8@   @   4@  B@   ^@   H  @   @  $@   @   UUUU!@  :@   "@   4@  B@  A^Stop) @    ) UUUU!@    ) 8@    ) @    ) 4@    )  @@    )  @    )  B@    )  ^@     A^Stop<  @ ( ;S <  UUUU!@ ( ;S <  :@ ( ;S <  UUUU!@ ( ;S <  4@ ( ;S <  B@ ( ;S <  4@ ( ;S <  B@ ( ;S <  ^@ ( ;S   H @  UUUU!@  8@  @  4@  @@   @   B@  ^@   H  @   UUUU!@  :@   UUUU!@   4@  B@   4@   B@  ^@  UUUUUU?    @ Z 3   ;;; ;         3    3   ;;; ;         3   n 3   ;;; ;         3    3   ;;; ;         3   R 3   ;;; ;         3    3   ;;; ;         3   N‹ 3   ;;; ;         3   ‹ 3   ;;; ;         3   2Ë 3   ;;; ;         3   UUUUUU?    @ Ë 3   ;;; ;         3    ċ 3   ;;; ;         3   jċ 3   ;;; ;         3   ċ 3   ;;; ;         3   V 3   ;;; ;         3    3   ;;; ;         3   F 3   ;;; ;         3    3   ;;; ;         3   * 3   ;;; ;         3   UUUUUU?  @$ @   @ 3   ;;; ;           3   ;;; ;         3   z 3   ;;; ;         3    3   ;;; ;         3   ^ 3   ;;; ;         3    3   ;;; ;         3   Z‹ 3   ;;; ;         3   ‹ 3   ;;; ;         3   >Ë 3   ;;; ;         3   UUUUUU?  @ @  @ 3   ;;; ;          ċ 3   ;;; ;         3   vċ 3   ;;; ;         3   ċ 3   ;;; ;         3   b 3   ;;; ;         3    3   ;;; ;         3   R 3   ;;; ;         3    3   ;;; ;         3   6 3   ;;; ;         3   UUUUUU?   . VUUU@ -q=   @ 3   ;;; ;           3   ;;; ;         3    3   ;;; ;         3    3   ;;; ;         3   j 3   ;;; ;         3    3   ;;; ;         3   f‹ 3   ;;; ;         3   ‹ 3   ;;; ;         3   JË 3   ;;; ;         3   UUUUUU?    VUUU@  @ 3   ;;; ;          "ċ 3   ;;; ;         3   ċ 3   ;;; ;         3   ċ 3   ;;; ;         3   n 3   ;;; ;         3    3   ;;; ;         3   ^ 3   ;;; ;         3    3   ;;; ;         3   B 3   ;;; ;         3   UUUUUU?   $ @  @ 3   ;;; ;          @ 3   ;;; ;           3   ;;; ;         3   @ 3   ;;; ;          v 3   ;;; ;         3    3   ;;; ;         3   r‹ 3   ;;; ;         3   ‹ 3   ;;; ;         3   VË 3   ;;; ;         3   UUUUUU?    @ @ 3   ;;; ;          @ 3   ;;; ;          ċ 3   ;;; ;         3   ċ 3   ;;; ;         3   z 3   ;;; ;         3    3   ;;; ;         3   j 3   ;;; ;         3    3   ;;; ;         3   N 3   ;;; ;         3   UUUUUU?  @$  @  f 3   ;;; ;         3   @ 3   ;;; ;           3   ;;; ;         3   @ 3   ;;; ;           3   ;;; ;         3    3   ;;; ;         3   @ 3   ;;; ;          ‹ 3   ;;; ;         3   bË 3   ;;; ;         3   UUUUUU?  @  @ Ë 3   ;;; ;         3    @ 3   ;;; ;          ċ 3   ;;; ;         3   ŋ 3   ;;; ;         3    3   ;;; ;         3    3   ;;; ;         3   v 3   ;;; ;         3    3   ;;; ;         3   Z 3   ;;; ;         3   UUUUUU?   $ WUUU@  r 3   ;;; ;         3   @ 3   ;;; ;           3   ;;; ;         3    3   ;;; ;         3    3   ;;; ;         3   ‹ 3   ;;; ;         3    @ 3   ;;; ;          Ë 3   ;;; ;         3   nË 3   ;;; ;         3   UUUUUU?    WUUU@ Ë 3   ;;; ;         3    @ 3   ;;; ;          ċ 3   ;;; ;         3   @ 3   ;;; ;           3   ;;; ;         3     3   ;;; ;         3    3   ;;; ;         3    3   ;;; ;         3   f 3   ;;; ;         3   UUUUUU?   $  @  ~ 3   ;;; ;         3   ? 3   ;;; ;          @ 3   ;;; ;            3   ;;; ;         3   @ 3   ;;; ;          ‹ 3   ;;; ;         3   ~‹ 3   ;;; ;         3   Ë 3   ;;; ;         3   zË 3   ;;; ;         3   UUUUUU?     @ Ë 3   ;;; ;         3   ? 3   ;;; ;          @ 3   ;;; ;          ? 3   ;;; ;          @ 3   ;;; ;           3   ;;; ;         3   @ 3   ;;; ;           3   ;;; ;         3   r 3   ;;; ;         3   UUUUUU?   @$ *@   3   ;;; ;         3    3   ;;; ;         3    @ 3   ;;; ;           3   ;;; ;         3   @ 3   ;;; ;          ‹ 3   ;;; ;         3   ‹ 3   ;;; ;         3    ? 3   ;;; ;          Ë 3   ;;; ;         3   UUUUUU?   @ *@ Ë 3   ;;; ;         3   .ċ 3   ;;; ;         3    @ 3   ;;; ;          ŋ 3   ;;; ;         3   @ 3   ;;; ;          " 3   ;;; ;         3   @ 3   ;;; ;           ? 3   ;;; ;          ~ 3   ;;; ;         3   UUUUUU?   $ XUUU5@   3   ;;; ;         3    3   ;;; ;         3   ? 3   ;;; ;          " 3   ;;; ;         3    3   ;;; ;         3    @ 3   ;;; ;          ‹ 3   ;;; ;         3    @ 3   ;;; ;          Ë 3   ;;; ;         3   UUUUUU?    XUUU5@ Ë 3   ;;; ;         3   :ċ 3   ;;; ;         3   @ 3   ;;; ;          & 3   ;;; ;         3    3   ;;; ;         3   @ 3   ;;; ;           3   ;;; ;         3    @ 3   ;;; ;           3   ;;; ;         3   UUUUUU?   $ @@   3   ;;; ;         3    3   ;;; ;         3    3   ;;; ;         3   . 3   ;;; ;         3    3   ;;; ;         3   *‹ 3   ;;; ;         3   ‹ 3   ;;; ;         3   ? 3   ;;; ;           @ 3   ;;; ;          UUUUUU?    @@ Ë 3   ;;; ;         3   Fċ 3   ;;; ;         3   ċ 3   ;;; ;         3   2 3   ;;; ;         3    3   ;;; ;         3   ? 3   ;;; ;           3   ;;; ;         3   ? 3   ;;; ;           @ 3   ;;; ;          UUUUUU?  @@$ J@   3   ;;; ;         3    3   ;;; ;         3    3   ;;; ;         3   : 3   ;;; ;         3    3   ;;; ;         3   6‹ 3   ;;; ;         3   ‹ 3   ;;; ;         3   Ë 3   ;;; ;         3    @ 3   ;;; ;          UUUUUU?  @@ J@ Ë 3   ;;; ;         3   Rċ 3   ;;; ;         3   ċ 3   ;;; ;         3   > 3   ;;; ;         3    3   ;;; ;         3   . 3   ;;; ;         3    3   ;;; ;         3    3   ;;; ;         3    @ 3   ;;; ;          UUUUUU?   $ YUUUU@   3   ;;; ;         3    3   ;;; ;         3    3   ;;; ;         3   F 3   ;;; ;         3    3   ;;; ;         3   B‹ 3   ;;; ;         3    ‹ 3   ;;; ;         3    &Ë 3   ;;; ;         3   @ 3   ;;; ;          UUUUUU?    YUUUU@  Ë 3   ;;; ;         3    ^ċ 3   ;;; ;         3   ċ 3   ;;; ;         3    J 3   ;;; ;         3     3   ;;; ;         3   : 3   ;;; ;         3     3   ;;; ;         3     3   ;;; ;         3   @ 3   ;;; ;         $ `@  `@ ! Section 30$u<7~7,:с u           ރ::::::::::::: [!B !r3S@Arrow66 X {GX ? "!B " #r@Arrow67 X {GX ? "\$"     K!'Repetitive Production Through Multiple Work Centres - Pull Schedule%!'The next operation can start  !!^W/C !^6 ! ^6 ! ^6 ! ^7 ! ^7 ! ^7 !^8 !^8 !^8%!'The next operation can start  !!^W/C !^6 !^6 !^6 !^7 !^7 !^7 !^8 !^8 !^8!!'@MIN(Q31-(G31/24),J31)!"'@IF(C31=C32,O32,L31)!$'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. !!^Jobs !^job A ! ^job B ! ^job C ! ^job A ! ^job B ! ^job C !^job A !^job B !^job C !!^Jobs !^job A !^job B !^job C !^job A !^job B !^job C !^job A !^job B !^job C !!^Op ! !  !  !  !  !  ! ! ! !!^Op ! ! ! ! ! ! ! ! !!)^Job/Op6!.V"i/H3!A/116! NV"i/H3! B/116! nV"i/H3! C/116! V"i/H3! A/226! V"i/H3! B/226! V"i/H3! C/226!V"i/H3!A/336!W"i/H3!B/336!.W"i/H3!C/33!)^Job/Op6!6X"i/H3!A/116!VX"i/H3!B/116!vX"i/H3!C/116!X"i/H3!A/226!X"i/H3!B/226!X"i/H3!C/226!X"i/H3!A/336!Y"i/H3!B/336!6Y"i/H3!C/33 !)^Qty ! !  ! l !  !  ! l ! ! !l !)^Qty ! ! !l ! ! !l ! ! !l !!)^Units per Hour !A ! < ! K ! i ! _ ! n !P !U !_ !!)^Units per Hour !A !< !K !i !_ !n !P !U !_<!'hour after the start of the previous operation (Lag)!)^Dur- ation Hours$!5;;7@ $! UUUUUUB@ $! UUUUUU9@ $! 5%I$I,@ $! k(7@ $! F]tE1@ $!52@ $!9@ $!4@ <!'hour after the start of the previous operation (Lag)!)^Dur- ation Hours$!5;;7@ $!UUUUUUB@ $!UUUUUU9@ $!5%I$I,@ $!k(7@ $!F]tE1@ $!52@ $!9@ $!4@ !)^Previous Op:!>V&i/ H3!A/00:! ^V&i/ H3! B/00:! ~V&i/ H3! C/00:! V&i/ H3! A/11:! V&i/ H3! B/11:! V&i/ H3! C/11:!V&i/ H3!A/22:!W&i/ H3!B/22:!>W&i/ H3!C/22!)^Next Op:!FX&i/ H3!A/22:!fX&i/ H3!B/22:!X&i/ H3!C/22:!X&i/ H3!A/33:!X&i/ H3!B/33:!X&i/ H3!C/33:!Y&i/ H3!A/44:!&Y&i/ H3!B/44:!FY&i/ H3!C/44!)^Previous Op Offset6!"6! "6! "6!  @"6! "@"6! $@"6!&@"6!(@"6!*@"!)^Next Op Offset:!9@& #:!:@& #:!;@& #:!<@& #:!=@& #:!>@& #:!& #:!& #:!& #t!!'- 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.! !1^Start of Previous Op +LagJ !=? 62b  ;J ! ? 62b  ;J ! ? 62b  ;J ! =@ 62b  ;J ! oo@ 62b  ;J ! S2%S6@ 62b  ;J !=@ 62b  ;J !N@ 62b  ;J ! z7@ 62b  ; !1^Start of Next Op -LagN !=!@ :2b  ;#N !!@ :2b  ;#N ! A<@ :2b  ;#N !=v@ :2b  ;#N !v@ :2b  ;#N ! oo=@ :2b  ;#N !=ooY@ :2b  ;#N !ooY@ :2b  ;#N ! ooY@ :2b  ;#  !8 !E?  ! ?  ! ?  ! E@  ! oo@  ! S2%S6@  !E@  !N@  !z7@   !8 !E!@  !!@  !A<@  !Ev@  !v@  !oo=@  !EooY@  !ooY@  !ooY@  !'Start of first job: !1^Stop of Previous Op +LagJ !M? 62b  ;J ! ? 62b  ;J ! ? 62b  ;J ! Moo@ 62b  ;J ! S2%S6@ 62b  ;J ! AX@ 62b  ;J !MN@ 62b  ;J !z7@ 62b  ;J !ooY@ 62b  ; !'Stop of last job: !1^Stop of Next Op -LagN !M!@ :2b  ;#N !A<@ :2b  ;#N !NV@ :2b  ;#N !Mv@ :2b  ;#N !oo=@ :2b  ;#N !AX@ :2b  ;#N !MooY@ :2b  ;#N !ooY@ :2b  ;#N !ooY@ :2b  ;#  !8 !U?  ! ?  ! %?  ! Uoo@  ! S2%S6@  ! %AX@  !UN@  !z7@  !%ooY@   !8 !U!@  !A<@  !%NV@  !Uv@  !oo=@  !%AX@  !UooY@  !ooY@  !%ooY@  !)^Wait hoursH !] 4 ;  H !  4 ;  H ! - 4 ;  H ! ]? 4 ;  H !  4 ;  H ! - 4 ;  H !]? 4 ;  H ! 4 ;  H !- 4 ;   !)^Wait hoursA !] - ; A ! - ; A !- - ; A !] - ; A ! - ; A !- - ; A !] - ; A ! - ; A !- - ; !@ !A^StartD!eUUUU@ 0  ;TD! A@ 0  ;TD! 54@ 0  ;TD! e@ 0  ;TD! oo@ 0  ;TD! 5S2%S6@ 0  ;TD!e@ 0  ;TD!N@ 0  ;TD!5z7@ 0  ;T!ooY@  !A^Start2!eVUUU@    S2!A@    S2!54@    S2!ev@    S2!v@    S2!5oo=@    S2!eAiB@    S2!AiB@    S2!5N>@    S!#UUUUUU? !H!mUUUU@ ! A@ ! =4@ ! m@ ! oo@ ! =S2%S6@ !m@ !N@ !=z7@ !%ooY@  !H!mVUUU@ !A@ !=4@ !mv@ !v@ !=oo=@ !mAiB@ !AiB@ !=N>@  !A^Stop2!uA@    T2! 4@    T2! ENV@    T2! uoo@    T2! S2%S6@    T2! EAX@    T2!uN@    T2!z7@    T2!EooY@    T !A^Stop3!uA@ ;3!4@ ;3!ENV@ ;3!uv@ ;3!oo=@ ;3!EAX@ ;3!uAiB@ ;3!N>@ ;3!EooY@ ; !H!}A@ ! 4@ ! MNV@ ! }oo@ ! S2%S6@ ! MAX@ !}N@ !z7@ !MooY@  !H!}A@ !4@ !MNV@ !}v@ !oo=@ !MAX@ !}AiB@ !N>@ !MooY@  !Q^Effective Units per Hour1!?P@     1! /M@     1! UR@     1! ?P@     1! /M@     1! UR@     1!?P@     1!/M@     1!UR@      !Q^Effective Units per Hour1!?P@     1!/M@     1!UR@     1!T@     1!?U@     1!UW@     1!T@     1!?U@     1!UW@     !UUUUUU?  !p!@!@p@ 5   ;;;  ;!  5   ;;;  ;3 ! ! ]J 5   ;;;  ;3 ! Y! f`h@ 5   ;;;  ;!  5   ;;;  ;3 ! ! ]F 5   ;;;  ;3 ! Y!@`@ 5   ;;;  ;! 5   ;;;  ;3 !!] 5   ;;;  ;3 !Y!UUUUUU?  !p!@!?p@ 5   ;;;  ;! 5   ;;;  ;3 !!]* 5   ;;;  ;3 !Y!~ 5   ;;;  ;3 !! 5   ;;;  ;3 !!]J 5   ;;;  ;3 !Y! 5   ;;;  ;3 !! 5   ;;;  ;3 !!]v 5   ;;;  ;3 !Y!UUUUUU? !}@$!@ !?@ 5   ;;;  ;!  5   ;;;  ;3 ! ! ]V 5   ;;;  ;3 ! Y! ?@ 5   ;;;  ;!   5   ;;;  ;3 ! ! ]R 5   ;;;  ;3 ! Y!?@ 5   ;;;  ;! 5   ;;;  ;3 !!]" 5   ;;;  ;3 !Y!UUUUUU? !}@!@!?@ 5   ;;;  ;! 5   ;;;  ;3 !!]6 5   ;;;  ;3 !Y! 5   ;;;  ;3 !! 5   ;;;  ;3 !!]V 5   ;;;  ;3 !Y! 5   ;;;  ;3 !!" 5   ;;;  ;3 !!] 5   ;;;  ;3 !Y!UUUUUU?  !.!VUUU@ -q= !?@ 5   ;;;  ;!  5   ;;;  ;3 ! ! ]b 5   ;;;  ;3 ! Y! ?@ 5   ;;;  ;!  5   ;;;  ;3 ! ! ]^ 5   ;;;  ;3 ! Y!?@ 5   ;;;  ;! 5   ;;;  ;3 !!]. 5   ;;;  ;3 !Y!UUUUUU?  !!VUUU@!?@ 5   ;;;  ;! 5   ;;;  ;3 !!]B 5   ;;;  ;3 !Y! 5   ;;;  ;3 !! 5   ;;;  ;3 !!]b 5   ;;;  ;3 !Y! 5   ;;;  ;3 !!. 5   ;;;  ;3 !!] 5   ;;;  ;3 !Y!UUUUUU?  !p$!@ !Gh@ 5   ;;;  ;!  uzb'vr@ 5   ;;;  ;! ]n 5   ;;;  ;3 ! Y! fp@ 5   ;;;  ;! Nlm@ 5   ;;;  ;! ]j 5   ;;;  ;3 ! Y!t@ 5   ;;;  ;!Ne@ 5   ;;;  ;!]: 5   ;;;  ;3 !Y!UUUUUU?  !p!@!_h@ 5   ;;;  ;! uzb'vr@ 5   ;;;  ;!]N 5   ;;;  ;3 !Y!yc@ 5   ;;;  ;! 5   ;;;  ;3 !!]n 5   ;;;  ;3 !Y!@B{@ 5   ;;;  ;!: 5   ;;;  ;3 !!] 5   ;;;  ;3 !Y!UUUUUU? !}@$! @ ! 5   ;;;  ;3 !! /}@ 5   ;;;  ;! ]z 5   ;;;  ;3 ! Y!  5   ;;;  ;3 ! ! /}@ 5   ;;;  ;! ]v 5   ;;;  ;3 ! Y! 5   ;;;  ;3 !!/}@ 5   ;;;  ;!]F 5   ;;;  ;3 !Y!UUUUUU? !}@! @!v 5   ;;;  ;3 !!/}@ 5   ;;;  ;!]Z 5   ;;;  ;3 !Y!@ 5   ;;;  ;! 5   ;;;  ;3 !!]z 5   ;;;  ;3 !Y!@ 5   ;;;  ;!F 5   ;;;  ;3 !!] 5   ;;;  ;3 !Y!UUUUUU?  !$!WUUU@ ! 5   ;;;  ;3 !! /}@ 5   ;;;  ;! ] 5   ;;;  ;3 ! Y!  5   ;;;  ;3 ! ! /}@ 5   ;;;  ;! ] 5   ;;;  ;3 ! Y! 5   ;;;  ;3 !!/}@ 5   ;;;  ;!]R 5   ;;;  ;3 !Y!UUUUUU?  !!WUUU@! 5   ;;;  ;3 !!/}@ 5   ;;;  ;!]f 5   ;;;  ;3 !Y! t@ 5   ;;;  ;!|^3vb7t@ 5   ;;;  ;!] 5   ;;;  ;3 !Y! y@ 5   ;;;  ;!im@ 5   ;;;  ;!] 5   ;;;  ;3 !Y!UUUUUU?  !p$! @ ! 5   ;;;  ;3 !! /}@ 5   ;;;  ;! ] 5   ;;;  ;3 ! Y!  5   ;;;  ;3 ! ! /}@ 5   ;;;  ;! ] 5   ;;;  ;3 ! Y! 5   ;;;  ;3 !!/}@ 5   ;;;  ;!]^ 5   ;;;  ;3 !Y!UUUUUU?  !p! @! 5   ;;;  ;      !"#$%&'()*+,-./0123456789:;<=>?@ABCDEFGHIJKLMNOPQRSTUVWXYZ[\]^_`abcdefghijklmnopqrstuvwxyz{|~3 !!/}@ 5   ;;;  ;!]r 5   ;;;  ;3 !Y! 5   ;;;  ;3 !!?@ 5   ;;;  ;!] 5   ;;;  ;3 !Y! 5   ;;;  ;3 !!?@ 5   ;;;  ;!] 5   ;;;  ;3 !Y!UUUUUU? !} @$!*@ ! 5   ;;;  ;3 !! l }@ 5   ;;;  ;! ]2Ʊ;3@ 5   ;;;  ;!  5   ;;;  ;3 ! ! /}@ 5   ;;;  ;! ] 5   ;;;  ;3 ! Y! 5   ;;;  ;3 !!/}@ 5   ;;;  ;!]j 5   ;;;  ;3 !Y!UUUUUU? !} @!*@! 5   ;;;  ;3 !!l }@ 5   ;;;  ;!]2Ʊ;3@ 5   ;;;  ;! 5   ;;;  ;3 !!?@ 5   ;;;  ;!] 5   ;;;  ;3 !Y! 5   ;;;  ;3 !!?@ 5   ;;;  ;!] 5   ;;;  ;3 !Y!UUUUUU?  !$!XUUU5@ ! 5   ;;;  ;3 !!  5   ;;;  ;3 ! ! ]@ 5   ;;;  ;!  5   ;;;  ;3 ! ! LNF@ 5   ;;;  ;! ]Rn@ 5   ;;;  ;! 5   ;;;  ;3 !!"vb'Z@ 5   ;;;  ;!] ;S}@ 5   ;;;  ;!UUUUUU?  !!XUUU5@! 5   ;;;  ;3 !! 5   ;;;  ;3 !!]@ 5   ;;;  ;! 5   ;;;  ;3 !!MN$@ 5   ;;;  ;!]֕b'f@ 5   ;;;  ;! 5   ;;;  ;3 !!N̂@ 5   ;;;  ;!]1NU@ 5   ;;;  ;!UUUUUU?  !p$!@@ ! 5   ;;;  ;3 !! & 5   ;;;  ;3 ! ! ]@ 5   ;;;  ;!  5   ;;;  ;3 ! ! " 5   ;;;  ;3 ! ! ]@ 5   ;;;  ;! 5   ;;;  ;3 !! 5   ;;;  ;3 !!]@ 5   ;;;  ;!UUUUUU?  !p!@@! 5   ;;;  ;3 !! 5   ;;;  ;3 !!]@ 5   ;;;  ;! 5   ;;;  ;3 !!& 5   ;;;  ;3 !!]@ 5   ;;;  ;! 5   ;;;  ;3 !!R 5   ;;;  ;3 !!]@ 5   ;;;  ;!UUUUUU? !}@@$!J@ ! 5   ;;;  ;3 !! 2 5   ;;;  ;3 ! ! ]@ 5   ;;;  ;!  5   ;;;  ;3 ! ! . 5   ;;;  ;3 ! ! ]@ 5   ;;;  ;! 5   ;;;  ;3 !! 5   ;;;  ;3 !!]@ 5   ;;;  ;!UUUUUU? !}@@!J@! 5   ;;;  ;3 !! 5   ;;;  ;3 !!]@ 5   ;;;  ;! 5   ;;;  ;3 !!2 5   ;;;  ;3 !!]@ 5   ;;;  ;! 5   ;;;  ;3 !!^ 5   ;;;  ;3 !!]@ 5   ;;;  ;!UUUUUU?  !$!YUUUU@ ! 5   ;;;  ;3 !! > 5   ;;;  ;3 ! ! e~;1T@ 5   ;;;  ;!  5   ;;;  ;3 ! ! : 5   ;;;  ;3 ! ! eexc@ 5   ;;;  ;! 5   ;;;  ;3 !!  5   ;;;  ;3 !!eJȺl@ 5   ;;;  ;!UUUUUU?  !!YUUUU@! 5   ;;;  ;3 !! 5   ;;;  ;3 !!e~;1T@ 5   ;;;  ;! 5   ;;;  ;3 !!> 5   ;;;  ;3 !!efةh@ 5   ;;;  ;!  5   ;;;  ;3 !!j 5   ;;;  ;3 !!eNDr@ 5   ;;;  ; !$!-`@  !!`@!" Section 31$u<7~T7, , X: с   :               S"'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: " !^W/C " ^6 " ^7 " ^8%"'The next operation can start  "!^W/C "^6 "^6 "^6 "^7 "^7 "^7 "^8 "^8 "^8"'"'- 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" !^Calendar" ^Calendar 2" ^Calendar 1" ^Calendar 2"!^Calendar+"U" " 3"Calendar 22+"ȆU" " 3"Calendar 22+"ކU" " 3"Calendar 22+"U" " 3"Calendar 11+" U" " 3"Calendar 11+" U" " 3"Calendar 11+"6U" " 3"Calendar 22+"LU" " 3"Calendar 22+"bU" " 3"Calendar 22"i^Product"^prod A"^prod B"^prod C"^prod A"^prod B"^prod C"^prod A"^prod B"^prod C "!^Op " " " " " " " " "")^Prod/Op6"Y"i/H3"A/116"Y"i/H3"B/116"Z"i/H3"C/116"&Z"i/H3"A/226"FZ"i/H3"B/226"fZ"i/H3"C/226"Z"i/H3"A/336"Z"i/H3"B/336"Z"i/H3"C/33 ")^Qty " " "l " " "l " " "l "")^Units per Hour "A "< "K "i "_ "n "P "U "_<"'hour after the start of the previous operation (Lag)")^Dur- ation Hours$"};;7@ $"UUUUUUB@ $"]UUUUUU9@ $"}%I$I,@ $"k(7@ $"]F]tE1@ $"}2@ $"9@ $"4@  ")^Days "ON?   "qq?   "e98?   "0 0 ?   " #?   "e|?   "?   "AAAAAA?   "?   ")^Next Op: "Y&i/ H3 "A/22: "Y&i/ H3 "B/22: "Z&i/ H3 "C/22: "6Z&i/ H3 "A/33: "VZ&i/ H3 "B/33: "vZ&i/ H3 "C/33: "Z&i/ H3 "A/44: "Z&i/ H3 "B/44: "Z&i/ H3 "C/44 ")^Next Op Offset6 "5@"6 "6@"6 "7@"6 "8@"6 "9@"6 ":@"6 ""6 ""6 "" "^Stop of Next OpI "Y@ 52 b;I "-@ 52 b;I "mVUUU@ 52 b;I "ҳ@ 52 b;I "TUUU@ 52 b;I "m@ 52 b;I "po@ 52 b;I "po@ 52 b;I "po@ 52 b;  " "Y@  "-@  "uVUUU@  "ҳ@  "TUUU@  "u@  "po@  "po@  "po@ ! "i^1st calendar calculations "1^Start of Next OpI "Y@ 52 b;  +I " -@ 52 b;  +I "}VUUU@ 52 b;  +I "ҳ@ 52 b;  +I " TUUU@ 52 b;  +I "}@ 52 b;  +I "po@ 52 b;  +I " po@ 52 b;  +I "po@ 52 b;  + "8 "8"Y@ "-@ "VUUU@ "ҳ@ "TUUU@ "@ "po@ "po@ "po@  "8 "'Calc1"<@0[:G$6..:G$100s:G$6..:G$100s:H$6..:H$100s "B@0[:G$6..:G$100s:G$6..:G$100s:H$6..:H$100s "E@0[:G$6..:G$100s:G$6..:G$100s:H$6..:H$100s ":@0[:G$6..:G$100s:G$6..:G$100s:H$6..:H$100s "D@0[:G$6..:G$100s:G$6..:G$100s:H$6..:H$100s "I@0[:G$6..:G$100s:G$6..:G$100s:H$6..:H$100s "F@0[:G$6..:G$100s:G$6..:G$100s:H$6..:H$100s "F@0[:G$6..:G$100s:G$6..:G$100s:H$6..:H$100s "F@0[:G$6..:G$100s:G$6..:G$100s:H$6..:H$100s  "8 "'Calc2"@0j:I$6..:I$100sb:H$6..:H$100sb:G$6..:G$100sbT  " @0j:I$6..:I$100sb:H$6..:H$100sb:G$6..:G$100sbT  "@@0j:I$6..:I$100sb:H$6..:H$100sb:G$6..:G$100sbT  "@ @0j:I$6..:I$100sb:H$6..:H$100sb:G$6..:G$100sbT  "TUUU@0j:I$6..:I$100sb:H$6..:H$100sb:G$6..:G$100sbT  "TUUU@0j:I$6..:I$100sb:H$6..:H$100sb:G$6..:G$100sbT  "`*@0j:I$6..:I$100sb:H$6..:H$100sb:G$6..:G$100sbT  "`*@0j:I$6..:I$100sb:H$6..:H$100sb:G$6..:G$100sbT  "`*@0j:I$6..:I$100sb:H$6..:H$100sb:G$6..:G$100sbT   "8 "'Calc3U";@0A)  :I$6..:I$100s U"A@0A)  :I$6..:I$100s U"E@0A)  :I$6..:I$100s U":@0A)  :I$6..:I$100s U"D@0A)  :I$6..:I$100s U"I@0A)  :I$6..:I$100s U"E@0A)  :I$6..:I$100s U"E@0A)  :I$6..:I$100s U"E@0A)  :I$6..:I$100s  "8"1^Start of Next Op -Lag"@0}M:H$6..:H$100sb:I$6..:I$100sb    "@0}M:H$6..:H$100sb:I$6..:I$100sb    "@0}M:H$6..:H$100sb:I$6..:I$100sb    "y}}}@0}M:H$6..:H$100sb:I$6..:I$100sb    "TUUU@0}M:H$6..:H$100sb:I$6..:I$100sb    "VUUU@0}M:H$6..:H$100sb:I$6..:I$100sb    "@0}M:H$6..:H$100sb:I$6..:I$100sb    "@0}M:H$6..:H$100sb:I$6..:I$100sb    "@0}M:H$6..:H$100sb:I$6..:I$100sb     "@ "8"@ "%@ "@ "y}}}@ "%TUUU@ "VUUU@ "@ "%@ "@ "'Stop of last job:!"i^2nd calendar calculations "A^Stop3"3@ ;3"-78@ ;3"VUUU@ ;3"m@ ;3"->@ ;3"@ ;3"x}}}@ ;3"-@ ;3" po@ ; "8 "H"3@ "578@ "VUUU@ "m@ "5>@ "@ "x}}}@ "5@ "po@  "8 "'Calc4",@0[:G$6..:G$100s:G$6..:G$100s:H$6..:H$100s "?@0[:G$6..:G$100s:G$6..:G$100s:H$6..:H$100s "E@0[:G$6..:G$100s:G$6..:G$100s:H$6..:H$100s "A@0[:G$6..:G$100s:G$6..:G$100s:H$6..:H$100s "E@0[:G$6..:G$100s:G$6..:G$100s:H$6..:H$100s "I@0[:G$6..:G$100s:G$6..:G$100s:H$6..:H$100s "7@0[:G$6..:G$100s:G$6..:G$100s:H$6..:H$100s "A@0[:G$6..:G$100s:G$6..:G$100s:H$6..:H$100s "F@0[:G$6..:G$100s:G$6..:G$100s:H$6..:H$100s  "8 "'Calc5"?0j:I$6..:I$100sb:H$6..:H$100sb:G$6..:G$100sbT  "68@0j:I$6..:I$100sb:H$6..:H$100sb:G$6..:G$100sbT  "@@0j:I$6..:I$100sb:H$6..:H$100sb:G$6..:G$100sbT  "@0 @0j:I$6..:I$100sb:H$6..:H$100sb:G$6..:G$100sbT  "D]t@0j:I$6..:I$100sb:H$6..:H$100sb:G$6..:G$100sbT  "TUUU@0j:I$6..:I$100sb:H$6..:H$100sb:G$6..:G$100sbT  "@ @0j:I$6..:I$100sb:H$6..:H$100sb:G$6..:G$100sbT  " @0j:I$6..:I$100sb:H$6..:H$100sb:G$6..:G$100sbT  "`*@0j:I$6..:I$100sb:H$6..:H$100sb:G$6..:G$100sbT   "8 "'Calc6P"@0<$ :I$6..:I$100s P",@0<$ :I$6..:I$100s P"?@0<$ :I$6..:I$100s P"<@0<$ :I$6..:I$100s P"A@0<$ :I$6..:I$100s P"E@0<$ :I$6..:I$100s P",@0<$ :I$6..:I$100s P"7@0<$ :I$6..:I$100s P"A@0<$ :I$6..:I$100s "k@ "8 "A^Start"[@0xH:H$6..:H$100sb:I$6..:I$100sb   "=3@0xH:H$6..:H$100sb:I$6..:I$100sb   "78@0xH:H$6..:H$100sb:I$6..:I$100sb   "O} @0xH:H$6..:H$100sb:I$6..:I$100sb   "=m@0xH:H$6..:H$100sb:I$6..:I$100sb   ">@0xH:H$6..:H$100sb:I$6..:I$100sb   "2@0xH:H$6..:H$100sb:I$6..:I$100sb   "=x}}}@0xH:H$6..:H$100sb:I$6..:I$100sb   "@0xH:H$6..:H$100sb:I$6..:I$100sb   "s(oo? "@ "H"[@ "E3@ "78@ "O} @ "Em@ ">@ "2@ "Ex}}}@ "%@ !"q^3rd calendar calculations "'Calc7U",@0A)  :I$6..:I$100s U">@0A)  :I$6..:I$100s U"E@0A)  :I$6..:I$100s U"@@0A)  :I$6..:I$100s U"E@0A)  :I$6..:I$100s U"I@0A)  :I$6..:I$100s U"6@0A)  :I$6..:I$100s U"A@0A)  :I$6..:I$100s U"E@0A)  :I$6..:I$100s  ""A'Stop -lag"PUUU2@0}M:H$6..:H$100sb:I$6..:I$100sb    "M8@0}M:H$6..:H$100sb:I$6..:I$100sb    "@0}M:H$6..:H$100sb:I$6..:I$100sb    "Y@0}M:H$6..:H$100sb:I$6..:I$100sb    "M-@0}M:H$6..:H$100sb:I$6..:I$100sb    "VUUU@0}M:H$6..:H$100sb:I$6..:I$100sb    "ҳ@0}M:H$6..:H$100sb:I$6..:I$100sb    "MTUUU@0}M:H$6..:H$100sb:I$6..:I$100sb    "-@0}M:H$6..:H$100sb:I$6..:I$100sb     " "H"PUUU2@ "U8@ "@ "Y@ "U-@ "VUUU@ "ҳ@ "UTUUU@ "5@ "# Section 32$u<7~7,,,, ,!#$%,& с с с с сссс  $% [!B -0iV@Arrow68 X {GX ? "!B -1 v@Arrow69 X {GX ? "!B -4@Arrow70 X {GX ? "\f4...4.... 3 . . . ....%#'Push Pull Push - 3 Pass Logicp#'In Section 26 we had a series of jobs that were all required at the same time, but here we consider jobsr#'with different required times. We will start with a simple example of Job A required in 24 hours, and Jobm#'B required 48 hours from now. The objective is to try and meet the due date of each job, but to startr#'each job just in time. The logic works in 3 passes, and "shuffles" the jobs taking into consideration the #'following: # #^JobsT#'In the first pass job A starts now, and job B starts immediately after that: #^job A #^job Bj#'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 BI#'The third pass pushes job A later so that it starts at hour zero: #^job A # ^job B%#"'Here are some other examples: #$^job A #%^job B #'^job A #(^job B #*^job A #+^job B #-^job A #.^job B#0'@PUREMIN(C46,F47)#1'@MAX(D46,F46,I45)#4'+H46+B46/#'- 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 # #^Hours # # # # # #  #$ #% #' #( #* #+ #- #.( # #^Due # #0 # #0 # # 0 #$ #%0 #' #(0 #* #+0 #- #.0#^Push 1 #^Start##@@##@@## @@#$#%@@#'#(0@#*#+ @#-#.0@ # #^Stop$#@@ $#D@ $#@@ $#D@ $#@@ $# D@ $#$@@ $#%L@ $#'0@ $#(@@ $#* @ $#+D@ $#-0@ $#.L@ O#0'- pull stop is the earlier of the due time or the start of the next job'#1'- push 2 start is the later of:-#4'- push 2 stop is the start plus hours #^Pull #^Start # #$#D $#  $#D $#  $#$L $#%8 $#'@ $#(0 $#*D $#+@ $#-L $#.D  # #^Stop # #)#  )# )#  )#  )#$8 )#% )#'0 )#( )#*@ )#+ )#-D )#. #^Push 2 #^Start # # # #,#T,# @@T,#$T,#%@@T,#'T,#(0@T,#*T,#+ @T,#-T,#.0@T # #^Stop # # # #$#@@ $# D@ $#$@@ $#%L@ $#'0@ $#(@@ $#* @ $#+D@ $#-0@ $#.L@   #< # (###;3 # < # (###;3 # < # [(###;3 # ##< # +T(###;3 # ####< # (###;3 # < # .(###;3 # < #$ (###;3 #$ < #% (###;3 #% < #'(###;3 #'< #(6…(###;3 #(< #*~…(###;3 #*< #+…(###;3 #+< #-Å(###;3 #-< #.JÅ(###;3 #. #1'the earliest start #2'the push start$ #3'the stop of the previous job #^Day 1  #< #F[(###;3 #####< #(###;3 #< #[(###;3 ###< #(###;3 #< #[(###;3 #####< # :(###;3 # < #$6\(###;3 #$####< #%(###;3 #%< #'+T(###;3 #'####< #(B…(###;3 #(< #*ҁ(###;3 #*####< #+…(###;3 #+< #-6](###;3 #-####< #.VÅ(###;3 #.  # #0@  < # V[(###;3 # ####< # (###;3 # < # [(###;3 # ##< # (###;3 # < # [(###;3 # ####< # F(###;3 # < #$ F\(###;3 #$ ####< #% (###;3 #% < #'\(###;3 #'####< #(N…(###;3 #(< #*\(###;3 #*##< #+ҁ(###;3 #+####< #-F](###;3 #-####< #.bÅ(###;3 #.  # #8@  < #f[(###;3 #####< #*(###;3 #< #[(###;3 ###< #(###;3 #< #\(###;3 #####< # R(###;3 # < #$V\(###;3 #$####< #%(###;3 #%< #'\(###;3 #'##< #(v+T(###;3 #(####< #*…(###;3 #*< #+\(###;3 #+####< #-Å(###;3 #-< #.V](###;3 #.#### #^Day 2 #@@  < #v[(###;3 #####< #6(###;3 #< #z(###;3 #< #(###;3 #< #\(###;3 #####< # ^(###;3 # < #$f\(###;3 #$####< #%(###;3 #%< #'…(###;3 #'< #(,T(###;3 #(####< #*…(###;3 #*< #+](###;3 #+####< #-Å(###;3 #-< #.f](###;3 #.#### # #D@  <# (###;3 # <# [(###;3# ####<# (###;3 # <# (###;3 # <#  (###;3 # <# +T(###;3# ####<#$ (###;3 #$ <#% v\(###;3#% ####<#'…(###;3 #'<#(\(###;3#(##<#*…(###;3 #*<#+](###;3#+####<#-&Å(###;3 #-<#.v](###;3#.#### # #H@  <#(###;3 #<#B(###;3 #<#(###;3 #<#[(###;3###<#(###;3 #<# &\(###;3# ##<#$(###;3 #$<#%\(###;3#%####<#'…(###;3 #'<#(\(###;3#(##<#*…(###;3 #*<#+&](###;3#+##<#-2Å(###;3 #-<#.](###;3#.#### #L@  <# (###;3 # <# n(###;3 # <# (###;3 # <# (###;3 # <# "(###;3 # <# v(###;3 # <#$ (###;3 #$ <#% \(###;3#% ####<#'*…(###;3 #'<#(r…(###;3 #(<#*…(###;3 #*<#+…(###;3 #+<#->Å(###;3 #-<#.](###;3#.#####$ Section 33$u<7~7,,, с  uu   : u :: :ссссссссссссс  [!B   Gc@Arrow71 X {GX ? "!B  V@Arrow72 X {GX ? "!B  VF @Arrow73 X {GX ? "!B # $ @Arrow74 X {GX ? "!B # K & @Arrow75 X {GX ? "\#     !.$'Multiple Work Centre - 3 Pass Schedule$'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.I$'So now the scheduling of a job takes 7 things into consideration: $^W/C $^6 $^6 $^6 $^7 $^7 $^7 $^8 $^8 $^8$'Q$#'Try changing the hours and due figures, and see how the schedule behaves./$'- the start of the schedule (hour zero)($'- the length of the job in hours$ '- when the job is due9$ '- the stop of the previous job on the work centre6$ '- the start of the next job on the work centre7$ '- the stop of the previous operation on the job9$ '- the start of the next next operation on the job $^Jobs $^job A $^job B $^job C $^job A $^job B $^job C $^job A $^job B $^job C $^Op $ $ $ $ $ $ $ $ $$^Job/Op6$^"i/H3$A/116$f^"i/H3$B/116$""i/H3$C/116$b"i/H3$A/226$"i/H3$B/226$"i/H3$C/226$R"i/H3$A/336$"i/H3$B/336$⊄"i/H3$C/33 $^Hours $ $ $ $ $ $ $ $ $2$'These are the formulas that are different: $^Due $ $ $ $ $ $ $ $H $`4$'@IF(@ISERR(M26),F26,@INDEX(O$1..O$31,0,M26))*$'@PUREMIN(@IF(A26=A27,O27,999),N26),$'@MAX(J26,O26,Q26,@IF(A26=A25,S25,0))$^Push 1$^Prev. Op:$&^&i/ H3$A/00:$∄&i/ H3$B/00:$2&i/ H3$C/00:$r&i/ H3$A/11:$&i/ H3$B/11:$"&i/ H3$C/11:$b&i/ H3$A/22:$&i/ H3$B/22:$&i/ H3$C/22 $$)^Prev. Op Offset6$"$$ 6$"$$ 6$"$$ 6$1@"$$ 6$2@"$$ 6$3@"$$ 6$4@"$$ 6$5@"$$ 6$6@"$$ $$)^Stop of Prev. OpC$ /2b;C$ /2b;C$ /2b;C$0@ /2b;C$@@ /2b;C$D@ /2b;C$8@ /2b;C$P@ /2b;C$R@ /2b;  $ $^Start< $0(;< $0@0(;< $@@0(;< $0@0(;< $@@0(;< $P@0(;< $8@0(;< $P@0(;< $R@0(;  $ $^Stop$ $ 0@  $ $@@  $ $D@  $ $ 8@  $ $P@  $ $R@  $ $ D@  $ $R@  $ $V@   $^Pull $^Next Op: $6^&i/ H3 $A/22: $&i/ H3 $B/22: $B&i/ H3 $C/22: $&i/ H3 $A/33: $‰&i/ H3 $B/33: $2&i/ H3 $C/33: $r&i/ H3 $A/44: $Š&i/ H3 $B/44: $&i/ H3 $C/44  $ $)^Next Op Offset6 $4@"$$ 6 $5@"$$ 6 $6@"$$ 6 $7@"$$ 6 $8@"$$ 6 $9@"$$ 6 $"$$ 6 $"$$ 6 $"$$  $ $)^Start of Next OpA $ -2b;A $@@ -2b;A $R@ -2b;A $ @ -2b;A $P@ -2b;A $T@ -2b;A $8@ -2b;A $R@ -2b;A $X@ -2b;l $'- 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: $ $^Start$$0  $$0@  $$P@  $$  $$@@  $$R@  $$ @  $$P@  $$T@   $ $^Stop<$ 0(;<$@@0(;<$R@0(;<$  @0(;<$P@0(;<$T@0(;<$ 8@0(;<$R@0(;<$X@0(;$^Push 2$^Stop of Prev. OpC$ /2b;C$ /2b;C$ /2b;C$0@ /2b;C$@@ /2b;C$R@ /2b;C$8@ /2b;C$P@ /2b;C$T@ /2b;$'earliest (push 1) start$'latest (pull) start&$ 'stop of the previous operationD$!'stop of the previous job on the work centre, if there is one $ $^StartL$ 8;TL$0@ 8;TL$P@ 8;TL$0@ 8;TL$@@ 8;TL$R@ 8;TL$8@ 8;TL$P@ 8;TL$T@ 8;T $ $^Stop$$ 0@  $$@@  $$R@  $$ 8@  $$P@  $$T@  $$ D@  $$R@  $$X@   $<$nÅ (###;3 $<$Å (###;3 $<$vą (###;3 $<$Ņ (###;3 $<$Ƅ (###;3 $<$Ƅ (###;3 $<$DŽ (###;3 $<$DŽ (###;3 $<$&Ȅ (###;3 $ $^Day 1 $<$]F^ (###;3$Y####<$eÅ (###;3 $a<$eą (###;3 $a<$]Ņ (###;3 $Y<$e"Ƅ (###;3 $a<$eƄ (###;3 $a<$]DŽ (###;3 $Y<$eDŽ (###;3 $a<$}2Ȅ (###;3 $y $ $0@  <$V^ (###;3$####<$ ą (###;3 $<$ą (###;3 $<$ń (###;3 $<$.Ƅ (###;3 $<$Ƅ (###;3 $<$*DŽ (###;3 $<$DŽ (###;3 $<$>Ȅ (###;3 $ $ $8@  <$5zÅ (###;3 $1<$M (###;3$I####<$Mą (###;3 $I<$5 (###;3$1####<$M:Ƅ (###;3 $I<$MƄ (###;3 $I<$56DŽ (###;3 $1<$MDŽ (###;3 $I<$UJȄ (###;3 $Q $^Day 2 $@@  <$]Å (###;3 $Y<$e (###;3$a####<$eą (###;3 $a<$]ń (###;3 $Y<$eFƄ (###;3 $a<$eƄ (###;3 $a<$] (###;3$Y####<$eDŽ (###;3 $a<$}VȄ (###;3 $y $ $D@  <$Å (###;3 $<$ą (###;3 $<$ą (###;3 $<$ń (###;3 $<$҉ (###;3$####<$Ƅ (###;3 $<$ (###;3$####<$DŽ (###;3 $<$bȄ (###;3 $ $ $H@  <$5Å (###;3 $1<$M"ą (###;3 $I<$Mą (###;3 $I<$5ń (###;3 $1<$M≄ (###;3$I####<$MƄ (###;3 $I<$5BDŽ (###;3 $1<$MDŽ (###;3 $I<$UnȄ (###;3 $Q $^Day 3 $L@  <$]Å (###;3 $Y<$e.ą (###;3 $a<$eą (###;3 $a<$]ń (###;3 $Y<$e (###;3$a####<$eƄ (###;3 $a<$]NDŽ (###;3 $Y<$eDŽ (###;3 $a<$}zȄ (###;3 $y $ $P@  <$Å (###;3 $<$:ą (###;3 $<$ą (###;3 $<$ń (###;3 $<$ (###;3$####<$Ƅ (###;3 $<$ZDŽ (###;3 $<$DŽ (###;3 $<$Ȅ (###;3 $ $ $R@  <$5Å (###;3 $1<$MFą (###;3 $I<$MR (###;3$I####<$5ń (###;3 $1<$MRƄ (###;3 $I<$MƄ (###;3 $I<$5fDŽ (###;3 $1<$MҊ (###;3$I####<$UȄ (###;3 $Q $^Day 4 $T@  <$]Å (###;3 $Y<$eRą (###;3 $a<$eą (###;3 $a<$]ń (###;3 $Y<$e^Ƅ (###;3 $a<$eB (###;3$a####<$]rDŽ (###;3 $Y<$eȄ (###;3 $a<$}Ȅ (###;3 $y $ $V@  <$Å (###;3 $<$^ą (###;3 $<$ą (###;3 $<$ń (###;3 $<$jƄ (###;3 $<$Ƅ (###;3 $<$~DŽ (###;3 $<$Ȅ (###;3 $<$ (###;3$#### $ $X@  <$5Å (###;3 $1<$Mją (###;3 $I<$Mą (###;3 $I<$5 Ƅ (###;3 $1<$MvƄ (###;3 $I<$MDŽ (###;3 $I<$5DŽ (###;3 $1<$MȄ (###;3 $I<$U" (###;3$Q####$% Section 34$u<7~7,,,:::: :   :  :::::ރ !"#$% [!B W b@Arrow76 X {GX ? "!B  r y@Arrow77 X {GX ? "!B # @Arrow78 X {GX ? "\  #   #" !"#$%M%'Repetitive Production Through Multiple Work Centres - 3 Pass Schedule%'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 next operation can start  %^W/C %^6 %^6 %^6 %^7 %^7 %^7 %^8 %^8 %^8/%'- the start of the schedule (hour zero)(%'- the length of the job in hours%'- when the job is due9%'- the stop of the previous job on the work centre6% '- the start of the next job on the work centre% Tsg- the H hours; that the next operation lags behind the previous operation3R% - the 1 hour that the next operation lags behind the previous operationn8% '- the start of the previous operation on the job7% '- the stop of the previous operation on the job9% '- the start of the next next operation on the job8%'- the stop of the next next operation on the job%^Prod-uct%^prod A%^prod B%^prod C%^prod A%^prod B%^prod C%^prod A%^prod B%^prod C %^Op % % % % % % % % %%^Prod/Op6%"i/H3%A/116%⋄"i/H3%B/116%""i/H3%C/116%b"i/H3%A/226%"i/H3%B/226%⌄"i/H3%C/226%"i/H3%A/336%B"i/H3%B/336%r"i/H3%C/33 %^Qty % % %l % % %l % % %l % %^Due % % % % % % % %H %`;%'@IF(@ISERR(J28),-F$17,@INDEX(X$1..X$31,0,J28)+F$17)`%LFhours; after the start of the previous operation (Lag)3>%hour after the start of the previous operation (Lag))%^Units per Hour%V@%0a@%m@%pg@%------P@ % %2 %%98cZ@ %^Hours$%1@ $%0@ $% @ $% @ $%A@ $%$@ $%>@ $% @ $%2@ ,%'@MAX(M28,S28,U28,@IF(A28=A27,X27,0))%!^Push 1%^Prev Op:%‹&i/ H3%A/00:%&i/ H3%B/00:%B&i/ H3%C/00:%&i/ H3%A/11:%Œ&i/ H3%B/11:%&i/ H3%C/11:%"&i/ H3%A/22:%R&i/ H3%B/22:%%&i/ H3%!C/22  %( %^Prev Op Offset6 %"%%6 %"%%6 %"%%6 %3@"%%6 %4@"%%6 %5@"%%6 %6@"%%6 %7@"%%6 %8@"%%  %( %^Start of Prev OpJ % 62b ;  J % 62b ;  J % 62b ;  J %? 62b ;  J %2@ 62b ;  J %A@ 62b ;  J %@ 62b ;  J %3@ 62b ;  J %J@ 62b ;   %#'@MAX(W28+H28,V28)  %( %^Stop of Prev OpJ % 62b ;  J % 62b ;  J % 62b ;  J %2@ 62b ;  J %A@ 62b ;  J %E@ 62b ;  J %3@ 62b ;  J %J@ 62b ;  J %O@ 62b ;    %( %^Start> % *;T> %1@ *;T> %@@ *;T> %? *;T> %2@ *;T> %J@ *;T> %@ *;T> %@@ *;T> %J@ *;T  %0 %^Stop- %1@  T- %@@  T- %D@  T- %2@  T- %J@  T- %O@  T- %@@  T- %J@  T- %Q@  T %!^Pull%^Next Op:%ҋ&i/ H3%A/22:%&i/ H3%B/22:%R&i/ H3%C/22:%&i/ H3%A/33:%Ҍ&i/ H3%B/33:%&i/ H3%C/33:%2&i/ H3%A/44:%b&i/ H3%B/44:%%&i/ H3%!C/44 %(%^Next Op Row6%6@"%%6%7@"%%6%8@"%%6%9@"%%6%:@"%%6%;@"%%6%"%%6%"%%6%"%%%'- 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:%#- the stop is the later of, the start plus the hours, and the stop of the previous operation (plus H hours));3t%#- the stop is the later of, the start plus the hours, and the stop of the previous operation (plus 1 hour)) %(%^Start of Next OpI% 52b ;I%B@ 52b ;I%S@ 52b ;I% 52b ;I%O@ 52b ;I%@S@ 52b ;I%8@ 52b ;I%R@ 52b ;I%X@ 52b ;%'earliest (push 1) start% 'latest (pull) starto%![Othe start of the previous operation (plus H hours));3<%!the start of the previous operation (plus 1 hour))D%"'stop of the previous job on the work centre, if there is one %(%^Stop of Next OpI%6@ 52b ;I%Q@ 52b ;I%W@ 52b ;I%7@ 52b ;I%Q@ 52b ;I%W@ 52b ;I%8@ 52b ;I%R@ 52b ;I%X@ 52b ; %( %^Start-%  S-%B@  S-%S@  S-%  S-%B@  S-%@S@  S-%  S-%P@  S-%S@  S %0 %^Stop<%6@0(;<%Q@0(;<%W@0(;<%7@0(;<%Q@0(;<%W@0(;<%8@0(;<%R@0(;<%X@0(;%!^Push 2%^Start of Prev OpJ% 62b ;J% 62b ;J% 62b ;J%? 62b ;J%B@ 62b ;J%@S@ 62b ;J%@ 62b ;J%C@ 62b ;J%%S@ 62b ; %(%^Stop of Prev OpJ% 62b ;J% 62b ;J% 62b ;J%2@ 62b ;J%J@ 62b ;J%@U@ 62b ;J%3@ 62b ;J%R@ 62b ;J%V@ 62b ; %( %^StartL% 8;TL%B@ 8;TL%S@ 8;TL%? 8;TL%B@ 8;TL%@S@ 8;TL%@ 8;TL%P@ 8;TL%S@ 8;T %( %^Stop-%1@  T-%J@  T-%U@  T-%2@  T-%Q@  T-%U@  T-%@@  T-%R@  T-%X@  T %0 %^Effective Units per Hour-%=V@   -%E0a@   -%Em@   -%=V@   -%E------P@   -%Eg@   -%=I@   -%E0q@   -%98cZ@    % %!^Day 1 %%@ /   ;;; ;%]Ʉ /   ;;; ;3 %Y%]Ʉ /   ;;; ;3 %Y%-----M@ /   ;;; ;%]v /   ;;; ;3 %Y%]ʀ /   ;;; ;3 %Y%r@ /   ;;; ;%] /   ;;; ;3 %Y%e& /   ;;; ;3 %a % %( % @  %@ /   ;;; ;%M"Ʉ /   ;;; ;3 %I%MɄ /   ;;; ;3 %I%@ /   ;;; ;%M /   ;;; ;3 %I%Mր /   ;;; ;3 %I%y@ /   ;;; ;%M /   ;;; ;3 %I%m2 /   ;;; ;3 %i % %0 %0@  %V@ /   ;;; ;%U.Ʉ /   ;;; ;3 %Q%UɄ /   ;;; ;3 %Q%f@ /   ;;; ;%U /   ;;; ;3 %Q%U  /   ;;; ;3 %Q%y@ /   ;;; ;%U /   ;;; ;3 %Q%> /   ;;; ;3 % % %!^Day 2 %8@  %Ȅ /   ;;; ;3 %%]:Ʉ /   ;;; ;3 %Y%]Ʉ /   ;;; ;3 %Y%Ʉ /   ;;; ;3 %%] /   ;;; ;3 %Y%] /   ;;; ;3 %Y%y@ /   ;;; ;%]Ɓ /   ;;; ;3 %Y%eJ /   ;;; ;3 %a % %( %@@  %Ȅ /   ;;; ;3 %%]0@ /   ;;; ;%]Ʉ /   ;;; ;3 %Y%ʄ /   ;;; ;3 %%]Ch@ /   ;;; ;%] /   ;;; ;3 %Y%B /   ;;; ;3 %%]ҁ /   ;;; ;3 %Y%mV /   ;;; ;3 %i % %0 %D@  %Ȅ /   ;;; ;3 %%e0@ /   ;;; ;%eɄ /   ;;; ;3 %a%ʄ /   ;;; ;3 %%e------@ /   ;;; ;%e /   ;;; ;3 %a%N /   ;;; ;3 %%eށ /   ;;; ;3 %a%b /   ;;; ;3 % % %!^Day 3 %H@  %Ȅ /   ;;; ;3 %%]0@ /   ;;; ;%]Ʉ /   ;;; ;3 %Y%ʄ /   ;;; ;3 %%]------@ /   ;;; ;%] /   ;;; ;3 %Y%Z /   ;;; ;3 %%]ꁂ /   ;;; ;3 %Y%en /   ;;; ;3 %a  %  %( %L@   %Ȅ /   ;;; ;3 % %mFɄ /   ;;; ;3 %i %mɄ /   ;;; ;3 %i %*ʄ /   ;;; ;3 % %m------@ /   ;;; ; %m /   ;;; ;3 %i %f /   ;;; ;3 % %m /   ;;; ;3 %i %mz /   ;;; ;3 %i !% !%0 !%P@  !%Ȅ /   ;;; ;3 !%!%uRɄ /   ;;; ;3 !%q!%uɄ /   ;;; ;3 !%q!%6ʄ /   ;;; ;3 !%!%uO|@ /   ;;; ;!%u* /   ;;; ;3 !%q!%r /   ;;; ;3 !%!%u0@ /   ;;; ;!% /   ;;; ;3 !% "% "%!^Day 4 "%R@  "%Ȅ /   ;;; ;3 "%"%]^Ʉ /   ;;; ;3 "%Y"%]@ /   ;;; ;"%Bʄ /   ;;; ;3 "%"%] /   ;;; ;3 "%Y"%]Ё@ /   ;;; ;"%~ /   ;;; ;3 "%"%] /   ;;; ;3 "%Y"%e98cj@ /   ;;; ; #% #%( #%T@  #%Ȅ /   ;;; ;3 #%#%}jɄ /   ;;; ;3 #%y#%}@ /   ;;; ;#%^ /   ;;; ;3 #%#%} /   ;;; ;3 #%y#%}Ȕ@ /   ;;; ;#% /   ;;; ;3 #%#%} /   ;;; ;3 #%y#%m98c@ /   ;;; ; $% $%0 $%V@  $% Ʉ /   ;;; ;3 $%$%vɄ /   ;;; ;3 $%$%Ʉ /   ;;; ;3 $%$%j /   ;;; ;3 $%$% /   ;;; ;3 $%$%6 /   ;;; ;3 $%$% /   ;;; ;3 $%$% /   ;;; ;3 $%$%98c@ /   ;;; ; %% %%X@  %& Section 35$u<7~ 7,  :     uރ    u ރ [!B 9dCU@Arrow79 X {GX ? "\H        '&'3 Pass Schedule With a Calendaro&'All the calculations in the 3 pass schedules in Sections 33 and 34 have been done on the basis of hourst&'into the schedule. If all work centres work 24 hours a day, 7 days a week, then it would be straightforward u&'to convert the start and stop times to Julian dates. However, if the work centres follow a calendar, then we p&'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. & ^W/C & ^6 & ^6 & ^6 &^7 &^7 &^7 &^8 &^8 &^8&'(D20-C20)/24& ^Prod -uct& ^prod A& ^prod B& ^prod C&^prod A&^prod B&^prod C&^prod A&^prod B&^prod C&'Start of first job:& ^Start hour&  & B@ & S@ &? &B@ &@S@ &@ &P@ &S@ & ^Stop hour& 1@ & J@ & U@ &2@ &Q@ &U@ &@@ &R@ &X@ X&'- the duration of the operation is re-established from the stop and start hours,K&' as it is not always the same as the hours (Section 30, column H) & ^Days)& ?    )& UUUUUU?    )& UUUUUU?    )&?    )&?    )&?    )&?    )&UUUUUU?    )&?    &@& ^Start DateH& UUUU@ 4   ;H&  4   ;H&  4   ;H&@ 4   ;H& 4   ;H& 4   ;H&@ 4   ;H& 4   ;H&  4   ;&? & & UUUU@ &  &  &@ & & &@ & &  & ^Calc1`& 0L ccc`& 0L ccc`& 0L ccc`&0L ccc`&0L ccc`&0L ccc`&0L ccc`&0L ccc`&0L ccc & ^Calc2u&  a+bbbT  cccu&  a+bbbT  cccu&  a+bbbT  cccu& a+bbbT  cccu& a+bbbT  cccu& a+bbbT  cccu& a+bbbT  cccu& a+bbbT  cccu& a+bbbT  ccc & ^Calc39 & 0%  c9 & 0%  c9 & 0%  c9 &0%  c9 &0%  c9 &0%  c9 &0%  c9 &0%  c9 &0%  c & ^Stop^ &  Jbb   cc^ &  Jbb   cc^ &  J      !"#$%&'()*+,-./0123456789:;<=>?@ABCDEFGHIJKLMNOPQRSTUVWXYZ[\]^_`abcdefghijklmnopqrstuvwxyz{|~bb   cc^ & Jbb   cc^ & Jbb   cc^ & Jbb   cc^ & Jbb   cc^ & Jbb   cc^ &% Jbb   cc  &  &   &   &   &  &  &  &  &  &- &' Section 36$u<7~ 7,,  , pu::                   ! "#$% &                                  ! " # $ % & /''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 ' '  ' )^W/C '^6 '^6 '^6 '^7 '^7 '^7 '^8 '^8 '^8'''^Calendar'^Calendar 2' ^Calendar 1' ^Calendar 2' )^Calendar+'!}U 3'Calendar 22+'4U 3'Calendar 22+'JU 3'Calendar 22+'`U 3'Calendar 11+'vU 3'Calendar 11+'U 3'Calendar 11+'U 3'Calendar 22+'U 3'Calendar 22+'·U 3'Calendar 22 ' )^Jobs '^job A '^job B '^job C '^job A '^job B '^job C '^job A '^job B '^job C ' )^Op ' ' ' ' ' ' ' ' '' )^Job/Op6'"i/H3'A/116'⎄"i/H3'B/116'"i/H3'C/116'́"i/H3'A/226'6́"i/H3'B/226'f́"i/H3'C/226'́"i/H3'A/336'́"i/H3'B/336'΁"i/H3'C/33 ' )^Hours ' ' ' ' ' ' ' ' ' ' )^Days '?   '?   'UUUUUU?   'UUUUUU?   '?   'UUUUUU?   'UUUUUU?   'UUUUUU?   'UUUUUU?   ' )^Due '@ ' ' '@ ' ''C@'@'@' 'Start of first job:' 9^Push 1' )^Prev. Op:'Ž&i/ H3'A/00:'&i/ H3'B/00:'́&i/ H3'C/00:'́&i/ H3'A/11:'F́&i/ H3'B/11:'v́&i/ H3'C/11:'́&i/ H3'A/22:'́&i/ H3'B/22:'6΁&i/ H3'C/22  ' @ ' )^Prev. Op Offset6 '"''6 '"''6 '"''6 ',@"''6 '.@"''6 '0@"''6 '1@"''6 '2@"''6 '3@"''  ' @ ' ^Stop of Prev. OpC 'M /2b;C ' /2b;C ' /2b;C 'M@ /2b;C '*@ /2b;C '6@ /2b;C 'M @ /2b;C 'V@ /2b;C '[UUU@ /2b;  ' @  '  'U  '  '  'U@  '*@  '6@  'U @  'V@  '[UUU@  ' 3@  ' @ ' ^StartD ']@ 0  ;T  D '@ 0  ;T  D '*@ 0  ;T  D ']@ 0  ;T  D '*@ 0  ;T  D 'V@ 0  ;T  D '] @ 0  ;T  D 'V@ 0  ;T  D '@ 0  ;T   ' ;?  ' @  '  'e@  '@  '*@  'e@  '*@  'V@  'e @  'V@  '@  ' @ ' )^Calc1'@0[:G$6..:G$100s:G$6..:G$100s:H$6..:H$100s '@0[:G$6..:G$100s:G$6..:G$100s:H$6..:H$100s '(@0[:G$6..:G$100s:G$6..:G$100s:H$6..:H$100s '@0[:G$6..:G$100s:G$6..:G$100s:H$6..:H$100s '.@0[:G$6..:G$100s:G$6..:G$100s:H$6..:H$100s '7@0[:G$6..:G$100s:G$6..:G$100s:H$6..:H$100s '"@0[:G$6..:G$100s:G$6..:G$100s:H$6..:H$100s '4@0[:G$6..:G$100s:G$6..:G$100s:H$6..:H$100s '7@0[:G$6..:G$100s:G$6..:G$100s:H$6..:H$100s  ' @ ' )^Calc2'm?0j:I$6..:I$100sb:H$6..:H$100sb:G$6..:G$100sbT  ' ?0j:I$6..:I$100sb:H$6..:H$100sb:G$6..:G$100sbT  ' ?0j:I$6..:I$100sb:H$6..:H$100sb:G$6..:G$100sbT  'm?0j:I$6..:I$100sb:H$6..:H$100sb:G$6..:G$100sbT  ' ?0j:I$6..:I$100sb:H$6..:H$100sb:G$6..:G$100sbT  ' @0j:I$6..:I$100sb:H$6..:H$100sb:G$6..:G$100sbT  'm?0j:I$6..:I$100sb:H$6..:H$100sb:G$6..:G$100sbT  ' SUUU?0j:I$6..:I$100sb:H$6..:H$100sb:G$6..:G$100sbT  '@TUUU@0j:I$6..:I$100sb:H$6..:H$100sb:G$6..:G$100sbT   ' @ ' )^Calc3P'@0<$ :I$6..:I$100s P'(@0<$ :I$6..:I$100s P'.@0<$ :I$6..:I$100s P'&@0<$ :I$6..:I$100s P'7@0<$ :I$6..:I$100s P';@0<$ :I$6..:I$100s P'0@0<$ :I$6..:I$100s P'7@0<$ :I$6..:I$100s P'>@0<$ :I$6..:I$100s  ' @ ' ^Stop'u@0xH:H$6..:H$100sb:I$6..:I$100sb   '*@0xH:H$6..:H$100sb:I$6..:I$100sb   '6@0xH:H$6..:H$100sb:I$6..:I$100sb   'u @0xH:H$6..:H$100sb:I$6..:I$100sb   'V@0xH:H$6..:H$100sb:I$6..:I$100sb   '[UUU@0xH:H$6..:H$100sb:I$6..:I$100sb   'uJ@0xH:H$6..:H$100sb:I$6..:I$100sb   '@0xH:H$6..:H$100sb:I$6..:I$100sb   '@0xH:H$6..:H$100sb:I$6..:I$100sb    ' H ' '}@ '*@ '6@ '} @ 'V@ '[UUU@ '}J@ '@ '@  ' 9^Pull' )^Next Op:'Ҏ&i/ H3'A/22:'&i/ H3'B/22:'́&i/ H3'C/22:'&́&i/ H3'A/33:'V́&i/ H3'B/33:'́&i/ H3'C/33:'́&i/ H3'A/44:'΁&i/ H3'B/44:'F΁&i/ H3'C/44 ' @' )^Next Op Offset6'1@"''6'2@"''6'3@"''6'4@"''6'5@"''6'6@"''6'"''6'"''6'"'' ' @' ^Start of Next OpA'@ -2b;A'%@ -2b;A'%N@ -2b;A'RUUU@ -2b;A'%SUUUM@ -2b;A'%TUUU@ -2b;A'@ -2b;A'%@ -2b;A'@ -2b; ' @ ' '@ '-@ '-N@ 'RUUU@ '-SUUUM@ '-TUUU@ '@ '-@ '@  ' @ ' ^StopB'@0.i@;B'5@0.i@;B'5N@0.i@;B'RUUU@0.i@;B'5SUUUM@0.i@;B'5TUUU@0.i@;B'SUUUM@0.i@;B'5TUUU@0.i@;B'@0.i@; ' @ ' '@ '=@ '=N@ 'RUUU@ '=SUUUM@ '=TUUU@ 'SUUUM@ '=TUUU@ '@  ' @ ' )^Calc4'@0[:G$6..:G$100s:G$6..:G$100s:H$6..:H$100s '$@0[:G$6..:G$100s:G$6..:G$100s:H$6..:H$100s '1@0[:G$6..:G$100s:G$6..:G$100s:H$6..:H$100s '&@0[:G$6..:G$100s:G$6..:G$100s:H$6..:H$100s '4@0[:G$6..:G$100s:G$6..:G$100s:H$6..:H$100s '8@0[:G$6..:G$100s:G$6..:G$100s:H$6..:H$100s '0@0[:G$6..:G$100s:G$6..:G$100s:H$6..:H$100s '4@0[:G$6..:G$100s:G$6..:G$100s:H$6..:H$100s '<@0[:G$6..:G$100s:G$6..:G$100s:H$6..:H$100s  ' @ ' )^Calc5'?0j:I$6..:I$100sb:H$6..:H$100sb:G$6..:G$100sbT  '?0j:I$6..:I$100sb:H$6..:H$100sb:G$6..:G$100sbT  '?0j:I$6..:I$100sb:H$6..:H$100sb:G$6..:G$100sbT  '?0j:I$6..:I$100sb:H$6..:H$100sb:G$6..:G$100sbT  '@0j:I$6..:I$100sb:H$6..:H$100sb:G$6..:G$100sbT  '@0j:I$6..:I$100sb:H$6..:H$100sb:G$6..:G$100sbT  'RUUU?0j:I$6..:I$100sb:H$6..:H$100sb:G$6..:G$100sbT  '?0j:I$6..:I$100sb:H$6..:H$100sb:G$6..:G$100sbT  '@0j:I$6..:I$100sb:H$6..:H$100sb:G$6..:G$100sbT   ' @ ' )^Calc6P'@0<$ :I$6..:I$100s P'@0<$ :I$6..:I$100s P'*@0<$ :I$6..:I$100s P' @0<$ :I$6..:I$100s P'(@0<$ :I$6..:I$100s P'5@0<$ :I$6..:I$100s P'"@0<$ :I$6..:I$100s P'0@0<$ :I$6..:I$100s P'4@0<$ :I$6..:I$100s  ' @ ' ^Start'@0xH:H$6..:H$100sb:I$6..:I$100sb   'E@0xH:H$6..:H$100sb:I$6..:I$100sb   'ERUUU0@0xH:H$6..:H$100sb:I$6..:I$100sb   '@0xH:H$6..:H$100sb:I$6..:I$100sb   'E@0xH:H$6..:H$100sb:I$6..:I$100sb   'EN@0xH:H$6..:H$100sb:I$6..:I$100sb   'RUUU@0xH:H$6..:H$100sb:I$6..:I$100sb   'ESUUUM@0xH:H$6..:H$100sb:I$6..:I$100sb   'TUUU@0xH:H$6..:H$100sb:I$6..:I$100sb    ' H ' '@ 'M@ 'MRUUU0@ '@ 'M@ 'MN@ 'RUUU@ 'MSUUUM@ 'TUUU@ ' 9^Push 2' ^Stop of Prev. OpC' /2b;C'U /2b;C'U /2b;C'@ /2b;C'U*@ /2b;C'UN@ /2b;C'RUUU@ /2b;C'UV@ /2b;C'[UUU@ /2b; ' @ ' ' '] '] '@ ']*@ ']N@ 'RUUU@ ']V@ '[UUU@   ' @ ' ^StartL '@ 8;TL 'e@ 8;TL 'eRUUU0@ 8;TL '@ 8;TL 'e*@ 8;TL 'eV@ 8;TL 'RUUU@ 8;TL 'eV@ 8;TL '@ 8;T !' @ !' !'@ !'m@ !'mRUUU0@ !'@ !'m*@ !'mV@ !'RUUU@ !'mV@ !' @  "' @ "' )^Calc7"'@0[:G$6..:G$100s:G$6..:G$100s:H$6..:H$100s "'@0[:G$6..:G$100s:G$6..:G$100s:H$6..:H$100s "'*@0[:G$6..:G$100s:G$6..:G$100s:H$6..:H$100s "' @0[:G$6..:G$100s:G$6..:G$100s:H$6..:H$100s "'.@0[:G$6..:G$100s:G$6..:G$100s:H$6..:H$100s "'7@0[:G$6..:G$100s:G$6..:G$100s:H$6..:H$100s "'"@0[:G$6..:G$100s:G$6..:G$100s:H$6..:H$100s "'4@0[:G$6..:G$100s:G$6..:G$100s:H$6..:H$100s "'7@0[:G$6..:G$100s:G$6..:G$100s:H$6..:H$100s  #' @ #' )^Calc8#'?0j:I$6..:I$100sb:H$6..:H$100sb:G$6..:G$100sbT  #'?0j:I$6..:I$100sb:H$6..:H$100sb:G$6..:G$100sbT  #'?0j:I$6..:I$100sb:H$6..:H$100sb:G$6..:G$100sbT  #'?0j:I$6..:I$100sb:H$6..:H$100sb:G$6..:G$100sbT  #'?0j:I$6..:I$100sb:H$6..:H$100sb:G$6..:G$100sbT  #'@0j:I$6..:I$100sb:H$6..:H$100sb:G$6..:G$100sbT  #'OUUU?0j:I$6..:I$100sb:H$6..:H$100sb:G$6..:G$100sbT  #'SUUU?0j:I$6..:I$100sb:H$6..:H$100sb:G$6..:G$100sbT  #'@TUUU@0j:I$6..:I$100sb:H$6..:H$100sb:G$6..:G$100sbT   $' @ $' )^Calc9P$'@0<$ :I$6..:I$100s P$'(@0<$ :I$6..:I$100s P$'1@0<$ :I$6..:I$100s P$'&@0<$ :I$6..:I$100s P$'7@0<$ :I$6..:I$100s P$';@0<$ :I$6..:I$100s P$'0@0<$ :I$6..:I$100s P$'7@0<$ :I$6..:I$100s P$'>@0<$ :I$6..:I$100s  %' @ %' ^Stop%'@0xH:H$6..:H$100sb:I$6..:I$100sb   %'u*@0xH:H$6..:H$100sb:I$6..:I$100sb   %'uN@0xH:H$6..:H$100sb:I$6..:I$100sb   %'RUUU@0xH:H$6..:H$100sb:I$6..:I$100sb   %'uV@0xH:H$6..:H$100sb:I$6..:I$100sb   %'u[UUU@0xH:H$6..:H$100sb:I$6..:I$100sb   %'SUUUM@0xH:H$6..:H$100sb:I$6..:I$100sb   %'u@0xH:H$6..:H$100sb:I$6..:I$100sb   %'@0xH:H$6..:H$100sb:I$6..:I$100sb    &' H &' &'@ &'}*@ &'}N@ &'RUUU@ &'}V@ &'}[UUU@ &'SUUUM@ &'}@ &'@ '( Section 37$u<7~7, , , :     :    u          :! " # $ % &'( ) * + , - . / 0 1 2 3 4 5 67 8 9 : ; <u= > ?@ A B C D E FG          !"#$%&'()*+,-./0123456789:;<=>?@ABCDEFGF('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 72 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,P(' in Section 31 to a pull schedule, and here we have put it all together. ( ^W/C ( ^6 ( ^7 ( ^8%('The next operation can start  ()^W/C (^6 (^6 (^6 (^7 (^7 (^7 (^8 (^8 (^8('( ^Calendar( ^Calendar 2( ^Calendar 1( ^Calendar 2()^Calendar+(숁U 3(Calendar 22+(U 3(Calendar 22+(U 3(Calendar 22+( `U 3(Calendar 11+(.U 3(Calendar 11+(DU 3(Calendar 11+(U 3(Calendar 22+(ZU 3(Calendar 22+(pU 3(Calendar 22()^Prod-uct(^prod A(^prod B(^prod C(^prod A(^prod B(^prod C(^prod A(^prod B(^prod C ()^Op ( ( ( ( ( ( ( ( ( (()^Prod/Op6(ρ"i/H3(A/116(6ρ"i/H3(B/116(fρ"i/H3(C/116(ρ"i/H3(A/226(ρ"i/H3(B/226(ρ"i/H3(C/226(&Ё"i/H3(A/336(vЁ"i/H3(B/336(Ё"i/H3(C/33`(րLFhours; after the start of the previous operation (Lag)3>(hour after the start of the previous operation (Lag)) ()^Qty ( ( (l ( ( (l ( ( (l()^Units per Hour (X ( ( ( (A ( (2 ( (j()^Dur-ation Hours$(%. 1@ $(~FQ/@ $(87@ $(%lw6@ $(NN@@ $($@ $(%>@ $(K @ $(]1@  ()^Days (%袋.?   (ٰA?   (%I%I?   (%F?   (ii?   (?   (%?   (Hx?   (]_.=?   (^Due  ((  (  (  ((  (  ( (+@ (@ (c@  (  (0  (  (  (0  (  ( (3VUUU? (? (kVUUU? (9^Push 1 ()^Prev Op: (ρ&i/ H3 (A/00: (Fρ&i/ H3 (B/00: (vρ&i/ H3 (C/00: (ρ&i/ H3 (A/11: (ρ&i/ H3 (B/11: (Ё&i/ H3 (C/11: (VЁ&i/ H3 (A/22: (Ё&i/ H3 (B/22: (Ё&i/ H3 (C/22  (@ ()^Prev Op Offset6 ("((6 ("((6 ("((6 (2@"((6 (3@"((6 (4@"((6 (5@"((6 (6@"((6 (7@"((  (@ (^Start of Prev Op +Lag= (=? )2b;  = (? )2b;  = ( ? )2b;  = (=@ )2b;  = (.*@ )2b;  = (QU@ )2b;  = (=@ )2b;  = (,@ )2b;  = (uU0@ )2b;  (@ ((E? (? (? (E@ (.*@ (QU@ (E@ (,@ (}U0@  (@(^Stop of Prev Op=(M? )2b;   =(? )2b;   =(? )2b;   =(M.@ )2b;   =(dS@ )2b;   =(]@ )2b;   =(MO6d@ )2b;   =(ɘ@ )2b;   =(ɘ@ )2b;   (@ ((U? (? (%? (U.@ (dS@ (]@ (UO6d@ (ɘ@ (ɘ@  (@ ()^Calc1(0[:G$6..:G$100s:G$6..:G$100s:H$6..:H$100s (0[:G$6..:G$100s:G$6..:G$100s:H$6..:H$100s (0[:G$6..:G$100s:G$6..:G$100s:H$6..:H$100s (*@0[:G$6..:G$100s:G$6..:G$100s:H$6..:H$100s (6@0[:G$6..:G$100s:G$6..:G$100s:H$6..:H$100s (:@0[:G$6..:G$100s:G$6..:G$100s:H$6..:H$100s ((@0[:G$6..:G$100s:G$6..:G$100s:H$6..:H$100s (8@0[:G$6..:G$100s:G$6..:G$100s:H$6..:H$100s (<@0[:G$6..:G$100s:G$6..:G$100s:H$6..:H$100s ('Start of first job: (@ ()^Calc2(]0j:I$6..:I$100sb:H$6..:H$100sb:G$6..:G$100sbT  (0j:I$6..:I$100sb:H$6..:H$100sb:G$6..:G$100sbT  (-0j:I$6..:I$100sb:H$6..:H$100sb:G$6..:G$100sbT  (]E]?0j:I$6..:I$100sb:H$6..:H$100sb:G$6..:G$100sbT  (Ԓ@0j:I$6..:I$100sb:H$6..:H$100sb:G$6..:G$100sbT  (@l@0j:I$6..:I$100sb:H$6..:H$100sb:G$6..:G$100sbT  (]?0j:I$6..:I$100sb:H$6..:H$100sb:G$6..:G$100sbT  (0&@0j:I$6..:I$100sb:H$6..:H$100sb:G$6..:G$100sbT  (@0j:I$6..:I$100sb:H$6..:H$100sb:G$6..:G$100sbT   (@ ()^Calc3U(W@0A)  :I$6..:I$100s (U(W@0A)  :I$6..:I$100s (U(W@0A)  :I$6..:I$100s (U(,@0A)  :I$6..:I$100s (U(7@0A)  :I$6..:I$100s (U(;@0A)  :I$6..:I$100s (U((@0A)  :I$6..:I$100s (U(8@0A)  :I$6..:I$100s (U(<@0A)  :I$6..:I$100s ( (@(^Stop of Prev Op +Lag(e?0S2:H$6..:H$100sb:I$6..:I$100sb    ;((?0S2:H$6..:H$100sb:I$6..:I$100sb    ;((5?0S2:H$6..:H$100sb:I$6..:I$100sb    ;((eO6d@0S2:H$6..:H$100sb:I$6..:I$100sb    ;((QU@0S2:H$6..:H$100sb:I$6..:I$100sb    ;((SK@0S2:H$6..:H$100sb:I$6..:I$100sb    ;((e,@0S2:H$6..:H$100sb:I$6..:I$100sb    ;((U0@0S2:H$6..:H$100sb:I$6..:I$100sb    ;((@0S2:H$6..:H$100sb:I$6..:I$100sb    ;( (@ ((m? (? (=? (mO6d@ (QU@ (SK@ (m,@ (U0@ (@ (@ (@ (^StartD(u@ 0  ;TD(.@ 0  ;TD(EdS@ 0  ;TD(u@ 0  ;TD(.*@ 0  ;TD(ɘ@ 0  ;TD(u@ 0  ;TD(YUUUR@ 0  ;TD(U0@ 0  ;T(? (@ ((}@ (.@ (MdS@ (}@ (.*@ (ɘ@ (}@ (YUUUR@ (U0@  (@ ()^Calc4(@0[:G$6..:G$100s:G$6..:G$100s:H$6..:H$100s (&@0[:G$6..:G$100s:G$6..:G$100s:H$6..:H$100s (2@0[:G$6..:G$100s:G$6..:G$100s:H$6..:H$100s (@0[:G$6..:G$100s:G$6..:G$100s:H$6..:H$100s (.@0[:G$6..:G$100s:G$6..:G$100s:H$6..:H$100s (=@0[:G$6..:G$100s:G$6..:G$100s:H$6..:H$100s (@0[:G$6..:G$100s:G$6..:G$100s:H$6..:H$100s (2@0[:G$6..:G$100s:G$6..:G$100s:H$6..:H$100s (8@0[:G$6..:G$100s:G$6..:G$100s:H$6..:H$100s  (@ ()^Calc5(?0j:I$6..:I$100sb:H$6..:H$100sb:G$6..:G$100sbT  (E]?0j:I$6..:I$100sb:H$6..:H$100sb:G$6..:G$100sbT  (U%?0j:I$6..:I$100sb:H$6..:H$100sb:G$6..:G$100sbT  (TUUU?0j:I$6..:I$100sb:H$6..:H$100sb:G$6..:G$100sbT  ({?0j:I$6..:I$100sb:H$6..:H$100sb:G$6..:G$100sbT  ({L@0j:I$6..:I$100sb:H$6..:H$100sb:G$6..:G$100sbT  (RUUU?0j:I$6..:I$100sb:H$6..:H$100sb:G$6..:G$100sbT  (TUUU?0j:I$6..:I$100sb:H$6..:H$100sb:G$6..:G$100sbT  ({L@0j:I$6..:I$100sb:H$6..:H$100sb:G$6..:G$100sbT   (@ ()^Calc6P(&@0<$ :I$6..:I$100s P(2@0<$ :I$6..:I$100s P(6@0<$ :I$6..:I$100s P( @0<$ :I$6..:I$100s P(=@0<$ :I$6..:I$100s P(@@0<$ :I$6..:I$100s P(2@0<$ :I$6..:I$100s P(6@0<$ :I$6..:I$100s P(@@0<$ :I$6..:I$100s  (@ (^Stop(.@0K:H$6..:H$100sb:I$6..:I$100sb   T(dS@0K:H$6..:H$100sb:I$6..:I$100sb   T(]]@0K:H$6..:H$100sb:I$6..:I$100sb   T(O6d@0K:H$6..:H$100sb:I$6..:I$100sb   T(ɘ@0K:H$6..:H$100sb:I$6..:I$100sb   T(ɘ@0K:H$6..:H$100sb:I$6..:I$100sb   T(YUUUR@0K:H$6..:H$100sb:I$6..:I$100sb   T(U0@0K:H$6..:H$100sb:I$6..:I$100sb   T( @0K:H$6..:H$100sb:I$6..:I$100sb   T (@ ((.@ (dS@ (e]@ (O6d@ (ɘ@ (ɘ@ (YUUUR@ (U0@ ( @  (@ ()^Calc7U(@0A)  :I$6..:I$100s (U((@0A)  :I$6..:I$100s (U(3@0A)  :I$6..:I$100s (U(@0A)  :I$6..:I$100s (U(.@0A)  :I$6..:I$100s (U(=@0A)  :I$6..:I$100s (U(@0A)  :I$6..:I$100s (U(2@0A)  :I$6..:I$100s (U(9@0A)  :I$6..:I$100s ( (@(^Start +Lag(@0}M:H$6..:H$100sb:I$6..:I$100sb    ((.*@0}M:H$6..:H$100sb:I$6..:I$100sb    ((mQU@0}M:H$6..:H$100sb:I$6..:I$100sb    ((@0}M:H$6..:H$100sb:I$6..:I$100sb    ((,@0}M:H$6..:H$100sb:I$6..:I$100sb    ((U0@0}M:H$6..:H$100sb:I$6..:I$100sb    ((@0}M:H$6..:H$100sb:I$6..:I$100sb    ((S@0}M:H$6..:H$100sb:I$6..:I$100sb    ((ɘ@0}M:H$6..:H$100sb:I$6..:I$100sb    ( (H ((@ ( .*@ (uQU@ (@ ( ,@ ( U0@ (@ ( S@ (ɘ@  (9^Pull ()^Next Op: (&ρ&i/ H3 (A/22: (Vρ&i/ H3 (B/22: (ρ&i/ H3 (C/22: (ρ&i/ H3 (A/33: (ρ&i/ H3 (B/33: (Ё&i/ H3 (C/33: (fЁ&i/ H3 (A/44: (Ё&i/ H3 (B/44: (с&i/ H3 (C/44 !(@!()^Next Op Offset6!(5@"((6!(6@"((6!(7@"((6!(8@"((6!(9@"((6!(:@"((6!("((6!("((6!("(( "(@"(^Stop of Next Op -LagA"(n@ -2b;A"({mĺ@ -2b;A"(}VUUU@ -2b;A"(ܑV@ -2b;A"({mĶ@ -2b;A"(@ -2b;A"(@ -2b;A"(@ -2b;A"(@ -2b; #(@ #(#(n@ #({mĺ@ #(VUUU@ #(ܑV@ #({mĶ@ #(@ #(@ #(@ #(@  $(@$(^Start of Next OpI$(Ć<@ 52 b; I$(%)@ 52 b; I$(M@ 52 b; I$(ܑ@ 52 b; I$(%So1@ 52 b; I$(%{m@ 52 b; I$(@ 52 b; I$(%@ 52 b; I$( @ 52 b;  %(@ %(%(Ć<@ %(-)@ %(M@ %(ܑ@ %(-So1@ %(-{m@ %(@ %(-@ %( @  &(@ &()^Calc8&(@0[:G$6..:G$100s:G$6..:G$100s:H$6..:H$100s &((@0[:G$6..:G$100s:G$6..:G$100s:H$6..:H$100s &(8@0[:G$6..:G$100s:G$6..:G$100s:H$6..:H$100s &(@0[:G$6..:G$100s:G$6..:G$100s:H$6..:H$100s &(8@0[:G$6..:G$100s:G$6..:G$100s:H$6..:H$100s &(=@0[:G$6..:G$100s:G$6..:G$100s:H$6..:H$100s &(9@0[:G$6..:G$100s:G$6..:G$100s:H$6..:H$100s &(>@0[:G$6..:G$100s:G$6..:G$100s:H$6..:H$100s &(@@0[:G$6..:G$100s:G$6..:G$100s:H$6..:H$100s  '(@ '()^Calc9'(ܑ?0j:I$6..:I$100sb:H$6..:H$100sb:G$6..:G$100sbT  '(5?0j:I$6..:I$100sb:H$6..:H$100sb:G$6..:G$100sbT  '(@@0j:I$6..:I$100sb:H$6..:H$100sb:G$6..:G$100sbT  '(No1?0j:I$6..:I$100sb:H$6..:H$100sb:G$6..:G$100sbT  '(5(!O@0j:I$6..:I$100sb:H$6..:H$100sb:G$6..:G$100sbT  '(5Qp@0j:I$6..:I$100sb:H$6..:H$100sb:G$6..:G$100sbT  '(SUU@0j:I$6..:I$100sb:H$6..:H$100sb:G$6..:G$100sbT  '(5@0j:I$6..:I$100sb:H$6..:H$100sb:G$6..:G$100sbT  '(@0j:I$6..:I$100sb:H$6..:H$100sb:G$6..:G$100sbT   ((@(()^Calc10U((@0A)  :I$6..:I$100s (U((&@0A)  :I$6..:I$100s (U((7@0A)  :I$6..:I$100s (U((@0A)  :I$6..:I$100s (U((7@0A)  :I$6..:I$100s (U((<@0A)  :I$6..:I$100s (U((8@0A)  :I$6..:I$100s (U((>@0A)  :I$6..:I$100s (U((?@0A)  :I$6..:I$100s ( )(@)(^Start of Next Op -Lag)(Ć<@0}M:H$6..:H$100sb:I$6..:I$100sb    ()(=@0}M:H$6..:H$100sb:I$6..:I$100sb    ()(@0}M:H$6..:H$100sb:I$6..:I$100sb    ()(Ć<@0}M:H$6..:H$100sb:I$6..:I$100sb    ()(=So1W@0}M:H$6..:H$100sb:I$6..:I$100sb    ()(=M@0}M:H$6..:H$100sb:I$6..:I$100sb    ()(@0}M:H$6..:H$100sb:I$6..:I$100sb    ()(=@0}M:H$6..:H$100sb:I$6..:I$100sb    ()( @0}M:H$6..:H$100sb:I$6..:I$100sb    ( *(@ *(*(Ć<@ *(E@ *(@ *(Ć<@ *(ESo1W@ *(EM@ *(@ *(E@ *(@  +(@ +(^Stop3+(@ ;3+(M@ ;3+(VUUU@ ;3+()@ ;3+(MM@ ;3+(M@ ;3+(So1@ ;3+(M{m@ ;3+(@ ; ,(@ ,(,(@ ,(U@ ,(VUUU@ ,()@ ,(UM@ ,(U@ ,(So1@ ,(U{m@ ,(%@  -(@-()^Calc11-(&@0[:G$6..:G$100s:G$6..:G$100s:H$6..:H$100s -(7@0[:G$6..:G$100s:G$6..:G$100s:H$6..:H$100s -(?@0[:G$6..:G$100s:G$6..:G$100s:H$6..:H$100s -(,@0[:G$6..:G$100s:G$6..:G$100s:H$6..:H$100s -(<@0[:G$6..:G$100s:G$6..:G$100s:H$6..:H$100s -(B@0[:G$6..:G$100s:G$6..:G$100s:H$6..:H$100s -(4@0[:G$6..:G$100s:G$6..:G$100s:H$6..:H$100s -(8@0[:G$6..:G$100s:G$6..:G$100s:H$6..:H$100s -(@@0[:G$6..:G$100s:G$6..:G$100s:H$6..:H$100s  .(@.()^Calc12.(TUUU?0j:I$6..:I$100sb:H$6..:H$100sb:G$6..:G$100sbT  .(]TUUU@0j:I$6..:I$100sb:H$6..:H$100sb:G$6..:G$100sbT  .(@@0j:I$6..:I$100sb:H$6..:H$100sb:G$6..:G$100sbT  .(h?0j:I$6..:I$100sb:H$6..:H$100sb:G$6..:G$100sbT  .(]^@0j:I$6..:I$100sb:H$6..:H$100sb:G$6..:G$100sbT  .(] @0j:I$6..:I$100sb:H$6..:H$100sb:G$6..:G$100sbT  .(?0j:I$6..:I$100sb:H$6..:H$100sb:G$6..:G$100sbT  .(]^@0j:I$6..:I$100sb:H$6..:H$100sb:G$6..:G$100sbT  .(-@0j:I$6..:I$100sb:H$6..:H$100sb:G$6..:G$100sbT   /(@/()^Calc13P/(@0<$ :I$6..:I$100s P/(0@0<$ :I$6..:I$100s P/(;@0<$ :I$6..:I$100s P/(&@0<$ :I$6..:I$100s P/(,@0<$ :I$6..:I$100s P/(@@0<$ :I$6..:I$100s P/(@0<$ :I$6..:I$100s P/(4@0<$ :I$6..:I$100s P/(8@0<$ :I$6..:I$100s  0(@ 0(^Start0(|@0K:H$6..:H$100sb:I$6..:I$100sb   S0(e@0K:H$6..:H$100sb:I$6..:I$100sb   S0(@0K:H$6..:H$100sb:I$6..:I$100sb   S0(Ć<@0K:H$6..:H$100sb:I$6..:I$100sb   S0(e)@0K:H$6..:H$100sb:I$6..:I$100sb   S0(eM@0K:H$6..:H$100sb:I$6..:I$100sb   S0(ܑ@0K:H$6..:H$100sb:I$6..:I$100sb   S0(eSo1@0K:H$6..:H$100sb:I$6..:I$100sb   S0(5{m@0K:H$6..:H$100sb:I$6..:I$100sb   S 1(@ 1(1(|@ 1(m@ 1(@ 1(Ć<@ 1(m)@ 1(mM@ 1(ܑ@ 1(mSo1@ 1(={m@  2(@2()^Calc14U2(&@0A)  :I$6..:I$100s (U2(7@0A)  :I$6..:I$100s (U2(>@0A)  :I$6..:I$100s (U2(,@0A)  :I$6..:I$100s (U2(<@0A)  :I$6..:I$100s (U2(B@0A)  :I$6..:I$100s (U2(3@0A)  :I$6..:I$100s (U2(7@0A)  :I$6..:I$100s (U2(?@0A)  :I$6..:I$100s ( 3(@3(^Stop -Lag3( VUUU@0}M:H$6..:H$100sb:I$6..:I$100sb    (3(uVUUU@0}M:H$6..:H$100sb:I$6..:I$100sb    (3(@0}M:H$6..:H$100sb:I$6..:I$100sb    (3( n@0}M:H$6..:H$100sb:I$6..:I$100sb    (3(u{mĺ@0}M:H$6..:H$100sb:I$6..:I$100sb    (3(uVUUU@0}M:H$6..:H$100sb:I$6..:I$100sb    (3( ܑV@0}M:H$6..:H$100sb:I$6..:I$100sb    (3(u{mĶ@0}M:H$6..:H$100sb:I$6..:I$100sb    (3(E@0}M:H$6..:H$100sb:I$6..:I$100sb    ( 4(H 4(4(VUUU@ 4(}VUUU@ 4(@ 4(n@ 4(}{mĺ@ 4(}VUUU@ 4(ܑV@ 4(}{mĶ@ 4(M@ 5(9^Push 25(^Start of Prev Op +Lag=5(? )2b;  =5(? )2b;  =5(? )2b;  =5(@ )2b;  =5(.*@ )2b;  =5(@ )2b;  =5(ܑ@ )2b;  =5(,@ )2b;  =5(UU0@ )2b;  6(@ 6(6(%? 6(? 6(? 6(%@ 6(.*@ 6(@ 6(%ܑ@ 6(,@ 6(]U0@  7(@7(^Stop of Prev Op=7(-? )2b;   =7(? )2b;   =7(? )2b;   =7(-.@ )2b;   =7(dS@ )2b;   =7(QO@ )2b;   =7(-O6d@ )2b;   =7(ɘ@ )2b;   =7(eɘ@ )2b;   8(@ 8(8(5? 8(? 8(? 8(5.@ 8(dS@ 8(QO@ 8(5O6d@ 8(ɘ@ 8(mɘ@  9(@9()^Calc159(0[:G$6..:G$100s:G$6..:G$100s:H$6..:H$100s 9(0[:G$6..:G$100s:G$6..:G$100s:H$6..:H$100s 9(0[:G$6..:G$100s:G$6..:G$100s:H$6..:H$100s 9(*@0[:G$6..:G$100s:G$6..:G$100s:H$6..:H$100s 9(6@0[:G$6..:G$100s:G$6..:G$100s:H$6..:H$100s 9(@@0[:G$6..:G$100s:G$6..:G$100s:H$6..:H$100s 9((@0[:G$6..:G$100s:G$6..:G$100s:H$6..:H$100s 9(8@0[:G$6..:G$100s:G$6..:G$100s:H$6..:H$100s 9(<@0[:G$6..:G$100s:G$6..:G$100s:H$6..:H$100s  :(@:()^Calc16:(=0j:I$6..:I$100sb:H$6..:H$100sb:G$6..:G$100sbT  :(0j:I$6..:I$100sb:H$6..:H$100sb:G$6..:G$100sbT  :( 0j:I$6..:I$100sb:H$6..:H$100sb:G$6..:G$100sbT  :(=E]?0j:I$6..:I$100sb:H$6..:H$100sb:G$6..:G$100sbT  :(Ԓ@0j:I$6..:I$100sb:H$6..:H$100sb:G$6..:G$100sbT  :(R/S @0j:I$6..:I$100sb:H$6..:H$100sb:G$6..:G$100sbT  :(=?0j:I$6..:I$100sb:H$6..:H$100sb:G$6..:G$100sbT  :(0&@0j:I$6..:I$100sb:H$6..:H$100sb:G$6..:G$100sbT  :(u@0j:I$6..:I$100sb:H$6..:H$100sb:G$6..:G$100sbT   ;(@;()^Calc17U;(W@0A)  :I$6..:I$100s (U;(W@0A)  :I$6..:I$100s (U;(W@0A)  :I$6..:I$100s (U;(,@0A)  :I$6..:I$100s (U;(7@0A)  :I$6..:I$100s (U;(@@0A)  :I$6..:I$100s (U;((@0A)  :I$6..:I$100s (U;(8@0A)  :I$6..:I$100s (U;(<@0A)  :I$6..:I$100s ( <(@<(^Stop of Prev Op +Lag<(E?0S2:H$6..:H$100sb:I$6..:I$100sb    ;(<(?0S2:H$6..:H$100sb:I$6..:I$100sb    ;(<(?0S2:H$6..:H$100sb:I$6..:I$100sb    ;(<(EO6d@0S2:H$6..:H$100sb:I$6..:I$100sb    ;(<(QU@0S2:H$6..:H$100sb:I$6..:I$100sb    ;(<(@0S2:H$6..:H$100sb:I$6..:I$100sb    ;(<(E,@0S2:H$6..:H$100sb:I$6..:I$100sb    ;(<(U0@0S2:H$6..:H$100sb:I$6..:I$100sb    ;(<(}@0S2:H$6..:H$100sb:I$6..:I$100sb    ;( =(@ =(=(M? =(? =(? =(MO6d@ =(QU@ =(@ =(M,@ =(U0@ =(@  >(@ >(^StartF>(U@ 2;TF>(.@ 2;TF>(%@ 2;TF>(UĆ<@ 2;TF>(.*@ 2;TF>(ɘ@ 2;TF>(Uܑ@ 2;TF>(So1@ 2;TF>(U0@ 2;T ?(@ ?(?(]@ ?(.@ ?(-@ ?(]Ć<@ ?(.*@ ?(ɘ@ ?(]ܑ@ ?(So1@ ?(U0@  @(@@()^Calc18@(@0[:G$6..:G$100s:G$6..:G$100s:H$6..:H$100s @(&@0[:G$6..:G$100s:G$6..:G$100s:H$6..:H$100s @(7@0[:G$6..:G$100s:G$6..:G$100s:H$6..:H$100s @(@0[:G$6..:G$100s:G$6..:G$100s:H$6..:H$100s @(.@0[:G$6..:G$100s:G$6..:G$100s:H$6..:H$100s @(=@0[:G$6..:G$100s:G$6..:G$100s:H$6..:H$100s @(@0[:G$6..:G$100s:G$6..:G$100s:H$6..:H$100s @(4@0[:G$6..:G$100s:G$6..:G$100s:H$6..:H$100s @(8@0[:G$6..:G$100s:G$6..:G$100s:H$6..:H$100s  A(@A()^Calc19A(e?0j:I$6..:I$100sb:H$6..:H$100sb:G$6..:G$100sbT  A(E]?0j:I$6..:I$100sb:H$6..:H$100sb:G$6..:G$100sbT  A(5TUUU@0j:I$6..:I$100sb:H$6..:H$100sb:G$6..:G$100sbT  A(eܑ?0j:I$6..:I$100sb:H$6..:H$100sb:G$6..:G$100sbT  A({?0j:I$6..:I$100sb:H$6..:H$100sb:G$6..:G$100sbT  A({L@0j:I$6..:I$100sb:H$6..:H$100sb:G$6..:G$100sbT  A(eNo1?0j:I$6..:I$100sb:H$6..:H$100sb:G$6..:G$100sbT  A(?0j:I$6..:I$100sb:H$6..:H$100sb:G$6..:G$100sbT  A({L@0j:I$6..:I$100sb:H$6..:H$100sb:G$6..:G$100sbT   B(@B()^Calc20PB(&@0<$ :I$6..:I$100s PB(2@0<$ :I$6..:I$100s PB(;@0<$ :I$6..:I$100s PB($@0<$ :I$6..:I$100s PB(=@0<$ :I$6..:I$100s PB(@@0<$ :I$6..:I$100s PB(4@0<$ :I$6..:I$100s PB(8@0<$ :I$6..:I$100s PB(@@0<$ :I$6..:I$100s  C(@ C(^StopC(m.@0K:H$6..:H$100sb:I$6..:I$100sb   TC(dS@0K:H$6..:H$100sb:I$6..:I$100sb   TC(=QO@0K:H$6..:H$100sb:I$6..:I$100sb   TC(mO6d@0K:H$6..:H$100sb:I$6..:I$100sb   TC(ɘ@0K:H$6..:H$100sb:I$6..:I$100sb   TC(ɘ@0K:H$6..:H$100sb:I$6..:I$100sb   TC(mSo1@0K:H$6..:H$100sb:I$6..:I$100sb   TC(U0@0K:H$6..:H$100sb:I$6..:I$100sb   TC( @0K:H$6..:H$100sb:I$6..:I$100sb   T D(@ D(D(u.@ D(dS@ D(EQO@ D(uO6d@ D(ɘ@ D(ɘ@ D(uSo1@ D(U0@ D( @  E(@E()^Calc21UE(@0A)  :I$6..:I$100s (UE((@0A)  :I$6..:I$100s (UE(8@0A)  :I$6..:I$100s (UE(@0A)  :I$6..:I$100s (UE(.@0A)  :I$6..:I$100s (UE(=@0A)  :I$6..:I$100s (UE(@0A)  :I$6..:I$100s (UE(4@0A)  :I$6..:I$100s (UE(9@0A)  :I$6..:I$100s ( F(@F(^Start +LagF(}@0}M:H$6..:H$100sb:I$6..:I$100sb    (F(.*@0}M:H$6..:H$100sb:I$6..:I$100sb    (F(M@0}M:H$6..:H$100sb:I$6..:I$100sb    (F(}ܑ@0}M:H$6..:H$100sb:I$6..:I$100sb    (F(,@0}M:H$6..:H$100sb:I$6..:I$100sb    (F(U0@0}M:H$6..:H$100sb:I$6..:I$100sb    (F(}Ć<@0}M:H$6..:H$100sb:I$6..:I$100sb    (F(Ć<@0}M:H$6..:H$100sb:I$6..:I$100sb    (F(ɘ@0}M:H$6..:H$100sb:I$6..:I$100sb    ( G(H G(G(@ G(.*@ G(U@ G(ܑ@ G(,@ G(U0@ G(Ć<@ G(Ć<@ G(ɘ@ () Section 38$u<7~,7,uuuuރރ [!B #r e@Arrow80 X {GX ? "!B $, q@Arrow81 X {GX ? "!B % I^@Arrow82 X {GX ? "\0)  %)'Project Schedulingj)'So far our examples have related to production activities, where a job's dependancies are implied,g)'such as the previous job on a work centre, or a previous operation on a job. With a project theL)' pattern of dependancies have to be spelled out. Here is an example:)'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) 'Total Duration:))#'@IF(C31,@@("H"&@STRING(C31,0)),0))$'@MAX(E31,F31))%'+G31+B31t)''Please excuse the fact that this example is not politically correct and gender neutral. Please note however,t)(' that you brush your teeth whilst turning on the bath taps, but you shouln't have bothered because you standx))'waiting for the bath to fill. You also put on your tie while walking to the kitchen, which saves you 15 seconds.)^Duration secs. ) ) )  ) )  ) )  ) ) )< )P ) ) )Z )( ) ) ) ) ) )< )Z )< ),() ?< <  )^Depend-ency 1 ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) )) 'hh:mm:ss)^Depend-ency 2 ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) )D)#'- find the time that the dependency stops, at its row numberB)$'- the activity starts at the later of the two dependencies1)%'- the stop is the start plus the duration)^Dep. Stop 1<)(HHs;<)>@(HHs;<) F@(HHs;<) @P@(HHs;<) @P@(HHs;<) @U@(HHs;<) @U@(HHs;<)Y@(HHs;<)[@(HHs;<)Y@(HHs;<)d@(HHs;<)n@(HHs;<)@o@(HHs;<)pr@(HHs;<)x@(HHs;<)z@(HHs;<)p|@(HHs;<)}@(HHs;<)~@(HHs;<)~@(HHs;<)h@(HHs;<)H@(HHs;<)@(HHs;<)@(HHs;)^Dep. Stop 2<)(HHs;<)(HHs;<) (HHs;<) (HHs;<) (HHs;<) (HHs;<) (HHs;<)(HHs;<)(HHs;<)(HHs;<)(HHs;<)(HHs;<) r@(HHs;<)(HHs;<)(HHs;<)(HHs;<)(HHs;<)(HHs;<)(HHs;<)(HHs;<)@(HHs;<)n@(HHs;<)`m@(HHs;<)@(HHs; )^Start%)T%)>@T%) F@T%) @P@T%) @P@T%) @U@T%) @U@T%)Y@T%)[@T%)Y@T%)d@T%)n@T%) r@T%)pr@T%)x@T%)z@T%)p|@T%)}@T%)~@T%)~@T%)h@T%)H@T%)@T%)@T )^Stop$)>@ $)F@ $) @P@ $) n@ $) @U@ $) `m@ $) Y@ $)[@ $) r@ $)d@ $)n@ $)@o@ $)pr@ $)x@ $)z@ $)p|@ $)}@ $)~@ $)h@ $)@ $)H@ $)@ $)@ $),@ )* Section 39$u<7~77,:ʈ         [!B  !rGV@Arrow83 X {GX ? "!B  " Vt p@Arrow84 X {GX ? "!B  # B @Arrow85 X {GX ? "!B  $ @Arrow86 X {GX ? "!B % R@Arrow87 X {GX ? "!B & A@Arrow88 X {GX ? "!B '  @Arrow89 X {GX ? "\l''     *'Critical Path Analysis*'The activities with zero float 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. *'Row * * * * * * *  * * * * * * * * * * * * * * * * *!*!'@MATCH(A31,D$1..D$32,0)+1<*"'@MATCH(A31,@@("D"&@STRING(J31+1,0)&"..D31"),0)+J31+1!*#'@MATCH(A31,E$1..E$32,0)+15*$'@IF(@ISERR(J31),$I$31,@@("Q"&@STRING(J31,0)))*%'@MIN(M31..O31)*&'+P31-C31*''+Q31-H31*'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. secs * * *  * *  * *  * * *< *P * * *Z *( * * * * * *< *Z *< *, *^Dep 1 * * * * * * * * * * * * * * * * * * * * * * * * *^Dep 2 * * * * * * * * * * * * * * * * * * * * * * * **^Dep. Stop 1<*(IHs;<*>@(IHs;<* F@(IHs;<* @P@(IHs;<* @P@(IHs;<* @U@(IHs;<* @U@(IHs;<*Y@(IHs;<*[@(IHs;<*Y@(IHs;<*d@(IHs;<*n@(IHs;<*@o@(IHs;<*pr@(IHs;<*x@(IHs;<*z@(IHs;<*p|@(IHs;<*}@(IHs;<*~@(IHs;<*~@(IHs;<*h@(IHs;<*H@(IHs;<*@(IHs;<*@(IHs;*^Dep. Stop 2<*(IHs;<*(IHs;<* (IHs;<* (IHs;<* (IHs;<* (IHs;<* (IHs;<*(IHs;<*(IHs;<*(IHs;<*(IHs;<*(IHs;<* r@(IHs;<*(IHs;<*(IHs;<*(IHs;<*(IHs;<*(IHs;<*(IHs;<*(IHs;<*@(IHs;<*n@(IHs;<*`m@(IHs;<*@(IHs;*^Push Start%*T%*>@T%* F@T%* @P@T%* @P@T%* @U@T%* @U@T%*Y@T%*[@T%*Y@T%*d@T%*n@T%* r@T%*pr@T%*x@T%*z@T%*p|@T%*}@T%*~@T%*~@T%*h@T%*H@T%*@T%*@Te*!'- find the row on which the activity is first cited as a dependancy, by looking down column DK*"'- find the second citing by looking down the column below the firstl*#'- find the row on which the activity is first cited as a second dependancy, by looking down column Ez*$'- the start of the first acivity which is dependant on this one, if there isn't one, return the end of the project0*%'- the stop is the earliest of the startsB*&'- the start of the activity is the stop minus the durationO*''- the Float is the difference between the Pull Start and the Push Start*^Push Stop$*>@ $*F@ $* @P@ $* n@ $* @U@ $* `m@ $* Y@ $*[@ $* r@ $*d@ $*n@ $*@o@ $*pr@ $*x@ $*z@ $*p|@ $*}@ $*~@ $*h@ $*@ $*H@ $*@ $*@ $*,@  *^Next 1: *"@& : *$@& : * &@& : * & : * *@& : * & : * .@& : *0@& : *& : *2@& : *3@& : *4@& : *5@& : *6@& : *7@& : *8@& : *9@& : *:@& : *<@& : *& : *=@& : *>@& : *& : *&  *^Next 2Y *E3D H..D31s  Y *E3D H..D31s  Y * (@E3D H..D31s  Y * E3D H..D31s  Y * ,@E3D H..D31s  Y * E3D H..D31s  Y * 1@E3D H..D31s  Y *E3D H..D31s  Y *E3D H..D31s  Y *E3D H..D31s  Y *E3D H..D31s  Y *E3D H..D31s  Y *E3D H..D31s  Y *E3D H..D31s  Y *E3D H..D31s  Y *E3D H..D31s  Y *E3D H..D31s  Y *;@E3D H..D31s  Y *E3D H..D31s  Y *E3D H..D31s  Y *E3D H..D31s  Y *?@E3D H..D31s  Y *E3D H..D31s  Y *E3D H..D31s   *^Next 3: *& : *& : * & : * =@& : * & : * >@& : * & : *& : *4@& : *& : *& : *& : *& : *& : *& : *& : *& : *& : *& : *<@& : *& : *& : *?@& : *&  *^Next start 1; *>@'2QHs;*; *F@'2QHs;*; * Py@'2QHs;*; * ,@'2QHs;*; * h@'2QHs;*; * ,@'2QHs;*; * Y@'2QHs;*; *[@'2QHs;*; *,@'2QHs;*; *i@'2QHs;*; *q@'2QHs;*; * r@'2QHs;*; *pr@'2QHs;*; *x@'2QHs;*; *z@'2QHs;*; *p|@'2QHs;*; *}@'2QHs;*; *~@'2QHs;*; *h@'2QHs;*; *,@'2QHs;*; *H@'2QHs;*; *@'2QHs;*; *,@'2QHs;*; *,@'2QHs;* *^Next start 2; *,@'2QHs;*; *,@'2QHs;*; * @P@'2QHs;*; * ,@'2QHs;*; * @U@'2QHs;*; * ,@'2QHs;*; * a@'2QHs;*; *,@'2QHs;*; *,@'2QHs;*; *,@'2QHs;*; *,@'2QHs;*; *,@'2QHs;*; *,@'2QHs;*; *,@'2QHs;*; *,@'2QHs;*; *,@'2QHs;*; *,@'2QHs;*; *@'2QHs;*; *,@'2QHs;*; *,@'2QHs;*; *,@'2QHs;*; *@'2QHs;*; *,@'2QHs;*; *,@'2QHs;**^Next start 3;*,@'2QHs;*;*,@'2QHs;*;* ,@'2QHs;*;* H@'2QHs;*;* ,@'2QHs;*;* @'2QHs;*;* ,@'2QHs;*;*,@'2QHs;*;* r@'2QHs;*;*,@'2QHs;*;*,@'2QHs;*;*,@'2QHs;*;*,@'2QHs;*;*,@'2QHs;*;*,@'2QHs;*;*,@'2QHs;*;*,@'2QHs;*;*,@'2QHs;*;*,@'2QHs;*;*h@'2QHs;*;*,@'2QHs;*;*,@'2QHs;*;*@'2QHs;*;*,@'2QHs;**^Pull Stop"*>@S"*F@S"* @P@S"* H@S"* @U@S"* @S"* Y@S"*[@S"* r@S"*i@S"*q@S"* r@S"*pr@S"*x@S"*z@S"*p|@S"*}@S"*~@S"*h@S"*h@S"*H@S"*@S"*@S"*,@S*^Pull Start$* $*>@ $* F@ $* Py@ $* @P@ $* h@ $* @U@ $*Y@ $*[@ $*a@ $*i@ $*q@ $* r@ $*pr@ $*x@ $*z@ $*p|@ $*}@ $*~@ $*@ $*h@ $*H@ $*@ $*@  *^Float$* $* $*  $* @u@ $*  $* {@ $*  $* $* $*D@ $*D@ $*D@ $* $* $* $* $* $* $* $*.@ $* $* $* $* *+ Section 40$u<7~S7,          :::::uс:    [!Ia /V t6@ Inserted10CHART 1"\3 33333333 3 3 3 3 333333333333333333*+'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:+'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:+a^Prod-uctT+@Idle;;;T+@Idle;;;T+@Idle;;;T+@Idle;;;T+ @Idle;;;T+!@Idle;;;T+"@Idle;;;T+#@Idle;;;T+$@Idle;;;T+%@Idle;;;T+&@Idle;;;T+'@Idle;;;T+(@Idle;;;T+)@Idle;;;T+*@Idle;;;T++@Idle;;;T+,@Idle;;;T+-@Idle;;;T+.@Idle;;;T+/@Idle;;;T+0@Idle;;;T+1@Idle;;;T+2@Idle;;;T+3@Idle;;;$+ '- which product to make next+ '- the quantity to make+ '- when to stop"+ '- how long to lie idle for +a^Force + + + + + +! +" +# +$ +% +& +' +( +) +* ++ +, +- +. +/ +0 +1 +2 +3 +a^Qty^+J&Idle;;;^+J&Idle;;;^+J&Idle;;;^+J&Idle;;;^+ J&Idle;;;^+!J&Idle;;;^+"J&Idle;;;^+#J&Idle;;;^+$J&Idle;;;^+%J&Idle;;;^+&J&Idle;;;^+'J&Idle;;;^+(J&Idle;;;^+)J&Idle;;;^+*J&Idle;;;^++J&Idle;;;^+,J&Idle;;;^+-J&Idle;;;^+.J&Idle;;;^+/J&Idle;;;^+0J&Idle;;;^+1J&Idle;;;^+2J&Idle;;;^+3J&Idle;;; +a^Force + + + + + +! +" +# +$ +% +& +' +( +) +* ++ +, +- +. +/ +0 +1 +2 +3+@ +^C +H +a^Idle hoursf+-R.  ?; ;Tf+-R.  ?; ;Tf+-R.  ?; ;Tf+-R.  ?; ;Tf+ -R.  ?; ;Tf+!-R.  ?; ;Tf+"-R.  ?; ;Tf+#-R.  ?; ;Tf+$-R.  ?; ;Tf+%-R.  ?; ;Tf+&-R.  ?; ;Tf+'-R.  ?; ;Tf+(-R.  ?; ;Tf+)-R.  ?; ;Tf+*-R.  ?; ;T      !"#$%&'()*+,-./0123456789:;<=>?@ABCDEFGHIJKLMNOPQRSTUVWXYZ[\]^_`abcdefghijklmnopqrstuvwxz{f++-R.  ?; ;Tf+,-R.  ?; ;Tf+--R.  ?; ;Tf+.-R.  ?; ;Tf+/-R.  ?; ;Tf+0-R.  ?; ;Tf+1-R.  ?; ;Tf+2-R.  ?; ;Tf+3-R.  ?; ;T+VUUU? +a^Force+'Opening stock: + + + + + +! +" +# +$ +% +& +' +( +) +* ++ +, +- +. +/ +0 +1 +2 +3+'Speed - units per hour'+'Forecast sales - units per week+'Min cover - days+'Max cover - days+'Min cover - units+'Max cover - units+a^Run hoursK+57IdleZ ; ++K+57IdleZ ; ++K+57IdleZ ; ++K+57IdleZ ; ++K+ 57IdleZ ; ++K+!57IdleZ ; ++K+"57IdleZ ; ++K+#57IdleZ ; ++K+$57IdleZ ; ++K+%57IdleZ ; ++K+&57IdleZ ; ++K+'57IdleZ ; ++K+(57IdleZ ; ++K+)57IdleZ ; ++K+*57IdleZ ; ++K++57IdleZ ; ++K+,57IdleZ ; ++K+-57IdleZ ; ++K+.57IdleZ ; ++K+/57IdleZ ; ++K+057IdleZ ; ++K+157IdleZ ; ++K+257IdleZ ; ++K+357IdleZ ; ++ +i^Start+=@+=+=+=+ =+!=+"=+#=+$=+%=+&=+'=+(=+)=+*=++=+,=+-=+.=+/=+0=+1=+2=+3= +X+E@+E+E+E+ E+!E+"E+#E+$E+%E+&E+'E+(E+)E+*E++E+,E+-E+.E+/E+0E+1E+2E+3E +i'Stop$ +@ 2 +M    2 +M    2 +M    2 +M    2 + M    2 +!M    2 +"M    2 +#M    2 +$M    2 +%M    2 +&M    2 +'M    2 +(M    2 +)M    2 +*M    2 ++M    2 +,M    2 +-M    2 +.M    2 +/M    2 +0M    2 +1M    2 +2M    2 +3M      +X +@ +U +U +U +U + U +!U +"U +#U +$U +%U +&U +'U +(U +)U +*U ++U +,U +-U +.U +/U +0U +1U +2U +3U +i^A  +j,  +j  +j  +j( +%I$IR@  ( +m@  # +i^Projected Inventory - units  +})T +]@      T +]@      T +]@      T +]@      T + ]@      T +!]@      T +"]@      T +#]@      T +$]@      T +%]@      T +&]@      T +']@      T +(]@      T +)]@      T +*]@      T ++]@      T +,]@      T +-]@      T +.]@      T +/]@      T +0]@      T +1]@      T +2]@      T +3]@       +q^B  +r^  +r@  +r  +r( +m۶m۱@  ( +u@@    +P  +T +e@      T +e@      T +e@      T +e@      T + e@      T +!e@      T +"e@      T +#e@      T +$e@      T +%e@      T +&e@      T +'e@      T +(e@      T +)e@      T +*e@      T ++e@      T +,e@      T +-e@      T +.e@      T +/e@      T +0e@      T +1e@      T +2e@      T +3e@       +y^C  +z  +zX  +z  +z( +@@  ( +}X@    +P  +:"T +m@      T +m@      T +m@      T +m@      T + m@      T +!m@      T +"m@      T +#m@      T +$m@      T +%m@      T +&m@      T +'m@      T +(m@      T +)m@      T +*m@      T ++m@      T +,m@      T +-m@      T +.m@      T +/m@      T +0m@      T +1m@      T +2m@      T +3m@       +^D +6 +d + +(+I$I@  (+d@   +P +.T+u@      T+u@      T+u@      T+u@      T+ u@      T+!u@      T+"u@      T+#u@      T+$u@      T+%u@      T+&u@      T+'u@      T+(u@      T+)u@      T+*u@      T++u@      T+,u@      T+-u@      T+.u@      T+/u@      T+0u@      T+1u@      T+2u@      T+3u@       +^E + +(# + +(+%I$I"@  (+@   +X +1T+}@      T+}@      T+}@      T+}@      T+ }@      T+!}@      T+"}@      T+#}@      T+$}@      T+%}@      T+&}@      T+'}@      T+(}@      T+)}@      T+*}@      T++}@      T+,}@      T+-}@      T+.}@      T+/}@      T+0}@      T+1}@      T+2}@      T+3}@       +i^A+i^Projected Cover - days(+H.!-@  (+  (+  (+  (+  (+   (+!  (+"  (+#  (+$  (+%  (+&  (+'  (+(  (+)  (+*  (++  (+,  (+-  (+.  (+/  (+0  (+1  (+2  (+3   +q^B +P(+ C?  (+  (+  (+  (+  (+   (+!  (+"  (+#  (+$  (+%  (+&  (+'  (+(  (+)  (+*  (++  (+,  (+-  (+.  (+/  (+0  (+1  (+2  (+3   +y^C +P(+/$!@  (+  (+  (+  (+  (+   (+!  (+"  (+#  (+$  (+%  (+&  (+'  (+(  (+)  (+*  (++  (+,  (+-  (+.  (+/  (+0  (+1  (+2  (+3   +^D +P(++)@  (+  (+  (+  (+  (+   (+!  (+"  (+#  (+$  (+%  (+&  (+'  (+(  (+)  (+*  (++  (+,  (+-  (+.  (+/  (+0  (+1  (+2  (+3   +^E +X(+%JX̦#@  (+  (+  (+  (+  (+   (+!  (+"  (+#  (+$  (+%  (+&  (+'  (+(  (+)  (+*  (++  (+,  (+-  (+.  (+/  (+0  (+1  (+2  (+3  +a'Lowest Cover"+S"+S"+S"+S"+ S"+!S"+"S"+#S"+$S"+%S"+&S"+'S"+(S"+)S"+*S"++S"+,S"+-S"+.S"+/S"+0S"+1S"+2S"+3S+a'Offset6+"6+"6+"6+"6+ "6+!"6+""6+#"6+$"6+%"6+&"6+'"6+("6+)"6+*"6++"6+,"6+-"6+."6+/"6+0"6+1"6+2"6+3"+a'Next Prod1+ b1+ b1+ b1+ b1+  b1+! b1+" b1+# b1+$ b1+% b1+& b1+' b1+( b1+) b1+* b1++ b1+, b1+- b1+. b1+/ b1+0 b1+1 b1+2 b1+3 b+a'Inventory1+ b1+ b1+ b1+ b1+  b1+! b1+" b1+# b1+$ b1+% b1+& b1+' b1+( b1+) b1+* b1++ b1+, b1+- b1+. b1+/ b1+0 b1+1 b1+2 b1+3 b +a'Speed1+ b1+ b1+ b1+ b1+  b1+! b1+" b1+# b1+$ b1+% b1+& b1+' b1+( b1+) b1+* b1++ b1+, b1+- b1+. b1+/ b1+0 b1+1 b1+2 b1+3 b+a'For- cast1+ b1+ b1+ b1+ b1+  b1+! b1+" b1+# b1+$ b1+% b1+& b1+' b1+( b1+) b1+* b1++ b1+, b1+- b1+. b1+/ b1+0 b1+1 b1+2 b1+3 b+a'Min Cover1+ b1+ b1+ b1+ b1+  b1+! b1+" b1+# b1+$ b1+% b1+& b1+' b1+( b1+) b1+* b1++ b1+, b1+- b1+. b1+/ b1+0 b1+1 b1+2 b1+3 b+a'Min units1+ b1+ b1+ b1+ b1+  b1+! b1+" b1+# b1+$ b1+% b1+& b1+' b1+( b1+) b1+* b1++ b1+, b1+- b1+. b1+/ b1+0 b1+1 b1+2 b1+3 b+a'Max units1+ b1+ b1+ b1+ b1+  b1+! b1+" b1+# b1+$ b1+% b1+& b1+' b1+( b1+) b1+* b1++ b1+, b1+- b1+. b1+/ b1+0 b1+1 b1+2 b1+3 b+a'To makeV+B      V+B      V+B      V+B      V+ B      V+!B      V+"B      V+#B      V+$B      V+%B      V+&B      V+'B      V+(B      V+)B      V+*B      V++B      V+,B      V+-B      V+.B      V+/B      V+0B      V+1B      V+2B      V+3B      +, Section 41$u<7~b7,hh %,'Make-to-Stock Logic Explained#,'The logic works as follows: ,'-  ,'-  ,'-  , '-  , '-  , '-  , '-  , '-  ,'-  ,'-  ,'-  ,'- ,'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.2,'the first job is the "Product running now" ,'-  ,'- a,'the duration of the job is calculated from the speed, and shown in the "Run hours" columng,'a separate column for each product records the projected inventory level at the end of each jobu, '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 coverl, '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 runningN,'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 run1,'it starts at the "Start of the first job"1,'and the "Quantity committed" is specified,- Section 42$u<7~k7,   :        :::::uс:     . .  .)       .  &        3-'Make-to-Stock Schedule - Formulas Explained-'Start of the first job:-'Product running now:-'Quantity committed:- a^Prod-uctT- @Idle;;;T-@Idle;;;T-@Idle;;;T-@Idle;;;T-@Idle;;; -'A -'C -'E -'G -'H -'I -'J -'K -'L -'Q -'V -'W - 'X -!'Y -"'Z -#'AA -$'AB -%'AC -&'AD -''AE -)'AG -*'AH -+'AI -,'AJ --'AK -.'AL - a^Force - - - - --'Product -'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#-)'Inventory replenishment qty-*'forecast units per hour-+'effective speed-,'run duration--'sales during production-.'to make - a^Qty^- 5J&Idle;;;^-5J&Idle;;;^-5J&Idle;;;^-5J&Idle;;;^-5J&Idle;;; - a^Force - - - - --@ -^C -H - a^Idle hoursf- =R.  ?; ;Tf-=R.  ?; ;Tf-=R.  ?; ;Tf-=R.  ?; ;Tf-=R.  ?; ;T-VUUU? - a^Force- 'Opening stock: - - - - -I-'@IF(E18,"Idle",@IF(@ISEMPTY(A17),E$5,@IF(@ISEMPTY(B18),X17,B18)))L-'@IF(A18="Idle",0,@IF(@ISEMPTY(C17),E$6,@IF(@ISEMPTY(D18),AE17,D18)))P-'@MAX(@IF(@ISEMPTY(F18),@IF(AA17,@ROUND((Y17-AC17)/AA17,7),0)*168,F18),0)R-'@IF(A18="Idle",0,C18/@HLOOKUP(A18,$Section 40:$L$19..$Section 40:$P$23,1)) -'+J17 -'+H18-'+H18+((G18+E18)/24) -'+J184-'+L17-(L$6*($G18+$E18)/168)+(($A18=L$4)*$C18)-'+L18/L$6*7-'@MIN(Q18..U18)-'@MATCH(V18,Q18..U18,0)- '@INDEX(Q$4..U$4,W18,0)-!'@INDEX(L18..P18,W18,0)-"'@INDEX(L$5..P$5,W18,0)-#'@INDEX(L$6..P$6,W18,0)-$'@INDEX(L$7..P$7,W18,0)-%'@INDEX(L$9..P$9,W18,0) -&'@INDEX(L$10..P$10,W18,0)4-''+AD18-Y18+AA18*(AD18-Y18)/(Z18-AA18/168)/168-*'+AA18/168-+'+Z18-AH18-,'+AG18/AI18--'+AJ18*AH18-.'+AG18+AK18-'Speed - units per hour'-'Forecast sales - units per week-'Min cover - days-'Max cover - days-'Min cover - units- 'Max cover - units- a^Run hoursK- E7IdleZ ; ++K-E7IdleZ ; ++K-E7IdleZ ; ++K-E7IdleZ ; ++K-E7IdleZ ; ++-)'+AD18-Y18 - i^Start- M@-M-M-M-M - X- U@-U-U-U-U - i'Stop$ - @ 2 - ]    2 -]    2 -]    2 -]    2 -]      - X - @ - e -e -e -e -e -i^A  -j,  -j  -j  -j( -%I$IR@  ( - m@  # - i^Projected Inventory - units  - })T - m@      T -m@      T -m@      T -m@      T -m@       -q^B  -r^  -r@  -r  -r( -m۶m۱@  ( - u@@    - P  - T - u@      T -u@      T -u@      T -u@      T -u@       -y^C  -z  -zX  -z  -z( -@@  ( - }X@    - P  - :"T - }@      T -}@      T -}@      T -}@      T -}@       -^D -6 -d - -(-I$I@  (- d@   - P - .T- @      T-@      T-@      T-@      T-@       -^E - -(# - -(-%I$I"@  (- @   - X - 1T- @      T-@      T-@      T-@      T-@       -i^A- i^Projected Cover - days(- H.!-@  (-   (-  (-  (-  (-   -q^B - P(- C?  (-   (-  (-  (-  (-   -y^C - P(- /$!@  (-   (-  (-  (-  (-  -'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 -veY-'quantity divided by the speed for that product, looked up in the horizontal table -'stop of the previous job-'start formatted as timeJ-'start plus idle hours plus run hours, divided by 24 to get to days-'stop formatted as time-'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 forecast4-'lowest inventory cover at the end of the jobF-'find the offset of the product with the lowest inventory cover!- 'for the product, get the:M-''this formula has been created by substituting together the following:/-)'maximum inventory minus inventory levelY-*'sales forecast in units per week, divided by 168 (7x24), to get to units per hourK-+'speed of building inventory, rate of production minus rate of sales@-,'time it will take to build up the replenishment quantity8--'units that will be sold while the job is running@-.'Inventory replenishment qty plus sales during production -^D - P(- %+)@  (-   (-  (-  (-  (-   -^E - X(- -JX̦#@  (-   (-  (-  (-  (-  - a'Lowest Cover"- S"-S"-S"-S"-S- a'Offset6- "6-"6-"6-"6-"- 'product code-!'inventory level -"'speed-#'forecast sales per week'-$'minimum inventory cover in days"-%'minimum inventory in units"-&'maximum inventory in units- a'Next Prod1-  b1- b1- b1- b1- b- a'Inventory1-  b1- b1- b1- b1- b - a'Speed1-  b1- b1- b1- b1- b- a'For- cast1-  b1- b1- b1- b1- b- a'Min Cover1-  b1- b1- b1- b1- b- a'Min units1-  b1- b1- b1- b1- b- a'Max units1-  b 1- b 1- b 1- b 1- b - a'To makeV- B      V-B      V-B      V-B      V-B      -. Section 43$u<7~7,с::::::  : : : : :::::: 9 *******6 * 1 * * *********#.'Inventory Cover Calculationt.'In Section 40 the sales forecast is expressed as a straight line, e.g.. 100 units per week. Calculating the r.'inventory cover is a simple division, e.g.. an inventory of 650 will last for 6.5 weeks. However, a sales n.'forecast may be non-linear, to reflect seasonal sales demand, sales promotions, a new product with an u.'anticipated increase in demand, or a product at the end of it's life cycle. Calculating the cover with a non-q.'linear forecast, is more complex. Here is a sales forecast for 20 weeks which falls and then rises again: .'Weeks. 'Sales forecast:. 'Cum forecast:/.'Here is an inventory cover calculation:.'Inventory:2.'the week before the inventory will run out;.'cumulative forecast at the end of the previous week'.'inventory left in the last week".'forecast for the last week8.'portion of the week that the inventory will last2.'cover - weeks that the inventory will lastm.'What if the inventory cover is greater then 20 weeks? We could assume that the forecast for the 20th o.'week will continue in a straight line at 72 per week thereafter. Here are some additional steps to cope.'with that:.'Inventory:.'last week.'last forecast quantity.'last cum forecast!. 'cover after the last week.!'total cover-."'test to see if this should be appliedj.$'Now lets consider a forecast that drops away to zero, and inventory which will last for infinity. u.%'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..('Sales forecast:.*'Cum forecast:.,'Inventory:.-'last week..'last forecast quantity./'last cum forecast!.0'cover after the last week.1'total coverM.3'The whole lot can be substituted together into 2 formulas as follows:2.5'the week before the inventory will run out.6'total covere.8'@IF(I17>@MAX(C13..Z13),@IF(@INDEX(B11..Z11,@MAX(C9..Z9),0),@MAX(C9..Z9)+(I17-@MAX(C13..Z13))/e.9'@INDEX(B11..Z11,@MAX(C9..Z9),0),999),I54+(I17-@INDEX(B13..Z13,I54,0))/@INDEX(B11..Z11,I54,0)) .  .* . . P$. T@  .(P$.*T@  . . L$. c@  .(K$.*`c@  . . H$. l@  .(F$.* l@  . . D$. r@  .(A$.* r@  . . @$. v@  .(<$.*u@  . . <$. @z@  .(7$.*Py@  . . 8$. }@  .-.@ 1.}@ b$.8@ 1.J@ b$.؉؉? $.؉@  .".4@T1.R@ b".0@T-. 88'@  $.!qq?@ $."? .(2$.*p|@  .,".-4@T1.. b"./@@T-.0  /.18@  ;-.5@ .6n۶m۶@PTTbTT Tb ;b b ;  .  . 4$ . @   .(-$ .*@@   .  . 0$ . @  .'@MATCH(I17,B13..Z13) .'@INDEX(B13..Z13,I18,0) .'+I17-I19 .'@INDEX(C11..Z11,I18,0) .'+I20/I21 .'+I18+I22 .'@MAX(C9..Z9) .'@INDEX(B11..Z11,I30,0) .'@MAX(C13..Z13) . '(I29-I32)/I31 .!'+I30+I33 ."'+I29>I32  .(($ .*@  .1'@IF(I47,I46+I49,999)  .  . ,$ . `@   .(#$ .*@   .  . ($ . @   .($ .*@   .  . ($ . @   .($ .*@  . . ,$. @@  .($.*P@  . . 0$. @  .($.*Ȅ@  . . 4$. `@  .( $.*@  . . 8$.  @  .($.*@@  . . <$. @  .($.*@@  . . @$. @  .($.*@@  . . D$. @  .($.*@@  . . H$. 0@  .($.*@@ .Md     Oh+'0 `lx CorjoannemConn\Ajoannem1@@Quattro Pro for Windows 7 !"#9$%&'()*+,-. figuraGroup14Group1C:\ScheM