1. Sales Forecast

Make-to-inventory companies need a sales forecast for each finished product. Some companies refer to a product as a “stock keeping unit” (SKU). The system will not create a forecast, but merely stores it or imports the data from a host system. At the simplest level it will be an average weekly or monthly figure for each product. A more sophisticated forecast will have a separate figure for each week or month, and will reflect products with seasonal demand, new products with increasing demand, and products at the end of their life cycle, with declining demand.

Some companies simply use sales history as a forecast, on the assumption that what was sold in the past, is what will be sold in the future. There are many statistical functions, within Excel, that may be used to read sales history and generate a sales forecast.

2. Forecast Accuracy Measurement

Forecast accuracy, otherwise known as “demand predictability”, makes a critical contribution to good supply chain management, so it should be measured. Making product managers, or account managers, responsible for the accuracy of the forecasts they make, and then monitoring their performance, is a very effective tool for improving the accuracy of the forecasts. A forecast for a product for a week or month, will be revised several times before the week or month arrives. Forecasts made several months in advance may be used to purchase long lead-time materials. Every version of the forecast is written away into a database, together with the actual demand, and used to generate several forecast accuracy reports. It is important to record actual demand (orders) rather than invoiced sales, as demand, which is not fulfilled in the period (lost sales), because of stock-outs, for example, is not reflected in invoiced sales.

3. Demand Calendar

This lists the public holidays, and is used to determine how many days of forecast demand there are in each month. (not to be confused with the production calendars – see below)

4. Demand Profile

For some companies demand, for their products, is higher during certain times of the month, for example “40% of our sales occur in the last week of the month”. If the forecast is in months, this worksheet is used to describe how the forecast will be spread throughout the month.

5. Splitting Forecast into Weeks

Typically, companies maintain their sales forecasts in months. However, a month is not a good time bucket for planning, as each month varies in length. This worksheet breaks down each month into weeks, and considers that:

· There is no demand on week ends and public holidays

· Some weeks start in one month and end in the next

· The demand profile (above)

Companies who have adopted LEAN manufacturing, may express demand in time buckets of 1 day or 1 hour.

6. Sales Order Records

A list of outstanding orders placed by customers. Typically this data will reside in a host system, and will be downloaded into Excel via an ODBC driver. Minimum requirements are Product, Quantity, and Due Date.

7. Starting Point

Planning and scheduling starts now, and looks forward into the future, so the system needs to recognise the date and time that is the starting point of the plan. The user may be key the starting point into a cell in Excel, or it could be the system time of the PC running the system, or it could be the date that the inventory balances were read from the host system.

8. Forecast Consumption Rules

The system may modify the sales forecast in light of actual sales or outstanding sales orders. Rules are set to tell the system how to respond, as illustrated by the following examples:

Consider example 1:

You forecast to sell 1000 of a particular product in the current month. It is the 15th of the month and you have already sold 800. The alternative ways of calculating the remaining forecast for the month are:

a. If sales continue at this rate, we will sell another 800 in the second half of the month. OR

b. Sales are always higher at the beginning of the month, so we will sell another 200 this month. OR

c. One customer buys this product, once a month. He has already taken this month’s supply, so we will not sell any more

Now, example 2:

You forecast to sell 1000 of a particular product next month, and you have sales orders for 800 due for next month. The alternative ways of calculating the forecast for next month are:

a. Customers order this product in advance, so our forecast is now 800. OR

b. Customers don’t generally order in advance, so we will sell the 1000 plus the 800 on order, the new forecast is 1800. OR

c. The forecast is still 1000, of which 800 are already ordered.

9. Inventory Records

Inventory transactions, i.e. receipts and issues, or production and sales, can be maintained in Excel, and a balance on hand calculated, but Excel is not ideal for transaction processing. Most Excel based supply chain planning and scheduling systems rely on an external, “host”, system to keep the inventory records, and allow Excel to import the balance on hand information via an ODBC driver. Typically, this will include:

· Finished products held centrally

· Finished products at regional depots

· Finished products at customers’ premises

· Finished products in transit to depots or to customers

· Manufactured sub-assemblies in a store

· Finished products and sub-assemblies in progress of manufacture

· Raw materials and purchased components

10. Inventory Cover

If you sell from inventory, then it is important to know how long the inventory will last, in other words, how much of the forecast demand is “covered” by the inventory. A simple way of doing the calculation is to use average demand, for example “inventory is 500, and on average we sell 200 a week, so the cover is 2.5 weeks”. A more sophisticated way of doing the calculations is to “count off” the forecast, for example, ” for the next 3 weeks the forecast is 300, 100, 500, so the inventory of 500 will last for the first two weeks, and 0.2 if the third week, so the cover is 2.2 weeks. If a product that has zero forecast for the 3 weeks and 200 in the third week, inventory of 2 will have a cover of 3.01 weeks. Ironically, inventory of zero will translate into 3 weeks cover, however, this is correct, “zero is enough to last 3 weeks”.

11. Inventory Policy

The inventory policy for a product, or a group of products, may be expressed in terms of cover, for example “when we get down to 2 weeks cover, we will replenish inventory up to 6 weeks cover, to the nearest whole box of 50”. The data will be stored as “Min 2, Max 6, Lot size 50”

The minimum inventory level, or safety level, is there to allow for unpredictability of demand, and for the time it takes to replenish inventory. The difference between min. and max., allows for an “efficient” production run. Manufacturers that learn to forecast demand accurately, have quick change-overs, short production runs, and respond quickly, can afford to have low inventory levels with which to service their customers.

The minimum inventory level is not the same as the “re-order point” (ROP) used in some systems, as the ROP allows for a safety level AND a replenishment lead time.

Many MRP/ERP systems store minimum and maximum inventory levels as units rather than cover. These require the user to manually modify the levels as the forecast demand changes. Companies that do not maintain a sales forecast, must have some idea how much they are going to sell when they set min/max levels in units.

12. TOC Buffer Management

Theory of Constraints buffer management (Drum-Buffer-Rope or DBR) requires min/max levels (buffers) to be expressed in units. The technique requires “buffer re-sizing” to be carried out periodically. This involves monitoring the number of times, and the extent to which the minimum inventory has, in the past, been penetrated, and adjusts the buffer accordingly. Strictly speaking DBR is an operational system rather than a planning system, in other words it is concerned with the decision of what to make now, rather than what to make in the future. Often DBR uses simple visible signals rather than a computer system, for example “when you can see that there are less than 3 boxes remaining, it is time to make some more, until there are 5 boxes, then you stop”.

13. Inventory Cover Reports

A report on the current status of the inventory of finished products, lists the products from those that are furthest below their minimum, to those that are furthest above their maximum policy level. Using Excel’s conditional formatting, the products below minimum are coloured red, those between minimum and maximum are yellow, and those above maximum are green. The aim of the person managing the inventory, is to have all products displayed in yellow.

In addition, dollar figures are calculated. The dollars worth of inventory that would need to be added, to bring everything up to minimum levels, and the dollars worth that would need to be taken out, to bring everything down to its maximum. These two figures are recorded each week or month, and plotted on a chart, to indicate if the management of inventory is getting better or worse over time.

14. Make-to-Order vs. Make-to-Inventory

When manufacturing companies are asked which of these two categories they fit into, many reply “both”. They will maintain a sales forecast for popular products, and make them to inventory, but certain obscure products they will only make when they receive an order. A mix of make-to-order and make-to-inventory can be catered for by setting the forecast consumption rules and the stock policies correctly, i.e. a make-to-order product will have a minimum inventory level of zero.

15. Distribution Plan

Companies that hold inventories at regional depots or retail outlets, maintain a sales forecast for each product through each depot, and replenish the inventory from a central distribution centre. Each depot will have an inventory policy, and the system will recommend replenishments, from the distribution centre, of products that are below their minimum at the depot. The system takes into consideration:

· Products are shipped from the distribution centre, and not from one depot to another

· If there is not enough inventory of a product, at the distribution centre, to replenish each depot to it’s maximum level, an “inventory rationing” algorithm distributes the inventory across the depots so as to give each depot equal cover.

· The need to replenish a depot is triggered by one of the products at the depot dropping below its minimum. However, the system can make up a full truck load by topping other products up to their maximum level, starting with the lowest cover product first.

The world class trend is to keep the depot inventory low, maintain the buffer inventory at the distribution centre, where the forecast demand can be aggregated, and replenish the depots quickly. Hence the need for a responsive distribution plan.

16. Projected Distribution

The distribution plan recommends what should be sent to the depots immediately. The system will also project what needs to be sent to the depots in the future. This information forms a base on which trucking companies can be given information about forecast transport requirements.

17. Master Production Scheduling (MPS)

This is the tool for managing the buffer of inventory between the manufacturer and the customer. It takes the forecast demand for each finished product, the opening inventory, the inventory policy, and calculates what needs to be produced to keep the inventory within the policy. It tracks the projected (future) inventory level of each product at the end of each week (or other time bucket), for the horizon of the forecast.

The same logic can also be used to manage a buffer of semi-finished products between two parts of the manufacturing process. See the example in 36 below.

18. Products Required

The MPS calculations generate a list of products required, either manufactured or purchased, both now and in the future. For any products that are currently below the minimum, the system recommends that they be produced immediately, and the system ranks them by lowest cover first. For future production runs, it calculates not only which week the product should be produced, but also the date and time within the week that the inventory would have reached its minimum, and would need to be replenished. This way the MPS generates a requirement to produce each product, each with a different due date and time. As no two production runs have the same degree of urgency, it is easier to prioritise them for scheduling.

19. Ideal vs. Achievable Inventory

When the MPS generates the required production, it assumes, initially, that there is capacity to manufacture the products at the times they are required, to keep the inventory at the “ideal” policy levels. The finite scheduling module (see below) will “shuffle” the required production to fit it into the available capacity, and may elect to schedule production earlier or later than the required time. The “achievable” production is passed back to the MPS module, which then calculates the “achievable” projected inventory. From this data, some useful reports may be generated, such as warnings of future stock-outs (zero inventory), because of a shortage of capacity.

20. Projected Inventory Value

From the ideal and achievable inventory, the total inventory value is calculated and plotted on a chart. From this the following questions can be answered:

· Can we afford our inventory policy?

· How much will inventory value change to support changes in forecast sales?

· How far out of line with our policy are we now? (see also 13 above)

21. Vendor Managed Inventory

Once a manufacturer or distributor has become proficient at managing their inventory, they often have an opportunity to approach their customers, and offer to manage their customers’ inventory of their products, for them. The customer’s inventory and sales forecast need to be visible, and can often be got across the Internet, but the management techniques are the same as managing your own inventory. The customer no longer places sales orders on you, and you change from being a make-to-order to being a make-to-inventory manufacturer. Dramatic reductions in inventory, and improvements to customer service have been achieved with vendor managed inventory.


22. Evaluating Capacity

If you can say, “I sell widgets, and I have a work centre/machine/production line that makes widgets at 100 per hour”, then evaluating your capacity is very easy, and you don’t really need a computer to help you do it. However, the complexity comes in when:

· Products are assemblies, which are made from manufactured sub-assemblies

· Products and/or sub-assemblies go through more than one work centre

23. Bill of Materials (BOM)

A BOM, otherwise known as a product structure, describes the parts that a product is made from, by specifying the relationship between products, sub-assemblies and raw materials/components. They are often displayed as diagrams or as indented BOM reports, but in the system, as in most MRP systems, a BOM is stored in a table with just 3 columns:

· Parent

· Child

· Quantity per

A single or multi-level BOM may be expressed in such a table. A parent product with 3 components will appear as 3 records in the table. When a child appears again in the table as a parent, with children of its own, then it is a multi-level BOM. Parents that are not also children, are finished products. Children that are not also parents, are raw materials or purchased components. BOMs are typically several thousand records long, and are often downloaded from a host MRP system via an ODBC driver.

24. MRP1 Explosion

The required production is exploded through the BOM to identify the sub-assemblies that need to be made, and the raw materials and components that need to be purchased. If the BOM is a single level, just finished products, and raw materials/components, then this is done with some simple Excel techniques. Some companies have product structures that allow each level of the BOM to be specified in a separate table, in which case a simple technique can be used to explode through each level of the BOM in turn. More complex product structures, where the same sub-assembly or component appears at different levels in the BOM, require an MRP module, written in Visual Basic.

25. Netting-off Inventory

The MRP explosion identifies the sub-assemblies that need to be made, and the raw materials and components that need to be purchased. However, some of these may already have been made or purchased, and be in inventory, so they do not need to be made or purchased again. The system works from the earliest production run to the latest, and from the highest level in the BOM to the lowest. At each stage it reduces the quantity to be made or purchased by the quantity in inventory, and keeps a note of the inventory remaining for the next requirement. At the end of the process, there is a list of the net quantities to be made or purchased, “things to make and buy”, and any inventory that will remain after the last production run.

26. Dynamic Re-allocation of Inventory

Some traditional MRP packages “hard allocate” inventory to production runs. A production run will be given a “works order”, or “manufacturing order” number, and inventory will be allocated to these numbers. With the Excel system, inventory will be dynamically re-allocated to the earliest production run first, as priorities change.

27. Netting-off Work in Progress (WIP)

Some sub-assemblies and components may have been issued to the factory floor, with the intention of using them to make a particular product or sub assembly, in fact, at the time of the MRP explosion, they may already be in the process of being assembled. The system needs to understand that these items exist, and do not need to be made or purchased again, but they will not be dynamically re-allocated to another product. WIP is recorded in the system as the item, the parent it is to be used for, and the quantity in WIP.

28. Pegging

The MRP explosion generates a worksheet of details of the explosion and allocation process, so that each item, that is to be made or purchased, can be traced back to it’s finished product. This data is used by the finite scheduling module (see below), and used to establish dependencies. Some MRP packages do an MRP explosion, but do not have pegging, so the Excel MRP is run to generate this data.


29. Purchase Order Records

To manage purchasing, “what to buy”, the system needs to see a record of outstanding purchase orders for raw materials and purchased components. A table, with a minimum of 3 columns, item, quantity, date due in, is required, and it is best to import this data from a host system via an ODBC driver. Although not ideal, records can be kept in Excel, of purchase orders placed, and goods received against them, and outstanding purchase orders derived from these records.

30. Purchasing

The MRP1 run generates a list of “what to buy”, after netting-off inventory and WIP. It really means, “what needs to be received from suppliers”, and is a list with a minimum of 3 columns, item, quantity required, and date required. This list is compared with the outstanding purchase orders, and a “Purchase Action Report” is generated, which highlights, for each item, when you will run short of material unless some action is taken. If you know the purchase lead time of each item, you can do one of 4 things:

· Place an order

· Chase (expedite or pull in) an order

· Defer (delay or push out) an order

· Cancel an order

Each time the system is run, there may be changes caused by, for example:

· New sales orders

· Sales above the forecast levels reducing inventory of finished products

· Changes to the forecast

· Changes to the inventory policy

· Changes to the BOM

· Materials being scrapped in production

· Materials from a supplier being rejected on inspection

· Suppliers delivering the wrong items

· Inaccurate inventory records being corrected at a cycle count

31. Purchase Exception Reports

We have clients who re-run the scheduling system 4 times a day, and it takes only 40 seconds to do so. They don’t want to spend a lot of time combing through the Purchase Action Report each time, looking for the results of any changes, so there are exception reports that alert the buyer to anything that requires one of the 4 actions, place, chase, defer, or cancel. To work well, it requires some intelligent parameters to be applied such as: “defer an order only if it is going to be more than 3 weeks early”.


32. Routing

Suppose one of the components of a product is a metal part that is pressed, then drilled, then de-burred. Some systems would describe this as 3 different items, each at a different level of the BOM, but we would describe this in a routing table. The item would appear as three records in the table, under 4 columns: item, operation number (1,2,or3), operation (press, drill, de-burr), rate (e.g. 120 per hour, or 30 seconds each). Purchased items only have one record in the routing table, “operation 1 – purchase”.

33. Work Centres

A list of machines, or groups of machines, that relate to the operations set out in the routing table, for example presses, drills, de-burring machines etc. If there are 3 presses and 2 drills, this would be listed in the table. In manufacturing operations that are constrained by labour, rather than machine capacity, the work centres may be people, not machines.

34. Routing Explosion – MRP2

MRP1 means “materials requirements planning”. Once we consider capacity, it is considered to be MRP2, “manufacturing resource planning”. The MRP1 explosion generates a list of “things to make and buy”. The routing explosion explodes this out further into individual operations, “things to do”. If a manufacturer has a multi-level product structure, and a multi-stage routing, and say, 200 products, then typically there may be 500 production runs scheduled over the next 3 months, 4000 things to buy, 2000 things to make, and, say, 7000 production activities or “things to do”.

35. Rule Based BOM & Routing

Suppose you manufacture steel drums, in several different gauges of steel, different heights and diameters, fitted with different filler caps, and you paint them different colours. You can see that potentially, you could produce many thousands of different products. One of your product codes may be 600x400x0.3/45LB, which means a drum 600mm high, 400mm diameter, made from 0.3mm thick steel, fitted with a 45mm diameter filler cap, and painted light blue.

Each drum consists of a body, two ends, a filler cap and some paint. The first level of the BOM would have 4 records per product. If you were given the task of creating the BOM for, say, 1200 products, you would soon realise that it could be done with a formula. Excel could split each product code into its 5 parts, height, width, gauge, filler cap and colour, by using the =MID function, and applying formulas to calculate the dimensions of the body and the ends. The item number of the body may be 600x1262x0.3. Paint coverage may be 15 square metres per litre, so you can see that the first level of the BOM could easily be derived. The second level of the BOM would specify the width of coiled sheet steel that would be used for each component.

If the system had some additional rules such as “up to 250mm diameter ends are stamped on the 50 ton press, up to 450mm diameter on the 100 ton press, and over 450mm are flame cut” and “guillotine 1 can cut up to 1200 mm in length, guillotine 2 is used up to 2300 mm”, you can see that the routing can also be derived with rules and formulas.

Traditional MRP systems require the BOM and routing tables to be built the hard way, by hand, but one of the significant advantages of an Excel system, for some companies, is that rule based BOM’s and routings can be developed, and dramatically reduce implementation times.

36. Buffer of Semi-Finished Products

Continuing with the drum manufacturer example. Steel drums take up a lot of storage space (they are canned air), drums of different heights, but the same diameter, would use the same ends, and drums are painted a variety of colours. For these reasons, it is likely that you would keep a very small buffer inventory of finished drums, and a larger buffer of stamped ends and guillotined bodies. This would enable you to assemble and paint drums in response to changes in the finished buffer, or to customer orders. This buffer of semi-finished products would have an inventory policy, and would be managed with the same MPS logic as described in 17 above. The manufacturing process would be managed as if it were two separate businesses, one that assembled and painted drums, and bought bodies and ends from another business.

37. Capacity Planning Charts

Finite scheduling (see below) calculates the start and stop time of each production activity, and involves some complex calculations. Before going into this detail, it is wise to check if the work load is roughly within the capacity to do it. Charts generated from the routing explosion would show information such as “in the week of July 19 there is 53 hours, per machine, of drilling work to do”. This could lead to a response such as “we should plan to work overtime during that week” or “there is only 26 hours of work to do the week before, so we should arrange to do some of next week’s drilling in this week”.

38. Capacity Time Buckets

The capacity plan could work with a number of time buckets such as, hours, shifts, days, weeks, months, quarters etc. Alternatively it could use a “log scale”, such as: “days for the next 1 to 2 weeks, weeks for the next 2 to 3 months, and months thereafter”

39. Time Standards

You can see that, in order to do capacity planning, you need to have some idea of how long future production activities are going to take, especially if the capacity plan indicates that the work centre is heavily loaded, and is likely to be the constraint for a period of time. The time standards may be recorded in the routing table, or the work centre table, or may be linked to an attribute of the item, such as length. For repetitive manufacturers, they have the opportunity to measure how long it took last time.

40. Change Over or Set Up Time

Time standards for set ups can be very significant, as in the printing industry, for example. Generally it is a good idea to record set up standards separately from running time standards, because you then have the ability to change the run lengths according to the inventory policy (see 11 above), and evaluate the effect that has upon capacity loading and projected inventory value. Set up times may be influenced by the sequence in which items are run through a work centre, and this can be evaluated with a set up matrix (see 54 below).

41. Lead Time Offsets

The capacity plan timings are driven by the due dates generated by the MPS, and/or by sales order due dates. However, to get a more realistic view of capacity loading, you may wish to apply assumptions such as “de-burring happens 4 days before the due date, drilling 2 days before that, and pressing 3 days before drilling”. Also, to get realistic timing for purchasing, you may wish to say “I need to receive the raw materials 3 weeks before the due date”. These parameters may be stored in the work centre table.

42. Closed Loop MRP

Much of the functionality that has been described so far, will be found in MRP and ERP packages, and is known as “closed loop MRP”. A production plan is presented to the system and tested using MRP1, to see if there will be materials to support the plan. If it is not possible to get materials in on time, the timing and quantities on the production plan are adjusted. It is then tested again, using capacity planning, to see if there is capacity to meet the plan, and if there isn’t, again the plan is adjusted. Adjusting the plan to fit available capacity may involve making some production runs earlier, which may mean getting materials in earlier, and testing the plan again with MRP1, which may require further adjustments, and so on. Hence the term “closed loop MRP”

43. Planning Horizon

Make-to-inventory companies can decide how far ahead it is appropriate for them to look, and with an automated planning system, the horizon can be stretched with very little effort. If there is seasonal demand for some of the products, it may be appropriate to look forward a year. It is certainly wise to look ahead well beyond the lead time of the longest lead time raw material.

Make-to-order companies are ruled, to an extent, by how far ahead their customers place orders on them. If supplier lead times are longer than customer lead times, make-to-order companies often put “forecast sales orders” into the system to purchase long lead time raw materials.

44. Rolling Horizon

When reviewing existing manual systems, we often hear, “once a week we prepare a schedule for a week”. On a Monday the horizon is a week, but on a Thursday it is only 2 days. In contrast, automated Excel based systems use a rolling time horizon, where the horizon is always x days away from now. Also, unlike manual systems, there is a difference between the horizon for calculations and the horizon for reports. For example, we may elect to have a report that looks out for the next 10 days, whilst the underlying data looks out 6 months.


45. Finite Scheduling vs. Capacity Planning

Capacity planning tells you about a capacity problem by saying, for example, “the 50 ton press will be 130% loaded during the week of June 21”. Finite scheduling is so called because it considers your capacity to be finite, It tells you about a problem in a different way, for example, “order 123B for Customer x will be 4 days late”, or “in 10 days from now we will have zero inventory of product 456A, for 2 days”. Perhaps capacity planning is good enough for you, and you can avoid the complexities of finite scheduling, but most manufacturers embark on finite scheduling so they can make better delivery promises to their customers, and better negotiate revised delivery promises as priorities change.

46. Finite Scheduling Logic

Generally, a machine or a person cannot do 2 jobs at the same time, their capacity is finite. So the logic behind the simplest of schedules is, “the next job will start when the previous job finishes”. For example, you start at 8:00 am and your first job will take 3 hours, so the next job will start at 11:00 am. Very simple, isn’t it?

47. Production Calendars

Some manufacturers, such as oil refineries, work 7 days a week and 24 hours a day. Others, that don’t work this way, need to maintain a calendar, which tells the system what the working periods are. The calendar is a list of the dates and times that each working period starts and stops, for example, start June 21 8:00am, stop June 21 10:00am, start June 21 10:10, stop June 21 12:30pm etc. This indicates that you have a 10 minute break in the morning and stop for lunch at 12:30pm. The example in 46 above would now be, you start at 8:00 am and your first job will take 3 hours, so the next job will start at 11:10 am. The calculations of the start time of the next job needs to skip over breaks, evenings, week ends, holidays etc. The calendars are edited to reflect holidays, planned overtime etc. Each work centre may work to a separate calendar, or groups of work centres may share a calendar.

48. Multiple Operations

Suppose we are creating a schedule for a drilling work centre, and we can’t drill holes in the components until they have been stamped. The finite scheduling logic becomes, “the next job will start when the previous job finishes, or when the previous operation finishes, whichever is the latest”. The result is that some work centres may be scheduled to lie idle and wait for material to arrive from previous work centres.

49. Assembly Operations

Suppose we are creating a schedule to assemble together 6 components, which are all manufactured at other work centres. We cannot start the assembly until all 6 of the components have been made. The finite scheduling logic now becomes, “the next job will start when the previous assembly job finishes, or when the last of the previous 6 operations finish, whichever is the latest”.

50. Multi-Machine Work Centre

If, for example, there are 3 drilling machines in the drilling work centre, the system will share the jobs between them by allocating the next job to the next machine that becomes available.

51. Transfer Batches

Suppose we are to assemble 1000 of the products with the 6 components, and we had 1000 each of 5 of the components, but the work centre that is making the 6th component has made 200 so far. We could go ahead and assemble 200 of them, and ask the work centre to transfer the next 100 components to the assembly work centre, when they are made. We would tell the system that the transfer batch is 100, and it would create a schedule that would prevent the assembly work centre from waiting.

52. A Pull Schedule

Looking at the above example, you may well ask the question, “Why did we make 5 of the components before we were ready for them? We have filled our factory with unwanted work-in-progress, and it is contrary to the teachings of Theory of Constraints (TOC) and Lean manufacturing.” The answer is that the scheduling logic we have described so far, is “pushes” work through the factory. The statement, “the next job will start when the previous job finishes”, implies that we are aiming to keep the work centres busy.

We turn the logic around and say, “the previous job will stop when the next job starts”, and also re-write the logic that applies to multiple operations and transfer batches, so that the schedule allows demand to “pull” work through the factory. The “pull” logic starts with the due date for the completion of each finished product, and works back from there. The result is that some work centres may lie idle and wait until the next work centre is ready to accept the material.

A “push” schedule may have products being completed only after their due date, but a “pull” schedule may have jobs that are scheduled to start in the past, for example “two weeks ago”, this is clearly not a “do-able” schedule.

53. 3 Pass Scheduling

This algorithm applies push, then pull, and then a third push set of logic, that creates a do-able schedule that has pull characteristics to keep work-in progress levels low. It will also suggest that you build up inventories in advance of peak seasonal demand, for example, “start making snow shoes 4 months before winter”. Similarly, if you plan to shut a work centre down for maintenance, it will suggest a build up of inventories to last through the shut-down period.

54. Change over or Set up Matrix

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

The relationship between the set up times can be expressed in a matrix, with the 2 axes of the matrix containing the items, or an attribute of the items, such as colour or diameter. The system will look up the set up time for an item depending on the previous item run in the work centre.

55. Re-Sequencing

The default sequence for a schedule is the earliest due date first. However, the user may wish to re-sequence the jobs on the schedule to make like colours or like diameters together, and thus reduce the set up times. This is done by overwriting the sequence number. For example if you want to run job 7 in between job 4 and 5, you would overwrite 7 with 4.5, and re-schedule. If you have a set up matrix, you can get the system to report the total set up time, so you can see the impact that re-sequencing will have on set-ups.

You may also wish to assign a job to an alternative work centre, and see the impact that it has.

56. Remembering the Sequence

Often the jobs, or orders that appear on the schedule are downloaded from a host system that keeps track of outstanding orders. Each time a fresh list of orders is downloaded from the host system, completed orders will disappear, and new orders will appear. It is unlikely that the host system knows the sequence of the orders on the schedule, and with each new download the schedule is completely re-generated. You may wish the system to “remember” the sequence you put the orders in last time, and re-apply the sequence to the fresh list of orders. New orders that the system has not seen before will be assigned sequence number 9999 and appear at the end of the schedule.

You may also want the system to remember alternative work centres as well.

57. Production Tracking

A scheduling system cannot assume that we start each day with an empty factory, so it needs to know the status of work in progress, for example, “of the order of 1000, 780 have been stamped, 530 have been drilled, and 360 have been de-burred”. A host order entry system will keep track of outstanding orders, but often there is a shop floor data capture system in place that keeps track of the detailed movements on the factory floor, and data can be down loaded from that. If no such system is in place, then it is feasible to capture these detailed movements directly into Excel.

58. Work-in-Progress Capture

Manufacturers who do not have any production tracking system in place, sometimes prefer, as an alternative, to count work-in-progress once or twice a day, and capture the results of the count into Excel. In Lean factories there is very little WIP, so this can be done very quickly. The system then derives from these figures, the quantities that have been produced from each work centre, and what has yet to be produced, a process known as “back flushing”.

59. Schedule vs. Actual Production

Having created a production schedule, it is understandable that you would want to monitor actual production against it. However, it is not that easy because a schedule is a dynamic thing, every time you re-schedule, which could be often, it changes. As you get ahead on some items, fall behind on others, and change the priorities, the schedule changes. Also, it doesn’t naturally store any history, it starts now, and looks into the future. You can see that you will be trying to compare actual production with a moving target.

The answer is that you need two copies of the schedule. You would take a “snapshot” of the schedule as it was on, say, Monday morning, keep a copy, and use it to report actual production against. The second copy, the dynamic version of the schedule, changes continuously, and at any point in time, is your best guess of what the future looks like.

60. Schedule Compliance Reporting

Many manufacturing companies implement finite scheduling to make better delivery promises to their customers, but often, the biggest benefit they derive is to have much more meaningful production targets. Many production people tell me, “my target is 1000 units a day”, regardless of whether the customer, or the next work centre needs them or not. Some days there are a lot of short production runs to be done, with many set ups, so it is not possible to make 1000. Other days they will be making only one product all day, and they can make 1800, but the target remains “1000 a day”. Such target setting encourages “cherry picking”, they will find jobs that make their performance look good against the target, rather than the jobs that are needed.

All this changes once production people are rewarded for sticking to the schedule, even if the schedule says “make nothing today”. The system will report, “your schedule compliance last week, or yesterday, was 89%”. It they fall behind and fail to make all the items on the schedule, the compliance % will be low. If they make everything on the schedule, the compliance will be 100%. If they get ahead and make items that are not on the schedule, they do not get rewarded, the compliance is still 100%. If fact, you could argue that they should be discouraged from getting ahead and producing unwanted WIP.

61. Time Fence

One of the main objectives of implementing a scheduling system is to become more responsive to changes in customer demand. If a customer takes more than the forecast, the finished inventory buffer drops, and the production run to replenish the inventory moves higher up the schedule. To be truly responsive, the best time to ask the question, “what shall I make next”, is as you finish the previous production run, because then the answer to the question is based on the freshest information available.

However, it may not be possible to change at the last minute, because there may be other activities that are synchronised to the schedule, such as material supply, or transport. You may wish to have a time fence that is say, 4 days out, which has the effect of “freezing” the schedule for the next 4 days.

62. Customer Service Level

For make-to-inventory companies, one way of measuring how well the supply chain is being managed, is to monitor “inventory availability” or “customer service level”. (see also 13 above). It could be a matter of saying, “on June 17, we had inventory of 96% of our products”. The assumption is that if a customer asks for a product and you can’t supply them immediately, then the relationship with the customer is damaged, or they may go to a competitor. Some ERP systems record “lost sales”, i.e. Dollars of orders that are not fulfilled.

The scheduling system can evaluate projected availability, i.e. what the availability will be in the future, assuming we stick to the schedule, and have100% forecast accuracy.

63. Due Date Performance

Make-to-order companies monitor how well they deliver orders within the due date. They can simply count the % of orders on time, and % late, or consider the value of the orders, and how late they were, using “Dollar days of lateness”. For example, an order with a sales value of $10000 and a raw material cost of $2000 would have a “throughput” (TOC term), or “contribution” of $8000, and if it were 5 days late, it would have 40000 dollar days of lateness. Some companies go further and multiply by a customer weighting factor, to recognise customers that are more important to satisfy. The measure is meaningless in isolation, but when it is plotted on a chart over time, it indicates whether due date performance is getting better or worse.

By measuring the projected lateness content of the schedule in Dollar days, i.e. the extent to which orders are going to be late in the future, you can monitor the effect that changes to the schedule will have on due date performance.

64. Optimisation

Manipulating a schedule effects the following factors:

· Customer service level or due date performance

· Set up time

· Overtime cost

The aim is to get the best balance between these factors. It is feasible to automate the process of finding the best balance, i.e. optimising the schedule, but first “best” needs to be defined in mathematical terms.

65. Grouping Like Products

An effective method of reducing set up time, is to get the system to group together products with a like attribute, diameter, colour, shut height, etc. The user will maintain a parameter that says, “group together products that are scheduled for production within, say, 5 days of each other”. By adjusting the parameter, you can get a balance between due date performance and set up reduction.

66. Manpower Availability

If a production process may be constrained by manpower, then the system can report the manpower required to support the schedule, and allow you to manipulate the schedule to fit within the manpower available. We have also developed custom optimisation algorithms to perform this task automatically.

67. Optimisers

There are software packages, on the market, that will simultaneously consider:

· Machine capacity

· Labour and skills available

· Material availability

They have algorithms that will generate a do-able schedule to maximise due date performance. We have not attempted to do the same thing in Excel.

68. Product Cost

Most scheduling systems contain BOM’s, routings and standard times, and if the raw material costs are also available, there is sufficient data in the system to calculate standard product costs.

69. Financial Projections

Traditional accounting reports are concerned with history rather than the future. A scheduling system can generate projected turnover, gross profit, and even net profit. When the projected figures are merged with the historical accounting figures, on a single report, some powerful management information can be generated.

70. Time Bucket Database

The scheduling calculations use one row per production run, and calculate a start and stop date and time for each one. We may want to generate some reports in daily time buckets, and a production run may span several days. The bucket database splits production runs up into buckets. For example, a production run that starts on one day, runs for the whole of the next day, and ends on the third day, will become 3 rows in the database, with the length of time and the production quantity in each bucket. There are a number of reports that can be generated from the database, including Gantt charts.

71. Gantt Chart

A Gantt is a way of displaying a schedule graphically, it looks very much like the Production-Scheduling.com logo, and lists each operation through each work centre. It is named after a Mr. Gantt who originated the idea. As with 38 above, the time buckets across the top can be variable, with for example, hours for the next two days, and days thereafter.

The coloured cells, may contain:

a. Just a colour, to indicate that work is scheduled for the time bucket

b. The hours scheduled for each job in the time bucket, the first and last bucket are likely to have less than a full bucket of hours.

c. The number of units scheduled to be produced in the time bucket.

72. Single Row Gantt Chart

In some instances it is possible to show all the jobs scheduled for one work centre on a single row, with the details of the job within the “bar” representing the job. This makes for a more condensed report, but quick jobs with long descriptions are difficult to display. A variation is a chart with the time running down columns rather than across rows.