Automated validation and extraction of spreadsheet data for a major global
property-casualty and general insurance organisation
The insurer has a major technology and business transformation program to implement high quality data quality governance in order to generate and demonstrate soundly-based solvency calculations.
Like all insurers, a significant proportion of the data to be used in solvency calculations is held and/or manipulated in spreadsheets. It would be a very long and expensive project to replace all these spreadsheets and, in certain areas, would not create a practical solution due to the continuing flexibility required by the business. The insurer therefore required an alternative approach to ensure the application of data governance policies for these business-critical spreadsheet applications.
The core technology components of Solvency II are the risk assessment models that determine the Solvency Capital Requirements (SCR). In order to provide a fully balanced perspective the insurer uses multiple models to satisfy this requirement. They include a specially built internal model (using Towers Watson Igloo) in addition to the use of the so-called Standard Formula Model and further risk assessments using supplemental models.
In order to feed these models the insurer needed to integrate highly disparate sources of critical current and historical information, including SAP, Algorithmics, EMB ResQ and many others. This data must be cleaned and validated prior to consumption.
For the spreadsheet based data sources, the insurer needed an approach that would ensure a sustainable approach to establishing appropriate data and function checks without compromising the business need to retain the flexibility to use and develop their critical spreadsheets.
For those data sources housed in robust, centralised IT applications IBM Infosphere provides the framework for data flow management and integration.
For those data sources in spreadsheet applications, ClusterSeven provides the automated validation solution to check the integrity of operational processes (e.g. timely spreadsheet submission), functionality (e.g. changes to formulas/VBA) and data (e.g. alerts on changes to static triangle data).
Results and Additional Value
The required integrity checks to maintain and evidence data governance policies are now embedded as business-as-usual controls for an initial 250 spreadsheets. This provides automated documentation on processes, with business-focused alerts for anomalous activity. This avoids many hours of manual checks and paperwork.
The solution is also being demonstrated more widely throughout the insurer with a view to addressing spreadsheet related audit concerns in other departments.
In addition, the ClusterSeven solution is now used to go beyond spreadsheet integrity checks. This is because all validated spreadsheet content is held in the underlying ClusterSeven database (MS SQL) and is now directly extracted into the common Data Staging Area. This eliminates slow manual data extraction and error-prone ‘hard-wired’ extraction processes.Print Version
Comment from another client (in the Lloyds market):
"Ensuring good quality data management is a fundamental requirement to support the continued success of Canopius. The real power of the software is its ability to embed appropriate data- and function-checks as part of our normal business practices. It is difficult to know how this could be done effectively without using this type of technology."Mark Allen, Head of Business Information at Canopius Lloyds Managing Agent
"When ClusterSeven approached us, it was the first time we had seen such a product. We immediately realized its value to us as a company."George Flynn, business analyst of product control, European Credit Management