When Planners Stop Trusting MRP, the Plant Runs on Spreadsheets

The Year the Lead Times Broke

If you work in manufacturing planning, 2021 has been the year the assumptions broke.

The Institute for Supply Management's supplier deliveries index reached 78.8 in May, its highest reading since April 1974, meaning deliveries were slowing more broadly than at almost any point in nearly fifty years. Dozens of container ships have been waiting at anchor off Los Angeles and Long Beach, and in October the White House announced that the Port of Los Angeles would move toward round-the-clock operations to clear the backlog. In October, ISM's survey put the average lead time for production materials at 96 days, a record.

Inside most plants, something quieter happened.

Inside most plants, something quieter happened. The MRP run kept producing suggestions based on the lead times in the item master, which were set before any of this happened. Planners looked at those suggestions, knew they were wrong, and stopped using them. The planning did not stop. It moved into spreadsheets.

How a Spreadsheet Takes Over

It usually starts reasonably. A planner exports open orders and on-hand balances, adjusts lead times by hand for the parts they know are late, and builds a view they can trust. It works, so they keep it. A second planner builds their own. Purchasing builds a third for expediting. Within a few months, the plant is being run on several spreadsheets that disagree with each other and with the ERP, each one maintained by a single person.

This is not a failure of discipline. It is a rational response to a system that is giving bad answers. But it has costs that compound. The spreadsheets are only as current as the last export. They live on one desktop, so when that person is out sick or leaves, the plan leaves with them. And because the ERP is no longer trusted, nobody fixes the data in it, so it gets worse, and the case for the spreadsheet gets stronger.

MRP Is Only as Good as Its Parameters

MRP is not a forecast or an opinion. It is arithmetic. Given demand, inventory, open orders, lead times, lot sizes and safety stocks, it calculates what to order and when. When the inputs are right, the arithmetic is right. When the lead time in the item master says four weeks and reality is twelve, MRP will tell you to order eight weeks too late every time, and it will do so very precisely.

The way back is to fix the inputs, starting with lead times. Every purchase order receipt records when the order was placed and when the goods arrived. That history tells you the actual lead time, part by part and supplier by supplier. Compare it to the planning lead time and update the ones that have drifted. In a year like this one, the list will be long, so begin with the parts that matter most: high value, long lead, single source, or on the critical path for your biggest customers.

Then look at the rest of the parameters the same way. Safety stocks set before the disruption may now be too low on fragile parts and too high on stable ones. Order minimums and lot sizes may no longer match what suppliers will actually ship. Each of these is a field in the item master, and each one was set by somebody at some point and probably never revisited.

Make Reviewing Parameters a Habit

The deeper fix is to stop treating planning parameters as setup data entered once at implementation. Lead times, safety stocks and lot sizes are living assumptions about the outside world, and the outside world has made clear that it moves.

A monthly review of actual versus planned lead time on critical parts, with a named owner and a rule for when to update the master data, is enough for most plants. It turns a slow, invisible drift into a short, visible list.

Bring the Planners Back In

The planners who built the spreadsheets are not the problem. They are the people who noticed the system was wrong first, and they know which parts are late and why. Involve them directly in correcting the parameters. Ask them which adjustments they are making by hand, and turn each one into a fix in the ERP. As the suggestions start matching their judgment, the spreadsheets become redundant on their own.

It also helps to agree on how planners work the exception messages, the reschedule-in, reschedule-out and expedite notices MRP produces. When the parameters are wrong, those lists run to thousands of lines and get ignored. When they are right, the list is short enough to act on every morning, and that daily habit is what keeps the system trusted.

The Bottom Line

The disruptions of 2021 did not break MRP. They exposed planning parameters that had not been revisited in years. When planners stop trusting the system, the plant runs on private spreadsheets, and the data in the ERP gets worse every month. Rebuilding trust means updating lead times from actual receipts, reviewing safety stocks and lot sizes, working exception messages daily, and making those reviews a habit rather than a project.

At Cherry Street, this is the work we do. We help manufacturers measure their real lead times, correct the planning parameters that drive MRP, and bring planning back into the system where the whole plant can see it.