Through MBE’s innovative use of Power Query for Excel, 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 on this project.“
Actuarial Manager
Client
The South African actuarial modelling and reporting team for a gIobal reinsurer.
Challenges
- The actuarial and reporting teams needed to transform the company’s policy administration data into the format 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 the data adjustments/corrections.
- Assessed the infrastructure and tools already available in the organisation including SQL Server and Excel.
Recommendations
- Recommended, developed and successfully implemented, using Power Query for Excel, a replacement of the model point creation process for two portfolios.
Results
- Achieved a 70% reduction in data transformation run time compared to the legacy tools (1 million+ records processed in 8 minutes).
- Removed the manual steps, with Power Query for Excel directly linking to the client’s SQL Server data warehouse for sourcing the data.
- Processing of the largest portfolio (1 million+ records) without the need to break it up into smaller files.
- Client planning to migrate all portfolios to the new solution.
