1. Solution of Addition 2
So we're going to demonstrate a data table now, and you may have
seen two dimensional data tables, but we're going to use a one dimensional
data table, which is a little bit more rare. If you've never seen
one before, you're in for a treat. Let's get started.
So the first thing that we're going to do here in this cell,
right here, is just put in an equal sign, and if you could
please link up to the weighted average cost of capital, right there,
and hit Enter. So now we're going to ask you to select from
this cell all the way down like this and across like that,
now we want to activate a data table. Let's tap the Alt key, all
these letters appear up here. Next, we want A for data,
and in the Data menu all the way across here, W for What
If Analysis, and then T for data table, it brings up this dialogue
box. But if we hit escape, we can also use Alt dt,
which is way easier to remember dt for data table. It's set up
for a two dimensional data table, but we don't need the row input
cell, so let's hit the Tab key to get here for column input
cell, and then we want to use the cursor to select down here,
where it says "Option B," and then we want to hit Enter like
that. Now, you should see different values in here for the weighted average
cost of capital, if you don't, there could be a couple of issues.
Number one, make sure that you have X look up functions all the
way across here, and you don't have these directly linked up to this
row right here. The other thing that you can check is your settings.
So for your settings, let's tap the Alt key once more and right up
here F for file, and we want to go all the way to
the bottom left, T for options. This dialogue box comes up,
we want to tap the down arrow once to get into Formulas and
here under Workbook Calculation, we want to make sure that you're on automatic,
you don't want to be on partial or manual, or your data tables
won't work. Once you're sure that you're on automatic, you can click Okay,
right here. Now, we just have one last step. Recall that we've hard
coded a one in here, but we want to automate this for the
minimum, so equals if if this is equal to or is equal to
the minimum of this range from here down to here,
then we want a one, otherwise, we just want a zero,
and then we're going to close that bracket and hit Enter.
Now, before we go any further, we want to lock down this red
reference with an F4 like this, and now we can do a copy,
highlight down to here, ALT S, down to Formulas and hit Enter.
So this is now completely automated now. What the computer's doing is it's
toggling through option A, B, C, D, and each time it does,
it's giving us the weighted average cost of capital there. Then what we're
having here is the Min function is investigating which one is the lowest,
and it's setting that as the optimal capital structure,
then that is coming through on here, which is translating all the way
through the schedule down here, all the way down to this weighted average
cost of capital, which is right here. Now, if you found that one
tricky, that's okay. There's lots of Excel techniques in there that we used,
so don't feel badly if you didn't quite get it. One last thing
you could do if you like, is set this font to white here,
since we don't really need to see it. Great work on this one.
2. Let's practice!