Automated Reconciliation Tool for End User Billing: A Case Study

The customer in this case study is a business process outsource company that provides a variety of products and services, including an End User Billing platform for insurance companies. The platform interfaces with many internal and external data sources, primarily focusing on coverage history and Cash Distribution. The system serves multiple client organizations, each with their own custom business rules, state-specific and federal rules, and variances due to implementation/on-boarding processes. The company is constantly challenged with discrepancies in the billing system due to the complexity of data sources, business rules, and manual interventions.

The case study revolves around a business process outsource company that provides an End User Billing platform for insurance companies. The platform interfaces with numerous internal and external data sources, primarily focusing on coverage history and Cash Distribution. The system serves multiple client organizations, each with their own custom business rules, state-specific and federal rules, and variances due to implementation/on-boarding processes. The complexity of data sources, business rules, and manual interventions often leads to discrepancies. The rapid change in data sources and rules/regulations from state and federal regulators further increases risk in new development/enhancements in software. The Billing system generates billing from coverage history, i.e., the record of what coverage you have and the rates and other attributes. Issues arise in the regular updating of coverage history due to late arriving rate changes and other things that affect the elements that drive billing. Further adjustments after the initial bill can be done in error or done incorrectly. Billing operations is regularly challenged to react to discrepancies that are found between coverage history and Cash distribution data.

The company developed the 'Billing Automated Reconciliation Tool' (BART) using Alteryx and collaborating with Billing SMEs for requirements and clarifications. The solution was built in a couple of months working part-time with one resource. The solution has been refined in iterative development sprints and now provides regular and on-demand reporting that identifies nearly a hundred issues comprised of specific conditions and rules for each, and provides recommendation for corrective actions. Some issues are packaged and fed into an interface for automated correction while others are used to expedite the manual process to verify and approve actions. The solution extracts data primarily from IDAA appliance using SQL. It blends data from the cash/billing tables and the coverage history tables, then cleanses and blends the data from both streams and then joins and reconciles the streams and produces reporting from a detail bill period level to high level summaries. The solution was designed to be configurable by client and run instance. An .xlsx was designed to provide a means for Billing Ops to enter/modify what client they want to report on, what edits they wanted to exercise, time frames, and other considerations.

• The implementation of the 'Billing Automated Reconciliation Tool' (BART) transformed the company's approach from reactive and anecdotal to proactive and comprehensive. The tool enabled the company to understand the magnitude of issues, quickly identify cases with issues and their specific bill periods problems and remedies. Some corrective issues were automated, and others accelerated and facilitated to allow large numbers of corrections. The company went from purely reactive, manual and time-consuming research, verification and action to largely automated processes enabling them to do a volume of work previously not possible. The tool also allowed Billing Ops to run the system on-demand with various configurations and for selected cases they may want to focus on in addition to the regularly scheduled production runs for an entire client’s portfolio using the standard configuration of edits and periods they defined.

• The solution was built in a couple of months working part-time with one resource, compared to the IT estimate of 6 months involving multiple resources.
• The solution identifies nearly a hundred issues comprised of specific conditions and rules for each.
• The solution saved ~60% of the total run time when it was all in one flow.