Process Automation in Insurance Series #1 : Workflow optimisation with automated process mapping

This article marks the start of a series where we explore practical use cases of process automation in insurance from process mapping to auto-generated reports. Finalyse focuses on financial reporting, valuation, risk management and pricing but the same approach can be applied to any other workflow.

Digitalisation is on the top of the agenda for insurers as part of their efforts to improve operational efficiency and transparency via modernisation. Process mapping is the critical foundation of all such initiatives especially when moving away from spreadsheet-based processes. It enables the exploration of key workflow components - inputs, calculation steps and outputs – using visual tools such as flowcharts regardless of the technological environment.

The aim of this article is to explain how process mapping can be accelerated via automation using an open-source toolkit. Finalyse project teams use the same approach to understand the context of their projects and deliver value. Insurers looking to optimise their workflows can benefit from the same methodology due to reduced project timelines, improved accuracy, and user experience.

In the next two sections we show how processes can be mapped manually or accelerated via automation by extracting metadata from spreadsheet-based processes, including a complex web of Excel files widely used in financial reporting.

Finally, we show how advanced technologies can take this a step further. The information underlying the auto-generated process maps can analysed further with process mining to provide additional insights. Generative AI tools turn a spreadsheet-based process into open-source code by the click of a button to enable repeated process runs or sensitivity analysis quickly.

Process maps are visual aids that describe the sequence of work for a particular process or procedure. They are helpful tools in financial reporting to clarify the set of tasks that need to be repeated periodically. With robust process mapping in place, teams can function efficiently with heightened levels of accuracy, transparency, and accountability.

A process map should clearly define each task, the sequence of process steps and who is responsible to perform them. They are particularly relevant when a business undergoes a process transformation project. A process map can serve as a starting point to define the steps in the current process and identify areas for improvement.

The visual nature of the process map allows it to be presented at a high level to a wider audience of stakeholders, who may not necessarily have the technical expertise required for each step.

Several tools are available to create flowcharts, process maps or other visual diagrams, some examples and their key features are included in the table below.

A simple process map describing the Best Estimate Liability (BEL) sensitivity analysis for annuities is shown below created with LucidChart:

An effective process map should distinguish between the types of steps in the process. In the example above, the inputs, process steps (model set up and calculations) and outputs are shown with different shapes and colours. This makes the process map more visually appealing and easier to follow. Setting standard shapes and colours for each step is good practice to achieve consistency across different process maps in the organisation. Start and end nodes can be particularly useful when merging individual process maps into a wider chain as it enables the user to connect them easily.

The level of detail in a process map can also be tailored to the needs of the audience. The process map shown above highlights the main calculation steps and defines the inputs and outputs associated with each; however, it is low on technical detail. It was prepared for senior stakeholders with non-actuarial background looking for a general overview of the steps involved in the BEL sensitivity analysis. Also, as the process is performed by the same team, no distinction was necessary on who carries out each step in this case.

On the other hand, this may have limited utility for experts with technical expertise (actuaries) running the process. For such an audience it is advisable to increase the level of detail for each step. This may include specifying file paths and names or showing the process steps in more detail making them easy to carry out in production mode and serve as a checklist. This will allow users to go through the process in a methodical manner.

No single process map will meet the requirements of all stakeholders as the visual appeal may be compromised when adding additional layers of detail. At Finalyse, when supporting process mapping, we tailor our solutions and reports to the needs of our clients, stakeholders, and the objective of the project. Diagramming tools provide techniques to tackle this, e.g. by hiding certain details in plain sight as they only appear when hovering over the step in question. Each process document should specify the target audience clearly and the level of detail included should match the needs of the audience.

Process mapping is usually performed manually by investigating all process components and their relationships one by one. This is often time-consuming, prone to human error, and difficult to scale for large, complex workflows. In the next section we show how these challenges can be overcome via automation.

Automated process mapping simplifies workflows by rapidly identifying relationships and dependencies and enabling scalability for complex processes.

Processes based on spreadsheets (mainly MS Excel) are widely used in financial reporting, planning, or risk management where calculations are performed across multiple worksheets in the result file and using external links to other spreadsheets. Exploring and describing the calculation steps of such a process manually is a time-consuming, error-prone, and iterative process whereby the analyst, reviewer or auditor follows the formulae and links working backwards from the results reported.

However, this process can be automated by utilising metadata via the following steps:

Collecting and exploring metadata on a spreadsheet-based process in step 1 requires going through each cell of the result spreadsheet via program code, analysis of the formulae and detection of links to other worksheets of external files. The level of granularity and aggregation will depend on the use case. Metadata describes the data flows between cells, spreadsheets, and Excel files (workbooks). It includes formulae in cells without regard to the current value of the calculation in the cell.

All steps can be achieved using open-source tools. We used R for step 1 and yEd for steps 2-4 to generate the charts in this section (but Python is a viable alternative for all steps).

The auto-generated process map for the BEL calculation of annuities is shown below. It involves annual cash-flow projection and present value calculations for 1,000 annuitants over a period of 60 years. The chart was created without even opening the five spreadsheets involved in the process.

Notes: 1. The blue boxes represent sheets of the Excel result file 2. The green boxes denote other linked Excel files (RFR_sport_no_VA: risk-free EIOPA discount rates, PNML00/PNFL00: UK mortality tables) 3. The number labels on edges show the number of references to the source file. 4. The thickness of the edges is proportional to the number of references.

The diagram shows that even such a simple process involves over 1.5 million formula references to other sheets or files. In a real-world setting this could quickly reach 1.5 billion e.g. with 100 thousand policyholders and 10 sensitivities (i.e. 100 x 10 = 1,000 times vs original base case) resulting in a slow and cumbersome workflow with little added value. Note that all such formula references are prone to error in Excel-based workflows with limited functionality to for debugging. Process mapping exercises often detect such errors even if the objective is only to document the current process for audit, validation, business continuity, or simply as good governance practice, not necessarily process improvement or automation.

The diagram also highlights that the most involved calculations are performed on the Liability calculation tab, with links to the Mortality tables and Parameters. This information can help speed up the process, e.g. by converting it into Python or R code and performing calculation intensive components of the calculation in vectorised manner instead of cell by cell.

The same process mapping approach was applied on the spreadsheet containing risk-free curves published by EIOPA for 31 December 2022. This file does not have external links, so the focus of the exercise was on formula references across sheets. Without opening the file, we obtained the chart below.

Finalyse supports its clients with pragmatic, tailor-made solutions to solve real-world business problems in the domains of financial reporting, valuations, risk management and pricing often by bridging the gap with IT. We aim to develop trust-based, lasting relationships with our clients. A cooperation in process mapping can be the first step in our journey together.

• Find out more about our approach for process automation for (re)insurers
• Request a complimentary process automation workshop so that we can help identify and prioritise your use cases