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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.