Free Resource Plan Template : Track Over & Under Allocation

by Elina D

Advanced Resource Planning Template Excel

Resource Management becomes more challenging when you have multiple projects and different teams. This Excel Resource Plan is designed to work with multiple projects and teams and highlight over-allocation and underutilization, making Resource Management more effective.

Resource plan allocation, resource plan template, resource plan allocation view

Resource Plan (Advanced )- Allocation View

Resource Plan Resource Load View, resource plan, resource plan template -Resource Load View

 Resource Plan Template Excel (Advanced) - Resource Load View

Features and Advantages of the Excel Resource Plan Advanced Version

  • Allocations for individual resources, teams, or projects can be viewed using default filters in MS Excel.
  • The resource load view provides a clear picture of how the resources are allocated and gaps in the plan.
  • The resource plan highlights over-allocated, under-allocated, and partially allocated resources.
  • The Team and Rate are auto-populated with the resource name. Also, the cost is calculated for each allocation.
  • The resources can be set up with a team and a daily rate.

How Excel Resource Plan Advanced Version Works?

The Resource Plan has 3 key sections - The Resource Plan, Resource Load, and Config sections. All the sections are contained within separate sheets.

The resource plan takes the allocations from the resource plan sheet and then combines them to generate a single view for each resource. Even if the resource has multiple allocations, the resource plan will sum up all the allocations for that resource and display that in the resource load sheet.

Resource Plan

  • The first image in the blog (Image 1) and the first sheet in the spreadsheet is the resource plan.
  • This sheet contains the allocations.
  • Each row represents an allocation for a resource. It can be different projects or the same project for different dates.
  • When starting for the first time, DO NOT DELETE ALL THE ENTRIES, as you might delete all the formulas. Always copy the row to make a new entry.
  • The resources come from the Resource Load sheet. The team, daily rate, and effort will be auto-calculated after you select the appropriate resource.

Resource and Capacity Plans Pack

Resource Load

  • This sheet contains the resource details (Resource Name, Team, Rate) and allocations auto-populated from the resource plan allocation. Different colors are explained in the image below.
  • DO NOT CHANGE THE ALLOCATIONS ON THE RESOURCE LOAD sheet. The allocations should be auto-populated from the resource plan sheet.
  • The resource load sheet highlights cells to highlight issues.
  • If a cell is highlighted with red color and white text, that means the resource is over-allocated in that week.
  • If the cell is highlighted with green color and green text, then all is good as the resource is fully utilized for that week.
  • If the cell is highlighted in white with red text, it is partially allocated in that week.
  • If the cell has a red color with red text and has a 0 in it, it has not allocated in that week.
Resource Loading Screen Explained, Resource Load View, Resource Over allocation View

Resource Load View

Config Section

The config section has the following -

  • Start Date - Start date for the resource plan.
  • Weekly Days - The no. of days your team will work in a week.
  • Min Days - The minimum day's threshold per week. If the resource allocation goes below this value, then the resource is indicated as underutilized.
  • Projects - The list of projects for which resources will need to be planned. The max. is 50 projects. It can be more, but then the Data Validation will have to be changed accordingly.
Resouce Plan Configuration Options, Resource Plan, Resource Plan Config Section, resource plan template

Resource Plan Config Section

Steps to Start Using the Excel Resource Plan Advanced Version

Step 1: Enter the values for the Config sheet. Configure the start date, weekly days, Minimum Days, and the project you want to resource.

Step 2: Enter the resource details in the Resource Load sheet. Enter the resource name, Team, and Rate. DO NOT ENTER ANYTHING UNDER THE WEEKS AS THAT WILL BE AUTOMATICALLY POPULATED WITH ALLOCATION DATA.

Step 3: Based on the project resource requirements allocate resources in the first sheet Resource Plan. Each will be an allocation. Always try to avoid partial allocations as it complicates the allocation.

Step 4: As you are allocating the tasks, check the resource load sheet to ensure that all the resources are allocated properly without over or under allocation.

Except for the config in Step 1, you will be repeating steps 2,3 and 4 regularly as resource requirements tend to change. A resource manager will typically update the resource plan at least once a week.

Click Here To Download Free Excel Resource Plan Template

Resource and Capacity Plans Pack