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.
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.
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.
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.
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.
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.