When a health client started the migration of their complex payments systems, they began to see discrepancies of £6m a month. Our actuarial team used expert data analysis and calculations to identify and rectify this urgent problem, saving the client millions of pounds.
Digital transformation is a key priority for many businesses but moving away from legacy systems can be a complex process and involves a vast programme of work. These types of programmes aren’t the natural home for actuarial teams, but when they involve large amounts of money and complex calculations actuaries can add a huge amount of value.
In 2019 a health client had embarked on phase 2 of a major digital transformation programme and were beginning to migrate their complex payments system. One part of the system generated over £400m in payments to customers per month. However, the client soon began to see huge discrepancies of around £6m a month, which was clearly unsustainable and an issue that needed to be rectified as soon as possible. That’s when they called on our actuarial team for help.
Uncovering the reason for the discrepancy involved processing large volumes of data with complex and convoluted calculations and a huge number of rules and dependencies. The programme’s payments team were under a good deal of pressure and the support of our actuarial experts gave them a much-needed confidence boost.
Whilst the migration of payment systems are not traditional actuarial projects, our people are very comfortable with complex calculations and are able to bring a level of forensic analysis that can uncover even the most deeply hidden issue.
Using forensic actuarial analysis to solve the problem
When we started to look at the data in the legacy and new payment systems, it was immediately clear that it wasn’t consistent. The data was structured and formatted differently and had varying constraints and precision levels. To solve the problem, we needed to drill down into both systems with a forensic level of analysis. We soon discovered there wasn’t a dedicated calculation specification and that the functional specification that did cover the calculations within the new system was ambiguous and open to interpretation.
To resolve the issue, we produced a new specification document which meticulously detailed the calculation requirements to the high standards we’d expect in the actuarial world, which left no room for ambiguity. To ensure this was done properly, we held extensive discussions with the solution architects for the new system, the development and data migration team and the third party administering the legacy system.
Developing testing tools to help identify calculation issues
To enable us to identify errors, we developed a Microsoft Access and Excel based test tool which reflected the way the calculation worked. This meant we could interrogate the data more easily to identify issues within the systems. We used the tool to reconcile the legacy system first and every time a discrepancy was found we could independently check where the deviation originated from.
Through this exercise we further refined the specified calculation and identified issues with how key standing data items were being constructed and treated, not only in the new system, but also within the legacy system that had been in place for 17 years.
Once these were corrected, we were able to fully reconcile the payments and began the process of identifying all issues with the calculation engine in the new system. This involved very detailed and comprehensive analysis of how data flowed and was used within the two systems. This meant we could pinpoint the source of issues and take immediate action to rectify them.
To ensure that our calculation engine was built efficiently and was making all the right calculations, we tested every component that needed to be validated. In total we tested over 500 different scenarios against the calculation. Through this process we were able to resolve all issues.
Enabling a stable financial future
The new system is now able to replicate payments more precisely. This has reduced the discrepancies between it and the legacy system from around £6m to under £5 per month. This small residual difference, comfortably below the client’s tolerance level, is entirely down to different rounding precisions between the two systems.
In addition to providing expert mathematical skills, our team has helped to future-proof the calculations against certain basic changes that may arise in future. We used our experience to predict and advise on the effects of differing inputs that may affect the reconciliation, and we produced detailed project documentation to help the client when future amends on the system are required.
We’re pleased to say that our work hasn’t stopped there. The client was impressed with the speed and agility of our team, and we are now continuing to support them to provide quality assurance on system payments. That way they can focus on giving their customers the best possible service whilst being confident the payments they receive will always be correct.