Docstoc

LBO Valuation

Document Sample
LBO Valuation Powered By Docstoc
					44                                                            MERGER LBO VALUATION WORKSHEET                                                          44



                               INPUT SHEET: MERGER & LBO VALUATION
STEP 1: Estimate the total cost of the deal
Price per share=                    201         Debt outstanding currently (mil)=                       1800
# shares outstanding (mil)=         21.9      Other costs (Investment banker etc.)=                       0
Total cost of the deal=           6201.9


STEP 2: Define how the deal will be financed
                                                                       Repayment schedule ( as % of principal)
Source              Amount       Int. rate          1           2             3             4             5         6        7        8        9
Equity               1000           NA
Pfd. STOCK                0       0.00%
Debt: Type 1         1700         16.00%        0.00%         0.00%         0.00%        0.00%         0.00%      0.00%    0.00%    0.00%    0.00%
Debt: Type 2         3500         18.00%        0.00%         0.00%         0.00%        10.00%       10.00%      10.00%   10.00%   20.00%   20.00%
Debt: Type 3              0       0.00%
Debt: Type 4              0       0.00%
Interest rate on debt remaining in terminal year=            12.00%
Suggestion: For the first run, enter an arbitrary schedule; Check cashflows to equity; If -'ve modify schedule;


STEP 3: Define rates of future growth
                                     1              2            3            4             5             6         7        8        9        10
Revenues                          14.00%       14.00%        14.00%        14.00%        14.00%        8.00%      8.00%    8.00%    8.00%    8.00%
Depreciation (See below)          14.00%       14.00%        14.00%        14.00%        14.00%        8.00%      8.00%    8.00%    8.00%    8.00%
Cap. Spending (See below)         14.00%       14.00%        14.00%        14.00%        14.00%        8.00%      8.00%    8.00%    8.00%    8.00%
Working Cap : % of Revenue        16.00%       16.00%        16.00%        16.00%        16.00%       16.00%      16.00%   16.00%   16.00%   16.00%
            45                                                             MERGER LBO VALUATION WORKSHEET                                                                     45



             COGS: % of Revenues              85.68%        85.68%        85.68%       85.68%        85.68%        85.68%       85.68%       85.68%      85.68%      85.68%
             Default values= If you do not enter rates, the growth rates in depreciation, capital spending = growth rate in revenues.
The default values for COGS and Working Capital come from Step 6 below.


             STEP 4: GENERAL INFORMATION
             Current T.Bill rate=              8.00%                Market risk premium=              8.50%
             Ordinary tax rate=               40.00%                Current beta of firm=              1.05


             STEP 5: SPECIFY ASSETS TO BE SOLD (Enter the year in which assets will be sold and market value and CF to the firm from those assets)
                                                  1             2            3              4              5          6             7             8         9           10
             Assets sold: Mkt. Value             $0           $0            $0              $0         $0            $0            $0             $0       $0           $0
             Assets sold: Revenues               $0           $0            $0              $0         $0            $0            $0             $0       $0           $0
             Assets sold: COGS (%)             0.00%         0.00%         0.00%        0.00%         0.00%        0.00%         0.00%        0.00%      0.00%        0.00%
             Assets sold: Depreciation           $0           $0            $0              $0         $0            $0            $0             $0       $0           $0
             Assets sold: Capital Spending       $0           $0            $0              $0         $0            $0            $0             $0       $0           $0
             [ Enter the market value of the asset sold; Enter the EBIT, Depreciation and Capital Spending of the asset; Only in the year sold]


             STEP 6: ENTER INFORMATION ON CURRENT INCOME STATEMENT
             Revenues =                       $10,000                   COGS as % of Revenue=                      85.68%     (Default = 1 - (EBIT+Depreciation)/Revenues)
             Current EBIT =                   $892.00                   Current Capital Spending=                 $438.00
             Current Int. Exp=                $200.00                   Working Cap. as % of Revenues=             16.00%
             Current Deprec'n=                $540.00                   Interest rate on Debt Currently=           12.00%
46                                              MERGER LBO VALUATION WORKSHEET                                                  46



                                                  CASHFLOWS FROM LBO
                 PRE- LBO                                               AFTER LBO
                 CURRENT       1         2         3         4         5         6         7         8         9        10
Revenues         $10,000    $11,400   $12,996   $14,815   $16,890   $19,254   $20,794   $22,458   $24,255   $26,195   $28,291
COGS              $8,568    $9,768    $11,135   $12,694   $14,471   $16,497   $17,817   $19,242   $20,781   $22,444   $24,239
Depreciation      $540       $616      $702      $800      $912     $1,040    $1,123    $1,213    $1,310    $1,415    $1,528
EBIT              $892      $1,017    $1,159    $1,322    $1,507    $1,717    $1,855    $2,003    $2,164    $2,337    $2,524
-Int: Type 1      $200       $272      $272      $272      $272      $272      $272      $272      $272      $272      $272
-Int: Type 2       $0        $630      $630      $630      $630      $567      $504      $441      $378      $252      $126
-Int: Type 3       $0         $0        $0        $0        $0        $0        $0        $0        $0        $0        $0
-Int: Type 4       $0         $0        $0        $0        $0        $0        $0        $0        $0        $0        $0
Taxable Income    $692       $115      $257      $420      $605      $878     $1,079    $1,290    $1,514    $1,813    $2,126
- Taxes           $277       $46       $103      $168      $242      $351      $432      $516      $605      $725      $850
Net Income        $415       $69       $154      $252      $363      $527      $647      $774      $908     $1,088    $1,275
+ Deprec'n        $540       $616      $702      $800      $912     $1,040    $1,123    $1,213    $1,310    $1,415    $1,528
CF from Oper.     $955       $685      $856     $1,052    $1,275    $1,567    $1,770    $1,987    $2,218    $2,502    $2,803
- Capital Sp.     $438       $499      $569      $649      $740      $843      $911      $984     $1,062    $1,147    $1,239
- WC Chg          $196       $224      $255      $291      $332      $378      $246      $266      $287      $310      $335
- Prin. Rep:1      $0         $0        $0        $0        $0        $0        $0        $0        $0        $0        $0
- Prin. Rep:2      $0         $0        $0        $0       $350      $350      $350      $350      $700      $700      $700
- Prin. Rep:3      $0         $0        $0        $0        $0        $0        $0        $0        $0        $0        $0
- Prin. Rep:4      $0         $0        $0        $0        $0        $0        $0        $0        $0        $0        $0
- Pref. Div        $0         $0        $0        $0        $0        $0        $0        $0        $0        $0        $0
+ Asset Sales      $0         $0        $0        $0        $0        $0        $0        $0        $0        $0        $0
CF to Equity      $321       ($39)     $32       $112     ($147)      ($5)     $263      $387      $168      $344      $529
47                                       MERGER LBO VALUATION WORKSHEET                                        47



+ Pref. Div           $0    $0     $0     $0      $0       $0      $0      $0       $0       $0        $0
+ Int (1-t)       $120     $541   $541   $541    $541     $503    $466    $428     $390     $314      $239
+ Princ. Rep          $0    $0     $0     $0     $350     $350    $350    $350     $700     $700      $700
CF to firm        $441     $502   $573   $653    $744     $849   $1,079   $1,165   $1,258   $1,359   $1,467
Term Value of Equity=                                                                                $17,828
Term Value of Firm=                                                                                  $19,528
48                                                   MERGER LBO VALUATION WORKSHEET                                                                 48



                                        CAPITAL STRUCTURE AND COSTS OF EQUITY/CAPITAL

                  PRE-LBO                                                               AFTER-LBO
                  CURRENT      1            2          3           4           5            6          7           8           9           10
Debt: Type 1       $1,800    $1,700      $1,700      $1,700      $1,700      $1,700      $1,700      $1,700      $1,700      $1,700      $1,700
Debt: Type 2        $0       $3,500      $3,500      $3,500      $3,500      $3,150      $2,800      $2,450      $2,100      $1,400       $700
Debt: Type 3        $0         $0          $0          $0          $0          $0          $0          $0          $0          $0          $0
Debt: Type 4        $0         $0          $0          $0          $0          $0          $0          $0          $0          $0          $0
Pfd. Div            $0         $0          $0          $0          $0          $0          $0          $0          $0          $0          $0
Equity             $4,402    $1,000      $1,069      $1,223      $1,475      $1,838      $2,365      $3,012      $3,786      $4,694      $5,782
D/E                40.89%   520.00%      486.47%    425.09%     352.54%     263.91%      190.29%    137.78%     100.36%      66.04%      41.51%

D/(D+E+Pfd)        29.02%    83.87%      82.95%      80.96%      77.90%      72.52%      65.55%      57.94%      50.09%      39.77%      29.33%
Pfd/(D+E+Pfd)      0.00%     0.00%       0.00%       0.00%       0.00%       0.00%        0.00%      0.00%       0.00%       0.00%       0.00%


Beta                1.05      3.47        3.30        2.99        2.63        2.18         1.81       1.54        1.35        1.18        1.05

Cost of Equity     16.93%    37.53%      36.08%      33.45%      30.33%      26.51%      23.35%      21.09%      19.48%      18.01%      16.95%
Interest rate      12.00%    17.35%      17.35%      17.35%      17.35%      17.30%      17.24%      17.18%      17.11%      16.90%      16.58%
WACC               14.10%    14.78%      14.79%      14.80%      14.81%      14.81%      14.83%      14.84%      14.86%      14.88%      14.90%


Cum Cost of Eq.             1.3752667   1.8715293   2.4974714   3.2548554   4.1178778   5.0793672   6.1506589   7.3489454   8.6722131   10.142286
Cum WACC                    1.1478172   1.3175329   1.5124633   1.7364457   1.9936703   2.2892479   2.6290457   3.0198367   3.4691409   3.985966
49                       MERGER LBO VALUATION WORKSHEET                         49



     RESULTS OF THE MERGER/LBO ANALYSIS


                                   PV of CF     Investment   Decision
        Equity Investors           $1,974        $1,000       ACCEPT THE DEAL
        All Investors              $9,148        $6,200       ACCEPT THE DEAL




        Cashflow to Equity Investors
        Average FCFE =                 $164
        Maximum FCFE =                 $529
        Minimum FCFE=                  ($147)
        Std. Deviation of FCFE=        $214




                        Leverage

        D/E Ratio before LBO =     40.89%
        D/E Ratio after LBO =      520.00%
        D/E Ratio in year 5=       190.29%
        D/E Ratio in year 10 =     24.09%


                           Beta

        Beta before LBO =              1.05
        Beta after LBO =               3.47
        Beta in year 10 =              0.96
50            MERGER LBO VALUATION WORKSHEET   50




      10




     0.00%
 20.00%




After yr 10
     8.00%
     8.00%
     8.00%
 16.00%
51        MERGER LBO VALUATION WORKSHEET   51



 85.68%
52            MERGER LBO VALUATION WORKSHEET   52




Term Year
 $30,554
 $26,179
     $1,650
     $2,725
     $204
      $0
      $0
      $0
     $2,521
     $1,009
     $1,513
     $1,650
     $3,163
     $1,338
     $362
      $0
      $0
      $0
      $0
      $0
      $0
     $1,462
53            MERGER LBO VALUATION WORKSHEET   53



      $0
     $122
      $0
     $1,585
54            MERGER LBO VALUATION WORKSHEET   54




Term Year
     $1,700
      $0
      $0
      $0
      $0
     $7,057
     24.09%

     19.41%
     0.00%


      0.96

     16.20%
     12.00%
     14.45%

				
DOCUMENT INFO
Shared By:
Categories:
Stats:
views:123
posted:1/1/2011
language:English
pages:11
Description: Various Templates Carrying Capital budget analysis, Rating calculation, LBO Valuation etc