So we've only got 30 minutes, so it's gonna be a really punchy session.

And the intention is to think about scenarios.

I'm gonna give you a bit of context for that.

When we get into it. We're gonna look at choose Offset, Index and Match.

So that's our focus.

I wanna make it really interactive, and so it'd be fantastic if you could jump onto the website. There should be a link and grab the materials.

So in Zoom on the kind of top bar, there'll be a button for resources.

And if you click on that, you should be able get through to the webpage that I've got up and access to materials.

I actually can't see that on my Zoom, but I'm confident you should be able to.

So if you can grab the resources, that would be great.

If you look at my screen share, there are a few files to download or potentially that you could download here.

I'm gonna be working in our 30 minutes off of the building scenarios workout, empty one file.

So it'd be great if you can download that.

There is a full version, so we're gonna go through and create an answer file together.

But if you wanted to be able to access that in the future, then you can always jump onto the website and grab that download from that, that link below.

So no one's saying anything on, on the chat box, I don't think, or on the Q&A.

So hopefully that means you can, you, you can hear me and you can see me and you can see my screen share.

I've downloaded this file already.

I'm gonna jump into that now.

So, I have that open here and, um, the file has a bunch of different sheets in it.

We've got 30 minutes.

Our focus is on Choose Offset, index and Match.

And they're all kind of short exercises in their own right.

I think we should start with choose.

So it would be great if you can go to the Choose sheet, can use your mouse if you want to, or your track pad, or you can hold down control and tap page down a couple of times, and that will take you to the Choose sheet.

Okay? We probably shouldn't delay because of our time constraints, but let's have a bit of context here.

Maybe I've built a model, okay? And if you think about how a financial model works at a really high level, the first line on your income statement is sales.

And is it not true that sales drives a lot of the model? So if we said, Hey, what do you think your costs are gonna be, your expenses? Well, it depends how much I'm gonna sell.

And you might say, well okay, so what do we think? What do we think the inventory's gonna be? The balance sheet, more inventory gonna go up. What about CapEx for, well, if that, the sales really drives a lot of the model, and it would be reasonable to expect that you could be uncertain about the level of sales, and it might be really interesting to, in the model, be able to quickly and easily sensitize for different sales growth levels and see how that flow through the model. Maybe see how that affects some of your metrics that you're calculating, or the valuation for the business, or, you know, if it was an M&A model thinking about things like eps, secretion and dilution.

So that's, that's kind of the, the context of what we're doing.

And if we zoom into that, what I'm looking at here is using a, a function called Choose it's good function.

There are many good reasons for using Choose, but we have alternatives.

We're gonna look at Choose, we're gonna look off, look at Offset, and look at Index.

And I should tell you those functions really achieve the same thing.

But there are some pros and cons of using those in different scenarios.

So let's talk about those as we come to.

Okay, we're gonna use the Choose function.

I'm going to go into cell C11, but in the adjacent cell, I'm just gonna use a function called Formula Text.

So you can see the formula as I'm building it.

Okay, so we're gonna use the Choose function if I say equals CHO, okay? Now, Excel uses predictive text.

So if I type in CHO cell says Excel says, well, look, there's a few functions that have that text string in them, but my best guess is Jonathan, you wanna use the the, the Choose function, which I do, and it tells me what it is.

So the Choose function chooses a value or action to perform from a list of values based on an index number.

So if you give it an index number and you give it a list of values, then it will choose that value. It's actually pretty simple.

If we part type in any function, we can hit tab on the keyboard and it will complete the, the name of the function, insert the bracket for us, and then it'll give us some syntax prompts, the various arguments, and they're in bold depending on whether or not you're looking at them.

So in bold Excel says, look, Jonathan, you're gonna use the choose function.

I need an index number. So I can choose from the list, but I need to know, you know, of that list. Which number from that list would you like me to choose for you? And my response to that is, if I arrow up Excel to C4 got a one in it, I want you to choose the first item in the list.

Now, if we hit comma, you'll see that it'll move on to the next argument, which is value one.

And so Excel is saying, okay, I can choose the first item in the list, but I don't have the list.

So where, where's the list, Jonathan? Okay, so I'm gonna arrow up to cell C7, which is 10%, and that's the first item in my list.

Now, I could give it a very long list or a very short list, and the size of the list is really determined by me.

In fact, if you look at the syntax, it says Value one, which is mandatory, then you have to include that.

But value two, value three, they're in square brackets.

And if something is in square brackets, for the most part in Excel, it is an optional variable, but I do want to give it a value two.

So I'm gonna hit comma again and arrow up for value two to C8 comma again and arrow up to C9 for my third value.

And I could keep going, but I don't want to, okay? Because I've only got three options here. So I'm gonna close the bracket and hit enter.

And what Excel does is hopefully what we expect it to do, it chooses 10% because 10% is the first item that I've referenced in my list.

If I change that scenario, pick up from a one to a two, it will choose the second item, which is 6%. And if I change it to a three, it will choose 3%.

I love the choose function. There are other alternatives, offset an index, and they have their particular uses.

I'd very much like choose because it's very easy to audit.

I mean, there's no ambiguity there.

You can see what it's, it's doing, you know, it's not in any way opaque, but there are problems with choose because I might say, well,I've had an idea.

Well, what I'd like to do is, I'd like to add an extra scenario to the list.

So I might go down here and add an extra scenario and I might say, in my choose function, Hey, why don't you pick the fourth item? Even if I have that populated it, it won't, won't select it because it has a list of three discreet items.

And if you say, go and choose the fourth one, there isn't the fourth one, so it can't cope with that.

So let's put that back to a three or maybe even back to a one.

So one of the issues with the choose function is you wanna, if you wanna sort of expand your model, then you need to go back to the choose function and also expand that choose function, um, itself.

So, that is, I mean that just makes it a little bit more fiddly to use.

I think it's very simple, but it requires a little bit more maintenance.

If you've got an, an expanding model and you might say, well, is there maybe a another option? Something that would do exactly the same thing, but perhaps approach it in a slightly different way? And of course there is, we're gonna have a look at the offset function.

So if you guys would be kind enough to move to the next sheet for me, that'd be great.

You can either click on it with your mouse or you can hold down control and tap page down.

And that's gonna take us to the offset function.

Again, I'm just gonna use the formula text function so you can see what I'm doing.

So the offset function achieves. The second thing. As the choose function, let's try and put it together.

If I say equals OF, in fact, that's all I need to type, because apparently in Excel there is no other function that has the text string, OF in it.

Excel is saying, Jonathan, I think you want the offset function.

Let's just read, read about that.

It returns a reference to a range that is a given number of rows and columns from a given reference.

I don't know how descriptive that is, but effectively a given reference, if you give it a reference and you count down a certain number of rows and count across a certain number of columns, it'll tell you what's in that cell.

Let's hit tap and have a look at the arguments.

In the first instance, Excel says, Hey Jonathan, I need a reference.

Now I might say to Excel, so what's special about the reference? And the answer there is nothing.

There's nothing remarkable or special about the reference from which we're gonna offset in its own right.

That cell is not particularly important, but it's just a convenient place to offset from.

I'm gonna go and select cell C6 because it's sort of logical, 'cause it's at the top of my list.

You could select elsewhere on the spreadsheet and that would work.

So you could, for example, select cell a one, but if you wanted to get to the best case, you'd need to always be adding one, two columns to this and uh, automatically be going down 1, 2, 3, 4, 5, 6, 6 rows, from there.

So it's probably more logical to offset from c6.

But the point I want to make is that C6 is very interesting in, its in its own right.

So if we hit comma in bold, Excel says, great, I know where I'm offsetting from, but by how many rows do you want me to offset? Now if I wanted to select best case, then I'd be offsetting one row rather than type that in.

Should maybe not be very nice, hard coding that into the model.

I can arrow up and go and select C4, which has a one in it.

Now, Excel might say, okay, so how many columns do you wanna offset? My answer is, well, I don't wanna offset any columns at all.

In fact, I'd quite like to ignore that argument, but Excel won't let me.

If you look, there's a column argument here, and it isn't in the square bracket, meaning it is mandatory comma.

So we're gonna have to populate this unfortunately, but we can just populate it with a zero, right? Because I don't wanna offset any columns.

You can offset positively or negatively left or right, but I don't want to, I just wanna offset by nothing.

I want to select an individual cell, and for the most part, that's what I wanna do.

But you can select an array, you can select a range by specifying a certain height and width that you wanna select.

But I'll do that here.

And they are optional, so I'm gonna ignore them.

I'm simply gonna close the bracket and hit enter.

So if we think about my scenario picker, which is the rose to offset, that's got a one in it.

If you change the one to a two, then it'll select 6% because that's from the offset point.

That is one, two rows down.

And clearly if we set it to a three, it'll select 3%, which is the third line down.

Now, one of the things that I think is quite cool about the offset function, and I really do like it, is if we wanted to expand our list and maybe have, you know, many other, you know, a number of other items different scenarios, then you don't need to go and change the offset function in any way.

You just need to add data.

So for example, if you said, I'd love to offset by four, then if you inserted some data down here in the the fourth row, then Excel would select that because you, you don't need to go back to the offset function and kind of choose discrete list items like you do in the choose function.

So think in that respect, offset is actually very, very handy.

It works very, very nicely.

I probably would say that it's a little more difficult, um, to audit.

So the, you know the cell you're offsetting from itself, you know, is not very interesting.

That could be a bit opaque.

I think if you're building a model and various other people in the team are gonna pick that up.

I typically think that the choose function, this is maybe a just a personal view, but the choose function is just easier to get into.

Um, and, and so that would usually be my preference, but I love the extra flexibility, the offset function.

It probably also occur to you that if you were choosing from a scenario with like 20 or 30 different options, trying to click on every one of those and the choose function's gonna drive you nuts, okay? So it's really, really fiddly to do that.

So in that case, the offset function works very, very nicely.

What we're doing here is we're, we're really just looking in one dimension because we're looking at choosing things from a, from a column or selecting things from a column.

What I'd quite like to do is go to the next sheet.

So control and page down to go to the index sheet.

I'm bound to say that the index function for us is just gonna achieve the same thing as choose and offset.

It's gonna approach it in a slightly different way, but it, it's gonna achieve the same thing.

So, um, I'm interested in going into cell C11, but I'm just gonna go into D11 and insert the formula text function so you can see what I'm doing.

And let's have a go at the index function.

So I'm gonna say equals IN in fact, that's it.

I I only only need to type in IN and Excel says, I think you wanna use index, Jonathan.

And my next question is, well, hey Excel, what does the index function do? It returns a value or a reference of a cell the intersection of a particular row and column in a given range.

So if you give it a range, sometimes called an array, you know, just a bunch of cells.

If you give it a range and you say, I wanna go down a certain number of rows and across a certain number of columns, then it will return that cell for you where wherever those points intersect.

So definitely gonna achieve the same thing as those previous two functions.

Let's hit tab, okay, to complete the function and insert the bracket.

And I get some prompts here. There's a couple of ways you can build this.

You can get it, you can get it to index from a reference or from an array.

I'm definitely gonna use an array.

So it says in bold, gimme an array. That just means a range.

So I'm gonna select these cells.

I've selected C7 to C9.

I haven't selected the heading I could have done, but I've chosen not to.

And the fact that I've chosen not to is significant for the way that we're gonna build the function.

So if you're doing this with me, then don't select the heading. Otherwise, you'll have to slightly change the way, the way that this works, in terms of the rows and the column references, if we hit comma, excel says in bold, okay, I can see the array.

So what row do you want? Well I I quite like row one, so if I arrow up to C4, it's got a one in it.

And then can you see that after that it says, well, what column do you want? So I really love the index function because you can work in rows and columns and that would be much more opaque to do that using the choose function where you'd have to be selecting various cells like across an array.

So I think it works very nicely actually, but in this instance, I don't want to choose a column.

It is optional. I'm going to ignore it.

I haven't inserted a comma, I'm doing nothing at all.

I'm just gonna hit enter.

Okay, so I really like the index function.

I think it's nice that it works by default in two dimensions.

I think it's fairly easy to see that it's selecting, I mean, we can press F2, but it's and, and look at it, it's fairly easy to see that it's selecting a particular range.

So it's really nice to audit.

If we change the scenario pick from a one to a two, then it chooses 6%, but two to a three, then it chooses 3%.

And that all works kind of as we'd hopefully as we'd expect it to.

If you have got questions, guys, please do feel free to ask.

There is a chat box, which I have on a second screen in front of me, which I can see, and I know not everyone has access to that. It depends on the permissions when you've logged in and if you were using a work laptop, et cetera.

But we do also have a question and answer section.

And my rudimentary understanding of Zoom, from an opera sort of technical operational perspective is that everyone should be able to access the Q&A box.

So if you do have any questions, feel free to feel free to ask.

Okay, I'm gonna go on to the match sheet.

So we've looked at choose offset and index and I really want to just change the upper gear and have a look at match.

I mean, we've got about maybe just under 10 minutes happy to have a look at this because I'll ask you if you've got any questions at the end.

That's probably about right.

So,let's go back and revisit these functions because I think they were somewhat limited in their application.

If we said, okay, let's use the choose function, it's a great function.

And someone said, brilliant, how do we, how do we sensitize this? Well, I mean you could put in a one or a two or a three or a four or five.

What does that mean? Like what does one mean? And you could say, but one means it's the best case.

Two means it's the base case, but how do I know that? Like if I didn't produce the model, it's not very intuitive, right? So you could say, well, no, that that is true.

Actually, maybe we should make this more intuitive.

Rather than using an index number, it'd be great just to use a description, like a name, like best case, base case, worst case.

So you could say, well let's try and make that work then, and this won't work.

But we could say equals CHO tab and Excel says I want an index number, I don't really have one, but I guess I could go up to best case comma, and I could select the three respective items in the list.

Clearly Excel's gonna say I don't get it.

Okay, if we hit enter, it's saying I don't get it.

I'm looking for best case, but it's not an index number.

Yeah, okay.

So what we need to do is we need to somehow change that name into a number.

If I go to the cell directly below the text stringing base case, so I'm gonna go into cell c5, you can see we're gonna use the match function.

It's a really cool function, it's a really easy function, okay? And so let's have a go.

If I say equals MAT, Excel says that's enough.

You typed in MATI think you wanna use the match function.

Let's look at what it does. It returns the relative position of an item in an array that matches a specified value in a specified order.

I don't know how descriptive that is.

It tells you the position of something in an array and array means a a list, right? So if you give it a list, it will say where the item is looking for appears in that list.

But best case, so where does best case appear in this list itself, where it's item one? So I'm gonna give you a one. Alright? So let's try and make that work.

If I hit tab, Excel says, okay Jonathan, we're using the match function. What am I looking for? It's the lookup value, it's an arrow up to C4, gonna look for best case, okay, as a text string comma look up array in Excel array just means range.

Yeah. Or like it's a bunch of cells.

So where's, where's my lookup range? Where am I? Where's my array? Where's, where am I looking for? What's my list? So we're gonna go and grab these three items, that's B8t colon B10.

We're gonna look for best case in that list.

No actually, sorry, lemme go back because it says match type and it's optional.

Okay? But if we comma, it says less than exact match or greater than.

Now clearly if I'm looking for a text string less than or greater than it doesn't make any sense, I wanna find an exact match.

If you don't specify this, it will, it will make a decision for you and it'll pick the first item in that list.

So it'll just do less than it is not right? We're gonna go for zero, I want an exact match close bracket.

Now I hope this won't be a surprise when I hit enter, it's gonna look for base case in that list.

It's the first item in the list, it's gonna return a one.

Now suddenly there's a clear application here because it's saying, alright, uh, okay, I could return a one, hopefully the choose function say that's brilliant for me because actually I need an index number.

So if I say equals CHO tab yeah, I'm really into index numbers as the choose function.

So I'm gonna go and grab C5 comma, and this is just your standard choose function.

I'm gonna grab the 10% comma, the 6% comma, the 3% close bracket, okay, enter.

So that means that if we change best case to say base case, that's the second item in the list because it's the second item in the list, the match function will spit out a two that will then be sucked into the choose function, which will look for the second item on the list.

So they work really nicely together.

I tell you what I don't like particularly is having to maintain two separate cells.

I just find that a bit cluttered, A bit fussy.

And so what we can do is if we go back to the choose function, you can see that C5.

So C5 is the location of our match function.

Why don't we rather than have a reference to C5, why don't you rip out the contents of fee C5 and stuff it into the, the the choose function.

So let's go up to C5. Now I'm gonna select this.

Just take note that I'm not selecting the equals sign.

I'm not, okay, so we're gonna select C5 I'm gonna copy that, okay? Or you can right click on it and copy it. Okay? I've copied it to my clipboard.

I know that's not obvious, but it's copied to my clipboard.

I'm just gonna press escape to kind of come outta there.

And then if I go down to the choose function where it references c5, I don't want that anymore, I'm gonna delete it.

And in place of that control V to paste, I'm gonna paste the match function.

Now, I know that looks quite convoluted.

So I'm a big fan of building the components of a formula in separate cells and then bringing them together.

When we bring them together, we call it nesting in Excel.

So I'm a big fan of building them as we've done here in two separate cells and then nesting them together.

What it means is that we can entirely do away with that match function. We don't need that. We could have worst case and the match function will say, Hey, you know, worst case is the third item in the list.

I'm gonna spit out a three.

And the choose function will say, that's fantastic because I can work with that as a number.

Incidentally, if you look at that formula and you selected the match function, Excel will, I don't know how clear that is screen, but when I selected that, I'm gonna try that again because it disappeared.

But when I select that match function, it says, oh, you know, the answer to this is three, shows me a little three there can also press F9.

So if you press F9 in Excel, it will tell you the result of part of a formula.

I don't wanna accept that as a hard code, so I'm just gonna press escape, okay? What we can do is we don't have to do this in two stages.

If you feel really comfortable with this, you can just type this out in one stream.

So if you said, well, let's use the offset function equals OF tab.

Excel says I need a reference, I'm gonna reference sales growth comm, I need to know how many rows, well, rather than me point at something, I'm gonna, I'm gonna build the match function inside the offset function.

So MAT and you can hit tab Now to complete that.

So it says, what am I looking for? You're looking for worst case, comma, where am I looking for that? You're looking for that in these list of headings, B8 to B 10 comma. And you really do want an exact match close bracket.

And uh, so we've got the offset function.

The match function is telling it how many rows to offset by.

It needs to know, comma, how many columns I I'm saying it needs to know that because it's not an optional variable, it's gonna insert the zero and then hit enter and it gives that 3%.

We've only got a few minutes left.

I was gonna do a couple of minutes at the end for Q&A, but I reckon I've just got 120 seconds to allow me to say equals IND for index.

I'm gonna give Excel an array, comma, Excel says in that array, how many rows do you want me to, you know, which row number do you want me to select? And I'm gonna say MAT for the match function.

Joan, look for worst case comma in the list of scenarios here, scenario headings here, comma zero, exact match.

Okay, close bracket.

And you say, oh my goodness, index function.

I better tell it how many columns, but you don't need to because it's an optional variable. Look, it's in square brackets. Okay, and hit enter.

So now we can change worst case to best case.

And it is, you've probably see from me typing this in, it isn't, uh, case sensitive, right? So best case and it selects best case or you could just say equals and link it into that cell to go for base case or for worse case.

So I've made it a Little bit more complicated at the end.

I don't think there's any particularly good reason why you would have to start just typing the whole thing out in one stream of consciousness.

I'm a big fan of building them in separate cells and then nesting them together at the end.

So if no one's got any questions, all that remains to be said is, look, thanks so much for dialing in. We've got loads of people on the call.

I really appreciate that. It's really nice that you made the, took the time to, to dial in.

Really looking forward to seeing you perhaps in a future session.

Have a fantastic, it's Friday.

Have a fantastic Friday and a and a great weekend.

Thanks very much guys. Take care. Bye now.