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.
- 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.
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.
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:
- Are there any trends that you can spot based on team, level, gender, ethnicity, age, tenure or any other dimension you capture?
- Gender: What is the pay gap between genders across levels and teams?
- Ethnicity: Are employees from a particular ethnic group paid less than others?
- Tenure: Do you have any âsalary compressionâ occurring?
- This occurs when companies aren't raising the salaries of their current employees. While, at the same time (usually due to labour market conditions), employers feel forced to offer higher wages to attract new talent.
- An example of this would be a less experienced new hire being paid close to or more than a seasoned employee in a similar position. This may lead to veteran employees feeling undervalued.
- How much will it cost you to move everyone who is below the band to the minimum of the band?
- What happens if you canât give a raise right now?
- 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.
- What budgetary constraints are in place?
- Has the finance team provided you with a budget?
- Is there a budget for each team? If so, how will the person responsible for the budget use it to make adjustments?
- What part does equity play in this?
- 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.
- For people who are within the band, why are some people at the top of the range and some people at the bottom?
- Before taking any action, make sure you have a clear understanding of the compensation disparities and the reasons behind them i.e. are they are on a higher salary compared to their peers due to take few stock options for example?
- 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. This is not ideal and therefore youâll need to consider other ways to keep them motivated.
- Consider alternative rewards: If adjusting their base pay is not feasible or appropriate at the moment, explore other ways to recognise and reward their contributions. This could include additional responsibilities, a promotion, professional development opportunities, or performance-based bonuses.
- So 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