An Excel MRP is often the first planning tool of an SME manufacturer: a workbook that lists the items, calculates requirements from orders and forecasts, and proposes manufacturing orders. Built by the person who does the planning, it fits the shop floor closely. Whether it is a “serious” tool is not the question: it is, since it keeps the plant running.
The real question lies elsewhere: how far can this workbook go, at what point does it cost more than it brings, and how can you strengthen it without starting over? This article answers those three questions, and sets out what software must keep from the workbook on the day you switch.
Why an Excel MRP lasts so long
Many companies hear “production management software” and immediately picture replacing their spreadsheets with an ERP. Yet Excel has strengths that no software package reproduces easily.
- It starts before the master data exists. When a new product arrives, its items, routings, bills of materials and work centres are not yet in the ERP. Excel lets you start production anyway, and formalise later.
- It follows the company's own rules. Rounding to the pallet, minimum batch size, seasonal target cover, main line and back-up line: it all fits in a few columns written by someone who knows the workshop.
- It can be corrected in a minute. A rule changes? A formula changes. No ticket, no update to wait for.
What a working Excel MRP table looks like
A well-built Excel MRP table has one row per item and one column per day or per week. For each period, it chains four pieces of information:
- the opening stock, equal to the projected stock of the previous period;
- the requirements: firm orders, then forecasts beyond the firm horizon;
- the expected receipts: manufacturing orders released in the ERP, supplier deliveries;
- the projected stock at the end of the period: opening stock, plus receipts, minus requirements.
When the projected stock falls below the safety stock, or when cover (projected stock divided by average consumption) drops below the target cover, the workbook proposes a production run, rounded to the batch size or the minimum run. The planner stays in control and corrects it. The “calculated proposal” and “requested production” columns stay separate, so you can see where the human has overruled the machine. An ERP performs the same calculation; in Excel, the rounding rule and the target cover remain visible and editable by the person doing the planning.
We have documented a workbook of this kind, with a 17-day horizon, load per line and a net requirements calculation (MRP run), in this MRP planning case study in a food processing workshop.
What eventually breaks
Multiple users
A workbook is designed for one person. As soon as the scheduler, the buyer and the workshop manager want to work in it at the same time, the file locks, gets duplicated (“planning_v3_final_bis.xlsx”) and which version is the reference becomes a topic of debate. Online sharing eases the symptom, but it manages neither access rights nor history.
Formulas
A column inserted in the wrong place, a VLOOKUP on a fixed range, a macro written by a colleague who left two years ago: the workbook still shows figures, but nobody knows any more whether they are right. The real risk is not the file that crashes; it is the file that is wrong without saying so.
Re-keying from the ERP
Every morning, someone exports stock, orders and open manufacturing orders, then pastes them into the workbook. Miss one, and the plan is based on yesterday's stock. In the other direction, the manufacturing orders decided in Excel are re-created by hand in the ERP, with all the item reference errors that implies.
Calculation time
A multi-level MRP run on hundreds of items over several weeks means millions of operations. Beyond a certain volume, recalculation takes minutes, and the planner ends up switching off automatic calculation, then forgets to run it again.
Strengthening an Excel MRP without throwing it away
Before talking about replacement, here are five ways to strengthen it, from the simplest to the most technical.
- Separate data, calculation and presentation. One raw import sheet that is never edited by hand, one parameters sheet (target cover, rounding, run rates), calculation sheets, and one output sheet.
- Automate the data feed. Power Query or a direct connection to the ERP replaces the morning copy and paste.
- Document the rules. Each calculation column carries a comment saying what it does and why: this will become the requirements specification for the future tool.
- Lock what needs locking. Protected sheets, drop-down lists, data validation.
- Offload the heavy calculations. The most effective reinforcement for large volumes, and the least known.
Macros that call a calculation server
At one client, we came across a set of Excel workbooks linked together by macros. Complex but consistent, this set could not be replaced in one go without disrupting the organisation.
Rather than a rebuild, we added a macro that sends the data to a remote server (NestJS and MongoDB), which runs the calculation and writes the result back into the workbook. The planner still works in Excel, but the heavy calculations no longer tie up their computer. The calculation rule lives in a single place, versioned and testable, and the cost runs to a few thousand euros, compared with rebuilding the whole system. Nothing exotic: VBA can call a web service and write the response back into a range (see the Excel object model for VBA).
What about the “free Excel MRP” templates found online?
Free planning or stock-tracking templates help you understand the logic of MRP, but they know nothing of your batches, your lines or your cleaning constraints. A downloaded template becomes useful the day it is rewritten with your rules, and by then it is no longer free: it has cost the days of the person who adapted it, and it will need maintaining. You may as well treat it from the start as a real tool, backed up and documented, with someone in charge of it.
When to move to software, and what it must keep from the workbook
The move is justified when at least two of these signs are present: several people need to work in the plan at the same time; an ERP is in place and data is re-keyed daily in both directions; nobody can confidently edit the macros any more; history matters (who changed what, and when); recalculation takes long enough that people avoid it.
The decisive point: the software that replaces the workbook must keep its rules. Rounding, minimums, target cover, line priorities: this is the company's know-how, refined over years. A tool that imposes its own “standard” MRP sends the SME back to square one; a tool in which every calculation column can be programmed and explained, as in the workbook, carries that know-how over. See also our article on MRP planning at infinite capacity.
What we see in real workshops
Three observations keep coming back from the shop floor. The Excel workbook is almost always closer to reality than the ERP's planning module, because its author lives with the result. The moment of switching is rarely a choice: someone leaving, a breakdown or an audit forces it. And the companies that make the transition successfully documented their rules before choosing the tool. The workbook is not the enemy of software: it is its requirements specification.
Conclusion: how far an Excel MRP can take you
An Excel MRP can go a long way: several weeks of horizon, MRP run, load per line, automatic proposals. It breaks down on multiple users, formulas, re-keying and volume. You can strengthen it before replacing it; and when the time comes, the right software is the one that takes over the workbook's rules instead of erasing them.
This is what Planiprod does: planning, MPS and MRP in which every column can be programmed and explained, fed by the ERP each morning or by your Excel workbooks, with the ERP remaining the master of manufacturing orders and purchasing. To see how your Excel rules become columns, visit our page MRP and Excel or book a demo with us.
And if your workbook does not yet calculate net requirements, we have published a free MRP Excel workbook where you paste in your MPS and your bills of materials, click, and read what needs to be launched and when. It is also a good way to tidy up your rules before choosing a tool.