The three financial statements in a model are only as good as the supporting schedules behind them. Schedules calculate the details — how fixed assets and depreciation move, how working capital follows sales, how each loan is drawn, repaid and charged interest — and feed the results into the income statement, balance sheet and cash flow statement. They all follow one simple pattern: the roll-forward.
What you will learn
- Build a fixed asset and depreciation schedule, including a depreciation waterfall
- Build a working capital schedule from days or percentage assumptions
- Build a debt schedule with drawdowns, repayments and interest
- Link each schedule correctly to the three statements
The roll-forward pattern
Roll-forward
Closing balance = Opening balance + Additions − Reductions
The closing balance of one period becomes the opening balance of the next. The additions and reductions are the flows that appear in the income statement or cash flow statement.
Every balance sheet item in a model — fixed assets, debt, equity, provisions — can be built this way. If every balance moves only through a roll-forward whose flows are also in the cash flow statement, the balance sheet will balance.
Fixed assets and depreciation
Worked example: fixed asset roll-forward with a depreciation waterfall
A company has existing fixed assets with a net book value of 3,000, depreciating at 600 a year for the next three years. It plans capital expenditure of 1,000, 1,200 and 1,500 in years 1–3. New assets are depreciated straight-line over 5 years, with a half-year charge in the year of purchase (illustrative figures and policy).
Depreciation waterfall (each year’s capex depreciated in its own row):
| Depreciation on | Year 1 | Year 2 | Year 3 |
|---|---|---|---|
| Existing assets | 600 | 600 | 600 |
| Year 1 capex (1,000 ÷ 5 = 200 a year) | 100 | 200 | 200 |
| Year 2 capex (1,200 ÷ 5 = 240 a year) | — | 120 | 240 |
| Year 3 capex (1,500 ÷ 5 = 300 a year) | — | — | 150 |
| Total depreciation | 700 | 920 | 1,190 |
Roll-forward:
| Year 1 | Year 2 | Year 3 | |
|---|---|---|---|
| Opening net book value | 3,000 | 3,300 | 3,580 |
| + Capital expenditure | 1,000 | 1,200 | 1,500 |
| − Depreciation | 700 | 920 | 1,190 |
| Closing net book value | 3,300 | 3,580 | 3,890 |
Links: capital expenditure → investing cash flow; depreciation → income statement and add-back in operating cash flow; closing net book value → balance sheet.
A waterfall is more accurate than depreciating as a simple percentage of revenue, because it follows the actual timing of investment. The methods themselves are explained in depreciation methods; disposals, if any, are an extra reduction line with any gain or loss in the income statement.
Working capital
Working capital items are usually forecast from operating drivers:
| Item | Common driver | Formula |
|---|---|---|
| Trade receivables | Days sales outstanding | Revenue × DSO ÷ 365 |
| Inventory | Days inventory outstanding | Cost of sales × DIO ÷ 365 |
| Trade payables | Days payables outstanding | Cost of sales × DPO ÷ 365 |
| Other items | % of revenue or cost | Revenue × % |
The change in each balance goes to operating cash flow: an increase in an asset uses cash; an increase in a liability provides cash. Using days assumptions makes the effect of collection or inventory improvements easy to test — see efficiency ratios and working capital management.
Debt schedule
Each borrowing is modelled separately, then summed.
Debt roll-forward and interest
Closing debt = Opening debt + Drawdowns − Repayments
Interest = Interest rate × Opening balance (or × Average balance, which creates circularity)
Worked example: a term loan
A 5,000 term loan at 10% is repaid in equal instalments of 1,000 a year (illustrative figures). Interest is calculated on the average balance.
| Year 1 | Year 2 | Year 3 | |
|---|---|---|---|
| Opening balance | 5,000 | 4,000 | 3,000 |
| − Repayment | 1,000 | 1,000 | 1,000 |
| Closing balance | 4,000 | 3,000 | 2,000 |
| Average balance | 4,500 | 3,500 | 2,500 |
| Interest at 10% | 450 | 350 | 250 |
Links: repayments → financing cash flow; closing balance → balance sheet (split between current and non-current portions); interest → income statement (and operating or financing cash flow, depending on the classification policy).
On opening balances, interest would be 500, 400 and 300 — slightly higher, without circularity. For loans repaid in equal instalments including interest, the schedule follows the amortization logic, as in the loan amortization calculator.
Revolving facilities and the cash sweep
A revolving credit facility draws automatically when cash would fall below the minimum and repays when there is surplus cash:
- Draw = MAX(0, minimum cash − cash before revolver)
- Repay = MIN(revolver opening balance, MAX(0, cash before revolver − minimum cash))
A cash sweep uses a share of surplus cash to repay term debt early, common in leveraged financing. Both make interest depend on cash, which depends on interest — a circular loop. Use a documented circularity switch or calculate interest on opening balances, as explained in modelling best practices.
Other schedules
- Equity: opening equity + profit + share issues − dividends − buybacks = closing equity.
- Tax: tax expense, tax paid and any tax payable balance; deferred tax where material.
- Leases: lease liabilities and right-of-use assets under IFRS 16.
- Provisions: roll-forwards of warranty or other provisions.
Lender metrics from the schedules
Debt schedules make it easy to calculate the ratios lenders test: interest coverage, debt service coverage and net debt to EBITDA — see solvency ratios and credit appraisal. Including covenant checks in the model shows in advance whether a plan could breach loan conditions.
Common mistakes
- Forecasting depreciation as a percentage of revenue regardless of the asset base.
- Forgetting that new capex starts depreciating only when the asset is in use.
- Showing the whole loan as non-current when part is repayable within a year.
- Letting debt go negative when repayments exceed the balance.
- Breaking the model with an uncontrolled circular reference.
Key terms
- Roll-forward — opening balance plus additions minus reductions equals closing balance.
- Depreciation waterfall — a schedule depreciating each period’s capital expenditure separately.
- Debt schedule — a roll-forward of borrowings with interest and repayments.
- Revolving credit facility — a borrowing line that draws and repays according to cash needs.
- Cash sweep — using surplus cash to repay debt early.
Frequently asked questions
Why calculate interest on average balances?
It better reflects that debt was repaid during the year. The cost is circularity, which must be managed.
How do I split debt between current and non-current?
The amount repayable within twelve months of the balance sheet date is current; the remainder is non-current.
What if capital expenditure assumptions are uncertain?
Separate maintenance capex (needed to sustain current operations) from growth capex, and test scenarios for each.
Key takeaways
- Every schedule is a roll-forward: opening + additions − reductions = closing.
- A depreciation waterfall follows the timing of each year’s capital expenditure.
- Working capital is best forecast from days or percentage drivers.
- Debt schedules feed interest, repayments and balances; revolvers and sweeps need controlled circularity.