Excel Online Structured Activity: Merger Valuation with Change in Capital Structure Hastings Corporation is interested in acquiring Vandell Corporation. Vandell has 1 million shares outstanding and a target capital structure consisting of 30% debt. Vandell's debt interest rate is 7%. Assume that the risk-free rate of interest is 3% and the market risk premium is 4%. Both Vandell and Hastings face a 40% tax rate. Vandell's beta is 1.10. Hastings estimates that if it acquires Vandell, interest payments will be $1,500,000 per year for 3 years. The free cash flows are supposed to be $2.4 million, $2.7 million, $3.3 million, and then $3.97 million in Years 1 through 4, respectively. Suppose Hastings will increase Vandell's level of debt at the end of Year 3 to $27.6 million so that the target capital structure will be 45% debt. Assume that with this higher level of debt the interest rate would level of debt the interest rate would be 8.5%, and assume that interest payments in Year 4 are based on the new debt level from the end of Year 3 and new interest rate. Free cash flows and tax shields are projected to grow at 6% after Year 4. The data has been collected in the Microsoft Excel Online file below. Open the spreadsheet and perform the required analysis to answer the questions below. X Open spreadsheet What is the value of the unlevered firm? Enter your answer in dollars. For example, an answer of $1.2 million should be entered as 1,200,000, not 1.2. Do not round intermediate calculations. Round your answer to two decimal places. $ What is the value of the tax shield? Enter your answer in dollars. For $ What is the value of the tax shield? Enter your answer in dollars. For example, an answer of $1.2 million should be entered as 1,200,000, not 1.2. Do not round intermediate calculations. Round your answer to two decimal places. $ What is the maximum total price that Hastings would bid for Vandell now? Assume Vandell now has $9.17 million in debt. Enter your answer in dollars. For example, an answer of $1.2 million should be entered as 1,200,000, not 1.2. Do not round intermediate calculations. Round your answer to two decimal places. $ Check My Work Reset Problem 26 Formulas #N/A #N/A #N/A #N/A 27 Calculate target firm's lovered cost of equity 28 L. 29 30 Calculate target firm's unlevered cost of equity 31 32 33 Calculate target firm's unlevered value: 34 Unlevered horizon value of FCF 35 Unlevered value of operations 36 37 Calculate value of Interest tax shields: 38 Tax shield, Year 1 39 Tax shield, Year 2 40 Tax shield, Year 3 41 Tax shield, Year 4 42 Tax shield, Horizon value 43 44 Value of tax shields 45 46 Calculate target firm's por share value to acquiring firm: 47 Value of operations 48 Target firm's equity value to acquiring firm 49 Per share value to acquiring firm 50 51 52 53 #NA WNA WNA #N/A #N/A NNA WNA #N/A WNA 54
