The company’s cash flow had been tracked for years in a hand-kept spreadsheet. The goal was to generate it from ERP data automatically — to remove two days of work every month.
With the generator ready I ran the first test: the spreadsheet and the system side by side for the same month. They did not agree.
The first reflex, and why it was wrong
When the automated and the manual disagree, the default reaction is obvious: the manual one is wrong. Humans get tired, skip rows, drag a formula incorrectly.
I started there. And I was wrong.
“The manual one is wrong” is not a finding, it is a prejudice. Any prejudice accepted without measurement sends the search to the wrong place. I spent two days looking for an error in the spreadsheet; the error was not there.
Drop the total, look at the lines
The turning point was a change of method. I stopped comparing totals — a total difference is one number that says nothing. I matched line items instead:
| item | spreadsheet | system | |
|---|---|---|---|
| Receipts (TRY) | 1,284,900 | 1,284,900 | = |
| Payments (TRY) | 911,430 | 911,430 | = |
| Receipts (EUR) | EUR 42,100 | TRY 1,845,000 | ≠ |
| FX difference | — | 48,117 | ≠ |
The difference appeared on the third line. The local-currency items matched to the last unit; the divergence was entirely in foreign currency.
Which rate?
The question: when a euro receipt is shown in a local-currency report, at which rate is it converted? Three reasonable answers, three different numbers:
- Transaction-date rate — the money arrived that day, book that day’s value.
- Period-end rate — the value of the currency held at month end.
- Average rate — the mean across the period.
The spreadsheet used the first. The system used the second and posted the gap to a separate “FX difference” line.
Neither is wrong. They are not answering the same question.
Cash flow asks “what came into the account this month”. Valuation asks “what is what I hold worth”. Mix them in one report and the number answers neither.
For cash flow the transaction-date rate is correct — so the spreadsheet was right. The system was carrying accounting valuation logic into a cash-flow report.
The fix
The report split in two. Cash-flow items are held in their own currency, with a local-currency equivalent at the transaction-date rate. FX effect sits in its own section, answering its own question.
And every foreign-currency row now states which rate was used and for which date. That is the same rule as “every field carries its source”.
The same pass surfaced a smaller bug: some numbers were rendered wrongly by locale formatting. A year, or an identifier code, must never take a thousands separator. When formatting a number you have to know whether it is a quantity or an identifier.
What I learned
When two sources disagree the question is not “which is wrong?” It is “are they answering the same question?”
Most reconciliation differences are not arithmetic errors but differences of definition. And the only way to find a definition difference is to go down to the lines; the total never tells you.
Also this: inside work that has been done by hand for years, correct decisions are hidden that nobody ever wrote down. Automating it means carrying those decisions across too — otherwise what you call modernisation is a loss of knowledge.