Carry out a pay equity audit and then make data driven salary adjustments all in one spreadsheet.
Intro
Salary benchmarking is the process of comparing your roles and salaries to similar roles and salaries in the market.
By slotting your employees into their respective roles, you can then carry out a pay equity audit and uncover whoâs being under or in some cases over paid.
Once you know where your pay equity challenges lie, you can then take steps to address them, by adjusting pay for employees who are underpaid, while also making sure that you have constructive conversations with those who are overpaid.
How this template works
The spreadsheet consists of âInputâ and âOutputâ sheets.
Input sheets
- Market data
- Setup
đ˘ This is where you copy and paste in your organisations salary bands (salary benchmarks).
đ˘Â Customise certain elements of the calculator such as the default currency you wish to work in, currency conversion rates, how youâd like to round the data, the number of levels you have, naming conventions for the levels, gender types, employment types etc.
Output sheets
- Employees
- Dashboard
- Gender
- Team/dept.
- Level
- Tenure
- Calculator
â Â This sheet will show you how your employees stack up against your salary benchmarks and uncover where pay inequities exist using either âPosition in bandâ or âCompa-ratioâ (more on how these work below).
â Â The sheet will automatically calculate suggested salary adjustments to move employees to the min or mid point of the band.
â Â Capture manager input into salary changes and see how their requests stack up against the suggested increases.
â Â A macro view of your organisation, allowing you to discover what it will cost to bring employees who are underpaid to your desired market rate.
â Â Uses GoogleFinance, to normalise multiple currencies into one, making budgeting a breeze across multiple locations.
â Â Calculate your gender pay gap.
â Â Analyse pay gaps based on:
â Â Easy to use pivot tables that allow you to dive deeper into any problem areas that require further investigation.
â Â Create departmental/team budgets for managers and/or finance.
â Â A calculator to help you quickly view your salary bands based on a location, job family and employment type.
- Benchmarks Matrix
â Â A quick snap shot of all your market data for all locations, job families and levels in one place.
Analysing pay equity in your organisation
The spreadsheet looks at pay equity in two common ways:
- âPosition in bandâ aka âSalary range penetrationâ
- âCompa-ratioâ
I dissect each one below, so you can come to your own conclusion on which one to use.
1. âPosition in bandâ aka âSalary range penetrationâ
This is my personal favourite and allows you to explore the full spectrum of the salary band, allowing you to understand where an employees salary sits.
In addition to this, it allows you to clearly identify employees who arenât even paid within the salary band i.e. they are below đ or above đ the band
Formula
Range penetration = (Employee salary - Min salary) / (Max salary - Min salary)
- A range penetration of 33% in the example below means the person is within the band â . They have another 77% left to go until they need to have her position, or range, looked at.
Below the band
- I prefer using this metric as itâs a great way to identify anyone who is below the min of the salary band and the cost to move them to within the band. This allows you to make the most important adjustments first to ensure pay equity.
- If you capture the right details in the spreadsheet, you can also look at position in range by gender, ethnicity, age or time within your organisation.
- For example, if you have lower penetration rates for females, then you may have an equity challenge with that particular gender.
Above the band
- Donât forget to identify and diagnose why some people are above the band.
Within the band
- For people who are within the band, why are some people at the top of the range and some people at the bottom?
- Do you need to move anyone who has a low range penetration (i.e. bottom of the band) to a higher range penetration based on their performance?
2. Compa-ratio
- Unlike position in band, the compensation ratio (or compa-ratio) is a formula designed to compare where an employeeâs actual salary stands relative to the midpoint, unlike the position in band which looks at the whole pay range.
- It quickly reveals how closely an employeeâs salary aligns with the midpoint of their salary band.
- Employees who have a compa-ratio less than 1.0 are paid below the market rate for their position. Employees who have a compa-ratio greater than 1.0 are paid above the market rate for their position.
- Iâm not a big fan of using compa-ratios as it focuses on just getting everyone to the mid-point. In an employees pay below the midpoint, they feel as though they are underpaid relative to the market, when that may not be the case, given their level of performance i.e. they could be new to the level and therefore being paid below the midpoint is perfectly acceptable.
- Another challenge with using compa-ratios is that even if two people have the same compa-ratio, one person could be below the middle minimum and the other person could be above the minimum if the bands they are in have different widths. Confusing!
Formula
Compa Ratio = Employees salary / Salary Range Midpoint
- A compa-ratio of 90% in the example below means the person is just within the market competitive pay range â
- Analysing employees by looking at both metrics at the same time can be very confusing as they measure pay equity in different ways. I recommend you chose one or the other.
What you get with this template
â 1 x Pay equity and salary increase calculator via GoogleSheets
Due to the GoogleFinance calculations, the spreadsheet does not work in Excel
â Â 1 x 1hr onboarding call with me
This will give us plenty of time to start importing your salary bands and get you set up.
FAQs
- If youâve never carried out a salary benchmarking project before and need help figuring out which data source to use, then feel free to book in time to chat with me using the link here. Iâll be happy to spend 30mins with you and point you in the right direction.
- Yes! Head over to this page and start building your salary bands with the template that I have there. Once youâve built your salary bands, you can start using this template.
Analysing the output
Itâs likely that you will have outliers when you first do this exercise, and that's okayâit takes time to get everyone into the salary ranges you've created.
Below are some questions to ask yourself as you look through the spreadsheet:
- Gender: What is the pay gap between genders across levels and teams?
- Ethnicity: Are employees from a particular ethnic group paid less than others?
- You can use the âDashboardâ sheet to determine how much cash is required to tackle those who are underpaid based on the criteria you set i.e. address employees who are below the minimum of the band first.
- Make sure you tackle those with the biggest deviations from the min of the band.
- If people are hugely underpaid, you could adjust their salary over time e.g. employee is ÂŁ15k underpaid. You may want to market-adjust their salary by ÂŁ10K initially and give them additional raises over the next 12 months to get them back into the right compensation band.
- Ask yourself whether an employees base salary and equity balance cause them to be under compensated? Perhaps they were offered a salary to equity sacrifice and consequently are below market on base salary but have been given more options that their peers.
- If someone is within the salary band then thatâs great, however, you may want to double check their position within it based on their performance and growth and then make adjustments accordingly.
- The descriptions below are commonly used to place someone within a band and more reading on salary bands can be found here.
- You may decide to keep people at their salary until their actual level âcatches upâ, but for some this may take a long time depending on how far out of the band they are (without getting any financial recognition), or for others it may not happen.
- Instead of dwelling on the negative, focus on what employees need to do to earn a promotion or performance-based compensation (if your company provides performance-based comp).
- âYou're actually above market rate, so if you want more, let's talk about how we can work together so you can earn a promotion into a new role with a higher pay.â
Setup videos
- Iâll help you with this during the onboarding call. It all depends on your salary bands and factors such as whether you pay Individual Contributors and Managers at the same level the same salary or not. With a few tweaks, weâll be able to import your ranges in a few mins.
Need something more bespoke?
Request a demo
All of the templates + a lot more in one place, allowing you to set your pay strategy and benchmark your organisation at scale. Check out more details here