PLEASE READ INSTRUCTIONS IN THE FILES ATTACHED BELOW.
HW 9>> IS THE INSTRUCTIONS FOR THE ASSIGNMENT
Don't use plagiarized sources. Get Your Custom Essay on
Excel HW
Just from $13/Page
Chapter 9>> slides to help you
CHAPTER 9
Common Stock Valuation
Timothy R. Mayes, Ph.D.
Metropolitan State University of Denver
© 2018 Cengage. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.
What Is Value?
The term “value” has many different meanings depending on the context in which it is used
For our purposes, there are four important types of value:
Most generally, value can be defined as the amount that a willing and able buyer agrees to pay for an asset to a willing and able seller
Book value is the original purchase price of an asset less its accumulated depreciation
Intrinsic value is the value of an asset to a particular investor as determined by calculating the present value of the expected future cash flows at that investor’s required rate of return
Market value is the price of an asset as determined in a competitive marketplace
© 2018 Cengage. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.
Fundamentals of Valuation
As noted earlier, the intrinsic value of an asset is the present value of the expected future cash flows provided by the asset
To determine the value of a security, then, we must first determine three things:
What are the expected future cash flows?
When will the cash flows occur?
What is the required rate of return for this particular stream of cash flows?
The value of the asset can be compared to its market price to determine whether the asset should be purchased, or not
© 2018 Cengage. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.
Determining the Required Rate of Return
© 2018 Cengage. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.
Valuing Common Stocks
There are many possible formulas that can be used to value common stocks, but most of them are simply present value models
The difference between them is in the pattern of future cash flows that they assume, or the particular cash flow that they use (e.g., dividends or free cash flow)
We will look at several discounted cash flow models:
The Constant-Growth Dividend Discount Model
The Two-Stage Growth Model
Three-Stage Growth Models
The Earnings Model
The Free Cash Flow Model
© 2018 Cengage. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.
The Constant-Growth Dividend Discount Model
Note that since the growth rate is constant, if we know the most recent dividend then we know all future dividends
© 2018 Cengage. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.
The Two-Stage Growth Model
Many companies can’t be expected to grow at a constant rate forever
Some of these may be currently growing at a unsustainably high rate now, but can be expected at some point to see their growth slow to a long-run constant rate
The two-stage dividend discount model allows for these two stages of growth:
© 2018 Cengage. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.
Three-Stage Growth Models
There are several models that allow for three stages of dividend growth:
© 2018 Cengage. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.
Alternative Discounted Cash Flow Models
The Free Cash Flow Model discounts the expected future free cash flows to get the value of the firm, and then subtracts the value of debt and preferred equity to arrive at the market value of equity (here we are assuming a constant growth rate):
© 2018 Cengage. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.
Earnings Model Example
© 2018 Cengage. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.
Relative Value Models
Relative value models provide a way to value a stock relative to other similar stocks using valuation ratios such as the Price to Earnings (P/E) ratio
These models have two major advantages:
They are easy to use
They can be used to value stocks for which the DCF models fail
The most common relative value model is based on the P/E ratio
The idea is to identify a “justified” P/E ratio and to multiply that by expected earnings per share
If earnings are negative, we could use the price to book or price to sales ratios
© 2018 Cengage. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.
Preferred Stock Valuation
Preferred stock is a kind of hybrid security
It represents an ownership claim on the firm’s assets, like common stock
Holders of preferred stock do not benefit from increases in the firm’s earnings and they generally cannot vote in corporate elections, like bonds
Further, like a bond, preferred stock generally pays a fixed dividend payment each period
There is no maturity date, so the life of a share is effectively infinite
Since preferred stock is expected to pay a constant dividend forever, we can simply find the present value of an infinite stream of constant cash flows:
Which is refreshingly simple given some of the previous formulas
© 2018 Cengage. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.
Excel Formulas
FAME_TwoStageValue
FAME_ThreeStepValue
FAME_ThreeStageValue
FAME_HModelValue
© 2018 Cengage. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.
(
)
1
1
N
t
t
t
CF
V
i
=
=
+
å
(
)
(
)
(
)
00
1
1
11
1
t
N
CS
t
t
CSCS
CS
DgDg
D
V
KgKg
K
=
++
===
—
+
å
(
)
(
)
(
)
(
)
012
01
2
1
1
11
1
1
1
1
1
n
n
CS
CS
n
CSCS
CS
Dgg
Dg
Kg
g
V
KgK
K
++
éù
+
æö
–
+
êú
=-+
ç÷
-+
èø
+
êú
ëû
1
1
1
CS
CS
CSCS
r
RE
K
EPS
V
KKg
æö
–
ç÷
èø
=+
–
(
)
1
Nonoperating Assets
DebtPreferrd
Number of Shares
F
F
CS
FCF
VV
WACCg
VVV
V
=+
–
—
=
Price per Share
Earnings per Share
P
E
=
1
CS
P
VEPS
E
=´
P
P
D
V
K
=
HW 9 – Problem 1, parts a-d (Note that the syllabus says to do Problem 2, but do Problem 1 (below) instead)
1. Bob’s Rawhide Company has a dividend payout ratio of 65%. Next year it will earn $1.25 per share and have a return on equity of 11%. The shareholders’ required return is 8%
a. Calculate the company’s growth rate of EPS.
b. Using the earnings model, what is the value of the stock?
c. Construct a data table that shows how the growth rate and value of the stock will change if the ROE ranges between 10% and 30%, in 1% increments. Now, using that data, create a scatter chart to show the relationship between the value of the stock and the ROE. Is the relationship linear? At what point does the model break down?
d. Using the constant-growth model, what is the value of the stock?
