Building a Model in a Spreadsheet

Why this matters

The lessons so far have used worked examples that were already built. Building one yourself is a different experience. It forces you to decide where each number comes from, and it exposes which of your assumptions carry the conclusion. You don't need special software or advanced formulas. Any spreadsheet program and four arithmetic operations are enough.

What a model is

A model answers a question of the form "if these things are true, what follows?" Its answer is exactly as reliable as its assumptions. Its real value is in letting you change an assumption and see the effect at once. That's how you find out that the value of a company depends heavily on its margin and hardly at all on something you'd spent an hour researching.

Layout

Three rules prevent most errors.

1. Separate inputs, calculations, and outputs. Put every assumption together in one block at the top or on its own sheet. Calculations refer to that block. Outputs, such as free cash flow and value per share, sit apart where they're easy to read.
2. Each assumption appears once. If the tax rate is typed in six different cells, sooner or later five get updated and one doesn't. Type it once and point every formula at that cell.
3. No numbers hidden inside formulas. A formula that multiplies by 1.1 hides a growth assumption. Put the 10% in the inputs and refer to it.

Some conventions make a model easier to read: time runs across the columns, one year each; the same row always holds the same item; units are stated once at the top; and inputs are shown in a different colour from formulas, so anyone can see at a glance what was assumed and what was calculated.

Drivers

A forecast line shouldn't be a guess at a dollar amount. It should be a simple relationship to something else, called a driver. That keeps the model consistent when an assumption changes: if sales are higher, costs, inventory, and investment all rise with them automatically.

A small model of STU Corp

Here are the inputs for a three-year forecast of STU Corp, the fictional company from Part 3. Each one is taken from this year's actual figures, so the starting assumption is that the business carries on as it is.

Assumption	Value	Based on this year's figures
Revenue growth	10%	The base case in the DCF lesson
Operating margin	20%	$80 million ÷ $400 million
Interest expense	$5.4 million	6% on $90 million of debt
Tax rate	21.6%	$16 million ÷ $74 million
Depreciation, share of revenue	6.25%	$25 million ÷ $400 million
Stock-based compensation, share of revenue	1.25%	$5 million ÷ $400 million
Working capital, share of revenue	10%	$40 million ÷ $400 million
Capital spending, share of revenue	10%	$40 million ÷ $400 million

	Year 1	Year 2	Year 3	How it's calculated
Revenue	440.0	484.0	532.4	Last year × (1 + growth)
Operating income	88.0	96.8	106.5	Revenue × margin
Interest	(5.4)	(5.4)	(5.4)	Input
Tax	(17.8)	(19.7)	(21.8)	(Operating income − interest) × tax rate
Net income	64.8	71.7	79.2	Operating income − interest − tax
Depreciation	27.5	30.3	33.3	Revenue × share
Stock-based compensation	5.5	6.1	6.7	Revenue × share
Increase in working capital	(4.0)	(4.4)	(4.8)	Increase in revenue × share
Cash from operating activities	93.8	103.6	114.3	Sum of the four lines above
Capital expenditures	(44.0)	(48.4)	(53.2)	Revenue × share
Free cash flow	49.8	55.2	61.1	Operating cash flow − capital spending

Every figure traces back to one of eight inputs. Free cash flow grows 13% in the first year and about 11% after that, a little faster than revenue, because interest stays flat while the business grows. The simple DCF assumed 10% a year. The model shows where that number comes from and what could move it.

Checks

A spreadsheet will calculate a wrong formula as readily as a right one. Build in ways to catch mistakes.

• Reproduce the past first. Before forecasting, apply the same formulas to the latest actual year and confirm they give the company's reported figures. STU Corp's actual net income was $58 million and its free cash flow $44 million. If the model's version of that year shows something else, find out why before going further.
• Add things two ways. If a column can be totalled down and across, do both and compare. If you forecast a balance sheet, add a row that subtracts liabilities and equity from assets. It should always show zero, as How the Statements Connect explained.
• Try extreme inputs. Set growth to zero, then to 50%. Set the margin to zero. If the results don't move the way common sense says, a formula is wrong.
• Compare with history. A forecast margin above anything the company or its competitors have achieved needs a reason.
• Read the year-to-year changes. Growth rates of each line, placed under the line, show jumps that a column of dollar figures hides.

Finding what matters

With the model built, change one input at a time and note the effect on an output.

Change	Year 3 free cash flow	Difference from 61.1
Capital spending at 12% of revenue instead of 10%	50.4	−10.7
Operating margin of 18% instead of 20%	52.7	−8.4
Revenue growth of 5% instead of 10%	54.6	−6.5
Working capital at 15% of revenue instead of 10%	58.7	−2.4

Two points of capital spending or two points of margin matter more than halving the growth rate. Working capital barely registers. That tells you where to spend your research time: on what STU Corp has to invest to keep growing, and whether its margin will hold, more than on the exact growth figure.

A spreadsheet's data table feature produces a grid like this for two inputs at once, and a single cell that selects between sets of inputs lets you switch between the bear, base, and bull cases from Scenario Analysis: Bull, Base, and Bear Cases.

Keep it small

It's tempting to add detail: revenue by product and region, a dozen cost lines, quarterly columns. Each addition is a new assumption to justify and a new place for an error. Add detail only where it changes the answer. If revenue by segment matters, as it does for a company like the one in Sum-of-the-Parts Valuation, model the segments. If it doesn't, one line is better.

Write a note beside each input saying where it came from: last year's figure, a five-year average, management's guidance, or your own judgment. In six months you won't remember, and the note is what lets you update the model honestly when results arrive.

Key takeaways

• A model shows what follows from your assumptions and which of them matter. It doesn't predict.
• Keep inputs in one place, use each once, and never hide a number inside a formula.
• Build each line from a driver, so the model stays consistent when an assumption changes.
• Reproduce the latest actual year before forecasting, and add checks that should always hold.
• Change one input at a time to find where the answer is sensitive, and keep the model small.

Check your understanding

You've built a model and applied it to the company's latest actual year. It shows net income of $72 million, but the company reported $58 million. What should you do?

1. Carry on forecasting, since small differences in the past don't affect the future.
2. Find the cause first, since the same formulas will carry the error into every year.
3. Change the reported figure in the model, since the model's logic is more consistent.

In a model, changing revenue growth from 8% to 12% moves the value per share by 5%. Changing the operating margin from 15% to 13% moves it by 20%. What does that tell you?

1. The model is broken, since growth should always matter more than margins do.
2. The growth assumption should be raised until it has more effect on the result.
3. The margin deserves most of your research, since the value depends on it far more.

What's next

This model took its inputs from STU Corp's tidy figures. A real company's inputs are scattered through a long filing. Building a DCF from a 10-K shows where to find each one.