Corporate Finance Institute. All right reserved. LBO Model for Company Inc. Live Case -> Sponsor IRR-> Error Checks -> Assumptions General Inputs LBO Target Name Company Inc. Share Price (if available) Operating Scenarios Cases--> Company Inc. $15.00 Transaction Close Date Year Forecast Starts Fiscal Year End (Month) Fiscal Year End (Day) Financial Reporting Units Currency Days in Year 31/03/2018 2018 12 31 $ 000s USD 365 Revenue Growth Rate Cost of Goods Sold Marketing & Advertising General & Admin (fixed costs) Accounts Receivable Days Accounts Payable Days Inventory Days Capital Expenditures (% Rev) Depreciation Rate (% PPE) 3.6% 40.0% 14.7% 8,400 12 30 75 10.0% 17.5% 3.6% 40.0% 14.7% 8,400 12 30 75 10.0% 17.5% 3.6% 38.0% 14.7% 7,900 12 30 75 9.6% 17.5% 3.6% 37.5% 14.7% 7,900 12 30 75 9.6% 17.5% 3.6% 35.0% 14.7% 8,100 12 30 75 9.2% 17.5% 3.6% 35.0% 14.7% 8,800 12 30 75 8.6% 17.5% 3.6% 35.0% 14.7% 8,800 12 30 75 8.6% 17.5% Normalized EBITDA for valuation 30,000 Company Inc. Debt Company Inc. Target Cash Restructuring Charges Minimum Cash Balance Other Closing Costs 1,500 16,000 3,200 Revenue Grow Rate Cost of Goods Sold Marketing & Advertising General & Admin (fixed costs) Accounts Receivable Days Accounts Payable Days Inventory Days Capital Expenditures (% Rev) Depreciation Rate (% PPE) 4.0% 40.0% 14.7% 8,400 12 30 75 10.0% 17.5% 4.5% 40.0% 14.7% 8,400 12 30 75 10.0% 17.5% 4.5% 38.0% 14.7% 7,900 12 30 75 9.6% 17.5% 5.2% 37.5% 14.7% 7,900 12 30 75 9.6% 17.5% 5.2% 35.0% 14.7% 8.100 12 30 75 9.2% 17.5% 5.8% 35.0% 14.7% 8,800 12 30 75 8.6% 17.5% 5.8% 35.0% 14.7% 8.800 12 30 75 8.6% 17.5% Tax Rate Company Inc. FD Shares Outstanding (000s) 30.0% 17,000 Company Inc. FD Shares Outstanding (000s) 17,000 Depreciation Rate (% PPE) 17.5% 17.5% 17.5% 17.5% 17.5% 17.5% 17.5% Error Checks Operating Balance Sheet Balances Transaction Balance Sheet Balances Cash Sweep can't be >100% of Cash Flow Sources and Uses of Cash Balance Debt Principle Repayment = 100% Revenue Growth Rate Cost of Goods Sold Marketing & Advertising General & Admin (fixed costs) Accounts Receivable Days Accounts Payable Days Inventory Days Capital Expenditures (% Rev) Depreciation Rate (% PPE) 3.0% 40.0% 14.7% 8,400 12 30 75 11.0% 17.5% 3.0% 40.0% 14.7% 8,400 12 30 75 11.0% 17.5% 2.5% 38.0% 14.7% 7,900 12 30 75 10.8% 17.5% 2.1% 37.5% 14.7% 7,900 12 30 75 10.8% 17.5% 1.2% 35.0% 14.7% 8,100 12 30 75 10.4% 17.5% 1.1% 35.0% 14.7% 8,800 12 30 75 10.1% 17.5% 0.9% 35.0% 14.7% 8,800 12 30 75 10.1% 17.5% Purchase Price Enterprise Value Less: Total Debt Plus: Cash Balance Equity Offer Value $/Share Premium (Discount) Revenue Growth Rate Cost of Goods Sold Marketing & Advertising General & Admin (fixed costs) Accounts Receivable Days Accounts Payable Days Inventory Days Capital Expenditures (% Rev) Depreciation Rate (% PPE) Entry EV/EBITDA Exit EV/EBITDA 5.3x 5.3x Direct Purchase Price Override $14.00 Financing Mandatory Principal Repayment (Loan Amortization) Year 2 Year 3 Year 4 Year 5 X EBITDA $ Value % of Total Financing Fee Coupon Sweep Year 1 Year 6 Year 7 Type Cash (target) Line of Credit Term Loan 1 0.30x 1.80x 1.8% 1.8% 2.5% 5.0% na 50.0% 0.0% 10.0% 0.0% 10.0% 0.0% 10.0% 0.0% 10.0% 0.0% 60.0% 0.0% 0.0% 0.0% 0.0% 2.20x 0.50x Term Loan 2 Sub Debt 1 Sponsor Equity Total 1.8% 1.8% 0.9% 7.5% 10.0% na 50.0% 0.0% na 10.0% 0.0% 0.0% 10.0% 0.0% 0.0% 10.0% 0.0% 0.0% 10.0% 0.0% 0.0% 10.0% 0.0% 0.0% 50.0% 0.0% 0.0% 0.0% 100.0% 0.0% Sources & Uses of Cash Sources of Cash Uses of Cash Cash Consideration Stock Consideration Target Debt - Replace Debt Financing Fees Equity Financing Fees Other Closing Costs Total Uses Total Sources Book Value Fair Value Adjustment Goodwill and Purchase Price Allocation Fair Market Value Cash Accounts Receivable Inventory Property, Plant & Equipment Identifiable Intangibles Identfable Assets 4,500 5,800 29,000 Purchase Price Allocation Total Purchase Price Net Book Value of Assets Excess Purchase Price Write-of Existing Goodwi Fair Value Adjustments Excess Purchase Price After Adjustments Goodwill Imported Questions - 10/06/2017 12:18 PM 10 What is the total debt/EBITDA in 2022 in the base case? > Review Later 1.5x 1.9x 1.7x 1.3x Page 10 of 20 Prev Page Next Page Imported Questions - 10/06/2017 12:18 PM 12 What is the closing balance for term loan 1 in 2021? Review Later 32,400 0 39,600 37,800 Page 12 of 20 Prev Page Next Page 13 What is the pro forma closing balance of cash Review Later 71,806 10,000 16,000 78,237 Page 13 of 20 Prev Page Next Page 14 What is the unlevered free cash flow in 2024 in the base case? Review Later 23,611 29,412 37,761 24,674 Imported Questions - 10/06/2017 12:18 PM 15 What is the terminal enterprise value in the upside case? Review Later 269,964 224,653 211,286 Page 15 of 20 Prev Page Next Page 16 The sponsor IRR for the upside case is Review Later 32.4% 35.6% 32.1% 30.5% Page 16 of 20 Prev Page Next Page 17 What is the NPV of equity value in the base case (assuming the discount rate is the same as the IRR)? Review Later 38,640 42,197 63,940 45,586 Page 17 of 20 Prev Page Next Page 18 Review Later In the downside case, what is the sponsor IRR if the entry EV/EBITDA is 6.5x and the exit EV/EBITDA is 6.0x? 16.0% 17.6% 18.1% 14.2% Page 18 of 20 Prev Page Next Page Imported Questions - 10/06/2017 12:18 PM 19 What is the investor IRR from term loan 12 Review Later 5.2% 4.3% 4.796 5.8% Page 19 of 20 Prev Page Next Page Imported Questions - 10/06/2017 12:18 PM 20 Review Later In the base case, what is the sponsor cash-on-cash return if the debt/EBITDA for term loan 1 is 1.75x and the debt/EBITDA for term loan 2 is 2.00x? (Round to one decimal place) 5.7 5.8 5.0 4.5 Page 20 of 20 Prev Page View Marked Questions Finished Corporate Finance Institute. All right reserved. LBO Model for Company Inc. Live Case -> Sponsor IRR-> Error Checks -> Assumptions General Inputs LBO Target Name Company Inc. Share Price (if available) Operating Scenarios Cases--> Company Inc. $15.00 Transaction Close Date Year Forecast Starts Fiscal Year End (Month) Fiscal Year End (Day) Financial Reporting Units Currency Days in Year 31/03/2018 2018 12 31 $ 000s USD 365 Revenue Growth Rate Cost of Goods Sold Marketing & Advertising General & Admin (fixed costs) Accounts Receivable Days Accounts Payable Days Inventory Days Capital Expenditures (% Rev) Depreciation Rate (% PPE) 3.6% 40.0% 14.7% 8,400 12 30 75 10.0% 17.5% 3.6% 40.0% 14.7% 8,400 12 30 75 10.0% 17.5% 3.6% 38.0% 14.7% 7,900 12 30 75 9.6% 17.5% 3.6% 37.5% 14.7% 7,900 12 30 75 9.6% 17.5% 3.6% 35.0% 14.7% 8,100 12 30 75 9.2% 17.5% 3.6% 35.0% 14.7% 8,800 12 30 75 8.6% 17.5% 3.6% 35.0% 14.7% 8,800 12 30 75 8.6% 17.5% Normalized EBITDA for valuation 30,000 Company Inc. Debt Company Inc. Target Cash Restructuring Charges Minimum Cash Balance Other Closing Costs 1,500 16,000 3,200 Revenue Grow Rate Cost of Goods Sold Marketing & Advertising General & Admin (fixed costs) Accounts Receivable Days Accounts Payable Days Inventory Days Capital Expenditures (% Rev) Depreciation Rate (% PPE) 4.0% 40.0% 14.7% 8,400 12 30 75 10.0% 17.5% 4.5% 40.0% 14.7% 8,400 12 30 75 10.0% 17.5% 4.5% 38.0% 14.7% 7,900 12 30 75 9.6% 17.5% 5.2% 37.5% 14.7% 7,900 12 30 75 9.6% 17.5% 5.2% 35.0% 14.7% 8.100 12 30 75 9.2% 17.5% 5.8% 35.0% 14.7% 8,800 12 30 75 8.6% 17.5% 5.8% 35.0% 14.7% 8.800 12 30 75 8.6% 17.5% Tax Rate Company Inc. FD Shares Outstanding (000s) 30.0% 17,000 Company Inc. FD Shares Outstanding (000s) 17,000 Depreciation Rate (% PPE) 17.5% 17.5% 17.5% 17.5% 17.5% 17.5% 17.5% Error Checks Operating Balance Sheet Balances Transaction Balance Sheet Balances Cash Sweep can't be >100% of Cash Flow Sources and Uses of Cash Balance Debt Principle Repayment = 100% Revenue Growth Rate Cost of Goods Sold Marketing & Advertising General & Admin (fixed costs) Accounts Receivable Days Accounts Payable Days Inventory Days Capital Expenditures (% Rev) Depreciation Rate (% PPE) 3.0% 40.0% 14.7% 8,400 12 30 75 11.0% 17.5% 3.0% 40.0% 14.7% 8,400 12 30 75 11.0% 17.5% 2.5% 38.0% 14.7% 7,900 12 30 75 10.8% 17.5% 2.1% 37.5% 14.7% 7,900 12 30 75 10.8% 17.5% 1.2% 35.0% 14.7% 8,100 12 30 75 10.4% 17.5% 1.1% 35.0% 14.7% 8,800 12 30 75 10.1% 17.5% 0.9% 35.0% 14.7% 8,800 12 30 75 10.1% 17.5% Purchase Price Enterprise Value Less: Total Debt Plus: Cash Balance Equity Offer Value $/Share Premium (Discount) Revenue Growth Rate Cost of Goods Sold Marketing & Advertising General & Admin (fixed costs) Accounts Receivable Days Accounts Payable Days Inventory Days Capital Expenditures (% Rev) Depreciation Rate (% PPE) Entry EV/EBITDA Exit EV/EBITDA 5.3x 5.3x Direct Purchase Price Override $14.00 Financing Mandatory Principal Repayment (Loan Amortization) Year 2 Year 3 Year 4 Year 5 X EBITDA $ Value % of Total Financing Fee Coupon Sweep Year 1 Year 6 Year 7 Type Cash (target) Line of Credit Term Loan 1 0.30x 1.80x 1.8% 1.8% 2.5% 5.0% na 50.0% 0.0% 10.0% 0.0% 10.0% 0.0% 10.0% 0.0% 10.0% 0.0% 60.0% 0.0% 0.0% 0.0% 0.0% 2.20x 0.50x Term Loan 2 Sub Debt 1 Sponsor Equity Total 1.8% 1.8% 0.9% 7.5% 10.0% na 50.0% 0.0% na 10.0% 0.0% 0.0% 10.0% 0.0% 0.0% 10.0% 0.0% 0.0% 10.0% 0.0% 0.0% 10.0% 0.0% 0.0% 50.0% 0.0% 0.0% 0.0% 100.0% 0.0% Sources & Uses of Cash Sources of Cash Uses of Cash Cash Consideration Stock Consideration Target Debt - Replace Debt Financing Fees Equity Financing Fees Other Closing Costs Total Uses Total Sources Book Value Fair Value Adjustment Goodwill and Purchase Price Allocation Fair Market Value Cash Accounts Receivable Inventory Property, Plant & Equipment Identifiable Intangibles Identfable Assets 4,500 5,800 29,000 Purchase Price Allocation Total Purchase Price Net Book Value of Assets Excess Purchase Price Write-of Existing Goodwi Fair Value Adjustments Excess Purchase Price After Adjustments Goodwill Imported Questions - 10/06/2017 12:18 PM 10 What is the total debt/EBITDA in 2022 in the base case? > Review Later 1.5x 1.9x 1.7x 1.3x Page 10 of 20 Prev Page Next Page Imported Questions - 10/06/2017 12:18 PM 12 What is the closing balance for term loan 1 in 2021? Review Later 32,400 0 39,600 37,800 Page 12 of 20 Prev Page Next Page 13 What is the pro forma closing balance of cash Review Later 71,806 10,000 16,000 78,237 Page 13 of 20 Prev Page Next Page 14 What is the unlevered free cash flow in 2024 in the base case? Review Later 23,611 29,412 37,761 24,674 Imported Questions - 10/06/2017 12:18 PM 15 What is the terminal enterprise value in the upside case? Review Later 269,964 224,653 211,286 Page 15 of 20 Prev Page Next Page 16 The sponsor IRR for the upside case is Review Later 32.4% 35.6% 32.1% 30.5% Page 16 of 20 Prev Page Next Page 17 What is the NPV of equity value in the base case (assuming the discount rate is the same as the IRR)? Review Later 38,640 42,197 63,940 45,586 Page 17 of 20 Prev Page Next Page 18 Review Later In the downside case, what is the sponsor IRR if the entry EV/EBITDA is 6.5x and the exit EV/EBITDA is 6.0x? 16.0% 17.6% 18.1% 14.2% Page 18 of 20 Prev Page Next Page Imported Questions - 10/06/2017 12:18 PM 19 What is the investor IRR from term loan 12 Review Later 5.2% 4.3% 4.796 5.8% Page 19 of 20 Prev Page Next Page Imported Questions - 10/06/2017 12:18 PM 20 Review Later In the base case, what is the sponsor cash-on-cash return if the debt/EBITDA for term loan 1 is 1.75x and the debt/EBITDA for term loan 2 is 2.00x? (Round to one decimal place) 5.7 5.8 5.0 4.5 Page 20 of 20 Prev Page View Marked Questions Finished