In my last article, I discussed in general terms the enormous proliferation of metrics to be found in an insurance business intelligence (BI) system, and their often arcane taxonomy, derived in part from the historic underpinnings of the insurance market and in part from the rigours of statutory and regulatory requirements more recently foisted upon it by an understandably nervous set of regulators. 

In a data warehouse, which has been tasked to solve several problems with one integrated solution, the resulting metrics are often at odds with each other; the figures required by the regulators may be subtly but notably different from those required by an underwriter manager. 

You can’t blame the regulators; they need a standardised series of measures to be returned to them, prepared to specific guidelines. In this way, they can be sure of what the numbers actually mean, having rigorously specified their formula and distributed the necessary documentation around the industry. Once returned to the regulator, the figures can be used for comparison and aggregation, providing a level playing field across the wider market. 

Equally blameless are the underwriters and their assistants, who are accustomed to working with a set of metrics they have always worked with, and which reflect the numbers in the operational underwriting systems which provide the underlying data to the warehouse and its reports. 

If the data warehouse is to supply statutory and regulatory returns and support the day-to-day operations of the business, this doubling-up of information immediately creates a problem for the business intelligence data architect, both in maintaining the two (or more) sets of metrics, and in keeping them sufficiently well defined and separated, that they are not confused and inadvertently transposed. When a particular report is created for the business by the business intelligence department, confusing the metrics should, hopefully, not be an issue. The problems really arise when the user community has access to ad hoc reporting tool sets such as MS Report Builder, Crystal/Business Objects or ProClarity, and free rein over which metrics to report upon.  

Let’s take a look at a particular example of a set of regulatory reports whose metrics are close to, and even derived from, the “everyday” underwriting metrics, but whose use is specialised and ought to be restricted: the syndicate premium income (monitoring) reports, commonly known as the PIM.  

In a nutshell, Lloyd’s of London requires its syndicates, via the medium of their managing agencies, to return a number of specific figures relating to the amount of business they have written in a particular period for each class of business in which it has declared it will operate, e.g., property, aviation, energy, etc. The insurers have, by this time, already submitted an estimate of these anticipated premium amounts for the year ahead, known as the Syndicate Business Forecast. The PIM is, in large part, the reckoning of those estimates. It is used by Lloyd’s not only to determine the veracity of the forecasts, but to determine whether an insurer has overstretched itself and taken on more risk than it can realistically cover. In other words, the PIM is a report not so much on the quantity of business written, but how much capacity remains in the individual insurer and whether this capacity has been exceeded. 

To achieve this, the report shows an indication of the amount of premium written, gross of broker deductions, and converted to sterling. This metric in its own right is a tempting target for any other user. After all, if it’s good enough for Lloyd’s of London, then why not pick it up and stuff it in a weekly report for internal use or present it for inclusion in the monthly report to the board? Some attention to the small print of the PIM literature will tell us why not. This particular metric does not contain any business transacted in U.S. dollars – greenbacks are reported in a separate column on the PIM. You’ve just misrepresented your premium position to the tune of several thousands bucks, buddy! 

Naming standards, departmental standards, data orientation training and a strong, popularised data dictionary (or shared business vocabulary) should help make the distinction between what is a good lingua franca metric for internal reporting and what should be left to the accountants. If more control is required, certain metrics should be hidden from general use and only revealed to those with the correct permissions. 

The Ultimates Challenge
Actuaries are the soothsayers of the industry, tasked with determining what the figures on the insurers’ balance sheets are going to say, twelve months before they actually say it. In fact, every figure on the balance sheet has its actuarial counterpart, called an ultimate. What is more, these highly educated guesses are constantly revised, usually on a quarterly basis, until they can be replaced at the end of the reporting year by the “actuals” the insurers have lovingly tended in their data warehouses. Some ultimates are relatively straightforward, being a stab at a particular figure, such as the amount of claims that will be incurred against the property portfolio by the end of the year. 

Others are complex curves that determine the amount of risk an insurer is exposed to at different points during a policy term. Construction projects, for example, have relatively well understood risk development curves that reflect the fact that in the early stages of construction, a concrete foundation is unlikely to burn down, but a half-completed shell, full of blowtorch-wielding heating engineers, is a highly risky proposition. Toward the end of the project, when the sprinkler system has been commissioned, the risk flattens out again. Applying this curve – usually supplied by  the actuary in the form of a series of percentages – to the premium can give an indication of how much of that premium has been “earned” at any one time, a source of much fascination to many underwriter managers.

Actuaries will ask for regular feeds of data from the business intelligence system in order to fine-tune their ultimates figures, which also require input from many other external sources, an opaque procedure which adds to the crystal ball mystique of the actuarial profession. For the insurers’ part, ultimates can be graphed alongside actuals to spot weaknesses in any part of the business plan or to determine how much more of a particular class of business can be written before the reserve limit is reached. 

Ultimates are usually supplied on spreadsheets – an approach anathema to most good data warehouse developers, as you can be sure that the format will change just enough every quarter to break the ETL. The purchase or construction of a flexible spreadsheet-reading tool is advised! One last point – as ultimates attempt to predict the future of your business up to a year in advance, they are amongst the more commercially sensitive pieces of data to be stored in the data warehouse – right up there alongside the horrors of the HR system, with its salaries and sick days. A little column-level security would not go amiss!

Recent articles by Richard Brayshaw

• Using the Data Warehouse to Determine the Profitability of the Insurance Company
• Structuring the Unstructured
• What Happens Next?
• The Language of Insurance Business Intelligence