Health Plan Claims Analytics: Cost per Member per Month, Utilisation per 1,000, High-Cost Claimants, Trend & Benefit Design Effects
Analyse a fictional health plan's claims like a healthcare analyst: member months, cost per member per month, visits per 1,000, the few members who drive most cost, cost trend and what a benefit change might do, with every cell checked as you go.
One-time payment · lifetime access · lessons, labs, exam and certificate included · no subscription.
About this course
This course analyses the claims of a fictional health plan in a spreadsheet, using anonymised member ids and service categories only. You count member months from enrolment dates with YEAR and MONTH, total paid claims by category with SUMIFS and divide them into PMPM, split PMPM into utilisation per 1,000 and unit cost, measure the share of cost caused by the top members with LARGE and chart it Pareto-style, separate utilisation and unit-cost trend, estimate claims still to be paid with completion factors and XLOOKUP, test co-pay and deductible designs with MIN and IF, and finish with a budget-vs-actual dashboard.
Lessons
- Member Months: Enrolment Dates, Months Covered per Member & the Plan's Exposure Pro
- Cost per Member per Month: Paid Claims by Category & by Month Pro
- Utilisation per 1,000 & Unit Cost: Splitting PMPM into How Often and How Much Pro
- High-Cost Claimants: Cost per Member, Top-Member Share & a Pareto Chart Pro
- Cost Trend: PMPM Year over Year, Utilisation Trend vs Unit-Cost Trend Pro
- Paid vs Incurred: Months of Development, Completion Factors & Claims Still to Come Pro
- Benefit Design Effects: Co-Pays, Deductibles, Out-of-Pocket Maximums & Plan PMPM Pro
- Plan Dashboard: Actual vs Budget PMPM, Traffic Lights & a Category Chart Pro