2026-11-10 · AI-drafted · Finance · Accounting · Modeling · 6 min read
Build the Asset Schedule That Ties Your Entire Model Together
Most three-statement models break at the asset schedule. The income statement carries a depreciation charge, the balance sheet carries a net asset value, and the cash flow statement carries a capital expenditure figure — and in a broken model, none of those three numbers talk to each other. I'll show you how to build a capital expenditure and asset schedule that drives all three statements from a single source, so the model ties out every time you change an assumption.
Why the Asset Schedule Is the Model's Load-Bearing Wall
A three-statement model has one job: every number that appears in more than one place must come from the same cell. Depreciation is the clearest test of that discipline. It reduces operating profit on the income statement, reduces the gross asset balance on the balance sheet, and appears as a non-cash add-back on the cash flow statement. If you hard-code any one of those three, the model is broken — it just hasn't told you yet.
The asset schedule is the single sheet that calculates depreciation and feeds it to all three statements. Think of it as a sub-ledger: it holds every asset category, its opening balance, additions in the period, disposals, accumulated depreciation, and closing net book value. When the balance sheet pulls net property, plant, and equipment from this schedule — and nowhere else — the model becomes structurally honest.
This matters across reporting regimes. A German manufacturer reporting under IFRS (International Financial Reporting Standards) and an Indian IT services firm reporting under Ind AS (India's converged IFRS standard) both require component-level depreciation — meaning a single piece of equipment may have its frame, engine, and interior depreciated on different schedules. A Brazilian retailer reporting under CPC (Comitê de Pronunciamentos Contábeis, Brazil's IFRS-aligned standard) faces the same requirement. The asset schedule has to handle that complexity without breaking the tie-out.
The Architecture: One Row per Asset Category
Start with a row structure that mirrors your chart of accounts. Typical categories: land (no depreciation), buildings, leasehold improvements, machinery and equipment, vehicles, and IT hardware. Each category gets its own block of rows: opening gross value, additions, disposals (gross), closing gross value, opening accumulated depreciation, depreciation charge for the period, accumulated depreciation on disposals, closing accumulated depreciation, and closing net book value. That last line — closing net book value — is what the balance sheet imports.
The depreciation charge row is where the method lives. Under straight-line depreciation (the most common method globally), the annual charge equals the depreciable amount divided by the useful life. For a machinery category with EUR 12 million of gross assets and a 10-year useful life, the annual charge is EUR 1.2 million. Under the declining-balance method — more common for assets that lose value quickly, such as vehicles — the charge applies a fixed percentage to the opening net book value each year, so the charge falls over time. The choice of method is an accounting policy decision; the schedule must implement whichever policy the entity uses.
Disposals require discipline. When an asset leaves the business, you remove both its gross cost and its accumulated depreciation from the schedule. The difference between the disposal proceeds and the net book value at the date of disposal is a gain or loss on disposal — which flows to the income statement, not to the depreciation line. A common modeling error is to net the disposal against the asset balance without routing the gain or loss correctly. That error distorts both operating profit and the cash flow reconciliation.
A Worked Example: A Retailer With Three Asset Categories
Consider an illustrative retailer with BRL 200 million in annual revenue. Its asset schedule at the start of the year shows: buildings at BRL 80 million gross, 20-year life, BRL 20 million accumulated depreciation; fixtures and fittings at BRL 30 million gross, 5-year life, BRL 12 million accumulated depreciation; IT systems at BRL 10 million gross, 3-year life, BRL 6 million accumulated depreciation. During the year, the retailer spends BRL 15 million on new fixtures and BRL 4 million on IT upgrades. No disposals occur.
The depreciation charge for the year: buildings BRL 4 million (BRL 80m ÷ 20), fixtures BRL 6 million (BRL 30m ÷ 5, applied to the opening balance; the BRL 15m addition mid-year is depreciated for half the year at BRL 1.5m), IT BRL 3.33 million (BRL 10m ÷ 3, plus BRL 0.67m for the half-year addition). Total depreciation: approximately BRL 14.5 million. That figure flows to the income statement as an operating expense, appears as a non-cash add-back in the operating section of the cash flow statement, and reduces net book value on the balance sheet.
The capital expenditure of BRL 19 million (BRL 15m fixtures plus BRL 4m IT) appears as a cash outflow in the investing section of the cash flow statement. If your model pulls that figure from the asset schedule's additions rows — rather than from a separate hard-coded line — then changing one assumption (say, delaying the IT spend by a quarter) automatically updates the cash flow, the asset balance, and the depreciation charge. That is what a structurally honest model does.
Connecting the Schedule to the Three Statements
The income statement link is straightforward: sum the depreciation charge rows across all asset categories and feed the total to the depreciation line in the operating expenses section. Do not hard-code this number. If your income statement has a separate line for amortization of intangible assets — goodwill, patents, customer relationships — build a parallel intangible asset schedule and feed that separately. Combining tangible and intangible depreciation into one line is acceptable under many reporting standards, but the schedule should keep them distinct so you can split them when needed.
The balance sheet link is the closing net book value for each category, summed to a single property, plant, and equipment line (or split into sub-lines if the entity discloses at that level). The cash flow statement link is the additions row, summed across categories, feeding the capital expenditure line in the investing section. The depreciation charge feeds the operating section as a non-cash add-back to net income. When all three links are live, changing the useful life assumption for one asset category ripples correctly through every statement — and the balance sheet still balances.
One final check: the movement in net book value must equal additions minus depreciation minus net book value of disposals. Write that check explicitly in the schedule as a formula that returns zero when the model is correct and a non-zero number when it is not. Color it red if it is non-zero. This is the asset schedule's equivalent of the balance sheet plug check — a visible, automatic signal that the model has broken before anyone presents it.
Scenario Discipline: CapEx as a Decision Variable
Capital expenditure is one of the most consequential levers in a financial model because it affects three things simultaneously: cash outflow today, depreciation expense for years into the future, and the productive capacity that drives revenue assumptions. A model that treats CapEx as a fixed number misses the point. The asset schedule should accept CapEx inputs from the inputs sheet — the single source of truth — so that scenario analysis changes one number and the entire model responds.
A practical approach: define a maintenance CapEx assumption (the spending required to keep existing assets operational, often expressed as a percentage of gross asset value or as a fixed amount per year) and a growth CapEx assumption (spending on new capacity). In a base case for the illustrative retailer, maintenance CapEx might be BRL 5 million per year and growth CapEx BRL 14 million. In a stress scenario, growth CapEx drops to zero and maintenance CapEx falls to BRL 3 million. The asset schedule calculates depreciation under each scenario automatically, and the DCF (discounted cash flow) valuation — which depends on free cash flow, which depends on CapEx — updates without any manual intervention.
This is the discipline that separates a model built for analysis from a model built to produce a predetermined answer. When the asset schedule is the load-bearing wall, and the inputs sheet controls every assumption, the model tells you what the numbers actually imply — not what you hoped they would say. Course 2 builds this architecture from the ground up, one schedule at a time.
A Note on What This Course Is — and Isn’t
We don’t pursue CE accreditation. The courses are pure education, not credentialing.
Nothing in this course constitutes personalized financial, legal, or investment advice. You’ll learn frameworks and analytical tools — what you do with them is your decision.
We use AI heavily and we’re transparent about it.
Drafted with AI from the outline of Dr. Kareem Tannous’s Course 2. Published automatically under his editorial standards.
$189 per course. $504 for the bundle of three.
100% refund within 3 days of enrollment AND zero module access. Accessing any module — even briefly — waives the right to a refund permanently. Decisions are final; no appeals.
Course 2: Excel Modeling for Financial Decisions →
New to financial statements? Start with Course 1 →
Instructor: Kareem — DBA International Business · MS Applied Economics & Predictive Analytics · MBA Finance & Accounting · Series 65 · university-level instructor since 2014.
— Dr. Kareem Tannous