WMS Reconciliation Never Balances? Our Tiny Warehouse Runs on Three Spreadsheets

Let me get this out of the way: we run a 3,000-square-meter warehouse — tiny by industry standards — and every year reconciliation eats roughly half a month of our already thin profit. Not lost on freight. Lost on numbers that never match.

Here's the setup. We fulfill for a handful of small brands plus some bulk storage. Goods ship from our warehouse, the WMS logs an outbound, the marketplace (Amazon, Shopify) logs a settlement, and our ERP logs revenue. Three systems, three ledgers. At month-end we reconcile — and it has never once tied out cleanly.

How Reconciliation Drove Our Little Warehouse Insane

Our reconciliation files arrive like this: the WMS pushes a CSV over SFTP every night with the previous day's outbound details; the marketplace settlement report gets exported weekly; finance books revenue in the ERP monthly. Three files, three schedules, three definitions of the same reality.

First, the fields. The marketplace CSV calls it SKU. Our WMS export calls it Item Code. The ERP calls it a material number. Same product, three names. The first time I reconciled, I wrote a VLOOKUP, hit enter full of confidence — and got a column of #N/A. I sat staring at the screen for two hours feeling like a joke.

Then the timing. The WMS books on shipment — goods leave, we're done. The marketplace settles on delivery — the customer signs, then you get paid. Shipments going out Friday night get signed for Monday. Hit month-end and those orders land in limbo between two months. Last Black Friday week we shipped 900+ orders in a day; the WMS booked them in November, the marketplace settled in December. The December variance report ran three pages. My blood pressure ran higher.

Then there's in-transit inventory, the eternal swamp. The parcel is on a truck: the WMS says shipped, finance says revenue recognized, the marketplace says not delivered, doesn't count. Three people, three answers — settled only after I bought everyone milk tea.

The best one: a marketplace settlement came up 40+ orders short. My accountant and I dug for two days. Turns out the marketplace had counted a batch of FBA transfer orders as outbound sales, while our WMS books transfers separately from sales shipments. Different definitions — no spreadsheet on earth reconciles that.

I priced reconciliation automation tools and EDI connections. Annual contracts, tens of thousands of dollars to start. We can't afford that kind of money. The enterprise playbook is fun to read about.

Three Spreadsheets, One Afternoon: Our Scrappy Method

Then it clicked: strip away the buzzwords and reconciliation is three tables — WMS outbound, marketplace settlement, ERP bookings. Align the three on one definition, and the variances surface themselves.

Step one: unify the names. One SKU mapping table — marketplace SKU to WMS code to ERP material number, one-to-one. Build it once, spend ten minutes a week adding new SKUs. That table alone kills more than half of all mismatches. These days, when we onboard a new brand, the first thing we ask for isn't pricing — it's their SKU master, so we can build the mapping before we walk into the minefield.

Step two: unify the time window. Our house rule: reconciliation recognizes only the ship date. The marketplace report gets re-sorted by its ship-date column. We close the prior month on the 5th, leaving a five-day buffer for cross-month in-transit. Black Friday and Cyber Monday weeks get ten days. Once that rule went in, timing variances basically vanished — not because they stopped existing, but because we know they auto-offset next period. Ignore them.

Step three: merge the three into one master sheet with XLOOKUP on date plus tracking number, with a variance column at the end. Anything nonzero turns red. One glance tells you what needs work. One more trick before the CSVs go in: normalize all dates to YYYY-MM-DD and strip the dollar signs from amount columns. Laugh if you want — those two tiny steps tortured us for three months.

The payoff, in numbers: it used to take my accountant and me from the 25th to the 3rd of next month — eight days, most of it hunting "why doesn't this row match." Now? The afternoon of the 5th, three hours, variance list done. Those eight saved days of labor are worth two more small clients to us. Over a year, the labor savings alone run into the tens of thousands of dollars — for a warehouse our size, that's pure profit.

Sort Variances Into Four Buckets — Don't Just Accept Them

When the variance list comes out, don't accept it blindly. Our scrappy SOP sorts every variance into four buckets:

Here's the counterintuitive part, listen up: the goal of reconciliation isn't a zero-variance ledger — it's finding where you're bleeding. The books will never tie out perfectly. Accept that, and you survive.

So we set a threshold rule: any single variance under $20, or under 0.5% of the order value, gets written off, no investigation. Why? Chasing one variance costs more labor than $20. Spending an afternoon on an $8 difference isn't reconciliation — it's a war on your own wallet. Did we pull that threshold out of thin air? Yes — but three years of thin air prove it works: not one large variance has slipped through, and the written-off small ones add up to less in a year than what one big promo over-ships.

The quantity bucket deserves a special mention. Every review traces back to operations nine times out of ten: missed scans, wrong labels, unlogged damage. Our rule now: any variance over the threshold gets a one-line root cause appended to the master sheet. A year of those lines is our hall of shame — new hires read it on day one, and it beats any training deck.

One Piece of Advice for Fellow Small-Warehouse Owners

Enterprises run EDI and keep three-person reconciliation teams. Us? The boss does it personally, armed with Excel. Scrappy, sure — but the books are clear and I sleep at night.

The honest truth: there is no standard answer for reconciliation file formats. The marketplace won't rename fields for you; the WMS vendor won't change definitions for you. Accept that reality, and the rest is your own game. Build the mapping table, lock the time window, set your thresholds — write off what should be written off, chase what should be chased.

Small warehouses have their own way of surviving. When the numbers make sense, sleep comes easy.

By Xiao Ma, Small Warehouse Owner