1. Solution of Refinance
So, you may have encountered a couple of tricky spots on this particular
worksheets, so let's go through it together. Starting right here, the current
capital structure, let's back solve for the amount of equity, so it'll be
800 million minus 300 million, gives us 500 million there. For the additional
capital, and we're gonna say equals minus, and we're gonna go up to
the top here. If we subtract this number, that'll give us a positive
number in that particular cell, let's bring us back down, so we can
see what we're doing. So now we can calculate the total capital in
here, it's gonna be equal to this cell, and we're gonna tap F4 to
lock it, plus this cell, and again, we're gonna tap F4 to lock
that in place. The total equity now here is going to be equal
to total capital minus the amount of debt, and the weight of the
debt is going to be equal to the debt divided by the total
capital there. Now we have these formulas in place, let's do a copy
Ctrl+C, highlight down with the shift key Alt+E+S for a Paste Special down
to formula and hit Enter. So now let's check out the cost of
debt here, the after tax cost of debt, pre tax cost of debt,
multiply that through by open bracket, one minus the tax rate here,
30%, we're gonna tap for to lock that cell, close the bracket and
hit Enter. Now, the weight of the equity would be equal to
the amount of the equity here divided by the total capital right there.
We have these two done, copy, highlight down with the shift key Alt+E+S,
and down to formulas for Paste Special formulas.
Now, let's bring this together to get the weighted average cost of capital,
it's gonna be the weight of the debt multiplied by the after tax
cost of the debt plus the weight of the equity multiplied through by
the after tax cost of the equity, like this. And we can copy
these, highlight down Alt+E+S, down to formulas and hit Enter.
Now, we can already see the weighted average cost of capital here and
the lowest one appears to be right here. So what we want to
happen in the cell is we want a one in here,
and we want zeros in these other cells, just like this.
But how are we gonna get a formula in there to do that
for us? So we can use an IF statement and a min function.
So if this cell is equal to the minimum of, and let's select
a range from this cell down to that cell, close the bracket,
then we're gonna wanna return one, otherwise, just revert to a zero and
then close the bracket like that, we're gonna hit Enter, but now we
need to go back in and lock some cells down. We wanna lock
down these red cell references right here, we're tapping F4 like this.
And now we can copy that formula, highlight down Alt+E+S, down to formula
and hit Enter. Now this is putting the checkbox there automatically.
Now, what we wanna do down here is that we don't just want
to link up to the weight of debt right here because that wouldn't
be dynamic if the optimal capital structure change. So we're gonna use in
here a SUM product formula equals SUM product like this. And we're gonna
grab array one will be the weight of the debt here,
comma and array two will be this array here, and when we hit
Enter, we're going to get the weight of the debt.
So now for the cost of the debt, let's also use a SUM
product again, and we can grab the after tax cost of debt there,
and then for this array, we wanna grab these and we can hit
Enter. Let's go through a similar exercise here with a SUM product and
we wanna grab the weight of the equity,
so right up here, the weight of the equity multiplied through for the
second array here and hit Enter, and then we wanna grab the cost
of the equity using a SUM product again, and the cost of the
equity is here, and we want to reference these just like that.
So the SUM product function is really useful because we can use it
again down here. And we're gonna select the first array here as being
the weights, second array as being the costs, and when we hit Enter,
we get the weighted average cost of capital.
Now, we've definitely done most of the work, but there's one little step
remaining. Back up at the top here, we had asked for their weighted
average cost of capital to be pulled into this cell, so we wanna reference
down to this 9.41% right here and hit Enter and now we've completed
2. Let's practice!