When the numbers do not agree: reconciling results

Why this matters

Sooner or later, two reports give two different answers to what looks like the same question. Finance says revenue was one figure, the sales dashboard says another, and an analyst is asked which is right. Often both are right about different things, and the work is to find exactly why they differ. This is reconciliation, and it is one of the most common troubleshooting tasks an analyst faces.

The exam's troubleshooting objective asks you to resolve analysis problems with appropriate tools and resources. This lesson covers finding where two figures diverge, the usual causes, reconciling step by step to a trusted source, documenting the difference, and judging when a difference is acceptable.

The lesson

Two reports, two totals: finding where they diverge

The course's practice orders can produce at least three different "total revenue" figures for 2025, all from the same 2,000 rows:

• £369,681: quantity × price for every valid order, before discounts;
• £354,627: the same orders after discounts are applied;
• £353,817: after discounts, with every row included, even the three with negative quantities.

None of them is a mistake in arithmetic. Each follows from a different definition, and a meeting in which two of them appear on different slides will spend its time arguing about which is right.

The first step in reconciling is to find where the figures diverge, and the approach is to break both totals down by the same dimensions and compare them piece by piece: by month, by region, by product, by source. If the difference sits in one month, or one region, the cause is there. If it is spread evenly across everything, it is probably a definitional difference, such as discounts or tax, applied throughout.

Grain, filters and time windows as the usual causes

Most discrepancies come from a short list of causes, worth checking in order.

• Definitions: gross or net of discounts, tax, returns and cancellations. The practice data's £15,054 gap between the first two figures above is exactly the discounts.
• Filters: one report excludes test orders, cancelled orders, internal accounts or invalid rows, and the other does not. The £810 gap between the last two figures is exactly the three negative-quantity rows.
• Time windows: one report uses order date and the other payment or ship date; one uses calendar months and the other a fiscal calendar; one includes the last day of the period and the other stops at midnight the day before. Time zones can move orders across a month boundary.
• Grain and joins: a join that fanned out, duplicating rows, or an inner join that dropped unmatched ones.
• Timing of the data: one report was refreshed this morning and the other last night, or one source had late-arriving records the other lacked.
• Rounding: figures rounded before summing.

Checking each cause produces a list of explained differences, and the work is finished when the explained differences add up to the whole gap.

Reconciling to a trusted source, step by step

A reconciliation needs a reference point: a trusted source, often called the system of record or a certified figure, such as the finance ledger for revenue. Other reports are reconciled to it rather than to each other.

The method is a bridge, or walk: start from one figure and add or subtract each explained difference until you arrive at the other. For the practice data:

Each line is a documented, verifiable adjustment, and the bridge shows that the three figures are consistent with each other once their definitions are known. If a bridge does not close, with an unexplained remainder at the end, the investigation is not finished.

Reconciling works best at a fine grain first: compare record counts, then totals by day or by category, then individual records in the part that differs. Comparing two lists of order IDs directly often reveals the cause in minutes.

Documenting the difference and its cause

A reconciliation that lives only in the analyst's head will be repeated the next time someone notices the discrepancy. The result should be written down: the figures compared, the source of each, the bridge between them, and the cause of each difference.

Documentation also prevents the problem recurring. If two reports differ because of definitions, the long-term fix is to agree one definition and label the reports clearly, or to make both use the same governed measure. If they differ because of a data error, such as the negative quantities, the fix belongs at the source or in the pipeline, with the reconciliation recording that it was found.

Labelling is the cheapest fix of all. A figure titled "Net revenue after discounts, valid orders, by order date" rarely gets confused with one titled "Gross revenue".

Knowing when a small difference is acceptable

Not every difference needs to be chased to zero. A tolerance, the size of difference that is acceptable without investigation, saves effort for the differences that matter.

Tolerances depend on purpose. Financial statements usually need exact reconciliation. An operational dashboard may accept a small percentage difference from the ledger caused by known timing, such as orders placed late on the last day of the month. The tolerance should be agreed in advance and written down, not decided after seeing the difference.

Two warnings apply. A small net difference can hide large offsetting errors, as the joins lesson showed: a gap of 47 rows can be 126 missing and 79 extra. And a difference that is stable is less worrying than one that is growing, because a growing gap usually means a process is failing progressively. Checking a reconciliation over several periods, not just one, shows which kind it is.