Financial modelling in Excel: the three-statement model

In short

A three-statement model is a linked Excel workbook that projects a company's profit and loss account, balance sheet and cash flow statement from a common set of drivers, so that every rupee of profit flows into retained earnings and every rupee of cash movement reconciles to the closing cash balance. It is the base layer on which valuation (DCF, LBO), credit assessment and annual budgets are built, which is why it is the standard practical test for roles in equity research, investment banking, credit rating, FP&A and consulting. It is examined in MBA Financial Modelling / Corporate Finance papers (model structure, forecast drivers, circularity, balance sheet balancing) and in NISM/CFA-style questions on forecasting and statement linkage. In Indian practice the statements follow Schedule III of the Companies Act, 2013 and Ind AS, so model line items should mirror the published annual report. Employers test it live: build or fix a model in 60-90 minutes, with the balance sheet tying to zero.

Explained

What the model is and how the three statements link

• Profit and loss (statement of profit and loss under Schedule III): revenue → EBITDA → depreciation and amortisation → EBIT → interest → PBT → tax → PAT.
• Balance sheet: Schedule III vertical format - Equity and Liabilities (equity share capital, other equity, borrowings, trade payables, provisions) and Assets (PPE, intangibles, investments, inventories, trade receivables, cash and bank).
• Cash flow statement: indirect method per Ind AS 7 - operating (PAT + D&A ± working capital ± non-cash), investing (capex, investments), financing (debt drawn/repaid, equity issued, dividends, interest paid if classified there).
• The three mechanical links that make it one model:
  1. PAT from the P&L adds to other equity (retained earnings): closing reserves = opening + PAT − dividend.
  2. Closing cash from the cash flow statement is the cash line in the balance sheet.
  3. Every balance-sheet movement must appear somewhere in the cash flow statement; if it does, the balance sheet balances automatically.
• The balance check: a row computing Total Assets − Total Liabilities and Equity for every year. It must read 0 (or <0.001 after rounding). A non-zero check means a movement is missing or double-counted in the cash flow - never "plug" it.

Structure and layering of a professional model

• Standard tab order: Cover and assumptions → Drivers/Inputs → Historicals (3-5 years restated) → Schedules (depreciation, debt, working capital, equity) → Statements (P&L, BS, CFS) → Outputs (ratios, valuation, charts) → Scenarios/Checks.
• One input, one place. Hardcodes live only on the input sheet, colour-coded blue for inputs, black for formulas, green for links from other sheets (the market convention). No typed numbers inside formulas.
• Rows consistent across columns: the same formula copies right across every forecast year; break this and the model becomes unauditable.
• Time structure: historical columns (FY22A-FY26A) then forecast (FY27P-FY31P), with a single flag row (1 for actual, 0 for projected) used by IF statements and formatting.
• Units stated once (₹ crore or ₹ lakh) and never mixed; percentages as true percentages, not 12 meaning 12%.
• Checks block: balance check, cash never negative, debt schedule closing = balance-sheet debt, sum of segment revenue = total revenue, depreciation ≤ opening gross block.
• No circular references by design except the deliberate interest-on-average-debt loop; no volatile or array-heavy constructs; avoid merged cells, hidden hardcodes and manual recalculation left ON silently.

Step-by-step build

1. Collect and normalise historicals from the annual report / BSE-NSE filings: three to five years of P&L, BS and CFS exactly as reported, then a restated block for one-off items. Tie totals to the printed accounts before forecasting.
2. Compute historical ratios as forecast evidence: revenue growth, gross and EBITDA margin, tax rate, capex/revenue, depreciation/opening gross block, DSO, DIO, DPO.
3. Build the revenue driver bottom-up: volume × price, stores × sales per store, capacity × utilisation × realisation, customers × ARPU, or order book × execution rate. Growth-rate-only forecasts are accepted only for immaterial lines.
4. Forecast costs - variable items as % of revenue, fixed items grown with inflation, employee cost per head × headcount - down to EBITDA.
5. Depreciation (and PPE) schedule: closing gross block = opening + capex − disposals; depreciation = rate × opening (or average) gross block, or asset-wise on a useful-life basis per Schedule II of the Companies Act, 2013. Closing net block feeds PPE in the balance sheet.
6. Working capital schedule: forecast receivables, inventory and payables from DSO/DIO/DPO on days; the change in net working capital feeds the operating section of the cash flow.
7. Debt and interest schedule: closing debt = opening + drawdown − repayment (as per the sanctioned amortisation); interest expense = rate × average debt; interest income = rate × average cash/surplus.
8. Tax: tax expense = effective rate × PBT; keep current vs deferred separate if deferred tax assets/liabilities are material. Corporate tax in India is governed by the Income-tax Act, 2025 (in force from 1 April 2026, which renumbered all sections of the 1961 Act); models typically use the concessional domestic company rate of 22% plus surcharge and cess (~25.17% effective) or the 15% new-manufacturing rate where eligible - take the rate from the company's own tax note and state it as an input, and check the current year's Finance Act since rates change.
9. Complete the P&L to PAT, then post PAT to reserves and dividends to the dividend/equity schedule.
10. Build the cash flow statement from the P&L and from the movement in every balance-sheet line; closing cash = opening cash + net cash flow.
11. Close the loop with the revolver/cash sweep: if closing cash falls below a minimum cash balance, draw a short-term revolver; if there is surplus, repay it or accumulate cash.
12. Run the checks, then scenarios and sensitivities, and only then build outputs (FCFF for a DCF, credit ratios, charts).

Circularity, the revolver and the plug

• The loop: interest depends on average debt → interest changes PAT → PAT changes cash → cash changes the revolver → the revolver changes average debt. This is genuine, not an error.
• Three ways to handle it:
  • *Interest on opening balance* - breaks the loop entirely; simplest and preferred in interview tests and credit models.
  • *Iterative calculation* - File → Options → Formulas → Enable iterative calculation (100 iterations, 0.001 change). Works, but the model can hold stale values and crash to #VALUE if an error propagates; always keep a "circuit breaker" switch (a toggle cell that forces interest to 0 to clear the loop).
  • *Macro / copy-paste convergence* - a short VBA loop that recalculates until interest stabilises.
• Plug discipline: the revolver (or surplus cash) is the legitimate balancing item because it is itself a modelled balance-sheet line with its own interest. An arbitrary "difference" row forced into the balance sheet to make it tie is a hard fail in any screening test.
• Minimum cash: typically a policy input (e.g. ₹50 crore or 2 weeks of operating cost) - drives the revolver trigger.

Excel craft the model depends on

• Lookups: XLOOKUP/INDEX-MATCH (=INDEX(data,MATCH(row,labels,0),MATCH(year,headers,0))) over VLOOKUP, which breaks when columns are inserted.
• Scenario switching: CHOOSE(scenario_no, base, bull, bear) or INDEX(range, , scenario) driven by one cell; combine with Data Validation drop-downs and Data → What-If Analysis → Scenario Manager sparingly.
• Error handling: IFERROR only where a blank is genuinely expected - blanket IFERROR hides mistakes.
• Flags and logic: IF, MIN/MAX for sweep and cap logic (revolver draw = MAX(0, min_cash − cash_before_revolver)), SUMIF/SUMPRODUCT for build-ups.
• Sensitivity: one- and two-variable Data Tables (What-If Analysis) on, say, revenue growth × EBITDA margin; Goal Seek to solve for the growth needed for a target PAT.
• Audit tools: Trace Precedents/Dependents, Evaluate Formula, F5 → Special → Constants (finds hardcodes), Ctrl+` (show formulas), Name Manager, Formula Auditing; Power Query for pulling and cleaning filings.
• Presentation: freeze panes, grouped rows, custom number formats (#,##0;(#,##0);"-"), no hidden rows holding live logic, print area set.

Outputs, uses and limitations

• Standard outputs: EBITDA and PAT bridge, FCFF = EBIT(1−t) + D&A − capex − ΔNWC feeding a DCF, credit metrics (Debt/EBITDA, interest coverage, DSCR), DuPont/ROCE, per-share metrics, covenant headroom.
• Advantages: internal consistency (a margin assumption automatically shows its balance-sheet and cash consequences), transparency, speed of sensitivity, re-usable each quarter against actuals (variance analysis).
• Limitations: output quality is limited by the assumptions; it is deterministic (no probability unless Monte Carlo is added); it cannot capture regime changes, regulatory shocks or management behaviour; false precision - 12 decimal places on a five-year forecast; model risk from spreadsheet errors is a recognised operational risk in banks, which is why model governance and independent model validation exist.
• Practice note: for listed Indian companies, model Ind AS numbers (lease liabilities and right-of-use assets under Ind AS 116 sit on the balance sheet, with depreciation and interest replacing rent) and keep consolidated vs standalone consistent throughout.

Key points

• Three-statement model - P&L, balance sheet and cash flow statement linked so one driver set produces all three.
• Three links - PAT to reserves; closing cash to balance sheet; every BS movement reflected in the CFS.
• Balance check - Assets − (Equity + Liabilities) = 0 every year; non-zero means a missing cash-flow movement.
• Driver-based forecast - volume × price or units × rate, not a single growth percentage typed over the top line.
• Supporting schedules - depreciation/PPE, working capital, debt and interest, equity and dividend; statements only reference them.
• Circularity - interest ↔ debt ↔ cash loop; fix by using opening balances, iterative calculation or a macro, with a circuit breaker.
• Revolver - the only legitimate plug; triggered when cash would fall below the minimum cash policy.
• Colour convention - blue inputs, black formulas, green cross-sheet links; no hardcodes inside formulas.
• Consistent rows - one formula copied across all forecast columns; the base of auditability.
• Working capital days - DSO = Receivables/Revenue × 365, DIO = Inventory/COGS × 365, DPO = Payables/COGS × 365.
• Depreciation link - rate × opening (or average) gross block; closing net block = PPE on the balance sheet.
• Indirect cash flow - start at PAT, add back D&A, adjust ΔNWC, then investing and financing (Ind AS 7).
• Indian formats - Schedule III presentation, Schedule II useful lives, Ind AS recognition; corporate tax under the Income-tax Act, 2025 (effective from 1 April 2026).
• Scenario switch - one cell driving CHOOSE/INDEX across all assumptions; scenarios never retyped into the statements.
• Sensitivity - two-variable Data Table on the two assumptions that move the answer most.
• FCFF bridge - EBIT(1−t) + D&A − capex − ΔNWC: the model's handover point to valuation.
• Checks block - balance check, negative-cash check, schedule-to-statement ties; visible on the cover sheet.
• Model risk - spreadsheet error is an operational risk; version control, documentation and independent review matter.

Formulas and examples

Example 1 - Revenue build and EBITDA (driver-based). A retailer has 120 stores at FY26 end, average sales ₹4.00 crore per store, and opens 15 stores in FY27 evenly through the year (so average stores = 120 + 15/2 = 127.5). Sales per store grows 6%.

• FY27 sales per store = 4.00 × 1.06 = ₹4.24 crore
• FY27 revenue = 127.5 × 4.24 = ₹540.6 crore
• COGS at 62% of revenue = 0.62 × 540.6 = ₹335.17 crore; gross profit = ₹205.43 crore
• Other operating cost at 24% of revenue = ₹129.74 crore → EBITDA = 205.43 − 129.74 = ₹75.69 crore (margin 14.0%)

Example 2 - Working capital and its cash impact. FY26 actuals: revenue ₹500 crore, COGS ₹310 crore, receivables ₹41.10 crore, inventory ₹59.60 crore, payables ₹47.60 crore.

• DSO = 41.10/500 × 365 = 30.0 days; DIO = 59.60/310 × 365 = 70.2 days; DPO = 47.60/310 × 365 = 56.0 days
• NWC FY26 = 41.10 + 59.60 − 47.60 = ₹53.10 crore
• Hold the same days in FY27 on revenue ₹540.6 crore and COGS ₹335.17 crore:
  • Receivables = 540.6 × 30/365 = ₹44.43 crore; Inventory = 335.17 × 70.2/365 = ₹64.46 crore; Payables = 335.17 × 56/365 = ₹51.42 crore
  • NWC FY27 = 44.43 + 64.46 − 51.42 = ₹57.47 crore
• ΔNWC = 57.47 − 53.10 = ₹4.37 crore → cash outflow of ₹4.37 crore in CFO.

Example 3 - Full one-year roll-forward with the balance check. Opening FY27 balance sheet (₹ crore): PPE (net) 300, inventory 59.60, receivables 41.10, cash 25 → total assets 425.70. Equity capital 50, other equity 180.70, long-term debt 150, payables 47.60 → total 428.30. (Opening sheet already balances only if it ties - here it does not, so first correct the opening data: take other equity as 178.10, giving total 425.70.) Assumptions: EBITDA ₹75.69 crore (Example 1); capex ₹40 crore; depreciation 10% of opening gross block, gross block opening ₹450 crore → depreciation ₹45 crore; interest 9% on average debt with ₹20 crore repaid at year end; tax 25.17%; dividend ₹5 crore; working capital per Example 2.

• Average debt = (150 + 130)/2 = ₹140 crore → interest = 0.09 × 140 = ₹12.60 crore
• EBIT = 75.69 − 45.00 = ₹30.69 crore; PBT = 30.69 − 12.60 = ₹18.09 crore
• Tax = 0.2517 × 18.09 = ₹4.55 crore → PAT = ₹13.54 crore
• CFO = PAT 13.54 + D&A 45.00 − ΔNWC 4.37 = ₹54.17 crore
• CFI = −capex 40.00 = −₹40.00 crore; CFF = −debt repaid 20.00 − dividend 5.00 = −₹25.00 crore
• Net cash flow = 54.17 − 40.00 − 25.00 = −₹10.83 crore → closing cash = 25.00 − 10.83 = ₹14.17 crore
• Closing other equity = 178.10 + 13.54 − 5.00 = ₹186.64 crore; closing net PPE = 300 + 40 − 45 = ₹295 crore

Example 4 - Revolver and minimum cash. Same company, but minimum cash policy is ₹25 crore and no debt repayment is made in FY28. Cash before revolver at FY28 end is computed as ₹18.40 crore.

• Revolver draw = MAX(0, 25.00 − 18.40) = ₹6.60 crore → closing cash = ₹25.00 crore, closing revolver = ₹6.60 crore.
• FY29 interest on the revolver at 10% on average balance; if FY29 cash before revolver is ₹34.00 crore, repay = MIN(6.60, 34.00 − 25.00 = 9.00) = ₹6.60 crore (fully repaid), closing cash = 34.00 − 6.60 = ₹27.40 crore.

Example 5 - FCFF handover and a sensitivity grid. From Example 3: EBIT ₹30.69 crore, t = 25.17%, D&A ₹45 crore, capex ₹40 crore, ΔNWC ₹4.37 crore.

• EBIT(1−t) = 30.69 × 0.7483 = ₹22.96 crore
• FCFF = 22.96 + 45.00 − 40.00 − 4.37 = ₹23.59 crore

Where it is used

• Equity research and investment banking (Kotak Institutional, ICICI Securities, Axis Capital, Jefferies India, Avendus): quarterly earnings models feeding DCF and relative valuation; the modelling test is part of hiring.
• Credit rating and lending (CRISIL, ICRA, CARE, India Ratings; SBI/HDFC Bank corporate credit teams): projections for DSCR, Debt/EBITDA and covenant testing in appraisal notes and rating committee papers.
• FP&A and corporate finance teams in Indian corporates: annual operating plan, rolling forecast, variance against actuals, capex approval notes, treasury cash planning.
• Private equity, VC and transaction advisory (Big Four deal advisory, KKR/Blackstone India, growth funds): LBO and base-case models, funds-flow and financial due diligence.
• Infrastructure and project finance: SPV models for roads, solar and transmission, with sculpted debt and DSCR covenants - a three-statement core with a long tail of schedules.
• Roles and certifications: analyst roles in FP&A, treasury, business finance and consulting; modelling features in NISM research-analyst preparation, CFA/CA curricula and campus "Excel + finance" screening rounds.

Interview questions

• Q: Why does a three-statement model's balance sheet balance automatically? A: Because PAT flows to retained earnings and closing cash flows from the cash flow statement, and every other balance-sheet movement is reflected in the cash flow statement - so the accounting identity is preserved by construction.
• Q: Your balance check shows ₹12 crore. How do you find the error? A: Compare the year-on-year change in each balance-sheet line with the corresponding line in the cash flow statement; the line whose movement is missing, double-counted or wrong-signed is the error - never force a plug.
• Q: What causes circularity in a model and how do you resolve it? A: Interest on average debt depends on cash and the revolver, which depend on interest; resolve it by charging interest on opening balances, by enabling iterative calculation with a circuit-breaker toggle, or with a convergence macro.
• Q: What is a revolver and why is it the correct plug? A: A short-term revolving facility drawn when cash would fall below the minimum cash policy; it is legitimate because it is a modelled liability that carries its own interest, unlike an arbitrary balancing row.
• Q: How do you forecast revenue in a model? A: Bottom-up from drivers appropriate to the business - volume × realisation, stores × sales per store, capacity × utilisation, customers × ARPU - benchmarked against historical growth and management guidance.
• Q: Depreciation increases by ₹10 crore. Walk through all three statements. A: EBIT falls ₹10 crore, PAT falls by ₹10 × (1 − t); in the cash flow, PAT is lower but ₹10 crore of D&A is added back, so cash rises by the tax saved (₹10 × t); net block falls ₹10 crore and reserves fall by the after-tax amount, so the sheet still balances.
• Q: Where does the cash flow statement come from in a model - the published statement or the other two statements? A: It is derived entirely from the forecast P&L and the movement in balance-sheet lines (indirect method); copying a published cash flow into a forecast breaks the linkage.
• Q: How do you build scenarios without rebuilding the model? A: Keep each scenario's assumptions in parallel rows and pull them with CHOOSE or INDEX driven by one switch cell, so every statement responds to a single input change.
• Q: What checks would you put on the cover sheet? A: Balance check, closing cash never negative, debt schedule closing equals balance-sheet debt, depreciation within gross block limits, sum of build-up equals the statement total - all flagging TRUE/FALSE.
• Q: Why is VLOOKUP discouraged in models? A: It counts columns from a fixed start, so inserting or moving a column silently returns the wrong value; INDEX-MATCH or XLOOKUP reference labels and survive structural changes.
• Q: A model shows rising profit but falling cash. Is it wrong? A: Not necessarily - growing working capital, heavy capex or debt repayment can consume more cash than profit generates; check ΔNWC, capex and financing lines before assuming an error.
• Q: How do you handle leases in an Indian listed-company model? A: Under Ind AS 116 recognise a right-of-use asset and a lease liability, with depreciation and interest replacing rent expense - so EBITDA is higher and debt-like obligations appear on the balance sheet; keep the treatment consistent with the reported numbers.

Common mistakes

• Forcing the balance sheet to tie with a "difference" or plug row instead of finding the missing cash-flow movement - an instant fail in screening tests.
• Hardcoding numbers inside formulas (growth, tax rate, days) so the model cannot be sensitised or audited.
• Breaking row consistency - a different formula in one year's column, which no reviewer will notice until the answer is wrong.
• Forgetting the sign convention on working capital: an increase in receivables or inventory is a cash outflow; an increase in payables is an inflow.
• Leaving iterative calculation on without a circuit breaker, so stale or #VALUE results propagate silently through every output.
• Mixing units and periods (₹ lakh with ₹ crore, quarterly with annual, standalone with consolidated) or mixing actual and projected columns in ratio averages.
• Double-counting: adding capex in investing while also having already netted it inside the PPE movement, or adding back depreciation twice.

Related topics

Cash flow statement · Ratio analysis · Equity research and valuation: DCF and multiples · Working capital management · Cost of capital and capital structure · Credit analysis and lending