Client

A South African actuarial modelling and reporting team for a global reinsurer.

Challenge

  • The actuarial and reporting teams needed to transform the company’s
    policy administration data into the format required required by the model
  • Extracts from each client were received in different formats and required a bespoke transformation process.
  • Transformations were programmed using legacy tools. The process had several manual steps, was slow to run and was very inflexible, creating a high level of operational risk.
  • Significant licence costs associated with the legacy tools that were being
    used.

Approach

Discovery

  • MBE conducted discovery workshops with the client’s SMEs based on the results from MBE’s Actuarial Performance Management (APM™) Assessment.

Analysis

  • Performed root cause analysis on the data management processes.
  • Analysed the end-to-end model point creation process including manual steps, legacy data tool transformation logic and data adjustments/corrections.
  • Assessed the infrastructure and tools available in the organisation including SQL Server and Excel.

Recommend

  • Recommended, developed and successfully implemented using Excel Power Query the replacement of the model point creation process for two portfolios as a pilot.

Results

  • Achieved a 70% reduction in data transformation run time compared to legacy tools.
  • Removed the manual steps through Excel Power Query directly linking to the SQL Server data warehouse to source the data.
  • Largest portfolio with more than a million records able to be processed without the need to break up into smaller files.
  • Client plans to migrate all portfolios to the new solution.

Testimonial

“Through MBE’s innovative use of Excel Power Query, we have achieved a dynamic solution to replace our legacy tools. The developed solution is well structured, transparent to use, and easy to maintain. It’s been a pleasure working with the MBE team.”

Actuarial Manager