Resource Capacity Planner Excel Template
Originally published: 16/11/2021 15:56
Last version published: 18/11/2021 08:44
Publication number: ELQ-92867-2
View all versions & Certificate

Resource Capacity Planner Excel Template

This template will enable you to easily calculate the available resource capacity, compare with demand, and identify surplus/deficit in capacity.

This workforce planning tool can be employed in multiple different contexts such as by an HR manager to plan their team’s capacity each month and thus calculate how much work can be achieved. Alternatively, it could be used for non-human recourses such as machines’ capacity in an industrial context.

How do you use the template?

1. The capacity template is efficient and easy to use.

Holidays: Input your company holidays into the chart and the template will automatically change the availability of resources to zero.

Project: Input multiple projects to the list.

Skill Groups: Enter the list of Skill Groups which refers to how resources can be grouped together for planning.

Resources: The word ‘Resource’ in the template refers to employees, but it could also refer to machines if that is in the data you are working with.

Capacity of Resources: In the CAPACITY Sheet, enter the resources’ regular availability.

• Resources may come in or out at any time. The template will automatically allot 0 availability before the start/after the end date of each resource.
• You may enter average working hours for each weekday and for each resource. Resultingly, the template will calculate capacity automatically.
• Resources may be allocated to one or more Skill groups.
• Resources must be allocated to one or more Projects.
• Input cost/hour for each resource so the template can automatically calculate planning costs.

Vacation/Overtime: Enter in any vacation (negative hours) or overtime (positive time) taken by resources in the VACATION_OVERTIME sheet.

Demand: In the DEMAND sheet, enter the demand data including the Date of the task, the Resource assigned to complete that task, Hours of work required, and Project and Skill assigned.

The template contains the Dashboard and Calendar output sheets. The dashboard presents the summarized metrics across all projects, skills, and resources. Use the slicers to filter down to specific projects, skill groups or resources.

The dashboard’s next page contains the Capacity vs Demand in Hours charts which express the overall capacity vs demand for each project, skill group and resource.

The final page contains the Capacity vs Demand in Cost charts for each project, skill group, and resource.

The calendar: This feature is entirely customizable and contains slicers. It may be created for the list of resources, skill groups or projects. In the calendar, you may choose to display capacity data, demand data, or surplus/deficit data. The unit options are hours and cost, and you may choose to depict the data daily, weekly, monthly, quarterly, or annually.

The information provided by the Dashboard and Calendar will aid in classifying the over/under utilized resources. To address this, you must do one or multiple of the following:

1. Change the capacity: In the Resources sheet, adjust the standard availability of resources or add overtime or vacation time.

2. Change the demand: If you can, edit the demand hours to reduce it and thus allow the capacity to fulfil the demand.

3. Change the allocation or assignment: Change the assignment of tasks to a different resource or change the capacity of allocation of resources to Projects.

Features Summary:
• 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 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.

This Best Practice includes
1 Excel Template

Acquire business license for $50.00

Add to cart

Add to bookmarks


0.0 / 5 (0 votes)

please wait...