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:
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:
Entering and storing data at the most granular level possible is usually the most flexible option. Best data practices include:
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?
Never miss a post. Get Risky Business tips and insights delivered right to your inbox.
For nearly 25 years, Elizabeth Hanke has been a trusted advisor in both the settlement and litigation arenas, and KCIC clients can always expect her to work passionately on their behalf.Learn More About Elizabeth