When it comes to important processes like annual reviews, goal-setting and compensation reviews, your HR team most likely does a lot of the heavy lifting. Given just how important these processes are, that is no small feat.
You don’t have to involve your full finance team or be a spreadsheet guru to create a comp review. With just Excel and the steps in this guide, you can create a great comp review to retain top talent and keep costs in check.
Make sure to download a copy of the salary benchmark Excel spreadsheet, which you’ll be able to use with your own data to create a comp review, rapidly.
How to Set Up Your Data For An Excel Compensation Review
When you build an Excel spreadsheet, the most important part is how you set the data structure. With the proper data structure, all of your analysis work is easier.
As you start to prepare your comp review, here’s a guide to the fields you should capture:
- Employee name – all of your data should be captured at an “employee level”, meaning it’s specific to each employee. You’ll need to capture the first and last name for each employee in separate columns, each on their own row.
- Current base salary – the current base salary is the starting point for all analysis, so make sure that you have this for each employee.
- Department (function) – comp structures vary by job role and function. Make sure to log the department for each employee so that you can apply comparisons accurately.
- Title – remember, the goal is to compare individuals to the overall population to find outliers. List the title for each employee so that you can make proper comparisons.
- Last year’s comp increase – every company goes through lean years. Sometimes, you’ll need to curtail raises so that you can make your budget goals. But make sure you track those adjustments each year so that employees aren’t disappointed in multiple years.
- Location – distributed teams are more popular than ever, so it’s important to note where each employee is working. Make sure to account for the cost of living for each employee based on location.
I call this tab the Base Data. It’s the single source of all your current information. Capture the data with care so that all of your analysis work is accurate.
After you capture your data in an Excel spreadsheet, you’re ready to start analyzing it. That means reviewing and summarizing. Let’s learn more about summarizing data for your comp review.
How to Create a Salary Benchmark in Microsoft Excel
When you review compensation, it helps to compare individuals to the overall population. Compensation reviews are designed to identify outliers so that you can adjust their compensation accordingly.
The first step is to create a salary benchmark. This helps you organize and summarize your data so that you can review situations that need to be adjusted. Continue reading here…
The post The Complete Guide to Compensation Reviews in Microsoft Excel appeared first on Workology.