Picture this:
It’s Monday morning, you’re sipping your first coffee of the day, and your inbox is already brimming with project updates.
As a manager or team lead, you’re constantly juggling tasks, deadlines, and, most importantly, people. You’ve heard the term ‘resource utilization’ thrown around in meetings and webinars, but what does it really mean in the hustle and bustle of your daily grind?
Well, let me break it down for you.
Think of resource utilization as your secret weapon in understanding exactly how your team is performing. Are they overworked, underutilized, or just right? It’s like having a fitness tracker, but for your team’s productivity and workload.
In this article, I’m going to guide you through the ins and outs of calculating resource utilization. We’re not just talking numbers and formulas here; we’re diving into real-world scenarios, just like the ones you face every day. From understanding the basics to applying them in practical, no-nonsense ways, I’ve got you covered.
What’s more: I even have a read-to-use Excel template for you where you can quickly calculate the utilization rate for your team members on an individual basis.
My goal? To give you crystal-clear clarity on your team’s productivity.
So let’s dive right in.
What is Resource Utilization?
In simple terms, resource utilization tells you how much of your team’s available time is being spent on productive, billable work. It’s about finding that sweet spot where your team is busy enough to be productive but not so swamped that they’re on the brink of burnout.
Understanding resource utilization will give you a roadmap for managing your team. You’ll see how your team’s time is being spent today and are able to make smarter decisions about tomorrow – like figuring out when it’s time to expand your crew or pump the brakes on hiring. It’s all about aligning your team’s capacity with the long-term vision for your projects.
Calculating resource utilization: What you need
To calculate resource utilization, you need specific pieces of information about your team and the projects they are involved in.
Here’s a breakdown of the information you need to gather:
- Total Available Hours
- Planned Leaves and Absences
- Project-Specific Effort
- Billable Hours Worked
- Non-Billable Hours Worked
- Overtime Hours
Let me explain what’s behind each item:
Total Available Hours:
The number of working hours your team members are expected to be available during a given timeframe, usually measured in hours per week.
Most team members will be working full-time, which would be somewhere between 35-40 hours per week, depending on your country. There may also be a few part-time workers who only work for a fraction of the week, let’s say 3 days per week (equating to 24 hours per week, assuming an 8-hour working day).
Leave and Absences:
This includes scheduled leaves, vacations, or unplanned absences that affect the availability of team members. Start by getting the public holidays for your state. Next, review your company’s closing calendar for the year, which may cover time off due to company-wide retreats or mandatory shutdown periods. Leaves and absences will reduce the total available hours for the respective period.
A good site to get the public holidays for your area is https://www.officeholidays.com/.
Project-Specific Information:
Get a detailed breakdown of project tasks and the estimated hours allocated for each task. To be able to calculate the expected utilization rate for a specific period, you also need to know what week or month each task is scheduled for.
If you want to calculate resource utilization retrospectively — that is for the past weeks or months — you also need the following data:
Billable Hours:
Get the hours each team member has spent on tasks that contributed directly to project deliverables or client work.
Total productive hours vs. billable hours: There are different ways of calculating resource utilization. A key difference is the type of hours you include in the calculation. If you are working for external clients, utilization is usually calculated based on billable hours. Why? Because only billable hours contribute to the company’s profit, which is the key metric for any business (or profit center).
On the other hand, if your team is working on internal projects only and their work isn’t billed to external clients, the concept of resource utilization still applies, but we calculate employee utilization using the total productive hours instead of billable hours.
Non-Billable Hours:
For sure, your team members have spent time on activities that were necessary but could not be charged to clients, like training, internal meetings, and administrative tasks.
Overtime Hours:
Did your team members work beyond the standard expected hours? Then we need to get the overtime hours – because overtime work will impact overall utilization rates.
With these variables in hand, you can now calculate the resource utilization rate for your team – individually per resource and aggregated for the whole time.
Formulas for calculating resource utilization
Here are the formulas for the calculation of resource utilization:
Case 1: The team works for external clients
Resource utilization = (total billable hours) / (total available hours) * 100%
Case 2: Work is provided to internal clients only
Resource utilization = (total productive hours) / (total available hours) * 100%
Let’s calculate resource utilization in Excel!
Excel is the ideal tool for tracking resource utilization. Not only can the calculation be done with just a few clicks. You can also visualize the utilization numbers for your team using the built-in charting features.
Step 1: Create a planning sheet
Let’s kick things off by crafting a planner sheet or calendar in Excel, which will serve as the foundation for tracking your team’s resource allocation. We’ll dive into the nitty-gritty of optimizing the layout for maximum effectiveness later. For now, focus on setting up a basic calendar covering the timeframe you’re aiming to monitor utilization for. Before doing so, decide if you want to monitor utilization on a weekly or monthly basis.
Make sure your sheet contains the following information:
- Columns for the timeframe you want to monitor utilization for, including appropriate header cells for the weeks, months, quarters and years
- Number of working days per time interval (e.g. week)
- Data rows to list all team members, including their estimated workload
Here is what my planning sheet looks like:
There’s a grey section for each team member. At the top, you’ll see their name. Below that, there are rows for each project they’re working on. And at the very bottom of their section, there’s a darker grey row that shows how much of their time is used up that week.
Notice also I have added a few more details such as the week number and the number of working days per week. We need the latter for correctly determining team members’ available capacity. I use a different tab in the spreadsheet to list all the public holidays and company closing days for the year, which helps in determining the working days via a formula.
Step 2: List all projects including planned effort
For each team member, write down all their projects and note down how many hours they plan to work each week. The effort estimation should be supplied by the project manager of each project.
I just did this for my team, including myself. The end result looks like this:
Each line shows the number of hours the individual is expected to work on a particular project within a given week. The planned effort varies from week to week, depending on the stage of the project and the type of work scheduled for that week. As a result, the total used capacity also fluctuates from week to week.
This can be seen from the color coding of the weekly capacity totals:
In our planner, we use colors to show how busy everyone is each week. If the total for a week is green, it means that team member still has some free time. If it’s red, it means we’ve given them more work than they’re supposed to do. And if it’s white, it means the amount of work they have is just right for their available time.
You see, determining resource utilization is that easy!
If you don’t care about utilization in percentages, you are already done.
With the above overview, you can already see how your resources are utilized.
To get the actual percentage values, let’s go to the next step.
Step 3: Let's calculate utilization rates
We’ll set up a different sheet to calculate the utilization rates, keeping the main view uncluttered. This allows us to track utilization in the way we prefer – either by counting working hours or using percentage values.
Utilization rates will be calculated using the resource plan we have prepared earlier.
For this example, I will determine utilization rates on a per-week basis. This is the right level because it gives us a reliable insight into the actual (perceived) workload of our team members. It also lets us balance the workload better, so nobody gets too much on their plate.
This is the final table showing people’s utilization rates:
In the 2nd column, we have people’s total weekly capacity, which we need for the calculation.
How utilization rates were calculated:
- Remember we need to divide a person’s true workload by their total available hours for a particular week.
- The total available hours depend on the individual’s official working time – are they working 40 hours per week? Or maybe more? Or less?
- Finally, we must subtract any public holidays or company closing days that fall within a given week from the employee’s total available hours.
Keeping these points in mind, the Excel formula needs to be as follows:
Let’s put in some real numbers:
Example:
Explanation: For an employee whose standard contract is 40 hours a week (Monday-Friday = 5 working days), but who effectively works 39 hours during a four-day workweek due to 1 public holiday, the utilization rate is 122%.
That means even when a resource works less than their standard hours, they can be deemed over-utilized if the workweek has been shortened due to a public holiday!
Congratulations!
Now you’ve learned how to calculate resource utilization in Excel.
The key is really to build a good utilization monitoring sheet tailored to your business model, providing you with the insights you need to make sound decisions about who is doing what and when.
The good news is that, for most cases, the weekly format shown above is just fine. So you can simply build your template using my screenshots.
If you don’t have the time to build a utilization monitoring sheet from scratch, you can download my resource planning template, which I have used for this example.
It’s much more than just an Excel template.
It is an integrated resource planning solution where you can assign people to projects and conveniently plan the resource allocation for your team. Of course, utilization monitoring is included. And it’s all happening within Excel!
Here’s a quick overview of my template:
My Ready-to-Use Resource Planning & Utilization Monitoring Template
My Resource Planning Template is a user friendly Excel tool designed for juggling resources in environments with multiple projects. It has gained popularity worldwide as it drastically simplifies resource planning and makes it clear to see each person’s workload and project duties.
Some of its unique features:
- Generate perfectly-styled planning sheets with the click of a button
- Assign team members to projects
- Plan team members’ individual workload on a per-week basis
- Color indicator to show over/under-utilization
- Utilization monitoring report
- Spares you the effort of manually adjusting formulas and styling (all happens automatically)
- You only need Excel to use it
Would you like to know more about the tool?
Wrapping It Up: Mastering Resource Utilization in Excel
As you’ve seen in this article, calculating resource utilization in Excel is straightforward. The journey begins with setting up a simple planning sheet – and I recommend a weekly format, as it tends to fit most scenarios perfectly.
For a ready-to-use resource planning sheet, check out my Excel Resource Planning Template (link), which is used in organizations all around the world.
The real task lies in gathering the necessary data, which is often the more time-consuming part. It involves collecting a variety of information: a list of your team members, the different projects they’re involved in, individual working hours. Plus, don’t forget those non-working days like public holidays. All these details need to find their way into your Excel workbook to crunch the numbers correctly.
Remember, the formula for resource utilization is simple: utilization rate equals the total billable hours divided by the total available hours within your chosen timeframe.
Once you’ve got your utilization sheet set up and all the data keyed in, you’re ready to calculate the utilization for the desired period. But here’s a heads-up: Keeping this sheet updated is where the challenge lies. Project schedules change, tasks get rescheduled, and team members go on vacations or fall sick. These changes mean your planning needs to be adjusted regularly. Encourage your team to communicate any such changes immediately, so you can adjust your resource planning accordingly.
Wrapping this up, the real benefit of having a clear view of your team’s resource utilization is the peace of mind it brings. As a project manager or team leader, you won’t be left guessing about resource availability or conflicts. You’ll have a transparent, up-to-date overview, allowing you to manage your resources effectively and avoid overburdening your team.
Got A Question? Need Some Help?
Have a question about this article? Need some assistance with this topic (or anything else)? Send it in and I’ll get back to you personally.
