When Two Systems Disagree, Check Things in This Order

So back in January I got on a call with a food manufacturer. They make a frozen product, they ship it through a third-party cold storage warehouse, and their problem was that their own inventory system and the warehouse's report never agreed. Every month somebody on their ops team sat down with two Excel exports and matched them by hand, and the number we were working against was 20 to 25 hours a month of manual matching to get to an answer that everybody still kind of doubted.

And I had talked to their guy for maybe five minutes about the problem before that call. I want to be upfront about that, because it matters for what comes next. I didn't know their SKUs, I didn't know what a pallet was for them, I didn't know their warehouse. What I built before the call was a tool that takes the two files, finds every place they disagree, and puts the disagreements in an order a person can actually work through. And then I got on the call and said, okay, I don't know any of this, so you tell me what's right.

So let me walk through the order, because the order is the part that saved the time.

Before you compare anything, make sure you're comparing the same thing

The first thing the tool does is not compare quantities. It checks whether the two files can even be compared.

Number one, units. When we lined the two reports up, the ratio between them was sitting right around 2.6, and that's not a discrepancy, that's a units problem. One side is probably counting cases and the other side is counting units, or the other way around. So the tool stops and asks. Are these one to one? If they are, you say so and it moves on. If they're not, you enter the ratio and it converts before it compares a single row. On that call the answer turned out to be one to one, individual cases on both sides, and that's exactly the kind of thing I would have gotten wrong if I'd guessed.

Number two, date ranges. Their internal file ran through December 1 and the warehouse file ended around November 21. So there are ten days sitting in one file that the other file has no idea about, and if you don't flag that up front, every one of those ten days shows up later as a discrepancy and you go chase ghosts.

Number three, SKU coverage. There were eight SKUs in their system that didn't exist in the warehouse's report at all. Those turned out to be product stored at other locations, which is fine, but you have to know that before you start so you exclude them here instead of finding them one at a time in the middle.

That whole step takes a minute, and it removes most of the noise before you've looked at a single number. We wrote about fourteen fields all named Status a while back, and this is the same problem from the inventory side: the systems aren't wrong, they just never agreed on the definitions.

Match on the most reliable key first, then work down

So once the files are comparable, you match. And the mistake I see people make is they start with the thing that's easiest to eyeball, which is usually the weekly totals, and totals hide everything.

The hierarchy we landed on with them puts reference number and date at the top, SKU after that, and weekly totals last. A reference number that exists in both files is the closest thing you have to the truth, so if those match, that row is done. Dates you match with a window, because the warehouse doesn't ship the same day the paperwork moves, so we gave it a week of look-ahead on either side. A shipment that's internal on October 1 and external on October 2 isn't a problem, it's a day delay, and the tool lets you mark it as expected and move on.

Only after those two do you go to SKU, and only after SKU do you look at totals, because by then the totals are made of rows you've already explained.

Look for patterns before you look for errors

And this is the part where the tool found things the people hadn't, and I want to be clear that it found them without knowing what they meant.

It saw 560 units going out on one day as exactly four shipments of 140. It flagged that as a pattern, a standardized quantity, and asked if that was right. And they said yeah, 140 is a pallet. I didn't know that. But once you know it, every split shipment that adds up to a multiple of 140 stops being four discrepancies and becomes one explained event.

It saw the same quantity at the same timestamp showing up six times on September 3 and five times on September 16, and it flagged that as a possible duplicate in the external system. Could be nothing. Could be a real duplicate pattern. Either way it's the kind of thing you want on a list in January, not the kind of thing you want to discover in March.

And then the big one. On one date the internal system showed zero quantity received across all fifty sample transactions we ran, and the warehouse received 808 units back that same day. So those were returns that never made it into their inventory, and that single issue accounted for roughly 72% of the total unit variance between the two systems. One thing was most of the gap, and nobody had been looking for it because they'd been looking at totals.

Split the variance into explained and unexplained, then stop

So at the end you don't want a list of every difference. You want two buckets. Explained variance is the pallet splits and the day delays and the returns you now know about. Unexplained variance is what's actually left, and that's the only list a person should be working. On that first pass the recommendations that came out were to fix how returns get recorded, standardize how both sides record dates, go verify two records that only exist internally and four that only exist at the warehouse, and then run the whole thing every week.

And I'd run it Thursday. Then you've got Friday to review it, and you go into the weekend knowing where you stand instead of carrying it.

What I told them at the end

I said, our job is to help you find the problems, and your job as the ops guys is to fix them. That's the honest division of labor. The tool is never going to know that 140 is a pallet or that eight SKUs live somewhere else. You know that. What it can do is get you 90 to 95 percent of the way and hand you a short list with the rows behind every line on it, so the 20 hours turns into an hour of saying yes that's right, no that one's expected, go look at this one.

And when the two systems are connected directly instead of exported to Excel, that pass runs on its own. That's what Liaison does with the tools you already have. It reads both sides, it does the matching in this order every night, and in the morning the unexplained bucket is sitting there with the records under it. You don't sit down with two spreadsheets anymore. You sit down with the six things that need a human.

If you've got two systems that are supposed to agree and don't, bring me the two files. We'll run this order on your data and you'll see where the gap actually lives.