Three-Statement Model Reconciliation Errors in Excel
Diagnose working-capital signs, debt roll-forwards and balanced-but-wrong cash forecasts with reproducible Excel reconciliation examples.
A failed balance-sheet check points to a broken link. A zero check can still conceal an omitted transaction. Diagnose both by comparing schedules with independent source records.
The three-way model guide covers statement structure and a compact correct build. This example starts with known control totals, introduces specific errors and shows how to isolate them. All amounts are illustrative and exclude GST, tax, inventory, dividends and asset disposals.
Establish the control totals
The opening balance sheet has cash of $20,000, debtors of $10,000 and net equipment of $30,000. These $60,000 assets equal creditors of $8,000, debt principal of $20,000, share capital of $25,000 and retained earnings of $7,000.
For the month, credit sales are $40,000, customer receipts $32,000, operating costs incurred $24,000 and payments against operating creditors $22,000. Cash capex is $12,000, depreciation $1,000, a month-end debt draw $10,000 and principal repayment $2,000. Interest of $200 is expensed and paid, classified within operating cash for this management example.
| Control total | Expected amount |
|---|---|
| Net profit | 14,800 |
| Closing debtors | 18,000 |
| Closing creditors | 10,000 |
| Closing net equipment | 41,000 |
| Closing debt | 28,000 |
| Closing retained earnings | 21,800 |
| Operating cash flow | 9,800 |
| Net cash movement | 5,800 |
| Closing cash | 25,800 |
| Closing assets and liabilities plus equity | 84,800 |
Use these as diagnostic fixtures. Enter a fresh copy for each fault below so errors do not offset one another.
Reverse the debtor sign
Indirect operating cash should be =14800+1000-8000+2000, returning $9,800. A mistaken plus sign on the $8,000 debtor increase returns $25,800. The error is $16,000 because the movement is added instead of subtracted.
An independent direct calculation, =32000-22000-200, still returns $9,800. The difference between the two methods isolates an operating bridge issue before you inspect capex or financing.
Check the debtor schedule next. Opening $10,000 plus $40,000 sales less $32,000 receipts gives $18,000. The $8,000 increase represents recognised sales that have yet to become cash. Correct the cash-flow sign and rerun both checks. If the closing balance sheet links to the incorrect cash, its assets exceed liabilities plus equity by $16,000.
Omit the principal repayment
Suppose the cash-flow statement includes the $2,000 repayment, but the debt schedule uses =20000+10000. Closing debt becomes $30,000 instead of $28,000. Assets remain $84,800 and liabilities plus equity become $86,800, so assets less liabilities and equity returns -$2,000.
Compare lender principal with opening principal plus draws less repayments. The P&L interest charge of $200 does not explain this difference; interest is separate from principal here. Correct the debt roll-forward to =20000+10000-2000.
If both the debt schedule and financing cash omit the repayment, the balance sheet balances again. It shows debt of $30,000 and cash of $27,800, both $2,000 too high. Only an independent lender reconciliation exposes the omitted repayment.
Find omitted capex in a balanced model
Remove the $12,000 equipment purchase from both the asset schedule and investing cash flow, while keeping the supplied depreciation charge unchanged. Net equipment becomes $29,000, and closing cash becomes $37,800. Total assets still equal $84,800 because the two errors offset within assets.
The bank transaction list still contains a $12,000 equipment payment. The purchase invoice and asset register still support the addition. Compare cash capex to those records; agreement between the model's cash flow and its own asset schedule is insufficient when both share the same omitted input.
Restore the addition and investing outflow together. Closing equipment returns to $41,000 and closing cash to $25,800. Review the depreciation schedule separately under the chosen policy.
Catch a stale monthly opening
After a correct first month, the next month's opening cash must be $25,800. If a copied formula keeps the original $20,000 opening cash and the next month has zero movement, its cash forecast drops by $5,800 without a transaction.
Compare each opening balance with the previous closing balance, including debtors, creditors, equipment, debt and retained earnings. A model with hardcoded openings can pass an internal monthly movement check while breaking continuity between periods.
For a row containing previous closing cash in B2 and next opening cash in C2, use =C2-B2. Repeat the continuity check for every balance carried forward.
Keep independent checks independent
| Check | Independent evidence | Failure it helps locate |
|---|---|---|
| Opening balance | Closed trial balance | Incomplete or stale starting position |
| Operating cash | Receipts and operating payments | Working-capital signs and duplicate add-backs |
| Debt principal | Lender statement or agreed facility schedule | Missing draws or repayments |
| Cash capex | Bank payments and asset additions | Omitted or duplicated investment |
| Retained earnings | Profit and approved distributions | Broken equity linkage |
| Period continuity | Prior closing balances | Copied hardcodes and shifted references |
Display raw differences alongside a documented rounding tolerance. A status formula such as =IF(ABS(B2)<=0.01,"Pass","Review") assumes B2 contains the check difference and that one cent is suitable for the model's precision. Investigate material differences at source, and keep funding limits and commercial assumptions under separate review.
Frequently asked questions
Where should you start when a three-statement model does not balance?
Check the opening trial balance and locate the first period where the difference appears. Trace changed working-capital, asset, debt and retained-earnings schedules to their source movements. Resolve the cause before extending the forecast or inserting a balancing value.
Why can cash be wrong when the balance sheet balances?
A transaction can be omitted from both its cash flow and related balance-sheet account. Cash can also be forced to balance through a plug. Compare independently sourced receipts, payments and financing movements with the forecast to detect omissions.
How do you identify a working-capital sign error?
Compare indirect operating cash flow with customer receipts less operating payments under the same classification convention. An increase in debtors generally reduces cash relative to profit, while an increase in operating creditors generally defers payment. Trace each movement to opening and closing balances.
What belongs in a debt reconciliation?
Reconcile opening principal plus draws less principal repayments and other documented principal movements to the closing lender balance. Separate interest and fees unless the agreement capitalises them. Compare financing cash flows with lender transactions independently of the balance-sheet formula.
How should model reconciliation tolerances be set?
Use a documented tolerance appropriate to the currency precision and calculation scale. Display the underlying difference as well as the pass status. A tolerance should accommodate rounding while leaving material unexplained movements visible.