1. Solution of Payback
All right, so let's take a look at this payback tab here.
First of all, we've got a formula in here which is accumulating these
cash flows, well, we could use the shift key to highlight down and
all the way across like this. We're holding down the shift key.
We're gonna hit Ctrl+R for a fill right, Ctrl+D for a fill down,
and you can see over on the right hand side, we already have
two check boxes. So the next step, we need to identify the place
where the cash flows, accumulative cash flow flipped from negative to positive,
so you can see that they're negative here, but then right in between
year three and four, they flip to positive.
So let's start by building a formula, actually right in here,
and we're gonna say if... And what we're gonna do is we're gonna
say if this cell times this cell is less than zero,
then put a one in the cell, otherwise put a zero in the
cell, oops, a zero in the salt like this, and then we're gonna
close the brackets and hit Enter. So let's take a look at what
we're doing in this formula. We're effectively multiplying these two numbers
together, and when we multiply a negative times a positive, we get a
negative, which is putting a one in the cell. But when we copy
this, we're gonna do a copy we're gonna paste it all the way
through here, paste like that, we get zeros everywhere else. Well, why is
that? Well, because if we're multiplying two negative numbers, we get a
positive, and up here where we're multiplying two positive numbers, we're
getting a positive as well. So searching for the inflection point is an
easy way to do this when we're multiplying them through and seeing if
they're less than zero. So next up, we need to identify in what
point during year three does the cash flow move from negative to positive?
Well, we can do that actually by using an intercept function,
let's add it in. So let's go in here and adjust this function,
we're gonna top F2. Now instead of putting a one in here,
let's type in the word intercept, like this, and we can see it
come up, so we can hit tab. We're gonna tap the F2 key.
The first thing it wants here is the knowny's. Well, the knowny's will
be up here, year three and four, put in a coma,
and the knownx's are these values right here, minus 40 million minus 30
million. Now what we can do is close the bracket and we can
hit Enter. Now the other thing that we wanna do, let's pop into
the cell one more time, is we wanna do some cell locking.
So for example here, this reference here, which is purple, we're gonna tap
F4 actually a couple of times because we wanna lock the row references,
but not the column references and we can hit Enter. Now,
let's do a copy and now we're going to paste it all the
way through this entire area here with a paste. Now, in order to
complete this exercise down here, we're just gonna put in a SUM function
and we're gonna add across this row here, and then we're gonna copy
that down with a Ctrl+D like this as well, and we can see
we have all the check boxes on the right, showing 100%
over here as well. So you may have never seen that type of
solution before and it's quite tricky, so again, if you didn't get it,
don't feel bad about it. Remember, the first thing that we did was
we searched for the inflection point, the point at which it changed from
negative to positive, once we'd found that with an IF statement,
then we use the INTERCEPT function and that helped us find the point
between year three and year four when it actually switched from negative
to positive, and that came out is 3.6. So well done on this
exercise. You've successfully completed one of the most difficult formulas
in this course. Nice work.
2. Let's practice!