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 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.
ok
ReplyDelete