Why your figures do not match from one tool to another

You have one figure in Excel, another in your management software, and a third in the management report. Everyone is sure of their own. The result: meetings spent wondering which figure is right, rather than making decisions.

Here is what I observed in a real case, and the six causes of discrepancy that come up again and again.

The case

At a food industry SME, I built a pivot table of one customer's 2025 sales. I compared it with the statistics from the management software. Revenue was identical in both. But the quantities were about eighty units too high, the total weight was nearly ten kilos out, and the average prices were completely off.

When revenue is right and the rest is not, it is almost always a problem with how the calculation is built, not with missing data. Here are the six causes I found.

The six causes

1. One product spread over several rows. The same shipped product can come from several production batches. In the database, one ham could take up eleven rows, one per batch. Without processing, my calculation counted eleven products instead of one, and multiplied quantities and weights.
2. Ordered or delivered? My initial calculation used ordered quantities. But a customer can order 90 units and receive only 60. The management software showed what had been delivered, mine showed what had been ordered.
3. Rows at zero euros. Some rows were adjustments, with a zero amount. They added quantities and weight without any revenue, which distorted every average price.
4. The wrong unit. The system held two units: the item unit and the invoicing unit. For some products, the item said “piece” while invoicing was by the kilo. Dividing revenue by a number of pieces instead of a weight made some average prices five to six times too high.
5. Too much detail in the groupings. My calculation grouped sales date by date. For a product sold eight times in the year, I had eight rows with eight average prices. The pivot table added them up and showed a total of more than €70 when the real average price was under €9. Never add up averages : recalculate revenue divided by quantity.
6. Dates that do not line up. A product shipped in December 2025 and delivered in January 2026 appeared on two rows, one per year. The annual totals no longer matched.

In the same project, another discrepancy, this time on value, came from small taxes (0.01%) that the management software excluded and that the pivot table counted in the overall invoice value.

How to spot them

1. Choose the reference figure. Which one is authoritative: the management software, the accounts? Always compare against that one.
2. Compare at a high level first, then in detail. Annual total, then month, then product, then row. Keep drilling down until you find where the gap appears.
3. One cause at a time. Fix, compare again, move on to the next one. Fixing several things at once makes it impossible to know what worked.
4. Write the rule down. Each cause you find becomes a documented calculation rule: “we use delivered quantities”, “we exclude zero rows”, “the reference unit is the invoicing unit”.

This last step is the most important. Without written rules, the same discrepancy comes back in six months, when someone else redoes the calculation.

The same principle elsewhere

In a completely different setting, in banking, to study the budget behaviour of a sample of nearly 10,000 contracts, I set criteria to keep only the usable ones: a minimum number of transactions, a minimum income. That left 4,486. Without this filtering step, outliers would have distorted the results.

Cleaning data is tedious, but it is what makes figures reliable.

What about your company?

If your figures do not match, it is almost never inevitable: there are specific causes, which can be found and written down. In thirty minutes, I can already tell you where to look first.

Alexis Zueras, independent data analyst. I make your figures reliable and readable.