Executive Summary
Providence Technology Solutions helped a company recover $900,000 that employees owed for missed benefits deductions. Providence identified the missed deductions through an employee benefits audit and reconciliation of four months of payroll deductions and direct bill premium payments for leave-of-absence employees. The audit included data from the client’s core HR/payroll system as well as its benefits and direct-bill administration systems.
Client
A full-service landscaping services company with over 9,000 employees.
Challenges and Objectives
A landscaping services company hired Providence to do an employee benefits audit and reconcile payroll deductions and direct-bill premium payments for leave-of-absence employees.
The company gave Providence the following objectives.
- Reconcile three sources of data to determine whether associates owed premium amounts or were due a refund.
- Validate coverage levels based on coverage elections and eligibility dates.
- Confirm that the direct billing and payroll deduction timing worked correctly for employees on leave.
Providence had to reconcile four months of data (Jan. 1, 2017 to April 30, 2017) from three sources.
- Core HR/Payroll system
- Benefits administration system
- Direct-bill administration system
Data gaps and inconsistencies between the information sources created challenges for Providence. For example, pre-defined benefits reports provided limited information based on the date that data was pulled.
Providence encountered the following challenges as well.
- The benefits system did not report canceled as a result of election or termination. It showed active coverage for terminated employees.
- The benefits system also reported pay-frequency rates and monthly rates for leave-of-absence.
- The Leave-of-Absence file included more than 1,800 employees. It also contained multiple duplicate or concurrent leave of absences, which needed to be consolidated.
- The company’s demographic and employee files failed to report employees with taken deductions and/or active enrollment.
- Five employees had coverage and premiums without any enrollment reported. Sixty employees had semi-monthly and weekly deductions for benefit payments.
How Providence helped
Providence Technology Solutions used its HCM Data Audit Xpert technology to:
- Identify gaps, inaccuracies, and differences between the client’s benefits and HR-related systems;
- Reconcile data from three systems; and
- Validate employee data, benefits elections, and deductions.
Process
Providence followed a three-step process.
Step 1 - Data Scrub
- Extracted active coverage and enrollment only. Removed all others.
- Applied logic to combine ‘no gap’ coverages.
- Extracted benefit deductions from file only. Removed all others.
- Combined all weekly payroll registers
- Extracted coverage where the plan name began with “DB.”
Step 2 - System and Deduction Mapping
Step 3 - Database Creation
Analysis and Logic
Providence analyzed data and applied logic to attain the information that the client wanted.
- Data scrubbed all files and reduced information to only the data relevant to the analysis. For example, the benefits file was reduced to only those benefits that were active during the scope of the project and payroll registers were reduced to only those deductions relevant to benefits.
- Reviewed and identified data for inconsistencies and data gaps.
- Incorporated all files into a database, where adding logic to calculate and analyze data was more efficient and could be applied to large sets of employees. Applied additional logic for exceptions.
- Built a template to serve as the employee view for the analysis.
- Created the process steps of the analysis and translated into logic in the database.
- Applied the logic and pulled employee reports to validate results.
- Validated the results and made adjustments for exceptions where necessary.
Final Report/Template
The final report gave the client a detailed account of all deductions and expected premiums for employees from Jan. 1, 2017 to April 30, 2017. The report included the following information and pages.
- Report Page – A summary of an employee’s demographic data, leave details, payrolls and payments, and expected premium.
- Audit Analysis – A breakdown of an employee’s monthly expected-premium and collected-premium from payroll and benefits information by pay period and month.
- Overview – Data and assumptions used to extract the Audit Analysis.
- Coverage – Reported where coverage was active or was active during the scope of the project based on the benefits file.
- Weekly/Semi-Monthly – Compiled from the payroll register, this report showed all deductions from an employee’s check.
- Source Data Files Used – Employee data was reported if there was an applied amount for the active benefits in the benefits file.
- Active-As-Of Dates – Employee data was reported only if an employee was included in the client’s Active As Of 1-1-2017 file.
- Leave of Absence – All of the leaves that the client reported for an employee.
- Audit Analysis Recap – Audit results validating if the employee passed/failed the Direct Bill/Payroll Audit. Employees who failed the Direct Bill Audit did not contribute to premiums when expected by the audit. Employees that failed the Payroll Audit, did not contribute to payroll when expected by the audit.
Results
Providence found that 5,654 of the 9,218 employees included in the landscaping company’s audit were either owed a refund by the client or had a balance that they owed to the client. So, all told, 61.3% of employees failed the audit because there was a difference of $1.00 or more between the expected deductions for them and the actual amount collected.
- The client owed a total of $86,592 in refunds to 913 employees.
- A total of 4,741 employees owed $902,270 in missed deductions to the client.
- The net result was that employees owed the client $815,670.
The landscaping company could update benefits deductions to correct for deficits based on the results of the audit by Providence.
Learn how you can improve data quality and integrity with data audit services from Providence Technology Solutions.
Contact us today to discuss your HR data management needs online, by email at info@theptsteam.com, or by calling 904.719.8264.