Policy information lends itself to database technology; the data should be stored in a way that lets you sort, sum and search for important answers. Yet, for a variety of reasons, policy data often ends up in a file drawer, an “electronic” format that is not much more useful, or a litigation management system. All of these systems make querying for basic answers like “How much coverage do I have in 1982?” difficult — even impossible.

It’s ideal to use a robust relational database system that preserves complex relationships among various pieces of information for maximum reporting flexibility down the road. However, this approach is not always an option — especially at the beginning of a coverage issue or for a first attempt at electronically organizing policy data.

As an alternative, it is possible to leverage relational database theory in Microsoft Excel, or other readily available spreadsheet software. Below are some tips to get you started. The ideas discussed here can be utilized for many types of coverage – first party property, workers compensation, third-party liability, professional liability, directors and officers, and even package policies that combine coverage types.

For purposes here, we’ll look at tracking third-party liability (such as general liability) policies:

Consistency Counts

When creating even the simplest database, it’s most important to be consistent — from spelling and text values the same (Home Ins vs. Home Insurance) to the data types in each column (dates, currency, text) and assumptions used during data entry.

Basic fields to capture include:

• Unique row ID
• Full name of insurance company (Continental Casualty Company, Continental Insurance Company, etc.)
• Insurance company group, if owned or operated by someone different than written on policy (CNA)
• Policy number
• Start and end dates
• Attachment point / self-insured retentions (SIRs) / deductible
• Occurrence limit (separately for each potential claim, i.e. products-bodily injury, non-products-property damage)
• Aggregate limits (separately for each potential claim, i.e. products-bodily injury, non-products-property damage)
• Important provisions or exclusions, each in a different column

Data Entry and Storage

Entering and storing data at the most granular level possible is usually the most flexible option. Best data practices include:

1. Capturing each field in its own column or field
2. Being consistent and granular within each column; never more than one data point per column and do not put comments together with data points
3. Use real date fields, not partial dates; make consistent assumptions when missing information, e.g. assume 6/1/1985 if June 1985
4. Remaining consistent in text fields, especially carrier names and policy provisions and exclusions
5. Deciding what limit type or types will be tracked — and track consistently
6. Determining how to consistently enter attachment points, deductibles and SIRs
7. Adding a column for the layer limit, if quota shares are present
8. Deciding how to enter polices issued for more than one year or renewed with the same policy number for multiple years (and do so consistently)
9. Using a column (or more) to track references (hyperlinks, unique IDs, file names) to other related systems or files

Tracking insurance policy information in Excel or a similar program results in a relatively simple, yet useful database that can be manipulated to some degree for reporting and calculation purposes. The information can also be imported into a more robust program when you are ready to gain more flexibility and efficiency.

In Part 2 of this series, we’ll look at the advantages of tracking insurance policy information in a relational database system.

What’s the current state of your policy data? Do you use Excel or another off-the-shelf program to track information? If so, what works for you, and what limitations do you encounter?