Project Parity

Development of a gender pay gap reporting model for an accounting firm.

The model was based on importing a snapshot of pay information for all staff, as well as their recorded gender and would calculate the required statistics. It was built in a scalable way so that it would automatically extend the calculation area when more staff were added, for example when the model was re-used in later periods.

In order to calculate one of the required statistics each person has to be assigned to a quartile based on their rate of pay, so that the gender composition of each quartile could be reported. In cases where multiple people are on the same rate of pay an approach was required to place them into an order to them without bias, to enable allocation to quartiles. (i.e. not to leave them in their original order as that could be based on staff ID number and hence reflect length of service, for example. That would result in bias if the quartile cut off fell within a group of people on the exact same pay as the original order would mean that longer serving employees were ordered later and therefore be more likely to be placed one side of the cut-off than the other, skewing the statistics. The staff need to be ordered randomly when on the same pay rate, so that if the quartile cut-off falls within a group of same pay then the gender split would be fair each side of the cut off).

The solution was to add a very small pseudo random number to each rate of pay (a fraction of a fraction of a penny) to allow staff to be sorted into an unbiased ordering. The use of a deterministic 'pseudo random' number was used instead of a RAND() function as otherwise the ordering, and consequently the statistics, would change by small amounts each time the file recalculated. (i.e. a male and female could swap positions if they were on same pay and move either side of a quartile cut off, thereby changing the statistics if the small random amount added were to change.) This was an interesting project with some unexpected nuances to solve.