Fixed assets roll forward on the pattern professionals call a corkscrew. Each year starts from an opening balance. Add the additions and take off the subtractions to reach the closing balance, which becomes next year's opening. It's the paper roll-forward wearing spreadsheet clothes, and the same shape will carry the debt schedule, the equity schedule, and the cash roll. Learn it once here and you'll reuse it three times this week, and for the rest of your career.
M rows 76 to 80, with the header in 76:
| M row | Line | Formula in F (fill right) | Note |
|---|---|---|---|
| 77 | Opening net PP&E | =E29 | Prior-year BS PP&E |
| 78 | Capex | =F7*A!F45 | Revenue × ACTIVE capex % |
| 79 | Depreciation | =F77*A!F55 | Opening PP&E × rate |
| 80 | Closing net PP&E | =F77+F78-F79 | The corkscrew closes |
Link the statements: F29 =F80 (BS) and F15 =F79 (IS depreciation), both filled right. Rows 15 to 16 of the income statement come alive, and EBIT now computes.
Pause on F77, because it hides the build's neatest architectural trick. =E29 filled right becomes =F29 in column G, and F29 is F80, since the BS links the schedule's closing. Each opening therefore equals the prior closing through the balance sheet, automatically, so the daisy chain holds by construction. And the row obeys one-formula-per-row with no special first-column edit. Every roll-forward in this build (debt, cash, retained earnings, shares) uses the same move. It's also auditable on purpose. Select F77 and trace precedents, and the loop's paper trail is visible cell by cell. In Excel that's Formulas → Trace Precedents, or Ctrl+[ to jump. In Sheets and LibreOffice, click through the highlighted reference.
Advertisement
How do you forecast depreciation in a PP&E schedule?
Depreciation runs on a percentage of opening PP&E because that's the sound small-model method. It's self-consistent, since depreciation scales with the asset base and capex feeds future depreciation the way the D&A triple path demands. It's calibratable, since Kaveri's history says 12.9 to 13.5% and you chose 13.0%. And it's impossible to break. Its known bias is that in a fast-growing capex program it slightly understates depreciation. New assets bear a full-year rate only from the following year, which is effectively a half-year-style convention. Sometimes D&A precision matters, as in a DCF where D&A drives the tax shield, or in a capex-heavy industrial. Then upgrade to the capex-vintage waterfall, a small triangle where each year's capex is a layer depreciating straight-line over its own life. Here's the layout for a 10-year life with a half-year first-year convention. Park it to the right of the schedule, or on its own tab in bigger models. It replaces row 79's formula with a SUM of layer rows:
- One row per capex vintage. FY26's layer charges
=capex_26/10*0.5in FY26 and=capex_26/10in FY27 to FY35. Anchor the vintage with=$F$78/AssetLife*0.5, then=$F$78/AssetLifefilled right. - One row for the existing FY25 net block, straight-line over its remaining life. For Kaveri that's ₹374 cr over ~7.7 remaining years ≈ 48.6/yr. Calibrate remaining life as opening net PP&E ÷ current depreciation.
- Total depreciation = SUM of the layer rows in each column.
The triangle costs fifteen rows and buys exact capex-to-depreciation articulation. Kaveri's build stays with the ratio method. Flag the choice in the README, know the upgrade exists, and build one triangle in practice problem 7.
Worked example: the Base case numbers
Checkpoint 3 (Base). Depreciation FY26 to FY30: 48.6 · 51.8 · 55.5 · 59.7 · 64.3. Closing net PP&E: 398.6 · 427.3 · 459.5 · 494.6 · 531.7. EBIT FY26: 151.0. Capex of 73.2 runs comfortably above depreciation of 48.6, because a growing business builds ahead of its wear. If you ever see forecast capex below depreciation while revenue grows at a capital-intensive business, an alarm should ring. Your model is claiming growth without paying for it.