Team capacity planner by week

What does this template do?

A team capacity planner shows whether the people on a team have the hours for the work planned for them. It is for team leads, project managers and resource planners who plan in weeks rather than months.

The Capacity tab holds standard weekly hours and leave for up to twenty-five people. It calculates the available hours for twelve weeks by subtracting leave. The Assignments tab holds planned hours by project, person and week. The Plan tab sums each person's hours for each week with SUMIF, divides them by the available hours to give utilization, and flags each person-week as Over above 100% or Under below a threshold you set.

The Plan tab also totals each week for the team and the hours by project. It reports team utilization, the number of over-allocated person-weeks and the unassigned hours. The example is a fictional team of eight people with twenty-two assignment rows over twelve weeks.

What’s inside

• Available hours by week: standard hours less leave and holidays
• Utilization for each person and week, with a flag of Over, Under or OK
• Team totals and team utilization for each of the twelve weeks
• Hours by project across the same twelve weeks
• Role filled in from the people list, with a check for names that do not match

Which tabs does the workbook have?

What formulas does this template use?

This template holds 1,982 formulas in 2,494 cells across 4 tabs, so 79% of its cells calculate. They use 9 distinct functions; the longest formula is 223 characters and 1,236 of them read from another tab.

Counted from the workbook itself. Only functions that Excel, LibreOffice Calc, Google Sheets and Apple Numbers evaluate the same way are used, so the formulas survive every download format.

How do you use it?

1. On the Capacity tab, set the plan start date, which should be a Monday.
2. Enter each person's role, standard weekly hours and leave for each week.
3. On the Assignments tab, enter the project, the person and the planned hours for each week.
4. On the Plan tab, read the team tiles, then the utilization and flag blocks for each person.

What is it good for?

• Checking whether a team can take on a new project next quarter
• Spotting weeks when one engineer is over-committed
• Planning around holidays and planned leave
• Showing how the team's hours split between projects and run work

Questions about this sheet