The Challenge

An insurance broker required a bilingual financial model to present various insurance strategies to its clients. The client’s requirement was the insurance tool needed to update dynamically to reflect varying age ranges, insurance products, and insurance providers. Finally it needed to revert between French and English to satisfy marketing presentation requirements.

The financial model needed to educate the insurance firm’s clients, by analysing the respective insurance products’ premiums, benefits, present value of premiums and benefits, and each internal rate of return (IRR). This marketing tool was to improve the presentation of various insurance strategies, and assist decision-making of insurance products for the insurance broker’s customers.

The Mandate’s solution

The insurance client required Strategize to construct a highly customised, user-friendly and seamless spreadsheet tool. It had to be bullet-proof, update seamlessly and present the comparative results in an array of dashboards and graphs.

Strategize built a financial model that could not only toggle between French and English across all worksheets and output reports, but it could also toggle between a French and an English version of the Insurance Advisory firm’s corporate logo.

Customised VBA macros were composed to add further value to the tool. A “Créer nouveau client?” (Create a new client?) macro cleared all values in data input cells in seconds, which guaranteed the spreadsheet user didn’t incorrectly apply previous insurance data to a new comparative analysis. It also saved time and removed the manual clearing of data input cells.

A second macro, “Mettre à Jour l’âge” (Update the Age), ensured all age-range sensitive reports were revised to fully reflect a new age range of insurance data. As the following demonstrates; it further improved the automated and user-friendly value of the model, by enabling seamless updating of output tables – no matter what the age of the client is. The following demonstrates how the tool flexes from 52 years old to 65 years old.

A simple, but powerful feature for the insurance client was the incorporation of conditional cell formatting in the “Comparative analysis – detailed table” schedule. This involved the highlighting the year in the schedule, when the cash surrender value would be greater than the total expense of annual premiums for the insured person.

Further challenges overcome

There is one feature of Excel that is always a challenge to resolve. It is the need, often with financial modelling, to create a flexible x and y-axis graphing functionality with Excel graphs. Evidently the marketing value of an insurance analysis is the presentation of a clean, error and space-free graph to summarise an insurance product. For example, creating a graphing solution whereby the insurance firm could move from a 52 year old to a 65 year old for illustrative purposes, without the tedious and time-consuming need to manually adjust several graphs was value-adding.

A further challenge was to produce a second graph that could go one step further; the ability to graph a section of a time series of insurance data, in order to better sell and present varying insurance products to a client. The simple addition of two drop-down boxes; one to allow the insurance professional to select the age range, and a second drop-down box to specify the age range in the graph increased marketing value of the tool.

Concluding words from the Client

The client was most satisfied with Strategize’s detailed comparative model. The very essence of the value-add to the insurance broker was the creation of an insurance marketing tool, which they believe “now makes us unique in our industry”. It is something Strategize strives to fully achieve with all of its clients; an “unwavering commitment to fully satisfy us” as the insurance client declared at the completion of this mandate.