Oops! The input is malformed!
Originally published 20 June 2007
Insurance is a complicated way of making money. So complicated, in fact, that you may not know you have done so for several years after you have started selling the stuff. The production of a set of accounts gives a once-a-year view suitable for regulatory purposes but not at all suitable or appropriate for management reporting, having neither the timeliness nor depth that that application inevitably demands. So, as usual, it’s business intelligence to the rescue.
Determining the pre-tax profitability of an insurance company as a whole, or of its various components and business activities, is well within the grasp of a reasonably mature data warehouse. If you have captured certain key pieces of data, or are prepared to make a reasonable estimate as to their value, you are halfway there. The second half of the process is the calculation itself.
Let’s address that first half: What is the minimum amount of data you require for a reasonably accurate attempt at calculating profitability? Also, what peculiarities does the insurance business model introduce that the avid business intelligence (BI) analyst needs to be aware of?
Money flows into an insurance company from a number of different streams. Capture these metrics in your fact tables! The most important and relevant to the task of calculating profitability are:
All of these metrics need to be allocated to the correct dimensions within the data warehouse. If your company is busy selling two classes of business, say, property insurance and motor insurance and you wish to determine the relative profitability of these two areas, then assign the correct dimension value to each transaction in the fact table to divide the money appropriately between them.
Assigning the class of business dimension to investment income is a little trickier. The two classes of business mentioned, property and motor, have different “tails”, that is, the amount of time between receiving the premium for a particular policy and settling the last of the claims on that policy is very different between the two classes. Motor policies can take a surprisingly long time to generate and settle claims because of all the potential legal meanderings associated with the claims settlement process in that particular class. Property claims, on the other hand, tend to be settled in short order – “Yes, that factory has definitely burnt to the ground. Here is a large cheque!”. This difference in tail means that premiums from motor are hanging around longer and available for investment for longer periods of time before they are needed to settle claims and, therefore, pound-for-pound are likely to generate more investment income. This has to be borne in mind when allocating these funds back to the classes. This money might make the difference between making a profit on a class of business or making a loss. A class may have generated £1million in premium and paid out £1million in subsequent claims and operating expenses. On the face of it, there is no net gain for the insurer. However, the time lag between these two events allows the premium money to be invested for a tidy return, which is allocated back to the class, making it profitable.
Now then, what else do we need to capture in the data warehouse?
Reasonably serious quantities of cash find their way out of the organisation in the following forms:
As with the money in, money out has to be captured within the data warehouse and allocated to the appropriate areas to be measured, whether they are individual classes of business, territories, types of risk, etc. Obviously, the more of these dimensions you can allocate to all of the metrics, the more areas of profitability you can explore.
Doing the Sums
For the most part, the profitability calculation is straightforward and is executable in a number of reporting tools. Subtract the money going out from the money coming in. For the purposes of comparison between different areas of the same business, a loss ratio is calculated. Quite simply, the amount of money out is divided by the amount of money coming in, the result being expressed as a percentage. At this stage, having included the major cash inflows and outflows on both sides of the ration, any result of 100% or more is a bad thing, and any result under 100% is money in the bank. Try it. I have. It works.
Recent articles by Richard Brayshaw