One of the most difficult things in data warehousing is actually not its technicalities, but understanding the business. There is no escape that we have to understand the business, in order for us to be able to model the data warehouse correctly. For example, I came across a case the other day where somebody asked me about the concept of layering in insurance, and specifically in Lloyd’s market. He needs to understand layering concept in order for him to be able to model the data warehouse correctly. He searched the web and found the following:
Layering: “The building of a program of insurance coverage using the excess of loss approach. Layered programs involve a series of insurers writing coverage, each one in excess of lower limits written by other insurers. Umbrella liability coverage is frequently structured in this manner, whereby a number of umbrella insurers write coverage at various levels, on an excess of loss basis, ultimately providing an insured with a high total limit of coverage”
After reading that definition he didn’t quite understand it so he asked me to explain. So I explained the following, which I’m sharing with you in this article/post.
In reinsurance industry nobody want to cover the whole thing. If a large US retail firm XYZ looks for coverage against fire, tornado, wind storm, hurricane, and flood for all of its 3500 stores in the US, the broker or managing agent will probably arrange it as follows:
Syndicate1 100m x 0
Syndicate2 400m x 100m
Syndicate3 500m x 500m
Syndicate4 800m x 1b
“x” means excess. Example: you took a house insurance with Churchill for £120k x 250 meaning that if the house burned down, and it costs £120k to rebuild, Churchill will give you £119,750 and you have to pay the 250 your self. That’s excess. Same in car insurance.
In the above case Syndicate1 is taking $100 million excess zero. Meaning, no excess.
Syndicate2 is covering an insured amount of $400m from $100m.
Syndicate3 is covering an insured amount of $500m from $500m.
So if the XYZ store in Buffalo was burned and the loss estimate is $5m, then Syndicate1 will pay $5m.
But if a disaster like Hurricane Ike hits and XYZ lost 70 stores, which cost them $150m, then Syndicate1 will pay $100m and Syndicate2 will pay $50m.
So when a catastrophe like Katrina hits and XYZ lost 200 stores which cost them $700m, then Syndicate1 will pay $100m, Syndicate2 pays $400m and Syndicate3 pays $200m.
Obviously the premium is different. Syndicate1 bears the biggest risk, because the chance of a small event occurs is much bigger than a big event. Statistically speaking that is, and here’s where actuarial calculations and matrices comes in. Hence Syndicate1 gets the biggest premium.
Syndicate4 will get the smallest premium (percentage wise), but very profitable because it is very likely that the year will pass without a single event hitting $1 billion mark. Which means Syndicate4’s loss ratio will be very healthy.
Reinsurance companies like high layer business because of its profitability. Unfortunately the capital adequacy standard from FSA (and from next year Solvency II) require them to have large enough capital in order to be able to make good amount of profitable business in that market.
As I said in the beginning of this post, one of the most difficult things in data warehousing is the business knowledge. And insurance is one of the most difficult industry there is. As usual I welcome any corrections, discussions and questions on email@example.com.