Extending the Analytic Capabilities of Excel for Actuaries – Insurance Loss Triangles

The client is one of the largest property and casualty insurance companies in the world. This solution was for the surety division who provide specialist insurance products such as contract, commercial and fidelity bonds.


The Vision

The client’s vision was to provide capable analytical tools to actuaries that are also easy to use. Loss Triangles are analyses used by actuaries to visualize annual loss development over time on a policy and predict the development of losses through to maturity. It is possible to model loss triangles with a number of tools of varying complexity. The vision was to do this with easy-to-use Excel together with a SQL Server back end that would provide the analytical and multi-dimensional modeling power required. It was also important that any new system could be validated to the ledger before being adopted.


Realizing the Vision


The system includes 25 metrics, 3 date dimensions and 8 business dimensions with the ability to filter data on business characteristics, provide various selections of loss development buckets, and recreate historical triangles at any point. The data is pulled from the source-of-record claim centre.

Integration Services is used to bring together the relevant data onto a single SQL Server database. Analysis Services is the analytical and calculation engine. The users work directly with Excel using drag and drop functionality. The Excel application and Reporting Services reports are accessed through a SharePoint portal within which users can extend, save and share their analyses.

Actuaries no longer spend time constructing individual triangles. They are able to concentrate their energies on exploring and understanding the data and making balanced judgements. Users can be confident that the data is accurate and current – it is validated to the ledger and allows drill-down to claim level detail.

Underwriters also find the application easy-to-use. It helps them to build proposals individually tailored to customer needs.


Next Steps

The client is impressed by the quality of predictions made by actuaries who are using the Surety Loss Triangle application because they are proving more accurate than the source of record reporting system. As a result the system will be used to provide more reporting in the future. The next step is to add reinsurance data to the triangles so the differences between direct/assumed and ceded data can be viewed.


Want to Know More?

If you would like to know more about what can be achieved with Business Intelligence & Analytics and would like to discuss your options with an independent specialist, please get in touch with Evelyn Heyes in the UK (evelyn.heyes@thorogood.com) or Trevor Jones in the US (trevor.jones@thorogood.com).