Question

Can someone please show me the break down on how to figure this out using Excel or Google spreadsheet? Thanks!

Merger Bid Hastings Corporation is interested in acquiring Vandell Corporation. Vandell has 1 million shares outstanding and a target capital structure consisting of 30% debt; its beta is 1.50 (given its target capital structure). Vandell has $10.88 million in debt that trades at par and pays an 7.7% interest rate. Vandell's free cash flow (FCFO) is $2 million per year and is expected to grow at a constant rate of 4% a year. Both Vandell and Hastings pay a 30% combined federal and state tax rate. The risk-free rate of interest is 5% and the market risk premium is 5%. Hastings Corporation estimates that if it acquires Vandell Corporation, synergies will cause Vandell's free cash flows to be $2.4 million, $2.8 million, $3.5 million, and $3.73 million at Years 1 through 4, respectively, after which the free cash flows will grow at a constant 4% rate. Hastings plans to assume Vandell's $10.88 million in debt (which has an 7.7% interest rate) and raise additional debt financing at the time of the acquisition. Hastings estimates that interest payments will be $1.6 million each year for Years 1, 2, and 3. After Year 3, a target capital structure of 30% debt will be maintained. Interest at Year 4 will be $1.448 million, after which the interest and the tax shield will grow at 4%. Indicate the range of possible prices that Hastings could bid for each share of Vandell common stock in an acquisition. Round your answers to the nearest cent. Do not round intermediate calculations. The bid for each share should range between $ per share and $ per share.

Answer 1

First we need to find the weighted average cost of capital (WACC) of Vandell Corp. , to discount its cash flows

so,

Cost of equity:

With the given details, we can calculate this as per CAPM

ie.Cost of equity,ke (as per CAPM)=RFR+(Beta*Market risk premium)

ie.ke=5%+(1.50*5%)=

12.50%

After-tax Cost of debt,kd=

Before-tax cost*(1-Tax rate)

ie.7.7%*(1-30%)=

5.39%

So, WACC=(Wt.e*ke)+(Wt.d*kd)

ie.(70%*12.50%)+(30%*5.39%)=

10.37%

WACC to discount Vandell's FCF figures available here=10.37%

Now, as per the FCF growth rate given in Para 1(ie. Without considering the effects of synergy), for Vandell

Firm Value of Vandell=FCF1/(WACC-g)

where FCF1=FCF0*(1+g) & g= the growth rate, 4%

so,firm value of Vandell=(2*1.04)/(10.37%-4%)=

32.653061

millions

	mlns.
Value of firm=	32.653061
Less: Mkt.Value of debt	10.88
Value of equity	21.773061
/ No.of shares o/s	1
So, value /share	21.77306
ie.	21.77

Year	0	1	2	3	4
FCFs		2.4	2.8	3.5	3.73
Terminal FCF(3.73*1.04)/(10.37%-4%)					60.897959
Interest tax shields(ITS)(int. amt*30%)		0.48	0.48	0.48	0.4344
Terminal ITS(0.4344*1.04)/(10.37%-4%)					7.092245
Total FCFs	0	2.88	3.28	3.98	72.1546
PV F at 10.37%	1	0.90604	0.82091	0.74378	0.67390
PV at 10.37%	0	2.609405	2.692599	2.960261	48.62503
NPV/Value of Firm(sum of row)	56.887294
Less: Value of debt	10.88
Value of equity	46.007294
/ No.of shares o/s	1
So, value /share	46.007294
ie.	46.01

So, the answer will be
The bid for each share should range between $ 21.77 /share   & $ 46.01 /share