Is a bell curve the wrong shape for your claims?
Paste your closed claim sizes in one column and get back which distribution (the shape of how often each size turns up) fits them best. Thirty-six sample claims ranked against every shape the fitter will offer. Lognormal wins by a distance, and the fitted mean of $38,019 sits nearly three times above the median claim, which is what a severity curve is supposed to look like.
Words on this sheet
- Standard deviation: How far a typical reading sits from the average, in the same units as the readings.
Legal Intermediate Statistics free
After you install, this is the model to open.
What Distribution Do Claim Sizes Follow?
- 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
- Best fit
- Lognormal KS distance 0.068 on 36 settled claims
- The bell curve
- 4x worse normal fits at KS 0.291, and it even allows negative claims
- Carry into Risk Analysis
- mean $38,019 sd $85,197, typed straight into an input
Thirty-six closed claims, smallest to largest. Click Run: the fitter ranks five shapes by how far each strays from your data, and lognormal wins at 0.0679 with a fitted mean of $38,019 and a standard deviation of $85,197. Exponential is second at 0.2366, normal third at 0.2909, and triangular and uniform are hopeless at 0.5443 and 0.6610.
The size of that gap is the result. Claim severity is not a bell curve and it is not a range with a most likely value in the middle; it is a shape whose mean sits far above its middle, and here the fitted mean of $38,019 is nearly three times the median claim of $13,050, because the largest four files carry more than the other thirty-two put together.
Notice that the report offers no PERT row at all. PERT needs a most likely value strictly inside the range, and data this skewed pushes the moment-based mode down onto the minimum, so the fitter declines rather than inventing one. That is worth knowing before you go looking for three-point estimates in a book that has no middle. Copy the winning shape and its two parameters into the three empty cells under Carry into a severity model and you have the severity half of a frequency-severity model: Risk Analysis takes lognormal directly as an input distribution, and the frequency half is a separate count you already have.
Then rerun on the Injury files alone and on the Property files alone, because heads of claim usually have genuinely different shapes and averaging them into one curve is how a reserve ends up wrong for both. Read the Months to settle column while you are there: the tail is also the slow half of the book, so the files that decide the year are the ones still open when it closes.
What the fit cannot tell you is how many claims arrive next year. To use your own book, paste settled amounts under the Settled amount heading and widen the input range.
The model
It arrives on a tab called Template: What Shape Are Claim Sizes, carrying these columns:
- Claim ref
- Settled amount ($)
- Head of claim
- Months to settle
- Carry into a severity model
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
- Two experts, one schedule. Where is the settlement zone?Delay Claim: Who Owns the Days?
- Neither side wants to blink. What happens?Deadline Standoff: Hold Firm or Concede?
- Should you take the settlement or go to trial?Settle or Go to Trial?
- How much should we reserve for this lawsuit?What Could the Lawsuit Cost Us?
- What range can a lost profits opinion actually support?How Much Are the Lost Profits Worth?
- Whose cost to complete should you believe?Is the Cost to Complete Claim Credible?
Every model like this one, and the method behind them: Statistics in Google Sheets.