Pay equity and salary increase calculator
🔢

Pay equity and salary increase calculator

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.

image

How this template works

The spreadsheet consists of ‘Input’ and ‘Output’ sheets.

Input sheets

  1. Market data
  2. 🔢 This is where you copy and paste in your organisations salary bands (salary benchmarks).

    🚨
    If you don’t have salary bands in place, then check out the Salary Bands Template here.
  3. Setup
  4. 🔢 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

  1. Employees
  2. ✅ 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).

    image

    ✅ 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.

    image

  3. Dashboard
  4. ✅ 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:

    • Gender
    • Team/dept.
    • Level
    • Tenure

    ✅ 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.

    image

    👉
    I’ve kept this section lightweight, as I’ve found most organisations like to build their own dashboards. If you’d like help analysing the output and building additional graphs, then simply use the link here to book in time for customisation work.

  5. Calculator
  6. ✅ A calculator to help you quickly view your salary bands based on a location, job family and employment type.

image
image
  1. Benchmarks Matrix
  2. ✅ A quick snap shot of all your market data for all locations, job families and levels in one place.

    image

Analysing pay equity in your organisation

The spreadsheet looks at pay equity in two common ways:

  1. ‘Position in band’ aka ‘Salary range penetration’
  2. ‘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.

image

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?

image

‣

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.
  • image

  • 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 ✅
  • image

🚨
You shouldn’t look at both metrics at the same time
  • 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

‣
What if I don’t have any market data?
  • 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.

‣
I need help building my salary bands. Can you help?
  • 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

‣
What should I do if someone is under paid 🚩

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?
‣
How much will it cost you to move everyone who is below the band to the minimum of the band?
  • 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.
‣
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?
‣
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.
‣
What should I do if someone is within the band 👌
  • 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.
‣
What should I do if someone over paid 👀
  • 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

‣
How do I import my salary bands?
  • 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.
‣
How do I import my employee data?

‣
How do I modify the employment types and their multipliers?

‣
How do I round the salary benchmarks?

‣
How do I modify the currencies and currency conversion rates?

‣
How do I modify the payment frequencies?

‣
How do I add different genders or ethnicities?

Need something more bespoke?

🧮
Compensation Calculator + Philosophy

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

This website was built without code using Notion + Super ⚡️ by Alistair

🤿
Templates