The Forecast Model: Build a Full Year From Its Drivers

A forecast model takes a set of driver assumptions and returns a full profit ladder: revenue from volume and realisation, cost of materials from volume and input cost per unit, operating costs from their own growth rates, then depreciation, finance cost, other income, tax and earnings per share. It computes. The model does not value anything, and it reaches no view.

The calculator above is the whole machine. Ten inputs go in, fourteen lines come out, and each of the fourteen is a single arithmetic operation on the line above it. The meaning of a driver, and the reason an assumption has to carry a unit and a label, is covered under forecast drivers. How the revenue line is constructed, and how the cost lines are constructed, are each covered separately. The rest of this guide explains the machine: where each input is found, the order the ladder computes in, and the one step that most models skip.

Every output above is a computed line and an assumption list, and neither of those is a statement about what the shares are worth. A completed ladder is the artefact in equity research most likely to be mistaken for a conclusion, because it looks finished, it has a decimal point, and it ends in a figure per share. Anybody building a ladder should know exactly how far it goes.

What does the model actually take in?

Ten inputs, and the useful thing about the list is not its length but the fact that the ten split into three groups that are nothing like each other in how far they can be trusted. Some are read straight off a filed statement, so anybody who goes back to the same document gets the same number. Some are obtained by dividing one published figure by another, so they inherit every error either figure carried. And one or two are not published anywhere at all. The analyst assumes those, writes the assumption down, and carries it as an assumption for the rest of the model.

Think about a household deciding what next year costs. The rent is on the agreement, so it is read. The cost of a kilo of rice is not written down anywhere, so somebody works it out from the monthly grocery total divided by how much was bought. The division carries whatever the household forgot to count. And how much rice next year needs is a straight assumption about how many people will be eating. The rent is the most reliable number in the household budget and the least interesting, and the rice assumption is the reverse. The ten inputs below are shaped the same way.

Where is each input found, one note at a time?

Field note one: volume

Rarely a disclosed number. Volume turns up in management commentary, occasionally in a segment noteThe part of a filed statement that splits the business into its reporting parts and gives revenue, and sometimes profit, for each. The definition of a segment is settled in the accounting layer., and often nowhere at all. Sarvani Coatings Limited put volume growth of 6.0 per cent for the year ended 31 March of year three into its commentary, so this model has it. Many will not.

When volume is absent the model has an unobserved input, and that fact belongs on the face of the model rather than in a footnote nobody opens. A model with a blank cell labelled "volume, not disclosed" is honest and usable. A model that quietly replaces the blank with a growth rate borrowed from revenue has decided something, and has not told anybody it decided.

Field note two: realisation

Never disclosed anywhere by anybody. Realisation is revenue divided by volume, so it stands one division away from a published figure and one division away from a figure that may not be published at all. Every error the volume figure carries, realisation carries too, in the same direction and by the same proportion. A change in realisation, and what it implies about the business, is covered separately.

Field note three: input cost per unit

The cost of materials divided by units of output. Input cost per unit is the single term the whole margin question for this company turns on, and it is two divisions away from anything published. Neither the units nor the per unit cost appears in any filing; a published cost is divided by a figure that may itself have had to be assumed. The alternative is worse, so the distance is no reason to avoid the term. The distance is a reason to hold it loosely and to record on the model where it came from.

Field note four: the operating cost lines

Employee cost and other expenses are disclosed in the statements at the year, and other expenses is sometimes split into parts. For Sarvani Coatings in the year ended 31 March of year three, other expenses of Rs 460 crore carries advertising and sales promotion of Rs 121 crore and freight and distribution of Rs 138 crore inside it, both disclosed. The split is useful and it does not change the arithmetic.

Employee cost follows headcount and wage rates rather than revenue, and the model has to record which of the two it is assuming rather than leaving it implied by a growth rate. A number typed into a cell as ten per cent looks identical whether it came from a headcount plan or from a habit of growing costs with the top line. The cell cannot tell the difference. The label beside it can.

Field note five: depreciation, finance cost, other income and the tax rate

Depreciation and amortisationThe same idea as depreciation, applied to assets that cannot be touched, such as a brand or a licence. Which assets attract which charge, and over how long, is set by the accounting standards. is Rs 92 crore. Finance cost is Rs 21 crore. Other income is Rs 38 crore. The effective tax rateThe tax charge in the statement divided by the profit before tax in the same statement, expressed as a percentage. The effective rate is observed after the fact rather than set by statute, and it moves for reasons the ladder does not show. is the tax charge of Rs 93 crore over profit before tax of Rs 371 crore, which is 25.07 per cent. The first three are read straight off the filed statement for that year, and the fourth is a division of two of its lines.

Depreciation, finance cost and other income are the three easiest lines on the whole ladder to get right and the three that move the answer least, and they routinely attract more modelling effort than the lines that decide it. The ranking below measures one point on each input against what it does to the bottom line. The observation stands for now: on this ladder, effort and consequence run in opposite directions.

Field note six: the share count

From the reporting of the company itself, and confirmable at the exchanges. Sarvani Coatings has 24.00 crore shares in issue. The count moves with a corporate actionA bonus issue, a split, a buyback, a rights issue or anything else that changes the number of shares in issue. The effect of each, and the reason per share figures are restated afterwards, is settled in the listings sequence., so the count in the denominator and the period whose profit is being divided have to belong to each other.

In what order does the ladder compute?

In one order and in no other. The drawing below sets out all fourteen rungs with the single operation on each, running from revenue at the top through gross profitRevenue with the cost of materials taken out, before any operating cost at all. Gross profit and what it signifies are settled in the accounting layer. On this ladder it is simply the third rung. and EBITDAShort for earnings before four things have been subtracted: interest, tax, depreciation and amortisation. On this ladder it is just the rung reached once the three cost lines come out. down to the figure per share. Fourteen lines, one arithmetic step each, and nothing interpreted anywhere along the way.

The word "downwards" is doing real work in that drawing. No rung reads the one below it. The model can therefore be worked through with a pencil, and an error at rung two shows up at every rung after it and nowhere before it. Downward flow is what makes a model correctable at all. Each rung has exactly one driver behind it, so a wrong answer traces to a wrong assumption rather than to the model as a whole.

Why must the last published year be rebuilt before anything is forecast?

Rebuilding is the step a model usually omits, and omitting it is the reason so many models cannot be argued with. Before anything is forecast, the year that has already been published is reproduced from its own drivers, starting at the year before it, applying the derived rates, and walking the fourteen rungs.

If the rebuild does not tie to the rupee, the model is wrong, and a forecast sitting on top of a model that reproduces nothing is decoration with a decimal point. Notice the shape of that condition. The rebuild is not a matter of good practice. It is a test with a pass and a fail, applied before the interesting work starts, and it is the only test in the whole exercise whose answer is already known.

What does the rebuild look like, worked all the way through?

Sarvani Coatings Limited makes decorative paints and industrial coatings, and every figure here belongs to its case record. The two years below both ended 31 March, one year apart: year two is the starting point and year three is the published year being reproduced. No figure from one period is paired with a figure from another.

Every rate below is measured against the starting point, so take a moment over it. The year ended 31 March of year two closed with revenue of Rs 2,120 crore, cost of materials of Rs 1,187 crore and gross profit of Rs 933 crore. Employee cost was Rs 186 crore and other expenses Rs 407 crore, so EBITDA was Rs 340 crore. Depreciation of Rs 84 crore left earnings before interest and tax, or EBIT, of Rs 256 crore, and finance cost of Rs 24 crore against other income of Rs 31 crore gave profit before tax of Rs 263 crore. Tax of Rs 66 crore left profit after tax of Rs 197 crore, or Rs 8.21/- a share. The eleven figures above are the base every multiplication below is applied to.

The three driver rates were derived rather than read. Revenue rose 13.915 per cent across the one year and volume rose 6.0 per cent. Dividing 1.13915 by 1.06 gives 1.07467, so realisation rose 7.467 per cent. The cost of materials rose from Rs 1,187 crore to Rs 1,304 crore on the same 6.0 per cent more volume, so input cost per unit rose 3.638 per cent. Employee cost rose 10.215 per cent and other expenses rose 13.022 per cent, each straight from one published figure over the one before it. The gross marginThe share of every revenue rupee still standing once the cost of materials has come out. Whether a movement in it was won by the company or handed to it by the whole field is covered under margin analysis. arithmetic ties too: materials at 56.0 per cent of revenue in year two, scaled by 1.03638 over 1.07467, lands on 54.0 per cent in year three exactly.

Two columns, fourteen rows, no differences. Be precise about what that has and has not proved. The rebuild has not proved the business is well understood. It has proved that the three inputs which did the real work, volume and realisation and input cost per unit, are consistent with every published line for that year. The three inputs that did the work were all derived rather than read, and a model that reproduces the year has demonstrated that the derivation was right. Reproducing a year is not a formality.

Which inputs are worth the effort, and which are not?

The observation from field note five can now be given a number. In the calculator above, the rebuilt year is taken, one input is moved by one point, the other nine are left alone, and the effect on profit after tax is read off. A point means one percentage point on a growth rate or a tax rate, and one crore of rupees on the three lines entered as amounts. Twenty five paise in every rupee of pre tax profit goes to tax at 25.07 per cent, so roughly seventy five paise of any pre tax movement reaches the bottom, and that one fact is what turns nine of the ten inputs into comparable numbers. The tenth, the share count, has no point to move.

The three inputs no line of a filed statement supplies sit at the top of that ranking and the six that come out of filed lines sit at the bottom. The arrangement is uncomfortable, and it does not change however carefully the bottom six are modelled. One point on realisation moves profit after tax by about as much as twenty two crore rupees of finance cost would. Time spent refining finance cost to the second decimal place is precision applied exactly where it cannot matter.

How does anybody actually use this?

Meghna Iyer, an invented analyst covering coatings, does not build this model to produce a figure per share. She builds it because a question has been asked that only a full ladder can answer, and the ladder is the cheapest way to find out which assumption the answer is actually resting on. When she reports the model, what travels is never the last line on its own. The last line travels with the ten inputs beside it and a note saying which two were derived and which one was assumed.

The output that gets handed over is the assumption list, and the computed ladder is the receipt showing that the assumptions were applied consistently. A colleague who disagrees with her can then point at one input and say why, rather than disbelieving a spreadsheet in general.

The same shape turns up outside research. A lender assessing a working capital facility runs a version of this on a borrower to see whether the cost lines the borrower has assumed are the ones that actually move, and the lender is far more interested in the two derived inputs than in the four read straight off the statement. A household planning a year does exactly the same arithmetic with three rungs instead of fourteen: money in, the costs that move with how much is used, the costs that move on their own, and what is left. And a household that has never once checked last year's plan against what last year actually cost is in precisely the position of a model that was never rebuilt.

What does this model refuse to do?

The model computes fourteen lines and it stops. There is no discounting in this calculator, no discount rateThe rate at which a future rupee is converted into a rupee today. The discount rate, and how one is arrived at, belongs to the valuation method layer. in any cell, and no valuation multipleA ratio of a share price or an enterprise value to some line of the ladder. Choosing one and applying it is a separate method, covered separately. applied to any output. Fair value, the price of a share and any rating built on it belong to the valuation method layer.

The output is a set of lines and the assumptions that produced them, and the reader is the one who has to defend the assumptions. A forecast model converts a set of beliefs into their arithmetic consequence, precisely and repeatably. The model has no opinion about whether the beliefs are any good, and the fact that the last line comes out to two decimal places does nothing whatever to improve them.