Why Spreadsheets Can't Keep Up with Modern Production Planning

Introduction

Most process plants still plan in spreadsheets. Not because nobody's tried to replace them, but because the spreadsheet is flexible, familiar, and it mostly works, right up until the plant gets complicated enough that it doesn't.

Here's the part that gets missed: a spreadsheet was never doing what people think it was doing. It stores numbers and runs formulas. It doesn't optimize anything. While the plant is simple, one planner's judgment plus a spreadsheet is enough to pass for a plan. Add products, lines, changeovers, and shared equipment, and the gap between what the spreadsheet does and what the plant actually needs turns into money, quietly, every month.

A spreadsheet stores data. It doesn't make decisions

A spreadsheet can hold your production rates, your inventory targets, your forecasts. What it can't do is decide how to balance them. The moment you've got more than a handful of products, lines, and constraints pulling against each other, "balance them" stops being a formula and becomes a judgment call, and the spreadsheet quietly hands that call back to a person.

That's the line between calculating and optimizing. A spreadsheet, like an ERP, nets and tallies. It tells you what's needed. What it can't do is search the enormous space of possible plans for the best feasible one, because it was never built to. Optimization does precisely that: it weighs every constraint at once and returns a plan a person couldn't find by hand. The spreadsheet isn't a weaker version of that tool. It's a different tool that doesn't do that job at all.

Real constraints don't fit in a cell

Process planning isn't arithmetic. It's setup times, sequence-dependent changeovers, shared tanks, batch rules, blend tolerances, material dependencies, and half of them move week to week. A spreadsheet holds all that together with human memory and fragile formulas. One planner knows the number two line fouls if you run the dark grade before the light one. That rule lives in their head and a colored cell, not in the system.

And that's the risk nobody puts on the balance sheet. The plan isn't really in the spreadsheet. It's in the person who built it. When they're on vacation, or they leave, the plant stops planning as well and nobody can quite say why, because most of what that person knew was never written down anywhere a system could pick it up.

No good way to see the trade-offs

Planning is trade-offs. Run the long campaign and cut changeovers, or the short one and hold less inventory? Protect this customer's service level at the expense of that one's? In a spreadsheet, testing any of those means copying the file and rebuilding it by hand, so most of the time nobody tests them at all. You run the plan you could build, not the plan that's best, because building the alternative is a day of work you don't have.

And the plant won't wait while you build it. A new order, a line down, a raw material that's late, and the version you locked this morning is already wrong. A spreadsheet can't re-solve itself. Optimization can. It weighs those trade-offs in a single run and re-solves in minutes when things move, so the plan matches the plant as it is right now, not as it was on Monday.

The gap between feasible and best is the cost

Add it up, and here's what the spreadsheet really costs. Not the license, that's the cheap part. The cost is the distance between the plan a person can build by hand and the plan that's actually best, and that distance is real money: capacity left on the floor, working capital stuck in buffers, service missed in the fine print. Then the errors, complex spreadsheets are famous for hiding them, and one bad reference can bend a whole week's plan without anyone noticing. Then the planner's time, most of it spent nursing the file instead of improving the operation.

None of it shows up as a line item, which is exactly why it survives. It isn't a crisis. It's a slow leak, and the spreadsheet is where it hides.

Frequently Asked Questions