In 2015, before Planitools existed, our founder joined a 200-person manufacturer serving the agricultural sector, with plastics, metalworking and galvanising under one roof. It managed several thousand items, counting finished products, semi-finished products, raw materials and bought-in components, and its worst bills of materials ran ten levels deep. The requirements calculation in place worked one finished product and one manufacturing order at a time.
This article explains why that kind of calculation cannot work as soon as components are shared and the plan stretches over several weeks, how a real net requirements calculation (MRP run) handles multi-level bills of materials, and why, years later, we published a free workbook that does exactly that.
The starting point: one finished product, one order, one calculation
The tool in place answered a single question: for this manufacturing order, of this finished product, do we have the components? It exploded the bill of materials of that product, compared each line with the stock, and listed what was missing. For an isolated order, the answer looked right. For running a plant, it was not.
The company is the one we describe in our article on demand forecasting for SME manufacturers. Stock was managed by eye and an ERP project had been stopped at the first dip in revenue, but the foundations were good: items correctly coded and bills of materials in order. The net requirements calculator was the fifth step of the action plan, and the hardest.
Why calculating order by order gives the wrong answer
A shared component is checked against the same stock several times
Take a steel bracket used in two finished products, A and B (fictitious figures). There are 100 in stock. The order for A needs 80, the order for B needs 70. Checked one at a time, each order looks fine: 80 is less than 100, and so is 70. Neither calculation sees that the two orders together need 150. The shortage of 50 appears on the day the second order reaches assembly.
With ten levels of bills of materials, the effect multiplies: a component can sit in a sub-assembly, itself used in several semi-finished products, each used in many finished products. The requirement for that component is the sum of everything above it, and no calculation that looks at one finished product at a time can produce that sum.
Without time, no master production schedule
The second limit is time. An order-by-order calculation answers “is there enough today?”. It cannot answer “what will be missing in six weeks if we follow the master production schedule (MPS)?”, because it knows neither the dates of future requirements nor supplier lead times. Yet that is the only question that matters for a part with a long lead time: by the time the manufacturing order exists, it is too late to buy.
In that plant, the consequence was very concrete: customer deliveries pushed back at the last minute, because parts were missing and their replenishment took weeks. The sales director could not understand how such mistakes could happen. Without a tool, they are almost inevitable: nobody can add up by hand, every week, the requirements of several thousand items through ten levels of bills of materials.
How a real net requirements calculation works
A net requirements calculation (MRP run) works the other way round: it starts from the whole MPS, item by item and period by period, and works down the bills of materials level by level.
- Each item is calculated once, at the right moment. Every item gets a low-level code: the deepest level at which it appears in any bill of materials. The run processes all finished products, then all items of level 1, then level 2, and so on. A component found at level 2 in one product and at level 7 in another is calculated at level 7, once all its parents are known.
- Gross requirements add up. For each period, an item's gross requirement is its own MPS plus the dependent demand from all its parents: the parents' planned orders multiplied by the quantity per unit, plus scrap.
- Stock is netted only once. Projected on-hand runs period after period: opening stock, plus scheduled receipts (open purchase and manufacturing orders), minus gross requirements. When it falls below the safety stock, the shortfall is a net requirement.
- Lot sizes are applied. The net requirement is rounded up to the minimum order quantity and the order multiple: that is the planned order receipt.
- The lead time sets the release date. The planned order is released earlier by the item's lead time. If that date has already passed, the order is flagged as late: exactly the alert that was missing.
Here is the bracket again, this time with dates: 100 in stock, 80 needed in week 10 for product A, 70 in week 14 for product B, and a supplier lead time of six weeks.
| Steel bracket | Week 8 | Week 9 | Week 10 | Week 11 | Week 12 | Week 13 | Week 14 |
|---|---|---|---|---|---|---|---|
| Gross requirements | 0 | 0 | 80 | 0 | 0 | 0 | 70 |
| Projected on-hand | 100 | 100 | 20 | 20 | 20 | 20 | 0 |
| Net requirements | 0 | 0 | 0 | 0 | 0 | 0 | 50 |
| Planned order receipts | 0 | 0 | 0 | 0 | 0 | 0 | 50 |
| Planned order releases | 50 | 0 | 0 | 0 | 0 | 0 | 0 |
The run says in week 8 what the order-by-order method would only have revealed in week 14: order 50 brackets now. Six weeks later, the customer delivery holds.
The parts that hurt most: long lead times and slow movers
Fast-moving parts forgive a lot: they are reordered often, so a mistake is soon corrected. The late deliveries come from the others: a part with a lead time of several months, a component consumed a few times a year in a single product. These are precisely the items that disappear from view in an order-by-order calculation, and those for which a time-phased MRP run makes the biggest difference. Once they are under control, you sleep better at night.
What we built then, and what we have built since
In 2015, the calculator was built alongside responsibilities on the shop floor, without professional development skills and without the time it deserved. It existed, it helped, but it remained fragile. Since founding Planitools, software development has become our profession, and we have taken the time to do this calculation properly.
The result is our free MRP Excel workbook. You paste your MPS (items in rows, periods in columns) and your bills of materials (parent, component, quantity), you click, and the workbook writes back the orders to release, make or buy, with the quantity, the release date, the due date and the product that created each requirement. It handles as many levels as your bills of materials have, shared components, lead times, safety stock, minimums and multiples. The calculation runs on our server, so the workbook stays light, and it stays on your computer.
Lessons for any SME with deep bills of materials
- Calculate by item, not by order. A requirement that is not added up across all products is not a requirement.
- Put the MPS on a timeline. Without dates and lead times, shortages are only discovered once they have happened.
- Look after the master data. Bills of materials, lead times and stock: the calculation is only as reliable as they are.
- Keep the planner in charge. The run proposes; the planner checks, adjusts and releases.
- Do not blame people for a tooling problem. The mistakes that puzzled the sales director came from a calculation nobody can do by hand, not from carelessness.
Conclusion: from the shortage you suffer to the order you anticipate
Calculating requirements one finished product and one order at a time works as long as nothing is shared and nothing has a long lead time. In a plant with thousands of items and bills of materials ten levels deep, it hides shortages until the day they stop the line. A time-phased MRP run, item by item and level by level, brings them forward to the day they can still be avoided.
To try it on your own data, get the free MRP Excel workbook. And when the plan needs several users, a daily feed from the ERP and your own rules, Planiprod takes over: MPS, MRP and load per work centre, with every column programmable and explained. To see it on your data, book a demo.