Loading documents preview...
Confidential 03/09/2015 22:22:55
NOTE: You can't rely on the formulas in this sample spreadsheet, because it has some circular references and pulls values from other worksheets which are not included. If you want to adapt it for use with the case, you will have to review the logic and formulas to be sure they are valid.
Sample Leveraged Buyout Analysis Purchase Price
($ in millions)
$771.9
EBITDA
58.0
Implied EBITDA multiple
13.3x
Sources and Uses Summary Uses of Funds Purchase Price of Equity Plus: Debt Refinanced Less: Cash Enterprise Value Senior Debt Financing Fees Subordinated Debt Financing Fees Advisory Fees Total Uses of Funds
Cash-for-Stock Purchase - Goodwill Not Deductible For Tax Purposes
Return Calculation $538.5 0.0 0.0 538.5 0.0 0.0 20.0 $558.5
Sources of Funds Revolver Term Loan B Senior subordinated notes Equity Other [Mgmt. Equity or Options] Total Sources of Funds
% 3.1% 22.4% 31.3% 43.1% 0.0% 100.0%
$17.5 125.0 175.0 241.0 0.0 $558.5
Int. Rate 7.3% 8.1% 10.9% 0.0% 0.0%
Equity %
2.5% 97.5% 0.0% 100.0%
Subordinated Debt IRR Equity IRR
Purchase Price Multiple Analysis 2000 Net Sales EBIT EBITDA
Cash Flow Analysis
Net Sales EBITDA Depreciation & Amortization EBITA Amortization of Intangibles Assets and Fin. Fees EBIT Less: Interest Expense Revolver Term Loan B Senior subordinated notes Total Interest Expense Pre-Tax Profit Cash Income Taxes @ Net Income
30.0%
+Depreciation and Amortization -Capital Expenditures, Incl. Acq. Earn-Out -Working Capital Requirements Cash Flow Available for Debt Repayment Cash Flow Available after Debt Repayment
Pro Forma 2000 $471.4 58.0 17.5 40.5 0.0 40.5
0.0
2001 $536.4 75.5 19.8 55.6 0.0 55.6
Fiscal Years Ending December 31, 2002 2003 2004 $618.0 $710.7 $816.5 89.1 109.1 134.3 22.9 26.3 30.2 66.2 82.8 104.1 0.0 0.0 0.0 66.2 82.8 104.1
2005 $938.2 162.1 34.7 127.4 0.0 127.4
2006 $985.1 $170.2 36.4 133.8 0.0 133.8
19.0 29.1
0.0 10.1 19.0 28.0
0.0 9.0 19.0 26.8
0.0 7.8 19.0 25.4
0.0 6.4 19.0 24.0
0.0 5.0 19.0 22.5
11.4 3.2 8.2
27.6 8.1 19.5
39.4 11.6 27.8
57.4 17.0 40.4
80.1 23.8 56.3
104.9 31.3 73.6
113.0 33.7 79.3
17.5 15.2 24.6 ($14.0)
19.8 18.5 4.3 $16.6 $0.0
22.9 18.5 (1.0) $33.2 $18.3
26.3 18.5 (0.3) $48.5 $33.4
34.7 18.5 (0.6) $90.5 $72.8
36.4 18.5 (0.6) $97.9 $140.8
30.2 # 18.5### (0.6) # $68.6 $52.3
Pro Forma 2000 1.4x 2.0x N/A N/A $58.0 2.5x 5.5x 56.9%
2001 2.0x 2.7x 5.2% 0.0% $75.5 1.7x 4.0x 53.6%
At Closing $17.5 125.0 175.0 241.0 $558.5 $0.0
2001 $0.9 125.0 175.0 260.5 $561.4 $0.0
IRR
Fiscal Years Ending December 31, 2002 2003 2004 2.5x 3.3x 4.3x 3.3x 4.3x 5.6x 9.9% 14.7% 19.8% 11.2% 23.3% 36.3% $89.1 $109.1 $134.3 1.2x 0.9x 0.6x 3.2x 2.5x 1.9x 49.8% 45.2% 39.8%
2005 5.7x 7.2x 25.4% 50.4% $162.1 0.4x 1.5x 34.1%
2006 6.4x 8.2x 31.4% 65.7% $170.2 0.3x 1.3x 28.8%
Revolver Term Loan B Senior Subordinated Debt Equity Total Capitalization Cash Balance
Fiscal Years Ending December 31, 2003 2004 $0.0 $0.0 95.9 79.6 175.0 175.0 328.7 385.0 $599.7 $639.6 $51.7 $104.0
2005 $0.0 62.0 175.0 458.6 $695.6 $176.8
2006 $0.0 42.9 175.0 537.9 $755.8 $140.8
EBITDA Times: Assumed Exit Multiple Equals: Implied Enterprise Value Less: Total Debt Outstanding Plus: Cash Balance Equals: Implied Enterprise Value
$75.5 5.5 $415.0 (179.2) $0.6 $236.4
$162.1 5.5 $891.5 (89.40) $0.6 $802.7
$170.2 6.5 $1,106.3 (89.40) $1.6 $1,018.5
$0.0 111.03 175.0 288.3 $574.4 $18.3
$89.1### 5.5 # $490.1### (159.70) $0.6### $331.0###
$109.1### 5.5 # $600.1### (138.40) $0.6### $462.3###
$134.3### 5.5 # $738.8### (114.70) $0.6### $624.7###
2002
1.0 x 9.7 x 7.1 x
0.9 x 8.1 x 6.0 x
27.21% (uses RATE function)
Acquisition Fees % Advisory Fees (Buyer's Inv. Banking, Legal, etc.) $ Advisory Fee Senior Term Debt Fee % Deferred Senior Debt Fee $ Amortization Period Annual Amortization
Capitalization 2002
2001
1.1 x 13.3 x 9.3 x
0.0 3.5 19.0 20.8 ###
Coverages & Debt Amortization
EBITA Interest Coverage EBITDA Interest Coverage Cumulative Total Debt Repaid Cumulative Senior Term Debt Repaid EBITDA Senior Debt / EBITDA Total Debt / EBITDA Total Debt / Total Capitalization
2005 EBITDA Exit Multiple 5.5x 6.0x #VALUE! #VALUE! 27.5% 29.9%
Subordinated Debt Cash Flows
0.0% $0.0 5 $0.0
1.0% $5.4 Subordinated Debt Fee % Deferred Sub. Debt Fee $ Amortization Period Annual Amortization
2.0% $3.5 5 $0.7