Resource Capacity Planner

This 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 the impact of the changes instantly.

This workforce planning tool can be used in different contexts. For example, an HR manager or a team manager can use this to plan the capacity of the team every month and hence determine how much work can be taken up and fulfilled. A project manager who is working on a multi-month long project can plan the resource capacity of the project for the entire duration. This can also be used outside corporate environments wherever you want to compare demand vs capacity.

The template can also be used for non-human resources as well, for example, machines’ capacity in an industrial setup.

FEATURES

• Simple and easy

  • Simplified data entry

  • Simple Excel spreadsheet with no macros

• Automated relevant output

  • Interactive Dashboard

  • Customizable Calendar view

  • Cost calculation

  • Print or export as PDF and share

• Practical Functionality

  • Multiple aggregation periods (Daily, Weekly, Monthly, Quarterly, and Annual)

  • Planning Units (Hours and Cost)

  • Supports company Holidays

  • Allocate the same resource to multiple projects and/or skills

  • The resource can start and end any day

• Limitations

  • Default: Maximum 200 Resources, 100 Skill Groups, 100 Projects, and 200 Holidays.

If you need a custom solution expanding these limits, please reach out to support@indzara.com

Framework

Resource Capacity Planner – Excel Template – Planning Framework

How to use the template

Simplified Data Entry

The capacity planner template is designed to be very simple and easy to use.

Holidays

You can enter your company holidays and the template will set the availability of resources to 0 by default.

Enter Holidays

Projects

You can enter the list of projects. The template can handle multiple projects.

Enter list of Projects

Skill Groups

You can enter the list of Skill Groups. Skill Group (in other words, called a Resource pool) is how you can group resources for planning. For example, you may have 3 resources who are skilled in Marketing. Marketing is the skill group in that case and if you have demand for marketing work, all the three resources have the skills to complete the tasks.

Enter a list of Skill Groups

Resources

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.

Enter a list of Resources

Capacity of Resources

You can enter resources’ standard availability in the CAPACITY sheet.

Enter capacity for resource skill group and project

• Resources can be hired anytime and similarly, resources may leave a company anytime. The template will assign 0 availability before the start date and after the end date of each resource.

• Standard working hours for each weekday can be entered for each resource. The template will use this to calculate capacity automatically.

• Each resource can be assigned to one or more Skill groups.

• Each resource must be assigned to one or more Projects.

• Enter the cost per hour for each resource and the template can calculate costs in planning.

Vacation / Overtime
Though we have entered standard availability above, we know that employees may take a vacation or sometimes work overtime.  You can enter any vacation or overtime taken by resources in the VACATION_OVERTIME sheet.

Enter vacation or overtime for resources

Please enter -ve hours for vacation and +ve hours for overtime.

Demand
Enter demand data in the DEMAND sheet. Required fields are the Date of the task, the Resource assigned to do that task and Hours of work required, Project assigned and Skill assigned.

Resource Demand

Automated Relevant Output

There are two output sheets in the template: Dashboard and Calendar.

The dashboard shows the summary of metrics across all projects, skills, and resources.

Resource Capacity Planner – Excel Template – 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).

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

Dashboard – Charts – Capacity vs Demand in Hours

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

Dashboard – Charts – Capacity vs Demand in Cost

The Dashboard provides the metrics for the entire planning period duration in aggregate.

The Calendar sheet allows us to view capacity/demand by time periods within the planning duration.

Calendar

The calendar is very flexible as it has many options to choose from. It also has the slicers (filters) just like the Dashboard.

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 Units 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, the screenshot below shows a surplus/deficit in Hours by Skill Group every week.

Addressing Over-utilization and Under-utilization 

The Dashboard and Calendar provide the information that will be helpful in identifying the over utilized and underutilized resources.

In order to address the overutilization and underutilization, we have to do one or more of the following:

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

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 resources to Projects.