Client
A UK leader in retirement and protection solutions, investment management and capital investment with operating profits of more than £2bn wanted to streamline their complex actuarial processes.
Challenge
- The current Group own fund and Solvency II surplus consolidation process was taking a disproportionate number of days to complete.
- The update of Excel workbooks across the process had to be carefully conducted with multiple workbooks open simultaneously.
- The saving and closing of the workbooks had to be performed in the correct sequence to avoid incorrect numbers.
- This process was identified as a significant source of operational risk.
Approach
Discovery
- We asked the client to complete our Actuarial Performance Management (APM) Assessment to help identify the key issues with the process.
Analysis
- We engaged senior stakeholders to map out the process and perform a root cause analysis to identify the cause and effects.
- We performed a desktop analysis to identify alternative technologies that could be used to automate the process and reduce operational risk.
- We successfully completed a prototype using Excel Power Query which perfectly met the needs of the team.
Recommend
- We recommended, developed and implemented a new solution using Excel Power Query based on the success of the prototype.
Results
- Released additional capacity that can be used to better achieve other business objectives.
- Based on the dry run, we estimated a return on investment of 12-15%.
- The implemented solution removed all spreadsheet links, improved auditability and significantly reduced the operational risk.
- Facilitated knowledge transfer and delivered solution specific training to ensure the team were enabled to maintain the solution.
Testimonial
“What MBE developed for us was perfect for our requirements. My team and I have become great advocates for Power Query in the organisation. Without doubt it has massively reduced our operational risk and created additional capacity.“
Head of Actuarial Reporting