Excel Tips for Loss Runs - Filters, Pivot Tables, and Formulas

Alex Leaf, Actuarial Analyst

Excel is often seen as a foundational tool for anyone wishing to navigate and organize large sets of data, but getting acclimated to working in Excel can feel like a daunting task. Thankfully, there are numerous easy-to-learn tools such as filters, pivot tables, and cell formulas which can make the learning process much easier.

Using Filters in Loss Runs

One of the most common data sets used in the insurance industry is the loss run, which may be provided in Excel format.  If, for example, one is tasked with identifying a claim or group of claims within a loss run, one of the most effective methods of doing so is to make use of the Filter tool. In order to do so, one must first highlight the cells desired to be filtered.  From there, they’ll find and select the “Data” tab at the top of the screen.  Within the “Data” tab, they’ll select the “Filter” option, as shown in the image above.
By clicking on the arrow next to any column header, the loss run can be sorted based on the data presented in that column. For example, a column containing incurred losses by claim can be sorted by highest to lowest value, lowest to highest value, all claims above or below a certain value, and much more. Another example pertains to the loss or occurrence date field. Dates can be filtered based on oldest to newest, dates within in a certain month, specific days, and other options. Multiple filters can even be used at once.

Summarizing Loss Data Using a Pivot Table

Pivot tables can be used to summarize and reorganize large data sets.  In this example, let’s assume we’ve been tasked with summarizing a large set of claim data by policy period. To create a pivot table in an Excel-based loss run, highlight all relevant data, including column headers, then navigate to the “Insert” tab at the top of the screen and select the “PivotTable” option.

A dialog box should appear on the screen making sure the data range is correct. Make sure the dotted line is capturing all of the desired loss data. Then, select “OK” in the small pop-up box. Excel should direct you to a new tab with a “PivotTable Fields” section to the right of the screen. Now, the loss data can be organized as needed. In this example, we would want to drag the column heading corresponding to the policy period in the loss run from the top of the “PivotTable Fields” section into the “Rows” area at the bottom of the “PivotTable Fields” section.
Next, drag the field corresponding to incurred loss amounts into the area labeled “Values” near the bottom of the “PivotTable Fields” section. Now, the total amount of incurred losses by policy period is summarized in an easy-to-read table. Multiple fields can be placed in the “Values” section to add additional columns to the pivot table containing those summarized data points. For example, incurred losses, paid losses, and outstanding reserves can be compared for each policy period by dragging each field into the “Values” area together. Of course, this example is only scratching the surface, as pivot tables have many more applications.

Inserting Rows, Columns, and Formulas into a Loss Run

Sometimes, it’s necessary to add additional rows or columns to an Excel-based loss run.  To insert a column, right-click the column to the right of where the column should be inserted. Then, from the popup menu, select “Insert.” A blank column will then be inserted to the left of the column selected. A similar process is used to insert a row. Right-click the row number below where a row is needed to be inserted, then select “Insert.” A row will be inserted above the row selected. To insert multiple columns or rows, simply highlight multiple columns or rows, then right-click and select “Insert.”

Columns or rows should be inserted corresponding to the number of columns or rows highlighted.
Adding a Formula to a Loss Run

When working in an Excel-based loss run, formulas can be added to automate certain calculations or add further value to the data presented. To create a formula in a cell, select the cell where the formula will be implemented, select the Formula Bar located just beneath the Toolbars, and type in the “=” sign. This will signal to Excel that a formula will be used. From there, any formula can be inserted. It can even be as simple as “=2+2” (which will output 4). An important application of formulas in Excel is to apply computation between multiple cells. For example, if you insert a column to calculate outstanding reserves, select a cell and type in the “=” sign. Next, select the cell corresponding to “incurred losses” for that claim. Then, press the “-“ key and select the cell corresponding to “paid losses” for that claim. After pressing “Enter,” Excel should subtract the values in these two cells and output the difference.

Instead of creating the formula for every row, select and drag the box in the bottom right corner of the cell to apply the formula for every row above or beneath your selection.  To delete a column, right-click the column and select “Delete.” Select multiple columns at once to delete multiple columns.These tools are just a few of many that Excel has to offer to organize data. Hopefully, these tips will help the next time you want to sift through a loss run or other data sources in Excel.

Looking for more simple excel solutions when working with loss runs? Register for access to our complimentary RISK66 Education License today! There, you can watch our recent Lunch and Learn webinar recording that highlights tips and tricks that anyone can use to create useful analytics from an Excel loss run.

We welcome your feedback by posting a comment or contacting us at support@SIGMAactuary.com.

Leave a Reply

Your email address will not be published. Required fields are marked *

Archives

Recent Posts

A Practical Guide to Navigating Fairness in Insurance Pricing
Rich Moncher recently co-authored a research paper with Jessica Leong and Kate Jordan, and it was published by the Casualty Actuarial Society. Each paper in this research series addresses a different aspect of race and insurance pricing as viewed through the lens of property and casualty insurance, ...
Read More
When Captives Require Multiple Actuaries: Key Considerations
In a recent article published by Captive International, Jason Luckett and Ben Brandon of Risk Strategies Consulting look at what happens when a captive sees the need to employ another actuary. Captive insurance companies can be a great tool, as they are uniquely capable of meeting the specific insu...
Read More
Navigating the Impact of Claims-made and Occurrence Policies on Loss Development
When analyzing insurance-related data, actuaries must always be cognizant of the nuances within the underlying policies, particularly when it comes to their impact on Loss Development Factors (LDFs). The complexities they present may grow more pronounced when the policy type changes over time or has...
Read More
Assessing Financial Strategies for Cyber Risk
The assessment tools available in RISK66 are an integral part of our platform, and we would like to thank those who have shared positive feedback over the years regarding their usefulness. Because of our desire to continue providing exceptional support to our RISK66 users, SIGMA is excited to a...
Read More

Subscribe to Our Blog



hello world!
Copyright © 2023 – 2024 SIGMA Actuarial Consulting Group, Inc. All Rights Reserved.
chevron-down linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram