Benefits Invoice Reconciliation Template
This free Excel workbook compares a full month of medical premiums against the census and contracted rates. It includes a blank reconciliation sheet, a fictional worked example and a follow-up log. Download it directly; no signup is required for the file.
XLSX · 15 KB · Reviewed November 5, 2026. Open in a spreadsheet application, save a copy and check the formulas before entering real account data.
What goes into the template
- Read me: scope, input instructions, signed variance and review rules.
- Blank reconciliation: 20 rows for one invoice period, mapped plan and coverage tier, census and invoice counts, contract and invoice rates, source references and notes.
- Fictional example: four medical tiers plus an unmapped dental line to show how unresolved items stay visible.
- Follow-up log: issue, amount, supporting evidence, owner, confirmed correction deadline, carrier request and resolution.
The workbook handles full-month premium rows. It does not calculate prorations, retroactive adjustments, fees, taxes or credits. Record those separately in the follow-up log and reconcile them to the invoice grand total. Avoid member names or health information in this shared working file.
How the calculations work
Expected monthly amount = census count × contract monthly rate. Billed monthly amount = invoice count × invoice monthly rate. Variance = billed minus expected. A positive variance means billed above expected; a negative variance means billed below expected. Neither result establishes who owes a correction without reviewing effective dates and supporting records.
Mark a mapping Verified only after checking plan, tier, coverage period, enrollment and contract. Missing numeric inputs or unverified mapping leave expected amount and variance blank, with an UNVERIFIED status. Billed amount can remain visible. Totals include reviewed rows only, so a partial total must never be presented as a reconciled invoice.
A fictional example that does not hide offsetting errors
| Tier | Expected | Billed | Billed − expected |
|---|---|---|---|
| Employee only | $2,200 | $2,750 | +$550 |
| Employee + spouse | $3,300 | $2,200 | −$1,100 |
| Employee + child(ren) | $990 | $990 | $0 |
| Family | $3,080 | $2,800 | −$280 |
| Reviewed medical subtotal | $9,570 | $8,740 | −$830 |
The net $830 under-billing combines $550 above expected with $1,380 below expected: $1,930 in absolute differences. One dental line, ASH-DEN-B at $38, remains unverified and is excluded from the medical subtotal. It is not a matched plan priced at zero. These are invented inputs, not recovered credits or customer savings.
Use the follow-up log to finish the review
- Check each variance against enrollment changes, rate effective dates and the actual billed period.
- Record any fee, credit or partial-month adjustment separately so the medical calculation is not used outside its scope.
- Confirm the carrier or administrator's correction deadline for this account and record the source. The workbook does not assume a universal adjustment window.
- Submit the approved request through the account's normal channel, record its reference and keep it open until the carrier response and a later invoice agree.
For the complete review process, use the invoice reconciliation guide. For a product workflow that brings extraction, mapping and variance review into a case, see Planlined invoice analysis. Software does not automatically authorize or apply a carrier credit.