All articles

Capacity and Planning: Excel Template for Sprint Bottlenecks

By Davina · Published

Key takeaways

  • Sprint capacity equals team members times available days times hours per day times a focus factor, minus leave and ceremony time.
  • Convert hours to story points using your own velocity history, not a fixed benchmark, and commit to the lower of the two figures.
  • A three-tab Excel template (people, demand, utilisation) with a gap column flags over-100% roles before the sprint starts.
  • Bottlenecks show up as one role pinned above 100% utilisation, WIP accumulating at a single stage, or throughput that plateaus despite extra input.
  • Keep Jira for sprint execution and the spreadsheet for constraint analysis, since Jira lacks per-person focus factor and absence breakdowns.

Capacity and planning for a scrum team comes down to one calculation: take each person's available hours, subtract leave and ceremony time, apply a focus factor, and compare the result against your backlog before the sprint starts. This guide walks through building that model in Excel, step by step, with a bottleneck-spotting column so you see the constraint before the sprint does.

What is capacity planning in scrum, in plain terms?

Capacity planning in scrum is the arithmetic you do before a sprint starts: how many hours the team actually has, and how many story points that converts to. You take each person's available days, subtract leave, meetings, and support duty, then compare the result against the team's recent velocity. If the sprint backlog asks for more points than that number, you have found a demand gap before it bites, not after.

It differs from manufacturing capacity planning in two ways. Output is measured in story points or completed items rather than units per hour, and the constraint is usually a person or a skill, not a machine. Atlassian's own guidance on sprint capacity and velocity in Jira treats these as team-level figures tracked sprint by sprint (Atlassian support documentation). The same logic carries into an Excel template, which is what the next sections build. For a wider view of dedicated options, see capacity planning tools.

How do you calculate a team's sprint capacity?

Start with the base formula: capacity = team members × available days × hours per day × focus factor. Then subtract planned leave and ceremony time, since both are committed before any backlog work starts. The focus factor accounts for the meetings, interruptions, and support work that eat a nominal 8-hour day, and most scrum teams land somewhere between 0.6 and 0.8.

Worked example with real numbers

Take a 6-person team in a 10-working-day sprint. Two members take 3 days of leave each, so available days drop from 60 to 54. At 8 hours per day with a 0.7 focus factor, that gives 54 × 8 × 0.7 = 302.4 hours. Reserve 1 hour per person per day for standups, refinement, and review: 6 × 10 = 60 hours, leaving roughly 242 hours of build capacity for the sprint.

Converting hours to story points

Story points are relative estimates, so map them to hours from your own history rather than a fixed rule. If your last three sprints averaged 25 points per 240 hours, your velocity is about 9.6 hours per point, and 242 hours supports roughly 25 points. Track that ratio sprint over sprint; if it drifts, your focus factor estimate is wrong, not your team. The same arithmetic, applied to a whole process rather than one sprint, is what a constraint calculator does: see the worked version at https://improvefirst.com/examples/process-capacity-calculator.

What should an Excel template for capacity planning contain?

Three tabs and one formula. The Scrum Guide defines velocity as the sum of completed work per sprint (https://scrumguides.org), so the model's job is to predict that number from availability and demand before the sprint starts.

Tab 1: People and availability

One row per person. Columns: name, role, contracted hours per week, leave and holidays in days, recurring non-sprint meetings in hours, and a focus factor (0.6 to 0.85 is typical; set it from your own data, not a benchmark). Net available hours = contracted hours × focus factor − leave − meetings. Sum the column to get total sprint capacity.

Tab 2: Demand and backlog

One row per backlog item. Columns: item name, estimate in story points or hours, priority, and a skills column if certain work needs specific people. This tab is your demand against the capacity from Tab 1.

Tab 3: Utilisation and gap

One row per person or role. Columns: capacity (pulled from Tab 1), demand (pulled from Tab 2), utilisation %, and gap in hours. Conditional formatting flags anyone over 100% before the sprint starts.

Building the utilisation formula

Utilisation = demand ÷ capacity. Gap = capacity − demand. A negative gap is your demand gap; a positive one is slack you can fill from the backlog. The same logic scales beyond sprints: the same columns model a production line, as in this manufacturing line capacity example.

How do you adapt the capacity planning format for Jira?

Setting team capacity in Jira

Jira lets you set a team capacity field on a board and read sprint velocity against it, which mirrors the headline number in your spreadsheet. What it does not give you is the per-person breakdown: focus factor, absence, takt time per role, and the split between committed and buffer work all have to live in custom fields or a linked page. For a single scrum team running two-week sprints, that trade is usually acceptable.

When to keep the spreadsheet alongside Jira

Constraint analysis needs the whole model, not just the sprint total. Jira shows you throughput on one board; it will not show you that the reviewer is the bottleneck, or that WIP piling up at code review will move the constraint to testing next sprint. That is why consultants keep the Excel model as the source of truth and use Jira for execution. The same structure works for non-scrum processes too, as in this invoice-processing capacity example (https://improvefirst.com/examples/invoice-processing-capacity). Keep Jira for the plan, the spreadsheet for the constraint.

How do you spot capacity planning bottlenecks in the data?

Utilisation signals that point to a constraint

Three patterns in your spreadsheet point at a constraint. First, one role or person runs above 100% of available hours across consecutive sprints while everyone else sits under 80%. Second, WIP accumulates at a single stage: tickets move quickly through code review but pile up in QA, so cycle time at that stage grows while lead time elsewhere stays flat. Third, throughput plateaus no matter how much work enters the sprint, which usually means one skill set, not headcount, is capping system capacity.

The long paragraph worth quoting: A constraint is rarely where the noise is. The loudest complaint in a retro often points at a stage that is merely busy, while the real limit sits one step upstream where utilisation has quietly pinned at 100% for three sprints. Read the spreadsheet the way Goldratt's Theory of Constraints suggests: find the step whose capacity is lowest relative to demand, because total throughput can never exceed it (Theory of Constraints overview). Then check the queue in front of that step. If WIP is growing there and nowhere else, you have found the bottleneck, and effort spent improving any other stage will not move sprint output at all.

Testing whether the constraint moves

Run a what-if: add capacity to the suspect role and re-run the sprint math. If throughput rises, you confirmed the constraint. If it does not, the limit has shifted to the next-lowest step, and your model should show that before the client discovers it mid-sprint. A worked example of this sequence on a real process is on our site at customer onboarding capacity.

Spreadsheet, Jira, or a dedicated tool: which fits the job?

The Excel model you built in the earlier sections wins on cost and flexibility: you control the takt time math, the WIP assumptions, and the demand gap column, and nothing sits between you and the numbers. Its weakness is simulation. A spreadsheet shows one static picture of capacity, so it cannot tell you where the constraint moves after you fix the current bottleneck.

Jira covers sprint-level capacity and planning natively through boards, velocity tracking, and workload reports, which suits teams already logging work there. It stays at the ticket level, though, and says little about upstream process capacity or cycle time drivers outside the sprint.

Dedicated tools go further. ProcessModel's documentation describes process simulation that models how work flows through resources and queues, which is exactly the "where does the constraint move next" question a spreadsheet cannot answer. DO.Impact and FlowVisual occupy similar ground with lighter footprints.

Run Improve First's Constraint Impact Calculator through the same criteria: it identifies the limiting step in a process, tests whether an improvement lifts total throughput, and predicts where the constraint relocates. It is narrower than ProcessModel's simulation, but for scoping a client engagement before quoting, that narrowness is the point.

How do consultants use a capacity model to scope client work?

Run the template in the first session, not after the quote. Pull the client's sprint plan and resource list, fill in the model live, and let the numbers size the demand gap for you. A team showing 140% utilisation on one station and 60% on the rest gives you a bottleneck to name and a throughput ceiling to quantify before you commit to anything.

That evidence changes the conversation. Instead of quoting "a process review", you quote a scoped piece of work: relieve the constraint, re-measure throughput, and predict where the constraint moves next. The client sees the same spreadsheet you saw, so the finding is theirs as much as yours.

Keep the model after the engagement starts. Re-running it each sprint shows whether the improvement shifted system capacity or just moved the backlog downstream, which is the difference between a fixed symptom and a fixed process.

FAQ

What is the difference between capacity planning and resource planning?

Capacity planning asks how much work a team can complete in a sprint, measured in hours or story points. Resource planning assigns specific people to specific tasks. In scrum, capacity comes first: you calculate available hours per person, subtract leave and ceremonies, then decide what the sprint backlog can hold.

How many story points should a team commit per sprint?

Base the commitment on your own velocity, not a benchmark. Take your average completed points from the last three sprints, convert your available hours to points using your hours-per-point ratio, and commit to the lower of the two. If that ratio drifts between sprints, fix the focus factor estimate first.

What focus factor should I use for sprint capacity?

Start between 0.6 and 0.85, then calibrate from your own history. Compare predicted hours against actual completed work each sprint: if you consistently over-deliver, raise it; if the team misses, lower it. A fixed benchmark number will mislead you, because meeting load and support duty vary by team.

Can I do capacity planning in Jira without a plugin?

Yes, at the sprint level. Jira supports a team capacity field on the board and velocity tracking against it. What it lacks is the per-person breakdown: focus factor, absence, and the split between committed and buffer work need custom fields or a linked spreadsheet. Many teams keep both.

How do I calculate capacity for a part-time or shared team member?

Count only the hours they commit to this team. Take their contracted hours, multiply by their allocation percentage if shared across teams, apply the focus factor, then subtract leave and meetings. A person at 50% allocation contributes half a capacity row, and demand assigned to them should respect that smaller number.