Client

The Actuarial team of a global reinsurer engaged MBE Consulting to review and improve their capital model point data process, moving away from a costly legacy system.

Challenge

  • The actuarial team needed to transform and aggregate the valuation model point data for their capital model runs.
  • The model point file for each treaty was too large to work with in Excel and was being transformed and aggregated using legacy tools.
  • The mappings and aggregation groups required code changes, but only some team members had experience of coding in the legacy tools.
  • The legacy tools had very high ongoing license costs.

Approach

Discovery

Our team identified the set of treaties and model point data that needed to be reengineered to achieve significant efficiencies in the client’s capital model point data process.

Key Outcomes

MBE performed a thorough review of the process and successfully implemented the following actions:

  • Performed a root cause analysis on the existing data management processes.
  • Analysed the end-to-end capital model point creation process including the manual steps, the legacy data tool transformation logic and the aggregation logic.
  • Prototyped dynamic mapping and aggregation mechanisms in Excel Power Query to avoid the need for users to make code changes.

Recommendation and Implementation 

  • Following the success of the first stage of the project, the MBE team recommended, developed and successfully implemented the use of Excel Power Query as the replacement tool for the capital model point creation process.

Results

  • Removed the need for code changes when actuaries were required to make mapping and aggregation grouping changes.
  • Processed the largest treaty with more than a million model points without the need to split it into smaller files.
  • Completed a production ready implementation in less than two months which the client successfully used in parallel runs.
  • Removed the ongoing license costs for the legacy tools.

Testimonial

“MBE Consulting was instrumental in streamlining our capital model point process and reducing our reliance on outdated, costly legacy systems, providing a far more efficient, cost effective and flexible solution.”

Actuarial Modelling Lead