Name's Jonathan.

It's great to have everyone in the room.

We're going to go through some stuff on data tables, and, uh, if you follow the link, there we go. If you follow the link in the, uh, chat box that it take to, it'll take you to this page.

Scroll a bit further down and download the file sensitivity data tables Workout one empty would be great.

I have that open already, and so once you've jumped into that file, um, it would be great if you could let me just get my stylist there.

It would be great if you can go from the welcome sheet.

If you can jump to the simple data tables sheet for me.

I'm going to do that now. You can click on that with your mouse, or you can hold down control and tap page down to jump you there.

Okay, so let's have a, a talk about data tables and what they are and why we're going to use them in this file.

We have a very, very simple financial model.

I probably couldn't conceive of a model that was more straightforward than this, but of course, you know, a real model is going to be, you know, even slightly more complicated.

It's going to be significantly, uh, larger.

What this model does is it picks up the price and the demand, and from that calculates the revenue, uh, picks up the unit cost and the demand. And from that, it calculates the variable cost to get to the profit, take the revenue, we deduct the variable cost and deduct the fixed cost, which we were given.

We get to the profit number.

So, uh, it, it wouldn't be an unusual thing for me to calculate the profit and say, Hey, you know, what do you think, uh, the profit might be? If the revenue changed is the, the, the, the, uh, the selling price changed, what do you think the profit might be? If the cost structure changed, that wouldn't be an unusual thing for me to want to explore.

And actually, it's relatively easy for me to, for me to do that.

Now, before we go anywhere and do anything with this, let's just build this model.

Let's get this built. So the revenue not hard at all.

If you go and grab the price and you multiply it by the demand, then that gives you the revenue.

If you want the variable cost, you can go and get the unit cost and multiply it by the demand to get the variable cost.

And if you want to do the profit, well, the profit must be the revenue minus the variable cost and minus the fixed cost. I've got fixed cost given to me, 45,000.

I've got profit at 57, 9 50.

Now, as I said, let me zoom out a little bit here.

As I said, if you wanted to explore how the profit might change, th this really simple model doesn't make that terribly difficult.

So I could copy this and just maybe paste this a few times.

So I could take this, this, the sales price and I could add like, I dunno, a dollar to it or something.

Let's paste that over here.

I could take the, uh, unit cost there and I could, uh, maybe subtract 0.1, uh, maybe 0.5 or something.

Oh, sat and subtract 0.05, sorry.

Um, so something like that, I mean, uh, the, the numbers are not really that relevant, but of course what I could do there is I could explore, um, I could explore the different profit, profit numbers.

Now, if we had a, if we had a model, and I really don't think it needs to be a very big model, but if you had a pretty basic maybe, uh, m and a model or an LBO model or just a regular three statement model, even a very basic iteration of those things, that's going to take up a good sheet, right? You know, in Excel it could be a hundred lines, maybe 150 lines deep, you know, maybe it could be 10 columns, 10, 15 columns wide.

And so you, you know, it, it's, it's relatively big.

So to try and copy that and paste that in some sort of meaningful way, it's just too cumbersome.

So the question is, well, is there a better way of doing this? And, and, and at that point we, we are looking at data tables because data tables are actually an extremely good way of sensitizing a model.

So let's hold down control and tap ZA few times just to get rid of those other iterations. We're going to achieve the same kind of thing, but we're going to do it using a data table.

So if we look below, we've got a, a pretty simple data table.

It says, now this is important.

It says one input and one output.

So in terms of output, it doesn't really tell you what it wants you to output, but what I'm going to do, I'm gonna color code this or hh and then I'm going to go and select yellow F four repeats. The last thing I did, uh, let's go to cell C 14 and let's strike E equals.

So in C 14, let's make it equal to C 11.

Now, the reason I'm doing that, I'm going to build a data table that sensitizes for the, uh, change in price, and it, it outputs, it spits out the profit.

So I'm interested to know the profit number for a change in the price.

And building a data table is a sort of standard thing in Excel, but it requires you to, uh, set it up in a certain relatively rigid way.

What we're going to do is, uh, at the very top, as we've done already, we we're going to put the outputs. We're saying, Hey Excel, this is what I want to show.

I want you to calculate this across the table.

And then down the side, we've got the input.

Now it's super important when we're building the data table that we select the entire table, the case, select the entire table as I've done there, including these headings and here these input cells, uh, headings and including the, the output at the top.

Now there is a keyboard truck cut, and it's alt a w and T, but let's just have a think about that.

If I go and grab those cells, again, I want to use a data table.

So I'm going to tap a tap alt to bring out the hot keys.

And then, uh, looking at the hotkeys, I've got things like file home, insert, draw page, layout, formulas, and then data.

And actually a data table, not no great surprise, probably a data table sits within the, uh, the data ribbon in Excel.

So as I've shown here, let's tap a on the keyboard and then I appreciate that there are a complete sea of different shortcuts here.

But if we're doing, um, if we're doing a, a, a sensitivity, then we, we are looking at sort of what if analysis that what if the from sales price was this number, what if the cost was this number? And if you look about probably about three quarters of the way along this ribbon from left to right, you can see, um, w for what if analysis.

I've got w there, so alt A W and then you've got things like, uh, scenario manager and Gose. But we're interested today in data tables.

So I'm going to hit T, so, or a WT now it doesn't look like much is happening here, but Excel has chucked up this, uh, uh, dialog box and it says, Hey Jonathan, uh, okay, so you want to build a data table.

Of course you do 'cause you've used that shortcut or a WT and Excel can see where I want to place the data table.

So that's not in question without me doing anything further.

Excel can also see what I want to output.

So it looks at the top of the table and it knows that I'm trying to output the profit number.

So it just knows that by the way that the table is laid out, but it doesn't know what I want to change, it doesn't understand what I want to sensitize for.

So I could look at the table and I could say, well, look, Excel, it's really obvious here, you know, you've got, uh, you've got the price.

So what I want to do is I want to take a price of $3 and 50 cents and I want to substitute that into sell C five and then rerun the profit number and then spit out the profit into the table.

'cause that's my output. And then I want to have the price at 3 75 want to go and push that into the, uh, model, have it rerun the profit number and spit that out.

So I kind of systematically excel when you go through and pick off all of these price numbers and chuck 'em into the model and Excel will say, okay, um, Jonathan, if you look at the table here, it says, uh, row of inputs.

Are there a row of inputs? Well, actually in this example, there are not.

So Excel is set up by default to be able to sensitize on two inputs.

You can have a row and a column, but we haven't built the table in that way here. We've built it in a, a more simple fashion.

So I'm going to, you don't have to put a zero in there or do anything like that, we can just leave it blank.

So I'm going to click into the next field, or you can hit tap to move to the next field.

And Excel says, Jonathan, do you have a column of inputs? And the answer is, well, yeah, of course here, you know, these are inputs.

I want you to grab these respectively and substitute them into the model and then move to the next one. Substitute to the model, having run the profit time. You do that. So, um, yeah, their inputs and Excel will say, but Jonathan, I dunno what they are.

And I might say, well, that's crazy because it says price there. So obviously it's the price, but Excel, it won't be able to figure that out.

So what it wants me to do is it, it knows it's got these cells, it knows it wants to put them somewhere, but it doesn't know where to put them.

So I'm going to click on C five, okay, I'm going to click on C five.

Now it gives you dollar C dollar five, it doesn't matter, you know where you are as you move up and down the table, you could even be moving to the right here.

It will always be taking these numbers and putting 'em to sell C five because it's got dollars dollar signs around it.

Okay? So if I click okay, what I get is I get a range of different profit numbers.

And the cool thing is it's all kind of captured in a, a very, very neat and tidy, uh, very small table without having to recreate the, the, the underlying model itself.

Now, I probably should say a few things at this point.

If you run the data table and all the numbers are the same, something's wrong, right? I mean, something's definitely going wrong with that. If all the numbers are the same, that would seem a bit weird.

Perhaps it hasn't calculated properly in some way.

And there are two reasons why that is. And we'll, we'll look at one of them now and we'll look at one of them shortly.

So if I go, um, into file and options, which is alt FT, okay? So, uh, it doesn't matter where you are in the spreadsheet, that's not really relevant, but if you tap alt, you tap F, see file is FF for file.

And then strangely, t for Excel options.

So all f and t, it brings up the Excel options and it defaults to general.

But I'm going to move down to formulas.

And if you look on the top left, it says workbook calculation automatic, which is great, but it also says partial, and I won't read all of this out, but if you hover over partial, it says, control what Excel calculates when a value is changed, when a value is changed and automatic in automatic mode, everything is calculated partial mode, everything is calculated with the exception of data tables and Python formulas.

And so that, that's interesting.

We, we need to be aware of that.

Now, you know, modern computers are hugely powerful.

I don't know what processor I've got in my PC in front of me, but I know it's got about 16 gig of memory, which is an insane amount of memory.

Nevertheless, if we build a, a large financial model and we maybe build several data tables, you know, as a kind of dashboard maybe in that, uh, in that model, every time you change a cell, anything in the model, it will rerun the model, which you wouldn't really perceive, but it will rerun it for all the data tables. So it could end up rerunning it sort of a hundred times.

And actually that can be quite annoying.

Now, every time you change a cell, you amend something, you add something in, you know, maybe you just press enter and then you decide, you know, press F two to jump into the cell and press enter to move beyond it, move beyond it. You didn't even do anything, but you force it to recalculate and it can really stutter.

So it's pretty annoying. So, um, Microsoft have built in a feature in options and formulas, which, uh, does a partial calculation and, and won't recalculate data tables really to speed things up.

A lot of people you find on the desk might have that just selected.

Uh, if they don't use data tables very often, they don't really care.

So they might, you have that selected by default.

So if you are in someone's model and you're running it and you get all the same numbers in here, that's because it's not automatically calculating.

And the reason it's not automatically calculating is probably because of this.

Now, there is one other reason there could be a circularity within the data table, but I'm going to talk about that in a minute.

Okay? I'm very happy with the automatic calculation, so I'm going to click okay.

Uh, wouldn't it be cool perhaps if we're only going to sensitize on the price? Wouldn't it be a really good thing if, um, we, we were showing more than just the profit? It might be interesting for me to show, say, well, let's scroll down to show the profit, which I think we've highlighted in yellow already.

Maybe the variable cost, and if I just pick a color at random, we could highlight that in green and maybe the revenue.

And I think maybe that could have MoVI purpley color could use for the revenue.

Wouldn't it be cool if we could do that? Well, we, we can, let's just move beyond that first example.

If I grab my stylus, we are going to show the profit number.

We are going to show the variable cost number, which I'm about to color code, and we're going to show the revenue number.

So it's really important that the data table, um, what color did we have for revenue that was that sort of move color.

It's really important that the, uh, data table is linked in, you know, kind of dynamically to the, um, model.

So for revenue, if I say equal, I can go and grab the revenue for the revenue for variable costs, I can go and grab the variable costs and for profit I can go and grab the profit.

So now, little bit more powerful we can recalculate the data table, but those three outputs, so if you look at the heading, it says still one input.

We only going to change the price here, but we've got multiple outputs.

Now just take note that to build the data table I have selected on the leftmost side, it's super important.

I've selected the, uh, inputs because that needs to be kind of like within range so that Excel can find them.

Um, and I've also selected at the top the outputs.

So I haven't selected, I'm, I don't want to do this, I don't want to select the headings. That wouldn't work because Excel would look at the top and think it was trying to spit out revenues and spit out variable costs and profit, you know, as like text strings or something that didn't make any sense.

So dunno what, what, what would happen there? Nothing good.

So, um, we're going to make sure that the top most row in the mo in the data table that we're building represents, um, the, the actual outputs.

And then we'll use the same thing. You can see the shortcut above alt a, w and T.

Uh, Excel says, Hey Jonathan, uh, what's your row input sell? It is Excel's kind of saying, Jonathan, I could take these numbers and I could grab these numbers and I could go and substitute 'em somewhere into the model.

Do you want me to do that? But the answer is not at the moment excel for the table I'm building. These are outputs. So they're, they're not inputs.

So I don't have any row, I don't have a row of inputs for you Excel.

I'm going to skip over that.

So I'm going to click to the next Excel, we could press tap and then Excel will say, right, I've got, you know, I've got a, some numbers here.

These must represent inputs. Surely where do you want me to take these numbers respectively and put them? Now we've talked about this before.

So if I, I don't need to use my mouse, I can put that to one side.

I can arrow up and if I arrow up, what I want is the price to go into sell, um, C five.

Okay? So when it's go into sell C five, and if we hit okay, what it will do is it will calculate all the various revenues if the price changes and all the various profits. If the price changes, hang on a minute, the variable cost remains exactly the same, which seems kind of pointless.

So why are we bothering to show that? Well, what we might say at this point is, um, maybe it would be a good thing if a data table wasn't just sensitizing on one variable but had the ability to sensitize on multiple variables, which is our next example.

So we do have to do a little bit of work on this example because it's mostly blank.

Um, it doesn't really tell you what the output is, but we're going to assume it's profit.

If we've got two inputs into a data table, the output needs to be on the top left.

So, you know, previously the output was at the top of the data table.

We had the output here at the top.

Well, now what we're going to do is we're gonna put the output on the top left because we are sensitizing for two variables. There wouldn't be any space otherwise.

I will show you my formula, but I'm going to link in the very top left to the profit number in C 11.

So if I now selected the, the, the actual table, Excel doesn't actually have enough information to be able to do anything at the moment.

What it does have is it has information on what to display.

So when we give it a bit more data so it knows how to sensitize on various inputs, it will always know now to show the profit.

So let's do a bit of work here. Uh, unit costs.

So apparently we want to sensitize on unit cost.

Now if we go and have a look at the original model, the unit cost is 45 cents.

So I'm going to type in 0.45, okay? What I didn't do is I didn't link it into the model, and I will explain that in about 20 seconds.

Okay? So I didn't link it into the model, I have hard coded it. So I'm going to say this is not linked to the model.

It is a hard code, okay? So this is not linked to the model.

It is a hard code, which you can see there.

Now we can still have formulas in here.

So, uh, you could say, well, what would happen if the, uh, what would happen if the unit cost went down? So if I said, uh, 0.45 minus 0.05, for example, so 40 cents and 35 cents, or what would happen if that kinda midpoint cost went up 0.05, 0.05? What would happen if it went up? Now you, you might say, why don't you just link it in, Jonathan, the problem with, uh, and I, I can explain this.

The problem with me linking this when we run the, uh, when we run the data table, um, so like let's say for this cell here it says 40 cents. When we were on the data table, what would happen is it would take, uh, this cell is 40 cents, which remember is 45 cents, minus 5 cents. That's the formula. So it would take that cell and it would go and squirt it up into the model into here.

And so that this cell here, I wonder if I can just draw this down here.

Sometimes excel's a little bit fiddly with the annotation. There we go. So it's going to take, it's basically gonna take that cell and it's going to chuck it in there and it'll rerun the model and get the, um, profit number and chuck it in.

The problem is right, if this was formula driven, so if I linked this in to that cell in that way, if the data table took 40 cents and substituted it into there, then this sale would become 40 cents and 40 cents would then feed into here and Excel would say, Hey, wait a minute.

I thought I was supposed to be, uh, taking 5 cents away and showing that number in there.

If, if 40 cents gets fed into the model, then 40 cents would be fed into the data table.

And so this would become 35 cents and the 35 cents would then be fed into the model and then that would become 35 cents in the model and then that would feed into the data table and it would actually create a circularity.

So unfortunately 0.45, um, uh, 0.45, it needs to be a hard code.

Now there is a workaround to that and there's a cool, uh, there's some cool content in Felix if you're interested in looking at that.

But, uh, for this 30 minute session, we won't, won't get time to look at that.

Uh, okay, so, uh, we're going to do the same here for the price.

I'm going to say equals so tempted to link it to the model, but I won't, the models are $4, so I'm going to leave it at $4.

Now wouldn't it be nice if perhaps the very top left, uh, because we've got the highest costs, wouldn't it be nice if that was the lowest profit number? So the kind of combination of the highest cost and the lowest selling price.

So I'm going to take the selling price, I'm going to subtract point, I don't know, two, five maybe from that copy and paste that.

So on the top left of this data table, we've, we've got the worst of all scenarios. We've got the highest cost and we've got the lowest selling price. So that, that will give us the, the lowest profit number.

And then if we think about the bottom right of the table, we've got the lowest cost and we want the highest price to give us the highest profit.

So if I take the $4 and I add point, um, two, five to that and copy that down, okay, as with before, I'm going to select the entire table.

I'm going to say alt a, w, and T for data table.

And now let's just be careful, I built the data table and Excel.

Say, Hey Jonathan, what do you want me to show? Well, that question's already been answered, Excel, because if you look at the top left, I want you to show profit, okay? So Excel will say, well, what do you want me to change? Well, look, look at the headings here.

It says row input cell.

So you have a row of headings here, why don't you re respectively, why don't you take each one of these headings and substitute it into the unit cost cell.

And again, Excel said, I don't know what that is.

Like which cell are you talking about? It's, well, the one in the model where the unit cost goes and Excel isn't going to know that.

So what we need to do is we need to kind of scroll up to sell, I think it must be cell C seven and chuck that in.

And then if we click to the next field, it now says column input cell.

So we have got a column of inputs or a column of headings here.

Excel basically is saying, I can see those, I know you want me to take those numbers and I know you want me to substitute it into the model, rerun the model.

And Excel will say, I know you want me to show you the profit because that much is obvious, but it would say, I dunno where I'm supposed to substitute these into.

So if we scroll up, I'm going to tell it that the uh, correct sell is in C five.

So the price sell is in C five.

If we click okay, then it'll rerun the model.

And what we've got is we've got the worst of all scenarios on the top left. This is the lowest possible profit number.

And on the right hand side it is the highest possible profit number.

Now I think it's probably pretty likely if you are building a model and you want to sensitize it, you want to sensitize around it. Yeah. So sometimes I might look at maybe a dashboard and the model's being set up in a certain way.

I look at the model and think these input numbers that are actually in the model are probably what the person building the model thought was most likely.

But the sensitization will flex some of those inputs.

You'd imagine whatever the inputs are in the model, but the data say would flex either side of them.

So what I mean is it wouldn't make sense in the model.

If the unit cost is 45 cents, it probably wouldn't make sense to have like 80 cents here and sensitize either side of that because that would mean that we're not sensitizing around the kind of midpoint for the model.

45 cents feels a lot more likely.

And the same for the price you'd imagine you'd sensitize around that to the point really where I would suggest that the intersection of these numbers there, the intersection of these numbers, uh, uh, is the kind of where the, where the model is built.

57, 9 50 is that midpoint.

It's also worth saying that if you wanted to take these numbers and use them and apply them, maybe bring them into a, um, some, uh, a PowerPoint or some other sort of visual analysis, you might not take the entire table.

You might take a subset of the table.

So let's just get rid of that.

You might, for example, grab these and say, well, I've used a data table to analyze various outcomes, but these are the most likely outcomes.

So I think in the next two years, each year the profit is going to range between 66, uh, six 50 to 49, 2 50.

That is what I think is a kind of reasonable range here.

Okay? Um, so that is data tables that was, uh, intended to be a 30 minute introduction and we've hit that point in time. There's some really Cool content on Felix, um, on this.

So please feel free to grab some, uh, extra stuff.

Um, uh, we referenced for example, uh, the ability to link these things into the model that would create a circularity, but there is a, um, there is a work around.

So kind have a look on Felix to pick that up. Okay? I wouldn't wish to keep you any longer than, uh, we'd intended.

So I'm just going to say a massive thanks for being dialed in. Really great to have you on the call, uh, loads of you on the call today.

Uh, and hope to see, uh, um, many of you in the future in other sessions.

Have a great day guys. Thanks ever so much. Thanks you.