How to build an equity research forecast model
A forecast model is built in one order. The build starts from the published year, tied line by line. Revenue is driven from volume and realisation as two lines. Each margin is decided rather than inherited. The balance sheet is carried alongside the profit ladder. Every chosen number goes into a register. Then every line is grown at one rate: if the answer barely moves, the model carries no view.
One idea sits underneath that order. A forecast model is not a machine for producing numbers. Numbers are the easy part, and a spreadsheet will produce as many as it is asked for. The model is a machine for making a small number of deliberate choices visible. Somebody else can then disagree with them one at a time. Every step below exists because skipping it hides one of those choices, and a hidden choice is still a choice; it just no longer belongs to anybody. The meaning of an assumption, and how one is tested, is covered under assumption testing. Only the order in which those assumptions are made is set out here.
The worked material is Sarvani Coatings Limited, an invented maker of decorative paints and industrial coatings, whose year three is the most recent published year in the record. One forecast year is built here as an exercise. A forecast year is a set of assumptions arranged in order, and an arrangement of assumptions is not a prediction until somebody defends each one.
An order is learned by walking it. How far the answer moves when a single assumption changes has a control of its own under assumption testing. Every figure below is worked in full and can be rebuilt from the published year with a calculator.
Step one
Where does the model start, and what does it start from?
The model starts from the published year, and it starts by tying that year rather than by typing a growth rate. Open the sheet, put year three in a column, and reconcile every line to the filed statements before a single forecast cell exists. For Sarvani Coatings Limited that means revenue of Rs 2,415 crore, cost of materials of Rs 1,304 crore, gross profit of Rs 1,111 crore, employee cost of Rs 205 crore, other expenses of Rs 460 crore and earnings before interest, tax, depreciation and amortisation, or EBITDAA profit measure taken before the financing and asset charges, defined in the accounting work rather than here., of Rs 446 crore, then depreciation of Rs 92 crore, finance cost of Rs 21 crore, other income of Rs 38 crore, tax of Rs 93 crore and profit after tax of Rs 278 crore.
Tying the base year is not analysis yet. The reconciliation is a check that the analyst's column adds up to the column somebody else published. Every forecast year is built off the base rather than off the filing, so a base year that does not tie to the published statements will be wrong in every year that follows it. That reconciliation takes about an hour on a business this size, produces nothing that can be shown to anybody, and is skipped constantly, which is why the most embarrassing model errors are almost never in the clever part.
| Year three, as published | Rs crore |
|---|---|
| Revenue | 2,415 |
| Cost of materials | 1,304 |
| Gross profit | 1,111 |
| Employee cost | 205 |
| Other expenses | 460 |
| EBITDA | 446 |
| Depreciation and amortisation | 92 |
| Earnings before interest and tax | 354 |
| Finance cost | 21 |
| Other income | 38 |
| Profit before tax | 371 |
| Tax | 93 |
| Profit after tax | 278 |
A household does the same thing before deciding what next year looks like. The starting point is not a feeling that spending went up a bit. Last year's statement comes out, the twelve months are added, and an actual number appears. Only then is there anything to grow. Analysts who skip this step do what everybody does with a bank statement. They trust a memory of it.
The base year column does not tie to the published statements. The column is out by a small amount on one line. How much of the model is affected?
Step two
Why are volume and realisation two lines and not one?
Because a single revenue growth rate hides the only question worth asking about the top line. Split it. One line for volumeUnits sold. Volume moves for reasons of demand and capacity, and it moves separately from what each unit fetches., meaning how many units go out of the gate, and one for realisationRevenue per unit sold. Realisation moves with price and with the mix of what is sold, and it can rise while volume falls., meaning what each unit fetches. Selling more and charging more are different claims about a business, they break under different conditions, and a blended rate makes them indistinguishable.
The record already shows how differently the two behave. Take one year only, year two set against year three. Revenue climbed 13.92 per cent on volume that climbed 6.0 per cent. About 7.47 per cent of that is the rise in what each unit fetched. Across those same twelve months, what the business paid for the materials inside each unit went up by about 3.64 per cent. Keep all three of those numbers pinned to that single year. Year one volume growth is not published, so the wider two year figure in this record, year one to year three, cannot be broken down per unit at all. Pairing a two year headline with a one year per unit split is the single most common way a reader of these statements ends up with an answer that cannot be reproduced.
Now the forecast, a different thing again. The analyst chooses volume growth of 6.0 per cent and realisation growth of 3.0 per cent for the year ahead. The chosen 3.0 per cent is an assumption about a future year; it is not the 7.47 per cent the business achieved last year, and writing it down next to the historical figure is what stops a repeat being assumed quietly. The two multiply: 1.06 times 1.03 is 1.0918, so revenue grows 9.18 per cent, from Rs 2,415 crore to Rs 2,636.70 crore.
A street vendor's takings rose by a fifth this year. The rise on its own settles almost nothing. If the vendor served a fifth more plates, demand has been found and the problem is capacity. If the same plates were served at a fifth higher price, pricing power has been found and the problem is whether customers stay. Same takings, two different businesses, two different next years. The split is the whole of the exercise.
A model forecasts revenue growing 9.18 per cent next year. Which two facts has that single number hidden?
Step three
How is a margin decided instead of inherited?
By writing the decision as a sentence before writing it as a formula. Carrying last year's gross marginGross profit as a share of revenue. The meaning of gross margin and the forces that move it belong to the accounting work; here it is only a cell somebody has to fill. forward unchanged is a decision, not a neutral default, and the only difference between a good model and a lazy one is whether that sentence was ever written. The spreadsheet has no opinion. Leaving the cost of materials as a percentage of revenue and dragging it right asserts something about the year ahead, and asserts it silently.
There are two ordinary ways to hold a margin, and they are not the same thing wearing different clothes. The cost of materials can be held at its share of revenue, in which case the margin comes out wherever last year left it. Or materials cost per unit can be held flat and the share left to move with realisation, in which case charging more per unit while paying the same per unit widens the margin on its own. On this forecast year the difference is not decorative.
| The forecast year, volume up 6.0 per cent, realisation up 3.0 per cent | Gross profit | Gross margin |
|---|---|---|
| A. Hold the cost of materials at its published share of revenue | Rs 1,212.99 crore | 46.00 per cent |
| B. Hold materials cost per unit, so it grows with volume alone | Rs 1,254.46 crore | 47.58 per cent |
| The difference between two cells that look identical | Rs 41.47 crore | 1.57 points |
Both of those figures are computed from the published rupee amounts, not from the printed percentages. The distinction matters more than it sounds. The published share of 54.0 per cent is a rounding of 53.9959 per cent, and the gross margin printed as 46.0 per cent is really 46.0041 per cent. Scale a rounded percentage instead of the Rs 1,304 crore and the answer comes out wrong by lakhs while looking derived and therefore trustworthy. Wherever a split has to close, close it in rupees.
Last year's gross margin is carried forward into every forecast year, unchanged. Is that a neutral choice?
Materials cost per unit is held flat while volume grows 6.0 per cent and realisation grows 3.0 per cent. Against holding the cost at its published share of revenue, what does the forecast gross margin do?
Step four
Which cost lines does a view actually rest on?
Very few, and the useful move is to say so on the face of the model. Below the gross line Sarvani Coatings reports employee cost of Rs 205 crore and other expenses of Rs 460 crore, inside which sit advertising and sales promotion of Rs 121 crore and freight and distribution of Rs 138 crore, leaving Rs 201 crore that the record does not break out further. Four lines, and an analyst will genuinely have an opinion about one or two of them at most.
A line grown with revenue by default is an assumption nobody made on purpose. Growing every cost line with revenue is a perfectly valid choice, and it stops being valid the moment it is unstated. The register in step seven is where that distinction gets recorded. The model to avoid is the one in which the freight line and the advertising line are both linked to revenue for no reason other than that the cell above them was, and are then defended in a meeting as though somebody had thought about them.
| Cost line, year three | Rs crore | Is there a view? |
|---|---|---|
| Employee cost | 205 | Usually yes. Headcount plans and wage revisions are discussed publicly. |
| Freight and distribution | 138 | Sometimes. It moves with volume and with fuel, which are separate things. |
| Advertising and sales promotion | 121 | Rarely from the outside. It is a management choice, not an outcome. |
| The rest of other expenses | 201 | No. Not broken out, so a link to revenue is the honest default, stated as one. |
Step five
What happens below the earnings line, and what is missing from the record?
The step is to stop and read what the record actually contains before filling anything in. Below EBITDA sit depreciationThe charge that spreads an asset's cost across the years it is used. How it is measured belongs to the accounting work and is applied here rather than explained. and amortisation of Rs 92 crore, finance cost of Rs 21 crore, other income of Rs 38 crore, and a tax charge of Rs 93 crore against profit before tax of Rs 371 crore. All five lines are disclosed, and all five are forecastable in the ordinary way.
Then there is the one that is not. The record carries capital work in progressAn asset the business has paid for but has not switched on yet, so no charge for wear runs against it while it waits. of Rs 118 crore, being a coatings line that has not been commissioned. When it is, the depreciation charge changes. And the rate at which it will be charged is nowhere in the record: no useful life, no method, no rate for that asset.
Rs 118 crore of capital work in progress, being the uncommissioned coatings line, is switched on part way through the forecast year. Which missing input does the model now need?
There is a tempting substitute, and it is worth naming because it looks like arithmetic rather than invention. Rs 92 crore of depreciation against a net block of Rs 806 crore is 11.41 per cent, and it is very easy to type that into the new line and feel that the figure was derived. It was not. The 11.41 per cent is a blended charge across a set of assets bought in different years with different lives, and a new coatings line is not the average of them. Where the record is silent, the correct response is to write the silence into the model, stating on screen that the rate is one the reader is supplying, rather than importing a plausible figure from somewhere it does not belong.
Step six
Why does the balance sheet have to move alongside the ladder?
Because profit and cash are not the same thing and the gap between them lives on the balance sheet. Run the two together, row by row, rather than building the profit ladder for five years and bolting a balance sheet on afterwards. The working capital cycleInventory plus trade receivables less trade payables. Growth in the cycle takes cash in first and returns it later. at Sarvani Coatings is inventory of Rs 402 crore plus trade receivables of Rs 289 crore less trade payables of Rs 356 crore, which is Rs 335 crore, against Rs 281 crore a year earlier. The cycle absorbed Rs 54 crore of cash in year three, so a profit forecast with no working capital line has quietly assumed the cycle stops moving, without anyone writing that assumption down.
The forecast makes it concrete. Hold the cycle at the same days and the flat growth year takes working capital to Rs 365.75 crore, absorbing a further Rs 30.75 crore. The extra Rs 30.75 crore is money the profit ladder never mentions. In days, the cycle is inventory at 112.5 days on the cost of materials, receivables at 43.7 days on revenue, payables at 99.6 days on the cost of materials, a cash cycle of 56.6 days.
Step six is where a lot of real models discover a business with cash in the bank borrowing by year three, the cycle growing faster than the ladder generates. No such squeeze appears here. Sarvani Coatings closed year three with borrowings of Rs 240 crore against cash and investments of Rs 312 crore, so net debtBorrowings less cash and investments. When the figure is negative the business is in net cash, meaning it holds more cash than it has borrowed. is minus Rs 72 crore, and operating cash flow of Rs 304 crore covered capital spend of Rs 186 crore with Rs 118 crore to spare. Running the two together is what settles the question: the profit ladder alone could not have shown that the year went this way, and could not have shown the other outcome either.
A caterer feels this every wedding season. The vegetables, the staff and the hire charges all go out before the client settles, so more bookings is good news on the profit line and terrible news on the bank balance for about six weeks. Growth eats cash first and returns it later, and nothing about that appears in a forecast that only has a profit ladder in it.
A profit forecast runs five years and has no working capital line anywhere in it. Which assumption has been made?
Step seven
What goes into the assumption register?
Every chosen number, with four things beside it: its unit, the period it applies to, who chose it, and the event that would change it. Not the outputs. Not the formulas. The choices. The register is short even when the model is enormous, and that shortness is the useful part.
The two or three numbers that actually decide the answer become visible only once the register exists, and they are almost never the lines the model spent the most rows on. A model can have fifteen tabs of revenue build and still turn entirely on a single margin cell. The tabs do not show that. The dependence becomes visible the moment the choices are listed in one place, where four of them barely move the answer and one moves all of it.
Which job does the assumption register actually do?
Every single line in the model grows at the same rate. Where does the EBITDA margin land in the forecast year?
Step eight
How is a finished model checked for a view?
By breaking it on purpose. In the finished model every driver is overridden so that every line grows at the same rate, and the output is read. The test takes about five minutes, and it is the most useful thing in the whole build. Here is that test run on the forecast year.
| Grow everything at 9.18 per cent | Year three | Forecast year |
|---|---|---|
| Revenue | 2,415.00 | 2,636.70 |
| Cost of materials | 1,304.00 | 1,423.71 |
| Gross profit | 1,111.00 | 1,212.99 |
| Employee cost | 205.00 | 223.82 |
| Other expenses | 460.00 | 502.23 |
| EBITDA | 446.00 | 486.94 |
| EBITDA margin | 18.4679 pc | 18.4679 pc |
Every row moved. Every number in the worked column changed. The margin came out at 18.4679 per cent against the published year's 18.4679 per cent, identical to four decimal places. Arithmetically, that is exactly what the model was built to do. The model produced precisely nothing the published year had not already said. A forecast with no view inside it looks exactly like that, and a first attempt almost always takes that shape.
The alternative takes one line to state and is not built out here. The worked thesis on this business holds gross margin at 46.00 per cent on the claim that the gain was a level shift; a give back case returns it to year two's 44.01 per cent. On the same forecast revenue the gap between the two is Rs 52.59 crore of gross profit. Two different views of the business are expressed as one number in one cell. Neither case is asserted here, and neither becomes a view until somebody names the cell it lives in and defends the number in it.
The fifteen tab model that said nothing
An analyst spends six weeks on Sarvani Coatings Limited. Fifteen tabs, revenue driven off a single growth rate, every cost line linked to revenue, a balance sheet that balances, a cash flow that nets. The work is genuinely neat. Arithmetically, the model was built to produce an EBITDA margin identical to last year in every forecast year, and it does. The fifteen tabs are then taken into a meeting and presented as a view.
The cost is not that the numbers are wrong. The numbers are internally consistent, and they will survive any check anybody runs on them. The cost is that six weeks produced no claim a reasonable person could disagree with, and the one question worth answering, whether the margin gain holds, was never asked because the structure never made room for it.
The fix is step eight and it takes five minutes. Grow everything at one rate and see whether the answer changes. If it does not, the view has to be put in somewhere on purpose, in a cell somebody is willing to defend.
Who reads which step first
A lender goes straight to step six and reads it before the profit ladder. A credit team needs to know whether the business will need funding, and the answer sits in the cycle rather than in the margin: a business growing at 9.18 per cent with a 56.6 day cash cycle is going to put money into inventory and receivables before it takes any out, and a forecast without that line simply does not answer their question.
The register is the only part of the model a reviewer can actually engage with, so an analyst being reviewed goes to step seven. Nobody argues with a formula. People argue with a number, a period and a name beside it, and the register is what turns a spreadsheet into something that can be reviewed at all.
An investor reading somebody else's model runs step eight on it uninvited. The question is what the model says if every line grows together, and if the answer is roughly the same as the headline number on the front of the note, the work is arithmetic rather than research. The question is fast and slightly rude, and it is the one worth asking first.
Where the conduct rules sit
Research produced for others in India is conducted under the Securities and Exchange Board of India, then SEBI, and the exchanges at nseindia.com and bseindia.com are where a filed result becomes available to build a base year from. Periods, thresholds and disclosure requirements are set there and are amended. The current requirement stands in its own words at sebi.gov.in, and it governs.
A finished model produces almost exactly the answer expected before the build started. How much does that confirm?
Where each step hands off, and to whom
| The handoff | Whose subject it becomes | Site | Read on |
|---|---|---|---|
| Step one ties a published column. What that column had to contain and how each line was measured is decided elsewhere and only used here. | Institute of Chartered Accountants of India | icai.org | consulted 28 August 2026 |
| Step five stops at an absence. Whether a rate or a life for a new asset had to appear anywhere in the annual accounts is settled by the statutory frame, not by a model builder. | Ministry of Corporate Affairs | mca.gov.in | consulted 28 August 2026 |
| Step seven writes down who chose each number. What a person producing research for other people must then disclose about it is a conduct matter, set by SEBI and covered separately. | Securities and Exchange Board of India | sebi.gov.in | consulted 28 August 2026 |
| The base year in step one has to be downloaded from somewhere before it can be tied to anything. | National Stock Exchange of India | nseindia.com | consulted 28 August 2026 |
| The same result is lodged a second time, which matters when a prior year has to be pulled and one archive comes up short. | BSE Limited | bseindia.com | consulted 28 August 2026 |
Sarvani Coatings Limited, Nandivarman Paints Limited, Kesaria Surface Solutions Limited, Thottam Chemicals Limited, Meghna Iyer and Ravindra Setlur are invented.
Educational material. Not advice on any investment, tax, budget or market position.