Title example How to Create a KPI Dashboard in Excel

Learn step-by-step how to visualize key metrics and track performance in your department.

Download the Excel file used in this tutorial:

Step By Step Instructions

How to Calculate Loss Ratio % by Insurance Product Line in Excel

This walkthrough explains how to calculate and visualize Loss Ratio Percentage by product line using Excel.

Loss Ratio compares total claim cost to written premium and helps you evaluate product performance,
profitability, and carrier relationships.

1. Understand the Loss Ratio Formula

The dataset includes a column for Loss Ratio, defined as:

Loss Ratio = (Claims Paid + Loss Adjustment Expense) ÷ Written Premium

Averaging individual loss ratios can produce inaccurate results. Instead, calculate:

Sum of Total Claim Cost ÷ Sum of Written Premium

This approach accounts for different premium volumes and produces accurate results.

2. Generate a Unique List of Product Lines

Select the Product Line column.
Use the UNIQUE function to remove duplicates.
If a zero appears from blank rows, add a period inside the formula to remove it while keeping the list dynamic.
For static values, copy and paste using Ctrl + Shift + V.

This gives you a clean list of product lines to analyze.

3. Set Up Columns for Claims Paid and Written Premium

Create two supporting columns:

Total Claim Cost (Claims Paid + Loss Adjustment Expense)
Written Premium

Use exact header names from your dataset to ensure formulas work correctly.

4. Use SUMIFS to Calculate Total Claims and Premium

Total Claim Cost by Product Line

Use the SUMIFS function to sum Total Claim Cost for each product line.

After entering the first formula:

Double click the fill handle to autofill.
Format as currency using Ctrl + Shift + 4.
Center values using Alt + H + A + C.

Written Premium by Product Line

Copy the SUMIFS formula from Total Claim Cost, change the range to the Written Premium column, and autofill. Apply the same formatting.

5. Calculate Loss Ratio Percentage

The formula for Loss Ratio Percentage is:

Loss Ratio % = Total Claim Cost ÷ Written Premium

Format the results using:

Ctrl + Shift + 5 for percentage formatting.
Adjusting decimal places as needed.

For a cleaner model, embed the SUMIFS formulas directly into the Loss Ratio calculation.

6. Build a Bar Chart to Visualize Loss Ratios

Highlight the Product Line and Loss Ratio columns.
Select Insert and choose a recommended Clustered Bar Chart.

Sorting the Chart

Convert formulas to values.
Sort smallest to largest to place higher ratios at the top.

Add Data Labels

Enable Data Labels.
Set labels to Percentage with zero decimals.

7. Optional: Analyze Loss Ratio by Carrier

You can repeat this process using Carrier instead of Product Line to identify carrier-level profitability patterns and underwriting concerns.

8. Download the Excel File or Request Custom Content

To download the Excel file used in this tutorial or request custom KPI walkthroughs, submit the form on this page or email ryan@databoards.io.

FAQ

How to Calculate Average Commission Rates by Carrier and Product Line in Excel

This walkthrough explains how to calculate dynamic average commission rates in Excel — segmented by carrier and product line — and how to build dropdowns that automatically update your charts and calculations.

1. Create a List of Carriers

Use the UNIQUE() function on your dataset’s Carrier column (for example, Column J).
Copy the results and paste them as values with Ctrl + Shift + V.
Transpose the list horizontally (Paste → Transpose).
Optionally sort alphabetically for cleaner visuals.

2. Create the Month Labels

Type January in a cell and drag down to December to quickly populate your months.

3. Build the Product Line Dropdown

Go to Data → Data Validation → List.
Instead of typing each product manually, generate a comma-separated list dynamically:
- Use UNIQUE() on the Product Line column.
- Copy and paste values.
- Apply the TEXTJOIN() function with a comma as a delimiter to combine them into one cell.
Copy that result and paste it into the data-validation source.
Now your dropdown updates dynamically whenever new product lines are added.

4. Calculate Average Commission Rate

You’ll use the SUMIFS() function to sum commissions and premiums by carrier, product line, and month.

Sum the total commissions for each carrier/month/product combination.
Sum the written premium for the same combination.
Divide the commission total by the premium total.
Format as a percentage with Ctrl + Shift + 5.

Use F4 to lock cell references correctly:

Lock rows or columns depending on the direction you plan to drag your formulas.
This ensures your formulas stay accurate when copied across months and carriers.

To avoid errors, wrap your formulas in IFERROR() so blanks appear instead of “#DIV/0!”.

5. Add Conditional Formatting

Highlight your table and apply a color scale to visually flag high or low commission rates — turning your matrix into a quick-read heat map.

6. Build Dynamic Charts

Select the completed matrix.
Insert a Line Chart or Clustered Column Chart.
Set up your chart titles and legends to update based on dropdown selections.
Handle blank or zero values by using the chart setting:
Select Data → Hidden & Empty Cells → Show Empty Cells as Gaps.

This ensures your chart displays smooth data without artificial valleys or spikes.

7. Add a Second Dropdown to Filter the Chart

Create another dropdown for selecting a single carrier (using TEXTJOIN() again if needed).
Link this selection to your chart’s data range.
As you change the carrier, the chart instantly updates — giving you a fully dynamic dashboard that compares commissions over time.

8. Optional Enhancements

Add slicers or form controls for more interactivity.
Use named ranges for cleaner formulas.
Incorporate conditional charts for different product categories.

Result

You now have a live-updating Excel dashboard showing average commission rates by carrier and product line, complete with dropdowns and dynamic charts — no manual updates required.

Get more lessons like this
by joining our newsletter

Receive weekly Excel tutorials, tips, and KPIs straight to your inbox

Databoards

Analysis & Development