London

71-75 Shelton St
Covent Garden
London
WC2H 9JQ
01235 821 160
View map

Edinburgh

1 Lochrin Square
92 Fountainbridge
Edinburgh
EH3 9QA
01235 821 160
View map

Administrative Office

BH Office
Church Street
Ardington
Wantage
Oxfordshire
OX12 8QA
01235 821 160
View map

Send us a message
CLOSE X
Contact Us

Implementation of New GI Reserving Tool

Our client is a leading Global General Insurance group, serving clients in locations around the world with an extensive product range.

Background

The client needed some support within its Group Reserving Team, to cover the maternity leave of an existing Actuarial Analyst; key aspects of the role were to produce management information, undertake reporting tasks and develop the processes used to produce this information.

Project Aims

The main focus of the project was to develop and run the required reporting processes to produce quarterly and monthly summaries.  Reserving figures would be taken from each business line and collated by the Group Reserving Team to produce final reserving results; these would then be input into a number of Excel summaries for the business line actuaries and management.

Shortly after the start of the project it was confirmed the client would be merging with another insurer to form a much larger group; as part of this merger a new proprietary reserving tool was being introduced.  Processes therefore needed to be developed to reflect both the business reorganisation and the switch to a new home for the data, and the project also required input into testing and reconciliation around this new tool.

APR's Role

APR proposed to send Richard Collins, one of our actuarial associates, to work on the project; after an interview he was selected for the project.

During Richard’s time on the project he worked on a number of Excel spreadsheets and Access databases as part of the quarterly reporting processes; these included Simple Reserving Summaries, US Earnings Reports, Earned ULR Development, a ‘Triggers’ spreadsheet showing current position compared to Syndicate Business Forecast and US Trust Fund Data.

Following the introduction of the new reserving tool and the business reorganisation, each of these processes and tools required some development.  Data was now being taken from new sources so Richard created a database to collect this information and output in the same format as the previous data source, to be used in several existing spreadsheet models.

Most processes were automated using Excel VBA, but making use of the skills in Excel / VBA he had developed with APR, Richard was able to improve the underlying code and structure, in most cases substantially decreasing run time.  Some of the processes also required the use of WPS (World Programming System, using SAS language) where Access or Excel could not manage the data effectively.

In addition to developing the models themselves, all changes were fully documented, including a detailed description of how to run processes in future.  This was of particular importance to the client given a relative lack of VBA experience.

Richard also took part in testing of the new reserving tool.  This involved creating parameters and datasets based on past data, to be imported to the new system by the system administrators.  Once imported, the new system’s output was tested against that from the previous system, with any differences investigated and detailed to the administrators who would make the recommended changes in the tool.

Outcome

The production and development of BAU processes, the main focus of the project, were successfully delivered over the course of the placement, with all testing worked on completed to schedule.

The reports and processes Richard worked on showed improved run-times, are well-documented and will be easier to update for runs in future quarters.