Insights / Capacity planning
Capacity Planning Without Spreadsheets
A capacity spreadsheet tells you what each line should produce on average. It cannot tell you what the plant will produce, because it has no way to represent stoppages that happen at different times, buffers between stages, shared crews or a changeover sequence. Those effects nearly always reduce output, so a spreadsheet plan tends to promise more than the plant can deliver. This guide shows where the spreadsheet breaks, when it is still good enough, and what to use when it is not.
How a capacity spreadsheet works
Most manufacturing capacity spreadsheets follow the same pattern. For each line or stage:
- rated speed × planned hours = theoretical output
- theoretical output × an efficiency or OEE factor = expected output
- the plant’s capacity is the lowest expected output of the stages in sequence
Demand is then compared with that figure, product by product or in total. It is quick, everyone can read it, and for a stable plant with one product per line it gives a reasonable answer. The trouble starts when the plant has shared equipment, variable stoppages or a changing product mix, which describes most food, drink and pharmaceutical sites.
Where the spreadsheet breaks
It runs on averages
Average cycle time, average downtime, average yield. Losses on a real line do not average out between stages. When one stage stops, the next one runs out of material, and when a downstream stage stops, the upstream one fills its buffer and has to stop too. Each of those knock-on stoppages costs output that never appears in an average.
It treats stages as independent
A spreadsheet works out each stage on its own and takes the lowest. In a plant, the buffer between two stages decides how much one stage’s stoppages hurt the other. The same two machines can give very different output with a large buffer, a small one or none.
It cannot see the sequence
How much of the week goes to changeovers depends on the order products run in. A single average changeover time per week hides the difference between a good sequence and a bad one, which can be large. See cutting changeover time by resequencing.
It does not share resources
Crews moving between lines, cleaning-in-place sets serving several tanks, one palletiser fed by three lines, a forklift fleet. A spreadsheet gives each line the full resource. In practice they queue for it.
It gives one answer with no sense of risk
A spreadsheet produces a single figure. It cannot say how likely the plant is to hit it, or how badly a bad week would go. A model can be run many times with different stoppage patterns and show the spread.
A small example of the gap
Take two machines in sequence, each rated at 100 units an hour and each available 90% of the time, with breakdowns that happen at random and independently. The figures are illustrative.
| Approach | Line output |
|---|---|
| Spreadsheet: slowest stage × availability | 90 units/h |
| Real line with no buffer between the machines | about 82 units/h |
| Real line with a large buffer between the machines | close to 90 units/h |
With no buffer, every stop on either machine stops the whole line, so the losses add up instead of overlapping. The spreadsheet overstates output by about 10% before any changeovers, rejects or crew shortages are counted. With more stages, the gap grows. A buffer recovers most of it, but buffers cost money, take space and, in food, are limited by how long product can be held. Sizing them is a question a spreadsheet cannot answer. Our guide on sizing buffer tanks and intermediate storage goes through it.
When a spreadsheet is good enough
Keep the spreadsheet if all of these are true:
- each line runs one product or a small, stable mix
- stages are not shared between lines
- there is plenty of buffer, or the line is a single machine
- demand sits well below capacity, so a 10% error does not change any decision
- no capital decision depends on the figure
If the plant is near its limit, or a decision worth hundreds of thousands of pounds depends on the capacity figure, the spreadsheet is no longer adequate.
What to use instead
There are several steps up from a single-figure spreadsheet, and they suit different questions.
| Method | Good for | Limits |
|---|---|---|
| Spreadsheet with ranges and scenarios | Quick checks, stable plants | Still no interaction between stages |
| Advanced planning and scheduling (APS) software | Day-to-day and weekly scheduling | Plans the plant you have; not built to test a plant you might build |
| Discrete-event simulation or a factory model | Capital decisions, layout, buffer sizing, shift patterns, consolidation | Needs a model built and checked against real output |
A factory model follows product through every stage on real cycle times, changeovers and stoppage patterns. It includes tanks, vessels, crews and shared equipment, so the knock-on effects appear in the output. It can then answer the questions planners and finance actually ask: can we drop the weekend shift, where is the real bottleneck, what does a new line add, what happens if demand moves to smaller pack sizes.
A model is checked the same way any forecast should be: run a past period through it and compare its output with what the plant actually made. Once it matches, the scenarios it runs can be trusted for decisions.
What modelling has found that spreadsheets missed
- Pickles and condiments: a plant running 24/7 moved to 24/5. The model found that 12% of SKUs contributed 3% of margin but took 25% of changeover time, and resequencing cut total changeover time by 20%.
- Vinegar: 15% more throughput from capacity the plant already had, once the model showed which tanks were causing bottlenecks across milling, fermentation and bottling.
- Chocolate moulding: not everything that looked like a bottleneck on the floor turned out to be one. The model showed which constraints were holding throughput back.
- Toothpaste tube filling: 30% more output from identifying and dealing with the real capacity constraints on the line.
Data you need to start
Most of this already exists in a plant, often spread across several spreadsheets:
- a line list with rated speeds per product
- a process flow: which stages feed which, and what sits between them
- tank and vessel sizes, batch sizes and cycle times
- changeover times between products, ideally as a from-to table
- downtime records or OEE data for each line
- shift patterns and crewing per line
- a recent demand or production history by product
Gaps can be filled with estimates and refined later. We can build a working model of your plant from this data in 48 hours.
Common questions
What is rough-cut capacity planning?
Rough-cut capacity planning checks a production plan against the capacity of key resources, usually in hours per week. It is a sensible first check, and it is what most capacity spreadsheets do. It does not account for interaction between stages, so it overstates what a tightly coupled plant can make.
Can Excel run a simulation?
Monte Carlo add-ins can vary inputs and show a spread of results, which is an improvement on a single figure. They still calculate each stage on its own. Representing product moving through buffers over time, with stages blocking and starving each other, needs a simulation model.
Is this the same as a digital twin?
The terms overlap. Our guide to digital twins and factory simulation explains the difference and which one a capacity question needs.
Related guides
- New line or more from the lines you have: finding the capacity already in the plant before buying more.
- How to size buffer tanks and intermediate storage: the calculation, and where it needs a model.
- Factory modelling for food and beverage manufacturers: what we model in food plants.