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
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.
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.”
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.
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.