Retailer sales do not match ERP records
Sell-out week 14 against invoices from 1 to 7 April
Data reconciliation for FMCG
Your ERP says 91.
Your retailer says 86.
We find out why
Fifteen years in consumer goods. I have seen every excuse for a mismatch. Cutoff dates, missing EANs, a private Excel that runs the close. We find the real cause. Then we fix it.
Sell-out / POS / inventory / shipments / product master / ERP
RETAILER FILEretailer_sellout_w14.xlsx
| EAN | Store | Period | Sell-out |
|---|---|---|---|
| 8412345678901 | ES-042 | W14 | 124 |
| 8412345678918 | ES-042 | W14 | 86 |
| 8412345678925 | ES-107 | W14 | 43 |
INTERNAL FILEsupplier_shipments_apr.csv
| SKU | Customer | Invoice date | Quantity |
|---|---|---|---|
| SKU-8901 | CUST-042 | 08 Apr | 124 |
| SKU-8918 | CUST-042 | 07 Apr | 91 |
| SKU-8925 | CUST-107 | 09 Apr | 43 |
01SKU maps to EAN
02Customer maps to two stores
03Reporting cutoff differs
04Quantity difference needs review
Product key SKU to EANLocation level Customer to StoreCutoff Retail calendar W14
1,214matched
27differences
7unmapped
OPEN ITEMS
Why it keeps breaking
The files arrive every month
The same differences come back with them
Every partner sends a different version of the truth.
Reporting cutoff differs
Product, store and customer codes do not line up
Retailer SKU / internal SKU / EAN / GTIN
Every distributor sends a different file format
Columns, units, dates and levels change by partner
Your team rebuilds the same spreadsheet every month
Email threads / copied tabs / undocumented fixes
How we work
One mismatch
traced, explained,
fixed as a rule
One gap, five steps, one rule. The cycle runs every month, so the same difference cannot come back.
-
01Files in
- Retailer
- 86
- ERP
- 91
-
02Rules agreed
- Product key
- SKU → EAN
- Location
- Customer → store
- Period
- Retail calendar
-
03Compared
- Difference
- 5 units
-
04Explained
- Cause
- Timing
- Owner
- Assigned
-
05Fixed
- Rule
- Cutoff updated
- Next month
- Clean
Four ways in
Where do you fit?
The workflow above is the engine. But you might not need the whole engine. Here is where we actually start: with the part of the process that is costing you time, trust or another round of Excel.
Root Cause Audit
You bring the mismatch. We trace it through the files, dates, codes and rules until we find the reason.
Best when you know the numbers are wrong but not why Stop guessing. Let's find the culprit.Permanent Fix
We fix the mapping, cutoff or business rule behind the difference, so the same problem does not need another investigation next month.
Best when the same exception keeps coming back Make this the last time it breaks.Ongoing Maintenance
We run the monthly comparison, update the mappings and keep each open difference attached to an owner and a next step.
Best when the files change faster than the team can maintain them Take it off your plate permanently.Full Custom Solution
We build the workflow around your sources, systems and reporting cycle: intake, mapping, checks, exceptions and handover.
Best when the current process is too large or fragile to keep patching Build it once, run it forever.What changes
A number your team can trust
and explain
After the review, your team reads one reconciled number instead of six files.
01One reconciled number1,214 rows matched, 27 flagged with a cause
02A close that ends earlyThree days of Excel become one morning
03Exceptions with ownersEach difference carries evidence and a next step
04Mappings that stay fixedThe SKU-to-EAN spec survives each cycle
05One calendar across marketsThe retailer calendar sets the cutoff
06No more copied tabsThe exception queue replaces the email threads
What you walk away with
The work leaves
three working documents
A source register, a mapping spec and an exception queue, updated each month
SOURCE REGISTERSRC / 01
Working material / monthly close| Source | Cadence | Cutoff |
|---|---|---|
| Supplier shipments | Weekly | Friday |
| Internal ERP | Daily | 18:00 |
| Retailer sell-out | Monthly | Day 3 |
| Returns file | Weekly | Monday |
MAPPING SPECIFICATIONMAP / 07
Working material / versioned| Internal key | Retailer key | Rule |
|---|---|---|
| SKU | EAN | Exact |
| Customer | Store | Mapped |
| Invoice date | Sales period | Calendar |
EXCEPTION QUEUEEXC / 27
Working material / active review| Exception | Evidence | Next |
|---|---|---|
| Quantity difference | Shipment vs sell-out | Review |
| Product not mapped | Missing EAN | Assign |
| Period timing | Cutoff rule | Explain |
Two ways to start
Start where the mismatch is
Both start with one mismatch and the files behind it
Build or repair the reconciliation
We build the source register, the mapping spec and the exception queue. Your team runs them
Setup: one to two weeks. After the handover your team runs the cycleRun the reconciliation every cycle
We run the monthly review, keep the mappings current and hand over the reconciled number
We keep the cadence: files in, number out, every monthNot sure which part is failing? Bring one mismatch and start from the evidence
CHANNEL EXPERIENCE / TRAVEL RETAILComplex, multi-market data environments
Multiple operators, distributors, currencies, calendars and product masters make Travel Retail a demanding proving ground for reconciliation.
Specialist experience, applied more broadly across FMCGWhy Marksyte
We learned this in Travel Retail,
airports, duty-free
and multi-market files
We built our method in Travel Retail: airports, duty-free operators, multi-currency files. FMCG has the same files, fewer airports.
Hands-onWe review the source files ourselves and stay until the handover
Fifteen yearsCutoff dates, missing EANs, one private Excel. We have seen them all before
A practical first conversation
Review your current
reconciliation process
Bring one mismatch and the files behind it. We will show you the cause.
contact@marksyte.comBook a reconciliation review