In the growing and evolving actuarial profession, more and better data has replaced the data of years past.

However, for a variety of projects, the good, clean data used for analysis often begins with a host of challenges.

What makes data “bad” or difficult to work with? If you ask an actuary that question, you’ll receive a wide array of answers. For the purposes of this blog, we will focus on data issues commonly seen during a reserve analysis. The issues we typically encounter include not having a prior analysis, changing reinsurance structures, balancing issues, missing crucial information and issues within the structure of the data itself.

Let’s begin by investigating possible reasons for not having a prior analysis. Usually, this is because the company or coverages being reviewed are new. Whenever this type of issue arises, the first step actuaries often take is determining how much data the company has and if it will be credible enough to use on its own. Another option is to use benchmark data such as the National Council on Compensation Insurance (NCCI) for workers’ compensation, or A.M. Best data for a similar larger company. Communication is essential in this scenario to ensure full understanding of the underlying exposures and use of the correct benchmarks.

The next two issues, changing reinsurance structures and balancing issues, often go hand in hand. Reinsurance structures can change frequently, and rarely coincide with the start of a new policy period. Different structures and treatments of expenses may exist depending on the coverage and state. Changes to per-occurrence retention levels, claims reaching new higher limits, and uncertainty in how a program is capped can also lead to balancing difficulties. Actuaries typically balance to gross losses first, and then focus on net. To account for a changing reinsurance structure, losses are split into segments based on which specific reinsurance agreements various claims fall into. Finally, if there are still difficulties balancing, actuaries focus on large losses to make sure all claims were capped correctly. Again, communication to fully understand the reinsurance structure is key to making sure losses are handled correctly.

Situations do arise where the data itself is missing. Premium or exposure information may be missing for certain years, and must be estimated based on company and industry trends. In addition, premium is occasionally provided on an aggregate basis for several coverages, requiring actuaries to allocate it back to coverages based on loss funding projections or other methods. Another common challenge is working with summarized claim detail, instead of a listing of individual claims. In these cases, a request can be made for the individual claim detail, or if that is not available, a listing of any claims over a specific retention. When encountering these issues, a full understanding of the provided data is essential.

A final challenge is related to the structure of the data, a fairly broad area. One example of this issue is data received in multiple files or multiple PDFs. Converting the PDFs into Microsoft Excel sheets and combining multiple files into one makes the data easier to manage. Another concern can be confusion about how to allocate losses between lines of business. In this case, communicating with claims administrators to understand fields in a loss run or fields that potentially need to be added to a loss run is often helpful.

Finally, for larger clients who have thousands, or even millions, of claims over multiple policy periods, size and manageability of data files can become a concern. While Excel is a valuable tool for actuaries, the program does have its limitations. In this case, using software such as SQL to manipulate and summarize the data before bringing the required information back into the analysis may be much more efficient.

For actuarial consultants, data from clients is crucial to a comprehensive analysis. Although that data may arrive with any number of issues and challenges, solving those puzzles gives actuaries the opportunity to demonstrate our ability to adapt, innovate and communicate.

Darcie Truttmann is a consulting actuary with Pinnacle Actuarial Resources in the Bloomington, Illinois, office. She holds a master’s degree in mathematics from Western Illinois University and a bachelor’s degree in mathematics from Illinois College. She has experience in assignments involving group captives, alternative markets, loss reserve and funding studies, public entities, and staffing and construction industries. Darcie is a Fellow of the Casualty Actuarial Society (FCAS) and a Member of the American Academy of Actuaries (MAAA).

Trenton Lipka is an associate actuary with Pinnacle Actuarial Resources in the Bloomington, Illinois, office. He holds a Bachelor of Science degree in actuarial science from Illinois State University. He has experience in assignments involving loss reserving, group captives and large data processing. Trenton is an Associate of the Casualty Actuarial Society (ACAS) and a Member of the American Academy of Actuaries (MAAA).