Reconciling Ledger Totals After a Mid-Quarter Currency Change
A finance team runs a quarterly close on a system that stores monetary amounts as integers in minor units (cents) in a single reporting currency. At the start of Q2 the base currency changed from USD to EUR, but 1,900 of the 12,400 historical transaction rows were never re-denominated; they still carry USD minor units. The close process sums all rows, converts the total once at the current rate, and compares it to the bank feed. This quarter the variance is 0.8%, or about €41,000, and the close is blocked. The team lead proposes converting the 1,900 rows at today's rate and re-running. A junior analyst notes the original transaction dates span 14 months and exchange rates moved by up to 11% over that window. Auditors require that restated figures be traceable to source documents.
1. Is converting the 1,900 rows at today's rate an acceptable fix? Explain the reasoning and state what the team should do instead.
Read the worked answer
No. Converting at today's rate produces a number that matches no source document and no historical rate, so it cannot be traced by auditors and it silently embeds 14 months of rate drift into the ledger.
The reasoning chain:
- The variance is not a rounding artifact. A 0.8% gap on a sum that includes 1,900 of 12,400 rows (about 15% of records) is consistent with those rows being in the wrong unit, not with accumulation error.
- The correct conversion rate is the rate on the transaction date, because that is the economic event being recorded. Using a current rate is a restatement, not a correction.
- Traceability requires each restated row to carry its original amount, original currency, the rate applied, the rate's source, and the date. A single blended conversion destroys all five.
The team should: identify the 1,900 rows by their currency tag or batch ID; join each to a dated rate table; recompute minor units with explicit rounding rules (state whether half-up or banker's rounding, and apply it consistently); store the original and restated values side by side with an audit column; then re-run the close and reconcile the residual. Any residual after correct re-denomination should be investigated as a separate defect rather than absorbed.
2. The close process converts the grand total once instead of per row. What class of problem does this create, and how would you demonstrate it to the team lead?
Read the worked answer
It creates an order-of-operations defect: conversion is not linear across a mixed-currency sum, so summing first and converting once is only valid when every row shares one currency. With mixed currencies the single conversion applies one rate to amounts that were booked at many different rates.
To demonstrate without argument, build a small worked example. Take two rows: 100 USD booked when EUR/USD was 1.10, and 100 USD booked when it was 1.25. Correct per-row conversion yields 90.91 + 80.00 = 170.91 EUR. Convert-the-total yields 200 / 1.25 = 160.00 EUR, or 200 / 1.10 = 181.82 EUR depending on which rate is chosen. The gap is 10-14% on a two-row sample, which scales to the observed variance.
Then show the fix as a pipeline change: convert at row grain, aggregate after. Add a guard that fails the close if the currency column has more than one distinct value, so the defect cannot recur silently. The point to make is that the aggregate was never wrong by a little; it was wrong by construction whenever the input was mixed.