Production-Scheduling.
com
Thank you for downloading this free tutorial, I hope it will be of use to you.
Take a look at the model system at:
[Link]
a Flash presentation that runs for 30 minutes
Introduction
For years spreadsheets have been used to design and prototype scheduling systems.
They have now grown up, and are being used to develop serious production scheduling
applications.
Who Should Use This Tutorial
This tutorial is aimed at spreadsheet literate people who are involved in planning and scheduling production
activities. The techniques and formulas set out in this tutorial are being used by manufacturing companies
daily; this is a practical, not an academic, exercise.
Background to Scheduling With a Spreadsheet
We have been designing, building and implementing production scheduling systems for manufacturing
companies since 1990. When PC's and spreadsheets were less capable than they are now, we used
spreadsheets to design and prototype scheduling algorithms, and to train on some of the principles of
scheduling. Prototype designs were then handed over to software developers to write in more resilient and
efficient programming languages.
Often the pressure was on to throw several thousand records of data at the prototypes and use them for live
scheduling, before handing them over to the software developers. So, in order to 'shoehorn' a big
scheduling task into a small PC, we recorded macro's that wrote a formula, copied it down, overwrote the
cells with values, then moved on to the next column, so that no memory consuming live formulas were left
behind. Typically, most of the macro code prepared downloaded data for scheduling, and generated reports
from the schedule, with only a small portion of the macro calculating the schedule itself. We ended up with
big cumbersome macro driven scheduling systems that ordinary, spreadsheet literate people were locked
out of.
Thankfully we now have powerful computers which allow us to apply formulas to large amounts of data, and
we have features such as Excel's PivotTable which will re-arrange and summarise data for scheduling, and
prepare reports without resorting to writing macros. It makes the job, of building a scheduling system with a
spreadsheet, a whole lot easier, and within the capability of the average speadsheet user.
Build Your Own System
Our offering to our clients has now changed, and we can now give them the option of either building a
scheduling system for them, or teaching their staff to build one for themselves, and providing guidence
while they do it.
Interface to Your ERP System
Our clients include companies such as Shell, Toyota and Unilever, as well as many smaller manufacturing
concerns. They all have ERP or older MRPII systems, and have felt that the functionality of their systems
need to be extended to give them the kind of responsive scheduling they are looking for. Spreadsheet
based scheduling applications have been interfaced to SAP, Baan and many other ERP systems.
Structured and Disciplined Approach
Spreadsheets have earned themselves a bad reputation amongst software purists, because they can, and
often are, used in an unstructured way. Building a scheduling system requires a structured and disciplined
approach. Please resist the trap, that many fall into, by creating a table on a single worksheet that looks like
the report that you want to see. The approach used here is to create lists in the form of databases, with a
heading at the top of each column, and with universal formulas that can be copied and pasted down a
column, and work on every row. If all the calculations are done in a structured database, then reports, with
sub-totals and charts, can easily be created with a PivotTable.
E-mail Us:
Production-Scheduling@[Link]
Web Site
[Link]