1. Solution of Addition 1
So if you couldn't figure out part of this worksheet, don't worry about
it, it's quite tricky, so let's get into it and go through it
together. So, let's start off in this cell, instead of linking this to
Option B, let's use an XLOOKUP functions. We wanna put in XLOOKUP. The
LOOKUP value we're gonna hard code as a one, that's what we're looking
for. The LOOKUP array, we're gonna LOOKUP in this array here,
comma, and then the return array we wanna return from here,
when we hit Enter, we get option B.
So next up, let's use option B now to help us look for
other values. Let's start in this cell here, we're gonna pop into this cell
and use an XLOOKUP again with a tab key, the LOOKUP value is
going to be Option B. We're gonna tap F4 to lock that.
The LOOKUP array is going to be this array right here,
and we're going to put in a comma, the return array in this
case is going to be this after tax cost of debt.
Let's hit Enter, before we finish off there, let's go back in and
we're gonna completely lock the reference for this red reference here with
an F4 and hit Enter. Now, we can copy that formula,
highlight all the way across these cells, Alt+E+S down to formulas and hit
Enter. Next up, let's go down here and backsolve for the amount of
equity, it would be the total capital minus the debt like this,
we can copy this formula, highlight down Alt+E+S down to formulas and hit
Enter. The amount of new capital and total that we need it would
be equal negative, and go all the way up to the project here,
1.2 billion and hit Enter. Let's just bring the screen back down so
we can see what we're doing. And now what we can do is
we can link this new capital up to the tranche of debt that
we're using right here and hit Enter. To get the revised structure here,
we want a SUM function, great little keyboard shortcut is to hit Alt+Equals,
which puts in an auto sum, we can pop over here,
Alt+Equals and hit Enter. So now the weight of debt here would be
equal to the debt divided by the total capital, and we know the
rate of that new debt as well on a pre tax basis,
that would be equal to 6% right here. Calculate this cost of debt
in the moment, first of all, the weight of the equity would be
the equity divided by the total capital right there.
Now, in order to get the cost of debt here, we're gonna use
these costs, but we need to weigh them for these weights here,
so it's a little tricky, but let's do it. Equals, we're gonna open
up a bracket and we're gonna first of all calculate the weight of
this debt divided by the total revised structure here, we're gonna close
the bracket and multiply that through by this cost, and we're gonna put
in a plus sign, we're gonna open a bracket again and we're gonna
take the weight of the new capital divided by the revised structure there,
close the bracket and multiply that through by this cost and then hit
Enter. So now we can get the weighted average cost of capital here.
We'll grab the weight of the debt multiply that by the after tax
cost plus the weight of the equity, and multiply that through by the
cost here and hit Enter. But for this section, it's gonna be okay
just to link things up, so the weight of the debt is over
here, 44%, and the weight of the equity, 56,
cost of debt after tax basis is right there, and then the cost
of equity is right here. Definitely a nice place down here to use
our SUM product function again, so let's grab... This is the first array,
this is the second one, and now we have the weighted average cost
of capital. We can quickly link that in up here,
where it says weighted average cost of capital there, let's link it up
to 8.37, and we're all set at the top. Let's go back down
here and just look at how we can complete the schedule now.
So that was tricky, nice work. And we've definitely completed the hardest
parts of the schedule, let's jump ahead to the next video though,
to look at this one dimensional data table, and how we can automate
the calculation for what is optimal. Well see you there.
2. Let's practice!