Resource Capacity Planner Excel Template – Step by Step User Guide

Posted on

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:

  1. Enter Settings
  2. Enter Resource list with standard availability
  3. Enter vacation and overtime
  4. Enter Demand (tasks) data
  5. Refresh Calculations
  6. View Dashboard to evaluate plan
  7. Address any over-utilization and under-utilization
  8. View Calendar to ensure desired utilization at granular time periods

 

VIDEO DEMO

 

Step 1: Enter Settings

In the Settings sheet, we will enter the basic settings.

Planning Period
We will set the planning period by entering a Start Date and End Date. The template allows a maximum of 2-year planning period.

Enter Settings - Planning Period
Enter Settings – Planning Period

We will use a 3 month period from July 1, 2018 to Sep 30, 2018.

 

Weekends
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.

Enter Weekends
Enter Weekends

We will choose SAT & SUN as weekends for this tutorial.

Enter Settings - Weekends
Enter Settings – Weekends

If a resource works during a weekend day, we can enter that as overtime in the VACATION_OVERTIME sheet.

 

Excel Tables

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

 

Holidays
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.

Enter Settings - Holidays
Enter Settings – Holidays

If a resource works during a holiday, we can enter that as overtime in the VACATION_OVERTIME sheet.

 

Projects
The template allows managing capacity for multiple projects.
We will enter 2 projects, to keep it simple.

Enter Settings - Project List
Enter Settings – Project List

 

Skill Groups
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 Settings - Skill Group List
Enter Settings – Skill Group List

 

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.

Enter list of resources and their standard availability Hours
Enter list of resources and their standard availability Hours

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.

Enter Vacation and Overtime hours for employees
Enter Vacation and Overtime hours for employees

Project Planner 1 is taking 3 days off (8 hrs each day) from 16th July to 18th July. Two important things to note here:

  1. 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.
  2. 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.

Enter Demand (Tasks) - Date, Resource and Hours
Enter Demand (Tasks) – Date, Resource and Hours

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.

Calculated Fields - Do not delete or edit
Calculated Fields – Do not delete or edit

 

Planning granularity

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.

Refresh All calculations
Refresh All calculations

 

Step 6: Review Dashboard

Dashboard

Dashboard sheet shows the summary of metrics across all projects, skills and resources.

Resource Capacity Planner Dashboard with slicers
Resource Capacity Planner Dashboard with slicers

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.

Dashboard - Resource Capacity Planning - Summary Metrics
Dashboard – Resource Capacity Planning – Summary 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.

 

Changing Currency

To change the currency from US$ to another currency, please follow the steps below:

Press Ctrl+G and select CURR named range.

Changing Currency - Press Ctrl G and select CURR
Changing Currency – Press Ctrl+G and select CURR

Press Ctrl+1 to open the Formatting dialog box. Choose the currency desired and click OK.

Press Ctrl+1 and Change Currency
Press Ctrl+1 and Change Currency

Now, the currency on the dashboard display will change accordingly.

 

Let’s continue with the rest of the dashboard.

Resource Capacity Planning Dashboard - Over utilized and under utilized Projects
Resource Capacity Planning Dashboard – Over utilized and under utilized Projects

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.

Resource Capacity Planning Dashboard - Over utilized and under utilized Skill Groups
Resource Capacity Planning Dashboard – Over utilized and under utilized Skill Groups

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.

Resource Capacity Planning Dashboard - Over utilized and under utilized Resources
Resource Capacity Planning Dashboard – Over utilized and under utilized Resources

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.

Capacity vs Demand Hours Chart
Capacity vs Demand Hours Chart

 

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.

Capacity vs Demand Cost Chart
Capacity vs Demand Cost Chart

 

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:

  1. Increase (to increase capacity) or Decrease (to reduce capacity) standard availability of resources in the Resources sheet.
  2. Add overtime (to increase capacity) or vacation time (to reduce capacity)
  3. Change assignment of tasks to a different resource.
  4. 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.

Assigning Resources in Project A - Capacity Planning
Assigning Resources in Project A – Capacity Planning

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.

Change assignment of task
Change assignment of task

 

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.

Resources under utilized and over-utilized
Resources under utilized and over-utilized

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.

Splitting a resource to two skills
Splitting a resource to two skills

Important things to note here:

  1.  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.
    1.  We have given the name Project Planner 2 D an assigned to Development skill.
  2. 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.

Re-assign tasks to new resource
Re-assign tasks to new resource

 

Let’s refresh the dashboard again.

Resources under utilized and over-utilized updated
Resources under utilized and over-utilized updated

We have no resources over utilized. 🙂

Overall metrics look like below.

Dashboard Summary updated
Dashboard Summary updated

 

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.

Capacity Planning Calendar - Capacity Hours Weekly
Capacity Planning Calendar – Capacity Hours Weekly

 

There are four selections (Dimension, Measure, Unit, Time Dimension) to make and each selection has several options.

Resource Capacity Planner Calendar Settings
Resource Capacity Planner Calendar Settings

 

Dimension
The calendar can be created for the list of resources or list of skill groups or list of projects.

Calendar Settings - Dimension Resource Skill or Project
Calendar Settings – Dimension Resource Skill or Project

 

Measure
You can select to display Capacity data or Demand data or Surplus/Deficit data.

Calendar Settings - Measure Capacity Demand or Surplus Deficit
Calendar Settings – Measure Capacity Demand or Surplus Deficit

 

Unit
The options for Unit are Hours and Cost.

Calendar Settings - Unit Hours or Cost
Calendar Settings – Unit Hours or Cost

 

Time Dimension
Finally, you can choose to show daily, weekly, monthly, quarterly or annual data.

Calendar Settings - Time Dimension Daily Weekly Monthly Quarterly or Annual
Calendar Settings – Time Dimension Daily Weekly Monthly Quarterly or Annual

 

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.

Capacity Planning Calendar - Surplus Deficit Hours Weekly
Capacity Planning Calendar – Surplus Deficit Hours Weekly

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.

20 thoughts on “Resource Capacity Planner Excel Template – Step by Step User Guide

  1. Hi,
    I bought your very useful template.
    One question: is there a way to show more than 53 periods when the calendar has a daily granularity?

    Thanks and regards

    1. Thanks for purchasing.
      Yes, the dates can be expanded to more than 53 periods by extending the formulas to the right.
      Thanks & Best wishes.

  2. Hi

    How do you extend the dates. i need to extend to 5 years. And also i have input all the data for Resources and Demand and i have multiple projects but on the dashboard it is only showing 1 project

    1. Thanks.
      1. Number of Projects and Resources can be extended, but the time period cannot be extended.
      2. Please email file with data to support@indzara.com. We can review and get back to you.

      Best wishes.

  3. Hello-

    Just purchased this template. Once we split the same resource for multiple projects, scheduling the vacation for the resource would get tedious?

    As in, a Resource 1 is split into Resource 1 for 10% in one Project and Resource 1D is for 90% in another project. Resource is taking a day of vacation. How do I split the vacation days?

    1. Thanks for purchasing.
      The vacation has to be entered split in the same way as availability. For example, if the resource is usually available for 10 hours and is split as 1 hr (Resource 1) for Project A and 9 hrs (Resource 1D) for Project B. And if the resource wants to take vacation, we will enter Vacation as 1 hr for Resource 1 and 9 hours vacation for Resource 1D.
      Please let us know if there are any questions.
      Best wishes.

  4. Just purchased tonight and noticed that on the “Calendar” dashboard, there are no dropdown selections as described in this user guide. The data validations seem to be missing. I can type in the different values and the formulas seem to work properly, but would rather have the dropdown selections.

    1. Thanks for purchasing.
      The drop down functionality will work inside the Excel table only. Please ensure that you are entering data inside the table.
      If there are further questions, please email the file to support@indzara.com.
      Please specify Excel version and Operating system.

      Best wishes.

  5. I have updated the information and refreshed the file. However, the dashboard and calendar are blank. Any reason why would this be the case?

    1. Thanks for emailing the file. I have replied to your email. The dates were entered in a different format and that’s why the Dashboard was not displaying results. I have sent you the file.
      Best wishes,

  6. I have resources working on multiple projects. If I create multiple resources, I won’t be able to see resource over allocation. Am I understanding this wrong?

    1. A single resource can be assigned to multiple projects. They have to be entered separately. To assign a resource to 2 projects, we have to enter two entries in Resources sheet. Resource A – Project One, Resource A – Project Two.
      We have to treat them as two resources. The available time will have to be separately given in the Resources sheet. For example, Resource A – Project One works 6 hours a day while Resource A – Project Two works 2 hours a day. Overall, the person works 8 hours 6 hours for Project One and 2 hours for Project Two.
      By this set-up, if the resource is assigned demand more under Project One than the capacity under Project One, then we would see the over-allocation.
      In summary, we will see over-utilization and under-utilization, but not as one resource, but individually for the two.
      Please let us know if there are any questions.
      Best wishes.

  7. Hi,

    Great Workbook, how can I extend the entries to allow more than 100 resources? I need to assigned more than one employee to multiple projects so I will need to create the same resource multiple times.

    1. Hello

      We have shared a file which allows planning more resources.

      Best wishes

  8. I have purchased this template, but my resources(employees) need to be assigned to multiple projects. I have roughly 40 employees over 40 or 50 projects. How do I assign the same employee to multiple projects?

    1. Thanks for purchasing.

      A single resource can be assigned to multiple projects. They have to be entered separately. To assign a resource to 2 projects, we have to enter two entries in Resources sheet. Resource A – Project One, Resource A – Project Two.
      We have to treat them as two resources. The available time will have to be separately given in the Resources sheet. For example, Resource A – Project One works 6 hours a day while Resource A – Project Two works 2 hours a day. Overall, the person works 8 hours 6 hours for Project One and 2 hours for Project Two.

      This is explained in Step 7 in the user guide: https://indzara.com/2018/07/resource-capacity-planner-excel-template-step-by-step-user-guide/ where we illustrate how a resource can be assigned to multiple skills. We use a similar approach to assign to multiple projects.

      Please let us know if there are any questions.

      Best wishes.

    2. Having same issues. How do I assign a resource to multiple projects

      1. Hello,

        A single resource can be assigned to multiple projects. They have to be entered separately. To assign a resource to 2 projects, we have to enter two entries in Resources sheet. Resource A – Project One, Resource A – Project Two.
        We have to treat them as two resources. The available time will have to be separately given in the Resources sheet. For example, Resource A – Project One works 6 hours a day while Resource A – Project Two works 2 hours a day. Overall, the person works 8 hours 6 hours for Project One and 2 hours for Project Two.

        This is explained in Step 7 in the user guide: https://indzara.com/2018/07/resource-capacity-planner-excel-template-step-by-step-user-guide/ where we illustrate how a resource can be assigned to multiple skills. We use a similar approach to assign to multiple projects.

        Please let us know if there are any questions.

        Best wishes.

  9. Hello,

    I am trying to use this tool for multiple industrial manufacturing plants. Any tips?

    1. Thanks for your interest in our templates.

      You can review our templates on Manufacturing business at https://indzara.com/product/manufacturing-inventory-sales-manager-excel-template/

      Please elaborate as to how you want to use the Capacity Planner for a multi-location manufacturing business.

      Best wishes

Leave a Reply

Your email address will not be published. Required fields are marked *