Can someone please show me the break down on how to figure this out using Excel or Google spreadsheet? Thanks!
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 |
Can someone please show me the break down on how to figure this out using Excel...
Problem 22-03 Problem 22-03 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.20 (given its target capital structure). Vandell has $10.10 million in debt that trades at par and pays an 7.2% interest rate. Vandell's free cash flow (FCF) is $2 million per year and is expected to grow at a constant rate of 4% a year. Both Vandell and...
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 $9.48 million in debt that trades at par and pays an 7.7% interest rate. Vandell’s free cash flow (FCF0) is $1 million per year and is expected to grow at a constant rate of 6% a year. Both Vandell and Hastings pay a 30% combined federal...
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.20 (given its target capital structure). Vandell has $9.09 million in debt that trades at par and pays an 7.1% interest rate. Vandell’s free cash flow (FCF0) is $2 million per year and is expected to grow at a constant rate of 5% a year. Both Vandell and Hastings pay a 30%...
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.30 (given its target capital structure). Vandell has $10.21 million in debt that trades at par and pays an 7.4% interest rate. Vandell’s free cash flow (FCF0) is $2 million per year and is expected to grow at a constant rate of 5% a year. Both Vandell and Hastings pay a 35%...
Problem 22-03 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.60 (given its target capital structure). Vandell has $11.88 million in debt that trades at par and pays an 7.5% interest rate. Vandell’s free cash flow (FCF0) is $2 million per year and is expected to grow at a constant rate of 6% a year. Both Vandell and Hastings pay...
Problem 22-03 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.04 million in debt that trades at par and pays an 7.5% interest rate. Vandell’s free cash flow (FCF0) is $2 million per year and is expected to grow at a constant rate of 6% a year. Both Vandell and Hastings pay...
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.30 (given its target capital structure). Vandell has $11.31 million in debt that trades at par and pays an 7.6% interest rate. Vandell’s free cash flow (FCF0) is $1 million per year and is expected to grow at a constant rate of 5% a year. Both Vandell and Hastings pay a 30% combined federal...
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 $8.23 million in debt that trades at par and pays an 7.2% interest rate. Vandell’s free cash flow (FCF0) is $2 million per year and is expected to grow at a constant rate of 4% a year. Both Vandell and Hastings pay a 40% combined federal...
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.81 million in debt that trades at par and pays an 7.5% interest rate. Vandell’s free cash flow (FCF0) is $2 million per year and is expected to grow at a constant rate of 4% a year. Both Vandell and Hastings pay a 35% combined federal...
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.35 (given its target capital structure). Vandell has $11.22 million in debt that trades at par and pays an 7.9% interest rate. Vandell’s free cash flow (FCF0) is $1 million per year and is expected to grow at a constant rate of 5% a year. Both Vandell and Hastings pay a 30% combined federal...