Retiring the reconciliation spreadsheet

Walk into almost any plan running non-medical benefits at scale and you’ll find the same artifact: a spreadsheet, usually owned by one or two people, where the month actually gets closed. Vendor invoices come in. Delivery records come from somewhere else. Eligibility is checked against a roster. And then someone spends days, often the same days every month, making the three agree.

It works. That’s the trap. It works well enough that the underlying problem stays invisible, right up until volume grows, an auditor asks a question, or the one person who understands the tabs goes on vacation.

What the spreadsheet is really doing

Strip away the formatting and the reconciliation spreadsheet is quietly performing four jobs the rest of the stack doesn’t:

• It’s the de facto system of record for what was actually delivered, because nothing else captures the delivery event cleanly.
• It’s the matching engine between what a vendor billed and what a member received.
• It’s the duplicate check that keeps the same service from being paid twice across funding sources. Medicaid’s third-party liability rules make that ordering a legal question rather than a preference.2
• It’s the audit file someone will reach for when a regulator asks what happened to a specific member, a record the plan is contractually obliged to retain for 10 years and produce for inspection.1

That’s a lot of load-bearing weight for a tool with no history, no access controls, and no enforcement. The spreadsheet isn’t the problem. It is heroically covering for four systems that were never built.

If closing the month requires a person to remember how the tabs connect, the knowledge lives in a head, not a system. That’s the risk, not the effort.

Three places it quietly breaks

Volume. Manual reconciliation scales linearly with delivered benefits. Twice the volume, roughly twice the days. The approach that felt manageable at launch becomes the bottleneck exactly when the program succeeds.

Timing. A spreadsheet reflects the roster as of whenever it was last pulled. Eligibility shifts month to month, so a delivery that looked clean can turn out to have been made to a member who had churned, a discrepancy the spreadsheet has no way to flag on its own. Churn is also not always a clean break: where a SNP enrollee no longer meets the eligibility criteria but can reasonably be expected to meet them again within six months, the enrollee is deemed to remain eligible for a period of not less than 30 days and not more than six months3, a state a static roster does not represent.

Memory as a control. The rule that stops double-counting across Medicare, Medicaid, and community funding usually lives in the analyst’s head. That’s not a control an auditor can inspect, and it’s not one that survives turnover. What CMS asks for is an effective system for routine monitoring, backed by internal and external audits4, a description that does not fit a rule held in one person’s memory.

What replaces it

The fix isn’t a better spreadsheet. It is moving the four jobs into a system that does them by construction. Capture the delivery event once, at the moment it happens, with the member, benefit, date, vendor, and funding source attached. Match invoices against those events automatically instead of by eye, remembering that delegated vendor activity has to be specified and monitored under the plan’s written arrangements.5 Enforce the funding boundary in software so duplication is prevented, not caught. Keep the whole history immutable so the audit file assembles itself.

When that spine exists, month-end stops being a reconstruction and becomes a review. The exceptions surface on their own; the team spends its time resolving the handful that matter instead of rebuilding the other 95% by hand.

The signal to watch for

You don’t need a formal assessment to know whether this applies to you. Ask a simple question: if the person who owns the reconciliation spreadsheet were out for two weeks during close, could someone else finish it confidently? If the honest answer is no, the spreadsheet isn’t a tool. It is a single point of failure wearing the costume of one. That’s the moment to move the record into a real system.