Principles of Finance with Excel (Основы финансов c Excel)
Подождите немного. Документ загружается.
PFE, Chapter 9: DCF valuation with financial models page 17
model this does not mean that fixed assets at cost are constant. It does mean, however,
that in the FCF calculations lines 45 (depreciation) and 48 (capital expenses) cancel out.
•
John assumes that in each of the next five years, Motherboard Shoes will repay $1
million of its $10 million debt.
• Cash is the plug.
Given the full-blown financial planning model, there are obviously many sensitivity
analyses we can perform. Below we show two data tables. The first table analyzes the effect of
the sales growth assumption (cell B2 of the model) on the share valuation. John Mba has
estimated sales growth of 10% annually for the next 5 years. As you can see, the effect of this
assumption is quite dramatic. The greater the sales growth (cell B2), the greater the valuation of
Motherboard’s shares:
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
ABCDEFG
Sensitivity analysis: Effect of sales growth (cell B2) on value per share
Sales growth
Share
value
65.71 <-- =B68, this is data table header
0% 39.96
2% 44.48
4% 49.29
6% 54.43
8% 59.89
10% 65.71
12% 71.89
14% 78.46
16% 85.43
18% 92.82
Motherboard Shoes: Impact of Sales Growth on Share Value
35
40
45
50
55
60
65
70
75
80
85
90
95
0% 5% 10% 15% 20%
Sales Growth
Share value
PFE, Chapter 9: DCF valuation with financial models page 18
Excel Note: Data tables
This may be the appropriate place to review Data Tables, which are covered in
Chapter000. What may be confusing in the previous data table is the “65.71” in cell B74. This
is a reference to the share price calculation in the initial model The data table asks Excel to redo
this calculation for the sales growths in cells A75:A84.
A second sensitivity analysis performed by John Mba is the effect of the weighted
average cost of capital and the long-term growth rate (cells B55 and B56) on the per-share
valuation. Notice that these two parameters affect the valuation in two ways:
•
The terminal value calculation in cell G60 is
(
)
*1 -
5
-
FCF long term FCF growth
WACC long term FCF growth
+
−
. This
computation is affected by both the long-term growth and the WACC parameters.
•
The present value calculation in cell B63 is affected by the WACC.
To examine the effect of these two parameters, John builds a two-dimensional data table:
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
ABCDEFG
WACC
65.71 10% 12% 14% 16% 18%
0%
118.54 97.01 81.68 70.22 61.33
Long-term growth rate 2% 141.87 111.62 91.49 77.14 66.40
4% 180.76 133.52 105.21 86.36 72.91
6% 258.54 170.04 125.80 99.28 81.60
8% 491.87 243.06 160.12 118.65 93.77
10% nmf 462.14 228.76 150.95 112.02
12% nmf nmf 434.68 215.53 142.44
14% nmf nmf nmf 409.29 203.27
16% nmf nmf nmf nmf 385.77
Note: Data tables are discussed in Chapter 30.
=IF(B55>B56,B68,"nmf")
The results produced by this sensitivity analysis are not surprising:
•
Going across rows shows that as the WACC increases, the value per share decreases.
Since a larger WACC means that the present value of a future cash flow is less, this is to
be expected.
PFE, Chapter 9: DCF valuation with financial models page 19
• Going down columns shows that the larger the long-term growth rate expected from
Motherboard Shoes, the more the shares are worth. Again, this is not a surprise, since
larger long-term growth rates mean higher FCFs after the year-5 model horizon. As
noted in the box below, our terminal value model only works when the long-term growth
rate is less than the WACC. When this assumption is not true (meaning: the long-term
growth >
WACC), we’ve had Excel write “nmf” (“no meaningful figure”). The
technique for doing this is explained below.
Excel/Finance Note
Notice our use of the If function in cell B93 of the data table above. The terminal value
formula is:
(
)
5
*1 -
-
FCF long term FCF growth rate
Terminal value
WACC long term FCF growth rate
+
=
−
As noted in Chapter 6, this formula is only valid when
-WACC long term FCF growth> . Since
some of the combinations of growth and WACC in the data table violate this condition, we’ve
used the
If function to isolate them. As used in cell B93, this function says:
If WACC long-term
If WACC>long-term
FCF growth,
FCF growth,
write "no meaninful
put in the valuation
figure"
as performed in cell
B78
If(B55>B56, B68 , "nmf" )
↑
↑
≤
PFE, Chapter 9: DCF valuation with financial models page 20
9.4. Advanced section: What’s the theory behind the model?
In this section we explain some theoretical points about the valuation model illustrated in
the previous section. Not all of this is easy, and you may (understandably) want to skip this
section.
1
Why is the firm’s value related to the PV of the future FCFs?
Our basic valuation formula is:
()()()
3
12
123
111
Firm value Debt Equity
Initial cash
FCF
FCF FCF
and marketable
WACC WACC WACC
securities
=+
=++++
+++
…
The enterprise value of the firm is defined to be the value of the firm’s operations. In
financial theory, the enterprise value is the present value of the firm’s future anticipated cash
flows. In this section we explain these concepts.
The valuation process
One way of viewing valuation is through the use of the accounting paradigm, but using
market values. We rewrite the balance sheet by moving the current liabilities from the
liabilities/equity side to the asset side of the balance sheet:
1
Why would an author put a section like this in this book? Our experience is that ultimately almost all finance
professionals are called upon to do valuations. At some point in every valuation, someone is going to question your
techniques and theory. That’s the time to come back to this section.
PFE, Chapter 9: DCF valuation with financial models page 21
ORIGINAL BALANCE SHEET
Assets Liabilities
Cash and marketable securities Operating current liabilities
Operating current assets Debt
Net fixed assets Equity
Goodwill
Total assets Total liabilities and equity
Assets Liabilities
Cash and marketable securities
Operating current assets Debt
- Operating current liabilities
=Net working capital
Net fixed assets Equity
Goodwill
Market value Market value
THE ENTERPRISE VALUATION "BALANCE SHEET"
USING THE BALANCE SHEET AS AN
ENTERPRISE VALUATION MODEL
Enterprise value:
PV of FCFs
discounted at WACC
To value a company, we set:
()
1
t
t
t
FCF
Market value Initial cash balances
WACC
I
nitial cash balances Enterprise value
=+
+
=+
∑
If we are valuing the equity of the firm, we subtract the value of the debt:
()
()
()
1
1
t
t
t
t
t
t
Equity value Marketvalue Debt
FCF
I
nitial cash balances Debt
WACC
FCF
Debt Initial cash
WACC
=−
=+−
+
=−−
+
∑
∑
Note that this means that we can write the enterprise balance sheet in a slightly different
form:
PFE, Chapter 9: DCF valuation with financial models page 22
Assets Liabilities
Operating current assets Debt - cash & marketable securities
- Operating current liabilities
=Net working capital
Net fixed assets Equity
Goodwill
Enterprise value Enterprise value
THE ENTERPRISE VALUATION "BALANCE SHEET"
A slight variation: Cash and marketable securities netted out from debt
Enterprise value:
PV of FCFs discounted at
WACC
We can use the FCF projections and a cost of capital to determine the enterprise value of
the firm. Suppose we have determined that the firm’s weighted average cost of capital (WACC)
is 20%.
2
Then the enterprise value of the firm is the discounted value of the firm’s projected
FCFs plus its terminal value:
()()()()
5
12
12 5 5
-
11 1 1
FCF
FCF FCF
Year 5 Terminal Value
Enterprise value
WACC WACC WACC WACC
=++++
++ + +
…
In this formula, the Year-5 Terminal Value is a proxy for the present value of all FCFs from year
6 onwards.
3
Terminal value
In determining the terminal value we use a version of the Gordon model described in
Chapter 6. We have assumed that—after the year 5 projection horizon—the cash flows will
grow at a rate of growth equal to the sales growth of 10%. This gives the terminal value as:
2
In Chapter 6 we introduced the topic of the WACC and showed you how to calculate this using the Gordon
dividend model. In Chapter 14 we show an alternative calculation of the WACC which uses the security market
line. In this chapter we simply assume a value for the WACC.
3
We don’t actually project these cash flows. We determine the terminal value based on year-5 FCF.
PFE, Chapter 9: DCF valuation with financial models page 23
()
()
()
()
5
5
11
5
*1
5
11
*1
t
t
tt
tt
FCF growth
FCF
Terminal Value at end of year
WACC WACC
FCF growth
WACC growth
∞∞
+
==
+
==
++
+
=
−
∑∑
The last equality is derived in a manner similar to the dividend valuation of shares (the Gordon
model) discussed in Chapter 6.
Summary
The valuation of a business (“how much is it worth”) is one of the most important
activities of a financial analyst. In this chapter we have described how to do a discounted cash
flow (DCF) valuation using the financial planning models of Chapter 8. To do a DCF valuation
you have to understand almost all facets of the business:
•
How the business works—this affects the financial parameters used in the financial
planning model. The composition of the firm’s current assets and current liabilities
(meaning: its net working capital needed to do its business) and the amount of fixed
assets (buildings and equipment and land) needed to do this business—all of these factors
affect a firm’s valuation.
•
How to compute the cost of capital. The weighted average cost of capital (WACC) is the
discount rate used to value the future FCFs of the firm. In this chapter we have not
discussed its computation (Chapters 6 and 15 give different aspects of the WACC
computation).
•
How to use Excel to do the relevant computations.
PFE, Chapter 9: DCF valuation with financial models page 24
Exercises
**The present value calculation above assumes that cash flows occur at the end of the year.
However, they actually occur throughout the year. To account for this fact, John assumes that
each of the projected free cash flows occurs in mid-year. As discussed in Chapter000, this
means that the present value calculation??????????????? [leave as exercise?]
PFE, Chapter 10: What is risk? page 1
CHAPTER 10: WHAT IS RISK?
*
slight bug fix: July 27, 2003
Chapter contents
Overview......................................................................................................................................... 1
10.1. The risk characteristics of financial assets—some introductory blather .............................. 3
10.2. A safe security can be risky because it has a long horizon................................................. 11
10.3. Risk in stock prices—McDonald’s stock............................................................................ 18
10.4. Advanced topic: Using continuously-compounded returns to compute annualized return
statistics......................................................................................................................................... 29
10.5. Risk and return depend on the your unit of account ........................................................... 31
Conclusion .................................................................................................................................... 33
Exercises ....................................................................................................................................... 35
Overview
Risk is the magic word in finance. Whenever finance people can’t explain something, we
try to look confident and say “it must be the risk.” If we want to appear intelligent when hearing
*
Notice: This is a preliminary draft of a chapter of Principles of Finance with Excel by Simon Benninga
(http://finance.wharton.upenn.edu/~benninga/pfe.html). Check with the author (benninga@wharton.upenn.edu
)
before distributing this draft (though you will probably get permission), and check the website to make sure the
material is updated. All the material is copyright and the rights belong to the author.
PFE, Chapter 10: What is risk? page 2
a financial presentation, we look skeptical and say “Have you considered the risks?” Usually
that’s enough to score a point or two.
1
Our intuition usually relates financial risks to unpredictability. A financial asset like a
savings account is thought to be not risky because its future value is known, whereas a financial
asset like a stock is risky because we do not know what it will be worth in the future. Financial
assets of different types have different gradations of risk: Our intuitions tell us that a savings
account is less risky than a share in a company, and a share in a high-tech start-up is more risky
than a share in a well-established blue-chip company.
The intuition which ties unpredictability and risk together is valid, but can have some
surprising aspects. For example, in section 10.??? we show that a Treasury bill, a bond issued by
the United States government, can sometime be risky. The T-bill becomes risky if you need to
sell it before it matures. We illustrate this risk with an example. We also look at the risk of
holding a share, and show that it can be quantified statistically. This is an important insight for
Chapters 11-15, where we use a statistical description of stock price risk to talk about choosing
portfolios of stocks.
Although we have tried to make the chapter unstatistical and non-mathematical,
inevitably the measurement of risk involves calculations.
2
1
I give my students the following hint about taking finance exams: Suppose you have to answer a question to which
you absolutely don’t know the answer (“What is the zeta function of the annual returns?” “How do you explain the
difference between XYZ Corp’s annual returns over time?”). If you know nothing about the question, make up a
meaningless sentence which includes the word “risk” (“The zeta function of the annual returns relates to the
riskiness of the returns.” “XYZ’s annual returns vary because of the changing risk of the company.”) You’re bound
to get a point or two.