Instructions:

Homework Deliverable:

Instructions for this LBO Model: 1 Please be mindful of the number of digits you show throughout this (and any other) spreadsheet. For example, we don't really need to see the number of cents spent on capex when the number is in the millions. 2 The depreciation expense for the assets already owned is $10,000 per year and continues throughout the full projection. So you will add the depreciation from the projected capex to that. 3 Calculate purchase price in cell G28 Do not hard code that cell. 4 Calculate first year interest expense by multiplying the interest rate times the purchase price times the percentage of the purchase price financed with debt Use the "MAX" function to make sure interest expense is zero in the event the interest rate calculation produces an interest amount less than zero. For each year compute interest expense based on the ending balance from the previous year. Assume that any cash balances earn 2.00% 5 Since you are using a cash sweep, which means any available cash is "swept" to pay down the loan balance, you will need to calculate how much cash you will have available to pay down the loan each year. Assume that no new NWC is used, so your FCF calculaton will be simplified a bit. You can calculate the end of Year 1 loan balance entirely in cell G30, or you can make a calculation out to the side and then use the result 6 If the operating results (EBITDA, capex) lead to a need to borrow more money from the lender, allow the loan balance to increase 7 In keeping with the structure of most LBOs, we will assume that the equity holders do not get any dividends while debt is still outstanding. So our IRR will be determined by the initial equity investment and the value of the equity at the end of the five years, with no intervening cash paid to the equity. LBO Model Change in IRR as Purchase Multiple Changes and Exit Multiple Changes Exit Multiples 5 6 6.5 7 7.5 8 8.9 9 5.5 6 6.5 7 7.5 Purchase Multiples Growth Rate \% \begin{tabular}{cccc} Change in Multiple of Equity Investment Realized as Growth Rate Changes and Exit Multiple Changes (Column Figures) \\ \hline 5% & 6% & 7% & 8% \end{tabular} 5 6 7 Instructions for this LBO Model: 1 Please be mindful of the number of digits you show throughout this (and any other) spreadsheet. For example, we don't really need to see the number of cents spent on capex when the number is in the millions. 2 The depreciation expense for the assets already owned is $10,000 per year and continues throughout the full projection. So you will add the depreciation from the projected capex to that. 3 Calculate purchase price in cell G28 Do not hard code that cell. 4 Calculate first year interest expense by multiplying the interest rate times the purchase price times the percentage of the purchase price financed with debt Use the "MAX" function to make sure interest expense is zero in the event the interest rate calculation produces an interest amount less than zero. For each year compute interest expense based on the ending balance from the previous year. Assume that any cash balances earn 2.00% 5 Since you are using a cash sweep, which means any available cash is "swept" to pay down the loan balance, you will need to calculate how much cash you will have available to pay down the loan each year. Assume that no new NWC is used, so your FCF calculaton will be simplified a bit. You can calculate the end of Year 1 loan balance entirely in cell G30, or you can make a calculation out to the side and then use the result 6 If the operating results (EBITDA, capex) lead to a need to borrow more money from the lender, allow the loan balance to increase 7 In keeping with the structure of most LBOs, we will assume that the equity holders do not get any dividends while debt is still outstanding. So our IRR will be determined by the initial equity investment and the value of the equity at the end of the five years, with no intervening cash paid to the equity. LBO Model Change in IRR as Purchase Multiple Changes and Exit Multiple Changes Exit Multiples 5 6 6.5 7 7.5 8 8.9 9 5.5 6 6.5 7 7.5 Purchase Multiples Growth Rate \% \begin{tabular}{cccc} Change in Multiple of Equity Investment Realized as Growth Rate Changes and Exit Multiple Changes (Column Figures) \\ \hline 5% & 6% & 7% & 8% \end{tabular} 5 6 7