Health & Welfare Data Validation and Cleanup

A large energy company’s data for active and retiree health and welfare benefits resided in several software systems and was utilized by multiple vendors. It represented close to 40,000 active employees and retirees across nearly a dozen entities and more than 20 union and non-bargained groups.

The systems didn't sync up and the data often didn't match. For example, a correction made in one system wasn’t always carried through to another, union group classifications sometimes lagged behind actual transfers, and life insurance amounts for retirees were periodically still tied to active salary figures. If these errors reached the actuary, they could produce incorrect liability calculations, affecting the company’s balance sheet. When the internal team member who previously managed the reconciliation work became unavailable, SBA stepped in to help.

To manage the scale and consistency of the work, SBA built a custom automated framework to compile the data and perform year-over-year comparisons, refining it over the course of the engagement to reduce manual steps with each annual cycle. SBA pulled participant records across both active and retiree populations, including census reports, termination and death records, life insurance files, and the base demographic file used for the valuation. They checked actives against a broad set of demographic and coverage elements and retirees against benefit eligibility considerations tied to how and when they left active status.

The complexity of the population also made the comparison more challenging, with disparate systems that were not updated consistently to reflect when participants changed subsidiaries and/or union memberships. Finally, SBA tracked discrepancies across prior cycles, using recurring patterns to refine the annual checks so that known issues were built into the validation process.