The Complete Guide to Annual Compensation Reviews Using Google Sheets

Learn how to do an annual comp review using Google Sheets.

Table of Contents

Annual compensation reviews are a must. Reviewing and resetting employee salaries help you ensure that your employees are paid fairly. In this article, you’ll learn to perform a salary and comp review in Google Sheets efficiently with our free spreadsheet template.

It’s no secret that compensation is crucial to attract and retain talent. When you lose an employee due to compensation, you’re sure to cost your organization more in the form of recruiting fees, signing bonuses, and loss of productivity.


This article is one of many tutorials on our blog. Among other things, we dive into actionable spreadsheet tips that you can implement in the office today. Is there something you’d like to know how to do in a spreadsheet? Let us know in the comments below!

What’s an Annual Compensation Review?

Beebole

What is a compensation (comp) or salary review?

A compensation review is when companies periodically review pay and other benefits to assure that each employee is compensated in line with their experience and expertise.

Let’s face it: we often become so busy in the day-to-day work of running a business that it’s easy to lose sight of people issues like compensation. An annual compensation review is a great time to revisit the pay of your teams and make adjustments where needed. Remember, this is also an important time to consider the true cost of employees in your organization.

Too often, companies adjust salary only as issues arise. Maybe an employee receives an offer from a competitor that leads them out the door. By then, it’s often too late to counter with a pay increase and retain them. Attracting and retaining employees is tough. Ask any HR manager, and you’re sure to hear that this tight job market is creating a battle for talent.

Let’s learn how to use a Google Sheets comp review that helps you keep your teams intact and your growth on track.

Elements of a Successful Comp Review

At its core, a successful annual compensation review asks one fundamental question: are my employees paid fairly?

There’s no single data point that can determine if someone is paid fairly. Instead, using several heuristics can help you identify outliers and adjust comp so that you don’t risk losing a rockstar employee.

Here are three key analytics to consider when reviewing compensation:

  • Comparison to peers (internal) – perhaps the riskiest part of comp reviews is if an employee finds out they make significantly less than a peer. You should review compensation for outliers to avoid just this type of situation.
  • Comparison to market (external) – what do employees at peer companies in similar roles make? Paying in line with the market is key to attracting and retaining talent.
  • Cost of living, performance, and other factors – salary is just one part of the “total compensation” package. You have to consider other factors that influence compensation levels, such as an individual’s performance and the geographic impact on cost of living.

With the help of a free and simple analysis tool like Google Sheets, you can incorporate all of these factors in your comp review. Let’s learn how to perform an effective comp review.

1. The Spreadsheet: Setting Up Your Data

When working in spreadsheets, half the battle is setting the data up correctly. All of your analysis is easier if you can build a central document with every data point you need. Let’s build a spreadsheet with perfectly structured data for easy analysis.

Here are the suggested fields that every salary review needs:

  • Employee name – capture the first and last names in separate columns.
  • Current base salary – a 5% increase is more impactful to senior employees than junior team members. Comp reviews require thinking in both total dollars and percent change.
  • Department – comparisons to peers rely on considering each employee’s department or “function” within the organization.
  • Title – checking for salary outliers is a matter of comparing to others in similar roles, so you need to capture job title.
  • Last year’s comp increase – you might not remember exactly what an employee received each year, but they certainly will. Monitoring for multiple successive years of low increases is a risk.
  • Location – remember, geographics impact the cost of living. Your employees in San Francisco might be paid differently than those in Austin.
Learn how to do an annual comp review using Google Sheets with this tutorial.
Get the Template & Start Implementing These Comp Review Techniques Today
DOWNLOAD THE SPREADSHEET & FOLLOW ALONG
Get the Template & Start Implementing These Comp Review Techniques Today

2. Create a Salary Benchmark

Once you have your salary and personnel information captured, it’s important to create averages and summarize the data. Each row in our salary data is an individual data point, but salary reviews are about comparisons to the average.

For this tip, we’ll use the Pivot Tables. These tables help us to summarize large sets of data to create a salary benchmark. Start by highlighting all of the rows and columns in our salary data, then go to the Data > Pivot table option. Then, click Create.

This Google Sheets tutorial walks you through how to do an annual salary review.

Remember, we want to compare individual salaries to the overall averages. That’s why we need to create those averages. But it’s also important to create averages within categories like a job title.

To do that, click on the Add button in the Rows section of your pivot creator. Then, choose Add again, and choose Title.

Now, you have a list of all departments and titles. The last step is to find the Values section on the right side and choose Current Base Salary. When you add it, you’ll see the total of all salaries across those titles. To change it to an average, choose AVERAGE from the Summarize by dropdown on the right side.

It's important to take into account average salaries when performing an annual compensation review.

So far, we’ve built a basic average, but it also helps to have the highest and lowest values for a given job title. We’re developing comparison points to help us understand where each employee falls on the salary curve, so knowing the upper and lower limits is key.

Click on Add again in the Values Section, and again choose Current Base Salary. This time, choose MIN from the Summarize By dropdown. This shows the minimum value (the lowest pay) for each title. Repeat the process, and choose MAX. Now, you have all of the needed data points.

Another key to a successful comp review at work is looking at the base minimum and maximum salaries.

In just a few steps, you’ve benchmarked salary across your organization. You know the average, the highest, and the lowest salaries by job title. Now let’s work to compare them.

3. VLookup formula for comp reviews: Comparison to Peers

First, let’s work to review salary in a peer group. Remember, the goal is to identify outliers so that you can course correct.

In the prior step, we created a benchmark that shows the average, maximum, and minimum salary by job title. Let’s leverage those statistics to review the annual compensation.

In the main sheet with all of our source data, let’s write a VLOOKUP formula to pull those benchmarks we created. For each employee, we’ll want to find the average salary from the benchmark, then compare it to the average.

Let’s create a “Salary vs Average” Column, then add this formula to it:

=VLOOKUP(E2,’Salary Benchmark’!B:E,2,FALSE)

This formula is looking up the job title in cell E2, then going to the tab labeled “Salary Benchmark” and pulling through the average for each title.

Now, let’s modify it to subtract the average from each employee’s salary:

=C2-VLOOKUP(E2,’Salary Benchmark’!B:E,2,FALSE)

Presto! Pull the formula down to apply it to each employee. Now, we have each salary compared to the average. Let’s also write two more formulas to compare versus minimum and maximum.

Salary versus minimum:

=C2-VLOOKUP(E2,’Salary Benchmark’!B:E,3,FALSE)

Salary versus maximum:

=C2-VLOOKUP(E2,’Salary Benchmark’!B:E,4,FALSE)

Notice that the only thing that changes between formulas is the column from the Benchmark to pull the comparable data from (column “2” for the average, “3” for the minimum, and “4” for the maximum.)

When performing an annual comp review, comparing salaries with peers is another important step.

Consider what a numeric value of “0” means for each of these fields:

  • A 0 in the Average column means the employee makes exactly the average for their title
  • A 0 in the minimum column means that the employee makes the minimum salary for their job title
  • A 0 in the maximum column means that the employee makes the maximum salary for their job title

Setting up these comparison points is key, but the real work begins as you start to review the data. Work through this column to find outliers and use it as the key input to flag needed comp adjustments.

4. Comparison to Market

The competition for top talent has never been tougher. It’s easy for your competitors to find your top talent and lure them away with a pay increase.

Unfortunately, there’s no structured way to create a comparison to peers. Here are three ways that companies gather competitive comp data:

  • Browse sites like Glassdoor of top competitors and build a list of salaries by job title.
  • Purchase a salary survey or data from a service like the SHRM Compensation Data Center.
  • Voluntarily ask for salary information from employees who are leaving the company in an exit interview. (Remember to ask for the title as well)

No matter what approach you use, create a new spreadsheet in your workbook called Competitor Salaries. Then, add the data you gathered from one of the techniques above.

Once you have your competitor data in a spreadsheet, you’ll need to normalize it for comparison. The hard part about creating comparisons to market is cleaning up the data. Titles aren’t always the same at every company so you’ll need to spend some time making it comparable.

The goal is to create a list that you can use for comparison. Each job title in your competitor compensation data needs to be matched to a comparable title within your organization.

Paste all job titles from your organization from our main tab into Column A. Then, use Data > Remove Duplicates and choose Remove Duplicates. This reduces all of the duplicates to each job title appearing once.

You can use Google Sheets to clean up data and get rid of duplicates when performing an annual salary review.

Now, you’ll need to map the titles from your company to comparable titles. In the first column, put the title in your team that most closely matches your imported data. The goal here is to create a comparable list that we can add to our comparison.

Learn how to use Google Sheets to perform an annual salary compensation review.

Once you’ve built out a comparable list, go back to your Base Data tab. Then, let’s write a VLOOKUP to pull the comparable compensation for each employee and compare it:

=C2-VLOOKUP(E2,’Competitor Salaries’!A:C,3,FALSE)

Use Google Sheets to analyze competitor salaries in your annual comp review.

With the new field you’ve added, a positive number means that the employee’s compensation is higher than competitive peers. A negative number shows the employee is paid lower than the competitive benchmark.

That’s it! You’ve added yet another crucial data point to compare each employee’s compensation.

5. Cost of Living And Other Factors

We all know that the cost of living rises every year with inflation. If you don’t include this as a part of your annual compensation increase, employees will feel their wallets squeezed and might look for a new job to “reset” their pay.

Many American companies use CPI, or consumer price index, as the inflation factor. CPI is published by the Bureau of Labor Statistics and describes how consumer goods are increasing or decreasing in price.

Don’t forget that pay also includes a number of intangible factors. For example, high potential employees might be paid more than peers in order to retain them for a long, productive career. All of these are factors “beyond the numbers” that are important to consider.

6. Applying Annual Compensation Adjustments

So far, our work has focused on setting up all of the data we need for salary comparisons. It’s up to your team to use this data to guide discussions and determine the appropriate adjustments.

Now, let’s create a spreadsheet, called Salary Adjustments, to calculate and apply the adjustments. First, start by creating a new sheet with all of your employees, their current job title, and their current salary.

Many teams apply compensation adjustments in multiple parts:

  • Cost of living / inflationary adjustments
  • Merit-based adjustments

In my experience, it’s typically best to keep these two components separate. Ultimately, every comp adjustment leads to higher payroll costs for the company. Keeping the two parts separate helps advise leadership of the cost of each component.

Let’s create two new columns: Inflation Adjustment, and Merit Adjustment. Then, let’s also create a Total Adjustment column.

Use Google Sheets during your annual comp review to easily look analyze salary adjustments.

In the inflation adjustment column, multiply the Current Base Salary times the adjustment you determined for inflation. I’ll apply a 1.8% increase across the board, and pull the formula down to calculate it for each employee.

One important thing during an annual salary review is adjusting for inflation.

Now, let’s apply a merit adjustment in column E. Multiply current compensation times the amount you’ve decided for each employee. Keep in mind that you don’t want to “double count” the increase with what you’ve already added for inflation.

Apart from inflation, you'll also want to take into account the merit-based increase during a yearly comp review.

One last step: create a SUM formula in the Total Adjustment column. This calculates the total increase in compensation for each employee.

When doing an annual salary or compensation review, by taking into account inflation and merit-based raises, you'll arrive to the total adjustment.

Now, you have everything you need to complete your compensation adjustment. You can report the total increase in payroll costs to management and issue a report to your managers as well.

5 Key Takeaways To Perform Your Comp Review

With the help of the right data, you can create a compensation review that’s fact-based and easy to explain to your employees. That helps ensure that you retain top talent and monitor your salary costs.

Keep these 5 key points in mind before you start your comp review:

  1. Spend the majority of your time creating a salary benchmark. This gives you data points to flag outliers for adjustment.
  2. It’s not easy to assemble the data, but including some comparison to your competition (both local and in your industry) is important.
  3. Keep the non-numeric factors in mind as well, like ensuring that your high potential employees are paid to retain them.
  4. Make sure that you calculate merit and cost of living as separate figures for easy reporting.
  5. The data you put together in this tutorial is just a starting point for your annual compensation review. Ultimately, you’ll need to balance the data with management conversations and budgets as well.

Remember, this post is part of our tutorial series, where we uncover nifty tips in Google Sheets and Microsoft Excel. If you found this post helpful, don’t miss this one on Excel Power Query and 5 business intelligence hacks you can try, this handy tutorial on building an automated employee cell phone data usage analysis, or this how-to on automating emails from Excel (which is especially useful when sending bonus emails!)

Next steps

Don’t forget about Beebole! It’s a place for employees to safely track hours worked and time spent on projects, as well as a powerful tool for project planning and accurate budgeting. By combining data like specific project estimations and employee salaries, you’ll be well on your way to unlocking invaluable business intelligence. What’s more, Beebole offers free integrations for Google Sheets and Microsoft Excel, so you can easily link all of your time data with spreadsheets in real time.

Streamline your HR Management

Project estimations, employee costs, absences and more. Try free. No commitment.

The experts who have written or contributed to this article are independent from Beebole, and their contribution doesn't serve as endorsement for our company/tool or their past/present organizations, employers, or associates.
Andrew Childress is a former FPA & CFO with a passion for simplifying, understanding, and analyzing financial data. He founded the FP&A consulting firm Beyond a Number to show others that great financial planning really starts once you go beyond the spreadsheets and numbers, and truly understand the data in front of you.

Comments

I rather like this tutorial however my present task is trying analyze my friends total financial position and earnings from the perspective of her past situation and present. This needs to also look at the advantage/disadvantage of rrsp, tfsa to see her best approach. She needs to reduce her tax exposure and the rrsp seems to be one present way. Obviously this is for Canada and Ontario.

Thanks so much for reading! I’m not as familiar with Canadian retirement plans, but in general, what helps me at times like this is to build out a few scenarios. Try carrying it all the way through under one plan versus the other and see if that teases out the details.

Related posts

What does annual compensation mean? (And 9 more up-to-date FAQ on employee compensation in the US)

Published: 2020/7/9 | Katie Stearns

When it comes to annual salary and compensation, there can be a bit of confusion. What do these words encompass, ...

Read more

How to Use Google Sheets Pivot Tables, Column Stats, Explore, & QUERY Function

Published: 2021/9/15 | David Benaim

As a manager, perhaps you’ve wondered how to take your financial reporting to the next level or how to amp ...

Read more

5 Google Sheets Formulas for HR Managers

Published: 2019/10/3 | Andrew Childress

Beebole’s blog is home to some really great tutorials. Often, both Google Sheets and Office 365 experts dive into practical ...

Read more