Okay, so if you can click on that to download it and open it up as I'm doing, that'd be fantastic.

And as you open it up, you land on a page, welcome page.

If you look at the, if you look at the sheets along the bottom, there's actually quite a lot of content here that we could work through.

So simple data tables, advanced data tables, three input data tables left to right.

Our session is focused on this sheet here, but you can check out the other content.

I'm gonna assume you've got already a working knowledge of data tables.

And if you wanted to kind of go backwards, there's some awesome content on Felix that will cover simple data, data tables, and advanced data tables.

And if you wanted to have a look at the answers to this file, then there's a file called sensitivity data tables workout one full, which has got the answers in it.

So you can always kind of check out some extra content prerecorded content on Felix.

Go and have a look at the answers to this file and that will kind of get, get you into some of the basics of data tables.

But I'm assuming we're, we're all good with the basics because this session is focused on three input data tables.

So if you pick good enough to go to that sheet, that would be fantastic.

And let's have a quick chat about what we're trying to achieve here.

By default, data tables are set up to sensitize on two variables.

So they look at row the a row of headings and they look at a column of headings. And it's two variables that, that we would sensitize on.

And what you could do if you wanted to sensitize on more things, you could create a dashboard.

You know, you have a sheet in Excel and you could have multiple data tables all sensitizing on different things.

But if you wanted to have just a smaller number of data tables here, we've got one data table that was sensitizing on more than one thing, it would be reasonable to say, is that actually possible? Like, like, can I do that? And the answer is yes, we can.

By default, Excel doesn't set up data tables to deal with more, any more than two variables, but we can kind of work around that.

And that's what this session is really about.

So let's have a look on the top left at the model.

We've got a really, really simple model here.

What we're looking to do, and I'm gonna highlight this in yellow, is we are looking, we are looking to arrive at the profit.

And so to arrive at the profit, I guess we need revenue and revenue would be the product of the price of the product multiplied by the demand.

Oh, no, we don't have anything in demand.

So if you look at the sell above, it looks like we've got costs slash demand cost is 45 cents, almost by default the demand is 29,000.

But if I try and multiply by that sell, this is obviously not gonna work.

Okay? So that's a problem.

What would be great would be if we could take the demand and extract that from that cell, chuck that in there, that would be very nice.

And if we could take the cost and extract that from that cell chuck that there, that would work really, really well.

And then we can go forward and work out revenue, work out variable costs, and ultimately work out profit.

So I wanna do a little bit of formula work to be able to extract the demand and unit cost from that cell.

Now in addition to that, if we look at the the data table, the data table as we have it set up, it's gonna sensitize on price. And I might just erase those limitations.

It's gonna sensitize on price.

What I wanted to do is I wanted to have it sensitized on the unit costs and on demand.

And it's difficult to achieve that.

If you try and point the data table to those two cells, we need to point it at one cell.

So if you might, you might think, well, what's Jonathan talking about here? What's, what's, you know, what, what's the game plan? The game plan is to have the data table go and change Cell C6.

Okay? So we want the data table to variously change C6.

Then we want to extract demand and unit costs from C6 and have that flow into the model.

So what I wanna do first is I want go and grab C6, I'm gonna copy it and I'm gonna go to cells D16, E, F, G, and H16.

And I'm just gonna copy, I'm gonna copy it and paste it as a value.

So ALT HVSV, just to paste it as a value.

So now we've got, actually for each of these cells, we've just got the same numbers.

We've got 45 cents and 29,000 all the way across.

I quite like having 45 cents unit cost and 29,000 as my midpoint.

What I'm gonna do is I'm gonna work to the left and work to the right and I'm just gonna amend these cells.

So if you look, the, so immediately to the left, immediately to the left, rather than have 45 cents, why don't we have 40 cents at 29,000 of demand to the left of that, rather than have 45 cents, why don't we have 35 cents, 35 cents and 29,000 of demand? If I go to the right and just to adopt the same kind of approach, I'm gonna say, well, not 45 cents, why don't we have 50 cents? And why don't we have 55 cents? Okay? The idea is, and we're not quite done yet, but the idea is that if we choose one at random, we could take 35 cents and 29,000, the data table could take that heading and input that into the model, and then the model would push out the separate demand and unit cost numbers and then ultimately get to the profit.

And so the data table having done that would then move to this cell and do exactly the same.

So it would go and push 40 cents and 29,000 into there.

So you can see that you can get the data table to choose one cell, one heading and chuck that into the model.

And then we can actually change two separate things at the moment. However, if you look at the model, you could reasonably say, Hey Jonathan, we've only got, you know, we've got unit cost changing, we've only got one demand level across here.

So why don't we copy those headings and go to the right and paste those on the right hand side of the data table.

What I'd like to do is each of the 29,000 demand numbers, I'd like to change those to be 30.

So let's do that.

If I go and grab those numbers, so let's go and grab those numbers.

I mean I could just manually type in 30,000, but a faster way of doing it would be to use fi, find and replace.

So control F is fine, but control H is find and replace.

So if I hold down control and tap H, that gives me find and replace and I'm gonna say, Hey Excel, go and have a look at all of these cells I've got selected here.

Find 29,000 and replace that with 30,000.

And if I say replace all, I think it should change, it should replace, find and replace five cells, replace all, all done.

We made five replacements. Okay, close.

So now we've kind of set up the table, we've got 35 cents and 29,000, 40 cents and 29,000 et cetera.

And then further through the table, 35 cents and 30,000, 40 cents and 30,000, et cetera.

So this is all good, but we do now have to turn away from the data table and do a bit of formula work.

I should definitely say to you that there are a number of different formulas we could use here.

There are a number of ways we could there are a number of ways that we could achieve what I'm about to do and I'm just gonna select one of them.

So what I wanna do is I wanna try and extract the demand from cell C6, 29,000.

And you might say, well how am I gonna do that? Rather than build that in a single cell, I'm gonna go to the right hand side, I'm gonna show you my formulas to move these down.

I'm gonna go to the right hand side and I'm gonna try and build out a bunch of function that I can then nest together that will extract 29,000.

As I say, this is not the only way of doing this.

There are other really fantastic approaches to doing this.

You know, there are other formulas that you could potentially, potentially select.

Alright? So in my head I've got bouncing around.

I could use the right function.

So for example, I could say equals R I R I, it says, Hey Jonathan, you might want to use the right function.

This returns the specified number of characters from the end of a string.

So that sounds pretty good, right? I'm gonna escape from that. I wanna kind of start at the right of this text string and return, I dunno, 1, 2, 3, 4, 5, 6, 7, 8 characters.

I wanna return eight characters here, 29,000, let's do that.

So if I say equals RIG and hit tab to complete the function, the syntax says Jonathan, gimme some text.

So I'm gonna arrow over to C6 comma number, number of characters.

So this is a horrible thing to do, but I'm gonna hard code in an 8.

So if I hard code in an eight and close the bracket, then it returns 29,000.

I'm not a massive fan of the hard coding of the 8 in there.

And so I wish there was a better way of doing it.

And the reason I don't like it is, well what if the demand was, say not 29,000 but was like 129,000? Well, it's still only gonna return 29,000.

What if the demand was say 900 well slash space 900? That's not great. So I don't like the hard coded 8.

I think we need something a bit more dynamic.

And if you look at the cell, you can see that the separation between the cost unit costs and the demand is a backslash.

And so what I'm gonna do is or is a slash what I'm gonna do is, is I'm gonna say to Excel, why don't you find that character and maybe like count up to there.

That's kind of what's running around in my head.

So I wanna do this all separately.

I'm gonna go it's gonna go to the top of this sort of section of workings and I'm gonna say Excel.

I want you to find that slash character.

So F I N and if you look I've typed in F I N Excel said I reckon you wanna use find.

What does that do? Well, it returns the starting position of one text string within another text string and it is case sensitive.

Okay, so if I hit tab it, first of all says find text, like what, what are you looking for? We do need to wrap this in double quotes.

So double quote slash double quote.

So go and find that slash character comma and Excel says, where, where do you want me to look? Go and find it here. So it's 6. So it's character 6.

And if you think about what it's doing, it's starting on the left left and it's counting 1, 2, 3, 4, 5, 6.

It's the 6 character, 6 character.

I don't, I don't think I can use that 6 in the right function.

What I wanna do is I wanna kind of, I need to somehow start on the right and calculate, you know, like use the right function and calculate something excluding the first six characters or maybe even seven characters I guess.

Because there's a space there as well.

So what I'm gonna do is I'm gonna say, do we know the length of that text string and whatever the length is, can we subtract six from it? And that will give us anything beyond that slash character.

So let's do that. I wanna use a function which will give me the length, the tech, the length of the text string in the cell.

So I'm gonna say equals LEN and that is the function.

So the function is len returns the number of characters in a string, let's it tab an arrow over to that cell.

So I didn't know that, but apparently we've got 15 characters in there.

So I guess if you said we wanna get back to 8, right? So this might be a bit silly, but if I say it course 15 minus 6, ideally I wanna get back to 8, but oh no, I get back to nine.

And the reason I'm getting to nine is we zoom in so it's easy to see.

There are 15, there are 15 characters in this string and we're getting, there are 15 characters in the string and the slash occurs at character 6.

So if we said 15 minus 6, it's basically saying 1, 2, 3, 4, 5, 6, 7, 8, 9, it's including that space.

I don't really like that.

So what we're gonna do, just a very easy amendment, we're just gonna say minus 1.

What I can do now is start to pull this all together and I might do this on the left hand side in fact.

So what I'd like to do, it's, it's absolutely fine the way we've built this, but it's kind of untidy.

So um, I'm gonna try and I think we used the word nest.

I'm gonna nest all of these functions together.

So first of all, I'm just gonna go and grab the right function, including the equals sign and chuck it in cell C7 and show you my formula there.

And then what I'm gonna do is say, well I don't like the the hardcoded 8, so we need to kind of like work with the formulas above.

I'm gonna go and grab this formula, which is E4 minus E3 minus 1 and it has got an equals in front of it, but I don't want to grab the equals, I'm gonna select it C to copy it.

I'm gonna go back to my formula here rather than have that hard coded 8 control V, I'm gonna post paste that formula in there.

Okay, so that means that you didn't really need to have these cells anymore and the formula will still work, we're not quite done of course, let's keep going.

So um, if you look at the formula, it references E4 minus E3.

Why don't we click on cell E4 and grab that function, which is lens C6, gonna select it control C to copy, go back to the formula I'm building and rather than use E4 as a reference, let's paste in that function and then the formula reference is E3.

So again, why don't we go to E3 and grab the function sitting in E3, not including the equals sign control C and let's paste it over here.

So control V and what that now means is that all of the cells I'm highlighting in gray, because I'm kind of trying to de-emphasize them all the cells I've highlighted in gray, we don't really need those anymore.

Okay? That's not necessary.

I'm gonna leave them there just for our reference. Okay? You don't have to do it the way I've done it.

One, you don't have to build it in four stages and nest it together.

You could just build it as a stream of consciousness. If you are happy doing that, I'm only breaking it out so it's clear to see what I'm doing. So you can audit my thought process and you don't actually have to use find len and write, there are other functions that you can play around with. There are probably a number of functions in fact, but a few of them spring to my mind right now that you could use. So this is just one example, okay, we're gonna do the same for unit cost.

Now the good news is that the unit cost I think is probably gonna be a fair bit easier.

So we'll do the same sort of thing.

I'm gonna say I want the unit cost, which is 45 cents and I reckon that's one including the period 2, 3, 4 characters from the left.

So no surprise, I'm gonna use a function called left.

So it equals LEFT left tab and uh, Excel says, hey where's the text I'm looking at? Well go and have a look at that cell comma.

Okay, so if I look at that cell and I start on the left, how many characters do you want me to count out? Well, let's count out 4.45 exactly the same discussion that we had previously.

I'm not a massive fan.

Probably you are not a massive fan of having a hard coded four in there.

So can we do something about that? Well, previously we used defined function.

So let's say equals F I N D go and find open quote slash close quote in this cell and it chooses a 6.

I don't want it to be a 6, right? Because we know I want it to be a 4.

So what I in fact don't want for the fine function is I don't want it to include the character position for the slash or the character position for the 0 I, I want to deduct that from the position of the slash character.

So this is pretty easy.

All I'm gonna do is I'm gonna say minus 2 and the fine function then I'm gonna grab, then we're gonna go into the unit cost.

I'm going, I'm gonna go and grab the left function, including the equals sign copy and paste that in.

I'm gonna go and grab where that 4 is.

Don't like that 4. So I'm gonna go and grab the find function and copy that and paste that in place of that hardcoded 4.

And that means that hopefully, and we can test this, I can delete the cells I'm highlighting in gray because we don't really need them anymore because I've nested the functions together and if I do delete them, it still works really nicely.

I've actually slightly put them in the way of that formula.

So I'm just gonna move them down there so you can see what's going on.

Okay, we are absolutely on a home run here.

So what I can now do is calculate the revenue, which is simply the price multiplied by the demand.

Let's show some formulas here, but these are not difficult calculations.

I can calculate the variable cost, which is the unit cost multiplied by the demand.

And I can calculate the profit, which is the revenue minus the variable cost minus the fixed cost.

Okay, I've got 57,950.

So there's nothing weird about that.

You might say, oh, I wonder what the profit would be if we change the demand and we change the unit cost.

You know what we could do? We could maybe like go to the data table and copy one of these headings and if I paste it as a value, all HVSV, now I haven't hit enter yet, but when I hit enter, this is gonna read 35 cents and 29,000.

Those numbers are gonna get extracted into the sales below and the profit's gonna update.

Let's do that.

So that's really all our data table is gonna do.

It's gonna grab the headings and it's gonna punch them into that selling the model.

And then the model would recalculate, I'll control Z just to get that back to where it was at.

So final steps, let's go and grab that profit number that really needs to go on the top left of our data table as is the convention when we're building data tables.

Okay, let's just go and grab that. I've color coded it and done an arrow for you just so it's nice and obvious what I'm doing.

Classic data table approach.

I'm gonna grab the whole table, including the headings and the keyboard shortcut, alt A for data, W for what if, and t for data table and Excel says, great Jonathan, I get it.

You're on the top left. This is what you want me to calculate the profit, I'm gonna spray that across the table.

But what, you know, you want me to change some stuff, right? So I can see I've got some variables in a row here.

So if I take each of those respectively, where do I put those? Well, I want excel you to take these cells and chuck them into C6 tab and Excel will say, right, and I've got some cells here, looks like maybe the price.

So I could take those and chuck them in the model somewhere.

Where do you want those to go? Well, I want those to go in C5 and then if we hit okay, what Excel will do is it will then run all the possible profit numbers, all the possible outcomes for variations in demand and variations in unit cost.

And that sort of brings us to the end of of the example really.

But we have got a couple of minutes.

And so if you do have any questions, please feel free to ask. There is a q and a box and I'll, I'm just gonna open that up and make sure I've got my eyes on that.

So if you have got any questions, I'll just, I'm gonna pause for just like 10 seconds or something.

And hopefully not an uncomfortable silence, but I'm just gonna draw a breath and just see if there's anything you wanted to ask.

Okay? So it doesn't look like you, you've got any questions.

So look, thanks so much for dialing into the session.

I appreciate you guys have busy, really nice to see you on the call.

Got loads of people in the room today.

Really looking forward to seeing you on another session.

Have a fantastic Friday guys. Take care. Bye now. Thank you.