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 (Advanced )- Allocation View
Resource Plan Template Excel (Advanced) - Resource Load View
Features and Advantages of the Excel Resource Plan Advanced Version
- The resource plan can handle multiple projects and teams. You can also check how to create a team resource plan.
- 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 resource plan is based on weekly allocations so you can plan for the long term.
- The resources can be set up with a team and a daily rate.
How the 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.
- 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.
- 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 Load View
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.
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