Potens

Case Study -

The Issue

My client needed a reliable and accurate way to calculate additional annual leave payments for salaried employees, ensuring compliance with statutory requirements. The calculations needed to account for average earnings over the previous 12 months, with additional payments limited to a maximum of four weeks’ annual leave per employee, per tax year.

The process was particularly complex because the information was spread across seven separate datasets, including payroll, contracted hours, pay rates, holidays and employee absences. Numerous variables also needed to be considered, including employee start dates, changes in contracted hours, statutory sick and maternity leave, and employees exempt from the calculations. The client required a solution that could be updated monthly, whilst maintaining a reliable historical record for payroll queries and auditing purposes.

Of course, as this was related to Employee Pay, accuracy was of paramount importance.

The Solution

Excel Ace developed a bespoke Excel solution that brings together the various datasets and automates the complex calculations.

Using employee numbers as unique identifiers, the solution calculates qualifying earnings over a rolling 12-month period, adjusts for relevant absences and employment dates, and determines each employee’s average hourly rate. This is then compared with their actual hourly rate to calculate any additional holiday pay due.

The solution automatically applies employee exclusions, identifies qualifying annual leave hours and ensures additional payments do not exceed the four-week annual limit. A rolling summary provides a comprehensive historical record of calculations and payments, enabling the client to quickly investigate queries, verify previous payments and maintain the process simply by updating the source data each month.

The result is a streamlined, repeatable payroll process that replaces complex manual calculations, reduces the risk of errors and provides a clear, auditable record of additional annual leave payments.

Due to the complexity and up to 1500 people a month you are looking at around a day saved per month

How it was built

Power Query
VBA
Pivot Tables
Data Validation

Formulas: IF, OR, VLOOKUP, MAX

What Nikki Said

We had a complex requirement involving multiple payroll datasets and numerous employee-specific variables, making additional holiday pay calculations both time-consuming and difficult to manage.

Excel Ace took the time to fully understand our requirements and delivered a solution that is practical, reliable, and easy to maintain.

The automation has significantly reduced manual effort, improved accuracy and consistency, and provided a clear audit trail for payroll queries and compliance purposes. The solution has streamlined a complex process and gives us confidence that additional annual leave payments are calculated correctly and consistently every month.