FCFFginzu.xls Using the Valuation Spreadsheet by drr10525

VIEWS: 52 PAGES: 13

									         FCFFginzu.xls
Using the Valuation Spreadsheet

         Aswath Damodaran
            A podcast




                                  1
                                Overview

   Suited for: most non-financial service firms
   What the spreadsheet tries to do:
     – Value the operating assets of the firm by disconting the cashflows to the
       firm at the cost of capital
     – Augments this value with the value on non-operating assets such as cash
       and cross holdings
     – Estimates the value of equity and equity per share




                                                                                   2
                                 A picture of the model…
             Are you investing optimally for        Determinants of Firm Value
             future growth?
                                                                                                                Is there scope for more
                                                                                                                ef ficient utilization of exsting
                                         Growth from new investments                 Eff iciency Growth
How well do you manage your                                                                                     assets?
                                         Grow th created by making new               Grow th generated by
existing inv estments/assets?            inves tments; function of amount and        us ing exis ting ass ets
                                         quality of investments                      better


   Cashflows from existing assets
   Cas hflow s before debt payments ,
                                                        Expected Growth du rin g high growth period
   but after taxes and reinvestment to                                                                                  Stable grow th firm,
   maintain exis ing assets                                                                                             w ith no or very
                                                                                                                        limited exces s returns
                                                    Length of th e high growth period
          Are you building on your                  Since value creating grow th requires exc ess returns,
                                                    this is a func tion of
          competitive advantages?
                                                    - Magnitude of competitive advantages
                                                    - Sus tainability of competitive advantages



                                               Cost of capital to apply to discountin g cashflows
             Are you using the right           Determined by
             amount and kind of                - Operating ris k of the company
             debt for your f irm?              - Default risk of the company
                                               - Mix of debt and equity used in financing




                                                                                                                                                  3
             Inputs: Financial Statement numbers

Master Input Sheet
Do you want to capitalize R&D expenses?               No        ! Yes or No
Do you want to convert operating leases to debt?      Yes       ! Yes or No
Do you want to normalize operating income?            No

Inputs
From Current Financials
Current EBIT =                                     $3,455.00    ! If negative, go back and cho
Current Interest Expense =                            $0.00
Current Capital Spending                           $3,100.00
Current Depreciation & Amort'n =                    $486.00
Tax Rate on Income =                                 35.00%     Previous year-end
Current Revenues =                                 $12,154.00               8488
Current Non-cash Working Capital =                  ($404.00)
Chg. Working Capital =                              ($700.00)   Previous year-end
Book Value of Debt =                                  $0.00                     0
Book Value of Equity =                             $11,722.00               7191

Cash & Marketable Securities =                     $2,016.00
Value of Non-operating Assets =                    $7,032.00




                                                                                    4
               Capitalizing R&D
                                                                                       R & D Converter
This spreadsheet converts R&D expenses from operating to capital expenses. It makes the appropriate adjustments to operating income, net
income, the book value of assets and the book value of equity.

Inputs
Over how many years do you want to amortize R&D expenses                                            5
Enter the current year's R&D expense =                                                             $1,594.00
Enter R& D expenses for past years: the number of years that you will need to enter will be determined by the amortization period
Do not input numbers in the first column (Year). It will get automatically updated based on the input above.
      Year          R& D Expenses
       -1             1026.00       ! Year -1 is the year prior to the current year
       -2              698.00       ! Year -2 is the two years prior to the current year
       -3              399.00
       -4              211.00
       -5               89.00
        0
        0
        0
        0
        0

Output
      Year          R&D Expense               Unamortized portion               Amortization this year
    Current           1594.00               1.00             1594.00
       -1             1026.00               0.80              820.80                       $205.20
       -2              698.00               0.60              418.80                       $139.60
       -3              399.00               0.40              159.60                        $79.80
       -4              211.00               0.20               42.20                        $42.20
       -5               89.00               0.00                0.00                        $17.80
        0               0.00                0.00                0.00                         $0.00
        0               0.00                0.00                0.00                         $0.00
        0               0.00                0.00                0.00                         $0.00
        0               0.00                0.00                0.00                         $0.00
        0                0.00               0.00                  0.00                       $0.00
Value of Research Asset =                                          $3,035.40               $484.60

Amortization of asset for current year =                             $484.60

Adjustment to Operating Income =                                   $1,109.40 ! A positive number indicates an increase in operating income (add to reported EBIT)




                                                                                                                                                                    5
                     Capitalizing Leases…
                                                                                 Operating Lease Converter
Inputs
Operating lease expense in current year =                                          $121.00
Operating Lease Commitments (From footnote to financials)
      Year           Commitment ! Year 1 is next year, ….
        1          $        156.00
        2          $        143.00
        3          $        122.00
        4          $        109.00
        5          $         97.00
  6 and beyond $            448.00

Output
Pre-tax Cost of Debt =                    5.75%        ! If you do not have a cost of debt, use the ratings estimator

From the current financial statements, enter the following
Reported Operating Income (EBIT) =                            $3,455.00 ! This is the EBIT reported in the current income statement
Reported Debt =                                                   $0.00 ! This is the interest-bearing debt reported on the balance sheet

Number of years embedded in yr 6 estimate =                    4          ! I use the average lease expense over the first five years
                                                                          to estimate the number of years of expenses in yr 6
Converting Operating Leases into debt
       Year          Commitment       Present Value
                1 $       156.00            $147.52
                2 $       143.00            $127.87
                3 $       122.00            $103.16
                4 $       109.00              $87.16
                5 $         97.00             $73.34
6 and beyond       $      112.00            $295.14 ! Commitment beyond year 6 converted into an annuity for ten years
Debt Value of leases =              $         834.19

Restated Financials
Depreciation on Operating Lease Asset =                                                                $92.69 ! I use straight line depreciation
Adjustment to Operating Earnings =                                                                     $47.97 ! PV of operating leases * Pre-tax cost of debt
Adjustment to Total Debt outstanding =                                                        $        834.19




                                                                                                                                                                6
                                 Normalizing Earnings..

Normalizing Earnings
Approach used to normalize earnings =                      3

If historical average,
Average Earnings before interest and taxes =              3500

If historical average RO C,
Historical average pre-tax return on capital =            22%

If sector margin
Pre-tax Operating Margin for Sector =                    14.72%           ! Look at industry average


Normalized Earnings before interest and taxes =           $1,788.67


Worksheet for normalization (Last 5 years of data)
                          -5                 -4            -3                    -2                -1            Total
Revenues                      2032                2376            2779          3155                      3248       13590
EBIT                            186                454              529         448                        383         2000
Operating Margin             9.15%              19.11%          19.04%              14.20%              11.79%      14.72%




                                                                                                                              7
                                   Market Inputs


Market Data for your firm
Is your stock currently traded?                      Yes
If yes, enter the following:
Current Stock Price =                              $64.88
Number of shares outstanding =                     6,890.00
Market Value of Debt =                              $0.00
If no, enter the following
Would you like to use the book value debt ratio?     No
If no, enter the debt ratio to use in valuation     35%

General Market Data
Long Term Riskfree rate=                            4.25%
Risk premium for equity =                           4.91%




                                                              8
                    Ratings and Options (if any)


Ratings
Do you want to estimate the firm's synthetic rating =                               No
If yes, choose the type of firm                                                      2
If not, what is the current rating of the firm?                                    BBB
Enter the cost of debt associated with the rating =                               5.75%

Options
Do you have equity options (management options, warrants) outstanding?              Yes
If yes, enter the number of options                                               439.00
Average strike price                                                              $22.52
Average maturity                                                                    6.8
Standard Deviation in stock price                                                  40%
Do you want to use the stock price to value the option or your estimated value?      P




                                                                                           9
                           A high growth period?
Valuation Inputs
High Growth Period
Length of high growth period =                                                            12
Beta to use for high growth period for your firm=                                        1.43
Do you want to keep the debt ratio computed from your inputs?                             Yes
If yes, the debt ratio that will be used to compute the cost of capital is              0.18%
If no, enter the debt ratio that you would like to use in the high growth period        7.00%
Do you want to keep the existing ratio of working capital to revenue?                     No
If yes, the working capital as a percent of revenues will be                            -3.32%
If no, enter the ratio of working capital to revenues to use in analysis                 12%
Do you want to compute your growth rate from fundamentals?                                Yes
If no, enter the expected growth rate in operating income for high growth period         15%
If yes, the inputs to the fundamental growth calculation (based upon your inputs) are
Return on Capital =                                                                     28.37%
Reinvestment Rate =                                                                     110.70%
Do you want to change these inputs?                                                       No
Return on Capital =                                                                     16.00%
Reinvestment Rate =                                                                     80.00%

Do you want me to gradually adjust your high growth inputs in the second half?           Yes


                                                                                                  10
                           Stable growth inputs



Stable Growth Period
Growth rate during stable growth period =                                           4.00%
Beta to use in stable growth period =                                                1.00
Risk premium for equity in stable growth period =                                   4.91%
Debt Ratio to use in stable growth period =                                         0.18%
Pre-tax cost of debt in stable growth period =                                      5.25%
Tax Rate to use in stable growth period =                                           35.00%
To compute the reinvestment rate in stable growth, you have two options
Do you want to compute reinvestment needs in stable growth based on fundamentals?     Yes
If yes, enter the return on capital that the firm will have in stable growth        12.00%
If no, enter capital expenditure as % of depreciation in stable growth               120%




                                                                                             11
       Valuation Summary and Diagnostics..


Length of High Growth Period =                      12      Forever
Growth Rate =                                     31.41%    4.00%
Debt Ratio used in Cost of Capital Calculation=   0.18%     0.18%
Beta used for stock =                              1.43      1.00
Riskfree rate =                                   4.25%     4.25%
Risk Premium =                                    4.91%     4.91%
Cost of Debt =                                    5.75%     5.25%
Tax Rate =                                        35.00%    35.00%
Return on Capital =                               28.37%    12.00%
Reinvestment Rate =                               110.70%   33.33%


                                                                      12
                               Reading the Output


Present Value of FCFF in high growth phase =                    $14,465.99
Present Value of Terminal Value of Firm =                       $109,386.19
Value of operating assets of the firm =                         $123,852.18
Value of Cash, Marketable Securities & Non-operating assets =    $9,048.00
Value of Firm =                                                 $132,900.18
Market Value of outstanding debt =                               $834.19
Market Value of Equity =                                        $132,065.99
Value of Equity in Options =                                    $13,853.02
Value of Equity in Common Stock =                               $118,212.96
Market Value of Equity/share =                                    $17.16




                                                                              13

								
To top