SOLUTION TO SPREADSHEET PROBLEMS
8-22 The detailed solution for the spreadsheet problem, Ch08 P22 Build a Model
Solution.xlsx, is available at the textbook’s Web site.
Answers and Solutions: 8 – 21
MINI CASE
Your employer, a mid-sized human resources management company, is considering
expansion into related fields, including the acquisition of Temp Force Company, an
employment agency that supplies word processor operators and computer programmers to
businesses with temporary heavy workloads. Your employer is also considering the
purchase of a Biggerstaff & McDonald (B&M), a privately held company owned by two
friends, each with 5 million shares of stock. B&M currently has free cash flow of $24
million, which is expected to grow at a constant rate of 5%. B&M’s financial statements
report short–term investments of $100 million, debt of $200 million, and preferred stock of
$50 million. B&M’s weighted average cost of capital (WACC) is 11%. Answer the
following questions.
a. Describe briefly the legal rights and privileges of common stockholders.
Answer: The common stockholders are the owners of a corporation, and as such, they have
certain rights and privileges as described below.
b. What is free cash flow (FCF)? What is the weighted average cost of capital?
What is the free cash flow valuation model?
Answer: Free cash flow (FCF) is the cash flow available for distribution to all of a company’s
investors. FCF is generated by a company’s operations.
Mini Case: 8 – 22
Vop =
∑
∞
=
+
1t t
t
)WACC1(
FCF
c. Use a pie chart to illustrate the sources that comprise a hypothetical company’s total
value. Using another pie chart, show the claims on a company’s value. How is equity
a residual claim?
Answer: Total corporate value is sum of value of operations and value of nonoperating assets.
Some company’s also have growth options, but assume they are negligible for this
Mini Case: 8 – 23
d. 1. Suppose the free cash flow at Time 1 is expected to grow at a constant rate of gL
forever. If gL < WACC, what is a formula for the present value of expected free
cash flows when discounted at the WACC?
Answer:
d. 2. If the most recent free cash flow is expected to grow at a constant rate of gL
forever (and gL < WACC), what is a formula for the present value of expected
free cash flows when discounted at the WACC?
Answer:
e. 1. Use B&M’s data and the free cash flow valuation model to answer the following
questions. What is its estimated value of operations?
Answer:
e. 2. What is its estimated total corporate value? (This is the entity value.)
= $520 million
e. 3. What is its estimated intrinsic value of equity?
e. 4. What is its estimated intrinsic stock price per share?
f. 1. You have just learned that B&M has undertaken a major expansion that will
change its expected free cash flows to −$10 million in 1 year, $20 million in 2
years, and $35 million in 3 years. After 3 years, free cash flow will grow at a rate
of 5%. No new debt or preferred stock were added, the investment was financed
by equity from the owners. Assume the WACC is unchanged at 11% and that
there are still 10 million shares of stock outstanding. What is its horizon value
(i.e., its value of operations at year three)? What is its current value of
operations (i.e., at time zero)?
Answer:
Year
0
1
2
3
4
5
… t
Mini Case: 8 – 25
Year
0
1
2
3
4
5
… t
FCF
FCF1
FCF2
FCF3
0 WACC = 11% 1 2 3 gL = 5% 4 N
| | | | | • • • |
f. 2. What is its value of equity on a price per share basis?
Answer:
Value of operations
$480.67
$580.67
$330.67
Mini Case: 8 – 26
g. If B&M undertakes the expansion, what percent of B&M’s value of operations
at Year 0 is due to cash flows from Years 4 and beyond? Hint: use the horizon
value at t = 3 to help answer this question.
Answer: First, calculate the present value of the horizon value. Then divide the present value
of the horizon value by the Year 0 value of operations. This will show what percent
h. Based on your answer to the previous question, what are two reasons why
managers often emphasize short-term earnings?
Answer: 1. Changes in quarterly earnings can signal changes future in cash flows. This would
Mini Case: 8 – 27
i. Your employer also is considering the acquistion of Hatfield Medical Supplies.
You have gathered the following data regarding Hatfield, with all dollars
reported in millions: (1) most recent sales of $2,000; (2) most recent total net
operating capital, OpCap = $1,120; (3) most recent operating profitability ratio,
OP = NOPAT/Sales = 4.5%; and (4) most recent capital requirement ratio, CR =
OpCap/Sales = 56%. You estimate that the growth rate in sales from Year 0 to
Year 1 will be 10%, from Year 1 to Year 2 will be 8%, from Year 2 to Year 3
will be 5%, and from Year 3 to Year 4 will be 5%. You also estimate that the
long-term growth rate beyond Year 4 will be 5%. Assume the operating
profitability and capital requirement ratios will not change. Use this information
to forecast Hatfield’s sales, net operating profit after taxes (NOPAT), OpCap,
free cash flow, and return on invested capital (ROIC) for Years 1 through 4.
Also estimate the annual growth in free cash flow for Years 2 through 4. The
weighted average cost of capital (WACC) is 9%. How does the ROIC in Year 4
compare with the WACC?
Answer:
The operating items are forecast as follows: Sales1 = $2,000(1+0.10) = $2,200;
Scenario:
No Change
Actual
Forecast
0
1
2
3
4
Sales
$2,000
$2,200
$2,376
$2,495
$2,620
Mini Case: 8 – 28
j. What is the horizon value at Year 4? What is the value of operations at Year 4?
Which is larger, and what can explain the difference? What is the value of
operations at Year 0? How does the value of operations compare with the
current total net operating capital?
Answer:
HV4= FCF4(1 + gL)
(WACC − gL)=$48.025(1 + 0.05)
(0.09 −0.05)= $1,260.65
The value of operations is the sum of the PV of the horizon value plus the PVs of the
FCFs:
Mini Case: 8 – 29
k. What are value drivers? What happens to the ROIC and current value of
operations if expected growth increases by 1 percentage point relative to the
original growth rates (including the long-term growth rate)? What can explain
this? Hint: Use Scenario Manager.
Answer: Value drivers are the inputs to the FCF valuation model that managers are able to
influence: sales growth rates, operating profitability, capital requirements, and cost of
capital.
l. Assume growth rates are at their original levels. What happens to the ROIC and
current value of operations if the operating profitability ratio increases to 5.5%?
Now assume growth rates and operating profitability ratios are at their original
levels. What happens to the ROIC and current value of operations if the capital
requirement ratio decreases to 51%? Assume growth rates are at their original
levels. What is the impact of simultaneous improvements in operating
profitability and capital requirements? What is the impact of simultaneous
improvements in the growth rates, operating profitability, and capital
requirements? Hint: Use Scenario Manager.
Mini Case: 8 – 30
Answer: .
Scenario
No Change
Improve OP
g0,1
10%
10%
The improvement in operating profitability increases the ROIC, which increases the value of
operations.
Scenario
No Change
Improve CR
g0,1
10%
10%
g1,2
g2,3
g3,4
The improvement in capital requirements increases the ROIC, which increases the value of
operations.
Mini Case: 8 – 31
g1,2
g2,3
g3,4
Scenario
No Change
Improve OP and CR
g0,1
10%
10%
The improvements in operating profitability and capital requirements increased the ROIC, so
growth now adds substantial value.
Scenario
No Change
Improve All
g0,1
10%
11%
g1,2
g2,3
g3,4
Mini Case: 8 – 32
g1,2
g2,3
g3,4
m. What insight does the free cash flow valuation model give provide us about
possible reasons for market volatility? Hint: Look at the value of operations for
the combinations of ROIC and gL in the previous questions.
Answer: .
ROIC
gL
8.0%
8.8%
9.8%
10.8%
n. 1. Write out a formula that can be used to value any dividend-paying stock,
regardless of its dividend pattern
Answer: The value of any stock is the present value of its expected dividend stream:
Mini Case: 8 – 33
$1,756
$2,008
n. 2. What is a constant growth stock? How are constant growth stocks valued?
Answer: A constant growth stock is one whose dividends are expected to grow at a constant
rate forever. “Constant growth” means that the best estimate of the future growth rate
With this regular dividend pattern, the general stock valuation model can be
simplified to the following very important equation:
n. 3. What happens if a company has a constant gL that exceeds its rs? Will many
stocks have expected growth greater than the required rate of return in the short
run (i.e., for the next few years)? In the long run (i.e., forever)?
Answer: The model is derived mathematically, and the derivation requires that rs > gL. If gL is
greater than rs, the model gives a negative stock price, which is nonsensical. The
Mini Case: 8 – 34
o. Assume that Temp Force has a beta coefficient of 1.2, that the risk-free rate (the
yield on T-bonds) is 7%, and that the market risk premium is 5%. What is the
required rate of return on the firm’s stock?
Answer: Here we use the SML to calculate temp force’s required rate of return:
p. Assume that Temp Force is a constant growth company whose last dividend (D0,
which was paid yesterday) was $2.00 and whose dividend is expected to grow
indefinitely at a 6% rate.
p. 1. What is the firm’s current stock price?
Answer: We could extend the time line on out forever, find the value of Temp Force’s
dividends for every year on out into the future, and then the PV of each dividend,
Mini Case: 8 – 35
p. 2. What is the stock’s expected value one year from now?
Answer: After one year, D1 will have been paid, so the expected dividend stream will then be
D2, D3, D4, and so on. Thus, the expected value one year from now is $32.10:
Mini Case: 8 – 36
p. 3. What are the expected dividend yield, the capital gains yield, and the total
return during the first year?
Answer: The expected dividend yield in any year n is
While the expected capital gains yield is
Alternatively,
Capital Gains Yield = rs – Dividend Yield = 13% − 7% = 6%
The total yield is comprised of the dividend yield and the capital gains yield.
q. Now assume that the stock is currently selling at $30.29. What is its expected
rate of return?
Answer: The constant growth model can be rearranged to this form:
Mini Case: 8 – 37
r. Now assume that Temp Force’s dividend is expected to experience nonconstant
growth of 30% from Year 0 to Year 1, 20% from Year 1 to Year 2, and 10%
from Year 2 to Year 3. After Year 3, dividends will grow at a constant rate of
6%. What is the stock’s intrinsic value under these conditions? What are the
expected dividend yield and capital gains yield during the first year? What are
the expected dividend yield and capital gains yield during the fourth year (from
Year 3 to Year 4)?
Answer: Temp Force is no longer a constant growth stock, so the constant growth model is not
applicable. Note, however, that the stock is expected to become a constant growth
stock in 3 years. Thus, it has a nonconstant growth period followed by constant
Simply enter $2 and multiply by (1.30) to get D1 = $2.60; multiply that result by 1.25
to get D2 = $3.25, multiply that result by 1.15 to get D3 = $3.7375 and multiply that
result by 1.06 to get D4 = $3.9618. Then recognize that after year 3, Temp Force
Mini Case: 8 – 38
The dividend yield and the capital gains yield are:
s. Compare and contrast the free cash flow valuation model and the dividend
growth model.
Answer: You can apply FCF model in more situations, such as privately held companies,
t. What is market multiple analysis?
Answer: Analysts often use the P/E multiple (the price per share divided by the earnings per
share) or the P/CF multiple (price per share divided by cash flow per share, which is
Mini Case: 8 – 39
u. What is preferred stock? Suppose a share of preferred stock pays a dividend of
$2.10 and investors require a return of 7%. What is the estimated value of the
preferred stock?
Mini Case: 8 – 40