I need the formula for this equation please provide a formula for excel and detail how you calculated the answers, thank you

0-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, however, 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 Marks firm. After several meetings, Mark decided to solicit an offer from Clearstone. In response to Randys 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 2020 EBITDA $ 11,000,000.00 $ 12,100,000.00 $ 13,310,000.00 $ 14,641,000.00 $ 16,105,100.00 Less: depreciation (3,900,000.00) (4,300,000.00) (4,700,000.00) (5,100,000.00) (5,500,000.00) EBIT $ 7,100,000.00 $ 7,800,000.00 $ 8,610,000.00 $ 9,541,000.00 $ 10,605,100.00 Less: interest (6,300,000.00) (6,235,600.00) (6,040,288.80) (5,690,457.10) (5,159,103.90) Earnings before taxes $ 800,000.00 $ 1,564,400.00 $ 2,569,711.20 $ 3,850,542.90 $ 5,445,996.10 Less: taxes (240,000.00) (469,320.00) (770,913.36) (1,155,162.87) (1,633,798.83) Net income $ 560,000.00 $ 1,095,080.00 $ 1,798,797.84 $ 2,695,380.03 $ 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 firms 2015 EBITDA of $10 million and resold in five years for the same multiple of the firms 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. 398 chapter 10 Valuation in a Private Equity Setting 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 non-operating assets. a. What is the projected enterprise value of Flanders in five years? What is the estimated value of Clearwaters equity in the firm at the end of five years if everything works out as planned? b. What rate of return should Clearstone expect on its equity in the acquisition under the projections made above? c. After further review, Randy estimated that the firms operating expenses could be shaved by roughly $1 mi