Large Claim Chart #1 - Background

In the coming days (or weeks, my Gamefly account is dominating a zero-sum game with my blog) I'm going to be posting my large claim chart.  It gets a little health insurance-y, so I thought I'd explain a few things before I begin.

For the zero people who read this blog...

Anyway, I'm an employee benefits consultant and analyst working on mostly self insured groups.  What self insured means in Washington State is that the group pays all of their claims up to a certain point, either on an individual or aggregate basis.  After that, the group's stop loss insurance will pay the rest of the claim(s).  For the purpose of the large claim chart, we're interested in individual stop loss insurance.

Individual stop loss insurance is nearly every self insured group's largest fixed cost.  Let's say a group has a specific deductible of $200,000.  Some jerk on the plan gets mauled by a basilisk and goes in for a $500,000 operation to save his life.  Why does the plan care?  Not only do they have to pay the $200,000 to meet the specific deductible, but their insurance company isn't going to be happy about eating a $300,00 stop loss claim and they'll get that money back with interest when they set premiums at the next renewal (medical plans renew annually). 

The insurance carrier knows they can charge anything they want next year because no one will touch a group that gets mauled by basilisks without lasering the large claimants.  A laser is a separate deductible for high risk people.  For this group, they'll pay all claims for every member up to $200,000 per member, but the high risk claimant with the laser will have a separate deductible, let's say $1,000,000, that the plan will have to pay before insurance kicks in.  No one likes lasers, they are risky and they destroy moral.

"Oh look, it's Xerxes from accounting.  I heard he has a $1,000,000 laser and that's why our medical plan is so expensive.  Let's put a basilisk in his office."

Because sick people never, ever get better, it's important for groups to be aware of their past large claims.  It helps them pick the right specific deductible level for their group, set a budget, and it's also a lot of fun to look at the de-identified large claim data and try to guess who it is.

When I started working at my office, we didn't have a chart for the large claim history, only a data table.  I'm a visual person, or at least I convinced myself that I was after reading a few Edward Tufte books, so I decided to create a large claim chart that would tell the story of their large claims visually.

Next post, I'll show the evolution of the chart. After that, I'll briefly explain how I made the chart in Excel and then you can laugh about how inelegant my solution is and tell me how I could have done it ten times faster.

-David