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.

  • 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.
image
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?
    • ‣
      Read more on this
      • 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.

‣
What should I do if someone is 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?
‣
What should I do if someone over paid? 👀
  • 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

‣
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