Must show work and use microsoft excel. Put an example on how I would like it done. If not will not accept the answer. Thank you!

Marston Marble Corporation is considering a merger with the Conroy Concrete Company. Conroy is a publicly traded company, and its beta is 1.30. Conroy has been barely profitable, so it has paid an average of only 20% in taxes during the last several years. In addition, it uses little debt; its target ratio is just 25%, with the cost of debt 9%. If the acquisition were made, Marston would operate Conroy as a separate, wholly owned subsidiary. Marston would pay taxes on a consolidated basis, and the tax rate would therefore increase to 35%. Marston also would increase the debt capitalization in the Conroy subsidiary to wd = 40%, for a total of $22.27 million in debt by the end of Year 4, and pay 9.5% on the debt. Marston's acquisition department estimates that Conroy, if acquired, would generate the following free cash flows and interest expenses (in millions of dollars) in Years 1-5: yr free cash flows interest expense 1 1.3 1.2 2 1.5 1.7 3 1.75 2.8 4 2 2.1 5 2.12 ? In Year 5, Conroy's interest expense would be based on its beginning-of-year (that is, the end-of-Year-4) debt, and in subsequent years both interest expense and free cash flows are projected to grow at a rate of 6%. These cash flows include all acquisition effects. Marston's cost of equity is 10.5%, its beta is 1.0, and its cost of debt is 9.5%. The risk-free rate is 6%, and the market risk premium is 4.5%. a. What is the value of Conroy's unlevered operations, and what is the value of Conroy's tax shields under the proposed merger and financing arrangements? b. What is the dollar value of Conroy's operations? If Conroy has $10 million in debt outstanding, how much would Marston be willing to pay for Conroy? USE EXCEL LIKE BELOW!!!! 4.66 Marston Marble Corporation is considering a merger with the Conroy Concrete Company. Conroy is a publicly traded company, and its beta is 1.30. Conroy has been barely profitable, so it has paid an average of only 20% in taxes during the last several years. In addition, it uses little debt; its target ratio is just 25%, with the cost of debt 9%. If the acquisition were made, Marston would operate Conroy as a separate, wholly owned subsidiary. Marston would pay taxes on a consolidated basis, and the tax rate would therefore increase to 35%. Marston also would increase the debt capitalization in the Conroy subsidiary to wd = 40%, for a total of $22.27 million in debt by the end of Year 4, and pay 9.5% on the debt. Marston's acquisition department estimates that Conroy, if acquired, would generate the following free cash flows and interest expenses (in millions of dollars) in Years 1-5: year free cash interest expense 1 1.3 1.2 2 1.5 1.7 3 1.75 2.8 4 2 2.1 5 2.12 ? In Year 5, Conroy's interest expense would be based on its beginning-of-year (that is, the end-of-Year-4) debt, and in subsequent years both interest expense and free cash flows are projected to grow at a rate of 6%. These cash flows include all acquisition effects. Marston's cost of equity is 10.5%, its beta is 1.0, and its cost of debt is 9.5%. The risk-free rate is 6%, and the market risk premium is 4.5%. a. What is the value of Conroy's unlevered operations, and what is the value of Conroy's tax shields under the proposed merger and financing arrangements? b. What is the dollar value of Conroy's operations? If Conroy has $10 million in debt outstanding, how much would Marston be willing to pay for Conroy? Solution: Part a: The appropriate discount rate reflects the risk of the cash flows. Thus, it is Conroy's unlevered cost of equity that should be used to discount the free cash flows and tax shields in years 1-5 and at the horizon. The horizon value should be calculated using Conroy's tax shields at the stable target capital structure, which are provided for Year 5. Since Conroy's beta = 1.3, its current cost of equity, rsL = 6% + 1.3(4.5%) = 11.85%. Since its percentage of debt is 25% and the rate on its debt is 9%, its unlevered cost of equity is rsU = = = wdrd + wsrsL 0.25(9%) + 0.75 (11.85%) 11.14% Interest5 = Debt4 (9.5%) = $22.27 (9.5%) = 2.116 TS5 = Interest5(Tax rate) = 2.116(0.35) = 0.7405 (You must use the post merger tax rate) In the other years, the tax shield is equal to the interest expense multiplied by the tax rate: TS1 = 1.2(0.35) = 0.42, TS2 = 0.595 TS3 = 0.98 TS4 = 0.735 HVTS5 = TS6/(rsU - g) = TS5(1 + g)/(rsU - g) = 0.7405(1.06)/(0.1114 - 0.06) = $15.28 million The value of the tax shields 0.42 0.595 0.980 0.735 0.741 15.28 2 3 4 1.1114 (1.1114 ) (1.1114) (1.1114) (1.1114 ) 5 = $11.50 million The unlevered horizon value is HVUL5 = = = FCF5(1+g)/(rsU - g) 2.12(1.06)/(0.1114-0.06) 43.71984 million The unlevered value of operations is 1 .3 1 .5 1.75 2 .0 2.12 43.74 2 3 4 1.1114 (1.1114 ) (1.1114 ) (1.1114 ) (1.1114 ) 5 = $32.02 million At the new capital structure of 40 percent debt with a rate of 9.5 percent, the new levered cost of equity and WACC will be rsL = = = rsU + (rsU -rd)(D/S) 11.14% + (11.14% - 9.5%)(0.40/0.60) 12.23% WACC = = = wdrd(1-T) + wsrs 0.40(9.5%)(1-0.35) + 0.60(12.23%) 9.81% Part b: The horizon value is: Horizon Value4 = = = FCF4(1+g)/(WACC - g) 2.0 (1.06)/(0.0981 - 0.06) $55.64 million The interest tax shields are calculated as interest payment x Tax rate. These tax shields, free cash flows, and horizon value are to be discounted at the unlevered cost Vops = 1.72 2.10 2.73 58.38 1.1114 (1.1114 ) 2 (1.1114 ) 3 (1.1114 ) 4 = $43.50 million Equity = = = Vops - Debt $43.5 - 10 $33.5 million is the maximum amount to pay cost of equity Marston Marble Corporation is considering a merger with the Conroy Concrete Company. Conroy is a publicly traded company, and its beta is 1.30. Conroy has been barely profitable, so it has paid an average of only 20% in taxes during the last several years. In addition, it uses little debt; its target ratio is just 25%, with the cost of debt 9%. If the acquisition were made, Marston would operate Conroy as a separate, wholly owned subsidiary. Marston would pay taxes on a consolidated basis, and the tax rate would therefore increase to 35%. Marston also would increase the debt capitalization in the Conroy subsidiary to wd = 40%, for a total of $22.27 million in debt by the end of Year 4, and pay 9.5% on the debt. Marston's acquisition department estimates that Conroy, if acquired, would generate the following free cash flows and interest expenses (in millions of dollars) in Years 1-5: year free cash interest expense 1 1.3 1.2 2 1.5 1.7 3 1.75 2.8 4 2 2.1 5 2.12 ? In Year 5, Conroy's interest expense would be based on its beginning-of-year (that is, the end-of-Year-4) debt, and in subsequent years both interest expense and free cash flows are projected to grow at a rate of 6%. These cash flows include all acquisition effects. Marston's cost of equity is 10.5%, its beta is 1.0, and its cost of debt is 9.5%. The risk-free rate is 6%, and the market risk premium is 4.5%. a. What is the value of Conroy's unlevered operations, and what is the value of Conroy's tax shields under the proposed merger and financing arrangements? b. What is the dollar value of Conroy's operations? If Conroy has $10 million in debt outstanding, how much would Marston be willing to pay for Conroy? Solution: Part a: The appropriate discount rate reflects the risk of the cash flows. Thus, it is Conroy's unlevered cost of equity that should be used to discount the free cash flows and tax shields in years 1-5 and at the horizon. The horizon value should be calculated using Conroy's tax shields at the stable target capital structure, which are provided for Year 5. Since Conroy's beta = 1.3, its current cost of equity, rsL = 6% + 1.3(4.5%) = 11.85%. Since its percentage of debt is 25% and the rate on its debt is 9%, its unlevered cost of equity is rsU = = = wdrd + wsrsL 0.25(9%) + 0.75 (11.85%) 11.14% Interest5 = Debt4 (9.5%) = $22.27 (9.5%) = 2.116 TS5 = Interest5(Tax rate) = 2.116(0.35) = 0.7405 (You must use the post merger tax rate) In the other years, the tax shield is equal to the interest expense multiplied by the tax rate: TS1 = 1.2(0.35) = 0.42, TS2 = 0.595 TS3 = 0.98 TS4 = 0.735 HVTS5 = TS6/(rsU - g) = TS5(1 + g)/(rsU - g) = 0.7405(1.06)/(0.1114 - 0.06) = $15.28 million The value of the tax shields 0.42 0.595 0.980 0.735 0.741 15.28 2 3 4 1.1114 (1.1114 ) (1.1114) (1.1114) (1.1114 ) 5 = $11.50 million The unlevered horizon value is HVUL5 = = = FCF5(1+g)/(rsU - g) 2.12(1.06)/(0.1114-0.06) 43.71984 million The unlevered value of operations is 1 .3 1 .5 1.75 2 .0 2.12 43.74 2 3 4 1.1114 (1.1114 ) (1.1114 ) (1.1114 ) (1.1114 ) 5 = $32.02 million At the new capital structure of 40 percent debt with a rate of 9.5 percent, the new levered cost of equity and WACC will be rsL = = = rsU + (rsU -rd)(D/S) 11.14% + (11.14% - 9.5%)(0.40/0.60) 12.23% WACC = = = wdrd(1-T) + wsrs 0.40(9.5%)(1-0.35) + 0.60(12.23%) 9.81% Part b: The horizon value is: Horizon Value4 = = = FCF4(1+g)/(WACC - g) 2.0 (1.06)/(0.0981 - 0.06) $55.64 million The interest tax shields are calculated as interest payment x Tax rate. These tax shields, free cash flows, and horizon value are to be discounted at the unlevered cost Vops = 1.72 2.10 2.73 58.38 1.1114 (1.1114 ) 2 (1.1114 ) 3 (1.1114 ) 4 = $43.50 million Equity = = = Vops - Debt $43.5 - 10 $33.5 million is the maximum amount to pay cost of equity