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

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 |
B» |
ïï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

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 ] |
sö |

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($) |
h« |
^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?

## Post a comment