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.
Overview of steps:
- Enter Settings
- Enter Resource list with standard availability
- 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 July 1, 2018 to Sep 30, 2018.
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.
Step 2: Enter Resources
Now, we move to the Resources sheet in the template.
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.
We enter the Start Date and End 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 a Project.
If a resource needs to be shared across 2 projects or 2 skill groups, they would have to created twice with a different resource name. We will explain this later in this blog post below.
Finally, we enter the Cost per Hour for each resource. The cost is fixed for each resource and cannot vary over time.
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 July to 18th July. 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 have to 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 16th July. We enter 16th July as Start and End Dates. It is important that we fill both dates.
Finally Project Planner 2 also does overtime on 21st July. We enter 8 (not -8) in Hrs, since this is overtime.
This 21st July 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.
Step 4: Enter Demand data
In this template, demand is entered as Tasks in the Demand sheet.
Required fields are Date of the task, Resource assigned to do that task and Hours of work required.
Detail column is optional to enter any details about the task or any notes.
The four 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.
We can enter just the first 3 required fields and move to the next row and continue entering tasks.
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.
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.
Hence it is important to view utilization at resource level.
On the left side, you can see the Capacity (2478 hrs), Demand (2303 hrs), Surplus of 175 hrs at 93% Utilization Rate.
On the right side, you can see the same metrics – based on Cost. The resources available cost totally $25,614 while the demand tasks will cost $23,453 resulting in a surplus of $2,161 at 92% utilization.
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 number of projects where resources are over utilized (on the left side) and under-utilized (on the right side).
For example, Project B has 1 resource who is over utilized. That resource has a capacity of 360 hours but they have been assigned to tasks for an additional 39 hours (referred to as Over Utilized hours). They have been assigned to (360 + 39= 399 hours).
A project can have over utilized resources as well as under-utilized hours.
Project B also has 1 resource who is under-utilized. That resource has a capacity of 504 hours but 80 hours of that are unutilized. They have been assigned to only (504 – 80 = 424 hours).
We can also understand if there is over utilization or under-utilization at skill group level.
The above visual presents the Skills where resources are over utilized (on the left side) and under-utilized (on the right side).
For example, Development has 2 resources who are over utilized. Those 2 resources have a capacity of 738 hours but they have been assigned to tasks for an additional 44 hours (referred to as Over Utilized hours). They have been assigned to (738 + 44 = 782) hours.
A Skill Group can have over utilized resources as well as under-utilized hours.
Development also has 1 resource who is under-utilized. That resource has a capacity of 378 hours but 37 hours of that are unutilized. They have been assigned to only (378 – 37 = 341) hours.
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.
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 have to do one or more of the following:
- Increase (to increase capacity) or Decrease (to reduce capacity) standard availability of resources in the Resources sheet.
- Add overtime (to increase capacity) or vacation time (to reduce capacity)
- Change assignment of tasks to a different resource.
- Change the demand hours if possible. This would reduce the demand and allow the capacity to fulfill the demand.
The first 2 options are straight forward and don’t need much explanation. The 4th option 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 will neither increase the cost nor reduce the demand. It is the re-assignment of tasks to existing resources to remove over-utilization.
If we filter the dashboard to Project A.
Developer 2 needs just 5 more hours of capacity. However, Developer 1 has 37 unutilized hours.
Since they are in same project, we can just go to the Demand sheet and change the assignment of a task that will take 5 hrs, from Developer 2 to Developer 1.
Let’s refresh the calculations. Data ribbon –> Refresh All.
That will update the dashboard calculations.
Let’s remove the project filter so that we can see all the resources.
Now, we can see that Developer 2 is no longer over utilized. Developer 3 in Project B is the only one we need to address.
There are 39 over utilized hours. However, Project B does not have any unutilized resources with Development Skill. We have Project Planner 2 with 80 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 resource must be created and the resource name has to be different. Resource Name column should only have unique names for the template to function.
- We have given the name Project Planner 2 D an assigned to Development skill.
- When we enter the standard work hours of the new resource (we have entered 1 hour a day), we must review the work hours for the existing resource (Project Planner 2). Previously Project Planner 2 had 8 hours a day availability. We have updated that to 7 hours and assigned the 1 hour to the new resource Project Planner 2 D. This assumes that the resource is still working same total number of hours. It’s just that we are changing the skill being used.
Now that we have created a new resource with Development skill, we need to assign tasks from Developer 3.
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 176 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.
How to assign a resource to multiple projects
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.