Optimize resource allocation based on team availability and project demands
✓ Free to download · ✓ Excel 2016+ and Google Sheets · ✓ Custom version in 24h
Capacity planning fails when availability is assumed rather than calculated. This planner starts from contracted hours, removes leave and non-project time, and only then allocates — so the plan is against hours that actually exist.
The actual columns, with sample rows
| A | B | C | D | E | F | G |
|---|---|---|---|---|---|---|
| 1 | Person | Contracted | Leave | Non-project | Available | Allocated |
| 2 | A. Duval | 160 | 16 | 24 | 120 | 134 |
| 3 | K. Owens | 160 | 0 | 20 | 140 | 112 |
| 4 | M. Ferrer | 128 | 8 | 18 | 102 | 98 |
| 5 | S. Novak | 160 | 40 | 22 | 98 | 104 |
Why each one is written the way it is
=[@Contracted]-[@Leave]-[@[Non-project]]
Available hours. Planning against contracted hours is the most common capacity error — it assumes nobody has meetings, holidays or admin.
=SUMIFS(Alloc[Hours],Alloc[Person],[@Person],Alloc[Month],[@Month])
Allocated hours from the assignment sheet, so a person's load is the sum of what they were actually given.
=IF([@Available]=0,"",[@Allocated]/[@Available])
Utilisation. Anything sustained above about 85% has no absorption capacity, and the first slipped task cascades.
=IF([@Allocated]>[@Available],[@Allocated]-[@Available],0)
Overallocation in hours. Reporting the hours rather than the percentage makes the conversation concrete: two people are 18 hours over, not "we are a bit stretched".
A worked example with real numbers
An agency planned against a 40-hour week and could not understand why deadlines slipped at reported 90% utilisation. Subtracting leave, standups, reviews and internal work left 30 available hours; real utilisation was 120%. Nobody was underperforming — the plan had been assigning hours that did not exist. Reducing committed work by one project restored delivery dates.
Tab by tab
| Tab | Contents |
|---|---|
| Capacity | Contracted, leave, non-project and available hours per person. |
| Allocations | Assignments by person, project and month. |
| Leave | Booked absence feeding the capacity calculation. |
| Heatmap | Utilisation by person and month, with overallocation flagged. |
| README | What counts as non-project time and how to calibrate it. |
What is included, and what to look at next
Specific to this workbook, not generic
70–80% of available hours for delivery roles. Above 85% there is no slack, and any variation — illness, a support escalation, a rework cycle — turns directly into a missed date.
Measure it for two weeks rather than guessing. Most teams find it is 15–20% of contracted hours, and most plans assume 5%.
Only in the contracted-hours column. Every downstream formula works on available hours, so part-time and full-time are treated identically once availability is calculated.
"This capacity planner has revolutionized how we manage our team resources. We've improved utilization rates while maintaining healthy work-life balance for our team."