It’s Monday morning in a Brisbane office. The sales manager puts up a slide showing $410,000 in revenue for the quarter. The bookkeeper, sitting two seats down, quietly checks Xero and sees $362,000. The operations lead has a third figure in her project tracker. Nobody is lying. Nobody has done anything wrong, exactly. But the meeting now spends twenty minutes arguing about whose number is right instead of what to do about it.
We see this in almost every business we review. Two spreadsheets, same business, same period, different numbers. Here’s why it happens and what actually fixes it.
Why do two spreadsheets show different numbers for the same thing?
Two spreadsheets show different numbers because they measure different things, at different moments, using different rules, even when everyone believes they’re measuring the same thing. The gap is almost never a maths error. It’s a definitions problem, a timing problem, or a copy-paste problem, and usually a mix of all three.
That’s the honest answer, and it’s also why the problem never fixes itself. People go hunting for the typo. There is no typo. The reports were built by different people, for different purposes, pulling from different sources, and each one is internally consistent. They just don’t agree with each other.
Once you accept that, the fix becomes a process job, not a formula-checking job.
The timing problem: when did the sale actually happen?
Most mismatches between a sales report and a finance report come down to timing. Sales counts a deal when it’s won. Finance counts it when it’s invoiced, or when the cash lands. Those can be weeks apart.
Take a $30,000 job signed on 28 June, invoiced 5 July, paid 2 August. Which quarter does it belong to? The sales spreadsheet says Q4. The invoicing report says Q1. The bank-based cash flow sheet says August. Three reports, three answers, all defensible.
Multiply that by every deal near a month end and the gap grows fast. Ten deals a month straddling the cut-off, averaging $15,000 each, and your monthly reports can disagree by $50,000 or more without a single wrong keystroke.
The fix is boring and effective: pick a cut-off rule per report, write it at the top of the sheet, and never let two reports with different cut-off rules be compared in the same meeting without saying so.
GST, definitions and what counts as revenue
The second big cause of spreadsheets with different numbers is definitions. GST is the classic in Australia. Sales teams often quote figures including GST because that’s what the customer signed. Accounting software reports excluding GST because that’s what the ATO and the P&L care about.
The working is simple:
- Contract value including GST: $110,000
- Same contract excluding GST: $100,000
- Gap: $10,000, or roughly 9 per cent of the bigger figure
A 9 per cent gap across every line is enough to make two reports look like they describe different businesses.
GST is only the start. Does revenue include deposits not yet earned? Refunds? Discounts given after invoicing? Freight recharged at cost? A trades business we looked at recently had “revenue” defined four different ways across four spreadsheets, and none of the four people who built them knew the other definitions existed.
Write a one-page data dictionary. What counts as a sale, a customer, a completed job, an expense. Sounds tedious. Saves hours every month.
Copy-paste, stale exports and the version problem
Manual handling is the third cause, and the one people find most embarrassing. Someone exports a report from Xero or the CRM, pastes it into a spreadsheet, tidies it up, and emails it around. Next month they do it again. Except this month they missed two rows, or the export ran before the bookkeeper finished reconciling, or the formula summing column D stops at row 200 and the data now runs to row 230.
The file name gives it away every time. If your shared drive has “Sales_Report_FINAL_v3_UPDATED_Chris_edits.xlsx” sitting next to “Sales_Report_FINAL_v4.xlsx”, you already know which meeting the argument happened in.
Stale data compounds quietly. A dashboard built on a March export, still being quoted in June, isn’t wrong so much as expired. Nobody dated it, so nobody noticed.
The five causes at a glance
| Cause | Where it shows up | Practical fix |
|---|---|---|
| Timing and cut-off dates | Month-end and quarter-end gaps | One written cut-off rule per report |
| GST in vs ex | Sales vs finance revenue | State GST treatment on every sheet |
| Different definitions | “Revenue”, “customer”, “job” vary | One-page data dictionary |
| Manual copy-paste | Missed rows, stale exports | Automate exports, date-stamp everything |
| Formula drift | Ranges not covering new rows | Use tables, not fixed ranges |
How do you get finance, sales and operations reporting the same number?
You get everyone to the same number by agreeing on one source of truth per metric, then making every other report pull from it rather than recreate it. Revenue comes from the accounting file, full stop. Pipeline comes from the CRM. Job status comes from the project tool. Any spreadsheet that retypes those figures is a copy, and copies drift.
In practice that means three moves:
- Nominate the source system for each number and write it down.
- Replace manual exports with connected reporting where you can, even a live sheet connection beats retyping.
- Reconcile monthly. Someone compares the headline figures across reports, chases any gap over a set tolerance (say 1 per cent), and records the reason.
A pattern we keep seeing in operational reviews: the businesses with the worst reporting mess aren’t the disorganised ones. They’re the ones that grew quickly and kept bolting on one more spreadsheet per problem. Each sheet made sense when it was built. Five years later there are forty of them and nobody owns the whole picture. The owner’s instinct is to buy software. The actual first step is the data dictionary and the source-of-truth list, which costs nothing but a couple of hours.
The other thing we’ve learned sitting inside these businesses: never fix the number in the downstream spreadsheet. If the report is wrong, fix the source or fix the rule. Patching the copy guarantees the same argument next month, with interest.
Common questions about mismatched reports
What does the dollar sign mean in an Excel formula?
A dollar sign locks a cell reference so it doesn’t shift when you copy the formula, so $B$2 always points at B2. Missing or misplaced dollar signs are a quiet cause of report drift, because inserting rows moves relative references and your totals silently start counting the wrong cells.
Why doesn’t my sales report match my accounting software?
Usually because sales counts deals when they’re won and often includes GST, while accounting counts revenue when it’s invoiced or earned, excluding GST. Align the timing rule and the GST treatment and most of the gap disappears.
What is a single source of truth?
A single source of truth is the one nominated system a given number comes from, such as your accounting file for revenue or your CRM for pipeline. Every other report references it instead of retyping it, which stops copies drifting apart.
How often should we reconcile our reports?
Monthly is the practical standard for most small and mid-size businesses, done as part of the month-end close. Compare the headline figures across finance, sales and operations reports, and investigate any gap beyond a small agreed tolerance.
Should we get rid of spreadsheets entirely?
No, spreadsheets are fine for analysis and one-off working. The problems start when a spreadsheet becomes a permanent system of record that duplicates data already living in your accounting software or CRM. Keep spreadsheets for thinking, keep systems for recording.
Getting to one number
If your management meetings keep stalling on whose figure is right, the fix is usually a fortnight of process work, not a new software subscription. We do this regularly as part of our operations work: mapping where each number comes from, writing the definitions, and setting up dashboards that everyone in the room can trust. If that sounds like your Monday meeting, get in touch and we’ll have a first conversation about where your numbers are coming apart.