A capacity planning template for teams of five to fifty
Most capacity planning templates found online are built for a resource manager at a company with four hundred people. They have tabs for scenario modelling, a section for capital equipment, and a utilisation dashboard that assumes somebody is entering actual hours every day. A team of fourteen downloads one, fills in about a third of it, and abandons it three weeks later.
The problem is not the spreadsheet. It is that a template designed for a role nobody on a small team holds will always cost more to maintain than it returns. What a small team needs is narrower: a grid that answers whether the people available over the next six to eight weeks can perform the work that has been promised, and shows which specific week the answer turns into no. This article sets out the columns that grid needs, the numbers that go in them, how to read the result, and the point at which a spreadsheet should be replaced by something else.
What the template is actually for
A capacity planning template compares supply against demand over a period. Supply is the hours the team genuinely has. Demand is the hours the committed work genuinely needs. The output is a gap, positive or negative, per person per week.
That is the whole mechanism, and keeping it that narrow is what makes it survivable. Three things that often get bolted on belong elsewhere.
Actual time tracking. Recording what people really spent is a different exercise with a different purpose, usually billing or estimation accuracy. Mixing it into the capacity template doubles the data entry and makes the forward looking view depend on backward looking discipline that small teams rarely sustain.
Task level detail. The template works at the level of person, week and project. Breaking demand down into individual tasks makes the grid enormous and does not improve the answer, because the error in the availability figures is larger than the precision gained.
Financial modelling. Cost per hour, margin by project and revenue forecasting are real questions and they belong in their own sheet. A capacity grid that also tries to be a P&L becomes a document only its author can read.
There is one distinction worth being clear about before building anything. Capacity planning asks whether the team as a whole can absorb the work. Resource allocation asks which named person does which piece. They use much of the same data and they answer different questions, and a template that tries to do both at once tends to do neither well. Build the capacity view first, because it is the one that catches a problem early enough to act on.
The columns the grid needs
A workable template is two blocks of columns sitting side by side, with one row per person.
The left block is fixed information about the person.
Name. One row per person, including part timers and regular contractors. Leaving contractors out is a common omission that makes the picture look worse than it is.
Contracted hours per week. The nominal figure, recorded only as a starting point.
Standing commitments per week. Recurring meetings, support rotation, line management, interviewing, administration. This is a single number and it is the most important one in the template. For most knowledge work it lands between eight and eighteen hours a week.
Net available hours. Contracted minus standing commitments. For a full time person doing project work this typically lands between twenty and thirty hours. If the figure comes out above thirty-two, the standing commitments have been underestimated rather than the person being unusually free.
The right block is one column per week, running six to eight weeks forward. Each cell holds two values: hours available that week, and hours committed that week.
Available hours for the week is net available hours minus known absence. Holiday, public holidays, training, conferences. Recording absence is the cheapest accuracy available and the most frequently skipped.
Committed hours for the week is the sum of what that person is expected to spend on work already agreed. This is the number that requires judgement, and the practical approach is to size work in bands rather than estimating precisely. Roughly a day, roughly a week, roughly a month is enough resolution, spread across the weeks the work is expected to span.
The cell then shows the difference. Negative means that person is oversubscribed in that week, and the week it first turns negative is the output of the entire exercise.
Setting the availability figure honestly
Everything the template produces depends on the net available hours figure, and it is where almost every capacity plan goes wrong. Planning against contracted hours does not produce an optimistic plan, it produces an impossible one, and the error compounds across every week in the grid.
Three sources of error account for most of the gap.
Meetings are counted, the time around them is not. A thirty minute meeting in the middle of an afternoon costs considerably more than thirty minutes, because the hour before it is not long enough to start anything substantial. Teams that measure this typically find the real cost is closer to double the calendar time. Blocking meetings into a smaller number of days helps more than shortening them.
Interruption work is invisible. Support questions, code review, unblocking a colleague, answering a client email. It appears on no plan and consumes a genuine share of the week. It does not need tracking in detail, only an allowance in the standing commitments column.
Nobody is productive for a full day. Even with a clear calendar, sustained focused work runs to around six hours for most people. A template that assumes eight is wrong before any other error is introduced.
The most reliable way to set the figure is to look backwards once. Take a recent month, pick two or three people, and work out roughly how many hours actually went into project work rather than everything else. Whatever ratio that produces is the team's real number, and it is usually lower than anyone expected. Setting it from a benchmark found online is guessing, and the whole template inherits the guess.
Reading the result without overreacting
The grid will show red weeks. What matters is which kind of red it is, because the responses are different.
One person negative in one week. Usually noise. Work moves by a few days, estimates are bands, and a single negative cell is inside the error of the method. Note it and check again next week.
One person negative across several consecutive weeks. A real allocation problem. That person is holding more than they can carry, and it will surface as a missed deadline in whichever project is least visible. Move work, cut something, or move a date.
Most of the team negative in the same weeks. The team has been promised more than it can perform. This is not solvable by reallocating, and treating it as an allocation problem wastes the time in which it could still be fixed by moving a delivery date.
Persistently positive across the whole grid. Either the demand figures are missing something, which is far more likely, or there is genuine free capacity. Check the demand side before concluding the second.
There is also a reading that looks like good news and is not. A grid where every cell sits at exactly zero, with supply and demand matching perfectly week after week, is a grid that has been tuned rather than measured. Real work varies, so a real grid is untidy. A perfectly balanced one usually means the demand figures were adjusted until they fitted the available hours, which removes the only thing the template was built to detect.
The single most useful habit when reading the grid is to look at the first negative week rather than the worst one. The first negative week is where the cheap intervention exists. By the time the grid reaches the worst week, the options have narrowed to the expensive ones.
When the spreadsheet stops being the right home
A spreadsheet template is genuinely correct for a lot of teams, and switching away from it too early is a real cost. It is worth being specific about what the actual trade is.
| Where capacity lives | Suits | What goes wrong |
|---|---|---|
| Spreadsheet built for the team | Five to fifteen people, priorities changing monthly | Diverges from the real task list within a fortnight, and the version in the shared drive stops matching the one being edited |
| Downloaded enterprise template | Almost nobody under fifty people | Two thirds of the fields are never filled, and the unfilled parts make the rest look unreliable |
| A capacity view inside the tool holding the work | Owners and dates already maintained in one place | Only works if the view puts people on one axis and dates on the other; a list grouped by assignee is not a capacity view |
| Dedicated capacity planning software | Fifty or more people, or billable utilisation reported to the business | Administration and licence cost exceed the value below that size |
The decisive factor is duplication rather than features. A spreadsheet requires someone to copy facts that already exist elsewhere, which is why it drifts. The moment the grid and the task board disagree, people trust neither, and the grid is the one that gets abandoned because it is the one nobody has to open to do their job.
If the work already has owners and dates in a board, a capacity view drawn from those same cards removes the copying entirely. What to check is the specific shape of that view, since a timeline showing projects and a grid showing people are different things and capacity planning needs the second. It is also worth confirming whether that view is included or sits behind an upgrade, which the feature list and a comparison of the alternatives will answer faster than a trial will.
Building the first version in an hour
The version that works is the one that exists. A complete template nobody fills in is worth less than a rough one that gets looked at every Monday.
Start with one row per person, the availability columns, and six weeks across the top. Fill in absence first, because it is factual and takes ten minutes. Then put in the committed work at the level of project rather than task, using bands rather than estimates. Then look at where it goes negative first.
Two rules keep it alive. Update it once a week at a fixed time, because a template updated when someone remembers is a template that stops being updated. And resist adding columns. Every additional column raises the maintenance cost, and the cost is what kills these documents, never the lack of detail.
What to change first
Set the net available hours figure honestly before building anything else, because every other number in the template inherits that error. Then put six weeks of committed work into a grid of people against weeks and find the first negative cell. If owners and dates already live in a board, use a capacity view built from the same cards rather than copying them into a spreadsheet, and check the pricing page for whether it is included. Pinateca puts people against dates alongside kanban, Gantt and calendar boards in the free plan, which covers up to five people and ten boards.
Q1. What should go in a capacity planning template at minimum?
One row per person, their net available hours per week, and one column per week for six to eight weeks ahead holding available hours and committed hours. Everything beyond that is optional and raises the maintenance cost. Known absence is the one addition that always pays for itself, because it is factual and takes minutes to enter.
Q2. How many hours a week should be entered as available?
Contracted hours minus standing commitments, which usually lands between twenty and thirty for a full time person doing project work. The reliable way to set it is to look at a recent month for two or three people and work out how much actually went into project work. A figure above thirty-two almost always means the standing commitments were underestimated.
Q3. How far ahead should a capacity plan look?
Six to eight weeks for a small team. Beyond that the committed work is not firm enough for the numbers to mean anything, and maintaining the grid costs more than the accuracy is worth. A coarse view of which project needs which skills in which month covers the longer horizon adequately.
Q4. What is the difference between capacity planning and resource allocation?
Capacity planning asks whether the team as a whole can absorb the work that has been promised. Resource allocation asks which named person does which specific piece. They share most of the same data, but capacity planning catches the problem earlier, which is why it is worth building first even though allocation feels more urgent.
Q5. When is a spreadsheet no longer good enough?
When keeping it current means copying facts that already live somewhere else. That is usually around fifteen to twenty people, or sooner if priorities change weekly. The signal is not size but drift: once the grid and the actual task list disagree and nobody reconciles them, the spreadsheet has already stopped being the source of truth.