Capital Structure by Dofz1YtU

VIEWS: 20 PAGES: 16

									                                                           PRELIMINARY STUFF AND INPUTS
Objective                               This spreadsheet allows you to compute the optimal capital structure for a financial
                                        service firm.
Before you start                        Open preferences in excel, go into calculation options and put a check in the iteration box.
                                        If it is already checked, leave it as is.
Inputs                                  The inputs are primarily in the input sheet. If your company has operating leases,
                                        use the operating lease worksheet to enter your lease or rental commitments.
Units                                   Enter all numbers in the same units (000s, millions or even billions)
Income inputs                           The key income input is the earnings before long term interest expenses and depreciation.
                                        Enter the most updated numbers you have for each (even if they are 12-month trailing
                                        numbers). If the most recent period for which you have data has an operating income that
                                        is abnormal, either because of extraordinary losses/gains or some other occurrence, use
                                        an average operating income over the last few years.
                                        Earnings before long term interest exp & depr = Net Income + taxes paid + long term
                                        interest expenses
                                        If long term interest expenses are not broken out, apportion the total interest expenses
                                        based upon how much long term debt the firm has, relative to total interest bearing debt.
                                        From the statement of cash flows, also enter the capital spending from the recent period.
                                        P.S: For financial service firms, both depreciation and capital spending are likely to be
                                        small numbers.
Balance Sheet                           Enter the book value of long term debt. If you have a market value enter that
                                        number. Alternatively, input the average maturity of the debt and I will estimate the
                                        market value of debt.
Market Data                             Enter the current stock price, the current long-term government bond rate, the risk
                                        premium you would like to use to estimate your cost of equity and the current rating for
                                        your firm. If you do not have a rating, there is an option for you at the very bottom of
                                        the spreadsheet to compute a synthetic rating.
Tax Rate                                Enter a marginal tax rate, if you can estimate it. Otherwise, use the effective tax rate.
Default Spreads                         This spreadsheet has interest coverage ratios, ratings and default spreads built into it in
                                        the worksheet. You can choose between two tables, one for large and stable
                                        firms, and the other for small or risky firms. If you want you can change the interest
                                        coverage ratios and ratings in these tables.
                                        READING THE OUTPUT
Summary                                 The summary provides a picture of your firm's current cost of capital and debt ratio, and
                                        compares it to your firm's optimal debt ratio and the cost of capital at that level. The
                                        firm value is computed at each debt ratio, based upon how the expected operating income
                                        and the cost of capital. The optimal debt ratio is that ratio at which firm value is
                                        maximized. It might not be the same point at which cost of capital is minimized.
Details                                 The details of the calculation at each debt ratio are below the summary.

References
Corporate Finance: Theory and Practice, Chapter 18
Applied Corporate Finance: Chapter 8
 l structure for a financial

 put a check in the iteration box.

 y has operating leases,
ntal commitments.

rest expenses and depreciation.
f they are 12-month trailing
 a has an operating income that
r some other occurrence, use

me + taxes paid + long term

n the total interest expenses
  to total interest bearing debt.
ending from the recent period.
 tal spending are likely to be

 et value enter that
 bt and I will estimate the

ment bond rate, the risk
uity and the current rating for
r you at the very bottom of

  use the effective tax rate.
 efault spreads built into it in
 r large and stable
ou can change the interest


  of capital and debt ratio, and
f capital at that level. The
 the expected operating income
at which firm value is
 capital is minimized.
Question
Q1: What do I do excel says there are circular references?
Q2: My spreadsheet has gone crazy. I get errors all over.
What did I do wrong?

Q3: I am entering the inputs for my company but the
optimal numbers do not seem to change from the
originals
Q4: I am getting an optimal debt ratio of 0%. This can't
be right. Can it?


Q5: My cost of capital at my optimal debt ratio is higher
than the current cost of capital. I thought it was supposed
to be lower.
Answer
Go into preferences, choose calculation options and make sure the iteration box has a check in it.
I am sorry to say this, but you probably just made an input error. While you might have
fixed it, the iterations in the spreadsheet make it very sensitive and the errors will not
go away. The only fix (Sorry, sorry…) is to copy the inputs into a fresh version of the spreadsheet.
You probably forgot to check the iteration box (see Q1)


Sure. If your operating income is either negative or very low, relative to your firm value,
you can end up at an optimal debt ratio of 0%. For instance, if you have EBIT of 100 on a
firm value of 10000, a 10% debt ratio would probably push you into a C rating and give
you a very high cost of capital.
Generally, you are right. However, I would suggest that you look at three factors:
 - If your optimal is just slightly higher or lower than your current debt ratio, it is possible that you
are closer to the optimal than the stated optimal. Let me explain. Assume that you are at a 24% debt ratio
and the optimal comes out to 30%. The true optimal is really somewhere around 30% since
I am constrained to work in 10% increments of the debt ratio. If the true optimal were
26%, your current debt ratio of 24% is closer to the optimal.
 - Rating Differences: One of the costs of rating a company based only on the interest
coverage ratio is that the rating might be very different from the actual rating. Thus, your
current cost of capital is based upon your current rating, and the optimal is based upon
the synthetic ratings, and the two don't match, the current and the optimal cost of capital
can be mismatched. You can get around this by switching to a synthetic rating for computing
the current cost of capital (in the input sheet).
 - Existing debt at low rates: I assume in the spreadsheet that existing debt gets refinanced at
the new pre-tax cost of debt at each debt ratio. Consequently, if you have a lot of old debt on
your books at much lower rates, the interest expense that I report will be much higher than
your actual interest expense. This, in turn, can affect your interest coverage ratio and rating.
This, too, you can fix by locking in debt at current rates in the input sheet.
Inputs
Please enter the name of the company you are analyzing:                             Hormel
Financial Information

Earnings before long term interest expenses, depreciation & amortization            $635.00
Depreciation and Amortization:                                                      $126.00
Capital Spending:                                                                   $126.00
Interest expense on long term debt:                                                 $28.00
Tax rate on ordinary income:                                                        40.00%
Current Rating on long term debt (if available):                                     AAA
Interest rate based upon rating:                                                     3.60%
Market Information

Number of shares outstanding:                                                       134.53
Market price per share:                                                             $31.08
Beta of the stock:                                                                   0.83
Book value of long term debt:                                                   $             450.00
Can you estimate the market value of the long term debt?                              No
If so, enter the market value of debt:
Do you want me to try and estimate market value of debt?                              Yes
If yes, enter the average maturity of outstanding debt?                              0.00
Do you have any operating leases?                                                     Yes
General Market Data

Current long-term (LT) government bond rate:                                         2.35%             (in percent)
Risk premium (for use in the CAPM)                                                   6.00%             (in percent)

General Data

Which spread/ratio table would you like to use for your anlaysis?                      2
Do you want to assume that existing debt is refinanced at the 'new' rate?             yes              (Yes or No)
Do you want the firm's current rating to be adjusted to the synthetic rating?         yes              (Yes or No)
                                                                 Operating Lease Converter
Operating lease expenses are really financial expenses, and should be treated as such. Accounting standards allow th
be treated as operating expenses. This program will convert commitments to make operating leases into debt and
adjust the operating income accordingly, by adding back the imputed interest expense on this debt.

Inputs
Operating lease expense in current year =                            $21.90
Operating Lease Commitments (From footnote to financials)
     Year        Commitment ! Year 1 is next year, ….
       1        $      10.00
       2        $        8.07
       3        $        6.76
       4        $        5.21
       5        $        4.51
 6 and beyond $        11.94

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

From the current financial statements, enter the following
Reported Operating Income (EBIT) =                    $509.00 ! This is the EBIT reported in the current income statement
Reported Interest Expenses =                           $28.00
Output
Number of years embedded in yr 6 estimate =            2      ! 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 $       10.00            $9.65
             2 $        8.07            $7.52
             3 $        6.76            $6.08
             4 $        5.21            $4.52
             5 $        4.51            $3.78
6 and beyond $          5.97            $9.49 ! Commitment beyond year 6 converted into an annuity for ten years
Debt Value of leases =          $       41.04

Restated Financials
Operating Income with Operating leases reclassified as debt =                  $      510.48
Interest expenses with Operating leases classified as debt =                   $       29.48
nverter


pense on this debt.




ed ratings estimator


 current income statement


over the first five years
f expenses in yr 6




nnuity for ten years
Inputs for synthetic rating estimation
Enter the type of firm =                    2 (Enter 1 if large financial service firm, 2 if smaller financial service firm)
Earnings before interest and taxes (EBIT) =                                           $509.00 (Add back only long term interest expense fo
Current interest expenses =                                                             $28.00 (Use only long term interest expense for fina
Current long term government bond rate =                                                 2.35%
Output
Interest coverage ratio =                         18.18
Estimated Bond Rating =                            AAA
Estimated Default Spread =                        1.25%
Estimated Cost of Debt =                          3.60%

For large financial service firms
 If interest coverage ratio is
       >              ≤ to        Rating is       Spread is    Drop in EBITDA
   -100000         0.199999          D             20.00%        -50.00%
       0.2         0.649999           C            15.00%        -40.00%
      0.65         0.799999          CC            12.00%        -40.00%
       0.8         1.249999         CCC            10.00%        -40.00%
      1.25         1.499999          B-             8.50%        -25.00%
       1.5         1.749999           B             7.25%        -20.00%
      1.75         1.999999          B+             6.00%        -20.00%
        2         2.2499999         BB              5.00%        -20.00%
      2.25          2.49999         BB+             4.25%        -20.00%
       2.5         2.999999         BBB             3.50%        -10.00%
        3          4.249999          A-             3.00%         -2.00%
      4.25         5.499999          A              2.50%          0.00%
       5.5         6.499999          A+             2.25%          0.00%
       6.5         8.499999         AA              1.75%          0.00%
      8.50          100000         AAA              1.25%          0.00%

For smaller and riskier financial service firms
 If interest coverage ratio is
 greater than         ≤ to       Rating is        Spread is    Drop in EBITDA
   -100000         0.499999          D             20.00%        -50.00%
       0.5         0.799999          C             15.00%        -40.00%
       0.8         1.249999         CC             12.00%        -40.00%
      1.25         1.499999        CCC             10.00%        -40.00%
       1.5         1.999999          B-             8.50%        -25.00%
        2          2.499999          B              7.25%        -20.00%
       2.5         2.999999         B+              6.00%        -20.00%
        3          3.499999         BB              5.00%        -20.00%
       3.5        3.9999999         BB+             4.25%        -20.00%
        4          4.499999        BBB              3.50%        -10.00%
       4.5         5.999999          A-             3.00%         -2.00%
        6          7.499999          A              2.50%          0.00%
       7.5         9.499999         A+              2.25%          0.00%
       9.5        12.499999         AA              1.75%          0.00%
      12.5          100000         AAA              1.25%          0.00%
er financial service firm)
Add back only long term interest expense for financial firms)
Use only long term interest expense for financial firms)
                                                                             CAPITAL STRUCTURE                                                                                               17


                                                       Hormel
Capital Structure                          Financial Market                       Income Statement
Current MV of Equity =          $4,181     Current Beta for Stock =     0.83      Current EBITDA =            $636
                                 $450
Market Value of interest-bearing debt =    Current Bond Rating =       AAA        Current Depreciation =      $126
# of Shares Outstanding =        134.53              Summary of Inputs            Current Tax Rate =         40.00%
                                  any)
Debt Value of Operating leases (if$41                                   Rate
                                           Long Term Government Bond2.35%=        Current Capital Spending= $126
Risk Premium =                   6.00%     Pre-tax cost of debt =      3.60%      Current Interest Expense =   $29

                                             RESULTS FROM ANALYSIS
                                                     Current Optimal                Change
                             D/(D+E) Ratio =         10.51%   20.00%                 9.49%
                                                                                                             Implied Growth Rate Calculation
                             Beta for the Stock =           0.83         0.89         0.06                   Value of Firm = $4,672
                             Cost of Equity =              7.33%        7.70%        0.37%                   Current WACC = 6.79%
                                                                                                             Current FCFF = $306.29 ! I am ignoring working capital
                             AT Interest Rate on Debt =    2.16%        2.16%        0.00%                                       0.22%
                                                                                                             Implied Growth Rate =
                                                                                                             If this number is >your riskfree rate, I use the riskfree rate as a perpetual growth rate.
                             WACC                        6.79%          6.59%        -0.19%
                             Implied Growth Rate =       0.22%
Assumes perpeutal growth                                $4,672
                             Firm Value (Perpetual Growth) =           $4,815        $143
                                                        $31.08
                             Value/share (Perpetual Growth) =          $32.14        $1.06

We use the following default spreads in our analysis. Change them in the input sheet if necessary:    Ratings comparison at current debt ratio
                              Rating       Coverage gt     and lt    Spread        Drop in EBITDACurrent Interest coverage ratio =         17.32
                              AAA                  12.5      100000        1.25%          0.00% Rating based upon coverage =               AAA
                              AA                     9.5 12.499999         1.75%          0.00% Interest rate based upon coverage =       3.60%
                              A+                     7.5   9.499999        2.25%          0.00% Current rating for company =               AAA
                              A                        6   7.499999        2.50%          0.00% Current interest rate on debt =           3.60%
                              A-                     4.5   5.999999        3.00%                                                           rating
                                                                                         -2.00% Drop in operating income based on current 0.00%
                              BBB                      4   4.499999        3.50%        -10.00%
                              BB                       3   3.499999        5.00%        -20.00%
                              B+                     2.5   2.999999        6.00%        -20.00%
                              B                        2   2.499999        7.25%        -20.00%
                              B-                     1.5   1.999999        8.50%        -25.00%
                              CCC                  1.25    1.499999       10.00%        -40.00%
                              CC                     0.8   1.249999       12.00%        -40.00%
                              C                      0.5   0.799999       15.00%        -40.00%
                              D                -100000     0.499999       20.00%        -50.00%
                                                                        CAPITAL STRUCTURE                                            18


Current beta=       0.83               Current Equity=       $4,181         Current Depreciation=                $126
Current Debt=      $491                Current EBITDA=        $636    Current Interest rate (Company)=           3.60%
Tax rate=         40.00%               Current Rating=         AAA           Current T.Bond rate=                2.35%
                                       Adjusted EBITDA =      $636
                                      WORKSHEET FOR ESTIMATING RATINGS/INTEREST RATES
D/(D+E)         0.00%        10.00%      20.00%       30.00% 40.00%   50.00%       60.00%        70.00%           80.00%    90.00%
D/E             0.00%        11.11%      25.00%       42.86% 66.67%  100.00%      150.00%       233.33%          400.00%   900.00%
$ Debt           $0           $467        $934        $1,402 $1,869   $2,336       $2,803        $3,271           $3,738    $4,205
Beta             0.78          0.83        0.89         0.97    1.20    1.44         1.80          2.39             3.59      7.18
Cost of Equity  7.00%         7.31%       7.70%        8.20%  9.53%   10.97%       13.12%        16.72%           23.90%    45.45%
% Drop in EBITDA0.00%         0.00%       0.00%      -40.00% -50.00% -50.00%       -50.00%      -50.00%          -50.00%   -50.00%
EBITDA          $636          $636        $636         $382   $318     $318         $318          $318             $318      $318
Depreciation    $126          $126        $126         $126   $126     $126         $126          $126             $126      $126
EBIT            $510          $510        $510         $256   $192     $192         $192          $192             $192      $192
Interest         $0            $17         $34         $173   $418     $522         $627          $731             $835      $940
Taxable Income  $510          $494        $477          $83   ($225)  ($330)        ($434)       ($539)           ($643)    ($748)
Tax             $204          $197        $191          $33    ($90)  ($132)        ($174)       ($215)           ($257)    ($299)
Net Income      $306          $296        $286          $50   ($135)  ($198)        ($261)       ($323)           ($386)    ($449)
(+)Deprec'n     $126          $126        $126         $126   $126     $126         $126          $126             $126      $126
Funds from Op.  $432          $422        $412         $176     ($9)   ($72)        ($135)       ($197)           ($260)    ($323)

Pre-tax Int. cov       ∞      30.35       15.17     1.48      0.46      0.37                  0.31      0.26       0.23      0.20
Funds/Debt             ∞       0.90        0.44     0.13      0.00     -0.03                  -0.05     -0.06      -0.07     -0.08
Likely Rating        AAA      AAA         AAA       CCC        D         D                      D         D          D         D
Pre-tax cost of debt 3.60%    3.60%       3.60%   12.35%    22.35%    22.35%                 22.35%    22.35%    22.35%    22.35%
Eff. Tax Rate       40.00%   40.00%      40.00%   40.00%    18.41%    14.73%                 12.27%    10.52%     9.20%     8.18%
                                                COST OF CAPITAL CALCULATIONS
D/(D+E)             0.00%    10.00%      20.00%   30.00%    40.00%    50.00%                  60.00%    70.00%    80.00%    90.00%
D/E                 0.00%    11.11%      25.00%   42.86%    66.67%   100.00%                 150.00%   233.33%   400.00%   900.00%
$ Debt               $0       $467        $934    $1,402    $1,869    $2,336                  $2,803    $3,271    $3,738    $4,205
Cost of equity      7.00%     7.31%       7.70%    8.20%     9.53%    10.97%                  13.12%    16.72%    23.90%    45.45%
Cost of debt        2.16%     2.16%       2.16%    7.41%    18.24%    19.06%                  19.61%    20.00%    20.29%    20.52%
Cost of Capital     7.00%
                       0      6.80%
                                 0        6.59%
                                             1     7.96%
                                                      0     13.01%
                                                                0     15.01%
                                                                          0                   17.01%
                                                                                                 0      19.01%
                                                                                                           0      21.01%
                                                                                                                     0      23.01%
                                                                                                                               0
                   $4,524
Value (perpetual growth)     $4,665      $4,815   $1,987     $903      $781                    $688      $615      $556      $507


                                        Interest cov Interest cov   RATING   Interest rate   Drop in
                                            Low          High                                EBITDA
                        CAPITAL STRUCTURE      19


-100000    0.499999    D    22.35%   -50.00%
   0.5     0.799999     C   17.35%   -40.00%
   0.8     1.249999    CC   14.35%   -40.00%
  1.25     1.499999   CCC   12.35%   -40.00%
   1.5     1.999999    B-   10.85%   -25.00%
    2      2.499999     B    9.60%   -20.00%
   2.5     2.999999    B+    8.35%   -20.00%
    3      3.499999    BB    7.35%   -20.00%
   3.5    3.9999999   BB+    6.60%   -20.00%
    4      4.499999   BBB    5.85%   -10.00%
   4.5     5.999999    A-    5.35%    -2.00%
    6      7.499999    A     4.85%     0.00%
   7.5     9.499999    A+    4.60%     0.00%
   9.5    12.499999    AA    4.10%     0.00%
  12.5      100000    AAA    3.60%     0.00%
                                 CAPITAL STRUCTURE   20




te as a perpetual growth rate.
                                       Chart - Cost of Equity



                              Cost of Equity and Beta: Debt Ratios

8.00                                                                              50.00%


                                                                                  45.00%
7.00

                                                                                  40.00%
6.00
                                                                                  35.00%

5.00
                                                                                  30.00%


4.00                                                                              25.00%

                                                                                           Beta
                                                                                  20.00%
3.00                                                                                       Cost of Equity


                                                                                  15.00%
2.00
                                                                                  10.00%

1.00
                                                                                  5.00%


0.00                                                                              0.00%
       0%   10%   20%   30%    40%       50%        60%         70%   80%   90%




                                               Page 21
Debt Ratio   Beta   Cost of Equity Bond RatingInterest rate on debt   Tax Rate Cost of Debt (after-tax)   WACC
   0%        0.78      7.00%          AAA            3.60%            40.00%            2.16%              7.00%
  10%        0.83      7.31%          AAA            3.60%            40.00%            2.16%              6.80%
  20%        0.89      7.70%          AAA            3.60%            40.00%            2.16%              6.59%
  30%        0.97      8.20%          CCC           12.35%            40.00%            7.41%              7.96%
  40%        1.20      9.53%           D            22.35%            18.41%           18.24%             13.01%
  50%        1.44     10.97%           D            22.35%            14.73%           19.06%             15.01%
  60%        1.80     13.12%           D            22.35%            12.27%           19.61%             17.01%
  70%        2.39     16.72%           D            22.35%            10.52%           20.00%             19.01%
  80%        3.59     23.90%           D            22.35%             9.20%           20.29%             21.01%
  90%        7.18     45.45%           D            22.35%             8.18%           20.52%             23.01%
Firm Value (G)
    $4,524
    $4,665
    $4,815
    $1,987
     $903
     $781
     $688
     $615
     $556
     $507

								
To top