Client

The Actuarial Modelling and Reporting team of a global reinsurer engaged MBE Consulting to develop and implement a new system for the model point creation process for all treaties in the business.

Challenge

  • The team needed to transform the Policy Administration System (PAS) data into the format required for their internal model.
  • Once the process had been successfully piloted for the migration of two treaties from the legacy tools to Excel Power Query, the solution needed to be productionised.
  • The Treaties sourced data from multiple sources, including the client’s SQL Server Enterprise Data Warehouse (EDW).

Approach

Discovery

MBE Consulting initiated the project by conducting an inventory assessment to compare the state of the PAS Data extract files with the data available in the EDW. This provided a clear understanding of the different data sources and their current condition.

Key Outcomes

Our team performed a thorough review of the client’s existing treaties processing model and successfully implemented the following deliverables:

  • Conducted a detailed analysis of the data quality and difference of the PAS data extracts versus the EDW data.
  • Prototyped the ability to dynamically switch between files and the SQL Server as source data.
  • Assessed and optimised the end-to-end treaty model point creation process, including the manual steps, legacy data transformations and data adjustments/corrections.
  • Assessed the non-functional requirements needed for the production process.

Recommendation and Implementation 

Based on the success of the initial pilot we developed and implemented a revised model point creation process for all treaties using Excel Power Query.

Results

  • The new process achieved a 70% reduction in data transformation run time compared to the legacy tools.
  • The largest portfolio with more than a million records can now be processed without being split into smaller files.
  • The time-consuming manual steps were removed using Excel Power Query logic, including dynamic switching between sources (files or the EDW).
  • When required, end users can easily follow the query logic and make data transformation changes without IT or developer input thanks to MBE’s clear design and self-documented Power Query steps.
  • The client successfully completed two parallel runs, comparing the legacy tools with the new tools in their production environment, which resulted in the client’s sign-off for the seamless transition to the new process.

Testimonial

“Throughout the project, the MBE team attentively listened to our requirements and maintained a high level of professionalism. We are extremely pleased with the results and the efficient, streamlined approach MBE brought to our operations.”

Actuarial Manager