How to reconcile health plan claims funding, bank activity, and the ledger

Reconcile three records separately before closing a plan period. Match plan sponsor funding to expected funding requests, match disbursements to claims payment batches, and tie both to bank and general ledger totals. Keep unmatched deposits, returned payments, and unposted entries in a review queue until an authorized owner resolves them.

Separate sponsor funding from provider payments

Sponsor funding references come from the administrator's own billing and bank arrangements. Where an applicable provider payment uses health care EFT and electronic remittance advice, CMS explains how the matching trace number in the EFT addenda and X12 835 helps reassociate payment and advice. Keep that trace matching separate from sponsor funding and from claim adjudication. Provider-level adjustments may affect a payment total without belonging to a specific claim. CMS EFT and ERA reassociation · CMS remittance advice

Reconcile from source transactions to plan close

What to automate

Stage bank and administrator records

At the plan-close cutoff, load expected funding requests, claim batch controls, bank movements and ledger entries. Record source versions, totals, plan and settlement date, with the original evidence retained under approved access. Alert Claims Finance to missing or late files and keep the affected close in draft until the required controls can be checked.

Match sponsor funding

Tie bank credits to sponsor requests using approved references, plan IDs and amount rules. Allow a split only when it is documented and the sum agrees with the bank movement. Keep ambiguous deposits in a held list with a Treasury owner. Preserve the allocation evidence so another reviewer can reproduce the attributed plan funding.

Match claim disbursements

Use the accepted payment-batch and bank trace references to match settlements and outstanding payments. Keep provider-level adjustments in the proper batch control and attach voids and returns as linked events on their actual dates. Flag a missing or reused reference for Claims Operations. Report original, settled and reversed amounts separately in the reconciliation detail.

Tie the cash bridge

For each plan and account, add matched sponsor funding to opening cash and apply settled disbursements, returns and fees with signed decimal amounts. Tie the result to the bank and ledger views using documented timing items. Give Accounting a break for any residual above tolerance and report unmatched deposits separately from attributed plan cash.

Resolve and sign off

Assemble the cash bridge, unresolved breaks by age, evidence links and proposed correction details for controller review. Have AI draft a summary from redacted exception counts and approved labels. Retain sign-off on the exact revision and require the authorized accounting owner to approve any posting or allocation before the package is released.

Test an unmatched deposit and a returned payment

In a synthetic close, opening cash is $100,000, matched funding is $40,000, settled claims are $25,000, and fees paid are $5,000. Expected closing plan cash is $110,000. A $2,000 bank deposit with no plan reference creates a separate $2,000 held item and leaves attributed plan cash unchanged. If a $1,000 claim payment is later returned, retain the original settlement and record the linked reversal on its actual date. Replaying the same bank file should leave the balance unchanged. Measure unmatched amounts, time to close, aged exceptions, and reconciling items without an owner.

Implement a batch-to-bank claims funding control

Create funding_batch(group_id, account_id, batch_id, revision, due_date, requested_minor, status) plus payment_line(batch_id, source_payment_id, revision, signed_minor, payment_type) and bank_movement(account_id, trace_id, value_date, signed_minor, source_revision). Keep protected claims data in controlled storage and use only approved evidence references in the reconciliation queue.

1. Confirm group/account scope and validate the claims administrator’s manifest. Accept positive payments and reversals under explicit signed amount rules. Keep an amendment distinct from a duplicate batch.
2. Sum expected payments and identify exclusions before matching bank movement by trace, amount, account and value date. Support several bank legs per batch with a controlled mapping. Never apply a partial match to the next group’s payment solely because the amount is similar.
3. Prepare a disbursement reconciliation and exception list. Match sponsor deposits separately against the approved funding request by group, account, amount and value date. The reconciliation records evidence and status for the authorized operator.
4. Reconcile the confirmed payment and verified bank movement to the ledger control. Keep unallocated bank movements, amount differences and reversals separately visible. Route missing approval, stale evidence and possible duplicate traces before closing the period.

def payment_difference(payment_lines, bank_legs):
    if len({trace for trace, _ in bank_legs}) != len(bank_legs):
        raise ValueError("duplicate bank trace")
    return sum(payment_lines) - sum(value for _, value in bank_legs)

assert payment_difference([75_000, 35_000, -10_000], [("T1", 100_000)]) == 0
assert payment_difference([75_000, 35_000, -10_000], [("T1", 75_000)]) == 25_000

The fixture uses one currency and integer minor units. The second case remains open with an assigned owner until the missing leg or approved explanation is verified.

Implement the work step by step

The plan finance controller decides whether the controller can approve plan cash and carry forward named exceptions without assigning uncertain bank activity to the wrong plan. The workflow starts with disbursement, funding, bank and GL cutoffs. Ends with a signed plan cash bridge and owned exceptions. Excludes claims adjudication, reimbursement decisions and movement of funds.

Stage and verify

• Capture and validate bank and claims feeds. Read scoped source fields and verify bank, plan, count, signed amount and cutoff. Read TPA paid register, bank, EFT/ERA when applicable, approved extract schedule. Record Versioned manifests and accepted records to finance. Owner Claims finance analyst. Correct missing or erroneous source record. Check Hold complete status when required source missing.
• Capture expected funding and GL balances. Join plan and account by effective date, preserve period basis and ledger version. Read Sponsor billing, expected funding and plan ledger. Record Expected funding and as-of GL controls to reconciler. Owner Accounting analyst. Confirm reopened ledger or failed extract. Check No cross-plan pooling without approved accounting rule.

Match and explain

• Match sponsor deposits to requests. Use approved funding reference mapping and cents checks. Model allowed split allocations. AI can suggest candidate reference from redacted descriptions. Read Bank deposits, sponsor invoices, plan IDs. Record Matched funding and held items to accounting. Owner Treasury ops. Approve uncertain allocation or retain hold. Check No deposit silently mapped to an unrelated plan.
• Reassociate claim payments and adjustments. Compare identifiers where present and permitted. Link reversals to settled entries. Read Payment register, bank transactions, applicable EFT/835 trace. Record Paid and outstanding batches to accounting. Owner Claims finance analyst. Approve dispositions for ambiguous batches. Check Provider-level adjustment remains separate from claim-specific entries.
• Investigate unmatched or returned cash. Track exception by plan, event and period. Retain pending and resolved versions. AI can summarize redacted issue details. Read Aged queue, source records and settlement trace. Record Disposition or aged item to controller. Owner Treasury ops. Authorize allocation or coordinate bank/TPA correction. Check No auto-post based on AI proposal. Time waiting measured separately.

Close and verify

• Build plan cash bridge and tie GL. Calculate opening plus approved movements and compare with bank and ledger controls. AI can explain aggregate differences from source rows. Read Opening cash, bank activity, claims payments, fees and GL. Record Reconciliation with explicit residuals to controller. Owner Accounting analyst. Approve timing classification and any journal correction. Check Post only under approved ledger role. Tolerance applied by plan and currency.
• Approve final reconciliation and carry open issues. Freeze revision and route unresolved lines by age and threshold. Read Close package, exception register and release policy. Record Signed reconciliation and owner list. Owner Plan finance controller. Signs off, retains authority over adjustments. Check Final package contains every open reconciling item and source pointer.

Parallel run. Shadow two close cycles and verify a returned EFT, a missing funding reference, a provider adjustment, and a stale GL extract. No unauthorized balance, adjustment or posting.

Build it yourself with AI

Use synthetic funding.csv, batches.csv, bank.csv and ledger.csv for one plan. Write the matching and close rules in reconciliation_policy.md. Give the coding assistant fictional references and plan-level totals only, and ask for a local command that produces cash_bridge.csv and an aged breaks file.

1. Define matching and evidence

   Draft separate decision tables for sponsor deposits and payment batch settlements. Use the approved reference, amount and date rules. Show how to hold an ambiguous deposit, a missing file, a split without approval and a payment reversal. Record the owner and evidence each case requires.

2. Build and test the cash bridge

   Write a Python reconciliation using Decimal. Start with 100000 opening cash, 40000 matched funding, 25000 settled claims and 5000 in fees. Expect 110000 attributed closing cash. Add a 2000 unreferenced deposit and expect a separate held item with the attributed cash unchanged. Record a later 1000 returned payment as a linked reversal in its own period. Replay the same bank file and verify the totals stay stable.

3. Draft the controller's review pack

   Given only aggregate cash totals and redacted break codes, summarize the matched and held amounts, the aged exceptions, and the owner and evidence needed for each material break. Keep proposed corrections in a separate approval list.

Run alongside a full close and have Treasury, Claims Operations and Accounting reconcile their assigned components. Check missing inputs, duplicates and a late reversal before scheduling regular runs. Keep ledger postings and allocation decisions with the authorized owners.

Need help automating this?

See how Mage matches sponsor funding, payment batches and bank records so controllers can review plan cash and exceptions. See Mage's claims funding and payment reconciliation solution.