Calculate the Gender Pay Gap in your organisation using my simple spreadsheet
Intro
The Gender Pay Gap is a pressing social issue, defined as the noticeable disparity in remuneration between male and female workers. This discrepancy is typically determined by comparing the average or median salaries of these groups.
It's a complex problem that has persisted, representing a form of inequality. It's not simply about men and women being paid differently for doing the same job, but rather the overall systemic factors that contribute to women, on average, earning less than men in the workforce.
Reporting requirements in the UK
In the UK, the requirement to report on the gender pay gap applies to private, voluntary, and public sector organisations that have 250 or more employees. That being said, there is a growing trend for companies that do not meet the 250 employee threshold to report their GPG to demonstrate their transparency on this topic.
The calculations required by the UK government are as follows:
- mean (average) gender pay gap in hourly pay
- median gender pay gap in hourly pay
- mean bonus gender pay gap
- median bonus gender pay gap
- proportion of males and females receiving a bonus payment
- proportion of males and females in each hourly pay quartile
You can view the list of calculations in more detail on here on the Gov.uk - Statutory guidance - Making your calculations.
You may also need to submit or publish:
- a written statement
- a supporting narrative
- an action plan
You can find more details on this here on Gov.uk - Statutory guidance Overview
Reporting dates
You are required to publish your organisation's gender pay gap report within one year of the relevant snapshot date. The snapshot dates are as follows:
- 5 April for businesses and charities
- 31 March for public sector organisations
The gender pay gap data must be submitted to the government using the following online service: Gov.uk - Report Gender Pay Gap Data. This data should be made publicly available on your organisation’s website.
How the calculator works
By inputting your organisation's salary details into the spreadsheet aka ‘calculator’, it will calculate all the outputs required by the UK Government. You can then use this information to identify and address any gender-based pay discrepancies.
1. Enter your employees data into the sheet called ‘Employees’.
Please refer to Gov.uk - Preparing your data for guidance on which employees to include in the report.
When modifying the spreadsheet, make sure to edit the blue cells only.
If you insert new rows, make sure to copy the formula down in the columns with the grey headers.
2. View your gender pay gap
Click on the sheet called ‘Dashboard’ to view the ‘Average’ and ‘Median’ pay gaps across your whole organisation.
You can view data for both annual salaries and hourly pay.
Calculating both the mean and median Gender Pay Gap values is important as a single high value can skew the average. For instance, if your CEO is a woman earning a high salary, this could significantly inflate the average pay for females.
3. View the ratio of males and females in each pay quartile
Part of the reporting requirements is to determine the percentage of men and women in each hourly pay quartile.
In this sheet, the calculator divides the list of both annual and hourly wages from the employee sheet into four equal quartiles.
- If this is the case, distribute them as follows:
- If there is one employee left over, add them to the lower hourly pay quarter.
- If there are two employees left over, add one to the lower hourly pay quarter and one to the upper middle hourly pay quarter.
- If there are three left over, distribute them between the lower, lower middle, and upper middle pay quarters.
- There may be employees who:
- earn the same hourly pay
- land in different pay quartiles due to the position of the 'dividing line'
- In such cases, strive to distribute men and women as equitably as possible.
Acme Ltd has 4,445 full-pay relevant employees. They have sorted them from highest to lowest hourly pay, and divided the list into 4 quarters.
40 employees have the same hourly pay. Of these employees:
- 36 are women and 4 are men
- 10 have fallen into the lower quarter
- 30 have fallen into the lower middle quarter
To distribute these employees by gender, Acme Ltd should make sure that:
- 9 women and 1 man make up the 10 employees in the lower quarter
- 27 women and 3 men make up the 30 employees in the lower middle quarter
Interpreting the data
➕ Positive Gender Pay Gap
A positive value indicates that your male staff are paid more (on average or median basis) compared to the female staff.
0% Gender Pay Gap
A zero percentage shows that there is equal pay between men and women in your organisation.
➖ Negative Gender Pay Gap
A negative value indicates that your female staff are paid more (on average or median basis) compared to the male staff.
Limitations with gender pay gap reporting
The Gender Pay Gap (GPG) provides valuable insights into the discrepancies in pay between men and women. However, to fully understand and address these statistics, we must acknowledge their limitations.
A gender pay gap within a company does not necessarily indicate discrimination.
- Lack of hierarchical insight The GPG does not show if there is an imbalance in the positions that men and women hold within a company. For example, if there are more women in lower-level roles and more men in senior roles, the GPG might not reflect the true extent of the issue.
It's helpful to look at the distribution of male and female employees across different salary ranges and seniority levels for clearer insights.
- Dig into the data beyond the top level Having a low or 0% GPG is great, however it isn't enough to ensure fairness. It's important to examine the GPG in more detail:
- By department: Check if some areas have bigger gaps than others.
- By location: Compare the GPG across different areas to spot location-based differences.
- By role: Look at differences between managerial positions and other roles.
- By tenure: Compare the pay gap between newer employees and those who have been with the company longer.
- Shares/equity: What part does equity play in this equation? For example some people may take more equity and less base pay which could skew the results.
- Relevance to small organisations For smaller organisations, especially those with fewer than 30 employees, GPG calculations might not be very informative or can be misleading due to the small number of staff. In such instances, the results can be skewed by just a few unusual cases.
By being aware of these limitations, organisations and policymakers can better design their strategies to effectively address pay disparities between genders and explain any possible imbalances. This means not just aiming to reduce the GPG superficially but taking a deeper, more thoughtful approach that considers all the factors contributing to the issue.
FAQs & Disclaimer
Note that the hourly rate can be calculated in various ways. Below is my method, but you may wish to modify the formula in the sheet to suit your needs. Alternatively, you can simply copy and paste the hourly rate from your payroll or HRIS system if it's provided there.
Step 1: Determine the total number of hours worked by multiplying the hours per week by the number of weeks in a year (52).
Step 2: divide this number from the annual salary. For example, if an employee has a salary of £40,000 and works 40 hours per week, the hourly rate is (£40,000/(40 x 52) = £19.23.
Justly has taken steps to ensure the accuracy of our spreadsheet's analytical calculations, yet cannot guarantee error-free results.
The functionality of our GPG Calculator and the reliability of the information in the Dataset hinge on the quality of Employee Data input by users. Therefore, Justly does not provide any warranties, expressed or implied, regarding the accuracy or completeness of the spreadsheet's outputs. Users bear full responsibility for meeting gender pay reporting obligations and should independently verify the accuracy of all generated reports.
Whenever you're ready, there are two ways I can help you
If you’ve downloaded a template and would like help customising it, then you can book in a 30min session with me as a jumpstart to using it.
30mins = £90
This website was built without code using Notion + Super ⚡️ by Alistair
Templates