One of the tricky parts during project planning is to develop a solid resource allocation for the involved team members. These may include in-house team members but also contractors who will be supporting the project.
The challenge is your team has limited capacity, and you need to balance the needs for your project with the resource requirements of other projects that your team members are working on.
In practical terms, you need to have a way to plan and track resource utilization for your team members. The tool of choice for this purpose is Microsoft Excel, and in this article I’m going to show you how you can calculate resource utilization in Excel.
What you need for utilization tracking
For creating an accurate resource utilization plan, you need the following details:
- List of team members
- Your team members’ capacity (i.e. their standard working time, e.g. 40 hrs/week)
- Your national holidays and other non-working periods to be taken into consideration
- Projects and tasks your team members are going to be working on
- The planned efforts per team member per project (broken down by week)
For this example, I’m going to assume each team member is working on multiple projects. They may also be spending some time on team activities such as team meetings every week. So you can’t allocate their complete working time towards projects, but instead you need to keep some buffer to ensure people are not being overcharged.
The following screenshots are taken from my Excel Resource Planner which has become very popular among team leaders and project managers.
You can see the exact process for creating a resource utilization report.
Step 1: Enter team members
We first enter our team members’ names into the spreadsheet:
In column B you can see I have entered the standard working hours. This information is needed to achieve accurate resource utilization data. Some team members are working only 20 hours per week, so these are my part-time workers.
Step 2: Enter projects and tasks
Then I enter the list of projects and tasks my team members will be working on:
As you can see, I have a generic task called “Admin & meetings”. This is to account for all the non-project and admin tasks every employee is typically also busy with.
Step 3: Assign projects and tasks to team members
Next we need to specify which exact projects and tasks each of our team members are going to be working on. This can be done in the dedicated assignment section in the Resource Planner:
The project assignment can be done very easily using a drop-down menu where you select the project or task you want to assign.
Step 4: Generate the resource planning sheet
Once we have entered our team members and done the project assignment, you generate the resource planning sheet. You can specify the country whose national holidays should be taken into account on the planning sheet:
(I won’t cover the other parameters shown above – they are explained in the Help tab).
Step 5: Enter the planned effort for each team member
In the below screenshot you can see the resource planning sheet. Now I enter the estimated effort for each team member and for each specific project. In my case, I have 7 projects that I am working on.
So I am going to enter the estimated hours on a weekly basis:
The resource planner automatically adds up the weekly effort numbers and shows the effective total weekly working hours in the dark gray row.
Let’s interpret the numbers:
In some of the weeks, you can see that I’m effectively working more hours than foreseen by my contract: For instance, in week 2 I am working 42 hours and in week 7 I’m spending 41 hours on projects where I should be working only 40 hours per week. The nice thing about the Resource Planner is that it provides color-coded resource utilization numbers:
- Whenever a resource has been given too much work (and we are exceeding our team member’s capacity), this will be shown as red.
- Whenever actual hours and official working hours are in balance the total value appears in white.
- Whenever we have some capacity left, the total value is shown in green (example: week 5 – I’m only working for 38 hours instead of 40 hours).
Can you guess why in week 4 the total hours appear in red (indicating an over-utilization) when in fact I have only been allocated 35 hours of work?
Well, that’s because there is a national holiday in week 4, so we are losing a total of 8 hours in working time.
An easy way to allocate project work and track resource utilization in Excel
As you can see, calculating resource utilization and performing the resource allocation to various projects is fairly easy even with standard Excel. The Resource Planner I used for this example is based 100% on standard Excel. It simply automates some of the functions and keeps the layout and formulas consistent whenever you add team members or add projects. In fact, when you use the Resource Planner, you don’t have to do any manual editing of formulas – it’s all done automatically within Excel.
The Resource Planner for Excel
Work allocation and utilization tracking made easy!
