Most teams discover they are overcommitted the same way: a date slips, someone finally adds up the hours, and the total comes to 130 percent of a workweek. A capacity planning template catches that math before the slip. It is not complicated. The whole thing is a spreadsheet with people down the rows, time across the columns, and a handful of formulas comparing what each person has against what they have been given. Building one takes about an hour in Excel or Google Sheets. This guide gives you the exact columns, the formulas, a worked example with numbers you can sanity-check, and an honest answer to when the spreadsheet stops being enough.
Key takeaways
- A capacity planning template is a spreadsheet that compares each person's available hours against the hours planned work needs, per week or month, and flags the gap.
- Net capacity is the number that matters: standard hours minus time off minus non-project load. Meetings, admin, and support work routinely absorb 20 to 30 percent of the week.
- Plan to 70 to 85 percent utilization, never 100. A plan that allocates every hour guarantees slippage, because real weeks include interruptions, rework, and requests nobody scheduled.
- Roll capacity up by role, not just by person. The portfolio's real constraint is almost always one or two roles, not total headcount.
- A spreadsheet handles roughly 30 to 50 people and a dozen projects. Past that, cross-project conflicts start hiding in the tabs.
Download the capacity planning template
capacity-planning-template.xlsx is the sheet described below, already wired up. Open it in Excel, or upload it straight to Google Sheets (File, then Import). No signup, no macros. There is also a plain CSV version if you would rather paste it into a sheet you already have.
What is in the file. Two tabs. Capacity Plan holds the seven input columns, the net capacity formula, a utilization formula with a conditionally formatted OVER / AT RISK / OK flag, and five sample rows you can overwrite. Role Rollup is a SUMIFS view that cuts the same data by role and month, because the portfolio's constraint is almost always one role rather than total headcount. The formulas are live, so replacing the sample names with your team recalculates everything.
What is a capacity planning template?
A capacity planning template is a pre-structured spreadsheet, usually in Excel or Google Sheets, that lists each team member's available working hours per period, subtracts time off and non-project commitments, and compares the result against the hours their assigned projects require. The output is a utilization percentage per person and per role that shows who is overallocated before the schedule finds out.
That definition hides the part people get wrong. The template's value is not the grid; it is the honesty of the availability numbers you put in it. A template that starts every developer at 40 available hours is fiction, and every plan built on it inherits the fiction. The build steps below spend most of their effort getting to a defensible net capacity number, because once that number is right, the rest is arithmetic.
The columns a capacity planning template needs
Every workable version of this template, whether it plans a five-person team or a whole portfolio, carries the same seven kinds of data. Name them whatever fits your organization; keep all seven.
| Column | What goes in it | Why it matters |
|---|---|---|
| Person and role | Name plus the role you staff against (developer, analyst, architect) | Role is what lets you find the constraint later; a person-only list cannot be rolled up |
| Standard hours | Contracted hours per week: 40 for most full-time staff, actual contracted hours for part-timers and contractors | The ceiling everything else is subtracted from |
| Time off | Planned PTO, public holidays, training days for the period | Known absences are the easiest capacity loss to plan for and the most commonly ignored |
| Non-project load | Recurring meetings, admin, support rotation, line management, expressed as hours or a percentage | Typically 20 to 30 percent of the week; skipping it is the single biggest source of template fiction |
| Net capacity | Standard hours minus time off minus non-project load | The only availability number allocations may be compared against |
| Allocation per project | Hours (or FTE fraction) committed to each project in the period, one column or tab per project | This is where demand meets supply; it must cover every project, not just the big ones |
| Utilization | Total allocated hours divided by net capacity, as a percentage | The flag: over 85 percent is a warning, over 100 is a promise already broken |
How to build a capacity planning template in Excel or Google Sheets
The mechanics are identical in Excel and Google Sheets. Expect 30 to 60 minutes for the first build, then a few minutes a week to maintain.
- Lay out the grid. People down the rows, one row per person, with a role column beside the name. Time across the top: weeks for a single team, months for a portfolio view. Freeze the name and role columns so they stay visible as you scroll.
- Enter standard hours and subtract time off. Put contracted weekly hours in one column and planned absence hours per period in the next. Pull PTO from wherever it actually lives (your HR system, the shared leave calendar) rather than asking people to remember.
- Deduct non-project load to get net capacity. Ask each person to estimate their recurring weekly overhead: standing meetings, admin, support duty, mentoring. If you have no data, start at 25 percent and correct it over time. The formula is simple:
=(standard_hours - time_off) * (1 - overhead_pct). A full-time developer with no PTO and 25 percent overhead nets 30 hours, not 40. - Add the demand side. One column per project (or one tab per project if allocations change week to week), holding the hours each person is committed to it. Total them per person with
=SUM()across columns or=SUMIF()across tabs. Every project that consumes real hours goes in, including the small internal ones, because those are exactly the hours that never get counted. - Calculate utilization and make it visible. Utilization is
=total_allocated / net_capacity, formatted as a percentage. Add conditional formatting: red above 100 percent, amber from 85 to 100, and a second amber band below 50 percent, since chronic underallocation usually means work is happening off the books. - Add a role rollup and a review cadence. On a summary tab,
=SUMIF()net capacity and allocated hours by role. This rollup is the most valuable sheet in the file: it shows that you have plenty of developer hours but your two data engineers are booked at 140 percent. Then put a 15-minute weekly slot in the calendar to update actuals, because a capacity plan that is three weeks stale is worse than none; people trust it and it is wrong.
The exact Excel formulas
These are the four formulas that do all the work, written against the column layout in the downloadable file (column C is standard hours, D is time off, E is non-project load, F and G are project hours). Paste them into row 7 and drag down.
| Cell | Formula | What it gives you |
|---|---|---|
| Net capacity (H) | =C7-D7-E7 | Real available hours after leave and overhead |
| Allocated (I) | =SUM(F7:G7) | Total hours committed across every project column |
| Utilization (J) | =IF(H7=0,0,I7/H7) | The percentage, guarded against divide-by-zero for people who are fully on leave |
| Flag (L) | =IF(J7>1,"OVER",IF(J7>0.85,"AT RISK","OK")) | The line a manager actually reads |
| Role rollup | =SUMIF($B$7:$B$11,A15,$H$7:$H$11) | Net capacity summed by role, which is where the constraint hides |
Two Excel details worth knowing. Conditional formatting beats a flag column for scanning: select the utilization column, add a rule for greater than 100 percent in red and 85 to 100 percent in amber, and the overload finds you instead of the other way around. And if you plan in FTE fractions rather than hours, divide by net capacity in FTE (a person at 30 net hours in a 40-hour week is 0.75 FTE, not 1.0), because mixing hours and FTE in one sheet is the most common way these templates quietly lie.
A worked example
Here is a four-person slice of a template, planned weekly, with overhead already deducted from net capacity. The numbers are simplified but the pattern they show is the one you will find in your own data.
| Person | Role | Net capacity (hrs/wk) | Project A | Project B | Project C | Total | Utilization |
|---|---|---|---|---|---|---|---|
| Maya R. | Data engineer | 24 | 12 | 10 | 8 | 30 | 125% |
| Sam T. | Developer | 26 | 16 | 4 | 0 | 20 | 77% |
| Priya K. | Business analyst | 25 | 6 | 8 | 6 | 20 | 80% |
| Dan O. | Developer | 26 | 0 | 10 | 6 | 16 | 62% |
Read the average and everything looks fine: 86 hours allocated against 101 hours of capacity is 85 percent utilization, right at target. Read the rows and the plan is already broken. Maya is booked at 125 percent, which means all three projects that depend on her will slip, and they will slip in an order nobody chose. Meanwhile Dan has 10 spare hours that could absorb some of that load if any of Maya's work can move to a developer. That single insight, visible in a one-hour spreadsheet, is the entire case for capacity planning: averages hide constraints, and constraints set your delivery dates. The resource and capacity planning guide covers the method side of this in depth, including why you should plan at the level of the constrained role rather than total headcount.
Team, sprint, and portfolio capacity planning templates
The same seven columns power three different templates, and the mistake is using the wrong grain for the decision you are making.
| Variant | Scope | Time grain | Unit | Decision it supports |
|---|---|---|---|---|
| Team capacity template | One team, named people | Weekly | Hours | Who does what this week; who is overloaded |
| Sprint capacity template | One sprint, one team | Per sprint | Hours or points | How much the team can commit this sprint |
| Portfolio capacity template | All projects, planned by role | Monthly or quarterly | FTEs or hours by role | Which projects can be staffed at all, and in what order |
The portfolio variant deserves a note, because it is the one PMOs actually need and the one least often built. It does not track named people at all. It compares role-level supply (how many analyst FTEs exist per quarter) against role-level demand from every approved project, and its output feeds sequencing: if the ranked list from your portfolio prioritization process demands six data engineer FTEs in Q3 and you have four, something ranked lower moves to Q4, and the portfolio roadmap should show that move. A portfolio capacity template that does not change the roadmap is decoration.
Capacity planning in Jira or Confluence
Plenty of teams never open Excel, because their work already lives in Jira. You can do honest capacity planning there, but you have to know what the tools do and do not give you out of the box.
Jira's own capacity feature works at the sprint and board level. In a scrum board's sprint planning view, you set a team capacity in story points or hours, and Jira warns you when the committed backlog exceeds it. Advanced Roadmaps (available on Premium and Enterprise plans) goes further: you can define teams, set a weekly capacity per team, and see a capacity bar per sprint or per month across a whole plan. That is genuinely useful for one delivery train. What it does not do is model a named specialist who is 40 percent on your project and 60 percent on someone else's, because Jira thinks in teams and boards, not in a shared resource pool.
Confluence has no capacity engine at all. What people mean by a Confluence capacity planning template is a page holding a table, usually one row per person and one column per week, kept alongside the team's other documentation. It is fine as a lightweight, visible artifact, and its real advantage is that the plan sits next to the meeting notes and decisions that produced it. Its real weakness is that a Confluence table does not calculate. There is no formula that subtracts leave from contracted hours, so every net capacity number is typed by hand and quietly goes stale.
| Where you build it | Calculates automatically | Handles people split across projects | Best for |
|---|---|---|---|
| Excel or Google Sheets | Yes, full formulas | Yes, with an allocation column per project | One team up to a small portfolio, 30 to 50 people |
| Jira sprint capacity | Yes, against the sprint commitment | No, it is board scoped | A single scrum team planning one sprint |
| Jira Advanced Roadmaps | Yes, per team per sprint or month | Partly, teams rather than named people | Several teams inside one delivery plan |
| Confluence table | No, every figure is manual | Only if you type it | A visible, low-maintenance summary next to the docs |
| PPM software | Yes, across every project at once | Yes, that is the point of it | A real portfolio with shared specialists |
The pragmatic setup for most PMOs is a spreadsheet that owns the math and a Confluence page that publishes the result. Do the calculation where formulas exist, then paste the role-level summary where the team will actually see it. What you should not do is maintain the same numbers in both places and expect them to agree by Friday.
The capacity report: one page for the steering committee
The template is a working file. The capacity report is what comes out of it, and it is a different artifact with a different reader. Nobody on a steering committee wants 40 rows of named people. They want to know one thing: can we say yes to what we are about to approve?
A capacity report template answers that in a single view, by role rather than by person:
| Role | Capacity (FTE) | Committed demand | Gap | Status |
|---|---|---|---|---|
| Data engineer | 4.0 | 6.2 | -2.2 | Over |
| Business analyst | 5.0 | 4.4 | +0.6 | OK |
| Solution architect | 2.0 | 2.9 | -0.9 | Over |
| QA engineer | 3.0 | 1.8 | +1.2 | Under |
Four columns and a status, with the same figures reproduced by quarter so the committee can see when the gap arrives rather than only that it exists. The figures are illustrative, but the shape is the point. A negative gap is not an error to be smoothed over before the meeting. It is the whole reason the report exists: it converts "we are stretched" into "we are 2.2 data engineers short in Q3, so one of these three approved projects slips unless we hire or buy the skill in." Pair the report with a named decision, and it belongs in the portfolio review meeting pack rather than in someone's inbox.
When the spreadsheet stops being enough
A well-kept template comfortably serves one team, and it stretches to a small portfolio: roughly 30 to 50 people, a dozen active projects, one person curating it. Beyond that, three failure modes show up on schedule. Allocations for shared specialists live on multiple tabs and quietly sum past 100 percent. The file forks, and two versions of the truth circulate. And updating actuals becomes a half-day job, so it stops happening. Those failure modes, not company size, are the signal to move the capacity model into PPM software, where allocations across projects share one database and conflicts surface automatically. Whichever side of that line you are on, the utilization-by-role view belongs in your monthly portfolio reporting, because capacity is the constraint behind most of the schedule variances the report will otherwise explain badly.
One limit of this template is worth naming, because it catches people out. It plans the supply you have against the work you have already committed to. It says nothing about the projects still sitting in the pipeline, and those are the ones that create next quarter's shortage. When you need to see a gap before it arrives, in time to hire against it, the demand-side companion to this sheet is resource forecasting, which probability-weights unapproved work by the gate it has passed and projects the shortfall by role.
Frequently asked questions
How to create a capacity plan in Excel?
Create a capacity plan in Excel by listing team members in rows and weeks in columns, entering each person's contracted hours, subtracting planned time off and 20 to 30 percent for meetings and admin to get net capacity, then adding allocated hours per project and a utilization formula. Conditional formatting above 85 percent turns the sheet into an early-warning system. The six steps above walk through each formula.
How to calculate capacity planning?
Capacity is calculated as available supply against demand: net capacity equals standard hours minus time off minus non-project overhead, and utilization equals allocated hours divided by net capacity. A person with 40 contracted hours, no leave, and 25 percent overhead has 30 hours of net capacity; 27 allocated hours puts them at 90 percent, which is already past a sustainable planning target.
What is a capacity planning model?
A capacity planning model is the structured comparison of resource supply against work demand over time, at a chosen level of detail: named people and hours for a team, roles and FTEs for a portfolio. A template is simply that model implemented in a spreadsheet. The model's quality depends on honest availability numbers and a demand list that includes every project, not on the sophistication of the tool holding it.
How to do capacity planning in agile?
Agile teams plan capacity per sprint: take each member's working days in the sprint, subtract leave and ceremony time, convert to hours or use the team's historical velocity, and commit only to what fits. The same net-capacity logic applies; the grain is just shorter. Portfolio-level capacity planning still matters above the sprint, because velocity tells you a team's throughput, not whether you have enough teams.
How do you do capacity planning in Jira?
Jira handles capacity at two levels. On a scrum board, set the sprint capacity in points or hours and Jira flags a commitment that exceeds it. In Advanced Roadmaps, define teams, set a weekly capacity for each, and the plan shows a capacity bar per sprint. Neither models one specialist split across several projects, so a shared resource pool still needs a spreadsheet or PPM software.
What is a capacity report?
A capacity report is the one-page summary produced from a capacity plan, showing available capacity against committed demand by role rather than by person, usually with a gap column and a status flag. Its job is to support a decision: whether the portfolio can absorb the work already approved, and which projects move if it cannot. Keep it to roles, quarters, and gaps.
Can you build a capacity planning template in Confluence?
You can, but a Confluence table does not calculate, so every net capacity figure is entered by hand and drifts out of date. The workable pattern is to keep the formulas in Excel or Google Sheets and publish only the role-level summary to Confluence, where it sits next to the decisions and meeting notes. Maintaining the same numbers in both places guarantees they disagree.
What should a capacity planning template include?
A capacity planning template should include each person's name and role, standard contracted hours, planned time off, non-project overhead, calculated net capacity, allocated hours per project, and a utilization percentage with visual flags. Add a role-level summary tab for portfolio decisions. Leave out task-level detail; the moment a capacity template tries to be a project plan, it stops being maintained.