How to reconcile retailer sell-out data with ERP data

A retailer reports 86 units sold. The ERP reports 91 units shipped. The difference is five units, but subtracting one number from the other does not explain it. The retailer may be reporting consumer sales for a retail week, while the ERP records shipments to a distribution centre by invoice date. The product may also use an EAN in one file and an internal SKU in the other.

A useful reconciliation makes those differences explicit. It aligns the product, location, period, unit of measure and business event before it compares values. It then separates expected timing differences from missing mappings, duplicate rows, returns and genuine unexplained exceptions. This guide shows how to build that process.

Start by defining what each source actually records

Before matching rows, write down the event, grain and timing of each dataset. A typical FMCG reconciliation involves at least three sources:

The first two sources may both contain a field called units, but the fields do not necessarily mean the same thing. Sell-out is consumer demand. A shipment is product entering the retailer's network. Inventory movements, unsold stock, returns and timing sit between them.

Decide what relationship you expect. A row-level one-to-one match may be impossible. A weekly comparison by mapped product and store may be appropriate for one retailer; a monthly comparison by banner and product may be the only defensible grain for another.

Choose the comparison grain deliberately

No grain is always correct. Choose the lowest level at which both sources have compatible events and evidence, then document what is lost when the data is aggregated.

Why retailer sell-out data and ERP data differ

Product identifiers do not match

The retailer may send EAN 8412345678901, while the ERP stores SKU ES-4582. Another retailer may use its own product code 00076291. The reconciliation needs a maintained path such as retailer product code → EAN or GTIN → internal SKU. A missing or outdated mapping leaves valid sales unmatched.

The location grain is different

ERP shipments may go to a distribution centre or customer account, while sell-out is reported by individual store. A customer-to-store mapping can be one-to-many, so the process needs an agreed aggregation level. Forcing a direct match creates false discrepancies.

Reporting periods and cutoff dates differ

A retailer's week can run Monday to Sunday. The ERP month may close on the last calendar day, and invoices may be posted later than physical delivery. A transaction near the boundary can appear in different periods even when both sources are correct.

Returns and adjustments follow different paths

Retailer sell-out may be net of consumer returns. The ERP may record a credit note when stock is physically returned, perhaps weeks later. Some partners restate earlier sell-out periods; others include adjustments in the current file. Treating every negative quantity identically can move the discrepancy rather than resolve it.

Pack sizes and units of measure are inconsistent

One file may report six-packs and another individual units. A case of four six-packs could appear as 4, 24 or 1 depending on the source. Conversion rules must be effective-dated because pack configurations can change.

Files contain duplicates, omissions or format changes

A retailer may resend a corrected file, add a new header name or switch decimal conventions. An ERP extract may omit a document status or include cancelled transactions. Input controls should catch these structural issues before the business comparison starts.

A worked retailer-to-ERP reconciliation example

Assume a beverage manufacturer receives weekly sell-out for one retailer and compares it with ERP shipments. The product and location mappings are already available.

The minus five is an exception, not yet an error. Investigation shows that five units were invoiced on Sunday evening but received into the retailer network after its W14 sell-out cutoff. The appropriate classification is timing difference. Evidence should link the invoice or delivery to the exception, and the next cycle should confirm that the quantity clears.

If it does not clear, the cause may be stock still held in the retailer network, damage, a return, an incorrect mapping or missing sell-out. The classification remains open until the evidence supports one explanation.

A practical reconciliation process

1. Register the inputs. Record the retailer, file name, reporting period, received date, expected columns, owner and whether the file is original or corrected.
2. Standardize the incoming data. Convert column names, dates, decimal symbols, units and signs into a canonical structure. Preserve the original file and raw values.
3. Validate the file before matching. Check required fields, row counts, duplicate keys, invalid dates, missing identifiers and unexpected totals. Stop or quarantine a file that fails critical controls.
4. Map external identifiers. Connect retailer product codes, EANs, GTINs, stores and customers to internal master data. Keep unmapped values in a visible queue; do not silently drop them.
5. Normalize the comparison grain. Aggregate both sources to a shared level such as product-store-week or product-customer-month. Document any one-to-many location logic.
6. Align periods and measures. Apply the agreed retailer calendar, cutoff rule, units of measure, currency and treatment of returns, cancellations and taxes.
7. Calculate differences. Keep retailer value, ERP value, absolute difference and percentage difference as separate fields. Percentage alone is misleading when the base is small or zero.
8. Apply materiality rules. Decide which differences can close automatically and which need review. Tolerances may vary by measure, product or account; they should not be improvised each month.
9. Classify and assign exceptions. Use a controlled set of causes such as mapping, timing, return, duplicate, missing retailer data, missing ERP record, unit conversion or unexplained business difference.
10. Close the cycle with an audit trail. Save the input versions, mapping version, rules, output, comments, evidence, owner and status. Carry unresolved items into the next cycle.

Manage exceptions as a queue, not a collection of comments

A reconciliation is useful when the team can see what remains unresolved. Each exception should have enough structure to be filtered, assigned and revisited.

Keep the cause and resolution separate. “Adjusted manually” is a resolution action, not a root cause. If the same cause recurs, it should lead to a mapping update, source control or rule change rather than another unexplained adjustment.

Reusable exception record

A row like this keeps the arithmetic, decision and evidence together without reproducing the full exception-management workflow.

Common reconciliation mistakes

• Comparing totals before checking scope. A clean total can hide missing stores and offsetting product differences.
• Dropping unmapped rows. This makes the matched dataset look accurate while excluding the hardest records.
• Overwriting mapping tables. Without effective dates and versions, rerunning an earlier period can produce a different answer.
• Using one date field without defining it. Order, shipment, invoice, delivery and POS dates describe different points in the flow.
• Closing timing differences permanently. A timing exception should clear in a later period; if it does not, it needs investigation.
• Correcting source data in place. Preserve the raw file and record transformations so the result can be reproduced.
• Allowing free-text causes only. Comments help, but controlled exception categories reveal recurring failure patterns.

When manual reconciliation stops working

Excel can support a disciplined process when the number of files is limited, formats are stable and the same trained team owns the cycle. It becomes fragile when new retailers, distributors or markets multiply the variations.

Automation should follow a stable control design. Automating an unclear comparison only produces unclear exceptions faster. Start with the source register, canonical structure, mappings, period rules, tolerances and exception categories. Then automate the repeatable steps while preserving a human review path for ambiguous cases.

Practical takeaway

To reconcile retailer sell-out with ERP data, first define the business event behind each number. Standardize the files, map external identifiers, align the comparison grain and calendar, and make the treatment of returns and units explicit. Calculate differences only after those steps. Then manage the remaining items through a versioned exception queue with owners and evidence.

If your team receives retailer or distributor data every month and spends significant time mapping, checking and explaining differences, Marksyte's data reconciliation and controls service can help review the process. Related work may include mapping product, customer and store identifiers or standardizing partner files and data-quality rules.

Frequently asked questions