Everything the CFS reports has already been computed somewhere. That's the deepest fact about the indirect method, and it dictates how you build: the cash flow statement contains no new economics, only links, deltas, and sums. If you find yourself typing an assumption into the CFS, you're in the wrong layer.
One design choice is deliberate. CFS formulas start in column D (FY24) instead of F. Those same formulas that will forecast FY26 to FY30 first reconstruct FY24 and FY25 from the typed historicals. That's a free, brutal test. If your formulas can't reproduce the past, they have no business predicting the future. This block runs in M rows 45 to 67, with the header in 45. Every formula below goes in D and fills right to J:
| M row | Line | Formula in D (fill right to J) | Note |
|---|---|---|---|
| 46 | Net income | =D21 | |
| 47 | + Depreciation | =D15 | Non-cash add-back |
| 48 | − Increase in receivables | =-(D25-C25) | Asset up, cash down: M1.05's law, now a formula |
| 49 | − Increase in inventory | =-(D26-C26) | |
| 50 | − Increase in other current assets | =-(D27-C27) | |
| 51 | + Increase in payables | =D32-C32 | Liability up, cash up |
| 52 | + Increase in other current liabilities | =D33-C33 | |
| 53 | Cash flow from operations | =SUM(D46:D52) | |
| 55 | Capital expenditure | D55, E55 typed: −60.0, −66.0 · F55: =-F78 | A/E-boundary row (flagged in the model structure rules) |
| 56 | Cash flow from investing | =D55 | |
| 58 | Term-loan repayment | =-(C36-D36) | Prior BS less current BS, as an outflow |
| 59 | Revolver draw / (repayment) | =D34-C34 | Net change in the revolver balance |
| 60 | Dividends paid | D60, E60 typed: −23.0, −27.0 · F60: =-F104 | A/E-boundary row |
| 61 | Share buybacks | D61, E61 typed: 0.0, 0.0 · F61: =-F105 | A/E-boundary row |
| 62 | Cash flow from financing | =SUM(D58:D61) | |
| 64 | Net change in cash | =D53+D56+D62 | |
| 65 | Opening cash | =C24 | Prior-year BS cash |
| 66 | Closing cash | =D64+D65 | |
| 67 | CHECK: CFS ties to BS cash (must be 0.0) | =D66-D24 | The second first-class check |
Advertisement
How do you check the cash flow statement is right?
Read the two structural choices. First, the working-capital deltas in rows 48 to 52 come from the balance-sheet rows instead of the schedule. If the BS links ever break, the CFS breaks with them and check 67 fires. Wire the CFS to the schedule directly and the statements could disagree without anyone noticing. Second, financing rows 58 to 59 are balance-sheet deltas too, so they take the other road from the schedules on purpose. That makes the CFS and the schedules two independent derivations of the same flows. Independent derivations that must agree are the raw material of error checks, and the X tab you'll build soon formalizes that agreement.
Verify the reconstruction immediately. FY24: CFO 106.0, CFI −60.0, CFF −48.0, net change −2.0, closing cash 87.0, check row 67 = 0.0. FY25: CFO 121.0, CFI −66.0, CFF −52.0, net change +3.0, closing cash 90.0, check 0.0. Your formulas just re-derived two years of reported cash movement from statements alone. That's the articulation exam you sat on paper, passed by machinery you built. Columns F:J still misbehave, and the revolver is next.