What is a Utilization Rate and How to Build a Resource Utilization Report in Google Sheets?

Resource utilization is a metric to monitor the workload of your employees. It lets project/product managers identify the overburdened team members who have too many tasks and hence may under-perform, as well as under-utilized staff. The latter means that you are not utilizing your resources to their maximum potential. So you need to perform some reallocation. 

Both under- and over-utilizing staff can lead to a loss in profitability, employee burnout, customer dissatisfaction, missed sales opportunities, increased overhead and much more. So, without a doubt, resource utilization is one of the most important metrics for service companies and a good start would be to measure the staff utilization rate. 

What is a utilization rate?

Utilization rate is the percentage of time employees work on billable projects versus their overall availability. Billable hours are those that are spent working on fee-earning tasks or projects. In other words, it is the percentage of time a person actually spends on the job. 

Staff utilization rate can tell you:

• which services are most in demand and profitable. 
• how to better match employees with the right projects based on their skills. 
• whether you are fairly billing your clients.

Utilization rate formula

The utilization formula in its essence is uncomplicated. You take the number of billable hours worked and divide that by the total available hours, and express the result as a percentage.

Utilization rate% = Total billable hours ÷ Total available working hours x 100

Utilization rate calculation example

Let’s say we want to find the utilization rate for a software engineer who works 40 hours per week. If 34 of those hours are considered billable, while 6 are left for other tasks such as self-study or mentoring juniors, the calculation you make is 34/40 x 100 = 85%. The software engineer’s utilization rate is 85%.

Well, that’s pretty simple, isn’t it? However, a logical question emerges out of this calculation – is 85% high or low?

What does the utilization rate show you?

Resource utilization rate equal or higher than 85% 

• Planning and resource management is your strong suit
• Employees are motivated and engaged
• Help to spot high performers and reward or promote them
• Is a signal that you are over-committing employees and it may be the right time to expand, so you don’t have to deal with burnout or decreased productivity later

Resource utilization rate less than 85% 

• There aren’t enough tasks in the pipeline, and you need to bring in more work 
• Helps you uncover inefficiencies in your company and minimizes bench time
• Too many hours are being wasted on non-billable administrative functions and maybe you are being too bureaucratic 
• May indicate that your company has too many freelancers on projects, 
• Tells you that people are spending too much time on role-irrelevant tasks or other menial jobs.

Resource utilization rate above 100%

A utilization rate above 100% can imply a lot of out-of-scope work and poor planning.

What is a resource utilization report?

Tracking utilization rates in isolation doesn’t help you much. To get the most out of them, you should consider them in the context of your business and connect them to other key metrics, such as revenue, gross margin and others. Therefore, to visualize the extent to which resources were used and be able to look at it from a different angle, you need to create the staff utilization report. Without it, you won’t be able to see the whole picture and drive valuable insight on your business productivity.

How to create a resource utilization report in Google Sheets

If you have no special needs and do not want to invest in any dedicated software, Google Sheets will cope brilliantly with the task. For example, your report may look as follows:

The report contains the following columns:

• Logged: Number of hours logged by each employee.
• Total hours: Expected working hours that may include logged hours, sick hours and vacations.
• Billable: Hours worked on clients’ tasks.
• Non-billable: All other tasks.
• Actual utilization: Total hours divided by Available hours, and expressed as a percentage.
• Target utilization: A fixed value that can be set for each person or job role. 
• Available hours: Hours that the person is available. Typical availability is 8 hours per day, 40 hours a week. 
• Over/Under utilization: Actual utilization minus Target utilization, expressed as a percentage.
• Over/Under utilization in hours: Available hours multiplied by Over/Under utilization, expressed in hours.

Here is the template for you to use.  You can add or change any row or column or use the default ones.

How to get data for a labor utilization report template

What do you think about time tracking? When it comes to time tracking discussion, the respondents are usually strictly divided into two opposing groups: those who love the idea (and track every minute, literally) and those who completely loathe it (and complain about hyper control and stifled creativity). The truth is, as always, somewhere in the middle. 

But, like it or not, time tracking is essential for measuring your company’s utilization rate and creating reports because this is the major source of your data. With the help of your time tracker, you fill out Logged, Sick and Holiday columns. For example, you can use Clockify for your time-tracking purposes. It is really an advanced tool and can be easily integrated with Google Sheets without any coding. For that, you need to install Coupler.io, a solution for importing data from different sources, and read our step-by-step guide on how to export time data from Clockify to Google Sheets. 

As an alternative, you can also export time reports from Harvest to Google Sheets.

How to calculate employee utilization percentage in Google Sheets

So let’s then calculate utilization rate for each employee, provided that you have extracted Logged, Sick, Holiday, Total hours and Billable hours from your time tracker. 

What you need to do is compose a formula to divide Billable hours by Total hours and use the ARRAYFRORMULA Google Sheets function to output the whole range, not just a single value.  

=Arrayformula((G13:G24)/(F13:F24))

With the help of ARRAYFRORMULA, you can also calculate how many hours were spent on non-billable tasks. To do this, subtract Billable hours from Total hours: 

=Arrayformula((F13:F24)-(G13:G24))

It may be also valuable to understand who is over-and under-working to take corresponding actions. For that, you need to subtract Actual utilization from Target utilization.

=Arrayformula((I13:I24)-(J13:J24))

To find out  over- and under-working in hours you need to multiply Available hours by Over/Under utilization percentage.

=Arrayformula(K13:K24*L13:L24)

If you want to separate or compare the amount of over-work with the amount of under-work, here is where SUMIF function can come in handy.

Over Utilization

=SUMIF(M13:M24,">0")

Under Utilization

=SUMIF(M13:M24,"<0")

Sometimes you may want to look at over- and under-utilization by role. In this case, you can use either FILTER function or create a pivot table in Google Sheets like this:

You can definitely also use other Google Sheets functions to manipulate your table and extract more meaning from it. Google Sheets always has a proper function to help you carry out any task. The choice of formula depends on your aim. 

What does staff utilization report mean?

The major goal of analyzing employee utilization and creating staff utilization reports is to gain a clear perspective of each employee’s workload. This information will allow you to manage your resources more effectively and make smarter, data-driven decisions.

Here are some ways you can use your utilization rates to improve your company.

Resource management 

First and foremost, utilization report helps you plan your resource accurately to maximize billable utilization. That is to say, it helps:

• Optimize resource pool. Having found out whether you have spare capacity or not enough capacity, you can reallocate resources accordingly.

• Plan hiring. Resource utilization lets you do more than simply plan current engagements – it helps you foresee your recruitment needs and plan future efforts. If some people or teams are constantly over-utilized, consider expanding your headcount.
• Set goals. When your team members are tracking time towards specific tasks, or in the middle of working on them, it helps set clear goals for everyone and removes confusion about what each person should be doing. Besides, you can use this information for your performance appraisal period and set some employees targets to improve their rates, if needed.
• Make training and development decisions. Again, if spans of inefficiency are tracked, that’s maybe also because there is a knowledge gap in the team and it’s time to arrange some learning activities. 
• Share best practices and motivate the team. By tracking utilization of the team, you can learn which teams are more profitable and use this to dig deeper to understand the reason why. It may be that they do something their own special way, and that’s a great opportunity to share the tricks and tips with other teams and to raise the bar across your whole company. 

Manage scope creep

Projects tend to exceed budgets or timeframes and, if that goes unspotted, they can end up profitless. If you are tracking your time and utilization, you can spot scope creep early and renegotiate fees or tasks before the project overruns.

Create better proposals

You will have more insight into how much time certain types of projects really take and what level of staffing is needed for them. This will increase your ability to estimate more accurately when preparing a similar proposal. 

Target more profitable services

By knowing which of your services are more profitable, you can ensure that you focus on growing the right part of your business. And there is no need for your sales team to waste valuable time on leads that don’t align with the organization’s business strategy.

Well, this is far from being an exhaustive list and if you dig deeper into the utilization report you will see that it touches nearly every aspect of your company’s operations. 

Which is the optimal rate for employee utilization analysis?

Apparently, a 100% utilization rate is considered to be full capacity, but it is a hardly realistic target, and shouldn’t be the aim. There will always be non-billable hours, such as some administrative tasks, training and development, strategy, finance management, etc. And some researches even indicate that 100% utilization is harmful, as it can lead to burnout, high staff turnover, and a decrease in quality of work. So what is the optimal target utilization rate then? 

There is no one-size-fits-all number. Each company has to figure out its own unique calculation. But it is always worth taking into account the fact that people can’t be available 40 hours every week (they may have sick days, want to take days off, or are simply having unproductive days). What is more, not all job roles are considered to be equal in terms of utilization, and each job function (or person, or team, or department) has to have its achievable target utilization rate not to feel devalued. 

The resource utilization rate is more like a baseline, kind of a puzzle with unique pieces, that should be adjusted every time to fit your company’s needs. For your measurement, you have to think about: 

• How many non-billable hours are there in the total available hours?
• Are you basing the calculation on planned or reported working time?
• Are you including or excluding:
  • Vacation days? 
  • Sick days ?
  • Internal activities such as training, off-sites and meetings? 
  • Professional development or conference attendance? 
  • Public holidays?  

It would be also a good idea to track utilization over an extended time period to make sure you’re not just assessing performance based on a bad day or week. Good luck and high productivity!