# Financial Analysis Spreadsheet Template - Stock Valuation

Document Sample

```					                                                            Main Menu

Problem 8-4                             Problem 8-6                  Problem 8-9
Problem 8-12                            Problem 8-19                 Problem 8-21

Fundamentals of Corporate Finance by Ross, Westerfield, and Jordan -- Fifth Edition

lates

Edition
om)

Fundamentals of Corporate Finance
Ross, Westerfield, and Jordan -- Fifth Edition

Problem 8-4 Objective
Calculate stock value (per share).

Student Name:
Course Name:
Student ID:
Course Number:

Madonna Corporation will pay a \$4.00 per share dividend next year. The company pledges to increase its
dividend by 3 percent per year, indefinitely. If you require a 14 percent return on your investment, how
much will you pay for the company's stock today?

Solution
Instructions
Enter the dividend per share, growth rate of dividend and required return on investment to calculate the
stock value per share.

Dividend per share
Growth rate of dividend
Required return on investment
Stock value per share                   #DIV/0!

Fundamentals of Corporate Finance
Ross, Westerfield, and Jordan -- Fifth Edition

Problem 8-6 Objective
Understand the relationship between expected return, projected dividends, and stock value.

Student Name:
Course Name:
Student ID:
Course Number:

Suppose you know that a company's stock currently sells for \$60 per share and the required return on the
stock is 16%. You also know that the total return on the stock is evenly divided between a capital gains
yield and a dividend yield. If it's the company's policy to always maintain a constant growth rate in its
dividends, what is the current dividend per share?

Solution
Instructions
Enter the current dividend yield and stock price.

Current dividend yield                    FORMULA
Stock Price
Projected dividend                           \$0.00
Current dividend                             \$0.00

Fundamentals of Corporate Finance
Ross, Westerfield, and Jordan -- Fifth Edition

Problem 8-9 Objective
Calculate the value per share of stock when the growth of dividends is nonconstant.

Student Name:
Course Name:
Student ID:
Course Number:

Smashed Pumpkin Farms (SPF) just paid a dividend of \$4.50 on its stock. The growth rate in dividends is
expected to be a constant 7.5 percent per year, indefinitely. Investors require an 20% return on the stock
for the next three years, an 11 percent return for the next three years, and then a 12 percent return,
thereafter. What is the current share price for SPF stock?

Solution
Instructions
Enter the dividend growth rate and the required returns for the three investment holding periods below. Then
enter the formulas to calculate the present value of the stock.

Dividend growth rate
Period 1 (next three years) required return
Period 2 (three years) required return
Period 3 (indefinitely) required return

Projected Dividends
Year      Dividends
0          \$4.50
1          \$4.50
2          \$4.50
3          \$4.50
4          \$4.50
5          \$4.50
6          \$4.50
7          \$4.50

Year 6 Projected Stock Value                     FORMULA
Year 3 Projected Stock Value                     FORMULA
Year 0 Projected Stock Value                     FORMULA

Fundamentals of Corporate Finance
Ross, Westerfield, and Jordan -- Fifth Edition

Problem 8-12 Objective
Calculate the value per share of stock when the growth of dividends is nonconstant.

Student Name:
Course Name:
Student ID:
Course Number:

South Park Corporation is expected to pay the following dividends over the next four years: \$4.75, \$3, \$2, and \$1.
Afterwards, the company pledges to maintain a constant 9 percent growth rate in dividends, forever. If the
required return on the stock is 16 percent, what is the current price per share?

Solution
Instructions
With the projected stock value in year 4 already calculated for you, enter the formula to find the current
stock value.

Dividend growth rate                                9%
Required return on stock                           16%

Projected
Year      Dividends
1          \$4.75
2          \$3.00
3          \$2.00
4          \$1.00

Projected Stock Value in Year 4                \$16.57
Current Stock Value                          FORMULA

What-if Analysis
Instructions
Click one of the scenario buttons to see the "what if" question. With each scenario, refer back to the
original data of the problem. Solve the scenario question by utilizing the stock valuation template
given below. Be sure to print your results before moving on to the next scenario.

What-if:                                    (Scenario 1)
Dividend growth rate =                             10%

Student's "What-if" Solution
Scenario 1
Dividend growth rate
Required return on stock

Projected
Year      Dividends
1          \$4.75
2          \$3.00
3          \$2.00
4          \$1.00

Projected Stock Value in Year 4              #DIV/0!
Current Stock Value                          FORMULA

Scenario 2
Dividend growth rate
Required return on stock

Projected
Year      Dividends
1          \$4.75
2          \$3.00
3          \$2.00
4          \$1.00

Projected Stock Value in Year 4              #DIV/0!
Current Stock Value                          FORMULA

Fundamentals of Corporate Finance
Ross, Westerfield, and Jordan -- Fifth Edition

Problem 8-19 Objective
Calculate stock yields.

Student Name:
Course Name:
Student ID:
Course Number:

Consider four different stocks, all of which have a required return of 20 percent and a most recent
dividend of \$3.75 per share. Stocks W, X, and Y are expected to maintain constant growth rates in
dividends for the foreseeable future of:

Stock W                           10%
Stock X                            0%
Stock Y                           -5%

Those rates are per year. Stock Z is a growth stock that will increase its dividend by 20 percent for the
next two years and then maintain a constant 12 percent growth rate, thereafter. What is the dividend yield
for each of these four stocks? What is the expected capital gains yield? Discuss the relationship among
the various returns that you find for each of these stocks.

Solution
Instructions
Enter formulas to calculate current price and dividend yield.

Current Dividend                  \$3.75
Required Return                     20%

Stock Name                 Price  Dividend Yield Cap Gains Yld
Stock W                 FORMULA    FORMULA             20%
Stock X                 FORMULA    FORMULA             20%
Stock Y                 FORMULA    FORMULA             20%
Stock Z               FORMULA      FORMULA             20%

Stock Z Value Calculation
P2 Value                   \$75.60
P0 Value                FORMULA

Fundamentals of Corporate Finance
Ross, Westerfield, and Jordan -- Fifth Edition

Problem 8-21 Objective
Calculate the value of stock using a nonconstant growth assumption.

Student Name:
Course Name:
Student ID:
Course Number:

Mummeball Company just paid a dividend of \$3.50 per share. The company will increase its dividend by 15
percent next year and will then reduce its dividend growth rate by 5 percentage points per year until it
reaches the industry average of 5 percent, after which the company will keep a constant growth rate,
forever. If the required return on Mummeball stock is 13.75 percent, what will a share of stock sell for today?

Solution
Instructions
Enter formulas below to calculate the current stock price

Current dividend                              \$3.50
Year 1 Dividend growth rate                     15%
Year 2 Dividend growth rate                     10%
Year 3 Dividend growth rate                      5%
Required Return                              13.75%

Projected
Year   Dividends
1       \$4.03
2       \$4.43
3       \$4.65

Calculation of Current Price
Present Value of Year 1 Dividend      FORMULA
Present Value of Year 2 Dividend      FORMULA
Present Value of Year 3 Price         FORMULA
Stock Price                              \$0.00