LBO Short Form Model

xlsx

School

University of Massachusetts, Amherst *

*We aren’t endorsed by this school

Course

403

Subject

Finance

Date

Jan 9, 2024

Type

xlsx

Pages

8

Uploaded by JusticeBook8498

Report
Updated 3/13/2021 1 Transaction Assumptions Purchase Year 1-Jan-16 EBITDA Purchase Multiple 8.0 Purchase Year EBITDA 409.0 Purchase EV 3,272.0 Less Existing Debt - Add Cash on Balance Sheet - Transaction Equity Value 3,272.0 Sponsor Equity Contribution 1,113.8 Sponson Equity as % of Sources of Cash 32.33% 2 Sources of Financing Debt Cap xEBITDA Amount Revolver (Floating) 500.0 - - 2.0% 0.0% Term Loan A (Floating) 3.2 1,308.8 2.3% 38.0% Term Loan B (Floating) - - 2.5% 0.0% Senior Notes (Fixed) 1.5 613.5 5.5% 17.8% Junior Notes (Fixed) 1.0 409.0 6.2% 11.9% Other Debt - - 8.0% 0.0% Total Debt 5.7 2,331.3 2.6% 67.7% Equity Management Equity 2.7 1,113.8 32.3% Sponsor Equity Excess Cash Excess Cash - Total Sources 8.4 3,445.1 100.0% 3 Income Statement & Free Cash Flow Forescast 2010 2011 2012 2013 2014 2015 Revenue 2,672.3 3,216.0 3,612.7 3,941.0 EBITDA 327.3 284.0 298.5 283.5 Capex (193.8) 254.9 (327.9) (297.7) Interest Rate (Spread) % of Sources
Net WC Change (6.7) (64.3) (22.0) (41.0) Interest Expenses Taxes 2010 2011 2012 2013 2014 2015 Tax Details D&A Charges (85.1) (104.8) (127.8) (151.6) Pretax Profit (Loss) Cash Flow Drivers Revenue Growth (YOY%) 20.3% 12.3% 9.1% EBITDA Margin 12.2% 8.8% 8.3% 7.2% D&A as % of Sales -3.2% -3.3% -3.5% -3.8% Capex as % of Sales -7.3% 7.9% -9.1% -7.6% NWC as % of Sales Change -0.3% -11.8% -5.5% -12.5% Taxes 2010 2011 2012 2013 2014 2015 Cash Flow Before Debt RePayments EBITDA Less Interest Expenses Less Taxes Less Changes in NWC Less CapEx Less Mandatory Debt Repatments Net Cash Flow Less Non-Mandatory Debt Repatments Net Cash Flow Cash Contributions/Withdrawls Cash Balance 121.0 4 Financing Payments 2010 2011 2012 2013 2014 2015 Benchmark Interest Rate 5.30% Debt Balances after Repayments Term Loan A (Floating) 1,308.8 Term Loan B (Floating) - Senior Notes (Fixed) 613.5 Junior Notes (Fixed) 409.0 Other Debt Total 2,331.3 Revolver (Floating) -
2010 2011 2012 2013 2014 2015 Mandatory Payments Revolver Amount - Interest Rate 7.30% Ending Balance - Interest Payments Term Loan A (Floating) Amount of Loan 1,308.8 Term 5 Interest Rate Ending Balance 1,308.8 Principal Payments Interest Payments Total Payments Term Loan B (Floating) Amount of Loan - Term 5 Interest Rate Ending Balance - Principal Payments Interest Payments Total Payments Senior Notes (Fixed) Amount 613.5 Term 5 Interest Rate Ending Balance 613.5 Principal Payments Interest Payments Total Payments Junior Notes (Fixed) Amount 409.0 Term 5 Interest Rate Ending Balance 409.0 Principal Payments Interest Payments Total Payments
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
2010 2011 2012 2013 2014 2015 Non-Mandatory Payments Revolver Term Loan A (Floating) Term Loan B (Floating) Senior Notes (Fixed) Junior Notes (Fixed) Total 5 Credit Statistics Net Debt to EBITDA EBITDA to Interest Expenses 6 IRR Calculations 2015 Cash Flow (1,113.8) IRR 21% Cash on Cash Mulitple 2.64 7 Sensitivity Analysis IRR EBITDA Margin Exit Multiple 9% 10% 11% 12% 7.50 7% 21% 8.00 10% 8.50 12% 17% 9.00 12% 28% Average 15% Median 12%
Sale Year 21-Dec-21 EBITDA Sale Multiple 8.5 Sale Year EBITDA 445.9 Sale EV 3,789.7 Less Sale Year Debt 1,022.5 Plus Sale Year Cash 169.7 Implied Equity Value 2,937.0 Less Management Equity Share - Sponsor Equity at Exist 2,937.0 Results Sponsor IRR 21% Sponsor Cash-on-Cash Return 2.64 Uses of Financing Purchase Price 3,272.0 Refinance Existing Debt - Transaction Fees 52.1 Cash for Operations 121.0 Other - Total 3,445.1 2016 2017 2018 2019 2020 4,166.8 4,083.7 4,083.7 4,206.2 4,458.5 416.7 408.4 408.4 420.6 445.9 (125.0) (102.1) (102.1) (105.2) (111.5)
(11.3) 4.2 - (6.1) (12.6) (161.8) (133.9) (107.1) (96.9) (89.1) (33.1) (44.6) (54.0) (60.3) (68.7) 2016 2017 2018 2019 2020 (160.3) (147.0) (147.0) (151.4) (160.5) 94.6 127.5 154.3 172.3 196.2 5.7% -2.0% 0.0% 3.0% 6.0% 10.0% 10.0% 10.0% 10.0% 10.0% -3.8% -3.6% -3.6% -3.6% -3.6% -3.0% -2.5% -2.5% -2.5% -2.5% -5.0% -5.0% -5.0% -5.0% -5.0% -35.0% -35.0% -35.0% -35.0% -35.0% 2016 2017 2018 2019 2020 416.7 408.4 408.4 420.6 445.9 (161.8) (133.9) (107.1) (96.9) (89.1) (33.1) (44.6) (54.0) (60.3) (68.7) (11.3) 4.2 - (6.1) (12.6) (125.0) (102.1) (102.1) (105.2) (111.5) 85.5 131.9 145.2 152.1 164.0 (229.9) (149.2) (267.6) (276.7) (385.3) (58.9) 114.7 22.8 27.5 (57.4) - 100.0 - - (100.0) (58.9) 214.7 22.8 27.5 (157.4) - - - - - 62.1 276.8 299.6 327.1 169.7 2016 2017 2018 2019 2020 4.20% 3% 1% 0.80% 1% 1,078.9 829.7 562.1 285.3 - - - - - - 613.5 613.5 613.5 613.5 613.5 409.0 409.0 409.0 409.0 409.0 2,101.4 1,852.2 1,584.6 1,307.8 1,022.5 - 100.0 100.0 100.0 -
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
2016 2017 2018 2019 2020 6.20% 5.00% 3.00% 2.80% 3.00% - 100.0 100.0 100.0 - - - (3.0) (2.8) (3.0) 6.50% 5.30% 3.30% 3.10% 3.30% 1,078.9 829.7 562.1 285.3 - (229.9) (249.2) (267.6) (276.7) (285.3) (85.1) (57.2) (27.4) (17.4) (9.4) (314.9) (306.4) (295.0) (294.2) (294.7) 6.70% 5.50% 3.50% 3.30% 3.50% - - - - - - - - - - - - - - - - - - - - 9.70% 9.70% 9.70% 9.70% 9.70% 613.5 613.5 613.5 613.5 613.5 - - - - - (59.5) (59.5) (59.5) (59.5) (59.5) (59.5) (59.5) (59.5) (59.5) (59.5) 4.20% 4.20% 4.20% 4.20% 4.20% 409.0 409.0 409.0 409.0 409.0 - - - - - (17.2) (17.2) (17.2) (17.2) (17.2) (17.2) (17.2) (17.2) (17.2) (17.2)
2016 2017 2018 2019 2020 - 100.000 - - (100.0) - - - - - - - - - - - - - - - - - - - - - 100.000 - - (100.0) 2016 2017 2018 2019 2020 5.0 4.5 3.9 3.1 2.3 2.6 3.1 3.8 4.3 5.0 2016 2017 2018 2019 2020 - - - - 2,937.0 Margin Multiple 10% 8.5