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.
One of SIGMA’s core objectives as an actuarial consulting firm is to reduce the perceived gap standing between insurance professionals and their ability to utilize actuarial analytics in their day-to-day work. All too often, this obstacle stems from of a lack of time, resources, or comfort in usin...
In a recent article published by Captive International, Michelle Bradley and Jason Luckett discuss, “Optimizing Cyber Risk Management: Key Captive Considerations”. In it, they examine the parameters of cyber risk, and how it changes constantly as hackers adapt and expand their avenues of threat....
The keys to a reliable actuarial analysis are good data and reasonable underlying assumptions for the program being analyzed. Therefore, any information regarding the insurance program which could materially affect the analysis should be provided to your actuary. The two most common types of act...
In 2023, SIGMA conducted a collateral survey to assess, on a national basis, trends in collateral negotiations, exposures, reviews, arbitration, and many other factors. Since 2009, SIGMA has conducted this survey eight times which has allowed us to better understand both short-term and long-term tre...