My name is Phil Sparkes, as you can see on the screen.

Hopefully, it's sharing correctly.

And I'm a full-time trainer at Financial Edge.

And we're going to be going through data tables, sensitivity analysis, and we're going to look at the very basics of setting up a data table very quickly, just recapping that, and then we'll dive into how we extend that, how we use data tables to combine two variables, and then a couple of examples of how you use a data table with three different variables, which needs a little bit of massaging of the data table.

Really sort of requires you to work quite hard to drive the data table with three different variables.

So I'm going to dive basically straight into Excel. If I end the slideshow, and hopefully if it's here, here is our data table. Okay. So this is basically what you can download from that URL link.

And so we're going to start by diving into the simple data tables, just reminding you of how a simple one-dimensional and then two-dimensional data table works. So we've got a very simple example here, just basically an incredibly simple income statement.

And throughout this session, the boxes that are highlighted in red, the four and the 0.45 here, those are the boxes that we're going to drive with our data table.

What we're going to do is we're going to do a calculation based on this set of data, but then we're going to adjust this data and see what happens to the outputs. And that's the whole kind of point of doing a data table.

So we'll start by just doing the calculation.

So we've got price per unit of four.

We've got demand of 29,000. That gives us revenue of 16,000, sorry, 116,000.

We've got a variable cost in total, which is 0.45 multiplied by that 29,000 of demand.

And then we've got profit, which is 116 minus the 13,050 of variable cost, minus the 45,000 of fixed cost. And it gives us that number there, 40, sorry, 57,950. And I can just see another couple of people have joined.

So if you've just joined and you want to pick up the spreadsheet that we're using, if you just follow the URL that's in the chat box, it should take you to two spreadsheets, and we're looking at the empty one here. So we've done a very simple calculation.

So our first data table is just going to say, what would happen if we change the price? What would happen if we adjust the price in this calculation? And we've got five different prices, and it's really important that in the middle of this table is the base case, is the default, is the four.

Then we can check that our data table actually works.

Always good form to basically include the base case, include the default case in the middle of your data table.

So the first thing we do is we start at the top, and we just put a link to the calculation. And that tells the data table what calculation it wants to sensitize, what calculation does it want to adjust with these various different inputs. And then, oh, I can see one more person has joined.

If you're a bit late coming in, if you jump into the URL that's in the chat box, then that'll basically get you the files that we're working on. Okay, so we basically highlight the entire thing, the whole data table, including in that column, all the various different prices.

And really great keyboard shortcut for data tables, Alt + D + T.

Oops. It would help.

Let me try that one more time because I jumped off to the chat box. Alt + D + T.

And we get this little box here that drives the data table.

And now in this one, we've just got one column of figures.

So in the column section, in this column box here, what we need to do is tell Excel where the variable is that that column of inputs is going to drive.

And the answer is it's this four box up here, the one that's in red. We click OK, and we get basically a data table super quick, works really, really quickly.

And right in the middle, you can see is our default case.

And what it's done is it's adjusted the price and said, what's the profits, what's the outputs if we reduce the price? If we reduce the price that we sell for and everything else stays the same, unsurprisingly, the profit falls.

If we increase the price from 4 up to 4.5, then that profit rises. Okay, so that's a simple one-dimensional, one variable data table. Let's go and do the same thing with two inputs.

Now, the first thing we need to do is to set up the data table headings, and I'm just going to copy and paste the price down to here.

And then for the unit cost, what I'm going to do is I'm going to start with zero. Oops, I hit something odd. 0.35. And then I'm going to say, add 0.5 to each of those, copy it to the right.

And you can see I've basically got, in the middle case, I've got the price of four and the cost per unit of 0.45.

Again, we need to link this into our calculation, and we do that by putting the result of the default calculation in the top left-hand corner.

So we link that up there. I'll just do a little formula text so you can see where that comes from.

And there we are. And now we need to drive the data table exactly as we did before. So we need to include the rows and the column headings, and that profit number and the result of the calculation in the top left-hand corner. And then again, we hit Alt + D + T And then we're going to use both of these. So we need to tell Excel where the variable is in the calculation, which is driven by the things that are in a row. So the things that are in a row are these unit costs here. So where is the unit cost within our profit calculation? And it's there, it's 0.45, it's in C7.

And what about the stuff that's in a column? Well, the stuff that's in a column is the price.

So where's the stuff that's in a column? The variable is there.

It's the price variable up in the calculation.

I click Okay. Well, I'll just move this down so you can see.

I click Okay, and it populates the data table straight away.

Super quick, super efficient, real sort of time saver. And again, right in the middle is our base case, that 57,950 that we recognize.

So all pretty good, all pretty sensible.

Now, what we're going to do is we're going to go through two of the methods of generating a three-statement or a three-variable data table. So I'm going to go to this one here, three input data table, and make it a little bit bigger on the screen.

And what we're going to do with this one is we're basically going to construct an unusual set of row headings, or sorry, column headings here.

And what we're going to do is we're going to construct something that's a compound of two of the variables. Now, you can see up here that I've basically got two boxes in red, like we had in the previous one.

One is price, just the same as the previous simple example.

But the second one is not just the cost per unit, it's also the volume as well.

And Excel doesn't care. Excel will run a data table with any variables at all, as long as you can feed these variables back into your calculation. Now, we're going to need a little bit of manipulation to extract this data from this compound variable, from this compound of cost and volume, and get it into our calculation.

But we're going to start with putting, I'm just going to type these numbers in.

So 0.35 is my unit cost. I need to follow exactly the same format as the item that we've already got up in the calculation. So 0.35, and then a space, and then a forward slash. So that space, forward slash, space is going to be our delimiter between the two elements.

And then what it says is we're going to put 29,000 as our volume. And I'm going to copy that to the right just there. And then what I'm going to do is I'm going to go through each one of these and just tweak them, just adjust them.

And it's actually easiest to do manually.

So I'm going to make the cost per unit 0.4, and then 0.45 in the next one, and then 0.50 in the next one, and then 0.55 in the final one. Okay, so I've got that cost per unit moving, and I've got the volume staying at 29,000.

Then what I'm going to do, I'm going to copy all of that, Control + C, move over, Control + V.

And then I'm just going to do a very simple find and replace, and replace all of the 29,000s with 30,000s. So I go up to here to Find and Select. I go into Replace.

And you can see I've already done this before, 29,000.

I want to change all of the 29,000s to 30,000 in that highlighted block. So I hit Replace all, and it changes five of them. And now I've basically got two blocks, two elements of column headings, which have got the unit price, the cost per unit price going gradually up, and one set is going with 29,000 of volume, one set is going with 30,000 of volume.

And of course, I could copy this to the right.

I could do 31,000 and 32,000 of volume and get lots and lots of blocks of data. Now, the hard bit, though, coming up here is I've now got this compound variable, and I'm going to replace it with these elements down here.

But I need a way of extracting each of these elements from that sort of compound piece of text.

So I'm going to do it over here and I'll just show you how to do it.

So what I'm going to do is I'm going to go left to grab the cost and right to grab the demand. But I need to be a little bit careful with how I drive this.

So I'll show you how I do the right one as separate steps, and then I'll show you how I do the left one. So the first thing I need to do is I need to use something called Find. And these are all text handling tools.

So I need to find that delimiter, which is the forward line, and I need to find that. And where do I need to find that? I need to find that in the text there in C6. Is that right? Yes, it's right. Okay.

And it basically tells me that that forward slash is the sixth character in that piece of text.

Okay, so that's going to enable me to find the delimiter, and then I can basically take the right characters, the characters after that.

So next thing I need to do is I need to work out how long the piece of text is, and I do that with a length function. Now, the reason for doing this is you can see that in these items down here, I haven't put a decimal point and a zero at the end.

So the one that's up here has got 29,000.0. The one that's down here has just got 29,000.

So I can't just say the right five characters or the right seven characters. I need to just basically say it's the characters that come after that forward slash. Okay? So you can see now that I've got 15 characters long. The slash is the sixth character, so 15 minus 6 is 9. So it's sort of the nine right-most characters. Need to be a little bit careful, it's not exactly, because you'll remember that the delimiter actually has a space after it. So it's actually the eighth characters to the right. Okay? So next I can say equals the right characters, and which-- I need to put an equals at the front, equals RIGHT.

So which one do I want? I want to point at this piece of text here.

And how many characters do I need? I need 15 minus 6, and then minus another 1 for the space.

And I hit close bracket, and it says 29,000. And it's extracted it exactly right.

And just to kind of demonstrate this works, if I actually delete the .0 at the end, it's okay. It's still okay, because it still takes the right number of characters. Okay, I'll hit Control Z and go back. Okay.

And then the final thing to do is, it's coming up with 29,000, but it's actually text. It's handling it as text, and you can tell that because it's left-justified. So the last thing we need to do is we need to create a VALUE function. And what the VALUE function in Excel does is it takes a piece of text, and it converts it into a number.

So that's easy to do. I just point to that 29,000, and you can now see it's 29,000, and it's right-justified, i.e. it's extracting it as a number. So I can basically point at that 29,000 there, and basically, it's extracted the 29,000. Now, the point of doing this is when I replace this compound variable with each of these items down here, Excel will happily see those different numbers, extract the volume correctly, and drive the calculation with this figure here. Next, I'm going to extract the units.

Cost, which is basically the left-hand three or four characters. In this case, left-hand four characters.

So you'll see that it's basically a VALUE function, exactly the same as we did before.

So we're effectively doing the same thing here, but now it's going to be the leftmost characters. And how many characters is it going to be? Again, what we're going to do is we're going to say it's the leftmost characters of that compound variable up there.

And then how many characters is it? Well, we don't need to find the length, because we just need the characters that come before that forward slash. So we're going to go C6, is where we're pointing to, and then we have another FIND function.

And then the FIND function says, "Find forward slash," and where does it say I need to find the forward slash? Again, that's in that C6 compound variable.

Now, the only thing I need to be a little bit careful of is we know already that the forward slash is the sixth character.

But if you actually look carefully at that contents of C6, you'll see that the sixth character is the forward slash.

I only actually need the first four characters, because there's a space, and then the delimiter of forward slash.

So I actually need the sixth character minus 2, which is going to give me just the first four characters.

Then I close off that LEFT function, I close off the VALUE function, and it should say, and it does, it basically should say 0.45. So now I've got everything working.

I've got everything that's going to extract from that compound variable.

Now I can do my calculation. So I say revenue, 29,000 multiplied by the price of 4, and that gives me that 116, as before.

The variable cost is 0.45 multiplied by the 29,000 again, and my profit is equal to revenue minus the variable cost, minus the fixed cost, which doesn't change, and it's the same 57,950 as we had before. Now, moment of truth, does this work? Well, the first thing we need to do is tell the data table what it's trying to flex, and the answer is it's trying to flex that profit up there.

Again, I'll just do a little formula text just above so you can see where that's coming from. And now, as before, what we need to do is we need to highlight the entire array, the entire data table, including the headings.

Alt, D, T, for our data table. So what's in a row? Well, what's in a row are all of these compound variables, all of these mixed variables with the unit cost and the demand. So where does that go? The stuff that's in a row goes into that cell there, the bottom of the two red cells, and the column input cell, the stuff that's in a column, is the price. Where does that go? That goes into that price, into C5.

Fingers crossed, let's see if it works. Click Okay. And it does, it works.

It works brilliantly well. And you can see right in the middle, I've got that 57,950. Now, the one on the left is just the same as we already did with that two-dimension, two-variable data table.

But over on the right, we basically have something that's got the same broad shape. As we start here and go down, the profit increases as the price increases. As we go to the right, then as our unit cost increases, then so the profit falls.

But all of the numbers are higher. All of the numbers are higher, and that's basically because the volume is now 30,000 rather than 29,000. And of course, you could just copy and paste this to the right, doing 31,000, 32,000, and so on. And so we've successfully created a three-variable data table.

And of course, the great thing about data tables is, as we change any of the numbers, in contrast to, say, a pivot table, as we change any of the numbers, everything changes. So if I change that fixed cost to 50,000, you'll see all of my numbers update, everything updates, so it's live. Whereas with a pivot table, it's a little bit more fussy. You need to manually refresh pivot tables, and they're not very good at maybe holding their structure as well. So these data tables are still very, very powerful for something you're going to come back to many, many times.

Okay, we're going to do one more. We're not actually going to do the middle one, the pivot table example.

It's a little bit time-consuming, and in our very short half-hour session, we don't really have enough time to do that. But we're going to dive two to the right.

We're going to go to this one here, three input tables with an OFFSET function So let me just get this on the screen correctly.

Here we are. And I'll make it a little bit bigger just so we can see. And so what we've got here is we're going to create a data table, and we're going to put the drivers, exactly the same drivers, the volume and the unit cost, but we're going to put them in separate rows up here, and then we're going to use an offset function to gradually step through and pluck out these different variables as we go. So first step, I need to find the headings. I need to get the headings in here.

So I'm going to be a bit lazy and go back and steal the price with a Control + C, jump over to this one and paste the price in there. And then what I'm going to do is I'm basically going to construct the row headings with those two variables.

So the top one, 29,000.

Here it goes. In fact, actually... Yep, that's fine.

And then I'm just going to copy that to the right.

And so I've got 29,000 all along. And apologies, it seems to be centering across the whole table.

But if I copy to the right, then I've manually keyed those in.

I've got 29,000 and 30,000. Similarly, in the unit price, I'm going to start with 0.35, and again, it centers across the whole table. But if I just populate the rest, so I say equals the 0.35 plus 0.05, then copy that to the right, and it's successfully pasting all of that in.

Copy and paste it over on the right-hand side, and there we are.

So we've got our data table, exactly the same variables as in the previous version. Now, what we're going to do underneath is we're going to create a little counter.

So I start with one, and I just say equals one, equals that first one plus one, and I'm going to copy that to the right-hand side.

Now, that's effectively 10 different cases, 10 different scenarios.

But I'm going to use this counter as an offset function.

So the volume is basically going to be driven by using an offset function and counting four, five, six, seven to the right.

And again, the unit cost is going to be driven by the offset function, which is going to count one, two, three, four, five, et cetera, to the right. And it's going to pull out these different elements.

And we're actually going to drive the data table with this offset, with this counter in the top, rather than the actual row headings just above.

So what we're going to do is we're going to start with this section here, and this section is basically going to construct the cost and the demand for when the data table actually runs. So what I'm going to do here, I'm going to have this offsets function here, 14, and you can see that this has got a red box around it.

So we're actually going to drive this offset function with these column headings down here. And so what I want is when this number, this offset number, is anything other than zero, I want an offset function that goes and selects the relevant mix of volume and of cost per unit. So I'm going to put an offset function in this unit cost, and I'm going to say equals offsets, and it's basically going to go to the unit cost down here. And it's often the case with an offset function, you step one to the left or one above, where you want the actual data. Here, I'm just going to go one to the left of all of this unit cost data on the right-hand side. So I'm going to start with C22. I lock it with an F4, and then how many rows up or down do I want to go? The answer is zero.

But how many columns to the right do I want to go? I'm going to basically go to the right, based on this offset number. Okay? And I'm going to lock that with an F4 as well.

I'm then going to do exactly the same with the demand.

I'm going to say equals offset, and I'm going to start here on the left-hand side of all of those demands, going to lock it with an F4, zero rows. How many columns? The offsets number.

And again, I just need to lock that with an F4.

There we are. And so you'll see what happens if I change this offset number to, say, six, and what happens is these numbers basically will change to whatever is in column six, which is the volume of 30,000 and the unit cost of 0.35. But I'm going to leave that most of the time at zero, and when it's zero, I just want the defaults to apply.

I want the defaults to apply, and they're going to be the numbers here on the right-hand side. Okay, so what we're now going to do is we're going to go back to our calculation, and I want to set the calculation up in a certain way so that normally it just goes through the default numbers. It just goes through those default numbers.

But if it's running the data table and it starts basically placing new costs and new prices and new volumes into the calculation, then I want this calculation to recognize that and go off and do the calculation correctly, select the correct price, select the correct volume, and select the correct unit cost from that assumption section underneath. So I'm going to start with price, and I'm going to basically say equals if, and my scenario is if the price down here is empty, so that will be the case. Oh, I need an equals sign And that will be the case if I'm not running my data table, if it's just going with the defaults and I don't have it running the data table.

If that's the case, then I want it to just select the price from the default column.

However, if it's basically the data table is running and the data table is putting a price in this price column here, then I want it to recognize that, the calculation to recognize that, and basically grab the price that the data table is pushing into that cell, C15. So I say equals C15. So basically, if the data table's not running, it's pointing to D15. If the data table is running and it's pushing numbers into that C15, then it needs to extract, it needs to use that C15 in its calculation.

Okay. And of course, now it's basically saying four because the data table's not running.

What about demands? Well, the same thing.

We need something similar for the if statements here.

So I'm going to say equals if, and in this case, I'm going to say is the offset equal to zero? In the case that the offset is equal to zero, then in that case, the data table is not running, and in that case, I therefore need to go and grab the default demand of 29,000. Otherwise, what I need to do is to pick up the offset demand, the demand driven by the offset function, which is going to be in that left-hand column.

I hit Return, and so it's coming back initially with 29,000. And I'll get to the end of this and just demonstrate that it really does work. And then exactly the same with the unit cost, equals if the offset function is equal to zero.

In that case, just pick up the unit cost from the default column, otherwise pick up the unit cost from the assumptions where the offset function is pushing those assumptions into that first column.

Okay. Fixed cost is equal to 45,000.

Revenue is equal to price times the volume. Variable cost is equal to volume times the unit price. Profit is equal to revenue minus variable costs, minus fixed cost, and then we're back to our 57,950. And just to kind of demonstrate this really does work, let's just push a few numbers into here. So if I change the price to $6 per unit, then basically that changes to six up here.

I delete that, and it will go back to being four. Now, what if my offset is equal to six? Then what happens, then basically the demand changes to 30,000, the unit cost changes to 35, and so that's the offset function. That's almost that case counter. So again, hit Control Z, get rid of that. Now here's our moment of truth. Does it work? Well, the first thing is I need to drive it with my profit number, which is the 57,950.

Let's highlight the entire data table.

And as before, what we need to do is we need to hit Alt D T, and so the stuff that's in a row, these numbers up here, these cases, they're going to go into that offset.

And then the stuff that's in a column, as before, is the price, and that's the number that's going to go into here.

I click OK, and it populates everything. Now let's just check that it works.

So we look right in the middle, and we've got our normal base case right in the middle. It does work, and yet remember that this base case has been driven by the data table.

It hasn't just defaulted to the originals with no adjustments or no amendments. It's basically pushed 0.45 as a unit cost, 29,000 as a demand, 4 as the price, and it's still coming out with the same number.

But again, what we see is the profit going up and down as the volume changes. The profit goes up as the price increases, and the profit goes up as the unit cost decreases. So there we are, and as if by magic, it's exactly on the half hour. So that's a really quick whistle-stop tour of two methods of combining Excel's normal sensitivity data tables and using some clever techniques within Excel to basically push them into displaying the results for three variables, not two.

And of course, you could actually make this run with four variables, not three, and you'd have a lot of rows and a lot of columns.

So, some clever techniques here. Hopefully, that's been useful.

Remember that this session is being recorded, so if you want to catch up on this session at a later point, you should be able to see the recording and so that you can watch it perhaps a little bit more slowly.

Make sure that you've got the files, the two files, the empty and the full version, so that you can look at the solutions, and do have a look at the pivot table version as well. Okay, that's it.

That's me done. Thanks for your attention, and hope you have a great weekend, and look forward to seeing you on another one of these very soon.

Okay. Thanks a lot.