The client is a global insurance company with a large number of reinsurance treaties covering multiple lines.
The company has grown over the years and the number and complexity of reinsurance arrangements and their inuring relationships has increased. This has made it much more difficult to quickly calculate the company’s net position after a natural disaster or cat event. The increased complexity also has knock-on effects in areas such as capital modelling, scenario modelling, Lloyds RDS reporting and reinsurance pricing and renewals.
The current system consists of a series of complex spreadsheets that have evolved over many years. These spreadsheets require the care and attention of a specialist reinsurance team.
The client decided to rewrite the reinsurance netting down system using modern tools and techniques to remove their spreadsheet dependency.
Further requirements were:
Describe Data was given access to examples of the reinsurance netting down spreadsheets and asked to build a proof of concept system that demonstrated how modern software could be used to meet the requirements above. This proof of concept solution was to be developed and delivered in a short time period.
Describe Data was chosen for this project because we possessed both the detailed reinsurance business knowledge and the technical skills to design and build the required software. The client had spent a considerable period of time trying to find resources that combined these two requirements with little success. Furthermore our insurance expertise meant that the client would have to spend very little time educating and supporting the project, this was a consideration due to the low availability of reinsurance staff, particularly during reporting deadlines and at year-end.
We worked rapidly as a small team and had weekly check in meetings with the reinsurance team to ensure that as our solution was being developed it stayed in sync with the client’s requirements.
Starting with the sample spreadsheets we reverse engineered the calculations and isolated the input and output data. We then proposed a database structure, which covered all of the reinsurance treaties, their input loss data, inuring arrangements and output calculations. To this we added a user/roles data model to allow role based access and monitoring of data changes. We shared this initial database design with the client to elicit feedback.
Using this data model we: Generated a SQL Schema and used this to build a Postgres database. Using Next.js we generated code modules that allow CRUD operations on the database tables/records accessible via API. We used tools to generate a simple set of UI screens to perform database updates.
We used this framework to internally workshop how reinsurance netting down calculations could be performed, how they could be presented to the end user and how to implement user authentication and access control.
Using off-the-shelf software tools and components, together with intelligent use of code generation, allowed us to make rapid progress and demonstrate our proposed solution to the client to get feedback for the next iteration.
In parallel with this work we also built a novel reinsurance netting down calculation engine that was accessed via API. Setting out the API interfaces between the system modules early in the project allowed this parallel development to proceed quickly.
We realised early on during the development of the calculation engine that various reinsurance arrangements and their inuring relationships behave like a directed network graph. By representing the calculations as nodes and the inuring relationships as edges, complex calculations could be completed by traversing the network graph in a defined order, performing and accumulating calculations as we go. We made use of off-the-shelf network/graph packages to accelerate this development.
Reinsurance Netting Down User Interface
The final portion of the project was to assemble the various pieces of software, database, data-layer, and calculation engine into a system that performed calculations and presented the results to the user. We used off-the-shelf user interface components to rapidly build the required functionality.
We demonstrated our solution to the client and discussed how we had taken a complex spreadsheet process and ported to a more maintainable and ultimately auditable system in a short period of time using our expertise and off-the-shelf open source software components. Delivered Workshops and training too
Employing Describe Data to design and build a proposed solution was a low-risk, low-cost option for the client that required minimal input from them once the problem space was defined.
Furthermore the following benefits were identified: