9 Review Later Complete the file named "LBO model - Questions" and use the completed model to answer questions 9 to 20 LBO Model - Questions.xlsx What is the adjustment amount for identifiable assets? (Round to the nearest integer) 9,243 6,875 7,312 10,691 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 9 Review Later Complete the file named "LBO model - Questions" and use the completed model to answer questions 9 to 20 LBO Model - Questions.xlsx What is the adjustment amount for identifiable assets? (Round to the nearest integer) 9,243 6,875 7,312 10,691 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