Building a pricing system from a spreadsheet

Have you got an Excel pricing model that you would love to turn into a system?  Has the complexity of building a system inhibited progress in your company? This is not an uncommon challenge in the insurance environment, and this paper will explore the new technology that makes it easy to convert these models to the cloud and make them accessible and massively scalable. The common assumption is that coding languages like Java and Python are the most popular in the world today, and they are – unless of course you include Excel as a coding language. Then the numbers alter dramatically – there are an estimated 800m Excel users in the world, and only 26m software developers who are already stretched too thin.

Excel users have been ignored for too long by developers, and Optalitix has developed technology that empowers Excel users to create systems as robust and scalable as systems built by software developers. The technology to convert these spreadsheets into cloud-based systems continues to improve, and with it, the enormous opportunity of turning Excel developers into software developers. This will lead to increasing increase in the speed of development and reduce the cost to build systems. This paper describes how an Excel developer could build their own pricing system from a spreadsheet. It answers the following questions:

A. Why are spreadsheets so commonly used in insurance pricing?

B. Why do these spreadsheet pricing models now need to go digital?

C. What options are available for spreadsheet models that need to scale?

D. What features are needed to enable the conversion of these spreadsheets?

E. What does this mean for the future?

1. Spreadsheets are frequently used for pricing

The widespread use of spreadsheets for pricing insurance has contributed to a business environment that is heavily dependent on spreadsheet results without due consideration for the need to connect these models to core operating systems. The reason for this adoption is that the user experience is simple and visual, and spreadsheet skills can be can be easily acquired. This has lead to significant adoption, making spreadsheets the most used tool for underwriters, actuaries and analysts in insurance. Spreadsheets allow for complex calculations to be built quickly in a flexible coding environment while simultaneously ensuring common calculations can be done repeatedly, although this is often at lower volume than a standard system. Surveys like the one performed by Milliman on actuaries in 2018 (see graph) highlighted the importance of spreadsheets to insurers – over 91% considered spreadsheets to have critical or medium importance to their work.

Source: Milliman UK survey, “Insurers, spreadsheets and Model Risk”, 2018

2. Spreadsheet models now need to go digital

The time is now right for spreadsheets to move to the cloud. Data collection and the models used for analysis have all migrated to new cloud-based systems, moving to a digital environment that is rich with features that include dashboards, databases and seamlessly integrated AI and automation. This digitization has reduced operational and security risks and accelerated the ROI and profits seen within the insurance sector. With a cloud-based digital solution, model results can be easily scaled, and with scale comes the ability to issue a greater volume of quotes resulting in greater lead flow and more sales. Transparency and collaboration are also huge benefits of a cloud-based digital pricing model. Spreadsheets are valuable to companies due to the critical business logic built into them. However, these models are not available to the wider business as they are held on users’ desktops or hidden in file directories. If these models could be connected to core systems and were more accessible and scalable, the value of the IP built into the spreadsheet could add significant value to the business. At the same time, regulators are uncomfortable with the lack of oversight and control exercised over spreadsheet models as many past cases have shown. A shift to a more cloud-based solution addresses these concerns.

3. Options available to convert spreadsheets

There are 3 options available to do this:

Option A:

Recoding the system into another language. This requires a full system build to replicate the underlying logic in the spreadsheet. This will require the developer to work alongside the model builder to explain the spreadsheet logic and check the results.+ Internal developer team can do it without requiring external systems- Cost is prohibitive (developer and pricing expertise required)- large volumes of models to convert which takes too much time- modelling errors are common- resources are unavailable- systems are hard and expensive to update when models change.

Option B:

Use an existing commercial pricing platform to recode the spreadsheet. There are a number of software platforms that provide standard tools to help developers create pricing systems. These often have their own internal language and structures to reduce the time taken for the build, with experts on these systems needed to create and maintain the pricing system.+ commercial pricing platforms have tools that reduce the time required to recode the spreadsheet+ these tools have methods of checking for errors- Recoding spreadsheets remains difficult in these systems- The cost of the platform can be high- identifying developers who know the pricing system can be trickier than finding standard developers.

Option C:

Use an Excel converted An Excel converter is new technology that allows the user to upload an Excel model and instantly convert it into a hosted system. This reduces the cost of the build and removes the need for developer involvement except for the integration to the core system.+ there is a significant reduction in the conversion time required per model (minutes rather than months per model)+ updates to the models can be done in Excel and automatically update in the system+ testing and debugging can be done against the spreadsheet while the Excel developer continues to maintain their own models- Integrating with other systems can be cumbersome as some Excel converters don’t work with all Excel models- There may be a system requirement to design screens for data inputs if not provided by the tool

4. Features needed for converting pricing models using an Excel converter

Optalitix has worked with all 3 options and have determined that using Excel converters to convert pricing models is the most efficient and effective.  Many clients require enterprise-grade systems and as such Excel converters are the most suitable, they also allow model updates without requiring relying on developer resources for full system recoding. Our view on this is based primarily on cost, accuracy and speed. The best Excel converter platforms have the following features:

A. Ability to convert Excel models: This is a core feature. The Excel converter needs to be able to faithfully convert the exact model calculations from the original spreadsheet, and this needs to be tested against other models. The converter needs to allow for complex formulae, and important model features like tables and graphs and have a solution for macro conversion.

B. Testing: The Excel converter platform must allow the user to test converted models and produce the exact same results as the original spreadsheet.

C. Connections: The converted Excel model needs to connect to all the other systems involved in the pricing process. This connection can be via an API (the standard method used by systems to interact), a hosted web environment that can be embedded on a client’s website or a custom integration with the underlying quote or administration system.

D. Model governance: The converter should be able to track model versions and model use as well as keep a record of the underlying calculations for checking and debugging as well as monitoring changes made to the model.

E. Model updates: All models go through multiple changes and versions. Any Excel converter must allow the user to update models quickly without impacting other core systems, allowing for proper user testing before promoting to a live system.

F. Databases and dashboards: The system must keep a database of model usage, and provide an overview of the health and usage of the underlying system using visual dashboards.

There are many complimentary features that are useful but generally, these are available in the better converter systems or in underlying insurance pricing platforms.

5. The future of systems with embedded Excel models

Optalitix maintain that the ease with which Excel models can now be converted and the number maintained for complex connections and for data inputs and outputs, the heart of these systems (the calculations and IP stored in these models) will be adopted by he Excel modelers and their Excel converters. This approach will significantly reduce the bottlenecks that currently exist in creating systems, and will lead to faster system development, lower costs and more imaginative system use cases as the 800m talented Excel users become system developers. The impact on companies will be similarly significant, with early adopters rapidly testing, publishing, and scaling new systems using the IP that their staff have currently got locked on their desktops for  insurers, the ability to quickly create products with spreadsheet-driven pricing models that can be scaled in the cloud will make them more agile and far more profitable.