Health Plan Claims Analytics: Cost per Member per Month, Utilisation per 1,000, High-Cost Claimants, Trend & Benefit Design Effects

What you'll build

What you will be able to do

Each lab ends with a concrete result that you build yourself and the checker confirms, cell by cell.

• Count the months each member was covered in 2026 from enrolment dates with YEAR and MONTH, clip enrolments that start before or end after the year, total the plan's member months and count the members on the books at year end.
• Total six months of paid claims by service category with SUMIFS, divide them by the member months of the same period to get PMPM by category and for the whole plan, and calculate PMPM month by month.
• Convert service counts into utilisation per 1,000 members per year, calculate the unit cost of each category, prove that utilisation × unit cost gives back the PMPM and name the most expensive category per service.
• Total each anonymised member's cost from claim lines with SUMIFS, measure how much of the group's cost the top 10 % of members drive, count members above a fictional high-cost level and chart the members from most to least expensive.
• Calculate the plan's PMPM trend from 2025 to 2026, split one category's trend into a utilisation trend and a unit-cost trend, and find the category that added the most PMPM.
• Count how many months each service month has had to develop, look up a fictional completion factor, estimate what each month will finally cost and total the claims that are incurred but not yet paid.
• Split sample claims between member and plan under a higher co-pay and under a fictional deductible design with coinsurance and an out-of-pocket maximum, and measure what each change does to the plan's cost.
• Compare actual PMPM with a fictional budget per category and for the whole plan, flag the categories above budget with a formula rule and chart actual against budget PMPM.