Original subtitles

Now, we get to step three.

We want the values here in the assumptions to

change depending on which scenario we choose.

We don't want to hardcore them by selecting only the strong case for revenue growth,

gross margin, and operating margin.

So, now we're looking for a way to tell Excel to use this cell,

in this row and this column when the scenario is a strong case scenario,

and use the cell in this row and this column when

the scenario chosen is a base case and change it to this.

This is where offset can be used,

and here is what the generic function isn't looking for.

So, it's looking for a reference point,

some place to start with,

and then it's asking for how many rows do you want me to move over,

and how many columns?

Based on that, that is the value I'll return in this particular cell.

Okay, and so for the rules I want

it to be able to move to the right row and give me this value.

I'm actually going to go ahead and use the match function to

match the row based on the scenario that is chosen here.

So, that just kind of a criteria that we're using and the scenario,

and so therefore I'm going to use the match function to narrow

down which row I am selecting.

For the which row am I selecting this one,

this one or this one.

This one, this one and this one.

Then the column I can tell is the one over here,

or the one over here.

So, I'm going to switch over to this tab here because

you can see that I'm using this reference point.

So, offset is always looking for a starting point.

So, that's what you do.

So when you're actually plugging in the opposite function,

it's starting a reference.

Reference is the starting point from which to count the number of rows and columns.

So, I want in here,

I want the reference point to be let's say this because I'm talking about revenue growth,

so I have this empty cell.

So, I'm going to just use this as my starting point and say okay,

go down to this row and this column.

So, I'm going to show you this.

So, I'm saying okay,

start with this reference point,

blue border cell here,

and match for the row,

match whatever is in.

So, matched function will return for me the row that I'm looking for,

which will be C24 because it's looking for this scenario,

matching it within this lookup array,

and saying strong case matches the row one of these three cases,

and so it's going to return the value.

I'm asking it for an exact match,

that's why I have the match type to be zero.

So, it's going to say, okay match row is one, and then column.

So, now I have the match giving me back row is one,

and now for this is the column reference.

So, I'm saying okay, stay in this column which belongs to H28 and give me this cell.

So, if it says base case,

match will give me back two because off

the lookup array it'll give me the second row for the first year,

or rather for year four.

So, when I again, Control, Alt, Shift,

it gives me minus five,

which is this case because right now scenario is selected as base case.

If I change it to a strong case,

then it switches to minus three.

So, for this one,

well it's one column of H28.

So, staying with the same reference point,

I'm saying H28 is the reference cell,

match it to the same criteria, strong,

the scenario criteria and give me an exact match for that row.

Then for column I'm saying,

offset move over one column.

That's this column here.

So, it's going to give me minus three.

Let me show you how it changes.

So, if I say this is minus four,

this value changes to minus four.

So, that's how offset and match are used.

So, for gross margin,

the only difference is I'm using the same offset and match combination,

but this time I decided to use this empty cell right above gross margin,

but in essence it's the same exact principle that I'm saying, okay,

for match row H32,

match it for me for C24 in this lookup array.

That's what the B33, B35 is.

You can see in the purple colored cells,

give me an exact match of that row.

So, this gives me back row one, two, or three,

based on whatever is inside this scenario strong scenario here.

Give me just the column the first with still within the same column

as H32 for year four and for year five,

just move by a column.

So again, I'm just going to demonstrate.

So, if I change this to well,

survey in the strong case,

so I'm going to change this to 0.32.

It changes to 32.

This is set up to be 32 percent to

a percentage but I just wanted to be a value, number value.

So, that is how I repeated it for operating margin and using H36 as a reference point,

and we get our assumptions and they are dynamic.

We're finally ready for step four,

which is the next video.