Every month the same files arrive, the same numbers differ and the same email thread tries to explain them. When reconciliation depends on the analyst who built the workbook, the process is not controlled: it is inherited. A reconciliation operating model makes the monthly run repeatable, auditable and improvable without making the people involved interchangeable.
This guide defines the model in eight parts: scope and grain, roles, source intake, rules, control stages, the exception lifecycle, sign-off and the improvement backlog. Together they turn a monthly comparison into a process with named owners, recorded outputs and a path for every remaining difference.
Scope and grain first
State what the process covers and at what level it must balance before anyone prepares numbers. Write down the sources in scope, such as retailer sell-out files, distributor sell-out files, shipment records, stock counts and ERP sales. Write down the measures, such as units, value or margin, and the grain: product-store-month, product-banner-week or product-market-month. Then write down what is explicitly out of scope, so a variance outside the definition is not silently absorbed.
The scope document is the contract for the run. When a new source appears or a market changes its reporting, the scope changes deliberately with an owner and a date, not by default.
Roles, not job titles
Assign process roles that can exist independently of the people who fill them. One person can hold several roles in a small team, but each role has a responsibility and a decision right. At minimum define: a process owner who answers for the run and its sign-off; a source analyst for each input who answers for that file's quality; a mapping owner who answers for product, store and calendar mappings; an exception coordinator who assigns and ages open items; and reviewers who validate outputs without having prepared them.
Separating preparation from review is the control. The person who built the comparison should not be the only person who declares it complete.
Source intake is a control stage
Treat every incoming file as a controlled input. Record when it arrived, which version it is, who sent it and what it claims to cover. Validate the structure before the comparison runs: required columns present, keys unique, dates parseable, units consistent and totals plausible. A file that fails intake is quarantined and returned for correction, not merged and fixed quietly.
When a corrected file replaces an earlier one, keep the earlier version. Retain a versioned trail so the numbers in the sign-off can be reconstructed from the files that produced them.
Rules make the logic reusable
Write each comparison rule once and reuse it every cycle. A rule states its inputs, its logic and its output: how a product or store is mapped, which calendar or cutoff applies, what tolerance is acceptable, and how a specific movement is treated. When the logic lives in a named, documented rule, it can be tested, versioned and explained. When it lives only in a formula or in the analyst's head, it is re-derived every month.
Distinguish between rules that transform data and rules that decide. Transformation rules standardize inputs, while decision rules classify what passes and what becomes an exception. Both need an owner and an effective date so a change is deliberate.
Control stages
Structure the run so each stage has a check and a recorded output before the next begins.
| Stage | What happens | Check | Output |
|---|---|---|---|
| Intake | Files are received, versioned and structurally validated | Schema, uniqueness, dates, units, totals | Quarantined or accepted inputs |
| Mapping | Products, stores and periods are aligned to the canonical references | Unmapped rows visible, no silent drops | Mapping completion report |
| Comparison | Sources are compared at the defined grain with the documented rules | Row counts and totals balance before variance is read | Comparison output |
| Classification | Every difference is assigned a cause-based category and an owner | No difference left uncategorized | Exception queue |
| Resolution | Owners investigate with evidence and update status | Changes are traceable to a file or document | Resolved or documented exceptions |
| Sign-off | Reviewers confirm the run within tolerance or approve the documented residual | Evidence is complete and retained | Signed-off monthly result |
Each stage ends with a named output, so the run can be audited later and rerun from the same inputs. If a stage produces no output, it did not happen.
Monthly close checklist
- Before intake. Confirm scope, expected sources, reporting period and calendar or mapping version.
- Intake. Log received files and versions, complete structural validation and identify missing sources.
- Standardization and mapping. Check product, customer/store and period mappings, then apply unit and currency rules.
- Reconciliation. Confirm the comparison grain, execute the rules, apply tolerances and create material exceptions.
- Exception resolution. Assign owners, record root cause, attach evidence and age or escalate unresolved items.
- Sign-off. Check completeness, review material open items, version the reconciled output, record approval and release downstream data.
The exception lifecycle
Give every exception a record with a cause-based category, one owner, a status and an aging rule. The categories should come from the causes that actually appear: timing, missing movements, identifier gaps, genuine variance and source-quality issues. A difference without a category is not an exception; it is a loose end.
Exceptions move through states: open, assigned, investigating, awaiting evidence, resolved and closed. Aging shows how long each item has been open and flags items that reappear. When the same cause returns in the following month, that is the signal for the improvement backlog, not for another email.
Sign-off closes the period
Define what completing a period means: the comparison balances within tolerance, every exception has a status and an owner, evidence is retained, and the result is approved by the reviewer. If the run cannot balance, the documented residual is the decision, not the raw number. Sign-off is recorded with the date, the approving role and the version of inputs used.
Without a defined sign-off, the period is never formally closed, and every monthly difference is reopened next month as if it were new.
Illustrative sign-off record
| Field | Illustrative record |
|---|---|
| Period | 2026-05 |
| Dataset and input version | Sell-out v3; ERP extract v2; mapping v7 |
| Scope | Retailer ES-042, product-store-week, units and net value |
| Total exceptions | 14 open or resolved in the cycle |
| Material open exceptions | 2; timing and missing coverage, both assigned |
| Completeness result | Expected files received; store coverage caveat recorded |
| Reconciliation status | Approved with documented residual |
| Approved by and date | Finance review role / 2026-06-05 |
| Comments and caveats | Two late store files remain outside the published comparison and are scheduled for the next run |
The improvement backlog
Keep a short list of structural improvements fed by recurring exceptions and repeated manual steps. Each item names the cause, the fix and the expected effect, such as adding a missing movement to the equation, changing a mapping, fixing a source file or automating a stable stage. The backlog gives recurring problems a path from exception queue to resolved system change.
Automation belongs at the end of this sequence, not at the start. Automate only the steps that are stable and documented, and keep the exception lifecycle and sign-off as the human controls on top.
Operating metrics for the process owner
| Metric | What it tells the process owner |
|---|---|
| Open exception count | How much unresolved work remains in the current cycle |
| Material open exception count | Whether the remaining risk requires management review before release |
| Exception aging | Which items are stalled and whether the queue is carrying old work forward |
| Recurrence rate | Which causes are returning and should enter the improvement backlog |
| Source completeness | Whether the run represents the expected files, locations and periods |
| Cycle time | How long the run takes from final intake to sign-off |
| Automatically resolved share | Whether stable, documented rules are reducing manual handling without hiding exceptions |
These metrics are for diagnosis, not universal targets. A process owner should compare them with the team's scope, materiality and reporting cadence rather than import a benchmark from another operation.
Practical takeaway
Formalize the monthly reconciliation run as a process: define scope and grain, assign roles, control the intake, document the rules, run the control stages, track exceptions through a lifecycle, sign off the period and feed recurring causes into an improvement backlog. The model makes the result defensible and each month's run cheaper than the last.
Marksyte's data reconciliation and controls service can design the operating model, and its managed data operations can run it month after month.
- Run a controlled retail reconciliation in Excel
- Design reusable reconciliation rules
- Manage reconciliation exceptions month after month
- Decide when reconciliation is ready to automate
- Reconcile retailer sell-out data with ERP
- See an illustrative FMCG data reconciliation case
- Explore data reconciliation and exception controls
- Explore managed data operations and analysis
Frequently asked questions
What is a data reconciliation operating model?
It is a documented monthly process that defines scope and grain, roles, source intake, rules, control stages, an exception lifecycle and sign-off, so reconciliation is repeatable instead of depending on the analyst who built the workbook.
What are the core stages of FMCG reconciliation?
Intake and validation, mapping, comparison at a defined grain, exception classification and assignment, resolution, and sign-off. Each stage has a named owner and a recorded output so the run can be audited and rerun.
How do you stop the same reconciliation exceptions recurring?
Track exceptions with a cause-based category and aging rule, then feed recurring causes into an improvement backlog: fix the source, add or change a rule, or automate a stable step, instead of resolving the same difference by email every month.