Shock horror 0% of insurance companies plan to eliminate spreadsheets

An informal poll of insurance professionals shows that no insurance companies expected to get rid of spreadsheets from their Solvency II reporting processes.

Finsbury hosted the session on Data Quality at the recent Insurance ERM Data for ERM & Solvency II event.    This is the second blog post on the key items discussed at the conference. One of the items that came up was whether you can you get all your data into a data warehouse and  get rid of spreadsheets.   An informal poll said it all.  The audience of maybe 100 insurance professions where asked how many of them planned to completely get rid of spreadsheets  in their Solvency II reporting.  No one put their hand up.

Following the vote there was a lively discussion on the role of spreadsheets in the  data and data quality.   One observation was that a typical system architecture  just showed data being stored in data warehouses.  Everyone knew that the reality was very different.   A great deal of processing and data storage would take place on spreadsheets, especially in the actuarial department.   Data warehouses are good for storing high volume low complexity data, but storing complex low volume data can be very expensive. By contrast spreadsheets are good for low volume, high complexity tasks.

A delegate recounted the story of the one sheet spreadsheet that contained historic annuity rates going back several decades.  As the data was critical the data warehousing team wanted to store it in the data warehouse.  To arrive at a specification for the work the questions were: Question (1): what is the source for this data. Answer: the spreadsheet.  Question (2):  How can the data be validated ?  Answer against the spreadsheet.  Question (3):  How are you going to use the data? Answer: it will be downloaded into a spreadsheet for input into the model.

Many insurance companies have sensibly reduced the scope of their data warehouse projects and accepted that some of the processing and reporting will be done on spreadsheets.

There are lots of reasons why spreadsheets will not always appear on the systems diagram.   IT architects often do not consider spreadsheets systems to be real systems and assume that everything is best stored in a formal system.   If the long term plan is to remove the reliance on spreadsheets, it can be embarrassing to admit that a big chunk of the most critical processing is spreadsheet based.

Regardless of whether a data item is processed in a database or a spreadsheet it still needs to meet the data quality requirements of Solvency II.

We think the practical considerations are:

  • More companies should accept that spreadsheet based processes will remain a critical part of the Solvency II landscape
  • Identify their critical spreadsheets so they can be tracked and managed
  • Put in place appropriate controls and automation to facilitate effective use of spreadsheets

Maybe a good first step is to put the spreadsheets on the systems architecture diagram and be explicit about their use.

Read a Case Study by a leading insurance and financial service business on implementing Solvency II, click here for Finsbury Knowledge.