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 In
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:

• Premiums received – which, for the purposes of this exercise will be a balance of received and returned premiums. Choose your premium: it needs to be net of any deductions taken by intermediaries such as brokers and, where possible, any estimated or so-called “written” premiums should be replaced with an actual received or “signing” premium which, because it is “real” money, is obviously more accurate!
• Reinsurance recoveries – the claims your insurance company has made against its own reinsurers. These claims tend be rare and pretty huge, so don’t forget them!
• Investment income – Any money that comes in via external investors or premiums payments can itself be invested. If it is not needed to create a reserve against any outstanding claims, then it can be used to buy bonds, shares or whatever else the finance guys believe will turn a penny for them. I’ll discuss more about investment income in the following paragraphs.

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?

Money Out
Reasonably serious quantities of cash find their way out of the organisation in the following forms:

• Claims incurred – the total amount of each claim made, incurred claims will tie up cash as reserves long before they are actually paid out.
• Ultimate claims – As we have discussed in previous articles, this is the actuarial “best guess” as to the volume and value of the claims that will be incurred against a piece of business. It may also include a provision for claims incurred against policies that haven’t even been sold yet. It may include the claims incurred figure, so be careful not to double count.
• Reinsurance premiums – Vast sums are paid out by some companies to ensure that they themselves are covered against several very large claims or even an unforeseen proliferation of small ones, which, if not hedged with reinsurance, could potentially bankrupt the company.
• Operating expenses – Apparently, some insurance underwriters have been demanding a decent, living wage. Most insurance companies find this an expensive though ultimately unavoidable outgoing...

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.

If you found this article helpful and would like to receive the latest insights each month from Mike Ferguson and other experts featured on the Business Intelligence Network, please subscribe to the UK Business Intelligence Network Newsletter.