Please use the provided excel template to solve the question and please show work / formula of working cell for a thumb up.

Please use the provided excel template to solve the question and please show work / formula of working cell for a thumb up.

Please use the provided excel template to solve the question and please show work / formula of working cell for a thumb up.

Please use the provided excel template to solve the question and please show work / formula of working cell for a thumb up.

Please use the provided excel template to solve the question and please show work / formula of working cell for a thumb up.

Please use the provided excel template to solve the question and please show work / formula of working cell for a thumb up.

10-9 LBO VALUATION Randy Dillingwater is the chief investment officer for Clearstone Capital. Clearstone is a private equity firm located in Orlando, Florida, that specializes in what Randy describes as make-over or fixer-upper investments. The firm tries to find privately held firms whose owners tried to grow their business too fast and ran into liquidity problems Clearstone has been in this business for eleven years and has had reasonable success Clearstone is now completing the investment of its second fund and considering the acquisition of a local manufacturing and distribution company, Flanders Inc. Flanders was founded by Mark Flanders eighteen years ago and grew rapidly. Recently, how- ever, the firm made a large acquisition of a competitor firm, and the problems the firm encountered when assimilating the acquisition led to financial difficulties for Flanders. The owner has recently voiced his interest in a buyout proposal to his local banker, who notified Randy (his next-door neighbor) of the opportunity. Randy contacted Mark, and the two decided to open a dialogue about the possible acquisition of Mark's firm. After several meetings, Mark decided to solicit an offer from Clearstone. In response to Randy's request, Mark supplied him with the following set of pro forma income statements spanning 2016 to 2020: Pro Forma Income Statements 2016 2017 2018 2019 EBITDA $ 11,000,000.00 $ 12,100,000.00 $ 13,310,000.00 $ 14,641,000.00 Less: depreciation (3.900,000.00) (4,300,000.00) (4.700,000.00) (5,100,000.00) EBIT $ 7,100,000.00 $ 7,800,000.00 $ 8,610,000.00 $ 9,541,000.00 Less: interest (6,300,000.00) (6,235,600.00) (6,040,288.80) (5,690,457.10) Earnings before taxes $ 800,000.00 $ 1,564,400.00 $ 2,569,711.20 $ 3,850,542.90 Less: taxes (240,000.00) (469,320.00) (770,913.36) (1,155,162.87) Net income $ 560,000.00 $ 1,095,080.00 $ 1,798,797.84 $ 2,695,380.03 2020 $ 16,105,100.00 (5,500,000.00) $ 10,605,100.00 (5,159,103.90) $ 5,445,996.10 (1,633,798,83) $ 3,812,197.27 In addition, Randy asked Mark to estimate capital expenditures for each of the next five years. Mark indicated that he thought the firm would have to spend about $4 million a year and that the new capital would have a ten-year depreciable life. (Depreciation expense for 2015 was $3.5 million so the addition of $4 million in capital expenditures will add $400,000 in added depreciation expense for 2016.) Mark indicated to Randy that his research suggested that a five-times-EBITDA multiple would be appropriate. Randy, however, was not sure that Clearstone could afford to pay this much for the firm. He decided to do a quick analysis using the LBO method of valuation based on the following assumptions: . The firm can be purchased for five times the firm's 2015 EBITDA of $10 million and resold in five years for the same multiple of the firm's year 5 EBITDA. Clearstone will finance 90% of the purchase price using debt that carries a 14% rate of interest. The debt will require a cash sweep so that all available cash flow will go toward the repayment of the note. A tax rate of 30% is assumed in all calculations. Capital expenditures (CAPEX) are estimated to be $4 million per year, and no net new investments in working capital are anticipated. Flanders does not carry any excess cash and has no nonoperating assets. a. What is the projected enterprise value of Flanders in five years? What is the estimated value of Clearwater's equity in the firm at the end of five years if ev- erything works out as planned? b. What rate of return should Clearstone expect on its equity in the acquisition under the projections made above? Given $ 10,000,000.00 10.00% 30.00% Earnings Estimates Current year EBITDA (millions) Planning Period EBITDA growth rate Corporate tax rate Depreciable life of assets Depreciation expense (Year 0) LBO Capital Structure Debt/Assets Annual Capex Solution Legend - Value given in problem = Formula/Calculation/Analysis required = Qualitative analysis or Short answer required -Goal Seek or Solver cell = Crystal Ball Input = Crystal Ball Output 10 years 3,500,000.00 90.00% 4,000,000.00 Acquisition and sale EBITDA multiples Multiple Company purchase Company harvest 5 times 5 times Capital Costs Interest Rate on Debt 14.00% Industry LBO Model Valuation Purchase Price Year Depreciation Expense EBITDA Interest (1) Principal Payment Loan Balance 2 3 4 Less: Capital Expenditures 4,000,000.00 4,000,000.00 4,000,000.00 4,000,000.00 4,000,000.00 5 (1) Based on beginning balance a. Enterprise and equity values after 5 years. Firm Selling Price Equity Value (EOY 5) Equity Investment Rate of Return on Equity Selling price = Year 5 EBITDA x Harvest Multiple Equity value = Selling price - Loan balance EOY 5 Initial equity investment in the LBO Compound annual retum over 5 years Note: Corporate taxes are considered in calculating debt repayments interest paid before tax and principal repaid using after-tax earnings). However, the return on equity is after corporate but before personal tax. The after-tax return would reflect the tax circumstances of the investor. b. Rate of Return on equity 2015 2017 Platform Company --Analysis of Equity Free Cash Flow Pro Forma Income Statements 2016 2018 #REF! $11,000,000.00 $12,100,000.00 $13,310,000.00 $ Less: Depreciation Expense (3,900,000.00) (4,300,000.00) (4,700,000.00) $ 7,100,000.00 $ 7,800,000.00 $ 8,610,000.00 $ Less: Interest (will populate from LBO Model Eval) Earnings before Taxes $ 7,100,000.00 $ 7,800,000.00 $ 8,610,000.00 $ Less: Taxes (2,130,000.00)' (2,340,000.00)' (2,583,000.00) Net Income $ 4,970,000.00 $ 5,460,000.00 $ 6,027,000.00 $ 2019 2020 14,641,000.00 $16,105,100.00 (5,100,000.00) (5,500,000.00) 9,541,000.00 $ 10,605,100.00 EBIT 9,541,000.00 $ 10,605,100.00 (2.862,300.00)' (3,181,530.00) 6.678.700.00 $ 7,423,570.00 Calculation of Equity FCF Plus: Depreciation Less: CAPEX Less principal payments Equity Free Cash Flow Equity Investment Cash Flows Firm investment cash flows Security Valuation (Enterprise Value = multiple of estimated EBITDA, Debt value = book and Equity value = residual value) a. Enterprise and equity values after 5 years. Firm Selling Price Equity Value (EOY 5) Equity Investment Rate of Return on Equity Selling price = Year 5 EBITDA x Harvest Multiple Equity value = Selling price - Loan balance EOY 5 Initial equity investment in the LBO Compound annual return over 5 years Note: Corporate taxes are considered in calculating debt repayments interest paid before tax and principal repaid using after-tax earnings). However, the return on equity is after corporate but before personal tax. The after-tax return would reflect the tax circumstances of the investor. b. Rate of Return on equity Platform Company--Analysis of Equity Free Cash Flow Pro Forma Income Statements 2015 2016 2017 2018 #REF! $11,000,000.00 $ 12,100,000.00 $13,310,000.00 $ Less: Depreciation Expense (3,900,000.00) (4,300,000.00) (4,700,000.00) EBIT $ 7,100,000.00 $ 7,800,000.00 $ 8,610,000.00 $ Less: Interest (will populate from LBO Model Eval) Earnings before Taxes $ 7,100,000.00 $ 7,800,000.00 $ 8,610,000.00 $ Less: Taxes (2,130,000.00)' (2,340,000.00)' (2,583,000.00) Net Income $ 4,970,000.00 $ 5,460,000.00 6,027,000.00 $ 2019 2020 14.641,000.00 $16,105,100.00 (5,100,000.00) (5,500,000.00) 9,541,000.00 $ 10,605,100.00 9,541,000.00 $ 10,605,100.00 (2,862,300.00)' (3,181,530.00) 6,678,700.00 $ 7,423,570.00 Calculation of Equity FCF Plus: Depreciation Less: CAPEX Less principal payments Equity Free Cash Flow Equity Investment Cash Flows Firm investment cash flows 5 Security Valuation (Enterprise Value = multiple of estimated EBITDA, Debt value = book and Equity value = residual value) Year 2 3 Debt Equity Firm Value (EBITDA x harvest mult in 1-5) $ Evaluation of IRR on the Platform Company Investment Year 0 Year 5 Debt Equity Firm Internal Rates of Return (IRR) on LBO Firm's Equity Investment Equity (with debt) Equity (no debt) 10-9 LBO VALUATION Randy Dillingwater is the chief investment officer for Clearstone Capital. Clearstone is a private equity firm located in Orlando, Florida, that specializes in what Randy describes as make-over or fixer-upper investments. The firm tries to find privately held firms whose owners tried to grow their business too fast and ran into liquidity problems Clearstone has been in this business for eleven years and has had reasonable success Clearstone is now completing the investment of its second fund and considering the acquisition of a local manufacturing and distribution company, Flanders Inc. Flanders was founded by Mark Flanders eighteen years ago and grew rapidly. Recently, how- ever, the firm made a large acquisition of a competitor firm, and the problems the firm encountered when assimilating the acquisition led to financial difficulties for Flanders. The owner has recently voiced his interest in a buyout proposal to his local banker, who notified Randy (his next-door neighbor) of the opportunity. Randy contacted Mark, and the two decided to open a dialogue about the possible acquisition of Mark's firm. After several meetings, Mark decided to solicit an offer from Clearstone. In response to Randy's request, Mark supplied him with the following set of pro forma income statements spanning 2016 to 2020: Pro Forma Income Statements 2016 2017 2018 2019 EBITDA $ 11,000,000.00 $ 12,100,000.00 $ 13,310,000.00 $ 14,641,000.00 Less: depreciation (3.900,000.00) (4,300,000.00) (4.700,000.00) (5,100,000.00) EBIT $ 7,100,000.00 $ 7,800,000.00 $ 8,610,000.00 $ 9,541,000.00 Less: interest (6,300,000.00) (6,235,600.00) (6,040,288.80) (5,690,457.10) Earnings before taxes $ 800,000.00 $ 1,564,400.00 $ 2,569,711.20 $ 3,850,542.90 Less: taxes (240,000.00) (469,320.00) (770,913.36) (1,155,162.87) Net income $ 560,000.00 $ 1,095,080.00 $ 1,798,797.84 $ 2,695,380.03 2020 $ 16,105,100.00 (5,500,000.00) $ 10,605,100.00 (5,159,103.90) $ 5,445,996.10 (1,633,798,83) $ 3,812,197.27 In addition, Randy asked Mark to estimate capital expenditures for each of the next five years. Mark indicated that he thought the firm would have to spend about $4 million a year and that the new capital would have a ten-year depreciable life. (Depreciation expense for 2015 was $3.5 million so the addition of $4 million in capital expenditures will add $400,000 in added depreciation expense for 2016.) Mark indicated to Randy that his research suggested that a five-times-EBITDA multiple would be appropriate. Randy, however, was not sure that Clearstone could afford to pay this much for the firm. He decided to do a quick analysis using the LBO method of valuation based on the following assumptions: . The firm can be purchased for five times the firm's 2015 EBITDA of $10 million and resold in five years for the same multiple of the firm's year 5 EBITDA. Clearstone will finance 90% of the purchase price using debt that carries a 14% rate of interest. The debt will require a cash sweep so that all available cash flow will go toward the repayment of the note. A tax rate of 30% is assumed in all calculations. Capital expenditures (CAPEX) are estimated to be $4 million per year, and no net new investments in working capital are anticipated. Flanders does not carry any excess cash and has no nonoperating assets. a. What is the projected enterprise value of Flanders in five years? What is the estimated value of Clearwater's equity in the firm at the end of five years if ev- erything works out as planned? b. What rate of return should Clearstone expect on its equity in the acquisition under the projections made above? Given $ 10,000,000.00 10.00% 30.00% Earnings Estimates Current year EBITDA (millions) Planning Period EBITDA growth rate Corporate tax rate Depreciable life of assets Depreciation expense (Year 0) LBO Capital Structure Debt/Assets Annual Capex Solution Legend - Value given in problem = Formula/Calculation/Analysis required = Qualitative analysis or Short answer required -Goal Seek or Solver cell = Crystal Ball Input = Crystal Ball Output 10 years 3,500,000.00 90.00% 4,000,000.00 Acquisition and sale EBITDA multiples Multiple Company purchase Company harvest 5 times 5 times Capital Costs Interest Rate on Debt 14.00% Industry LBO Model Valuation Purchase Price Year Depreciation Expense EBITDA Interest (1) Principal Payment Loan Balance 2 3 4 Less: Capital Expenditures 4,000,000.00 4,000,000.00 4,000,000.00 4,000,000.00 4,000,000.00 5 (1) Based on beginning balance a. Enterprise and equity values after 5 years. Firm Selling Price Equity Value (EOY 5) Equity Investment Rate of Return on Equity Selling price = Year 5 EBITDA x Harvest Multiple Equity value = Selling price - Loan balance EOY 5 Initial equity investment in the LBO Compound annual retum over 5 years Note: Corporate taxes are considered in calculating debt repayments interest paid before tax and principal repaid using after-tax earnings). However, the return on equity is after corporate but before personal tax. The after-tax return would reflect the tax circumstances of the investor. b. Rate of Return on equity 2015 2017 Platform Company --Analysis of Equity Free Cash Flow Pro Forma Income Statements 2016 2018 #REF! $11,000,000.00 $12,100,000.00 $13,310,000.00 $ Less: Depreciation Expense (3,900,000.00) (4,300,000.00) (4,700,000.00) $ 7,100,000.00 $ 7,800,000.00 $ 8,610,000.00 $ Less: Interest (will populate from LBO Model Eval) Earnings before Taxes $ 7,100,000.00 $ 7,800,000.00 $ 8,610,000.00 $ Less: Taxes (2,130,000.00)' (2,340,000.00)' (2,583,000.00) Net Income $ 4,970,000.00 $ 5,460,000.00 $ 6,027,000.00 $ 2019 2020 14,641,000.00 $16,105,100.00 (5,100,000.00) (5,500,000.00) 9,541,000.00 $ 10,605,100.00 EBIT 9,541,000.00 $ 10,605,100.00 (2.862,300.00)' (3,181,530.00) 6.678.700.00 $ 7,423,570.00 Calculation of Equity FCF Plus: Depreciation Less: CAPEX Less principal payments Equity Free Cash Flow Equity Investment Cash Flows Firm investment cash flows Security Valuation (Enterprise Value = multiple of estimated EBITDA, Debt value = book and Equity value = residual value) a. Enterprise and equity values after 5 years. Firm Selling Price Equity Value (EOY 5) Equity Investment Rate of Return on Equity Selling price = Year 5 EBITDA x Harvest Multiple Equity value = Selling price - Loan balance EOY 5 Initial equity investment in the LBO Compound annual return over 5 years Note: Corporate taxes are considered in calculating debt repayments interest paid before tax and principal repaid using after-tax earnings). However, the return on equity is after corporate but before personal tax. The after-tax return would reflect the tax circumstances of the investor. b. Rate of Return on equity Platform Company--Analysis of Equity Free Cash Flow Pro Forma Income Statements 2015 2016 2017 2018 #REF! $11,000,000.00 $ 12,100,000.00 $13,310,000.00 $ Less: Depreciation Expense (3,900,000.00) (4,300,000.00) (4,700,000.00) EBIT $ 7,100,000.00 $ 7,800,000.00 $ 8,610,000.00 $ Less: Interest (will populate from LBO Model Eval) Earnings before Taxes $ 7,100,000.00 $ 7,800,000.00 $ 8,610,000.00 $ Less: Taxes (2,130,000.00)' (2,340,000.00)' (2,583,000.00) Net Income $ 4,970,000.00 $ 5,460,000.00 6,027,000.00 $ 2019 2020 14.641,000.00 $16,105,100.00 (5,100,000.00) (5,500,000.00) 9,541,000.00 $ 10,605,100.00 9,541,000.00 $ 10,605,100.00 (2,862,300.00)' (3,181,530.00) 6,678,700.00 $ 7,423,570.00 Calculation of Equity FCF Plus: Depreciation Less: CAPEX Less principal payments Equity Free Cash Flow Equity Investment Cash Flows Firm investment cash flows 5 Security Valuation (Enterprise Value = multiple of estimated EBITDA, Debt value = book and Equity value = residual value) Year 2 3 Debt Equity Firm Value (EBITDA x harvest mult in 1-5) $ Evaluation of IRR on the Platform Company Investment Year 0 Year 5 Debt Equity Firm Internal Rates of Return (IRR) on LBO Firm's Equity Investment Equity (with debt) Equity (no debt)