Client background

The client is a leading manufacturer of semi-trailers, truck bodies and related products. The company has innovative design, manufacturing and supply chain management ideas, which has allowed them to produce a wide range of products that meet the niche needs of the freight and logistics industry.

The business challenge

The client was encountering challenges in programmatically generating fields for a regular rate of pay calculation involving the integration of eligible hours and pay (including overtime) and the application of specific coefficients for determining employee payments owed. They engaged Baker Tilly to conduct a thorough data analysis, develop calculated fields to accurately automate and streamline these financial computations and provide quality assurance guardrails that meet the highest standards of precision and reliability.

Strategy and solution

The client supplied Baker Tilly with time entry data from the time tracking system of record as well as biweekly payroll data. Our team organized the data into three distinct folders corresponding to their respective time entry systems, along with two additional folders dedicated to payroll data. Using SageMaker, we efficiently analyzed the data and converted these files into parquet format to optimize them for storage efficiency and query performance.

Subsequently, the team integrated all parquet files using AWS Lake Formation to help implement security and data governance rules and AWS Athena environment to create a relational database used for the calculations.

The team crafted the new regular rate of pay calculations by creating a comprehensive table amalgamating all personnel information, hours metrics/calculations and payout amounts. To uphold the highest data integrity and accuracy standards, we conducted thorough quality assurance checks. This critical step involved a detailed review of the data to identify and rectify any discrepancies or anomalies, ensuring the reliability of the final output. The culmination of our efforts was the submission of refined payout amounts for all eligible employees to ensure the integrity of the payroll process.