How to Analyze Claim Severity Across Your Agency’s Book

How to Calculate Claim Severity by Month in Excel

This tutorial walks you through how to measure claim severity across your agency’s book of business, determine how many claims exceed a certain threshold, and visualize the results dynamically. By adding a dropdown selector and formulas that update automatically, you’ll be able to analyze severe vs. non-severe claims for any given month.

1. Set Up the Month Labels

• In a new column, type January in the first cell.
• Drag down to December to fill in all months automatically.
  
• This provides the foundation for month-by-month analysis.
  

2. Create a Dropdown for Claim Severity Threshold

• Go to Data → Data Validation → Data Validation.
  
• Under Allow, select List.
  
• In the Source field, type or reference your desired thresholds:
  100, 250, 500, 1000, 2500, 5000
  
• Press Enter.
  
• You now have a dropdown list for setting the claim severity level.
  This allows the sheet to dynamically update all calculations based on the selected threshold.
  

3. Build the COUNTIFS Formula for Severe Claims

Use the COUNTIFS() function to count claims that meet all three conditions:

1. The Has Claim column equals “Yes.”
   
2. The Total Paid amount is greater than or equal to the selected threshold.
   
3. The Month equals the current row’s month.
   

Example:

=COUNTIFS(HasClaimRange, “Yes”, TotalPaidRange, “>=” & SelectedThreshold, MonthRange, MonthCell)

• If the “Yes” text appears often, consider referencing a single cell (e.g., A1 = “Yes”) instead of typing it into every formula.
  
• Lock the cell for your threshold (use F4) so it doesn’t shift when copying formulas down.
  
• Format results as numbers and center-align for readability.
  

4. Calculate Total Claims

• Use another COUNTIFS() formula, this time ignoring the threshold:

=COUNTIFS(HasClaimRange, “Yes”, MonthRange, MonthCell)

• This returns the total count of all claims per month.

5. Calculate Non-Severe Claims

• Subtract the severe claims from total claims:

=TotalClaims – SevereClaims

• This provides the number of smaller (non-severe) claims each month.

6. Calculate the Severity Percentage

• Divide severe claims by total claims:

=SevereClaims ÷ TotalClaims

• Format as a percentage using Ctrl + Shift + 5.
• This shows the proportion of claims above the chosen threshold each month.

7. Build a Stacked Column + Line Combo Chart

1. Highlight your data for Month, Severe Claims, Non-Severe Claims, and Severity %.
   
2. Go to Insert → Recommended Charts → Combo Chart.
   
3. Select Stacked Column for the claim counts.
   
4. Assign the Severity % series to the Secondary Axis.
   

You’ll get:

• Blue and orange bars showing severe vs. non-severe counts.
  
• A green line showing claim severity percentage.
  

8. Format and Test the Dashboard

• Title the chart: Claim Severity by Month.
  
• Adjust colors for consistency (for example, dark theme or grayscale).
  
• Add data labels if desired.
  
• Test your dropdown, as you change thresholds (e.g., 100 → 5000), the entire chart updates instantly.
  

9. Interpret the Results

• High thresholds (e.g., $5,000): smaller percentage, fewer claims.
  
• Low thresholds (e.g., $100): larger percentage, nearly all claims counted as severe.
  
• This helps you understand risk exposure, renewal challenges, and carrier negotiation opportunities.
  

10. Optional Enhancements

• Add conditional formatting to flag months with high severity.
  
• Create a summary cell that shows the average severity across all months.
  
• Integrate this into your larger performance dashboard.
  

Result

You now have a dynamic Excel model that calculates claim severity by month, allows for real-time threshold adjustments, and instantly visualizes your data. This tool helps insurance agencies track loss patterns, identify emerging risk trends, and prepare more confidently for renewal conversations with carriers.