Case Study: Streamlining Complex Processes with Power Query for Excel

C L I E N T

A UK leader in retirement and protection solutions, investment management

and capital investment with operating profits of more than ￡2bn.

C H A L L E N G E S

• 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.

A P P R O A C H

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 Power Query for Excel 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.

"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

R E S U L T S

• Released additional capacity that can be used to better achieve other business objectives.
• Based on the dry run, an estimated 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 that the team were enabled to maintain the solution.

Christo Muller, Partner, IT Services - For nearly 20 years, Christo has been at the forefront of delivering innovative technology solutions across diverse industries, most notably in the insurance sector. His wealth of experience is a valuable asset to MBE Consulting, bringing with him a deep understanding of the ongoing challenge in organisations: the intersection of business and IT. With a proven ability to rapidly analyse and grasp core business needs, he excels at bridging the gap between the business and technology worlds.

#actuaries #powerquery #insuranceindustry #lifeinsurance #excel #processes