Leveraged Buy Out (LBO) Financial Model with Guide

Document Sample
Leveraged Buy Out (LBO) Financial Model with Guide Powered By Docstoc
					BUY OUT LEVERAGE TEMPLATE

The world of business valuation is a challenging world and requires prudent application of professional
judgment when performing the process. The value is determined as benefits stream divided by risk; this
might not be as simple as it looks. It entails detailed work of producing business projection and
calculating risk rate to discount the benefits. There are other factors which can influence the value of the
business such as changes in technology and which ought to be factored in business valuation.

The benefits stream used when valuing business are
         Net income earnings,
         Income from Operations
         Income before taxes,
         Earnings before interest and taxes,
         Earnings before interest, Taxes, Depreciation and Amortization (EBITDA),
         Cash Flow to Equity,
         Cash Flow to Invested Capital,
         Sales,
Benefit stream selected determines the collect value of the business, it is essential to evaluate benefit
streams and match them with the correct discounted rate. Business valuation is a complicated world
requires experience and skills; the valuation is simplified by the leverage buyout template. Research and
dedication is vital in shaping the true picture of the business and projecting the growth rate, objective is
always to gain a fair value of the business.

The leverage buy out template is applied in step by step procedures, the first step requires estimating the
cost of the deal, which is market price multiplied by the outstanding shares plus the outstanding debt
and underwriting cost. The second step identifies how the deal will be financed, this include equity
shares, preferred shares and debt to be acquired. The template is designed to assist the user to value the
company in prospective term growth of up to 10 years. The determined revenues growth rate is
automatically applied as the depreciation growth and capital spending growth. The working capital
growth rate is determined by the user of the template.
The forth step is filled with general information; the discounting rate information to be applied in
valuation. The information required is the Treasury bill rate, tax rate, risk premium and the firm beta
rate. This is the information for valuing the firm using capital asset pricing method (CAPM). Since after
acquisition not all assets might be required, the step after determining discounting information is to key
in the assets to be sold. The final step requires feeding the template with information from the current
income statement.

Business valuation requires a professional judgment supported by concrete research. Private held
business offers a greater challenge since the private business are not heavily regulated by agencies such
as SEC, but to overcome the handle of obtaining financial statements reflecting fair value of the
business, services of an auditor can be obtained
44                                                            MERGER LBO VALUATION WORKSHEET                                                             44



LEVERAGE BUY OUT VALUATION
Cost of the deal
Price per share                     20           Debt outstanding currently (mil)=                       1800
Outstanding (mil)                  21.9        Other costs (Investment banker etc.)=                       0
Total cost of the deal=            2238


Financing of the deal
                                                                        Repayment schedule ( as % of principal)
Source              Amount       Int. rate          1            2              3             4            5               6       7       8       9
Equity               2000           NA
Pfd. STOCK                0
Debt: Type 1         1200         5.00%
Debt: Type 2         2000         5.00%
Debt: Type 3          100        100.00%
Debt: Type 4          100         5.00%
Interest rate on debt remaining in terminal year=             12.00%
Suggestion: For the first run, enter an arbitrary schedule; Check cash flows to equity; If necessary, modify schedule;


Projected percentage rate growth
                                     1              2            3              4             5            6               7       8       9      10
Revenues                          5.00%         5.00%
Depreciation (See below)          5.00%         5.00%         0.00%          0.00%         0.00%        0.00%            0.00%   0.00%   0.00%   0.00%
Cap. Spending (See below)         5.00%         5.00%         0.00%          0.00%         0.00%        0.00%            0.00%   0.00%   0.00%   0.00%
Working Cap : % of Revenue        5.00%         5.00%         5.00%          5.00%         5.00%        5.00%            5.00%   5.00%   5.00%   5.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.


             Discounting information
             Current T.Bill rate               8.00%                 Market risk premium               8.50%
             Ordinary tax rate                40.00%                 Current beta of firm                 1.05


             Disposable assets (Assets to be Sales)
                                                  1             2             3               4            5            6               7           8         9           10
             Assets sold: Mkt. Value            $600          $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 (%)             5.00%         5.00%         5.00%            5.00%      5.00%         5.00%         5.00%          5.00%    5.00%        5.00%
             Assets sold: Depreciation         $0.00         $0.00         $0.00            $0.00       $0.00        $0.00         $0.00          $0.00     $0.00       $0.00
             Assets sold: Capital Spending     $0.00         $0.00         $0.00            $0.00       $0.00        $0.00         $0.00          $0.00     $0.00       $0.00
             [ Enter the market value of the asset sold; Enter the EBIT, Depreciation and Capital Spending of the asset; Only in the year sold]


             Income statement information
             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




                                                  CASH FLOWS FROM LBO
                 PRE- LBO                                             AFTER LBO
                 CURRENT       1         2         3         4         5         6         7         8         9        10
Revenues         $10,000    $10,500   $11,025   $11,025   $11,025   $11,025   $11,025   $11,025   $11,025   $11,025   $11,025
COGS              $8,568    $8,996    $9,446    $9,446    $9,446    $9,446    $9,446    $9,446    $9,446    $9,446    $9,446
Depreciation      $540       $567      $595      $595      $595      $595      $595      $595      $595      $595      $595
EBIT              $892       $937      $983      $983      $983      $983      $983      $983      $983      $983      $983
-Int: Type 1      $200       $60       $60       $60       $60       $60       $60       $60       $60       $60       $60
-Int: Type 2       $0        $100      $100      $100      $100      $100      $100      $100      $100      $100      $100
-Int: Type 3       $0        $100      $100      $100      $100      $100      $100      $100      $100      $100      $100
-Int: Type 4       $0         $5        $5        $5        $5        $5        $5        $5        $5        $5        $5
Taxable Income    $692       $672      $718      $718      $718      $718      $718      $718      $718      $718      $718
- Taxes           $277       $269      $287      $287      $287      $287      $287      $287      $287      $287      $287
Net Income        $415       $403      $431      $431      $431      $431      $431      $431      $431      $431      $431
+ Deprec'n        $540       $567      $595      $595      $595      $595      $595      $595      $595      $595      $595
CF from Oper.     $955       $970     $1,026    $1,026    $1,026    $1,026    $1,026    $1,026    $1,026    $1,026    $1,026
- Capital Sp.     $438       $460      $483      $483      $483      $483      $483      $483      $483      $483      $483
- WC Chg           $24       $25       $26        $0        $0        $0        $0        $0        $0        $0        $0
- Prin. Rep:1      $0         $0        $0        $0        $0        $0        $0        $0        $0        $0        $0
- Prin. Rep:2      $0         $0        $0        $0        $0        $0        $0        $0        $0        $0        $0
- 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        $600       $0        $0        $0        $0        $0        $0        $0        $0        $0
47                                         MERGER LBO VALUATION WORKSHEET                                 47



CF to Equity      $493     $1,085   $517   $544     $544     $544    $544   $544   $544   $544   $544
+ Pref. Div           $0    $0       $0     $0       $0       $0      $0     $0     $0     $0     $0
+ Int (1-t)       $120     $159     $159   $159     $159     $159    $159   $159   $159   $159   $159
+ Princ. Rep          $0    $0       $0     $0       $0       $0      $0     $0     $0     $0     $0
CF to firm        $613     $1,244   $676   $703     $703     $703    $703   $703   $703   $703   $703
Term Value of Equity=                                                                            $4,182
Term Value of Firm=                                                                              $7,582
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,200      $1,200     $1,200     $1,200      $1,200       $1,200      $1,200      $1,200      $1,200      $1,200
Debt: Type 2       $200      $2,000      $2,000     $2,000     $2,000      $2,000       $2,000      $2,000      $2,000      $2,000      $2,000
Debt: Type 3       $100       $100       $100        $100       $100        $100         $100        $100        $100        $100        $100
Debt: Type 4       $100       $100       $100        $100       $100        $100         $100        $100        $100        $100        $100
Pfd. Div           $100        $0         $0          $0         $0          $0           $0          $0          $0          $0          $0
Equity             $438      $2,000      $2,403     $2,834     $3,265      $3,696       $4,127      $4,558      $4,989      $5,420      $5,851
D/E               502.28%   170.00%     141.49%    119.97%     104.13%     91.99%       82.38%      74.59%      68.15%      62.73%      58.11%

D/(D+E+Pfd)        80.35%    62.96%      58.59%     54.54%     51.01%      47.91%       45.17%      42.72%      40.53%      38.55%      36.75%
Pfd/(D+E+Pfd)      3.65%     0.00%       0.00%      0.00%       0.00%      0.00%        0.00%       0.00%       0.00%       0.00%       0.00%


Beta                1.05      0.53        0.48       0.45        0.43       0.41         0.39        0.38        0.37        0.36        0.35

Cost of Equity     16.93%    12.49%      12.11%     11.82%     11.61%      11.45%       11.32%      11.22%      11.13%      11.06%      11.00%
Interest rate      12.00%    7.79%       7.79%      7.79%       7.79%      7.79%        7.79%       7.79%       7.79%       7.79%       7.79%
WACC               8.49%     7.57%       7.76%      7.93%       8.07%      8.21%        8.32%       8.42%       8.52%       8.60%       8.68%



Cum Cost of Eq.             1.1249174   1.2611606 1.410283605 1.5740591   1.7543034    1.9529386   2.1720349   2.4138436   2.6808273   2.9756879
49                                     MERGER LBO VALUATION WORKSHEET                                                                   49



Cum WACC   1.0757102    1.1591338 1.251005531     1.352018    1.4629517   1.5846788     1.7181678   1.8644905   2.0248301   2.2004906




                                                 LEVERAGE BUY OUT ANALYSIS
                                                  PV of CF   Investment      Decision
                       Equity Investors            $4,960      $2,000      ACCEPT THE DEAL
                       All Investors               $8,642      $5,400      ACCEPT THE DEAL

           Cash flow to Equity Investors                      Leverage                                Beta
           Average FCFE =                 $595   D/E Ratio before LBO = 502.28%
           Maximum FCFE =              $1,085    D/E Ratio after LBO =    170.00% Beta before LBO =               1.05
           Minimum FCFE=                  $517   D/E Ratio in year 5=      82.38%     Beta after LBO =            0.53
           Std. Deviation of FCFE=        $172   D/E Ratio in year 10 =    54.12%     Beta in year 10 =           0.35
50            MERGER LBO VALUATION WORKSHEET   50




      10




After yr 10


     0.00%
     0.00%
     5.00%
51        MERGER LBO VALUATION WORKSHEET   51



 85.68%
52            MERGER LBO VALUATION WORKSHEET   52




Term Year
 $11,025
     $9,446
     $595
     $983
     $144
     $240
      $12
      $12
     $575
     $230
     $345
     $595
     $941
     $483
      $0
      $0
      $0
      $0
      $0
      $0
      $0
53          MERGER LBO VALUATION WORKSHEET   53



     $458
      $0
     $245
      $0
     $703
54            MERGER LBO VALUATION WORKSHEET   54




Term Year
     $1,200
     $2,000
     $100
     $100
      $0
     $6,282
     54.12%

     35.11%
     0.00%


      0.35

     10.95%
     12.00%
     9.63%
55   MERGER LBO VALUATION WORKSHEET   55
				
DOCUMENT INFO
Description: This Leveraged Buy-Out Financial Model sets forth a spreadsheet that can be used to analyze a potential acquisition using debt. The model first calculates the cost of the transaction by taking the market price, multiplying it by the number of outstanding shares, and adding outstanding debt and underwriting costs. The model then identifies how the transaction will be financed. The model provides a valuation of the company based on growth rates over ten years; the spreadsheet uses the capital asset pricing model (CAPM) to value the target company. This spreadsheet is useful to companies or investors seeking to assess the value of a target company.
This document is also part of a package Financial Models Bundle 7 Documents Included