Valuation Exercise Estimating Free Cash Flow for Cisco

Suffering through the pain and agony of creating all of these schedules longhand is reminiscent of the third grade at Saint Michael's Elementary School in Levittown, PA in 1958. Sister Mary Ignatius would be impressed by the ease with which a spreadsheet solves the divine

Vilm* Pin 3B2 Gtntul Input Scrtrii iBHriflifc Stock VjIuc S1Û.87 Gtnrral Inputs

Company TkHw

csco

ËxCGSS Rêlurt F^riûd [yMiS)

10

Oatfrsiisftsfl Raw (Ife of Rsv J

PU

Riwenws (ft*!)

MH

IrwSEmem ftae gi RIJY ]

Gro*Di Rate (%)

» te

Working Capital o( Rev)

963

Mer Operating PïolU Margin (%)

1641

Short-Team AstttS (f mil]

IÎ4Ï3

Tat Rate (%)

34 ï

Short-Tetin uabilitisstfrnil)

B3J5

$K>£k Pnee{()

II«

Eirsiy ftksfc Rf-flrrHiirt (%)

300

Shsr« Qutslenctng (ml)

7447

CQfrpany Bi[a

1

iQ-ysarTreeswy Yirid (%)

4 35

Vaiui of MX Out (imil]

S

Sptead [o Tre-asmy (%)

OK

value or Fver Stock Otf (f mil]

0

Preferred Slock YheW fftj

CR

Company WACC(%)

¡ou

EXHIBIT 5-5 Cisco General Input Screen

EXHIBIT 5-5 Cisco General Input Screen mystery of stock valuation with a few clicks of the miraculous mouse! Exhibit 5-5 shows the general input screen from the ValuePro 2002 integrated valuation software program. In this valuation, we use the analyst estimated growth rate of 20 percent per year, and all of the inputs that we have listed previously.

Given the inputs, ValuePro 2002 produces the general pro forma cash flow schedule for the excess return period that you specify—10 years in Exhibit 5-6.

Revenue, compounded at its estimated 20-percent rate growth, is shown in column 3. NOP, found by multiplying revenue by the NOPM of 16.41 percent, is shown in column 4. Adjusted taxes, equal to NOP times the tax rate of 34.36 percent, is shown in column 5. NOPAT, equal to NOP minus adjusted taxes (column 4 minus column 5) is shown in column 6.

New investment, equal to revenue times the 9.96-percent estimated investment rate, is shown in column 7. Depreciation, equal to revenue times the 8.31 percent estimated depreciation rate, is shown in column 8. And net investment, equal to new investment minus depreciation (column 7 minus column 8) is shown in column 9. The change in working capital, equal to the change in yearly revenue times the incremental working capital rate of 9.63 percent, is shown in column 10.

Grntul Pia Tnni'j äcrMfl IP-wj-i Fvf« l^iuirT^ruiJ

CStQ

Grntul Pia Tnni'j äcrMfl IP-wj-i Fvf« l^iuirT^ruiJ

CStQ

MC ÛWUHOfiPn4FÏÏF

I7iit3

T-lilJ ^D^fTl

jp Vik#

rajit

PKimK hiiijn VJ1U1

MBJ&S

H

>LJ| T|.m Aurli

11T.ÎÏ1Û

«

TMK :<waiVjhK

Lu 1 EhKl-T«m LjfcWc*i

1

(B3Î91

1 llf VitH lr ( HtW L>J|,

IJ.i. 933

Inlimtt SMa V4»H

1

tiouj

a

(31

H

K

m

»

ra

<ll>

(13i

4131

17 Uarthx

At

HlVl^J

Iii«

idvi

pMr^i

Jr3P

ll-tk

ND^ftf

1—1

□ iu'l*

[ml

rar

FHLH

tat

OVTiriOV)

in

(lniiSWi

hjm

S7S

1 A*

IXi

1JÏ(.

apt

M

in

own

1 J»

aini^M

H-'ll

tSE

7.9M

an

<49

437

ijixr

DES

H9I

DlrtJflJOfi

ïïJ

IfUj

JJ1E

ES

7.H<7

(>7*5

Iii*

uiiir.^nv

SJS

«¿31

»11

si«

Hi

S

if*

DWLT

ion

Dirnaus

CfET

77-*

'-SB*

iflTE

ilea

îfll

nr

BS

7,133

Hi.-liflKB

11 «

LW

1U!

•fin

SW

BP

5

Î.Bi

DlflllTCl»

&/7Z

11,172

tfn

7P0D

&JÏ3

SET;

11 IB

UEO

oon

D5U9

7006

Dl.'I'.CCI!

J91

J.w

6.IÜ1

.-. v.',

1 Ml

IJX

Giniitti:

ViSi

»016

UU

^USU

■i/Jl

a ne

1JÜU

im

fjjfc

u far

J.iUI

JIT 117

19.719

L IM

1* Ett

9J3G

1.9IJ

1.5(0

S SC]

C34J'

3.32

HUIT

1ÎJSIS

»/fi

C

«

IHλ

0ÄI!

Ifl.JW

EXHIBIT 5-6 Cisco General Pro Forma Screen

EXHIBIT 5-6 Cisco General Pro Forma Screen

Column 11 shows the free cash flow to the firm—equal to column 6 minus (column 9 plus column 10). Columns 12 and 13 are used to discount and total those FCFF numbers. You'll note that FCFF is calculated for the 10-year excess return period. At the end of the 10-year period, ValuePro 2002 calculates the residual value by dividing Cisco's NOPAT, estimated to be equal to $12,615 million, by the Cisco's WACC (which as we'll see later, turns out to be equal to 10.04 percent) to get a $125,650 million future residual value in 2013. You'll see how and where we get the inputs to calculate Cisco's WACC in Chapters 6 and 7.

We then discount the company's yearly free cash flow and the residual value at the discount factors, shown in column 12, associated with the WACC. Column 13 shows the product of FCFF (column 11) times the discount factors (column 12). The sum of these discounted numbers, along with short-term assets, is the enterprise value of $89,314 million, shown above column 5 in Exhibit 5-6. From the enterprise or corporate value, we subtract the value of debt and preferred stock (but Cisco has neither debt or preferred stock outstanding) and short-term liabilities to get total value to common equity of $80,939 million, as shown in Exhibit 5-6. We divide value to common equity by the amount of shares outstanding (using diluted shares outstanding here—more about that in Chapter 6) to calculate the per-share intrinsic stock value of $10.87, which is exactly what we want to know.

Based on historic data and analyst expected growth rate of 20 percent, the $10.87 intrinsic value for Cisco's stock is significantly less than

V Ji.rPrd 3XH

CiiiiUV.lI J flLllil

Jntiin>ir Slnck VjW SL fr] General Inputs

V Ji.rPrd 3XH

CiiiiUV.lI J flLllil

Jntiin>ir Slnck VjW SL fr] General Inputs

Company TlC(«r

cscg

Erce-ss Return Period [yaar&j

1q

Deprtcia&on Rato c) Rev )

a îi

Revenues (îml)

'ébi5

trweitrmert Rate ci Rev )

Gmwifi Rate

îc0

Wtaking C-apiial C R9v ]

Ne: Operating PreJn Mar^n (%j

It il

Short-Term asms iîitnll

I7J»

täx Rate

a s

Short. Terrti frirl'i

sws

SdKk Pflie($)

^Quly RiSkPrertiiuTi (%)

300

Shew QUSteaidins (m 1)

7tlf

COmpiarvBeta

1 S8

It^ywr Treasury YieM (%)

A s

VehjscrDetiO« ifrmi)

0

Bond Spread to Tressmy

OA

VetoofRref Stock 0* |iml)

0

PnsfsnwJ StMKYiflM (%)

ddg

Compariv WACC (%)

idjh

EXHIBIT 5-7 Cisco General Input Screen—5-percent Growth

EXHIBIT 5-7 Cisco General Input Screen—5-percent Growth its closing price of $14.45 on August 14, 2002. On the basis of these assumptions, a sell recommendation was in order on that date. Could it be that the market knows something that we do not, or that market players are using valuation assumptions that are more advantageous to Cisco's stock value than we are? Let's test the lower 5-percent growth rate assumption for Cisco and see how that affects its value. That valuation is shown in Exhibit 5-7.

Changing that assumption certainly did not help Cisco's intrinsic stock value—it dropped 57 percent to $4.64 based on a 5-percent long-term growth rate.

In Chapter 6, we discuss how to estimate a company's weighted average cost of capital and use that number in the valuation process. And in that chapter you'll learn about a company's WACC and market capitalization.

The cash flow estimation process may be tedious but it is not difficult. It's important for you to know that if you properly follow the free cash flow to the firm approach, you'll gain the talent of at least one of the five Chinese brothers—you won't get burned.

Was this article helpful?

0 0

Post a comment