Step by Step User Guide

Overview of steps:

1. Enter Settings (Projects, Skills, Resources, Holidays).
2. Allocate Capacity of Resources to Skills and Projects.
3. Enter vacation and overtime.
4. Enter Demand (tasks) data.
5. View Dashboard to evaluate plan.
6. Address any over-utilization and under-utilization.
7. View Calendar to ensure desired utilization at granular time periods.

Detailed Step by Step

Step 1: Enter Settings

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

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.

Step 1 – Enter List of Holidays

Note: 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

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 Management and Software Dev.

Enter Settings – Skill Group List

Resources

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 4 – Enter list of Resources

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.

Pasting Resource Names as Values

We assign the 6 resources each to a Skill Group and Project.

After assigning each resource with Skill Group and Project, 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. Finally, we enter the Cost per Hour for each resource.

• Important: There are hidden columns on the right end that are critical to the functioning of the template. Please do not edit/delete them.

Resources Capacity Table – Entered

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. 

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 Software Dev role for 4 hours and Project Mgmt role for 2 hours, we would enter as shown below.

Entering same resource with multiple skills

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

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 were available 6 hours in total (Software Dev skill) and will work 4 hours for Project 2 and 2 hours for Project 1, we would enter as shown below.

Entering same resource with multiple Projects

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 Software Dev role for Project 2 and Project Mgmt role for 2 hours for Project 1, we would enter as shown below.

Entering same resource with multiple Projects and Skills

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.

Assigning a resource to different projects over time

In the above illustration, Developer 4 works on Project 2 for 6 hours a day until 30th Apr. From May 1st, the resource works 6 hours a day on Project 1.

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.

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 Overtime taken by Resources

Project Planner 1 when working in Project Mgmt skill for Project 1 is taking 3 days off (8 hrs each day) from 12th Apr (Monday) to 14th Apr (Wednesday).

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 for 3 days. Please do not enter 24 hours here.
   • If you need a different vacation hours for each day, please enter them as separate rows. For example, if an employee usually works 4 hrs on Mondays while working for 8 hrs on Tue-Fri. If the employee Is taking Mon-Fri off on vacation,
     1. Enter one row for Monday (start date and end date are same) and enter -4 hrs of vacation.
     2. Enter a second row for Tuesday to Friday (Start date is Tuesday and End date is Friday) and enter -8 hrs of vacation.
2. We enter -8 since it is vacation. If it is overtime, we will enter 8.

Project Planner 2 is taking one day off (7 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 23rd May. We enter 7 (not -7) in Hrs, since this is overtime.

This 23rd May is a Sunday and is a non-working 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.

Important:

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

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 as Tasks assigned to Resource Project and Skill.

Required fields: 

• Resource assigned to do that task
• Skill Group needed for the task
• Project to which the task belongs to
• Start and End date of the task 
• Hours of work required for each weekday
• Detail column is to enter any details about the task

The calculated columns 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.

Step 5: Review Dashboard

Dashboard

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

Capacity Planning Dashboard

On the top side, you will have the slicers (filters) that you can use to drill down to specific Project(s), Skill group(s) and Resource(s).

Dashboard Slicers

At the top, you will see the overall summary of metrics. 

Capacity Planning Dashboard – Summary 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 (2337 hrs), Demand (2228 hrs), Surplus of 109 hrs at 95% Utilization Rate.

On the right side, you can see the same metrics – based on Cost. The resources available cost totally $23,994 while the demand tasks will cost $23,166 resulting in a surplus of $829 at 97% utilization.

We also see that there are 2 over utilized resources and 2 under-utilized resources.

Why is this important?

The overall aggregate utilization rate will not provide the complete picture. If there is a Software Dev 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.

The dashboard then presents the capacity vs demand for each of the Projects.

Project level Capacity vs Demand

On the left, you will see projects where there is deficit. Here we don't have any. 

On the right, you will see projects where there is surplus – capacity is greater than demand. 

• Project 1 has capacity of 1152 hours while demand is 1074.5 hours. The surplus is 77.5 hours.
• Project 2 has capacity of 1185 hours while demand is only 1153.5 hours. The surplus is 31.5 hours.

A project will either be in deficit or surplus – not both.

The Dashboard then presents the Skills which are over utilized (on the left side) and under-utilized (on the right side).

Skill level Capacity vs Demand

• Software Dev has a total capacity of 1416 hours but demand of 1314.5 hours. 101.5 hours under-utilized (surplus).
• Project Mgmt has a total capacity of 921 hours but demand of 913.5 hours. 7.5 hours under-utilized (surplus).

A Skill Group will be in Surplus or Deficit or neither; not in both.

Resource table shows which specific resources are over utilized and under-utilized.

Resource level Capacity vs Demand

Clearly two resources Project Planner 1 & Developer 1 do not have enough capacity to complete the tasks assigned to them. 

Meanwhile, 2 resources have a lot of additional hours available but not utilized. 

Finally 2 resources Developer 3 and Developer 4 are not even listed, because their capacity = demand. 

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 Planning Dashboard – Hours – Capacity vs Demand – Charts

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 Planning Dashboard – Cost – Capacity vs Demand – Charts

The Dashboard has so far given us a good overview of our plan and identified 2 over-utilized resources which need to be addressed. 

2 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 6: 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:

1. Change Capacity
   • Adjust standard availability of resources in the Resources sheet.
     1. Increase (to increase capacity) or Decrease (to reduce capacity) available hours
   • Add overtime (to increase capacity) or vacation time (to reduce capacity)
   • Add or remove resources
2. Change Demand
   • Change the demand hours if possible. This would reduce the demand and allow the capacity to fulfill the demand.
3. 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 (or re-allocation) 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 1 by using the slicers on the left of the Dashboard.

The Resources table shows the following.               

Resources in Project 1 – Capacity Planning

Project Planner 1 is overutilized by 24 hours and Developer 1 is overutilized by 24.5 hours while Developer 2 has 126 unutilized hours.

Let’s resolve the Software Dev skill imbalance first. In the Demand sheet, reduce Developer 1 hours and add for Developer 2.

Reassigning a task to Developer 1

In this case, we have added a demand task for 1 hour a day Mon to Fri from May 5th to June 10^th. This will add totally 26 hours to Developer 2.

We need to reduce the equivalent demand from Developer 1. Hence we have reduced the demand in the Developer 1 task (from 2 hrs a day to 1 hr a day). 

This should shift 26 hours of demand from Developer 1 to Developer 2. 

Let’s refresh the calculations. Data ribbon   –> Refresh All.

That will update the dashboard calculations.

Project 1 – Resource – Capacity vs Demand

Developer 1 no longer has overutilized hours. there is 1.5 unutilized hours. 

Project Planner 1 has 24 overutilized hours. However, we don’t have any other Project Management resource in Project 1. 

We have two options: 

1. We can have Developer 2 take on Project Management work to help out Project Planner 1 if Developer 2 can actually do that. This will require a resource taking on a new skill that was not available in Capacity. 
2. We can have Project Planner 2 help out Project Planner 1. This will require a resource to work on a project that was not planned in Capacity. 

We will see how we can implement both of these options easily. 

Developer 2 to help with Project Management

We will first enable capacity for Developer 2 to do Project Mgmt work for Project 1 by simply adding a new record in the Capacity sheet.

We won't assign any hours, as we are not changing the capacity now.

We go to the Demand sheet and now transfer the demand from Project Planner 1 to Developer 2.

Here, we have reduced the demand of Project Planner 1 (from 8 hours a day to 6 hours a day) from Apr 1st to Apr 20th. 

We added the equivalent demand to Developer 2 in a new task at the bottom. 

When we refresh data, the dashboard now shows for all projects that there is no overutilization. 

Project Planner 2 to help with Project Management for Project 1

We will first enable capacity for Project Planner 2 to do Project Mgmt work for Project 1 by simply adding a row in the Capacity sheet.

Now, in the demand sheet, we will transfer the demand.

When we refresh the Dashboard now, we can see that there are no over utilized resources. 

Special Note

Though the overall view shows that all the resources are properly allocated, when we filter to Project 1, we see the following. 

This is because, we didn't actually add capacity for the resource (Project Planner 2) to Project (Project 1). So according to our capacity planned, we didn't have enough to meet the demand. However, we asked Project Planner 2 to chip in and help though he/she was not actually assigned any hours in capacity. Depending on your specific objective, you can leave this as it is, to remind that Project Planner 2 came over and helped with Project 1. Or if you are planning this ahead of time and have the option to re-assign capacity, we can do that too. I will explain now.

In the capacity sheet, I have updated Project Planner 2 's time for Project 2 and then added time for Project 1. Overall, the resource is still working 7 hrs a day from 1st Apr to 30th June. But I have distributed it between projects.

When we refresh the dashboard, for Project 1,

Now, Project Planner 2 is actually assigned a capacity of 25 hrs and demand of 25 hrs. Hence no overutilization. 

All the resources will have enough capacity to meet the demand. We have 109 hours surplus capacity and are at 95% 

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.

Handling vacation/overtime

While we are modifying the capacity sheet to address the over-utilization and under-utilization scenarios, please note that our vacation/overtime entries need to be reviewed. When you add new combinations or modify existing combinations of Resource-Skill-Project to the Capacity sheet, please check the Vacation/overtime sheet and update if that combination has Vacation/Overtime entries.

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

Calendar – Capacity by Resource – Hours – Weekly

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

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

Measure

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

Calendar Settings – Measure Capacity Demand or Surplus Deficit

Unit

The options for Unit are Hours and 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

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.

Calendar – Surplus Deficit by Resource – 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.

Step 8: View the Project and Resource view for better planning

 Project View: Similar to the calendar, get a project level overview of the capacity/demand or the surplus-deficit. This visual shows, for a chosen time dimension (from Daily to Annually) the Projects and the list of resources currently on these projects.

This is a drill-down based on project and the corresponding resources who are with the project

Resource View:

To get a better understanding of the resource’ capacity or demand allocations, the Resource view can come in handy. This too, like the project view can be aggregated for various time dimensions.