A financial model is only useful if someone other than its builder can follow it, change an assumption and trust the answer. This guide draws on the principles in ICAEW's Financial Modelling Code (the PDF carries a 2024 copyright notice). It adds the Indian reporting details that trip up many analysts, and it walks through a small model you can rebuild in Excel or Google Sheets in under an hour.
1. Define the question before you open a spreadsheet
The ICAEW Code starts with scope and goals, not formulas. Before building anything, write down four things at the top of the workbook:
- Decision: what the model will help someone decide, for example whether a new product line covers its costs by year three.
- Users: who will change inputs and who will only read the outputs.
- Time base: annual or monthly periods, and the first and last period.
- Units: ₹ crore, ₹ lakh or ₹ million, chosen once and labelled everywhere.
Most rework happens because these weren't agreed at the start. A model built to value a company looks very different from one built to plan next quarter's cash.
Keep preparing
Continue with Sarkari Resume Templates₹299 — coaching के एक महीने से काफ़ी सस्ता / far cheaper than a month of coaching2. Separate inputs, calculations and outputs
The Code recommends segregating inputs (assumptions), calculations and outputs, either on separate worksheets or in clearly marked sections. In practice:
- Give every input one home. If the tax rate appears in three places, someone will eventually update only two of them. The Code's advice is to create a unique location for each input value and make every calculation refer back to it.
- Mark input cells visibly. Use a defined fill colour or cell style, not just a text label.
- Keep actual and forecast data apart. Historical figures from an annual report and your projections should never sit in cells that look identical.
- Don't hide things. Hidden rows, sheets and very small fonts make a model hard to review.
3. Write formulas that a reviewer can trace
Several of the Code's principles cover how calculations are written:
| Practice | What it means in a worksheet |
|---|---|
| Use consistent formulas | One formula copied across the whole row. A one-off exception in the middle of a row is where errors hide. |
| Avoid hardcoding | No typed numbers inside formulas, such as =B5*1.18. Put 18% in an input cell and refer to it. |
| Minimise complexity | Break long nested formulas into helper rows that each do one step. |
| Build traceable references | Formulas should point back to labelled inputs, not to unlabelled cells far away. |
| Avoid circular references | If interest depends on a cash balance that depends on interest, restructure the calculation (for example, base interest on the opening balance) or document the method clearly. |
| Avoid unnecessary rounding | Round only in presentation. Rounding inside calculations compounds small differences. |
| Use clear units and signs | State whether costs are shown as negatives, and keep that convention throughout. |
4. Build checks, and one master check
A model without checks is a model you have to trust blindly. The Code recommends individual checks plus a master check that summarises them. Common checks include:
- The balance sheet balances in every period (assets minus liabilities minus equity equals zero).
- Closing cash on the cash-flow statement equals cash on the balance sheet.
- The sum of segment revenues equals total revenue.
- Percentages that must add to 100% actually do.
Show each check as 0 when it passes and a visible flag when it fails. The Code also suggests allowing a small tolerance so that tiny spreadsheet rounding differences don't trigger false alarms. Then add a single cell that shows whether any check has failed. The Code suggests showing this master check in a frozen pane so it stays visible on every worksheet.
5. Worked example: a three-year revenue and EBITDA model
The company and figures below are illustrative. They are not a forecast for any real business.
Inputs (one cell each, coloured as inputs)
- FY26 actual revenue: ₹120.00 crore
- Revenue growth: FY27 15%, FY28 12%, FY29 10%
- EBITDA margin: 18% in every forecast year
Calculation rule, the same across every column: Revenue = previous year's revenue × (1 + that year's growth). EBITDA = revenue × margin.
| ₹ crore | FY26 (actual) | FY27 | FY28 | FY29 |
|---|---|---|---|---|
| Revenue growth | – | 15% | 12% | 10% |
| Revenue | 120.00 | 138.00 | 154.56 | 170.02 |
| EBITDA margin | – | 18% | 18% | 18% |
| EBITDA | – | 24.84 | 27.82 | 30.60 |
Reasonableness check. The compounded growth factor is 1.15 × 1.12 × 1.10 = 1.4168, and ₹120 crore × 1.4168 = ₹170.016 crore. That matches the FY29 revenue (shown rounded to 170.02), so the row formula is behaving as intended. Keep this as a live check cell, not a one-off sum in your head.
Sensitivity 1: lower first-year growth. Change only the FY27 growth input from 15% to 10%. Revenue becomes ₹132.00 crore in FY27, ₹147.84 crore in FY28 and ₹162.62 crore in FY29. FY29 revenue falls by about ₹7.39 crore, and FY29 EBITDA falls to about ₹29.27 crore.
Sensitivity 2: lower margin. Restore growth to the base case and cut the margin to 16%. FY29 EBITDA becomes about ₹27.20 crore, roughly ₹3.40 crore lower than the base case.
These two tests show which assumption your conclusion depends on more. If you could only defend one input in a meeting, this tells you which one to research properly.
6. Indian reporting details that change your inputs
- Financial year. Indian companies generally report for April to March, so "FY26" usually means April 2025 to March 2026. Label period headers with dates, not just "Year 1".
- Lakh and crore. ₹1 crore is ₹100 lakh, and ₹1 lakh is ₹1,00,000. Mixing lakh and crore inputs without a label is one of the most common scaling errors.
- Revenue and indirect taxes. Under Ind AS 115, the transaction price excludes amounts collected on behalf of third parties. Check whether a revenue figure you've pulled from a press release includes GST before you use it as a base.
- Source documents. Use the company's annual report and the results filed on stock exchanges. You can find these through BSE corporate announcements and NSE financial results filings. Record the document name, page and date next to every historical input.
7. Review checklist before you share a model
- The purpose, users, time base and units are written on the cover sheet
- Every input has exactly one location and a visible input style
- Each row uses one consistent formula across all periods
- There are no hardcoded numbers inside formulas
- Every check passes, and the master check is visible on the first sheet
- Each historical input cites its source document and page
- At least two sensitivities have been run on the key assumptions
- Someone else has changed an input and followed the result through
The Code itself says it isn't a tick-box compliance list and that there's no single right way to build every model. Use this checklist the same way: as prompts for a review, not a guarantee of correctness.
Using these practices in a job search
If you're applying for analyst roles, a small, clean model often impresses more than a large, fragile one. Pick a listed Indian company, take three years of historical data from its annual reports, forecast three years with documented drivers, add checks, and write one page explaining your two most important assumptions. In interviews, expect to be asked why you chose a driver and what happens to the output if it changes. The sensitivity tests above prepare you for exactly that. For the statement-reading skills that come first, see our financial analysis guide.