Resource Capacity Planner Excel Template – Step by Step User Guide
In this blog post, we will see step by step how we can use the Resource Capacity Planner Excel template. We will take a simple example planning scenario and see how the template can help us in smarter planning.
The Resource Capacity Planner template is designed to determine the available resource capacity, compare with demand and identify surplus/deficit in capacity. It also allows us to modify the resource availability or demand (to meet our objectives) and see impact of the changes instantly.
For more on template’s features, please visit the product page.
This user guide is for the latest version v3 of the template. If you are using a previous version and need the new file, please email email@example.com with your Order Number. If you need support with the previous version, please email your questions to firstname.lastname@example.org
Overview of steps:
- Enter Settings
- Enter Resource list with standard availability and allocate Capacity to Skills and Projects
- Enter vacation and overtime
- Enter Demand (tasks) data
- Refresh Calculations
- View Dashboard to evaluate plan
- Address any over-utilization and under-utilization
- View Calendar to ensure desired utilization at granular time periods
Step 1: Enter Settings
In the Settings sheet, we will enter the basic settings.
We will set the planning period by entering a Start Date and End Date. The template allows a maximum of 2-year planning period.
We will use a 3 month period from Apr 1, 2019 to June 30, 2019.
In most work places, we have days during a week where we don’t work (also called weekends). We can choose which weekdays are weekends from the list of options available. The template will set the availability of resources to 0 by default.
If you don’t have any weekends, then leave this input blank. Just click the cell and press the Delete key. It will remove the contents of the cell. Please do not delete the column or row.
We will choose SAT & SUN as weekends for this tutorial.
If a resource works during a weekend day, we can enter that as overtime in the VACATION_OVERTIME sheet.
In the next set of inputs, we use Excel Tables. Entering the data correctly inside the Excel tables is critical to the functioning of the template. If you are new to Excel Tables, please see our article on How to use Excel Tables for data entry?
Tip: Start entering data right below the Header of each Table
Since most companies have holidays (other than weekends), it is important that we factor that in when we calculate capacity. We can enter our company holidays and the template will set the availability of resources to 0 by default on those days. Each holiday must be entered individually.
We will enter a couple of days in the Holidays table.
If a resource works during a holiday, we can enter that as overtime in the VACATION_OVERTIME sheet.
The template allows managing capacity for multiple projects.
We will enter 2 projects, to keep it simple.
Skill Group is how we can group resources for planning.
Skill Group and Skill are used interchangeably in the template.
In our example, we have resources from 2 skill groups: Project Mgmt and Development.
Enter list of Resources available for planning.
The template uses the term ‘Resource’. The most commonly used resource in most scenarios is the human resource (can be called as Employee). But a machine could be a resource too. Hence instead of using ‘Employee’, the term ‘resource’ is generally used.
Let’s assume we have 6 resources in total.
Step 2: Enter Resources’ Capacity
Now, we move to the Capacity sheet in the template.
We must enter the resources, provide start and end dates of availability, available hours every weekday and other information related to capacity.
Tip: To help with data entry, you can copy the values you entered in the Settings sheet, for Resources, and then paste in the Capacity sheet. Please make sure that you paste as values.
After copying the values, right click on cell A4 in Capacity Sheet. In the menu that pops up, choose Values under Paste Options. This ensures that only the values are carried over, without impacting the formatting and drop-down validations.
After entering resource names, we enter the Start Date of each resource.
Resources can be hired anytime and similarly resources may leave a company anytime. The template will assign 0 availability before start date and after end date.
We enter the number of hours each resource will be available each weekday. For example, Project Planner 1 is available 8 hours a day from Mon to Fri. The template can handle varying hours by weekday as well.
We assign the 6 resources each to a Skill Group and Project.
We can provide the End Date of each resource if there is an End Date for the availability. If no end date is provided, the template will assume that the resource is available through the end of the planning period.
Finally, we enter the Cost per Hour for each resource.
Important: The last column is a calculated field that is critical to the functioning of the template. Please do not edit/delete it. If you don’t want to see it, you can hide the column
How to allocate same resource to multiple skills?
You can allocate each resource to one or more skills.
To allocate one resource’s capacity to multiple skills, add a new row to the table and assign a different skill. Ensure the available work hours are distributed between the two skills.
For example, if Developer 4 was available 6 hours in total and can perform Development role for 4 hours and Project Mgmt role for 2 hours, we would enter as shown below.
Resource name will be same Developer 4, but work hours may vary, and Skill Group will be different. In this case, we assigned to same Project B.
How to allocate same resource to multiple Projects?
You can allocate each resource to one or more Projects.
To allocate one resource’s capacity to multiple projects, add a new row to the table and assign a different Project. Ensure the available work hours are distributed between the two Projects.
For example, if Developer 4 was available 6 hours in total (Development skill), and will work 4 hours for Project B and 2 hours for Project A, we would enter as shown below.
Resource name will be same Developer 4, but work hours may vary, and Project will be different.
How to allocate same resource to multiple Projects and Skills?
You can allocate same resource to one skill in one project and a different skill in another project.
For example, if Developer 4 was available 6 hours in total, and will work 4 hours in a Development role for Project B and Project Mgmt role for 2 hours for Project A, we would enter as shown below.
How to shift same resource to a different Project after a certain date?
If we want a resource to work in one project for a certain date range and then change to a different Project, we can use the same approach. We can use the END DATE column to enter the end date of a certain Project assignment and then enter a new row with a different project.
In the above illustration, Developer 4 works on Project B for 6 hours a day until 30th Apr. From May 1st, the resource works 6 hours a day on Project A.
You can apply the same method if you want a resource to change skill from a certain date.
Let’s go with the simple setup now where each resource is assigned to only one skill and one project.
Now, the above examples would have made it clear that this Capacity table can have more than 1 record per resource. Though the default Resource limit is 100, this table can accept 300 entries.
For example, if you have 50 resources, each resource assigned to 2 skills on average and 3 projects on average, that would require 50 X 2 X 3 = 300 entries.
300 unique Resource-Skill-Project combinations can be entered.
Step 3: Enter Vacation and Overtime
Though we have entered standard availability above, we know that employees may take vacation or sometimes work overtime.
We can enter any vacation or overtime taken by resources in the VACATION_OVERTIME sheet.
Project Planner 1 is taking 3 days off (8 hrs each day) from 16th Apr to 18th Apr.
Two important things to note here:
- It is 8 hrs of vacation each and hence totally 24 hours in total for all 3 days. However, we only enter 8 hrs as the column is for daily hours. The template will calculate automatically the total.
- If the employee’s vacation hours vary over the three days, then we must create separate rows.
- We enter -8 since it is vacation. If it is overtime, we will enter 8.
- If vacation hours for a day is greater than the standard availability of that resource, then the result will be set to 0 to avoid negative availability.
Project Planner 2 is taking one day off (8 hrs) on 21st May. We enter 21st May as Start and End Dates. It is important that we fill both dates.
Finally Project Planner 2 also does overtime on 25th May. We enter 8 (not -8) in Hrs, since this is overtime.
This 25th May is a Saturday and is a weekend day. However, overtime can be done on weekends and holidays as well. The template supports that.
Overtime is positive to capacity and Vacation is negative to capacity.
This completes our capacity inputs. Now, we move to the Demand data input.
If you have assigned a resource to multiple skills and/or projects, you will have to enter vacation or overtime separately for each combination. This allows more flexibility in capacity planning as you can enter overtime only for a certain project instead of both projects (assuming resource assigned to 2 projects).
Step 4: Enter Demand data
In this template, demand is entered as Tasks in the Demand sheet.
Required fields: Date of the task, Resource assigned to do that task, Hours of work required, Project assigned and Skill Group.
Detail column is optional to enter any details about the task or any notes.
The three calculated cells on the right should not be edited or modified. That would break the functionality of the template. Please do not delete or edit these columns.
An important concept to understand is planning granularity. We have entered daily level of tasks in the above image. What level of tasks to be entered depends on the planning granularity you need.
When we say ‘weekly planning’ it means that our goal is to ensure all the tasks for the week are completed by end of week. If we are behind on Monday, Tuesday or Wednesday, that is okay. As long as we complete by end of week, we will be accomplishing our goals. In this scenario, you don’t have to enter daily level tasks. You could enter just one record for a resource for each week.
If you want to ensure you will be meeting your demand every single day, then, you should enter daily tasks individually.
Thus, the template can accommodate more granular demand entry (daily – even multiple tasks per day) or aggregated demand entry (weekly or monthly or quarterly).
Once we complete entering the demand, we are done with data entry.
Step 5: Refresh Calculations
The template uses pivot tables and hence every time we change input data, please refresh calculations.
Data ribbon –> Refresh All.
Step 6: Review Dashboard
Dashboard sheet shows the summary of metrics across all projects, skills and resources.
On the left side, you will have the slicers (filters) that you can use to drill down to specific Project(s), Skill group(s) and Resource(s).
At the top, you will see the overall summary of metrics.
You can see the number of Projects (2), Skills (2) and Resources (6) in the Plan.
On the left side, you can see the Capacity (2472 hrs), Demand (2287 hrs), Surplus of 185 hrs at 93% Utilization Rate.
On the right side, you can see the same metrics – based on Cost. The resources available cost totally $25,554 while the demand tasks will cost $23,273 resulting in a surplus of $2,281 at 91% utilization.
We also see that there are 2 over utilized resources and 3 under-utilized resources.
Why is this important?
The overall aggregate utilization rate will not provide the complete picture. If there is a Developer resource who is over utilized (utilization rate > 100% or capacity < demand) has 50 hrs over utilized and another Project Mgmt resource who is under-utilized (utilization rate < 100% or capacity > demand) with 50 unutilized hrs, they cancel each other when we aggregate. Though the overall utilization rate may be 100%, individual resources have surplus and deficit and that must be addressed.
Hence it is important to view utilization at resource level.
To change the currency from US$ to another currency, please follow the steps below:
Press Ctrl+G and select CURR named range.
Press Ctrl+1 to open the Formatting dialog box. Choose the currency desired and click OK.
Now, the currency on the dashboard display will change accordingly.
Let’s continue with the rest of the dashboard.
The above visual presents the capacity vs demand for each of the Projects.
On the left, you will see projects where there is deficit – that is, capacity is less than demand. In this case, we don’t have any.
A project will either be in deficit or surplus – not both.
On the right, you will see projects where there is surplus – capacity is greater than demand. Project A has capacity of 1236 hours while demand is only 1097 hours. The surplus is 139 hours.
Project B has capacity of 1236 hours while demand is only 1190 hours. The surplus is 46 hours.
The above visual presents the Skills which are over utilized (on the left side) and under-utilized (on the right side).
Development has a total capacity of 1488 hours but demand of 1495 hours. 7 hours overutilized (deficit).
Project Mgmt has a total capacity of 984 hours but demand of 792 hours. 192 hours under-utilized (surplus).
A Skill Group will be in Surplus or Deficit or neither; never in both.
Resource table shows which specific resources are over utilized and under-utilized.
Clearly two resources Developer 3 and Developer 2 do not have enough capacity to complete the tasks assigned to them. Meanwhile, 3 other resources have a lot of additional hours available but not utilized.
In the next page of the dashboard, you can see the charts of Capacity vs Demand in Hours.
The charts show the overall capacity vs demand for each Project, Skill group and Resource.
In the final page of the dashboard, you can see the charts of Capacity vs Demand in Cost.
The charts show the overall capacity vs demand for each Project, Skill group and Resource.
The Dashboard has so far given us a good overview of our plan and identified 2 over-utilized resources which need to be addressed.
3 resources have unutilized hours that will be costing the team. In this tutorial, I am assuming the under-utilization is not a major concern. In some project scenarios, underutilization can also be an issue. You can follow similar steps as explained below to address that too.
Now, let’s see how the template will help us address this.
Step 7: Addressing Over-utilization and Under-Utilization
In order to address the over-utilization and under-utilization, we must do one or more of the following:
- Change Capacity
- Adjust standard availability of resources in the Resources sheet.
- Increase (to increase capacity) or Decrease (to reduce capacity) available hours
- Add overtime (to increase capacity) or vacation time (to reduce capacity)
- Adjust standard availability of resources in the Resources sheet.
- Change Demand
- Change the demand hours if possible. This would reduce the demand and allow the capacity to fulfill the demand.
- Change Allocation/Assignment
- Change assignment of tasks (demand) to a different resource.
- Change capacity allocation of resource to Projects.
The first option Change Capacity is straight forward and don’t need much explanation. The impact is that it will cost more to increase capacity.
The second option Change Demand is also straight forward to implement in the template (update the demand sheet), though it may be hard to get project stakeholders to reduce the demand (or scope).
We will focus on the 3rd option which may neither increase the cost nor reduce the demand. It is the re-assignment of capacity and demand (tasks) to existing resources to remove over-utilization.
Changing Assignment of Tasks to a Different Resource
Let’s filter the dashboard to Project A by using the slicers on the left of the Dashboard.
The Resources table shows the following.
Developer 2 is overutilized by 5 hours while Developer 1 has 37 unutilized hours.
In the Demand sheet, Filter to Resource Developer 2. Find a task for 5 Hours and re-assign to Developer 1.
Let’s refresh the calculations. Data ribbon –> Refresh All.
That will update the dashboard calculations.
Now, there are no overutilized resources in Project A.
Let’s clear the Project filter in Dashboard.
Now, this is what the Resources table shows.
Developer 3 has 39 overutilized hours, but we don’t have a Developer with that many unutilized hours.
Project Planner 2 who also works on Project B has 85 unutilized hours. To demonstrate how this template can handle a resource having multiple skills, we will assume that Project Planner 2 can handle Development tasks.
Now, we need to assign some of the capacity of the Project Planner 2 to Development skill.
Important things to note here:
- A new record must be created in the Capacity Sheet as shown above (last row). Resource Name will be same, but Skill will be Development. Cost can vary and can be entered any value.
- When we enter the standard work hours of the new record (we have entered 1 hour a day), we must review the work hours already assigned for the resource (Project Planner 2). Previously Project Planner 2 had 8 hours a day availability. We have updated that to 7 hours for Project Mgmt and assigned 1 hour to the Development. This assumes that the resource is still working same total number of hours. It’s just that we are changing the skill being used.
- When you split a resource to multiple skills or projects, check the Vacation/overtime sheet and update if that resource has Vacation/Overtime entries.
- In this case, Project Planner 2 has vacation and overtime entries. We create additional entries for the new skill and update the previously entered entries.
Now that we have created the capacity allocation for Development skill, we need to assign tasks from Developer 3 to Project Planner 2 in Demand Sheet. Select enough tasks to switch to overcome the overutilization.
Let’s refresh the Dashboard again.
We have no resources over utilized. 🙂
Overall metrics look like below.
All the resources will have enough capacity to meet the demand. We have 185 hours surplus capacity and are at 93% utilization.
This was just an illustration of how to use the template to modify capacity and demand. The target utilization rate can vary in each organization.
The Dashboard provides the metrics for the entire planning period duration in aggregate. If, in your organization, it is important to ensure a certain utilization every day, then you can update the planning period to a day or use the Calendar sheet to view capacity/demand at a daily level.
Step 8: View Calendar for granular time periods
Calendar is very flexible as it has many options to choose from. It also has the slicers (filters) just like the Dashboard.
The below shows the capacity in hours for each resource every week.
There are four selections (Dimension, Measure, Unit, Time Dimension) to make and each selection has several options.
The calendar can be created for the list of resources or list of skill groups or list of projects.
You can select to display Capacity data or Demand data or Surplus/Deficit data.
The options for Unit are Hours and Cost.
Finally, you can choose to show daily, weekly, monthly, quarterly or annual data.
The template will display up to 53 periods (53 days, 53 weeks, 24 months, 8 quarters, 2 years).
When you make the selections, the calendar display will update automatically to reflect the selections.
For example, let’s see Surplus/Deficit Hours by week.
We can see that though overall there may be no deficit for a resource based on total hours, some weeks have deficit which are made up by surplus capacity in some other weeks.
If we want to ensure that the team completes the tasks every week, then we need to follow the steps outlined earlier on modifying the capacity or demand.
If there are any questions about this template, please see the Product Support page for more information.
Is there any way to use this file without it needing a specific project applied to the resource?
I want to be able to enter the capacity of a technician, and leave it open without being assigned to a project, and then enter the demand and assign the resource to the project there instead. If not then this tool did not do what I hoped it would.
Thanks for purchasing.
We assign capacity to project so that project level capacity vs demand in the dashboard can be calculated.
You can enter a dummy project in the capacity for a resource, and then ignore the project level capacity vs demand in the dashboard.
Alternatively, the Project planner template allows entering capacity without project assignment and enter only demand with project and resource assignment.
Please try that here https://indzara.com/2013/02/project-planner-basic-excel-template/ and let us know if that meets your needs.
We look forward to hearing from you.
I need help to do some text changes to my language (Spanish) since I am presenting this report in my company in which we all speak Spanish. Below I indicate the changes I would like to do:
1) on the dashboard tab, I would like to change the word SURPLUS on the graphic to “EXCEDENTE”;
2) on the calendar tab, I would like to change the names of the four selections on the drop-down list. The changes that I am not able to do since it is blocked are:
The words on the drop-down list:
RESOURCE = Recurso;
SKILL GROUP= Grupo de habilidades;
DEMANDA = Demanda
DAILY = Diario
MONTHLY = Mensual
QUARTERLY = Cuatrimestre
Would you please help me? I have the file in the V3_2 and my operational system is Windows 10. Thanks
Thanks for purchasing.
1. On the Dashboard, you can unprotect using indzara as password and change the cell L10. There are no other formulas connected to it.
a. Changes to textboxes can be done
2. On the Calendar, the drop-down list values are connected to formulas and hence changing it will require changing those formulas as well. All the cells in calendar sheet need to be reviewed and edited.
Please let me know if any questions.
How can I add more than 300 entries in capacity planning sheet?
Please email email@example.com and we have a version that supports 500 entries and 300 resources/projects/skills.
How can I Enter the Daily/Weekly Tasks in the Demand (Tasks) sheet, do you expect me to enter 365 entries for a daily task for different dates for one project and repeat that for every other project I am responsible for, and then do the same thing for Other resources?
Thanks for purchasing.
We can enter consolidated demand for a week or month and in that case, we should change the view to be weekly or monthly in the Calendar sheet. the granularity in which we enter the demand will be the granularity in which the demand vs capacity comparison can be made in the Calendar.
For example, you can enter one entry for a week for each resource for a project with the total demand for the week.
In the previous version of the template, demand was entered in aggregate for each skill group. Based on customer demand, the new version allows more fine task level entry. If you would like to try the previous version, please email firstname.lastname@example.org and I will send the file.
Can you tell How do I enter the consolidated demand in the DEMAND SHEET because there is no TO and FROM Date. For. Eg. “Task: Starting up the Simulator” is undertaken by the Resource Every single working day of the year. Then how can I enter this as weekly consolidated in the DEMAND SHEET. Please if you can advise. I am not talking about the CALENDAR Sheet.
If for this entire week of Sep 8 to Sep 14, it is 1 hr per day, we can enter Sep 8 as the date and assign 7 hrs. We can enter Sep 15 and assign 7 hrs for next week. The reason the Calendar is important here is since we entered weekly aggregated demand we need to choose Weekly view in the Calendar to see the Capacity vs Demand comparison. In the Dashboard, it would work anyway since the dashboard represents the whole planning period and does not break it by day/week/month.
Please let us know if you have questions.
I tried entering daily, weekly, monthly demand in hours, but in the dashboard does not show the aggregated demand. For example, if a resource is working on a project for 6 months and I enter 40 hours as the demand, the dashboard shows only 40 hours instead of 240 hours. What am I doing wrong?
Is 40 hours your weekly demand? Also, have your mentioned the start date and end dates correctly?
Please check, in case the issue persists, please email your file along with the list of issues to email@example.com.
I have just purchased the tool and finished setting it up.
It operates very well but is very slow due to the file size.
I am not interested in the cost functionality is there any issues with deleting the tables and graphs
it will help the performance on capacity which is my biggest concern
Thanks for purchasing.
You can remove the cost charts from the dashboard. Please do not delete any tables.
Please email the file to firstname.lastname@example.org and specify your Excel version and Operating System.
I can help remove the charts and the associated pivot tables.
Could I change the “Skill Group” to “Team” and eliminate any cost information from the dashboards? We are not interested in using the tool at this time for costs, but I want the dashboards to look good without cost information.
Thanks for using our template.
You may rename the skill group. However, ensure it’s changed at all locations. Ignoring the costs would not give you a balanced picture of the planning process.
What you really need this spreadsheet to do to be totally awesome…it to disconnect capacity and demand. What I mean is that you should be able to enter resource capacity without assigning it to a particular project but rather to a skillset and then enter the demand with the skillset needs not necessarily people. That way as the scheduler I can determine which person goes to which project based on skillset needs. Is there a way to do that that I might be missing?
Thanks for your feedback.
‘as the scheduler I can determine which person goes to which project’ – can you please clarify this statement? Would you be assigning the demand to specific resources?
In the template, the capacity and the demand are assigned specifically to projects and resources, in order for comparison of capacity vs demand at all levels (resource, skill and project). If we don’t assign a level (example: project) in capacity, that would prevent comparing capacity vs demand by that level (project).
I input on the settings tab the following
START DATE 1-Sep-2018
END DATE 31-Jan-2020
Max. 2 years. End Date will be set as dd-00-yyyy
But the end date on the “Dashboard” tab did not update automatically.
Also on the “Calendar” tab the “planning period” did not update at all, both start and end date appears just like this
please help me to resolve this issue
It appears that the dates entered are not being accepted by Excel as valid dates. I have replied to your email with some more details. Please reply with your file, screenshots and specify Excel language/country. Dates work differently in some language/country versions of Excel.
Thanks & Best wishes.
I already resolved this date issue. I have changed the format from dd-mmm-yyyy to tt-mmm-jjjj since we have german language settings.