FCFFginzuxls Using the Valuation Spreadsheet

Document Sample
FCFFginzuxls Using the Valuation Spreadsheet Powered By Docstoc
Optimizing Capital Structure

      Aswath Damodaran
         A podcast


   Suited for: most non-financial service firms
   What the spreadsheet tries to do:
     – Estimate the cost of equity and capital at the firm’s current debt
     – Estimate how both inputs would changes at alternative debt ratios
       (ranging from 0% to 90%)
     – Estimate the cost of capital at alternative debt ratios and the
       optimal debt ratio (based upon minimizing the cost of capital)

                             A picture of the model…
                                                                                            As debt ra tio increases,
                                                                                            interest expenses increase.
                             As debt ra tio increases, beta increases:                      Interest coverage ratio drops
Beta is unle vered beta      Levered Beta = Un leve red Beta (1+ (1 -t) (Debt/Equity))      and rating declines. This
Cost of eq uity is cost of                                                                  increa ses the cost of debt

                   Cost of Capital = Cost of Equity (Equ ity/(Debt + Equity)) + After-tax Cost of Debt (Debt/(Debt + Equity))

                                                                                         If interest expen ses exceed
                                                                                         the EBIT, th e tax benefit will
                                                                                         Tax rate = EBIT/Interest Exp *Tax rate

          As cost of capital changes,
          Change in firm value = (Old Cost of cap ital - New Cost of Capital)(Current firm value)(1+g) /
          (New Cost of Capital-g)
          If investors are rational, increase in stock price = Change in firm value/ Tota l number of shares outstanding
          If sto ck can b e bough t at tod ay’s price, Increase in stock price = Change in firm value/ Remain ing shares
          outsta nding .

                                                  Debt Ratio                                                     99%

                                       Thre e key assumptions:
                                       1. Operating inco me remains unchange d as the debt ratio change s
                                       2. All existin g debt is refinanced at th e new cost o f debt
                                       3. Debt is used to buy back stock rather tha n invest in projects

Before you start your inputs, set up for circular

       Inputs: Financial Statement numbers

Please enter the name of the company you are analyzing:     Lexmark
Financial Information
Earnings before interest, taxes and depreciation (EBITDA)   $767.80
Depreciation and Amortization:                              $200.90
Capital Spending:                                           $200.20
Interest expense on debt:                                   $22.10
Tax rate on ordinary income:                                27.40%
Current Rating on debt (if available):                       AAA
Interest rate based upon rating:                             5.00%

                          Input market-based data

Market Information
Number of shares outstanding:                                    103.5
Market price per share:                                          $54.81
Beta of the stock:                                                1.27
Book value of debt:                                          $            149.80
Can you estimate the market value of the outstanding debt?        No
If so, enter the market value of debt:
Do you want me to try and estimate market value of debt?          No
If yes, enter the average maturity of outstanding debt?           0.00
Do you have any operating leases?                                 yes

        Capitalizing Leases, if any…
                                                                                 Operating Lease Converter
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

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

                                            Market Inputs

General Market Data
Current long-term (LT) government bond rate:                                    4.65%
Risk premium (for use in the CAPM)                                              4.80%
Country default spread (for cost of debt)                                       0.00%

General Data
Which spread/ratio table would you like to use for your anlaysis?                1
Do you want to assume that existing debt is refinanced at the 'new' rate?        Yes
Do you want the firm's current rating to be adjusted to the synthetic rating?    Yes

                       The Output

                         RESULTS FROM ANALYSIS
                                 Current    Optimal   Change
D/(D+E) Ratio =                   4.11%      50.00%   45.89%

Beta for the Stock =                1.27      2.13     0.86
Cost of Equity =                   10.75%    14.85%   4.11%

AT Interest Rate on Debt =         3.92%     4.61%    0.69%

WACC                               10.47%    9.73%    -0.73%
Implied Growth Rate =              3.21%
Firm Value (no growth) =           $5,916    $6,362   $446
Firm Value (Perpetual Growth) =    $5,916    $6,603   $686
Value/share (No Growth) =          $54.81    $59.12   $4.31
Value/share (Perpetual Growth) =   $54.81    $61.44   $6.63

                       The implied growth rate!

Implied Growth Rate Calculation
Value of Firm =           $5,916
Current WACC =            10.47%
Current FCFF =          $415.93 ! I am ignoring working capital
Implied Growth Rate = 3.21%
If this number is >Riskfree rate, I use the riskfree rate as a perpetual growth rate.

                  Problem Checker:
    1. I am getting Div/0 and other errors all over!
    Why?
      – The iterative process in the spreadsheet will make your
        spreadsheet incredibly sensitive to small input errors. Thus,
        entering the riskfree rate as 5..30% instead of 5.30% can set the
        spreadsheet spinning towards spreadsheet hell.
    What to do?
      – If you love messing with Excel, you can try to coax the spreadsheet
        back from hell.
      – I find it quicker to open a fresh version of the spreadsheet and copy
        the input page (and the lease information, if any) into it.

    2. My cost of capital is higher at the optimal
                than it is currently…
   Why?
    1.   We work in 10% increments. Thus, if the true optimal debt ratio is
         26%, your reported optimal will be 30%. If your actual debt ratio is
         24%, you are already closer to the true optimal than the computed
            Use the actual debt ratio as the optimal debt ratio.
    2.   The optimal debt ratio is computed using synthetic ratings at every
         debt ratio. If your current cost of capital is computed using an
         actual rating and it is much higher than your synthetic rating (at
         the same debt ratio), you can get this result.
            Use the option (on page 8) to set the firm’s current rating = synthetic.
    3.   The interest expenses at each debt ratio in the table are computed
         on the assumption that all of the debt at that ratio is funded at the
         pre-tax cost of debt that is estimated for that ratio. The current
         interest coverage ratio reflects actual interest expenses and may
         yield a different rating.
            Use the option (on page 8) and answer “No” to the question of
             whether you want your existing debt refinanced at the new rate.
      3. I am getting an absurdly high (or low)
                 optimal debt ratio!
   Check to make sure that you have not mismatched units
    (everything on the input page should be in the same units) and
    that you are not carrying some other firm’s leases in your
   There are two key firm-specific factors that may drive a really
    high or low optimal debt ratio:
     – Marginal tax rate: Higher marginal tax rates will yield higher optimal
       debt ratios.
     – Cash flow generating capacity (relative to market value): Measures
       of cash flow (EBITDA/ Enterprise value) are highly correlated with
       optimal debt ratios.


Shared By: