Financial statement analysis template

The history spreadsheet is the spine of the deep dive. Everything downstream reads off it: incremental ROIC, benchmarking, the model, the reverse DCF's reality check. Build it once, correctly, and the rest of the analysis is fast. Here is the standard row template; it does not change from company to company (non-financials; banks/NBFCs/insurers use the M5.01–5.03 templates instead, because the operating/financing split fails for them; see M2.03 §4).

How is the template laid out?

Columns are years: 10 where available, oldest→newest, left to right (I show 6 representative columns below for legibility; your real sheet has all ten). Rows, grouped:

A. Revenue & mix

• Total revenue (net)
• Revenue by segment (one row each, from the segment note; see reading the notes to accounts)
• Revenue growth % (YoY) and, where separable, volume growth vs price/mix (the FMCG/consumer discipline from M5.05: you want to know how much of "growth" is units)
• Geography split if material (domestic vs export/international)

B. Profitability (the margin stack)

• Gross profit and gross margin %
• EBITDA and EBITDA margin %, flagged for the lease break (see the build process and the normalizations)
• EBIT (operating) and EBIT margin %
• PBT, tax, effective tax rate %
• PAT (and PAT attributable to owners, if NCI exists)
• Exceptional / one-off items (shown separately, stripped from the normalized lines)

C. Returns on capital (the M2.03 build)

• NOPAT = EBIT × (1 − normalized tax rate)
• Invested capital (operating): net working capital + net fixed & other operating assets [+ goodwill]
• Invested capital (financing), as the reconciliation check
• ROIC = NOPAT ÷ average invested capital (with and without goodwill)
• ROCE = EBIT ÷ average capital employed (the pre-tax cousin, for peer comparability with sources that report it)
• Incremental ROIC, computed over windows in a separate block (see incremental ROIC), not year-by-year

D. Cash generation

• CFO (cash flow from operations)
• Capex (purchase of PP&E + intangibles; separate maintenance vs growth if the notes allow)
• FCF = CFO − capex
• Cash conversion: CFO/EBITDA and FCF/PAT (the M2.04 quality ratios; persistent divergence is your earliest red flag)

E. Balance-sheet health

• Net working capital (and the cash conversion cycle: DIO + DSO − DPO)
• Gross debt, cash & investments, net debt (or net cash)
• Net debt / EBITDA
• Goodwill & intangibles (watched separately, because acquisitive growth lives here)

F. Per-share & distributions (the capital-allocation record, M3.07)

• Diluted share count. Watch the trend: rising = dilution (ESOP/raises), falling = buybacks
• Dividends paid (₹ cr / $m) and dividend per share; payout %
• Buybacks (₹ cr / $m)
• Total cash returned (dividends + buybacks) and its ratio to cumulative FCF

That is roughly 30–35 rows. It fits on one screen. The screener/data-vendor version (screener.in for India, stockanalysis.com or TIKR for the US) gets you 80% of these rows instantly. But you build it by hand at least once per company you deep-dive, because the act of building is where you find the normalizations the vendor silently smoothed over (see the build process and the normalizations), and because a number you typed yourself from the filing you can defend, and a number a vendor gave you, you cannot.

The build process and the normalizations

Where each row comes from. Revenue, margins, PAT, tax → the consolidated income statement. Segment revenue → the segment note. NOPAT and invested capital → income statement + balance sheet, reformulated per M2.03 (operating vs financing). CFO, capex → the cash-flow statement. Net debt, goodwill, share count → balance sheet and equity note. Dividends, buybacks → cash-flow financing section and the statement of changes in equity. Consolidated, not standalone: for any group with subsidiaries or JVs, standalone accounts hide the actual business (the M1.09 lesson).

The build is only worth trusting after the normalizations. A raw ten-year series pasted from filings is not comparable across time. Accounting standards change, tax regimes change, companies re-segment and acquire. The analyst's job is to make the decade speak in one language. The five that bite hardest, and two of them are India-specific landmines every Indian ten-year build must defuse:

1. The Ind AS 116 / IFRS 16 lease break (India: FY20). From 1 April 2019, Indian companies moved operating leases onto the balance sheet: rent that used to sit in EBITDA as an expense now splits into depreciation (below EBITDA) + interest (below EBIT). The mechanical effect: EBITDA and EBITDA margin jump in FY20 with zero economic improvement (the M1.08 "EBITDA comparability trap"). For a paints company with leased depots, offices, and Beautiful-Homes stores, this is not trivial. Fix: either capitalize leases consistently across all ten years (add imputed interest and depreciation to pre-FY20 years too, per the M2.03 method), or compare EBIT/EBITDAR/NOPAT which the standard leaves largely undistorted. Never eyeball an EBITDA-margin "improvement" straddling FY20 without checking it is not just the standard.

1. The India corporate-tax cut (FY20). In September 2019 the statutory rate for most Indian companies dropped from ~34.9% to ~25.2%. This mechanically raised post-tax ROIC and PAT across the whole market with zero operating change. If you compute NOPAT with each year's actual tax, your ROIC series shows a "step-up" in FY20 that belongs to the finance minister, not the paint chemist. Fix: compute NOPAT at a single normalized statutory rate across the decade (we use 25.2% throughout for Asian Paints), so incremental ROIC reflects operations, not tax policy. (US analysts have the mirror landmine: the FY18 TCJA cut from 35% to 21%; normalize US ten-year series the same way.)

1. Exceptionals and one-offs. Restructuring charges, asset-sale gains, impairment, litigation settlements, insurance recoveries, forex one-offs. Strip them into their own row and compute margins and ROIC on the clean operating line, but keep the stripped items visible, because a company that has an "exceptional" every single year has a recurring problem it is labelling exceptional (a classic Schilit tell from M2.06).

1. Re-segmentation. Companies redraw their reporting segments (Asian Paints has re-cut décor/industrial/international/home-improvement lines over the years). A segment-revenue row that changes definition mid-decade is a lie by continuity. Fix: re-map history onto the current segment definition where the notes give you enough to do it; where they don't, footnote the break and do not compute cross-break segment growth.

1. Acquisitions. A mid-decade acquisition injects goodwill and a step-change in invested capital and revenue that never grew organically. It contaminates both organic growth and incremental ROIC. Fix: compute ROIC both with and without goodwill; for incremental ROIC across an acquisition, either lag and pro-forma, or split the window at the deal (the incremental ROIC and Worked Example 1 method; Sherwin/Valspar is the set-piece).

Minor-but-real: pension re-measurements through OCI, FX translation of overseas subs, changes in capitalization policy (R&D, software), and share-count changes from splits/bonuses (adjust per-share history for splits; Asian Paints' history must be split-adjusted to be readable). Log every normalization in a "notes to the build" cell. The log is the audit trail that lets you, or the app's grader, check the workbook.

The Asian Paints ten-year history, built (illustrative)

Here is the spine, six representative columns of a ten-year build, normalized (NOPAT at 25.2% throughout; leases and exceptionals treated consistently; consolidated). Round, illustrative figures; rebuild from the filings.

*FY22's +32% laps a COVID-depressed FY21 (not shown). Never read a single growth number without knowing what it laps; the M2.05 base-effect discipline.

What can you read off the history in five minutes?

What an analyst reads off this in five minutes (the history's job is to provoke the right questions, which Days 2–3 answer):

• A genuinely great business. ROIC in the high-30s to low-40s for most of a decade, against a ~12% hurdle. A ~25-point spread, sustained, is the numerical signature of a real moat (M4.05). Asset-light, cash-generative (FCF/PAT ~0.6–0.8, dented only in heavy-capex years FY18–FY20 when the plants were built), net cash throughout, no dilution, 50–60% of profit paid out. This is a compounder that also self-funds and returns cash, the M3.07 "reinvest at high returns and return the surplus" pattern.
• Two warning lights in the last two columns. FY25 revenue fell (−5%) and EBIT margin compressed to 14.9% from FY24's 18.7%, dragging ROIC from ~40% to ~29%. The payout leapt to 82% — the tell of a board with more cash than high-return places to put it. These are the fingerprints of the competitive event the time-travel read already named.
• The home-décor drag, quantified. The new segment grew from ₹100 cr to ₹1,400 cr but you can see (and Day 3 confirms) it earns far below the decorative core: a decade of capital and attention for a still-thin business. The segment row is the capital-allocation debate.

The history has done its job: it has told you this is a wonderful business now being tested, and pointed a floodlight at exactly where to look. Now quantify the test.