Your spreadsheet does the math right — and is still wrong

Everyone knows this spreadsheet. Process steps down the left, average duration on the right, a sum at the bottom. Six steps, three days each on average, eighteen days lead time. That number then goes into a quote, a capacity plan, or a promise to a customer.

And then it takes longer. Not always, but far too often. The usual explanation is that the estimates were too optimistic. Convenient — and usually wrong. The estimates can be perfectly accurate and the calculation still misses.

So let us start on the uncomfortable side: the spreadsheet is not doing the math wrong.

First, a defence of the spreadsheet

If a process really is a chain — step one, then two, then three, no waiting, no shared resources — then the sum of the averages is exactly the average of the sum. That is not an approximation or a rule of thumb. It is the linearity of expectation, and it holds no matter how skewed the individual distributions are.

We ran it: six steps, each with a right-skewed duration averaging three days, 400,000 simulated cases.

The spreadsheet hits the mean dead on. That is precisely why nobody questions it. It is right — about exactly one question. Which happens not to be the question anyone is actually asking.

Because the second line is already in the room: in 44 percent of cases it takes longer than eighteen days. A mean says nothing about how often you hit it. It describes the average across many cases, not the case you are committing to right now.

Except your process is not a chain

A pure chain barely exists in practice. Three building blocks show up in nearly every process — and each one breaks the arithmetic.

The gate. Two things must be finished before work continues: the technical clearance and the credit check, before the quote goes out. Each takes three days on average. The spreadsheet writes down three days. Reality waits for the slower of the two — and the slower of the two is not, on average, three days fast.

The loop. A step sometimes has to be redone. Fifteen percent rework sounds like a rounding error. It is not.

The queue. One person handles two of the six steps. The spreadsheet records their handling time. What it does not record is the time the case spends lying on their desk, waiting, because they are busy with something else.

Same step durations. Same three-day averages. Only the structure differs:

The queue is the sledgehammer here. At 67 percent utilisation — a level most people consider relaxed — real time in system is already more than double the pure handling time. At 90 percent it is six times. That is not bad organisation, it is arithmetic: the closer utilisation creeps to a hundred percent, the more violently waiting time grows. And it does not grow linearly. It explodes.

Running your people at 90 percent utilisation is not efficiency. It just moves the waiting out of your books and into your customers' lead time.

Why the error always has the same sign

The pattern in that chart is not a coincidence. It has a name.

The spreadsheet computes f(mean): it plugs the average into every step, then runs the process. Reality delivers mean of f(X): it sends every case through the process with its real, fluctuating durations, and only then takes the average.

These are not the same thing. Jensen's inequality says: if the function is convex, the mean of the outcomes is greater than the outcome of the means.

And here is the point that matters: gate, loop and queue are all three convex. A maximum is convex. A geometric repetition is convex. A queue is violently so. Which is why no bar in that chart points down.

So the spreadsheet's error is not noise. It is a bias. You are not sometimes high and sometimes low. You are systematically optimistic — and the more structure the process has, the worse it gets.

Same process, two ways of computing it

Take a six-step process shaped the way real ones are. Steps two and three run in parallel and both must finish. Step five has fifteen percent rework. Nothing else exotic — no queue, no shared person. Every step averages three days.

The spreadsheet plugs in the averages and arrives at 15 days. That calculation is done carefully; the gate is even modelled correctly as a parallel branch.

You promise 15 days. You hit it 43 percent of the time.

That is the whole point. Not that the spreadsheet is off by ten percent — you could live with that. But that it produces a number which looks like a commitment and is in fact a coin flip. Plan against it and you are planning a process that does not exist.

You can run it yourself below. The simulation shows not only how long it takes, but which step becomes the bottleneck under which conditions — and the answer changes the moment the conditions do.

The one building block that points the other way

If every convex structure makes the spreadsheet too optimistic, is there a concave one? A structure where computing with averages is too pessimistic?

There is. And it is the reason the cheapest improvement is often not software at all.

Picture two caseworkers. Each has their own tray, their own queue. One handles new customers, the other existing ones. Equally fast, equally loaded.

Now change exactly one thing: a single shared queue. Whoever frees up takes the next case, whichever kind it is. The same two people, the same handling times, the same volume of work.

Time in system drops by 40 percent, the P90 by 45 percent. Without an extra person. Without a line of software. Without a tool that bills you monthly.

The reason is arithmetic again, only running the other way. Two separate queues cannot help each other. One person sits idle while work stacks up in front of the other, and that idle time is not recoverable — it is simply gone. A shared queue removes exactly that loss. The minimum over several free workers is a concave function, and that is where the sign flips.

For us this is the most honest finding in the whole article. When an analysis shows that your bottleneck is a queue, the right recommendation is often not "buy software." It is "merge the two trays." That costs you nothing — and costs us a project.

What follows for practice

One: point values do not belong in a process analysis. A duration is a range, not a number. Capture an optimistic, a typical and a pessimistic value per step. It takes no longer than guessing an average — and it is the only input you can actually compute with.

Two: never commit to the mean. The mean describes your annual average, not the next case. A committed date belongs at the P90. Commit to the mean and you are committing to break every second promise.

Three: structure beats estimation. Estimate step durations to within ten percent but miss a gate and a queue, and you will be off by a multiple. Conversely, rough estimates with a correctly modelled structure get you surprisingly close.

Four: utilisation is not a goal. Ninety percent utilisation feels like good management. It is the reason your lead time is exploding.

Which is why FLOW has an O

Our method is called FLOWREFY, and the first four letters are the measurement: Find — which processes are even candidates. Lay bare — what the current state costs in euros. Observe — simulate. Weigh — decide.

For F, L and W we publish free calculators. For O we do not. Not because we hid it behind a paywall, but because simulation does not fit in an input field. It needs the structure of the process: who waits on whom, who shares with whom, where rework happens. Exactly what a spreadsheet's SUM formula has no room for — and exactly what separates 15 days from 22.8.

That is what we built FlowVisual for: draw the process, enter ranges, play out the conditions. It shows you not only how long something takes, but where it jams when demand picks up — and whether the bottleneck you are about to fix is even the same one under load.

If you would rather just find out what your most expensive bottleneck costs per year, start here. It is free and takes ten minutes.

---

On method

Every number in this article comes from a Monte Carlo simulation, not from client work. Step durations are lognormal with a mean of 3.0 days and a spread giving a coefficient of variation around 0.65 — right-skewed, the way handling times actually behave: they cannot fall below zero but they can run away upward. Each building block was simulated with 300,000 to 400,000 cases; the queues with 80,000 arrivals and a discarded warm-up phase.

The specific percentages depend on those assumptions. The direction does not: it follows from Jensen's inequality and holds for any spread greater than zero. Narrower distributions give smaller deviations — but never a different sign.