Workforce Capacity Planning Template for Excel: Demand, Availability and Utilisation Tracking
Capacity PlanningResource ManagementWorkforce PlanningUtilisationOperations

Workforce Capacity Planning Template for Excel: Demand, Availability and Utilisation Tracking

EExcels.uk Editorial Team
2026-11-20
6 min read

Build an Excel capacity planning template to compare team availability, demand, utilisation, allocations and skill-specific gaps each week.

A workforce capacity planning template for Excel helps you compare available team hours with incoming work before over-allocation becomes a missed deadline. This guide shows how to structure a repeatable workbook for availability, leave, non-project time, skills, demand, allocation, utilisation and capacity gaps, with practical formulas and a weekly review process.

Overview

Capacity planning is the process of matching the work you expect to receive with the time and skills your team can realistically provide. It is different from simply listing tasks or recording timesheets: the purpose is to identify future pressure early enough to change priorities, redistribute work or consider additional capacity.

A useful resource capacity planning spreadsheet should answer five questions:

  • How many workable hours does each person have in the planning period?
  • How much of that time is already committed to meetings, administration, leave or other non-project work?
  • How many hours of demand are expected, and when are they needed?
  • Where is work assigned to people with the required skills?
  • Which individuals, teams or weeks have a capacity gap?

Build the workbook around separate input tables rather than one large manually edited grid. A practical structure is:

  1. Team: person, role, team, working pattern, hourly cost if required, and relevant skills.
  2. Availability: week or date, contracted hours, leave, public holidays where relevant, and other unavailable time.
  3. Non-project time: meetings, training, management, support and administration.
  4. Demand: project, task, due week, estimated hours, priority, required skill and status.
  5. Allocation: person, task, week and planned hours.
  6. Summary: capacity, allocated hours, utilisation, unassigned work and capacity gap.

Keep assumptions visible in a dedicated area. This makes the file easier to audit and allows managers to change planning rules without rewriting formulas.

How to estimate capacity and demand in Excel

Start with gross working hours for each person and planning period. If a full-time employee is scheduled for 37.5 hours in a week, the gross figure is 37.5. For a part-time employee working three equal days, enter the agreed weekly hours rather than applying a full-time assumption.

A simple net capacity formula is:

Net capacity = Contracted hours - Leave hours - Non-project hours

In an Excel table, the equivalent might be:

=[@[Contracted Hours]]-[@[Leave Hours]]-[@[Non-project Hours]]

Demand is the total estimated effort required in a period. Use task-level estimates where possible, then aggregate by week, project, skill or team. A SUMIFS formula can total demand for a selected person or week. For example:

=SUMIFS(Allocation[Planned Hours],Allocation[Person],[@Person],Allocation[Week],[@Week])

Planned utilisation shows how much net capacity has been assigned:

Utilisation = Planned project hours / Net capacity

In Excel:

=IFERROR([@[Planned Project Hours]]/[@[Net Capacity]],0)

Format the result as a percentage. A capacity gap can be expressed as:

Capacity gap = Net capacity - Planned project hours

A negative result indicates over-allocation. A positive result does not automatically mean that the person is free for any task: check their skills, location, working pattern, priority commitments and the timing of the available hours.

For unassigned demand, compare total required hours with hours already allocated. At task level:

Unassigned hours = MAX(0, Estimated hours - Allocated hours)

Use conditional formatting to highlight negative capacity gaps, unassigned hours greater than zero and utilisation above the threshold selected by your organisation. Treat thresholds as planning controls, not universal productivity standards. A team may need protected time for quality checks, urgent support or handovers.

Inputs and assumptions

The quality of a team workload tracker depends more on its assumptions than on its formulas. Record the following inputs clearly:

  • Planning period: weekly planning is often easier to maintain than a long daily schedule, while deadline-sensitive work may need a daily view.
  • Working pattern: use each person’s actual contracted hours, days and recurring constraints.
  • Leave and absence: enter approved leave and known unavailable periods before allocating work.
  • Non-project time: include recurring meetings, line management, training, support queues and operational administration.
  • Demand confidence: distinguish committed work from probable or exploratory work using a status or probability field.
  • Estimate quality: record whether an estimate is rough, reviewed or based on a comparable task. Avoid presenting uncertain estimates as precise forecasts.
  • Skills and dependencies: capacity is only usable when the person can perform the work and any required predecessor work is available.

Do not hide a shortage by increasing assumed availability. If the same person is needed across several projects, show the conflict in the allocation table. Likewise, do not treat every unallocated hour as spare capacity if it is reserved for customer support, incident response or management responsibilities.

For scenario planning, duplicate the summary or add a scenario column such as Base, High Demand and Hiring Case. A hiring scenario should include an assumed start date, ramp-up period and available hours rather than counting a new person at full capacity immediately. Keep scenario outputs separate from the approved plan so that provisional assumptions do not become commitments by accident.

Worked examples

Example 1: part-time availability

Suppose a team member has 22.5 contracted hours in a week, 3 hours of leave and 4.5 hours of recurring meetings. Net capacity is:

22.5 - 3 - 4.5 = 15 hours

If 14 hours are allocated to project tasks, planned utilisation is approximately 93%. The remaining one hour is not necessarily enough for a new task, particularly if work is split across several days. The summary should show 15 hours of capacity, 14 allocated hours and a one-hour positive gap, while the allocation view shows how those hours are distributed.

Example 2: demand exceeds team capacity

A project has 64 hours of estimated work due in a week. The team has 52 hours of relevant net capacity, but only 44 hours have been allocated. The workbook should report 12 hours of unassigned demand, not simply 8 hours of spare capacity. The apparent spare time may help with the gap, but the remaining shortfall still requires a decision: move the deadline, reduce scope, reassign suitable work or add capacity.

Example 3: skill bottleneck

Three people have a combined 30 hours of unallocated capacity, but a testing task requires a specialist skill held by only one person. If that specialist has 4 available hours and the task requires 10, the organisation has a 6-hour skill-specific gap even though total team capacity appears sufficient. Summarise capacity by skill as well as by person to reveal this type of bottleneck.

Link the capacity summary to a project status report so that resource risks sit alongside milestones, budget and delivery risks. You can also compare planned hours with actual delivery in a later review. Planned utilisation is a forward-looking allocation measure; it is not the same as productivity or actual time spent.

When to recalculate and update the workbook

Review the workforce capacity planning template at least weekly, using a consistent cut-off point. Update approved leave, new tasks, changed estimates, completed work, priority changes and allocations. A short weekly checklist is:

  1. Refresh team availability and absence inputs.
  2. Add new demand and mark its confidence or commitment status.
  3. Remove completed work and revise estimates for unfinished tasks.
  4. Check negative capacity gaps and unassigned hours by week, project and skill.
  5. Review people with unusually high or low planned utilisation.
  6. Rebalance work while protecting critical deadlines and dependencies.
  7. Record decisions, owners and follow-up dates.

Recalculate immediately when a major project is approved, a deadline moves, a team member joins or leaves, working hours change, or priorities are materially reordered. Review the workbook’s assumptions annually and whenever workplace-planning guidance, internal working arrangements or reporting definitions change.

Finally, compare the plan with actual delivery often enough to improve future estimates. Look for recurring patterns rather than judging a single week: tasks that routinely take longer, meetings that are omitted, support work that is underestimated, or skills that repeatedly constrain delivery. A well-maintained spreadsheet template becomes a decision record, not just a coloured calendar. Used consistently, it gives managers an early view of pressure and a clearer basis for discussing priorities, staffing and realistic deadlines.

Related Topics

#Capacity Planning#Resource Management#Workforce Planning#Utilisation#Operations
E

Excels.uk Editorial Team

Business Planning Editor

Senior editor and content strategist. Writing about technology, design, and the future of digital media. Follow along for deep dives into the industry's moving parts.