1

CHAPTER 8:COMMON STOCK VALUATION

IM Problem Set & Solutions

CHAPTER 8:COMMON STOCK VALUATION

Instructor’s Manual Problem Set

1. Determine the price of a share of stock whose last annual dividend payment was $1.5 assuming a required rate of return of 15% and considering the following restrictions:

a. The dividend payment is expected to remain constant indefinitely.

Worksheet:
Formulas:

b. The dividend payment is expected to grow at a constant rate of 3% per year indefinitely.

Worksheet:
Formulas:

c. The dividend payment is expected to grow at a rate of 7% for four years and then immediately decline to 3% indefinitely. Calculate your solution twice, first time using the formula 8-5 on page 235 and second time using the macro FAME_TwoStageValue.

Worksheet:
Formulas:

d. The dividend payment is expected to grow at a rate of 7% for four years and the gradually decline over three years transition period to 3% indefinitely.Calculate your solution twice, first time using the formula 8-6 on page 236 and second time using the macro FAME_ThreeStageValue

Worksheet:
Formulas:

e. How does the calculated intrinsic values compare to the current price of $14? Use an IF statement to display whether the stock is undervalued, overvalued, or fairly valued.

Worksheet:
Formulas:

2. The following table contains information aboutthe estimated next year’s EPS,payout ratio, shareholders’ required rate of return, and return of equity offour different companies:

Company A / Company B / Company C / Company D
EPS / $0.75 / $1.00 / $1.25 / $1.50
Payout Ratio / 30% / 50% / 60% / 40%
Required rate of Return / 13% / 11% / 12% / 14%
ROE / 16.00% / 15.00% / 14.00% / 16.00%

a. Calculate each company’s future earnings growth rate.Using the earnings model, what is the value of the stock?

Worksheet:
Formulas:

b. Using the constant-growth dividend discount model, what is the value of the stock?

Worksheet:
Formulas:

c. Assume that the companies will experience the growth rate determined in part “a” for a short period of time and after that the firms will grow at a lower rate. These periods of times and second growth rates are the following:

Company #1 / Company #2 / Company #3 / Company #4
Growth Rate #2 / 5.00% / 4.00% / 3.00% / 4.00%
Growth Rate #1 Time / 2.00 years / 3.00 years / 4.00 years / 3.00 years

Using thetwo-stage dividend growth model, what is the value of the stock?Calculate your solution twice, first time using the formula 8-5 on page 235 and second time using the macro FAME_TwoStageValue.

Worksheet:
Formulas:

d. Assume that the transition between growth rate 1 and 2 will be gradually, rather than instantaneously. The forecasted transition periods are the following:

Company #1 / Company #2 / Company #3 / Company #4
Transition Period / 3.00 years / 4.00 years / 2.00 years / 4.00 years

Using the three-stage dividend growth model, what is the value of the stock?Calculate your solution twice, first time using the formula 8-6 on page 236 and second time using the macro FAME_ThreeStageValue

Worksheet:
Formulas:

e. Create a Scatter chart to show the relationship between the value of the stock and the dividend payout ratio using Company D. Can you observe any price that is extremely different from the rest? Interpret your results.

Worksheet:
Formulas:

Possible Answers: As the growth rate approaches the value of the discount rate, the denominator approaches zero and the prices tends to infinite. In cases like this the earnings models and the constant-growth dividend discount model yield nonsense results.

3.The financial information of four different companies is provided in the following table:

Company 1 / Company 2 / Company 3 / Company 4
Annual Dividend 2007 / $0.60 / $0.70 / $1.20 / $1.00
Annual Dividend 2008 / $0.70 / $0.77 / $1.14 / $1.20
Annual Dividend 2009 / $0.80 / $0.85 / $1.08 / $0.90
Annual Dividend 2010 / $0.90 / $0.93 / $1.03 / $1.10
Annual Dividend 2011 / $1.00 / $1.02 / $0.98 / $1.00
Required rate of Return / 10% / 16% / 12% / 14%

a. If you expect that the dividend of each company will growat the geometric mean growth rate of the last 5 years into the foreseeable future, what is the price at which you would be willing to buy these companies’ stocks?

Worksheet:
Formulas:

a. If you expect that the dividend of each company will growat the arithmetic mean growth rate of the last 5 years into the foreseeable future, what is the price at which you would be willing to buy these companies’ stocks?

Worksheet:
Formulas:

a. Use the TREND function to forecast the next dividend payment of each company.If you expect that the dividend of each company will growindefinitely at the growth rate implied by the forecasted value, what is the price at which you would be willing to buy these companies’ stocks?

Worksheet:
Formulas:

4. The financial information of four different companies is provided in the following table:

Company A / Company B / Company C / Company D
Last Dividend / $0.50 / $0.75
Expected Dividend / $1.25 / $0.90
Required rate of Return / 15% / 14% / 17% / 12%
Growth Rate #1 / 10.00% / 9.00% / 7.00% / 8.00%
Growth Rate #2 / 5.00% / 3.00% / 4.00% / 2.00%
Growth Rate #1 Time / 5.00 years / 3.00 years / 4.00 years / 2.00 years
Transition Period / 3.00 years / 2.00 years / 4.00 years / 3.00 years
Quoted Price / $6.88 / $8.00 / $10.50 / $9.00

a. If you expect that the dividend of each company will grow at the growth rate #2 into the foreseeable future, what is the price at which you would be willing to buy these companies’ stocks?

Worksheet:
Formulas:

b. Assume that the companies will grow at rate #1 for the period indicated in the table above. After that period of time, assume that the companies will grow at rate #2. Under these assumptions, what is the price at which you would be willing to buy these companies’ stocks using the two-stage dividend growth model?Calculate your solution twice, first time using the formula 8-5 on page 235 and second time using the macro FAME_TwoStageValue.

Worksheet:
Formulas:

c. Assume that the transition between growth rate #1 and #2 will be gradually, rather than instantaneously. Using thetransition period given in the table, what is the price at which you would be willing to buy these companies’ stocks using the three-stage dividend growth model?

Calculate your solution twice, first time using the formula 8-6 on page 236 and second time using the macro FAME_ThreeStageValue

Worksheet:
Formulas:

d. How does the calculated intrinsic values compare to the quoted price of the stock? Use an IF statement to display whether the stock is undervalued, overvalued, or fairly valued.

Worksheet:
Formulas:

5. Green Planet, Inc., a manufacturer of airfilter systems for industrial facilities, is considering the addition of a new system to its current product line. The following data has been forecasted:

2012 / 2013 / 2014 / 2015
Depreciation / 30,000 / 35,000 / 40,000 / 45,000
EBIT / 100,000 / 125,000 / 150,000 / 175,000
Investment in Operating Assets / 30,000 / 40,000 / 50,000 / 60,000

The market value of the firm’s debt is $0.5M, and it has $150K in marketable securities. The company also has 10K of preferred stocks that just paid an annual dividend of $0.75. Investors require a rate of return of 7% on preferred stocks of similar risk. Moreover, the firm has200K shares of stock outstanding and itsweighted average cost of capital is 15%. The expected tax rate is 35% in the next two years and 40% after that.

a. Calculate the free cash flow for each of the next three years.

Worksheet:
Formulas:

b. After 2015 the firm’s free cash flow is expected to grow at 5% per year indefinitely. What is the value of the stock today?

Worksheet:
Formulas:

c. Assume that after 2015 the firm’s free cash flow is expected to grow at 9% per year for four years. After that time, the firm’s free cash flow will grow at just 3% indefinitely. Using thetwo-stage dividend growth model, what is the value of the stock?Calculate your solution twice, first time using the formula 8-5 on page 235 and second time using the macro FAME_TwoStageValue.

Worksheet:
Formulas:
/ =NPV(B15,B10:E10)+FAME_TwoStageValue(E10*(1+B14),B15,B14,B27,B28)/(1+B15)^COUNT(B3:E3)
/ =NPV(B15,B10:E10)+(E10*(1+B14)/(B15-B14)*(1-((1+B14)/(1+B15))^B28)+(E10*((1+B14)^B28)*(1+B27))/(B15-B27)/(1+B15)^B28)/(1+B15)^COUNT(B3:E3)

d. Assume that the transition between 9% and 3% will be gradually, rather than instantaneously. The forecasted transition period is 3 years. Using the three-stage dividend growth model, what is the value of the stock?Calculate your solution twice, first time using the formula 8-6 on page 236 and second time using the macro FAME_ThreeStageValue

Worksheet:
Formulas:

6. The following table contains the last five-year dividend history for Exxon Mobil Corporation.

Microsoft, Corp.
Date / Dividends / Date / Dividends
11/8/2011 / 0.47 / 2/6/2009 / 0.4
8/10/2011 / 0.47 / 11/7/2008 / 0.4
5/11/2011 / 0.47 / 8/11/2008 / 0.4
2/8/2011 / 0.44 / 5/9/2008 / 0.4
11/9/2010 / 0.44 / 2/7/2008 / 0.35
8/11/2010 / 0.44 / 11/7/2007 / 0.35
5/11/2010 / 0.44 / 8/9/2007 / 0.35
2/8/2010 / 0.42 / 5/10/2007 / 0.35
11/9/2009 / 0.42 / 2/7/2007 / 0.32
8/11/2009 / 0.42 / 11/9/2006 / 0.32
5/11/2009 / 0.42 / 8/10/2006 / 0.32

a. Calculate the quarterly percentage change in the dividends and the compound quarterly growth rate of the dividends using the GEOMEAN function.

b. Annualize the quarterly dividend growth rate and calculate the intrinsic value of the stock using a 11% required rate of return and the calculated annual growth rate. Use the sum of the most recent four dividends as D0. How does the calculated intrinsic value compare to the historical price of the stock of $79.76 at11/08/2011?

Worksheet:
Formulas:

c. Use the Goal Seek option to find the implicit required rate of return setting the price to $79.76 (the price of Exxon Mobil Corporation at 11/08/2011) by changing the cell of the required rate of return

Worksheet:
Formulas:
The same as in part b

Test Bank

1. What should be the formula on cell C4 if you want to determine the expected rate of return of security X using the CAPM model? /
a. =B4+C3*D4-B4
b. =B4+C3*(D4-B4)
c. =B4-C3*(D4+B4)
d. =B4+B3*(D4-B4)
e. =B4+D3*(C4-B4)

Solution: b.

2. Suppose that a preferred stock is expected to pay a $0.75 dividend every quarter indefinitely. If the required rate of return is 10% with quarterly compounding,what should be the formula on cell B3 to determine the price of this stock? /
a. =B1/B2/4
b. =B1/B2
c. =B1/(B2/4)
d. =B1*4/(B2/4)
e. =B1/(B3

Solution: c.

3. What is thecorrect formula for cell B4 (the price of a share of stock) assuming that the last dividend payment of $1.12 is expected to grow at a constant rate of 5% per year indefinitely?Assume a required rate of return of 12%. /
a. =B1/(B3-B2)
b. =B1*(1+B2/B3-B2)
c. =B1*1+B2/B3-B2
d. =B1*(1+B2)/(B3-B2)
e. =B1*(1+B3)/(B3-B2)

Solution: d.

4. The last dividend payment of Metallica's share of stock was $0.80 and this dividend is expected to grow at 6% per year for three years. After that time, the dividend payment will grow at just 3% indefinitely. Using the two-stage dividend growth model, what is correct formula for cell B if the required rate of return on this stock is 15%?
a. =B1*(1+B3)/(B2-B3)*(1-((1+B3)/(1+B2))^B5)+(B1*(1+B3)^B5*(1+B4)/(B2-B4)/(1+B2)^B5)
b. =B1/(B2-B3)*(1+((1+B3)/(1+B2))^B5)+(B1*(1+B3)^B5*(1+B4)/(B2-B4)/(1+B2)^B5)
c. =B1*(1+B3)/B2-B3*1-(1+B3)/(1+B2)^B5+B1*(1+B3)^B5*(1+B4)/(B2-B4)/(1+B2)^B5
d. =B1*(1-B3)/(B2+B3)*(1+((1-B3)/(1-B2))^B5)-(B1*(1-B3)^B5*(1-B4)/(B2+B4)/(1-B2)^B5)
e. =B1*(1+B3)/(B2-B3)*(1-((1+B2)/(1+B3))^B5)+(B1*(1+B4)^B5*(1+B3)/(B2-B3)/(1+B2)^B5)

Solution: a.

5. The last dividend payment of Metallica's share of stock was $0.80 and this dividend is expected to grow at 6% per year for three years. After that time, the dividend payment will grow at just 3% indefinitely. Assume that the transition between 6% and 3% will be gradually, rather than instantaneously. The forecasted transition period is also of 3 years. Using the three-stage dividend growth model, what is correct formula for cell B7 if the required rate of return on this stock is 15%?
a. =B1/(B2-B4*(1+B4+B5+B5+B6)/2*(B3-B4))
b. =(B1/(B2-B4)*(1+B4+(B5+B6)/2*(B3-B4)))
c. =(B1/(B2+B4)*(1-B4-(B5-B5-B6)/2*(B3+B4)))
d. =(B1/(B2-B3)*(1+B2+(B5+B5+B6)/2*(B4-B3)))
e. =(B1/(B2-B4)*(1+B4+(B5+B5+B6)/2*(B3-B4)))

Solution: e.

6. What would be the value of cell B7? (Remember that the basic syntax of the FAME_TwoStageValue function is: FAME_TwoStageValue(DIV1,REQRATE,GROWTHRATE1,GROWTHRATE2,G1PERIODS)
a. $5.25
b. $7.42
c. $12.36
d. $22.44
e. $28.46

Solution: b.

7. What would be the value of cell B7? (Remember that the basic syntax of the FAME_ThreeStageValue function is: FAME_ThreeStageValue(DIV1,REQRATE,GROWTHRATE1,GROWTHRATE2,
G1PERIODS,TRANSPERIODS)
a. $7.77
b. $12.36
c. $18.22
d. $22.44
e. $28.46

Solution: a.

8. What would be the values of cellsB5, B6, and B7?
a. Undervalued, Undervalued, Overvalued
b.Undervalued, Overvalued, Overvalued
c. Overvalued, Overvalued, Undervalued
d. Undervalued, Undervalued, Undervalued
e. Overvalued, Overvalued, Overvalued

Solution: b.

9. What is the correct formula to determine the stock price using the earnings model on cell B6? /
a. =B1*B2/B3+(B1*B2*(B4/B3-1))/(B3-B5)
b. =B1/B3-(B1*(1+B2)*(B4/B3+1))/(B3+B5)
c. =B1/B3+B1*(1-B2*B4/B3-1)/(B3-B5)
d. =B1/B3+(B1*(1-B2)*(B4/B3-1))/(B3-B5)
e. =B1/B3+(B1*(1-B3)*(B2/B3-1))/(B3-B4)

Solution: d.

10. What is the correct formula for cell B5? /
a. =B8*(1-B10)+B9-B7
b. =B8-B10+B7-B9
c. =B8*(1-B10)+B7-B9
d. =B8*(1-B10)-B7+B9
e. =B8/(1-B10)*B7*B9

Solution: c.

11. Polaris Inc. stock is selling for $50 a share based on a 12 percent rate of return. What is the correct formula for cell B4 to calculate the next annual dividend payment if the dividends are expected to grow at 3% annually? /
a. =B2*(B1-B3)
b. =B1/(B2-B3)
c. =B1*(B2+B3)
d. =B1*(B2-B3)
e. =B3*(B2-B1)

Solution: d.

12. Millennium Inc. stock is selling for $20 a share based on a 15 percent rate of return. What is the correct formula for cell B4 to calculate the last annual dividend payment if the dividends are expected to grow at 3% annually? /
a. =B1*(B2-B3)/(1+B2)
b. =B1*(B2-B3)/(1+B3)
c. =B1*(B2+B3)/(1-B3)
d. =B1/(B2-B3)*(1+B3)
e. =B1*(B2+B3)/(1-B3)

Solution: b.

13. Moonlighting Inc. stock is selling for $10 and the last dividend annual payment was $0.75. What is the correct formula for cell B4 to calculate the required rate of return if dividends are expected to grow at 3% per year? /
a. =B2*(1-B3)/B1-B3
b. =B1*(B2-B3)/(1+B3)
c. =B2*(1+B3)/B1+B3
d. =B1/(B2-B3)*(1+B3)
e. =B2*(1+B3)/(B1+B3)

Solution: c.

14. Galaxy Inc. stock is selling for $20 and the last dividend annual payment was $2.25. What is the correct formula for cell B4 to calculate the dividend growth rate if the required rate of return is 15%? /
a. =(B1*B3-B2)/(B2+B1)
b. =(B1*B3+B2)/(B2-B1)
c. =(B1/B3-B2)/(B2-B1)
d. =(B1*B3-B2)*(B2+B1)
e. =(B1*B3+B2)-(B2+B1)

Solution: a.

15. Laser Speed Inc. stock is selling for $28 and the next dividend annual payment will be $2. What is the correct formula for cell B4 to calculate the dividend growth rate if the required rate of return is 10%? /
a. =(B1*B3-B2)/(B2+B1)
b. =(B1/B3-B2)/B1
c. =(B1*B3-B2)/(B2+B1)
d. =(B1*B3+B2)/B1
e. =(B1*B3-B2)/B1

Solution: e.