All articles

Bottleneck Analysis: Worked Example + Excel Template

By Davina · Published

Key takeaways

  • Bottleneck analysis identifies the step whose cycle time exceeds takt time, since system capacity always equals the constraint's capacity, never the average of all steps.
  • WIP piling up in front of a step is diagnostic evidence: the queue grows at the difference between upstream and constraint cycle times, marking the constraint before you time anything.
  • A bottleneck is a station running near 100% utilisation; a blocker is a stoppage at low utilisation, and utilisation is what tells you which one you are dealing with.
  • In an Excel template, load is demand times minutes per unit, utilisation is load divided by available time, and an INDEX/MATCH on the MAX utilisation names the bottleneck automatically.
  • A fix only changes one input cell, so the template shows where the constraint moves next, which is the basis for scoping a phased client engagement.

Bottleneck analysis finds the one step in a process that caps total throughput, quantifies how much output that constraint costs you, and tests whether a fix raises system capacity or just moves the queue. This walkthrough runs one continuous example from identification through resolution, then rebuilds every calculation in Excel with cell-level formulas you can replicate.

What is bottleneck analysis (definition and core concepts)?

Bottleneck analysis definition

Bottleneck analysis is the method of finding the step in a process that limits total throughput, measuring how much it limits it, and testing what happens to the whole system when you change it. The core idea comes from the Theory of Constraints: every system has at least one constraint, and output can only improve if you manage that constraint first, as summarised in Goldratt's Theory of Constraints overview. You can see the mechanics in the workflow bottleneck calculator.

Constraint vs bottleneck vs blocker

A constraint is any factor limiting system capacity. A bottleneck is a physical constraint: a station whose cycle time exceeds takt time, so WIP piles up in front of it. A blocker is a stoppage (missing approval, absent staff) that halts work without being the structural limit. Utilisation tells you which you are dealing with: a station running near 100% utilisation is your bottleneck; a station at 40% that still blocks flow is a blocker.

How does bottleneck analysis work in operations management?

Takt time, cycle time, and the demand gap

Takt time is demand divided by available working time: how fast the process must run to keep up. Cycle time is how fast each step actually runs. Compare the two at every step and you get a demand gap: any step whose cycle time exceeds takt time cannot meet demand, and the largest gap marks your constraint. System capacity equals the constraint's capacity, never the average of all steps.

Why WIP piles up in front of the slowest step

When an upstream step finishes work faster than the constraint can consume it, finished items queue. That queue is WIP, and it grows at the rate of the difference between the two cycle times. WIP in front of the slowest step is therefore diagnostic evidence, not a scheduling accident: it shows you where the constraint sits. Measuring queue length per step is often the fastest way to find it before you time anything. For a comparison of tools that automate this measurement, see bottleneck software.

What is a real bottleneck analysis example, step by step?

A consultancy runs a five-step client onboarding process handling 40 engagements per week. Here is the full analysis, using one continuous example.

Step 1: Map the process and collect cycle times

Time each step over ten runs and use the median. Takt time is available work time divided by customer demand; cycle time is how long one unit actually takes at a step (Lean Lexicon, Lean Enterprise Institute). At 37.5 available hours per week, demand of 40 engagements sets a takt time of 56 minutes per engagement.

Step 2: Compare each step's capacity to demand

Capacity per step equals available time divided by cycle time. The step with the lowest capacity is the constraint.

StepCycle timeWeekly capacityUtilisation at 40/week
Intake review40 min56/week71%
Scope draft45 min50/week80%
Compliance check55 min41/week98%
Client sign-off35 min64/week63%
Kickoff scheduling30 min75/week53%

Compliance check is the bottleneck: 98% utilisation leaves no recovery capacity, so any absence or rework becomes backlog.

Step 3: Quantify the demand gap at the constraint

Capacity of 41 against demand of 40 looks fine, but at 85% OEE-style availability the constraint delivers roughly 35, a demand gap of 5 engagements per week. Test any fix against the whole system, not the step, using the Constraint Impact Calculator.

How do you build a bottleneck analysis template in Excel?

Cell layout and inputs

Rebuild the machine shop example from the previous section in six columns: A, station name; B, available minutes per week; C, demand (units); D, minutes per unit; E, load in minutes; F, utilisation. Rows 2 through 6 hold the five stations, with row 1 as headers. Keep inputs (B, C, D) formatted differently from calculated cells (E, F) so a client can see immediately what they are allowed to change. If you want a fuller version with shift patterns and calendars, the same structure extends into the capacity planning models covered at https://improvefirst.com/learn/capacity-planning-tools.

Formulas for utilisation and demand gap

Here is the whole template, and it is short enough to rebuild in ten minutes. In E2 enter =C2*D2, the load that demand places on the station. In F2 enter =E2/B2, formatted as a percentage, which gives utilisation. In G2 enter =B2-E2, the demand gap: negative means the station is short of capacity, positive means it has slack.

Copy all three down the column. The bottleneck is simply the row with the highest F value, so in a summary cell enter =INDEX(A2:A6,MATCH(MAX(F2:F6),F2:F6,0)) to name it automatically. Sorting by column F descending puts the constraint and the next-tightest station at the top, which is exactly what you need when the client asks what happens after the first fix.

Testing a fix: what changes and what does not

This is the paragraph to quote. The template's real value is that a fix only touches one input cell, so every downstream number updates and you see the constraint move rather than assume it does. Suppose station 3 runs at 110% and you cut its minutes per unit from 12 to 10 by changing one cell in column D. Its utilisation drops to about 92%, throughput rises to match, and the MAX formula now names a different station as the bottleneck, usually the one that was sitting quietly at 85% before. What does not change is the structure: demand in column C, available minutes in column B, and the fact that total system capacity equals the smallest B/D ratio in the sheet. That is the point worth making to a client before quoting a deeper engagement: a spreadsheet shows where the constraint sits and where it moves to, but it will not tell you whether overtime, offloading, or a layout change is the right fix, which is where the scoping conversation starts.

How do you decide which fix to apply once you find the bottleneck?

Goldratt's five focusing steps give the sequence: identify, exploit, subordinate, elevate, repeat (Theory of Constraints overview). Exploit first — get everything out of the constraint with zero spend. In the worked example, that means making sure the constraint never waits for material or changeovers.

Offload, elevate, or stabilise

Offload work to a non-constraint if the task doesn't truly need the constraint. Elevate only after exploitation is maxed out — that's when you buy capacity or add a shift. Stabilise when cycle time varies: at a constraint, variation converts directly into lost throughput, so reducing variation often buys more capacity than capital does. Test any candidate fix against the whole system before quoting it, as the Constraint Impact Calculator does.

Where the constraint moves next

Every fix shifts the constraint somewhere else. If you elevate the current bottleneck by 20%, rerun your capacity model: the next constraint is usually the downstream station or demand itself. Scope the engagement around that second constraint, or your client's gain stalls at the new limit.

Which bottleneck analysis tools should you use and when?

Comparison criteria

Judge any tool against four questions. Can it model a multi-step process with your cycle times? Can it quantify the constraint, not just flag it? Can it predict where the constraint moves after a fix? And can you produce something a client will accept as evidence?

How the options compare, including Improve First

A spreadsheet model built from the Excel template above meets all four criteria for a single-line process with stable demand. It fails once you add variability, shared resources, or rework loops, because the arithmetic stops being simple averages.

ProcessModel simulates those complex flows directly, so it suits deeper engagements where variability dominates. DO.Impact and FlowVisual cover lighter diagnostic work. KaiNexus sits adjacent, tracking improvement work rather than modelling throughput.

Improve First's Constraint Impact Calculator scopes the engagement: it identifies which step limits output, tests whether a fix lifts the whole system, and shows where the constraint moves next. That answers the question you need before quoting, not after. Its shortcoming is scope: it is a scoping tool, not a full simulator, so hand the build to ProcessModel or a spreadsheet once the engagement is signed.

How is bottleneck analysis applied in healthcare operations?

Healthcare bottleneck analysis works like the manufacturing example, with one twist: the constraint is usually a shared resource (a theatre, an ultrasound room, a consultant's clinic slot) rather than the longest task in the patient pathway.

Clinics, theatres, and diagnostics as constraints

In an outpatient clinic, the constraint is often the consultant's session, not reception or phlebotomy. Calculate takt time from appointment demand, measure cycle time at each station, and the utilisation gap points to the resource that caps daily throughput. The same logic applies to theatre lists, where turnaround between cases, not surgery itself, frequently eats capacity.

In diagnostics and inpatient flow, the constraint moves: a CT scanner limits ED throughput until discharge delays build a bed backlog, then the constraint shifts to ward capacity. NHS England's guidance on managing patient flow treats this as a system question rather than a single-department fix (https://www.england.nhs.uk/). Re-run the analysis whenever demand or staffing changes, because a shared constraint rarely stays put.

How do you scope a client engagement from a bottleneck analysis?

What to measure on day one

Run the full analysis on one process: constraint location, its capacity, throughput, and the demand gap against it. Capture cycle time at the constraint and WIP sitting in front of it. That is enough to state, in numbers, what the system produces now and what it would produce if the constraint's capacity rose by a given amount. Keep the calculation set small enough to rebuild live in front of the client.

Turning the numbers into a proposal

The scoping report becomes your quote's backbone: current constraint, projected gain from the first fix, and where the constraint moves next. That third point matters most, because it tells the client the work does not stop at one fix and justifies a phased engagement. Price phase one against the measured throughput gain, and let the projected shift of the constraint define phase two's scope.

FAQ

Can I do bottleneck analysis in Google Sheets instead of Excel?

Yes. Every formula in the template, including =INDEX(A2:A6,MATCH(MAX(F2:F6),F2:F6,0)), works identically in Google Sheets. The only differences are minor: percentage formatting and cell protection behave slightly differently. If you need to share the model with a client for live editing, Sheets is often the better choice.

What is the difference between a bottleneck and a constraint in Theory of Constraints?

A constraint is any factor limiting system capacity; a bottleneck is the physical version of one, a station whose cycle time exceeds takt time so WIP accumulates in front of it. A blocker, by contrast, halts work without being the structural limit: a station at 40% utilisation that still stops flow is a blocker, not a bottleneck.

How long does a bottleneck analysis take on a small process?

For a five-step process with stable demand, a few hours. Time each step over ten runs, take medians, compute takt time and per-step capacity, then build the spreadsheet, which takes about ten minutes. The measurement dominates the effort. Variability, shared resources, or rework loops extend the work considerably.

What data do I need before starting a bottleneck analysis?

Three inputs per step: available working minutes per week, demand in units, and cycle time in minutes per unit, measured as the median of roughly ten timed runs. Queue length or WIP in front of each step helps too, since a growing queue marks the constraint before you time anything.

Is utilisation of 100% at a step always a bottleneck?

Effectively yes, but treat it as a red flag rather than a precise reading. A step at 98% utilisation, like the compliance check in the worked example, leaves no recovery capacity, so any absence or rework becomes backlog. Confirm with WIP: a queue forming in front of the step confirms it is the constraint.