# FCFFginzuxls Using the Valuation Spreadsheet

Document Sample

```					        capstru.xls
Optimizing Capital Structure

Aswath Damodaran
A podcast

1
Overview

   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
ratio
– 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)

2
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
capital

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
decline:
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%
0%

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

3
Before you start your inputs, set up for circular
reasoning…

4
Inputs: Financial Statement numbers

Inputs
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%

5
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

6
Capitalizing Leases, if any…
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

7
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

8
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

9
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.

10
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
    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.

11
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
optimal.
   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.
12
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
worksheet.
   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.

13

```
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
 views: 4 posted: 3/7/2010 language: English pages: 13