IDS Case Study: Data Clean Up Project

Data analytics project provides significant tax credit for Virginia employer

Challenge and Opportunity

A leading logistics company, with a sizable workforce of more than 800 employees across 16 different locations, turned to us for assistance in claiming the Employee Retention Credit (ERC) under the CARES Act. The company was aware of the significant credit potential and sought to verify their eligibility as a qualifying employer, which hinged on proving their average full-time employee count was 500 or less.

Our Approach

A successful collaboration between data scientists and corporate tax specialists

To navigate the complexities of the project, the Keiter Technologies team collaborated with Keiter’s corporate tax specialists. The result was a methodical and comprehensive project approach that leveraged both team’s specialty areas—data analytics, payroll data, and corporate tax law.

The task was data intensive. Employee hours were recorded using various timekeeping applications, or manually via a punch card system, and stored across 20 diverse data sources. While some data was clear-cut and required minimal analytics, others, particularly the manually recorded sets, had inconsistencies and errors that needed rectification.

Our dedicated team of data analysts meticulously consolidated and cleaned the data, ensuring its integrity and reliability. We transformed the recorded hours from the timekeeping application into a decimal value, addressed the time zone issues, and even managed the complexities surrounding commissioned employees. We also addressed inconsistencies in employee names, some of which were similar across sources, making it challenging to identify unique employees. Furthermore, we corrected erroneous dates, particularly manually entered payroll data from some locations. Lastly, we cross-referenced the data from all sources that enabled us to track employees who worked at multiple locations or transferred locations during the calendar year.

Once the data was thoroughly validated, we subjected it to a comprehensive set of analytics. One challenge we encountered once the data was validated was calculating the monthly hours for weeks straddling two months. We developed an algorithm where weekly hours that straddle two consecutive months were allocated to the month where most of the days occurred. This meticulous process allowed us to calculate the average weekly hours per month and accurately determine the number of full-time employees for each calendar month of 2019. The process and logic behind the algorithm were well documented to support the companies record retention policies. The resulting data output provided a reliable basis for the calculations, and ensured the results were accurate, repeatable and verifiable.

Results

Our careful and in-depth examination of the data revealed that the number of employees for the measurement period was 500 or less, and confirmed the company was a small employer that is eligible for the ERC.

Our thoughtful project approach, detailed data analytics, and deep understanding of tax laws enabled our team to successfully guide the client through this complex process. The result of the intricate and intensive project culminated in the confirmation of the company’s eligibility for a more than $9M refundable payroll tax credit.

The Keiter Technologies team has access to in-house resources beyond data analytics which provide insights and expertise in areas such as corporate tax, compliance, and business advisory services. Our proficiency across multiple disciplines, enables us to offer clients comprehensive data solution services. Our processes save valuable time and resources and help maximize benefits under programs like the ERC. In working with the Keiter Technologies team, your business will work a team of professionals that will partner with you to help you achieve your business goals and add value.

View All IDS Case Studies >

Learn More about our Innovative Data Solutions Services