Docstoc

Financial Analysis Spreadsheet Template - Stock Valuation

Document Sample
Financial Analysis Spreadsheet Template - Stock Valuation Powered By Docstoc
					                                                            Main Menu




               Financial Analysis Spreadsheet Templates
                                              MAIN MENU -- CHAPTER 8

                    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
                    Copyright © 2000 Irwin/McGraw-Hill and KMT Software, Inc. (www.kmt.com)




File: d98d7d2f-1fae-4225-bb84-901ad0bbb9c9.xls Copyright © 1997 Richard D. Irwin, Inc.            Printed: 10/25/2011
                                                                       Main Menu




lates




 Edition
om)




           File: d98d7d2f-1fae-4225-bb84-901ad0bbb9c9.xls Copyright © 1997 Richard D. Irwin, Inc.   Printed: 10/25/2011
               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!




Problem: 8-4                                                 Copyright © 1997 Richard D. Irwin, Inc.       Printed: 10/25/2011
                         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




File: d98d7d2f-1fae-4225-bb84-901ad0bbb9c9.xls                     Copyright © 1997 Richard D. Irwin, Inc.         Printed: 10/25/2011
                         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




File: d98d7d2f-1fae-4225-bb84-901ad0bbb9c9.xls                     Copyright © 1997 Richard D. Irwin, Inc.            Printed: 10/25/2011
                                 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




File: d98d7d2f-1fae-4225-bb84-901ad0bbb9c9.xls                                Copyright © 1997 Richard D. Irwin, Inc.               Printed: 10/25/2011
                         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




File: d98d7d2f-1fae-4225-bb84-901ad0bbb9c9.xls                         Copyright © 1997 Richard D. Irwin, Inc.      Printed: 10/25/2011
                         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




File: d98d7d2f-1fae-4225-bb84-901ad0bbb9c9.xls                       Copyright © 1997 Richard D. Irwin, Inc.             Printed: 10/25/2011

				
DOCUMENT INFO
Shared By:
Stats:
views:5555
posted:3/27/2008
language:English
pages:8