PRODUCT

Resource Capacity Planner Excel Template

PURPOSE

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

This workforce planning tool can be used in different contexts. For example, a HR manager or a team manager can use this to plan 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.

REQUIREMENTS

  • Microsoft Excel for Windows (2010 or later)
  • Microsoft Excel for Mac (2016 or later)

PRODUCT TOUR VIDEO

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
    • Supports customizable Weekends
    • Resource can start and end any day
  • Limitations
    • Maximum 2 years of planning period
    • Maximum 100 Resources, 100 Skill Groups and 100 Projects

    • We have versions that support 200 and 300 resources. They are larger in size and slower. Please email support@indzara.com after purchasing.  
  • No Task scheduling feature (available in Project Planner template)

 

Download Brochure: Resource Capacity Planner v2 Brochure

HIGHLIGHTS

Simplified Data Entry

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

You will enter the basic settings in the SETTINGS sheet.

Settings - Planning Period, Weekends and Holidays
Settings – Planning Period, Weekends and Holidays

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

Weekends
You can choose which weekdays are weekends from the list of options available. The template will set the availability of resources to 0 by default.

Holidays
You can enter your company holidays and the template will set the availability of resources to 0 by default. Each holiday has to be entered individually.

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

Settings - Projects
Settings – Projects

Skill Groups
You can enter the list of Skill Groups. Skill Group (in other words, called as 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.

Settings - Skill Groups
Settings – 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.

You can enter resources and their standard availability in the RESOURCES sheet.

Resources Data - Standard Availability and Assignment to Project and Skill Group
Resources Data – Standard Availability and Assignment to Project and Skill Group
  • 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 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 must be assigned to one of the skill groups.
  • Each resource must be assigned to one of the Projects.
  • Enter 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 vacation or sometimes work overtime.  You can enter any vacation or overtime taken by resources in the VACATION_OVERTIME sheet.

Vacation and Overtime Information for Employees
Vacation and Overtime Information for Employees

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

 

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

Enter Demand Tasks assigned to each resource
Enter Demand Tasks assigned to each resource

 

Automated Relevant Output

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

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

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.

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

Dashboard - Capacity vs Demand Cost Charts
Dashboard – Capacity vs Demand Cost Charts

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

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

Resource Capacity Planning Calendar
Resource Capacity Planning Calendar

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, the screenshot below shows 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 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.

 

If there are any questions about this template, please see the Product Support page for more information. You will find a detailed step by step user guide for how to use this template. You can also leave a comment there if you have any suggestions or unanswered questions.

BUY

US$50 US$40

CUSTOMER RATING

100% SATISFACTION

If you are not satisfied with the product, please e-mail support@indzara.com and we will issue a full refund.

FILE DELIVERY PROCESS

You will be able to download the file immediately upon payment. You will also receive an e-mail immediately with the download link.

FREE UPGRADES

All future updates and upgrades are absolutely free.

LICENSE TERMS

PRODUCT SUPPORT

Product Support

OFFER

Offer Details

Special Offer - Buy One, Write Review, Get One Free

ADD A REVIEW

1 2 3 4 5

US$50 US$40

You may also like…

  • PM Toolkit - Product Logo

    Project Management Toolkit Excel Templates

    Rated 5.00 out of 5
    Sale! US$200 US$100
    Add to cart