“How many days do you need from us?”
You will hear this question a lot when planning a new project. Every team responsible would like to know how much they have to contribute.
The tool to use to answer this question is a project capacity planning sheet. In this article I’ll show you what my template looks like and it should be used.
(If you need a team capacity planner, see our team planner)
Project capacity planning template
The capacity planning sheet allows you to maintain the labor effort for each team member. The numbers should come from your project effort estimation. You can add department information and calculate the total resource requirements for every department. This is helpful for getting approvals from the department manager for the so-called resource contracting.
Using the template
Get the Excel sheet by clicking the buy button below. Using the file is pretty straightforward. Begin by filling in the team member’s names in the very left column. Add department to be able to separate the data.
Then you enter the needed efforts in the respective columns for every month.
- Be aware that capacity values must be entered in the form of percentages, not as days.
- For example, 0.8 means you are using a resource for 80% of the time in a given month. 1.0 would mean that the employee is dedicating his full schedule for a month to your project.
Adjust the number of work days per month
One thing you might want to do at the start: Entering the actual number of working days for every month. This is a prerequisite for an accurate calculation of labor effort. Per default, the template assumes the number of working days to be 20 (= 20 working days) But that will vary from month to month. Check Google to get the number of work days for your location.
How are the totals calculated?
If you scroll to the right you will see the totals column. Here you can see the total effort (in days) for every resource.
The values aren’t simply added up from the columns, because you can’t add up percentages and get a meaningful result.
Instead, the capacity planner uses the SUMPRODUCT formula. It multiplies the number of work days per month with the entered percentages. So, for example if there are 20 working days in May and John works on the project for 20% (0,2) of the time, then the Excel calculates 20 days x 0,2 and does so for every month to get the total effort.
What else can you do with the planning sheet?
Using the data, you can easily create beautiful charts in Excel to visualize the numbers. You can also create pivot tables to allow for an easy analysis of effort information. Especially useful in large projects with a lot of departments involved.
Did you like the capacity template?
Your feedback is very important for me, It helps me understand what your needs are and whether you like the content I’ve created on Tactical Project Manager. Leave a comment and let me know what you think!