Principles of Finance with Excel (Основы финансов c Excel)
Подождите немного. Документ загружается.
PFE Chapter 19 (Appendix), Valuing Procter & Gamble page 13
•
/
/
/
Book Debt Equity
M
arket Debt Equity
Market Equity Book valueof equity
=
(column E)
•
/
/
1/
M
arket Debt Equity
Market Debt Assets
M
arket Debt Equity
=
+
(column F)
()
E
1
Asset D C
ED
T
ED ED
ββ β
=+−
+
+
.
We’ve been somewhat cavalier in the above table—assuming that all the companies have
the same marginal tax rate and same debt beta. But a sensitivity analysis will convince you that
it doesn’t matter much. Here is a two-parameter data table in which we allow both the corporate
tax rate and the debt beta to vary:
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
ABCDEFGH
Data table: the effect of corporate tax rate and debt beta on WACC
Tax rate
30% 33% 35% 37% 39%
Debt beta--> 0.0 6.58% 6.58% 6.58% 6.58% 6.58%
0.1 6.68% 6.68% 6.68% 6.68% 6.68%
0.2 6.79% 6.79% 6.79% 6.79% 6.79%
0.3 6.90% 6.90% 6.90% 6.89% 6.89%
0.4 7.01% 7.00% 7.00% 7.00% 7.00%
0.5
7.12% 7.11% 7.11% 7.10% 7.10%
This data table cell (which has been hidden)
refers to cell B24. In this way the data table
computes the effect on the WACC in B24 given
changes in the tax rate on column H and the debt
beta in column G.
If you read across the rows, you will see that the tax rate barely affect the WACC. The
debt beta has a bigger effect, but the maximum effect is less than 1 percent, which in the cost-of-
capital literature is passable.
PFE Chapter 19 (Appendix), Valuing Procter & Gamble page 14
19.A.4. Procter & Gamble’s WACC using the Gordon model and the
company’s cost of debt r
D
and its tax rate T
C
We start by computing Procter-Gamble’s cost of debt at the end of 2001. Using financial
statement data results in 7.88%
D
r = :
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
ABCD
Y
ear 2001 2000
Interest paid $794,000,000
Short-term debt $2,233,000,000 $3,241,000,000
Long-term debt $9,792,000,000 $9,012,000,000
Total debt $12,025,000,000 $12,253,000,000
Cash and cash equivalents $2,306,000,000 $1,415,000,000
Investment securities $212,000,000 $185,000,000
Net debt $9,507,000,000 $10,653,000,000 <-- =C7-SUM(C9:C10)
Cost of debt, r
D
7.88% <-- =B3/AVERAGE(B12:C12)
Profits before taxes $4,616,000,000
Taxes $1,694,000,000
Tax rate, T
C
36.70% <-- =B17/B16
COMPUTING TAX RATE T
C
AND
THE COST OF DEBT r
D
FOR PROCTER & GAMBLE FOR 2001
Note that we have also calculated PG’s tax rate T
C
by computing:
C
Provision for incometaxes
T
Profits beforetaxes
=
This may not be the whole story, however. PG’s 2001 annual report gives the company’s
average interest rates at year-end 2001 as 4.5% for long-term debt and 4.1% for short-term debt.
On reflection these numbers appear to be more reliable estimates of the firm’s marginal
borrowing costs. Since, wherever possible, we prefer to use marginal costs instead of historical
costs, we will use r
D
= 4.3%.
Using the Gordon model
We’ve previously discussed this model in Chapter 6 and again in Chapter 16. If there’s
one single dividend growth rate, the Gordon formula becomes:
PFE Chapter 19 (Appendix), Valuing Procter & Gamble page 15
()
0
0
0
0
1
where
current equity payout (total dividend + stock repurchases) of firm
current market value of equity
anticipated dividend growth rate
E
Div g
rg
P
Div
P
g
+
=+
=
=
=
In Chapter 6 we emphasized the need to calculate the cost of equity using:
o Total dividends + repurchases of shares: These are the total payouts to equity
holders
o Total value of equity (number of shares * share price)
We start with the facts—the historical data on PG’s total cash dividends and stock
repurchases over the years 1993-2001:
PFE Chapter 19 (Appendix), Valuing Procter & Gamble page 16
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
ABCDE
Cash dividends
to shareholders
Purchases of
treasury stock
Total
1993 850,000,000 55,000,000 905,000,000
1994 949,000,000 14,000,000 963,000,000
1995 1,062,000,000 114,000,000 1,176,000,000
1996 1,202,000,000 432,000,000 1,634,000,000
1997 1,329,000,000 1,652,000,000 2,981,000,000
1998 1,462,000,000 1,929,000,000 3,391,000,000
1999 1,626,000,000 2,533,000,000 4,159,000,000
2000 1,796,000,000 1,766,000,000 3,562,000,000
2001 1,943,000,000 1,250,000,000 3,193,000,000
Growth 10.89% 47.76% 17.07% <-- =(D11/D3)^(1/8)-1
PROCTER & GAMBLE, DIVIDENDS AND REPURCHASES
0
500,000,000
1,000,000,000
1,500,000,000
2,000,000,000
2,500,000,000
3,000,000,000
3,500,000,000
4,000,000,000
4,500,000,000
1993 1994 1995 1996 1997 1998 1999 2000 2001
Cash dividends
to shareholders
Purchases of
treasury stock
Total
In the graph we see a pattern which is familiar to the U.S. corporate sector: The dividend
growth is very smooth and hence predictable, whereas the repurchases of stock are much less
predictable. The compound growth rate of dividends is 10.89% (very respectable) and the
compound growth rate of the total dividends + repurchases is 17.07% (high!).
The key question for the Gordon model calculation of r
E
is: What do shareholders
anticipate will be future dividend growth? It is unlikely that an intelligent shareholder (like the
reader of this book) will assume that the incredible growth of repurchases can continue over the
foreseeable future. In the spreadsheet below we assume that the 17% dividend rate will continue
PFE Chapter 19 (Appendix), Valuing Procter & Gamble page 17
for the next 4 years, after which the dividend growth will drop to 4%. This results in a cost of
equity (using the two-stage Gordon model) of r
E
=9.11% and a WACC = 8.55%.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
AB C
End-2001 stock price 78.08
Number of shares 1,300,000,000
Equity value, E 101,504,000,000 <-- =B3*B2
Debt value (net), D $9,719,000,000 Calculated previously
End-2001 dividend, Div
0
3,193,000,000 Sum of dividends and repurchases
High dividend growth rate, g
high
17.07%
Number of high-growth years 4
Normal dividend growth rate, g
normal
4.00%
Cost of equity, r
E
9.11% <-- =twostagegordon(B4,B7,B9,B10,B12)
Cost of debt, r
D
4.30% Calculated previously
Tax rate, T
C
37%
WACC = r
E
*E/(D+E)+r
D
*(1-T
C
)*D/(E+D)
8.55% <-- =B14*B4/(B4+B5)+B15*(1-B16)*B5/(B4+B5)
PROCTER & GAMBLE, CALCULATING WACC
USING TWO-STAGE GORDON MODEL
19.A.5. Procter & Gamble: the bottom line on the FCF valuation
The WACC for PG is between 6.79% and 8.55%, depending on whether we calculate it
using the CAPM or the Gordon model. If we assume that the company will have high FCF
growth of 10.48% for the next 4 years and 4% thereafter, we can use our two-stage FCF model to
value the shares:
PFE Chapter 19 (Appendix), Valuing Procter & Gamble page 18
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
AB C
2001 FCF (base year) 4,112,000,000 <-- This is cell G11 of the FCF spreadsheet
High FCF growth rate, g
high
10.48% <-- This is cell G12 of the FCF spreadsheet
Number of high FCF growth years 4 <-- A guess
Term 1 factor: (1+g
high
)/(1+WACC)
102% <-- =(1+B4)/(1+B10)
Normal FCF growth rate, g
normal
4.00%
WACC 8.00%
End-2001 debt 12,025,000,000 <-- From P&G's balance sheet
End-2001 cash 2,306,000,000 <-- From P&G's balance sheet
Term 1: PV of high-growth cash flows 17,414,591,486 <-- =B2*B6*(1-B6^B5)/(1-B6)
Term 2: PV of normal-growth cash flows 117,080,006,861 <-- =B2*(1+B4)^B5*(1+B8)/(B10-B8)/(1+B10)^B5
Enterprise value 134,494,598,348 <-- =SUM(B14:B15)
Add cash 136,800,598,348 <-- =B16+B12
Subtract debt -12,025,000,000 <-- =-B11
Value of equity 124,775,598,348 <-- =SUM(B17:B18)
Number of shares, end 2001 1,300,000,000
Computed value per share 95.98 <-- =B19/B21
End-2001 stock price 78.08
Analyst recommendation: Buy, Sell, Neutral Buy <-- =IF(B22>B23*(1.1),"Buy",IF(B22<B23/1.1,"Sell","Neutral"))
PROCTER & GAMBLE, VALUATION
As you can see in cell B25, this makes P&G a strong buy, since its current (i.e., end
2001) share price is $78.08. Even at our upper estimate for the WACC, 8.55%, PG’s valuation
using the FCF discounting is $83.33, which is still above its current market value.
If we use
Data Table, we can see that the current market value of $78.08 is apparently
based on significantly lower expectations. In the table below we value PG stock using normal
growth rates between 0% and 6% and varying the number of high-growth years from 0 to 7. The
highlighted cells are those valuations which are within 10% of the current market valuation.
6
6
The highlighting uses Excels conditional formatting tool (see Chapter 33).
PFE Chapter 19 (Appendix), Valuing Procter & Gamble page 19
1
2
3
4
5
6
7
8
9
10
11
12
13
14
EFGHIJKLM
Data table on number of high-growth years and on "normal" growth rate
Normal growth rate
0% 1% 2% 3% 4% 5% 6%
Number of 0
32.06 38.16 46.30 57.68 74.76 103.23 160.17
high-growth 1
36.21 42.45 50.77 62.42 79.89 109.01 167.25
years 2
40.45 46.83 55.34 67.26 85.13 114.92 174.50
3
44.78 51.31 60.02 72.21 90.49 120.97 181.92
4
49.22 55.90 64.81 77.28 95.98 127.16 189.51
5
53.76 60.59 69.70 82.46 101.59 133.49 197.27
6 58.40 65.39 74.71 87.76 107.34 139.96 205.21
7
63.15 70.30 79.83 93.18 113.21 146.58 213.33
Highlighted cells are within 10% of the current market share value.
The highlighted cells show the expected tradeoff between high-growth years and the
normal growth rate. If we assume that ultimately normal growth is in the range of 3-4%, then the
market seems to be valuing PG as if it has very few years of high FCF growth.
Conclusion: Procter & Gamble—what happened?
The FCF valuation produced bullish estimates about PG: Assuming a WACC of 8%, we
valued the company’s stock at the beginning of 2002 at $95.98, well above the actual price of the
stock of $78.08. In the event, we were also correct—in the first months of 2002, PG stock
climbed steadily, reaching a price of $91.93 by mid-April 2002:
PFE Chapter 19 (Appendix), Valuing Procter & Gamble page 20
Procter & Gamble--Price History
75
80
85
90
95
31-Dec-2001
7-Jan-2002
14-Jan-2002
21-Jan-2002
28-Jan-2002
4-Feb-2002
11-Feb-2002
18-Feb-2002
25-Feb-2002
4-Mar-2002
11-Mar-2002
18-Mar-2002
25-Mar-2002
1-Apr-2002
8-Apr-2002
We are not, of course, claiming perfect foresight—because of the vast amounts of
information which to be digested and because of the large number of assumptions the analyst has
to make, stock valuation remains one of the most problematic areas of finance. However, the
FCF methods illustrated in Chapter 16 and in this Appendix are powerful methods which often
give significant insights into the valuation of a firm’s stock.
PFE Chapter 20, Capital structure and valuation page 1
CHAPTER 20: CAPITAL STRUCTURE AND
THE VALUE OF THE FIRM
*
This version: November 28, 2004
Chapter contents
Overview..............................................................................................................................2
20.1. Capital structure when there are corporate taxes—ABC Corp..................................6
20.2. Valuing ABC Corp.—taking account of leverage and corporate taxes.....................9
20.3. Why debt is valuable in Lower Fantasia—buying a turfing machine .....................15
20.4. Why debt is valuable in Lower Fantasia—relevering Potfooler Inc........................21
20.5. Potfooler exam question, second part......................................................................26
20.6. Considering personal as well as corporate taxes—the case of XYZ Corp. .............29
20.7. Valuing XYZ Corp.—taking account of leverage and taxes...................................36
20.8. Buying a sturfing machine in Upper Fantasia..........................................................41
20.9. Relevering Smotfooler Inc., an Upper Fantasia company.......................................45
20.10. Is there really an advantage to debt?......................................................................51
Summary and conclusion—United Widgets Corporation .................................................53
Exercises............................................................................................................................58
*
This is a preliminary draft of a chapter of Principles of Finance with Excel. © 2001 – 2004 Simon Benninga
(benninga@wharton.upenn.edu
).
PFE Chapter 20, Capital structure and valuation page 2
Overview
“Capital structure” is finance jargon for how a firm should be financed—what mixture of
debt and equity should be used by the shareholders of a firm to finance the firm’s activities. To
start you off thinking about this tricky question, we offer the example of Mortimer and Joanna,
who are competing to buy the same supermarket.
The Fair City supermarket—does financing affect the price?
Mortimer and Joanna live in Fair City. Each heads a group of investors that wants to buy
a supermarket located in the center of town. Both Mortimer and Joanna have superb records as
supermarket managers. As manager of a supermarket, they’re pretty much the same—meaning
that the supermarket they manage will have the same sales, cost of goods sold, etc. However,
while the management aspect of Mortimer’s group and Joanna’s group is pretty much the same,
there’s a big financial difference between the two competing groups: Mortimer’s investors want
to borrow 50 percent of the money needed to purchase the supermarket, whereas Joanna’s
investors hate debt and have decided to put up the whole cost of purchasing the supermarket
without borrowing a penny.
The question: Which group of investors—Mortimer’s or Joanna’s—can afford to make
the higher bid for the supermarket? This is the question examined in this chapter. At this point
in the chapter we offer no answers to this question, but merely want to give you an insight into
how possible answers might look.