Health Plan Claims and Reserves

• Claims reserving (IBNR)

  build the paid triangle, estimate what is unpaid at the cut, and score the estimate against incurred_truth, split into claims not yet received, claims in process and appeals still to come.

• Medical economics

  allowed and paid cost per member month by product, age, condition and risk score; medical loss ratio as reported at the cut and on the final cost; the costliest members' share of spend.

• Payment integrity and denials

  denial rate and causes in count and in dollars, CARC codes, claims an edit should have stopped and paid anyway, appeal overturn rates by cause.

• Claims operations

  lag from service to receipt by claim type, auto-adjudication, days to decide, the queue at year end.

• Benefit design

  how deductibles, copays, coinsurance and the out-of-pocket maximum split the cost between the plan and its members, product by product.

• Teaching and demos

  an actuarial or health economics course, a payer analytics portfolio, a dashboard or a claims system demo, with no PHI anywhere.

• incurred_truth.unpaid_at_cutWhat each month of 2025 still owed at the cut, once every late claim and appeal is counted: the reserve's answer.0 to 1.78M, mean 193,387
• incurred_truth.not_received_at_cutThe part of it on claims that had not reached the plan by the cut, the IBNR proper.0 to 1.44M, mean 130,562
• claims.cause_no_prior_authOne of nine cause_* flags: every problem an edit could catch on the claim, denied or not.true on 0.8% of rows
• claims.denial_causeThe edit that denied the claim, behind its CARC code.9 values: out of network, other coverage primary, duplicate claim…, 91.7% empty
• members.risk_scoreThe member's expected cost against the plan's average member, which drives how many claims they make.0.046 to 28.92, mean 1.5
• members.chronic_conditionThe condition behind the risk score.7 values: none, diabetes, COPD or asthma…
• appeals.outcomeWhether a denial held up on appeal.2 values: upheld, overturned, 7.5% empty

• Premium earned $90.8 million. Plan-wide the medical loss ratio reads 75% on what had been paid by the cut and 85% once every 2025 claim is in. The gap is $9.3 million still owed at the cut, 68% of it on claims not yet received.
• November was 63% paid at the cut and December 16%: most of a month's cost arrives after it closes.
• Cost follows risk: the costliest 5% of members carry 47% of allowed spend, about as the Medical Expenditure Panel Survey finds for the US population.
• 8.4% of decided claims were denied; 85% were decided without review. Professional claims arrive in a median 10 days, institutional ones in 21. 42% of decided appeals were overturned.
• About 1,700 claims were received but not yet decided at the cut.

• every key resolves and every primary key is unique; one member month for every month a member was covered, none outside it; every claim's member, product and month are its member month's, and its provider suits its service;
• premium recomputed from each product's rate card: commercial by age band and never by health, Medicare Advantage by risk score, Medicaid by rate cell;
• every edit recomputed from the rows it comes from (the filing limit, the network and the product's out-of-network benefit, the referral and authorization rules); every denial's cause is flagged on its claim and its CARC code matches it;
• allowed recomputed from the provider's charges, the product's fee level and the provider's contract; the deductible and the out-of-pocket maximum rebuilt in the order claims were decided, and no member pays past either; paid is allowed less the member's share;
• nothing is dated after the cut, a claim in process shows no decision, appeals follow their denial; the incurred truth's member months and premium equal the member months table, and what it says was paid by the cut equals the files.

• claimsEvery claim received by the cut: service, provider, network, lag to receipt, referral and authorization, every cause an edit could catch, review, the decision with CARC code, allowed, deductible, copay, coinsurance, the out-of-pocket cap, paid140,659 rows · 45 cols
• member_monthsOne row for every month a member was covered, with the premium it earned (age-rated, risk-paid or by rate cell)128,688 rows · 8 cols
• membersAge, sex, region, product, chronic condition, risk score, other coverage, attributed PCP, enrollment dates and why coverage ended12,000 rows · 15 cols
• appealsAppeals of denied claims filed by the cut: who filed, expedited or not, decision, outcome and what an overturn paid2,052 rows · 14 cols
• providersPrimary care, specialists, hospitals, surgery centers, labs, imaging, behavioral health and therapy: network status, contract rate against the plan's standard rates, charge markup600 rows · 9 cols
• incurred_truthThe hindsight table: each 2025 month of service by product, its member months and premium, what it finally cost (claims still to arrive and appeals still to be decided included) and what had been paid by the cut48 rows · 13 cols
• productsThe four products (commercial HMO and PPO, Medicare Advantage, Medicaid managed care): fee level, deductible, out-of-pocket maximum, coinsurance, copays, referral rule, out-of-network benefit and rate, timely-filing limit4 rows · 16 cols

Want it bigger, in another setting, or with your own columns? Have us build it.