Why is your average claim the wrong number?
Thirty settled files, five cost bands, and one number that decides the year. Half the claims close under ten thousand dollars, and a single file is worth more than the twenty cheapest put together.
Legal Starter Statistics free
After you install, this is the model to open.
What Do Claims Actually Cost?
- In your spreadsheet, click the Sortia icon in the strip of icons down the right-hand edge. No strip? Click the arrow at the bottom-right to open it. You can also use Extensions, then Sortia, then Open Sortia.
- Click Start from a template and put that name in the search box.
- Pick the card with that name and click Load this template. It arrives on a new tab with real numbers already in it.
The answer
Where the files land, and where the money goes:
- Under $10,000
- 50% of the files
- Under $80,000
- 96.7% of the files
- The one in "More"
- 23.4% of everything paid out
- The twenty cheapest
- 25.1% of everything paid out
The arithmetic that matters is not the average claim of $20,247 and it is not the median of $10,200. It is that one file in thirty decides roughly a quarter of the annual cost, so a reserve set from the average is wrong in both directions at once: too high for the ordinary file and far too low for the one that matters. Move the top band down to $40,000 and four claims land in "More", which is a useful reminder that where you put the last band is a decision about what you are calling a large loss, and it should match the definition your reinsurance already uses.
The model
One row per settled claim with the amount and each claim as a share of the year, plus five band limits. The Histogram tool counts the bands and returns the cumulative column beside them.
| Claims | 30, closed this year |
| Settled amounts | $2,400 to $142,000 |
| Bands | $5K, $10K, $20K, $40K, $80K |
| Cumulative percentage | calculated |
| Share of the year each claim took | calculated |
Once it is in your sheet
- The model arrives with real numbers in it and runs as it stands, so you can press the button first and understand it second.
- Change the numbers to yours. The sheet marks which cells are inputs and which hold formulas, and most labels carry a note explaining the row.
- Press the run button at the bottom of the panel. It is labeled for the tool you are in, and the result lands on its own tab, with a written reading of it beside the figures.
Never used Google Sheets? Start here goes the whole way, in seven steps, and assumes nothing.
Next question
- Is intake growing, or does it just feel busy?How Many Matters Are Coming?
- The venue moves the odds. By how much?Does the Court Change the Settlement Rate?
- A legal matter risk register pricing five case risks against mitigation cost, showing which of the five mitigations lose money in expectation. Free template.What Could Go Wrong in This Matter?
- Which contingency matters actually lose you money?Will the Recovery Cover the Costs?
- What reserve holds nineteen years out of twenty?How Much Should We Reserve Per Matter?
- Is a bell curve the wrong shape for your claims?What Distribution Do Claim Sizes Follow?
Every model like this one, and the method behind them: Statistics in Google Sheets.