Simulating processes in Excel: what works, where the wall is
In Excel you can rebuild utilisation per step, a Kingman approximation for waiting time, and with random numbers even a Monte Carlo draw. Three things are out of reach: queues that build up over days and drain slowly, the coupling between steps (a step never sees more than the slowest one ahead of it lets through) and calendars with shifts and batch runs. Those three are exactly what determines the outcome in administrative processes. For a rough estimate a spreadsheet suffices; the moment it becomes a business case, you need a tool with real event simulation.
Table of contents
What genuinely works in Excel
Not a little. Anyone who sets up the following three calculations is ahead of most process projects.
1. Utilisation per step
ρ = (volume per day × processing time) ÷ (people × productive hours × 60)
One column per step, sorted descending. The highest value is your constraint. Anything above 85 % is an alarm level. This is the single most important number in the whole exercise, and it costs ten minutes.
2. Waiting time as an approximation
The Kingman formula fits in a cell:
Wq ≈ (ρ / (1 − ρ)) × ((ca² + cs²) / 2) × te
With ca and cs as coefficients of variation (standard deviation ÷ mean) and te as mean processing time. For a single step with a stable inflow this gives a usable order of magnitude, and shows immediately why 85 % utilisation is expensive.
3. Monte Carlo by hand
One row per simulated case, durations drawn from a distribution with RAND(), a thousand rows, then PERCENTILE() for P10, P50 and P90. That gives you a distribution instead of a mean. That is a large step up from the usual point calculation.
Add-ins (@RISK, Crystal Ball, ModelRisk) make it more comfortable, but the principle is unchanged.
The three walls
Wall 1: the queue over time
Kingman computes a steady state. What the spreadsheet never contains is that a backlog forms on the first of the month and takes two weeks to clear, because only the difference between capacity and inflow is available for clearing it, not the full capacity.
Concretely: a process at 71 % utilisation on the annual average and 148 % in the peak week has lead times of 9 days in a quiet quarter and 46 days afterwards. The spreadsheet computes 71 % and says "healthy".
Wall 2: coupling
A step never sees more cases than the slowest step ahead of it lets through.
While review runs at 114 %, the approval behind it looks relaxed. It receives a throttled volume. Fix review and approval takes the full volume and becomes the constraint itself. A spreadsheet computes each step in isolation and cannot see that movement.
This is the most common reason spreadsheet business cases fail in operations: the promised saving was computed at the constraint that got fixed, while the effect is held back by the next one.
Wall 3: calendars
Batch runs twice a week, approvals only on weekdays, holidays with no cover, shift handovers. Each of those creates waiting that no formula represents, because it arises from points in time rather than from utilisation.
The test: do you need more than Excel?
Five questions. One "yes" is enough.
- Is utilisation above 80 % at any step?
- Does your inflow vary noticeably at month end, by season or through campaigns?
- Do you want to evaluate a change that removes a constraint?
- Are there batch runs, shifts or approvals that only happen at certain times?
- Does somebody have to sign the number who will ask follow-up questions?
Five times "no" and the spreadsheet is the right tool; anything else is effort without return.
Alternatives, sorted by case
| Your case | Suitable alternative |
|---|---|
| You only need better spread, not a queueing model | Monte Carlo add-in for Excel (@RISK, Crystal Ball) |
| Your team writes Python | SimPy, Salabim or Ciw: open source, full event simulation |
| You design facilities | Simulation lab: Arena, Simul8, FlexSim, Simio, Plant Simulation |
| You model in BPMN anyway | Bizagi Modeler, Signavio, ARIS: check simulation scope per edition |
| You need a decision about an administrative process, in money | A lean decision instrument such as FlowVisual |
The transition, practically
You carry more out of your spreadsheet than you expect:
- The step list: usually already five to ten rows, exactly the right size.
- Volumes and capacities: the columns you have been maintaining anyway.
- Processing times: but now as a range instead of a mean. Ask three processors separately and take the range from their answers.
What is new: peak factor at the inflow, calendar, abort rate. Budget twenty minutes for the transfer.
The cross-check at the end, and it applies in Excel as much as in a simulator: count work in progress, divide by daily throughput (Little's Law), compare with the computed lead time. If the measured one is far above, a queue is missing from the model.
What you should keep in Excel
Not everything should migrate. Cost calculation (fully loaded rates, volume structure, annual extrapolation) belongs in a spreadsheet and stays checkable by any controller. The typical mistakes there are covered in Process costs in Excel.
What does not belong in a spreadsheet is lead time. It comes from queues, and queues are precisely what a spreadsheet cannot do.
Frequently asked
Can you simulate processes in Excel?
Up to a point. Utilisation per step, a Kingman approximation for waiting time and a Monte Carlo draw with random numbers can all be rebuilt. What cannot be represented is queues over time, the coupling between steps, and calendars with batch runs. Those three determine the outcome in administrative processes.
Are @RISK or Crystal Ball enough to replace a simulation tool?
They solve one problem: they turn point values into distributions and give P10–P90 instead of a mean. What they do not change is the structure of the calculation: even with an add-in, the spreadsheet adds processing times instead of making cases wait in front of busy steps. Good for cost calculations under uncertainty, insufficient for lead times.
How do I know my Excel calculation is too optimistic?
Little's Law. Count the cases waiting today and divide by daily throughput. If the lead time measured that way is well above the computed one, waiting is missing from your spreadsheet, which is practically always the case once a step runs above 80 % utilisation.
What is the single biggest difference to a simulation tool?
Coupling. A spreadsheet computes each step in isolation; a simulator runs cases through all of them. That is why the spreadsheet cannot see that a step behind the constraint only looks relaxed because it is being throttled, and that it becomes the constraint itself once the first one is fixed.
Is switching worth it for a single project?
If a decision with a meaningful sum depends on it: yes, because the effort is half a day and the spreadsheet is systematically too optimistic. If it is a rough estimate nobody has to sign: no. The spreadsheet is then more honest, because it does not pretend to be a model.
Run the numbers on your own process
FlowVisual turns the figures in this article into a model that runs, with your volumes, your capacities, your range.
Guide: seven steps to the number- Comparison
Simul8 alternatives: three groups, and when each is right
People searching for a Simul8 alternative usually want one of three things: lower cost, less training, or a tool that fits administrative processes rather than the factory floor. Each reason has a different answer. For one of them, switching is the wrong fix.
Read - Comparison
Digital twin or simulation? The difference is the wire
A digital twin is a simulation model with a data connection. That connection is the entire difference. It turns an afternoon into a project with an operations budget.
Read - Comparison
Process mining or simulation? The past against the future
The two get confused because both are called “process analysis”. They answer opposite questions. Choosing the wrong one only becomes obvious once the answer arrives.
Read