The Complete Guide to Annual Compensation Reviews 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?
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.
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.
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.
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.
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:
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:
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:
Salary versus maximum:
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.)
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.
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.
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:
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.
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.
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.
One last step: create a SUM formula in the Total Adjustment column. This calculates the total increase in compensation for each employee.
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:
- Spend the majority of your time creating a salary benchmark. This gives you data points to flag outliers for adjustment.
- It’s not easy to assemble the data, but including some comparison to your competition (both local and in your industry) is important.
- Keep the non-numeric factors in mind as well, like ensuring that your high potential employees are paid to retain them.
- Make sure that you calculate merit and cost of living as separate figures for easy reporting.
- 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!)
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.
Beebole’s Google Sheets integration is the winner for us. I love that you can easily integrate with Sheets; it makes my reporting an absolute breeze. Two clicks and the latest data is imported and ready for interrogation; it makes Beebole a really powerful tool.
Operations Director at Ostmodern
Our go-to timesheet system. We’ve been using Beebole for many years and we have never thought about switching to another platform. The system’s ease of use and our employees’ satisfaction with Beebole make it our best choice. The speed and helpfulness of their support team also deserve praise. Beebole is a user-friendly, intuitive, and easy-to-use timesheet system. The initial training for a new employee takes less than 30 minutes.
Country Operations Manager at HungaroTrial
The best part of Beebole is the customer service. With previous, more costly systems, it would take days or sometimes over a week to get a response, which often wasn’t helpful at all. With Beebole, we get replies within the hour. The Beebole team is incredibly patient and fast and we are continuously uncovering new ways the system can work harder for us. It is very user-friendly and easy to set up and understand. Pulling reports is so incredibly simple that our team was able to figure them out without any training.
Senior Manager at Faulhaber Communications
Beebole is TOP! I have tried different time tracking tools, but once I had my free introductory call with the Beebole team, I knew this was the best. It saves a lot of my admin time, the support team is fantastic, the tool is easy to use, and the reporting is easy. I don’t understand why I used Excel for so long.
Founder of Studio Pharma
How to Automate Employee Cell Phone Data Usage Analysis with Google Sheets (Free Template)Published: 2019/12/3 | Brian Wochele
There’s no arguing the importance of having a good handle on your employee cell phone data usage, nor the many ...
Contractor Payments and Pay Reports Made Easy with BeebolePublished: 2023/2/28 | Andrew Childress
If you have a team of contractors, paying them in a timely manner is a must. You need a speedy ...
Automating Emails from Excel: The Easiest Way for HR to Send Employee Bonus EmailsPublished: 2021/4/20 | Szilvia Juhasz
“Annual compensation and bonus planning is easy, ” said no Human Resource professional, ever. That’s precisely why we’re going to ...