Forecasting Performance

2
Presentation Overview
In this presentation, we focus on the mechanics of forecastingspecifically, how to
develop an integrated set of financial forecasts that reflect the companys expected
performance. This presentation covers:
1. The appropriate level of detail. The typical forecast will e split into three
time periods: the explicit forecast, a forecast of !ey value drivers, and
continuing value.
2. How to build a well-structured spreadsheet model: one that separates
raw inputs from computations, flows from one wor!sheet to the next, and is
flexile enough to handle multiple scenarios.
3. The mechanics of the forecasting process. To arrive at future cash flow,
we forecast the income statement, alance sheet, and statement of
retained earnings. The forecasted financial statements provide the
information we need for computing "#I$ and free cash flow.
%
The Length and Detail of the Forecast
&efore you egin forecasting individual line items, you must determine how many
years to forecast and how detailed your forecast should e. The typical forecast is
ro!en into three time periods:
' simplified forecast
for the remaining
years, focusing on a
few important
variales, such as
revenue growth,
margins, and capital
turnover.
Today
' detailed () to *)year
forecast, which
develops complete
alance sheets and
income statements with
as many lin!s to real
variales +e.g., unit
volumes, cost per unit,
as possile.
Years 1-5 Years 6-15 Years 15+
-alue the remaining
years y using a
perpetuity)ased
formula, such as the !ey
value driver formula.
.
The Length and Detail of the Forecast
The explicit forecast period must e long enough for the company to reach
a steady state, defined y the following characteristics:
The company grows at a constant rate and reinvests a constant proportion of
its operating profits into the usiness each year.
The company earns a constant rate of return on new capital invested.
The company earns a constant return on its ase level of invested capital.
In general, we recommend using an explicit forecast period of /0 to /(
years perhaps longer for cyclical companies or those experiencing very
rapid growth.
1sing a short explicit forecast period, such as ( years, typically results in a
significant undervaluation of a company or re2uires heroic long)term growth
assumptions in the continuing value.
(
"aw historical data
Integrated financials statements
3orecast ratios
4ar!et data 5 6'$$
"eorgani7ed financials
"#I$ 5 free cash flow
-aluation summary
In your model, data
should generally flow
in one direction
The valuation spreadsheet can
easily ecome complex. Therefore,
you need to design and structure
your model efore starting to
forecast.
6ell)uilt valuation models have
certain characteristics.
3irst, original data and user input
are collected in only a few
places.
8enote raw data or user input in
a different color.
1nless specified as data input,
numers should never e hard)
coded into a formula.
Co!onents of a "ood #odel
9
/. "aw historical data from company financials.
2. Integrated financials ased on raw data.
%. :istorical analysis and forecast ratios.
.. 4ar!et data and 6'$$ analysis.
(. "eorgani7ed financial statements +into ;#<='T and Invested $apital,.
9. "#I$ and 3$3 using reorgani7ed financials.
*. -aluation summary including enterprise 8$3, economic profit and
e2uity valuation computations.
4any spreadsheet designs are possile. In the valuation example from the
last slide, the >xcel wor!oo! contains seven wor!sheets:
Co!onents of a "ood #odel
*
'lthough the future is un!nowale, careful analysis can yield insights into how a
company may develop. 6e rea! the forecasting process into six steps:
1. Prepare and analyze historical financials. &efore forecasting future financials,
you must uild and analy7e historical financials. In many cases, reported
financials are overly simplistic. 6hen this occurs, you have to reuild financial
statements with the right alance of detail.
2. uild the revenue forecast. 'lmost every line item will rely directly or indirectly
on revenue. ?ou can estimate future revenue y using either a top)down +mar!et)
ased, or ottom)up +customer)ased, approach. 3orecasts should e consistent
with historical evidence on growth.
3. !orecast the income statement. 1se the appropriate economic drivers to
forecast operating expenses, depreciation, interest income, interest expense, and
reported taxes.
Overview of the Forecasting Process
@
6e rea! the forecasting process into six steps:
". !orecast the balance sheet# invested capital and nonoperating assets. #n the
alance sheet, forecast operating wor!ing capital, net property, plant, 5 e2uipment,
goodwill, and nonoperating assets.
$. !orecast the balance sheet# investor funds. $omplete the alance sheet y
computing retained earnings and forecasting other e2uity accounts. 1se cash
andAor det accounts to alance the cash flows and alance sheet.
%. &alculate '()& and !&!. $alculate "#I$ to assure forecasts are consistent with
economic principles, industry dynamics, and the companys competitive
advantage. To complete the forecast, calculate free cash flow as the asis for
valuation. 3uture 3$3 should e calculated the same way as historical 3$3.
Overview of the Forecasting Process
Let$s e%aine each ste! in detail&
B
'te! 1( Pre!are )istorical Financials
Cource: &oeing /0)D, 200%
*alance sheet +, illion-
*ccounts payable and other liabilities
'dvances in excess of related costs
Income taxes payale
Chort)term det and current portion of =T8
C.rrent lia/ilities
0ote 11 - 2cco.nts !aya/le and other lia/ilities
'ccounts payale
'ccrued compensation and employee enefit costs
<ension liailities
<roduct warranty liailities
=ease and other deposits
8ividends payale
#ther
2cco.nts !aya/le and other lia/ilities
1334
/%,(9%
%,.9.
2**
/,/..
156775
%,@22
2,B%0
/,/%@
@2(
%/9
/.%
.,%@B
146564
&oeings alance sheet reports
what appears to e an operating
line item, ut it is actually a
mixture of operating,
nonoperating, 5 financingE
1
operating liaility
nonoperating liaility
source of financing
)istorical
financials
"evenue
forecast
Income
statement
&alance
sheet
"etained
earnings
"#I$ and
3$3
To start the forecasting process, collect raw historical data and uild the financial
statements in a spreadsheet
&e sure to analy7e and scru historical data. ?ou dont want more detail than
necessary and you should not unwittingly aggregate operating and nonoperating items.
/0
'te! 1( *.ild the 8even.e Forecast
$reating a good revenue forecast is critical ecause most forecast ratios are directly
or indirectly driven y revenue. The revenue forecast should e dynamicF constantly
re)evaluate as new information ecomes availale.
To uild a revenue forecast, use a top)down forecast, in which you start with the total
mar!et, or use a ottom)up approach, which starts with the companys own forecasts.
*OTTO#
9P
TOP
DO:0
/. >stimate 2uantity and
pricing of aggregate
worldwide mar!et
2. >stimate mar!et
share and pricing
strength ased on
competition and
competitive advantage
'evenue
!orecast
/. <roGect demand
from existing
customers
%. >xtend short)term
revenue forecasts to
long)term
2. >stimate new
customer wins and
turnover
'evenue
!orecast
:istorical
financials
8even.e
forecast
Income
statement
&alance
sheet
"etained
earnings
"#I$ and
3$3
//
'te! 1( >stimate the
forecast ratio. 3or
simplicity, we start with an
Has)isI forecast.
6ith a revenue forecast in place, next
forecast individual line items related to the
income statement. To forecast a line item, use
a three)step process:
+ecide what economically drives the
line item. 3or most line items, forecasts
will e tied directly to revenue.
,stimate the forecast ratio. Cince cost
of goods sold is tied to revenue, estimate
$#JC as a percentage of revenue.
#.lti!ly the forecast ratio /y an
estiate of its driver; 3or instance, since
most line items are driven y revenue,
most forecast ratios, such as $#JC to
revenue, should e applied to estimates of
future revenue.
Forecast wor<sheet
<ercent
"evenue growth
Costs of goods sold = reven.es
CJ5' A "evenues
8epreciation A ;et <<5>
1337
20.0
4>;5
/@.@
*.B
1335?
20.0
4>;5
:istorical
financials
"evenue
forecast
@ncoe
stateent
&alance
sheet
"etained
earnings
"#I$ and
3$3
'te! 4( Forecast the @ncoe 'tateent
'te! 1( $hoose a
forecast driver and
compute historical ratios
/2
@ncoe
stateent
"evenues
Cost of goods sold
CJ5'
8epreciation
>&IT
Interest expense
Interest income
;on operating income
>arnings efore taxes +>&T,
Taxes on >&T
;et income
1337
2.0.0
+A3;3-
+.(.0,
+/B.0,
@9.0
+2%.0,
(.0
..0
*2.0
+2..0,
[email protected]
1335?
2@@.0
+135;3-
K 4illion
:istorical
financials
"evenue
forecast
@ncoe
stateent
&alance
sheet
"etained
earnings
"#I$ and
3$3
#.lti!ly the forecast ratio /y an
estiate of its driver;
3or instance, since most line items
are driven y revenue, most forecast
ratios, such as $#JC to revenue,
should e applied to estimates of
future revenue.
This why a good revenue forecast is
critical. 'ny error in the revenue
forecast will e carried through the
entire model.
37.5%
240
90
Revenues
COGS
Ratio Forecast
2004
2004
= = =
108 288 % 5 . 37 Revenues Ratio Forecast COGS
2005E 2005E
= = =
'te! 4( Forecast the @ncoe 'tateent
'te! 4( 4ultiply the
forecast ratio y next years
estimate of revenues +or
applicale forecast driver,
/%
The appropriate choice for a forecast driver depends on the company and the
industry in which it competes. &elow is some guidance on typical forecast drivers
and forecast ratios for the most common financial statement line items.
:istorical
financials
"evenue
forecast
@ncoe
stateent
&alance
sheet
"etained
earnings
"#I$ and
3$3
O!erating
0on
o!erating
Line ite
$ost of goods sold +$#JC,
Celling, Jen, 'dmin +CJ5',
8epreciation
;onoperating income
Interest expense
Interest income
8ecoended
forecast driver
"evenue
"evenue
<rior year net
property, plant, and
e2uipment +<<5>,
'ppropriate
nonoperating asset, if
any
<rior year total det
<rior year excess
cash
8ecoended
forecast ratio
$#JC A revenue
CJ5' A revenue
8epreciation A net <<5>
;onoperating income A
nonoperating asset or growth
in nonoperating income

Interest expense
t
A
total det
t)/

Interest expense
t)/
A
excess cash
t)/
'te! 4( Forecast the @ncoe 'tateent
@ncoe 'tateent Forecast 8atios
/.
Forecast wor<sheet
<ercent
"evenue growth
$osts of goods sold A revenues
CJ5' A revenues
8epreciation Arevenues
>&IT A revenues
1337
20.0
%*.(
/@.@
>;A
%(.@
1335?
20.0
%*.(
/@.@
%(.@
@ncoe stateent
"evenue
$ost of goods sold
Celling, general and admin
8epreciation
>&IT
1337
173;3
+B0.0,
+.(.0,
+1A;3-
@9.0
1335?
155;3
+/[email protected],
+(..0,
/0%.2
K 4illion
:istorical
financials
"evenue
forecast
@ncoe
stateent
&alance
sheet
"etained
earnings
"#I$ and
3$3
?%a!le 1( Forecast De!reciation
7.9%
240
19
Revenues
on Depreciati
Ratio Forecast
2004
2004
= = =
2005E 2005E
Revenues Ratio Forecast on Depreciati =
To forecast depreciation, you have three
options. ?ou can forecast depreciation
as a percentage of revenue or as a
percentage of property, plant, and
e2uipment.
3or simplicity, lets forecast next years
depreciation using an Has)isI percentage
of revenues.
'te! 4( Forecast the @ncoe 'tateent
/(
Liabilities and equity
Chort)term det
=ong)term det
=iailities and e2uity
22..0
@0.0
..0.0
2/%.
0
@0.0
.90.
0
Assets
6or!ing cash
>xcess cash


Total assets
1334
(.0
/00.0
..0.0
1337
(.0
90.0
.90.0
@9.0
+2%.0,
(.0
..0
*2.0
/0%.2

(.%
@B..
Condensed /alance sheet
Condensed incoe stateent
>&IT
Interest expense
Interest income
;on operating income
>arnings efore taxes +>&T,
1337 1335? K 4illion
:istorical
financials
"evenue
forecast
@ncoe
stateent
&alance
sheet
"etained
earnings
"#I$ and
3$3
'te! 4( Forecast the @ncoe 'tateent
?%a!le 1( @nterest ?%!ense
7.%
80 224
23
De!t "ota#
E$pense %nterest
Ratio Forecast
2003
2004
=
+
= =
2004 2005E
De!t "ota# Ratio Forecast E$pense %nterest =
?%a!le 4( @nterest @ncoe
5.0%
100
5
Cas' E$cess
%nco(e %nterest
Ratio Forecast
2003
2004
= = =
2004 2005E
Cas' E$cess Ratio Forecast %nco(e %nterest =
1335?
/9
:istorical
financials
"evenue
forecast
Income
statement
*alance
sheet
"etained
earnings
"#I$ and
3$3
'te! 7( Forecast the *alance 'heet
/,000
/00
Year 1
/,/00
/0(
Year 1
/,200
//*
Year 4
/,%00
/%(
Year 7
/0.0L B.(L B.@L /0..L
(.0L /2.0L /@.0L
"evenue +K,
'ccounts receivale +K,
'toc< ethod
'ccounts receivale as a
percentage of revenue
Flow ethod
$hange in accounts receivale
as a percentage of the change in
revenue
Forecasting 2cco.nts 8eceiva/le( 2n ?%a!le
The stoc<
ethod leads
to less
variation
To forecast the alance sheet, start with invested capital and nonoperating assets.
>xcess cash and sources of financing, such as det, will e handled in the next step.
6hen forecasting alance sheet items, use the stoc! method. The relationship
etween alance sheet accounts and revenue +the stoc! method, is more stale than
the change in accounts versus revenue +the flow method,.
/*
=ets use these drivers to forecast wor!ing cash and net <<5>M
:istorical
financials
"evenue
forecast
Income
statement
*alance
sheet
"etained
earnings
"#I$ and
3$3
'ccounts receivale
Inventories
'ccounts payale
'ccrued expenses
;et <<5>
Joodwill
;onoperating assets
<ension assets or liailities
8eferred taxes
Ty!ical forecast driver
"evenue
$ost of goods sold
$ost of goods sold
"evenue
"evenue
'c2uired revenues
;one
;one
'dGusted taxes
Ty!ical forecast ratio
'ccounts receivale A revenue
Inventories A $#JC
'ccounts payale A $#JC
'ccrued expenses A revenue
;et <<5> A revenue
Joodwill A ac2uired revenue
Jrowth in nonoperating assets
Trend towards 7ero
$hange in deferred taxes A adGusted taxes
O!erating line ites
0ono!erating line ites
To forecast the alance sheet, start with items related to invested capital and
nonoperating assets. &elow, we present forecast drivers and forecast ratios for the
most common line items.
'te! 7( Forecast the *alance 'heet( @nvCa!
/@
'te! 7( Forecast the *alance 'heet( @nvCa!
:istorical
financials
"evenue
forecast
Income
statement
*alance
sheet
"etained
earnings
"#I$ and
3$3
?%a!le 1( Forecasting wor<ing cash
2.1%
240
5
Sa#es
Cas'
Ratio Forecast
2004
2004
= = =
2005E 2005E
Sa#es Ratio Forecast Cas' =
?%a!le 1( Forecasting net PPB?
104.2%
240
250
Sa#es
E ) ** +et
Ratio Forecast
2004
2004
= = =
2005E 2005E
Sa#es Ratio Forecast E ) ** +et =
K 4illion
Cash
>xcess cash
Inventory
$urrent assets
0et PPB?
>2uity investments
Total assets
1337
5;3
90.0
.(.0
//0.0
153;3
/00.0
763;3
1335?
(..0
/00.0
763;3
Partial *alance sheet
Partial @ncoe stateent
1337 1335? K 4illion
"evenues 2.0.0 2@@.0
/B
To complete the alance sheet, forecast the companys sources of financing. To
do this, first rely on the rules of accounting. 1se the principle of clean surplus
accounting: ">
tN/
O ">
t
N ;et Income P 8ividends.
Increasing the dividend payout ratio should !eep excess cash at reasonale levels.
'ltering the payout policy, however, should not affect the value of operations in an
enterprise 8$3. If it does, your model is inconsistent with the principles of enterprise
8$3.
To forecast
retained earnings6
yo. .st generate
a forecast of
dividend !ayo.t
These are driven
/y other
forecasts6 and
sho.ld not /e
re-estiated;
Ctarting retained earnings
;et income
8ividends declared
>nding retained earnings
8ividendAnet income +percent,
1334
%9.0
%9.0
+/9.0,
(9.0
....L
1337
(9.0
[email protected]
+22.0,
@2.0
75;5C
1335?
@2.0
(B..
+2*.2,
//..2
75;5C
, #illion
:istorical
financials
"evenue
forecast
Income
statement
&alance
sheet
8etained
earnings
"#I$ and
3$3
'te! 5( Forecast *alance 'heet( The Pl.g
20
't this point, five line items remain: excess cash, short)term det, long)term det, a
new account titled newly issued det, and common stoc!.
Come comination of these line items must ma!e the alance sheet alance. 3or
this reason, these items are often referred to as Hthe plug.I
Cimple models use newly issued det as the plug.
'dvanced models use excess cash or newly issued det, to prevent det from
ecoming negative.
8eaining
2ssets
8eaining Lia/ilities
B
'hareholders$ ?D.ity
?%cess Cash 0ewly @ss.ed De/t The Pl.g
+for simple
models,
The Pl.g
+use I3AT:>;
statement for
advanced
models,
*alance 'heet
:istorical
financials
"evenue
forecast
Income
statement
&alance
sheet
8etained
earnings
"#I$ and
3$3 'te! 5( Forecast *alance 'heet( the Pl.g
2/
'te! 5( Forecast *alance 'heet( the Pl.g
'te! 1( 8etermine retained earnings
using the clean surplus relation,
forecast existing det using
contractual terms, and !eep e2uity
constant.
'te! 1( Test which is higher, assets
excluding excess cash or liailities
and e2uity, excluding newly issued
det.
'te! 4( If assets excluding excess
cash are higher, set excess cash
e2ual to 7ero and plug the difference
with newly issued det. #therwise,
plug with excess cash.
1se excess cash or newly issued det to HplugI the alance sheet.
:istorical
financials
"evenue
forecast
Income
statement
&alance
sheet
8etained
earnings
"#I$ and
3$3
$ash
>xcess cash
Inventory
$urrent assets
;et <<5>
>2uity investments
Total assets
Lia/ilities and eD.ity
'ccounts payale
Chort)term det
$urrent liailities
=ong)term det
;ewly issued det
$ommon stoc!
"etained earnings
Total lia/ilities and eD.ity
1334
(.0
/00.0
%(.0
/.0.0
200.0
/00.0
773;3
/(.0
22..0
2%B.0
@0.0
0.0
9(.0
(9.0
773;3
1337
(.0
90.0
.(.0
//0.0
2(0.0
/00.0
763;3
20.0
2/%.0
2%%.0
@0.0
0.0
9(.0
@2.0
763;3
1335?
9.0
(..0
%00.0
/00.0
2..0
2/%.0
2%*.0
@0.0
9(.0
//..2
*alance 'heet
Pl.g
Pl.g
22
#nce you have completed your
income statement and alance
sheet forecasts, calculate "#I$
and 3$3 for each forecast year.
This process should e
straightforward if you already
computed "#I$ and 3$3
historically.
Cince a full set of
forecasted financials are
availale, merely copy the
two calculations across
from historical financials to
proGected financials.
:istorical
financials
"evenue
forecast
Income
statement
&alance
sheet
"etained
earnings
8O@C and
FCF
'te! 6( Calc.late 8O@C and FCF
2%
6hen forecasting you are li!ely to come across three additional issues:
1; 0onfinancial o!erating drivers; In industries where prices or technology are
changing dramatically, your forecast should incorporate operating drivers li!e
volume and productivity.
$onsider the airline industry, where laor and fuel has een rising as a
percentage of revenue P ut for different reasons. 3uel is a greater
percentage ecause oil prices have een rising. $onversely, laor is a
greater percentage ecause revenue per seat mile has een dropping.
1; Fi%ed vers.s varia/le costs; The distinction etween fixed and variale costs
at the company level is usually unimportant ecause most costs are variale.
3or individual production facilities or retail stores, this is not the case, most
costs are fixed.
4; @nflation; #ften, the cost of capital is estimated using nominal terms. If this is
the case, forecast in nominal terms. &e careful, however, high inflation will
distort historical analyses.
Other @ss.es in Forecasting
2.
To value a companys operations using enterprise 8$3, we discount each years
forecast of free cashflow for time and ris!. In this presentation, we analy7ed a six)
step process for forecasting a companys financials, and suse2uently its free cash
flow.
6hile you are uilding a forecast, it is easy to ecome engrossed in the details of
individual line items. &ut we stress, once again, that you must place your aggregate
results in the proper context.
'lways chec! your resulting revenue growth and "#I$ against industry)wide
historical data. If re2uired forecasts exceed other companys historical
performance, ma!e sure the company has a specific and roust competitive
advantage.
3inally, do not ma!e your model more complicated than it needs to e. >xtraneous
details can cloud the drivers that really matter. #nly create detailed line item
forecasts when they increase the accuracy of the companys !ey value drivers.
Closing Tho.ghts