NEED EXCEL EQUATIONS! C138 and part D

Wansiey Portal Inc., a large internet service provider, is evaluating the possible acquisition of Alabama Connections Company (ACC), a regional Internet service provider. Wansley's analysts project the following post merger data for ACC (in thousands of dollars): If the acquisition is made, it will occur on January 1, 2019. All cash flows shown in the income statements are assumed to occur at the end of the year. ACC currently has a capital structure of 30 percent debt, which costs 9 percent, but Wansley would increase that to 40 percent debt, costing 10 percent if the acquisition were made. ACC, if independent, would pay taxes at 30 percent, but its Income would be taxed at 35 percent if it were consolidated. ACC's current market-determined beta is 1.40. The cost of goods sold is expected to be 65 percent of sales, but it could vary somewhat. Gross investment in operating assets is expected to be equal to depreciation-replacing wom out equipment, so net investment in operating assets will be zero. The risk-free rate is 7 percent, and the market risk premlum is 6.5 percent. Wansley currently has $400,000 in debt outstanding. ACC's management estimates the firms equity is currently worth $750,000 (pre-merger). Use the compressed APV model to answer the following questions. rL=16.10% "In this scenario, we state that investment in not operating capital is zero. This arises from the fact that the only needed investments are those needed to replace wom out capital, and that they equal depreciation. We must determine the tax shields. From this point, we can derive horizon value from the basic DCF framework. The tax shield is the interest multiplied by the post-merger tax rate. To calculate the value of the tax shields add the horizon value of the tax shields to the 2023 tax shield to get the total tax shield cash flow in 2023. In the other years the total TS cash flow is just the annual TS Then find the NPV of this stream of tax shields at the unlevered cost of equity. Total TS Cash Flows NPV of TS Cash Flows This is the value of all of the tax shields. 5 To calculate the unlovered value of operations, add the unlevered horizon value to the free cash flow 6 in 2023 to get the total unlevered cash flow in 2023. In the other years the unlevered cash flow is 7 just the annual free cash flow. The unlevered value of operations is the NPV of the unlevered 8. cash flows at the unlevered cost of equity. Year Total unlevered CFs NPV of unlevered CFs 51,947.6 This is the unlevered value of operations The value of operations is the value of the interest tax shields plus the unlevered value of operations To find the value of ACC to Wansley's shareholders take the value of operations, add in any non-operating assets (there are non for ACC) and subtract off the debt. d. Calculate the gain (per share) to ACC and Wansley at each share price below. Graph the gains to identify the negotiation range (graph the results) 10 points. 0. Should Wansley pursue the acquisition? Why? If they should, how should Wansley approach the acquisition (10 points)