Rebuilding multi year employee entitlement payments with Alteryx

Navigating a sea of legislated employee entitlements

When it comes to ensuring the workforce is enabled with leave that aligns to the unpredictability of human life, the New Zealand Government is amongst the most progressive. The Kiwi government even takes it up a notch by determining that leave types should be paid at an equitable rate, not disadvantaging any type of employee or employment.

With a workforce close to 3 million as of 2020, there is a lot of data and scenarios to track. Employers have a legal duty to ensure employees are properly compensated when one of the many leave scenarios presents itself. Since 2003 when legislation providing New Zealand employees with leave benefits took effect, payment miscalculations, payroll code inconsistencies and human error have been ever-present across the nation’s employers.

When Grant Thornton, a global business advisory, tax & audit firm, uncovered a need in the market for payroll assurance services, Elisha Nuttall, Sr Manager, Consulting, knew Alteryx was the right platform.

Scaling an analytics application

At the start of its payroll recalculation journey with Alteryx, the Grant Thornton team built a model for one of New Zealand’s largest cleaning companies, an exercise covering 21,000 employees and 6 billion rows of timesheet data. The entitlement definition in the Holidays Act makes for challenging architecture and model performance, constraining them to process only one week at a time for each employee over their employment period. The first iteration of the three bespoke models took 3.5 days to accurately process calculations.

Nuttall, along with Founder of Mission Intelligence, James Martin, then set out to build a groundbreaking Alteryx Analytic App that was capable of navigating the constantly changing leave entitlement legislative landscape. “The core driver for building this Alteryx Analytic App was to avoid rebuilding the calculation logic from scratch. Historically with each new client it has been an effort-intensive exercise that creates inefficiencies and may make the price prohibitive for a large part of the sector that needs this help,” says Martin.

The Analytic App envisaged had significantly more functionality and flexibility than earlier bespoke models, so performance posed a real risk and potential handbrake. Martin and Nuttall went back to the drawing board with the design so the application could be deployed at scale, to help organizations large and small. The innovative App architecture can now recalculate a single employee in seconds, or complete large recalculations of tens of thousands.

Tackling the challenge holistically

To put the challenge into perspective, every company in New Zealand must be compliant with the Holidays Act. Compliance is enforced by Employment New Zealand, the Kiwi Government’s compliance agency. Organizations typically have to work out how much they’ve under-paid staff and make back-payments. Nuttall estimates that only 10-20% of organizations are currently compliant. With traditional toolsets like Excel and SQL there simply aren’t enough skilled practitioners to help.

The many leave scenarios leave most organizations with a level of non-compliance forcing a remediation process. Prior to Alteryx, the typical approach was to manually re-calculate a sample of payments and extrapolate an estimate in Excel. Accuracy and payment miscalculations were an evergreen issue.

Martin and Nuttall began by building 42 different leave scenarios into the app model with 12 different configuration options. This allowed for flexibility and agility to meet the many types of employees’ working patterns. The model was built with two additional sublayers, one layer being client policy parameters which tell the application how to select between different scenarios, and the first layer being user configuration for customizability.

Supporting clients with an end-to-end service

The main goals for Martin and Nuttall was to build an application which was scalable, agile, and affordable for all organizations. After seeing one client spend north of $500k on a SQL-based model and over $100k to validate the model via an additional consultancy, the payroll re-calculation application built with Alteryx became one-of-a-kind in the market. The impact of the recalculation application model is best understood by looking at the cost of getting leave payments wrong:

• Remediation fees can be north of $1.5m
• Consultancy and lawyer fees are to be expected
• Underpayments can be as high as 1% of wages paid in each of the 6 years that re-calculation needs to be considered
• The cost involved in end-to-end remediation can exceed the total value of underpayments required to be paid

“We started in the most complicated area in this remediation process, an area where very few competitors operate, which is the 6+ year re-calculation of correct holiday pay to employees. From this we have expanded to be an end-to-end service to support clients in their remediation journey, saving them hundreds of thousands of dollars.” Nuttall concludes.

Building on success of the first version of the Alteryx application, Grant Thornton and Mission Intelligence have developed an enhanced second version covering all aspects of employee entitlement legislation in New Zealand. This App allows the user to choose between five areas of compliance to recalculate and has a total of 38 configuration options. The second version has expanded leave scenarios from 42 to nearly 150 possible scenarios – covering every type of employee in New Zealand.