Stock Valuation Spreadsheet - Excel

Document Sample
Stock Valuation Spreadsheet - Excel Powered By Docstoc
					fbceffa3-152c-4ee3-9b63-33fc65806876.xls                                                                                           Corporate Valuation



                                                                                                                                   7/11/2007

                                               Chapter 15. Mini Case

          This spreadsheet has two major components, one for Corporate Valuation and one for Value Based Management. Click on the
          tabs in the lower left of the screen to switch between sections.
          The value of a company is the sum of: (1) the value of its assets-in-place, including their associated growth opportunities, which
          is called the value of operations and (2) the value of its non-operating assets, such as marketable securities and investments in
          non-controlled affiliates. The value of operations is the present value of the free cash flows produced by the assets-in-place and
          their associated growth opportunities.

          Situation
          You have been hired as a consultant to Kulpa Fishing Supplies (KFS), a company that is seeking to increase its value. KFS has
          asked you to estimate the value of two privately held companies that KFS is considering acquiring. But first, the senior
          management of KFS would like for you to explain how to value companies that don't pay any dividends. You have structured
          your presentation around the following questions.

          a. List the two types of assets that companies own. Answer: See Chapter 15 Mini Case Show

          b. What are assets-in-place? How can their value be estimated? Answer: See Chapter 15 Mini Case Show

          c. What are non-operating assets? How can their value be estimated? Answer: See Chapter 15 Mini Case Show

          d. What is the total value of a corporation? Who has claims on this value? Answer: See Chapter 15 Mini Case Show


          e. The first acquisition target is a privately held company in a mature industry. The company currently has free cash flow of
          $20 million. Its WACC is 10% and it is expected to grow at a constant rate of 5%. The company has marketable securities of
          $100 million. It is financed with $200 million of debt, $50 million of preferred stock, and $210 million of book equity.

            (1) What is its total corporate value? What is its value of equity?
            (2) What is its value of operations?
            (3) What is its MVA (MVA = Total corporate value – Total book value)?

          Claims on Corporate Value
          Debt holders have the first claim on corporate value. Preferred stockholders have the next claim and the remaining is left to
          common stockholders.

          INPUT DATA SECTION: Data used for valuation (in millions)

          Free Cash Flow                      $20.0
          WACC                                10%           Vop =         FCF1                     FCF1 = FCF0 * (1 + g)
          Growth                               5%                       (WACC-g)
          Marketable Securities              $100.0
          Debt                               $200.0
          Preferred Stock                    $50.0
          Book Value of Equity               $210.0                       $21.0
                                                            Vop =
                                                                           0.05

                                                            Vop =         $420.0

          Sources of Corporate Value
          Value of Operation                     $420.0
                                                                       Total Corporate Value =        $520.0
          Value of Non-operating Assets         $100.0

          Claims on Corporate Value
          Value of Debt                         $200.0
          Value of Preferred Stock               $50.0
          Value of Equity                       $270.0                   Value of Equity =Total Corporate Value - Value of
                                                                                    Debt - Value of Preferred

            (3.) What is its MVA (MVA = Total corporate value – total book value)?

           Market Value Added (MVA)




Michael C. Ehrhardt                                                    Page 1                                                                  9/20/2011
fbceffa3-152c-4ee3-9b63-33fc65806876.xls                                                                                                Corporate Valuation




          Total value of the firm            $520.00
          Minus: Book value of debt          $200.00
          Minus: Book value of preferred stock$50.00
          Minus: Book value of equity        $210.00

          MVA                                     $60.00                                                  Market
                                                            Non Op.                                       Value         Book Value
                                                Vop          Assets          Debt        Pref. Stock      Equity         Equity         MVA
          Market Value Sources                   $420.0          $100.0
          Market Value Claims                                                   $200.0            $50.0        $270.0
          Book Value Claims                                                     $200.0            $50.0                    $210.0          $60.00


               $600.0

               $500.0
                                                                                           MVA
               $400.0                                                                      Book Value Equity
                                                                                           Market Value Equity
               $300.0                                                                      Pref. Stock
                                                                                           Debt
               $200.0                                                                      Non Op. Assets
                                                                                           Vop
               $100.0

                  $0.0
                           Market Value       Market Value         Book Value
                             Sources            Claims               Claims



          f. The second acquisition target is a privately held company in a growing industry. The target has recently borrowed $40
          million to finance its expansion; it has no other debt or preferred stock. It pays no dividends and currently has no marketable
          securities. KFS expects the company to produce free cash flows of -$5 million in one year, $10 million in two years, and $20
          million in three years. After three years, free cash flow will grow at a rate of 6%. Its WACC is 10% and it currently has 10
          million shares of stock.

            (1.) 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)?

            (2.) What is its value of equity on a price per share basis?

          Calculate the value of operations for the following company.
                                                               Actual                              Projected
                                                                                     1               2         3                  4
          Long-term growth rate                                     6%
          Weighted Avg. Cost of Cap. (WACC)                     10.00%
          Free Cash Flow                                                        ($5.0)       $10.0        $20.0              $21.2
          Find the horizon value.
          The horizon value is the value of all the free cash flows in year 4 and beyond, discounted back to the
          end of year 3. The formula is:
                                    HVyear 3 = [FCFyear 3 * (1+g)] / [ WACC - g].


          Horizon Value                                                                                    $530.00

          Find the PV of the horizon value and of the free cash flows.
          PV of Horizon Value @ WACC         $398.20
          PV of free cash flows @ WACC         $18.75




Michael C. Ehrhardt                                                        Page 2                                                                   9/20/2011
fbceffa3-152c-4ee3-9b63-33fc65806876.xls                                                                              Corporate Valuation



          Value of Operations                   $416.94

          PROBLEM
          Find the value of equity and the price per share.

          Step 1: Find the total value of the firm.
          Value of operations                    $416.94
          Minus: Value of Debt                     $0.00

          Total value of firm                   $416.94

          Step 2: Find the value of equity.
          Total value of firm                  $416.94
          Minus: Value of debt                  $40.00
          Minus: Value of preferred stock        $0.00

          Value of equity                       $376.94


          Step 3: Find the price per share.


          Value of equity                      $376.94
          Divided by number of shares                10

          Price per share                        $37.69


          See the other worksheet tab (Value Based Management) for the rest of the Mini Case questions and answers.




Michael C. Ehrhardt                                                 Page 3                                                     9/20/2011
VALUE-BASED MANAGEMENT:                                                                                       7/11/2007

g. KFS is also interested in applying value-based management to its own divisions. Explain what value-based
management is. Answer: Chapter 15 Mini Case Show

h. What are the four value drivers? How does each of them affect value? Answer: Chapter 15 Mini Case Show

i. What is expected return on invested capital (EROIC)? Why is the spread between EROIC and WACC so important?
Answer: Chapter 15 Mini Case Show


j. KFS has two divisions. Both have current sales of $1,000, current expected growth of 5%, and a WACC of 10%.
Division A has high profitability (OP=6%) but high capital requirements (CR=78%). Division B has low profitability
(OP=4%) but low capital requirements (CR=27%). What is the MVA of each division, based on the current growth of
5%? What is the MVA of each division if growth is 6%?

Impact on MVA
Expected growth goes to 6%
             Division A Division B
Sales          $1,000      $1,000
Growth           5%          5%
WACC            10%         10%
OP               6%          4%
CR              78%         27%

                                      Division A                   Division B

Operating Profit                    6%            6%            4%            4%
Capital Requirements               78%           78%           27%           27%
Growth                              5%            5%            5%            5%
MVA                               -300.0        -300.0         300.0         300.0

                              Market Value Added


      400.0

      300.0

      200.0

      100.0

          0.0                                                          MVA
                Division
      -100.0
                        6%
      -200.0
                                        5%                6%
      -300.0                                 Division B
      -400.0



k. What is the EROIC of each division for 5% growth and for 6% growth? How is this related to MVA?

                         Division A                  Division B
Capital             780.00        780.00        270.00        270.00
Growth                5%            6%            5%            6%
Sales              $1,050.0      $1,050.0      $1,050.0      $1,060.0
NOPAT               $63.0         $63.6         $42.0         $42.4
EROIC                8.1%          8.2%         15.6%         15.7%
MVA                 -300.0        -360.0         300.0         385.0




l. List six potential managerial behaviors that can harm a firm’s value. Answer: Chapter 15 Mini Case Show
m. The managers at KFS have heard that corporate governance can affect shareholder value. What is corporate
governance? List five corporate governance provisions that are internal to a firm and are under its control. Answer:
Chapter 15 Mini Case Show

n. What characteristics of the board of directors usually lead to effective corporate governance? Answer: Chapter 15 Mini Case Show

o. List three provisions in the corporate charter that affect takeovers. Answer: Chapter 15 Mini Case Show

p. Briefly describe the use of stock options in a compensation plan. What are some potential problems with stock options
as a form of compensation? Answer: Chapter 15 Mini Case Show


q. What is block ownership? How does it affect corporate governance? Answer: Chapter 15 Mini Case Show

r. Briefly explain how regulatory agencies and legal systems affect corporate governance. Answer: Chapter 15 Mini Case
Show

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:32
posted:9/20/2011
language:English
pages:10
Description: Stock Valuation Spreadsheet document sample