Description

The Resource Capacity Planner Google Sheet 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 Resource Capacity Planner Google Sheet Template can also be used for non-human resources as well, for example, machines’ capacity in an industrial setup.

FEATURES OF RESOURCE CAPACITY PLANNER GOOGLE SHEET TEMPLATE

• Simple and easy
  • Simplified data entry
  • Simple Google Sheet spreadsheet with no Google Scripts
• Automated relevant output
  • Interactive Dashboard
  • Customizable Calendar view
  • Project and Resource based view for precise planning
  • 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 same resource to multiple projects and/or skills
  • 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

 

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 Resource Capacity Planner Google Sheet Template can handle multiple 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.

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.

Capacity of Resources

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

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

Automated Relevant Output

There are two output sheets in the Resource Capacity Planner Google Sheet Template: Dashboard and Calendar.

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

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.

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.

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

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

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

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.

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.

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.

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.

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.

 

You will find a detailed step-by-step user guide for how to use this Resource Capacity Planner Google Sheet Template.

If there are any questions about this template, please see the Product Support page for more information. You can also leave a comment there if you have any suggestions or unanswered questions.

Microsoft Excel

FAQ

Some common FAQs

• Step by Step User Guide
• Limitations of the Template
• How to extend beyond 300 rows in Capacity Sheet?
• How to extend beyond 500 rows in Demand Sheet?
• Why Demand is not included in calculations?

Visit our Knowledge base for any more, link here

For Product specific FAQs, link here