Principles of Finance with Excel (Основы финансов c Excel)

Подождите немного. Документ загружается.

PFE Chapter 19 (Appendix), Valuing Procter & Gamble page 3

initial period of high dividend growth is followed by normal dividend growth. In the equation

below we’ve assume that dividends grow at a high growth rate for 5 years and that afterwards the

growth rate slows down to a normal dividend growth rate. In this case the basic Gordon model

equation becomes:

()

(

)

()

The present value of the first The present value of t

five years of dividend growth,

assumed to grow at a high growth

rate, .

*1 *1 *1

high

high high normal

Divg Divg g

−

∞

↑↑

+++

∑∑



()

he rest of the dividends,

assumed to grow at a lower rate .

Referred to as "term 1" in the spreadsheet below

normal

high

Div g

↑

⎛⎞

⎜⎟

−

⎜⎟

⎝⎠

⎜⎟

−

⎜⎟

⎝⎠





()

Referred to as "term 2" in the spreadsheet below

high

normal

Enormal

Div g

↑

⎛⎞

⎜⎟

−

⎝⎠







The problem here is to solve for r

. To show what’s going on, we use Goal Seek or

Solver in Excel to solve this problem.

Here’s an illustration: ABC Corp’s current share value is

$50. The firm has just paid a dividend of $10 per share. You anticipate that this dividend will

grow at 20% per year for the next 5 years, after which the dividend growth rate will slow to 5%

per year. To find the cost of equity r

, we set up the following spreadsheet:

Below we also show the use of an Excel function defined by the author which automates this process.

PFE Chapter 19 (Appendix), Valuing Procter & Gamble page 4

ABC

TWO-STAGE GORDON DIVIDEND MODEL

Div

High growth rate, g

high

20%

Number of high-growth years 5

Term 1 factor: (1+g

high

)/(1+r

)

0.9600 <-- =(1+B4)/(1+B10)

Normal growth rate, g

normal

Cost of equity, r

25.00%

Term 1: PV of high-growth dividends 44.3106 <-- =B3*B6*(1-B6^B5)/(1-B6)

Term 2: PV of normal-growth dividends 42.8071 <-- =B3*B6^B5*(1+B8)/(B10-B8)

Term 1 + Term 2 - P

37.12 <-- =B12+B13-B2

We want to find the cost of equity r

(cell B10) so that cell B15 equals zero. Doing this

with Goal Seek gives:

Pressing OK gives the cost of equity as r

= 37.620%:

PFE Chapter 19 (Appendix), Valuing Procter & Gamble page 5

ABC

TWO-STAGE GORDON DIVIDEND MODEL

Div

High growth rate, g

high

20%

Number of high-growth years 5

Term 1 factor: (1+g

high

)/(1+r

)

0.8720 <-- =(1+B4)/(1+B10)

Normal growth rate, g

normal

Cost of equity, r

37.62%

Term 1: PV of high-growth dividends 33.7744 <-- =B3*B6*(1-B6^B5)/(1-B6)

Term 2: PV of normal-growth dividends 16.2257 <-- =B3*B6^B5*(1+B8)/(B10-B8)

Term 1 + Term 2 - P

0.00 <-- =B12+B13-B2

Cost of equity, r

using the function

twostagegordon

37.62% <-- =twostagegordon(B2,B3,B4,B5,B8)

TwostageGordon—an Excel function for calculating the Gordon cost of capital

The spreadsheet for this appendix includes an Excel function defined by the author. The

function TwostageGordon computes the cost of equity in a two-stage Gordon model. Cell B18

of the spreadsheet clip above shows this function in action. Here’s the wizard for the function:

PFE Chapter 19 (Appendix), Valuing Procter & Gamble page 6

Pressing OK gives the answer which appear in cell B18.

19.A.2. Computing the FCF growth rate for Procter & Gamble

Discounting anticipated FCFs looks simple, and it is—mathematically. But reality is

very complicated. We have a number of tricky issues to resolve:

• How to calculate the FCF growth rate or rates (if there is more than one)? This is

illustrated in this section. We use an example for Procter & Gamble to illustrate the

difficulties that can be encountered.

• How to calculate the WACC. This is illustrated in the following sections.

Build a financial model for the firm—the best way to predict FCFs

The best way to compute the FCF growth rate is to build a full-blown model of the firm.

This technique was discussed in Chapters 8 and 9. The financial model method of computing the

FCF growth rate is usually preferable to other methods—it takes into account the all the

economic factors which drive the profitability of the firm, and it ties them together in a

meaningful way (both economically and accounting). The disadvantage of this method is that

building a financial model is relatively complicated and time consuming.

Projecting the FCF growth rate from the firm’s past performance

You could try to project the FCFs from the firm’s past performance. In this section we

give some examples; we’ll be frank about the difficulties and the assumptions we’ve had to

make.

PFE Chapter 19 (Appendix), Valuing Procter & Gamble page 7

There are two main problems in computing the FCFs from historical data. The first

problem has to do with the definition of the FCF. The definition given in Chapters 7-9 is:

Defining the Free Cash Flow

Profit after taxes This is the basic measure of the profitability of the

business, but it is an accounting measure that includes

financing flows (such as interest), as well as non-cash

expenses such as depreciation. Profit after taxes does not

account for either changes in the firm’s working capital or

purchases of new fixed assets, both of which can be

important cash drains on the firm.

+ Depreciation This noncash expense is added back to the profit after tax.

+ after-tax interest payments (net) FCF is an attempt to measure the cash produced by the

business activity of the firm. To neutralize the effect of

interest payments on the firm’s profits, we:

• Add back the after-tax cost of interest on debt

(after-tax since interest payments are tax-

deductible),

• Subtract out the after-tax interest payments on cash

and marketable securities.

- Increase in current assets When the firm’s sales increase, more investment is needed

in inventories, accounts receivable, etc. This increase in

current assets is not an expense for tax purposes (and is

therefore ignored in the profit after taxes), but it is a cash

drain on the company.

+ Increase in current liabilities An increase in the sales often causes an increase in

financing related to sales (such as accounts payable or taxes

payable). This increase in current liabilities—when related

to sales—provides cash to the firm. Since it is directly

related to sales, we include this cash in the free cash flow

calculations.

- Increase in fixed assets at cost An increase in fixed assets (the long-term productive assets

of the company) is a use of cash, which reduces the firm’s

free cash flow.

FCF = sum of the above

However, it’s not always clear what constitutes a change in current assets, current

liabilities, or fixed assets. In the next example, for example, we will question what items for

Procter & Gamble are related to the increases in fixed assets.

PFE Chapter 19 (Appendix), Valuing Procter & Gamble page 8

A second problem we will encounter is with the growth rates. In many cases we will see

growth rates which appear to us to be unnaturally large or small.

Procter & Gamble

Below we do a calculation of Procter & Gamble’s FCF for the years 1993-2001.

Our

problem in computing the historic FCF is whether to include the asset sales and the acquisitions

in the cash flow or not.

ABCDEFGHI

Cash from

operations

Interest expense

after-tax

Capex Asset sales Acquisitions

FCF w/o asset

sales and w/o

acquisitions

FCF w/ asset

sales and w/o

acquisitions

FCF w/ asset

sales and w/

acquisitions

1993 3,338,000,000 425,467,049 1,911,000,000 725,000,000 138,000,000 1,852,467,049 2,577,467,049 2,439,467,049

1994 3,649,000,000 318,500,299 1,841,000,000 105,000,000 295,000,000 2,126,500,299 2,231,500,299 1,936,500,299

1995 3,568,000,000 322,690,000 2,146,000,000 310,000,000 623,000,000 1,744,690,000 2,054,690,000 1,431,690,000

1996 4,158,000,000 315,755,836 2,179,000,000 402,000,000 358,000,000 2,294,755,836 2,696,755,836 2,338,755,836

1997 5,882,000,000 297,324,252 2,129,000,000 520,000,000 150,000,000 4,050,324,252 4,570,324,252 4,420,324,252

1998 4,885,000,000 362,901,191 2,559,000,000 555,000,000 3,269,000,000 2,688,901,191 3,243,901,191 -25,098,809

1999 5,544,000,000 650,000,000 2,828,000,000 434,000,000 137,000,000 3,366,000,000 3,800,000,000 3,663,000,000

2000 4,675,000,000 722,000,000 3,018,000,000 419,000,000 2,967,000,000 2,379,000,000 2,798,000,000 -169,000,000

2001 5,804,000,000 794,000,000 2,486,000,000 788,000,000 138,000,000 4,112,000,000 4,900,000,000 4,762,000,000

Growth 7.16% <-- =(B11/B3)^(1/8)-1 3.34% 1.05% 0.00% 10.48% 8.36% 8.72%

Definitions:

Cash from operations includes changes in NWC and depreciation, net of interest expense

"CAPEX" is changes in fixed assets, but does not include asset sales or acquisitions.

PROCTER AND GAMBLE, FCF CALCULATIONS

-1,000,000,000

1,000,000,000

2,000,000,000

3,000,000,000

4,000,000,000

5,000,000,000

6,000,000,000

1993 1994 1995 1996 1997 1998 1999 2000 2001 2002

FCF w/o asset

sales and w/o

acquisitions

FCF w/ asset

sales and w/o

acquisitions

FCF w/ asset

sales and w/

acquisitions

Notice that we have calculated the FCF in 3 different ways:

All the data are from Edgar, courtesy of http://edgarscan.pwcglobal.com .

PFE Chapter 19 (Appendix), Valuing Procter & Gamble page 9

• Column G: FCF = Cash flow from operations + after-tax interest – CAPEX. Our natural

preference is to use this definition of the FCF—in using it we will end up valuing the

firm’s existing asset base. This definition of FCF has grown at an annual rate of 10.48%

over the period.

• Column H: FCF = Cash flow from operations + after-tax interest – CAPEX + asset sales.

It could be argued that asset sales are part of PG’s normal business activities. If so, this

might be an appropriate definition of the free cash flows.

• Column I: FCF = Cash flow from operations + after-tax interest – CAPEX + asset sales –

asset acquisitions. If both asset sales and asset acquisitions are part of PG’s normal

business activities, then this is the appropriate definition of FCF.

We choose the first definition (the one that does not include either asset sales or

acquisitions) as our definition of Procter & Gamble’s FCF. Our theoretical justification for using

this definition is that the present value of the FCFs without asset sales and acquisitions gives the

value of the current activities of Procter & Gamble. Assuming that asset sales and acquisitions

are made at market value (that is, NPV = 0), this will also correctly value the firm.

(It also helps

that the first definition of the FCF is relatively smooth, so that it is more predictable.)

Using the first definition of the FCF, we conclude that PG’s future FCF growth rate is

10.48% (cell G12). This growth rate is very high; we ordinarily would not expect a consumer

This “theoretical justification” is easily punctured and shouldn’t be taken too seriously: If part of the ongoing

business of PG is to sell and acquire assets, then we should in principle take one of the two broader definitions of the

FCF.

PFE Chapter 19 (Appendix), Valuing Procter & Gamble page 10

products company to have a long-term growth rate much higher than the growth of GNP.

therefore conclude that after 4 years, the FCF growth rate of Procter & Gamble will decrease to

4%. We denote the two growth rates 10.48%

high

and g

normal

= 4%. If these numbers seem

somewhat arbitrary, be reassured: We’re going to do extensive sensitivity analysis on them

using Excel’s

Data Tables.

Having made this choice, the template below gives us the format for valuing Procter &

Gamble, provided we can specify the WACC and a number of other factors (highlighted below).

Suppose, for example, that the WACC = 7%; then the share value of PG is computed to be

$130.87 (cell B23):

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)

103% <-- =(1+B4)/(1+B10)

Normal FCF growth rate, g

normal

4.00%

WACC 7.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,830,002,057 <-- =B2*B6*(1-B6^B5)/(1-B6)

Term 2: PV of normal-growth cash flows 162,024,760,076 <-- =B2*(1+B4)^B5*(1+B8)/(B10-B8)/(1+B10)^B5

Enterprise value 179,854,762,133 <-- =SUM(B14:B15)

Add cash 182,160,762,133 <-- =B16+B12

Subtract debt -12,025,000,000 <-- =-B11

Value of equity 170,135,762,133 <-- =SUM(B17:B18)

Number of shares, end 2001 1,300,000,000

Computed value per share 130.87 <-- =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

A good ad-hoc model for long-term growth of a consumer products or maturity-industry is to take a real growth

factor and add anticipated inflation. For Procter & Gamble, for example, we might hypothesize that long-term real

sales will grow at the rate of population growth in the United States (approximately 1% - 2%). If the anticipated

long-term inflation rate is about 3%, this gives nominal sales growth as 4%-5%.

PFE Chapter 19 (Appendix), Valuing Procter & Gamble page 11

Clearly the ultimate valuation depends on PG’s WACC.

This issue is discussed in the

following sections. Section 19.A.3 calculates the industry average WACC using an asset beta

approach. Section 19.A.4 discusses PG’s cost of debt r

and its tax rate T

and then computes

PG’s WACC using the Gordon dividend model. The Proctor & Gamble valuation is summarized

in Section 19.A.5.

19.A.3. Using the industry asset beta,

asset

, to compute the WACC for Procter

& Gamble

We use the asset beta approach (see Chapter 15) to calculate the WACC for PG. Our

approach is the following:

•

For a representative sample of firms in PG’s industry, we compute the asset

asset

, defined

as:

()

asset equity debt C

ED ED

βββ

=+ −

The use of the industry average asset beta is predicated on the assumption that individual

firm calculations of betas include a lot of random “noise”; by calculating an industry

average we eliminate much of this noise. On the other hand, the use of the industry beta

assumes that PG risks are well-represented by the average risks of the industry. If you

Row 25 shows how an analyst might decide on her recommendation—if the valuation of the stock exceeds the

current price by more than 10%, this particular analyst gives a “buy” recommendation, and if the valuation is below

the current price, she recommends “sell.” Valuations within a ± 10% range of the current price lead to a “neutral”

recommendation.

PFE Chapter 19 (Appendix), Valuing Procter & Gamble page 12

find this assumption difficult to swallow, you may want to use the alternative calculation

in the next section, which focuses exclusively on PG data.

•

The average asset beta for the industry is then used to compute the WACC of PG:

(

)

average

Gf asset M f

WACC r E r r

⎡

⎤

=+ −

⎣

⎦

We use

= 4.1% and a market risk premium

(

)

Er r

−

= 5.50%. These values

correspond reasonably to the market parameters at the end of 2001, the time of the PG

valuation.

• The unknown in this analysis is the debt

debt

of the various companies used in the

industry sample. In order to overcome this problem, we perform sensitivity analysis on

this parameter. The spreadsheet below assumes a

debt

= 0.2.

Here is our implementation:

ABCDEFGHIJ

Equity Beta

Debt/Equity

book

Market

book

Debt/Equit

market

Debt/Asset

market

Debt

beta Tax rate

Asset

beta

Alberto-Culver Company (ACV) 0.48 0.43 4.35 0.0989 0.0900 0.2 0.37 0.4482 <-- =F3*G3*(1-H3)+(1-F3)*B3

Allou Health & Beauty (ALU) 1.62 2.42 0.59 4.1017 0.8040 0.2 0.37 0.4188

Blyth, Inc. (BTH) 0.84 0.54 3.01 0.1794 0.1521 0.2 0.37 0.7314

Church & Dwight Co., Inc. (CHD) 0.48 1.48 4.35 0.3402 0.2539 0.2 0.37 0.3901

Clorox Company (CLX) 0.50 0.52 6.2 0.0839 0.0774 0.2 0.37 0.4711

Colgate-Palmolive Company (CL) 1.02 3.83 64.03 0.0598 0.0564 0.2 0.37 0.9695

Dial Corporation (DL) 0.68 5.44 24.75 0.2198 0.1802 0.2 0.37 0.5802

Elizabeth Arden, Inc. (RDEN) 1.16 4.32 2.02 2.1386 0.6814 0.2 0.37 0.4554

Gillette Company (G) 0.72 2.02 17.73 0.1139 0.1023 0.2 0.37 0.6592

Home Products Int'l, Inc. (HOMZ) 1.31 46.31 10.23 4.5269 0.8191 0.2 0.37 0.3402

Newell Rubbermaid Inc. (NWL) 0.55 0.9 3.52 0.2557 0.2036 0.2 0.37 0.4637

Procter & Gamble Co. (PG) 0.24 1.27 10.45 0.1215 0.1084 0.2 0.37 0.2276

Tupperware Corporation (TUP) 0.47 2.9 11.25 0.2578 0.2049 0.2 0.37 0.3995

Unilever (UL) 0.46 3.98 3.68 1.0815 0.5196 0.2 0.37 0.2865

Average asset beta 0.49 <-- =AVERAGE(I3:I16)

Asset beta 0.49 <-- =I18

Risk-free, r

4.10%

Risk premium, E(r

) - r

5.50%

WACC 6.79% <-- =B21+B20*B22

COMPUTING THE INDUSTRY ASSET BETA

The procedure we follow is: