01 — Statement links
How the three statements link
An integrated model has three core links: net income into retained earnings, the cash flow statement's closing cash into the balance sheet, and balance-sheet movements into the cash flow statement. Schedules such as D&A and interest then tie the statements together line by line.
Build the income statement first, then the supporting schedules, then the balance sheet and cash flow statement together, so that every balance-sheet movement is explained by a cash flow line or a non-cash income statement item.
Quick rules
- Net income flows to retained earnings: closing retained earnings = opening + net income − dividends.
- Closing cash on the balance sheet comes from the cash flow statement, never from a plug.
- Every non-cash charge in the income statement (depreciation, amortisation, stock-based compensation) is added back in operating cash flow, and the movement in deferred tax is adjusted for.
- Every balance-sheet account other than cash and retained earnings is driven by a schedule or an assumption, and its movement appears once on the cash flow statement.
- Historical periods are typed inputs; forecast periods are formulas. The boundary is one visible column.
How to fix it
- List every balance-sheet line and write next to it which schedule drives it and which cash flow line carries its movement. A line with no driver, or with two, is the usual source of an imbalance.
- Trace net income with the formula bar into retained earnings and into the top of the cash flow statement; both references must point at the same cell.
- Replace any cash balance that has been typed or plugged with a reference to the cash flow statement's closing cash.
02 — Working capital
Working capital: days ratios and the cash effect
Receivables, inventory and payables are forecast with days ratios, and the change in their net balance is a cash flow.
Forecast each working-capital balance from its driver (receivables from revenue, inventory and payables from cost of sales) using days ratios, and carry the period-on-period change to the cash flow statement with the correct sign.
Quick rules
- Days sales outstanding: receivables = revenue × DSO ÷ 365. Days inventory outstanding and days payable outstanding use cost of sales.
- Use the same day count (360 or 365) that the historical ratios were calculated with.
- An increase in receivables or inventory is a use of cash; an increase in payables is a source of cash.
- Change in net working capital = (closing − opening) of the net balance; the cash flow line is the negative of that change.
- Other operating balances (prepayments, accrued expenses, deferred revenue) are forecast the same way, as a percentage of the line that drives them.
How to fix it
- Worked example (invented): revenue 1,000, DSO 45 → receivables = 1,000 × 45 ÷ 365 = 123. If opening receivables were 110, the cash flow effect is −13.
- Compute the historical days ratios in the model so the forecast assumption is visibly anchored to what the business has done.
- Check signs with a test: raise DSO and confirm operating cash flow falls; raise DPO and confirm it rises.
- Keep working-capital balances on their own schedule and link the balance sheet to the schedule, not the other way round.
03 — Fixed assets
PP&E, capital expenditure and depreciation
The fixed-asset schedule rolls net PP&E forward: opening balance plus capex, less depreciation, equals closing balance.
Build a roll-forward schedule for PP&E where capex is an input or a percentage of revenue, depreciation is calculated from a stated method, and the closing balance links to the balance sheet while depreciation links to the income statement and the cash flow add-back.
Quick rules
- Closing net PP&E = opening net PP&E + capex − depreciation (− disposals, if any).
- Depreciation on existing assets and on new capex are separate calculations; a common simplification is straight-line over a stated useful life.
- Capex appears in investing cash flow as an outflow; depreciation appears in the income statement and is added back in operating cash flow.
- Depreciation cannot exceed the asset base: closing net PP&E must not go negative.
- Intangible assets and amortisation follow the same roll-forward pattern on their own schedule.
How to fix it
- Worked example (invented): opening net PP&E 500, capex 90, depreciation 60 → closing net PP&E 530. The income statement shows depreciation of 60, operating cash flow adds back 60, investing cash flow shows capex of −90.
- Reference depreciation in the income statement and the cash flow statement from the same schedule cell.
- If the case gives a useful life, calculate depreciation on new capex by year of purchase in a small waterfall rather than applying one rate to the total.
- Add a check that closing net PP&E on the schedule equals the balance-sheet line.
04 — Cash flow statement
The cash flow statement under the indirect method
The cash flow statement translates accrual profit into cash by reversing non-cash items and adding balance-sheet movements.
Start operating cash flow from net income, add back non-cash charges, adjust for working-capital movements, then list investing and financing flows, so that the net change in cash reconciles the opening and closing balance-sheet cash.
Quick rules
- Operating: net income + depreciation and amortisation + other non-cash items − increase in net working capital.
- Investing: capital expenditure, acquisitions and disposals, with outflows negative.
- Financing: debt drawn or repaid, equity issued or bought back, dividends paid.
- Net change in cash = operating + investing + financing; closing cash = opening cash + net change.
- Signs follow the cash: an outflow is negative on the statement, whatever sign the source schedule uses.
How to fix it
- Worked example (invented): net income 70, depreciation 60, increase in net working capital 13, capex 90, debt repaid 20, dividends 10. Operating = 70 + 60 − 13 = 117; investing = −90; financing = −30; net change = −3.
- Check that every balance-sheet line's movement appears exactly once on the cash flow statement; list the lines and tick them off.
- Link closing cash to the balance sheet and confirm that the balance check passes; if it fails, the missing or duplicated movement is on this statement.
- Keep the section subtotals visible so a reviewer can see operating, investing and financing separately.
05 — Debt and cash
Debt, cash and the revolver
The debt schedule rolls each facility forward and the revolver keeps cash from going below its minimum.
Build one roll-forward per facility (opening, drawdown, repayment, closing), calculate interest on a stated balance basis, and size the revolver from the cash available after all other flows against a minimum cash balance.
Quick rules
- Closing debt = opening debt + drawdowns − repayments, per facility.
- Cash available for debt service = opening cash + cash flow before revolver − minimum cash.
- Revolver draw = MAX(0, −cash available); revolver repayment = MIN(opening revolver, MAX(0, cash available)).
- Interest on each facility = rate × opening balance, or × the average of opening and closing balance if the case specifies average-balance interest.
- Mandatory amortisation is a scheduled input; optional prepayment or a cash sweep applies only after mandatory payments and the minimum cash.
How to fix it
- Worked example (invented): opening cash 30, minimum cash 25, cash flow before revolver −15. Cash available = 30 − 15 − 25 = −10, so the revolver draws 10 and closing cash is 25.
- Put the minimum cash balance in the assumption block and reference it; do not embed it in the MAX and MIN formulas.
- Keep the revolver formula in two visible steps (draw, repayment) so a reviewer can read the logic without expanding a nested expression.
- Reconcile the sum of all facility movements to the financing section of the cash flow statement.
06 — Balance checks
Balance checks that diagnose, not just flag
A model that balances by construction still needs a visible check, and the check should show the variance rather than a bare OK.
Add a check row that computes total assets minus total liabilities minus total equity for every period, displays the measured difference, and rolls up to one model-level flag; never force it to zero with a plug.
Quick rules
- Check = total assets − (total liabilities + total equity), shown for every period to the model's precision.
- Show the variance and an OK or CHECK label beside it; a label alone hides the size and direction of the error.
- A model-level flag aggregates all period checks and any schedule reconciliations (PP&E, debt, cash) into one cell near the outputs.
- A plug that sets cash or equity to whatever balances the sheet is not a check; it is a hidden error.
- Round the check display, not the underlying calculation.
How to fix it
- When the check fails in the first forecast period only, look for a movement that is on the balance sheet but missing from the cash flow statement in that period.
- When it fails by the same amount every period, look for an opening balance that is typed rather than linked from the prior period's closing balance.
- When it fails by a growing amount, look for a movement carried with the wrong sign, which compounds.
- Compare the difference to the values of individual lines; the variance often equals one line exactly.
07 — Circularity
Circularity and simplified interest conventions
Interest on average balances makes interest depend on cash, which depends on interest. Decide how to handle the loop before building it.
Either calculate interest on opening balances, which removes the circularity, or calculate it on average balances with iterative calculation enabled and a circuit breaker switch that sets interest to a non-circular basis when the model needs resetting.
Quick rules
- Opening-balance interest: no circular reference, slightly understates interest in a year of heavy drawdown. The simplest convention and the default when a brief does not specify.
- Average-balance interest: more accurate, creates a circular reference through cash, revolver and interest income.
- If the model is circular, enable iterative calculation (File → Options → Formulas), cap iterations and set a small maximum change.
- Add a circuit breaker: an input cell (1 or 0) that switches interest to opening balances so a #REF! or #VALUE! error cannot propagate around the loop.
- Never leave a circular model with iterative calculation off; it will show zeros or errors and the balance check will fail.
How to fix it
- Worked example (invented): opening debt 200, closing debt 180, rate 6%. Opening-basis interest = 12.0; average-basis interest = 6% × (200 + 180) ÷ 2 = 11.4. The difference is small in a normal year and the choice should be stated once.
- If the model shows spreading errors, set the circuit breaker to 0, press F9 until the model stabilises, fix the source error, then set it back to 1.
- Document the interest basis and the breaker switch in the assumption block so a reviewer can see which convention the model uses.
- Confirm that interest income on cash follows the same basis as interest expense on debt.
Final pass
Checks before handover
- 01Net income, closing cash and every balance-sheet movement each link once between the statements.
- 02Working-capital balances come from days ratios on the right driver and their changes carry the correct sign.
- 03PP&E rolls forward with capex and depreciation linked to the income statement, cash flow and balance sheet.
- 04The cash flow statement reconciles opening to closing cash with every movement appearing exactly once.
- 05Each debt facility rolls forward, the revolver respects minimum cash and interest uses the stated basis.
- 06A balance check shows the variance for every period and rolls up to one flag; nothing is plugged.
- 07The interest convention and any circularity switch are stated in the assumptions.
StreetCred practice cases assess submitted workbooks and return cell-level feedback.
Start the free diagnostic →