Welcome along. Great to have you guys here. My name is Jonathan.

Um, hopefully you can hear me.

Hopefully you can see me.

Hopefully you can also see my screen share as well.

It would be great if I could, uh, get you to jump to this webpage.

We are gonna use that first file there.

So it's sensitivity data tables work out one empty.

There is an answer file for that directly below.

So there's a, there's a full file, uh, directly below and there's also another file out two empty if you wanted some further practice.

Um, but we've got a 30 minute session and so, uh, we, we'll only be looking at a, a few questions, really a few examples from that first file.

So it'd be great if you can click on that sensitivity data tables work out one empty.

Um, if you can click on that to download it, that would be fantastic.

I'm going open that up.

You should land on a page that looks like this.

Now, if I grab my stylus, um, we're, there are a load of sheets here, but we're gonna have a look at the, um, simple data tables sheet and the three input data tables left right sheet.

So this session is really about the three, three variable data tables.

That's our focus. But as a lead into that, we're gonna have a look at a few simple data tables just to kind of build it up, show you some of the limitations, and then how we're gonna get around those limitations, uh, with a, a bit of a bit of Excel wizardry.

Okay? So if you wouldn't mind going to the sheet, simple data tables, that would be fantastic.

I'm going to click on that now.

And, uh, what we have is a really simple model.

I'm gonna zoom in on this a bit that we're going to use, um, to, to play around with some data tables.

So we've got, um, we've got the price, we've got demand, we've got the unit cost, the variable cost per unit, and we're given a fixed cost.

And ultimately what we're trying to do, let's just all HH this, what we are attempting to do is to get to profit and how do we get to profit? Well, first of all, we need the revenue.

The revenue would be the product of the price and the demand.

Now we need the variable cost, and that's the product of the unit, cost and demand.

And then the profit would be the revenue minus the variable cost minus the fixed cost.

I've got 57, 950, so not nothing weird going on there at all.

Really straightforward model, uh, should be no, you know, no issue getting to that.

Now we're gonna have a look at just a few fairly straightforward data table examples, really just to show you the limitations.

So we've got some context for the work that we're gonna do thereafter. The first example Is one input, one output.

Now, when we say one output at the very top of the table, I'm gonna say equals and I'm gonna just link into the profit.

Uh, I have noticed we've got some people joining.

So as people are joining, welcome along guys.

Uh, if you look at the chat box, there's a link to the, uh, website which gives, uh, access to the materials at the bottom of bottom right of that web address.

So if you've just a few people just joining, if you've just joined, please click the link in the chat box, please go to the bottom right of that, um, webpage.

And, uh, the first file, uh, that you can see that, uh, see the title here, sensitivity data tables work at One Empty.

If you wouldn't mind downloading that, that would be great.

Okay, um, so we've got, we've got the, um, uh, we've got the profit and the intention is to try and sensitize the profit for some change in a variable.

We've only got one input variable changing here, and that's the price.

So the question is, if the price wasn't $4, but it was $3 50, but 3 75 or 4 25 or four 50, what would the various profit numbers be? So to do that, we're gonna use, uh, an in inbuilt function in Excel called Data tables.

The shortcut is alt a w and T.

What I'm gonna need to do to use a data table function is select the entire table.

So the whole thing needs to be selected, including that the column headings and the, the result we're trying to show.

If I say Alt a WT, you'll notice that Excel, uh, here says, right Jonathan, you're trying to produce a data table.

We've got a couple of fields for the row input cell and the column input cell, and there's no opportunity here to tell Excel what you're trying to output.

And that's because it already knows.

So if as long as you've dynamically linked the profit number or whatever it is you're trying to show to the top of this table, then it'll understand what you're trying to show, but it doesn't understand what you're trying to change.

Now, first thing it says is, do we have a row of inputs here? We don't have one. So I'm gonna click or tab down to the next field, which says column input cell.

What Excel means is, Jonathan, we've got a, a column here of numbers.

Um, and they, they, you can see there the, the price, um, that should, uh, we should apply to the model and Excel's simply saying, if I take these numbers, I can take three 50.

Where do you want me to insert that? Where am I gonna to input that? Well, I want the price to be input into C five.

So where it says column input cell, I'm going to click on C five. Now when I click, okay, let's do that.

Now what Excel will do is it'll take three 50, it'll chuck it into the model, recalculate the profit.

I presume it's 4, 4, 3, 4, 5, 0.

We can check that because we can go up to the model and change this to 3.5 and yeah, it's 4, 3, 4, 5, 0.

Um, let's put it back Sorl Z.

And so what Excel will do is it will variously grab these prices, chuck 'em into the model and rerun the profit.

And so that's pretty straightforward.

I wonder if we can make it more complicated than that.

Well, if we go down to the next example, which is still a relatively simple example, I can uh, let's go and highlight in yellow, I'm gonna highlight in yellow those three cells along the top because this is one input still. We're gonna change the price, but it's multi output.

I'm gonna show you just the formulas as I insert things.

So I want Excel to show the revenue.

I would need to link that into the model.

So we're gonna go and grab the revenue for the model.

We're gonna get Excel to show the variable cost and get Excel to show the profits. We're just trying to make it slightly more sophisticated.

Again, I'm gonna grab the entire table.

You might notice that the top of the table here is the, those cells I've made yellow.

So the things we're trying to show, so Excel knows what we're gonna output already.

I'm gonna use the shortcut we talked about up here, which is alt A for data over the side here, W is what if.

So w for what if.

And here T for data table, Excel says Jonathan, uh, row input cell are these inputs. Do you want me to change something in the model? Do you want me to put these into the model? I don't. They're actually my outputs.

So we're gonna skip over that and go for the column input cell.

It's the same thing. We're gonna go up to the model and we're gonna click on the price, which is in C five.

If I click okay, what Excel will do is it'll show the different revenues, variable costs and profit numbers for that variation in price.

Of course, there's no change in the variable cost because we're not changing the cost structure.

And maybe that's interesting. Maybe it would be useful for us to be changing the price and changing the unit cost and uh, see how that affects the profit of the uh, business.

And so we're gonna go to the final data table here.

I'm gonna color this in yellow again, just to draw your eye to it.

And I'm gonna just show you above the formula.

So because we're going to use unit cost inputs and price inputs, we've got two variables in this data table.

We're sort of running outta space in the data table to show the output.

So that's gonna get relegated to the top left.

So in the top left, I'm gonna say course, and we're gonna show, I think profit here.

So let's click on profit and hit enter.

And I'm gonna want some unit costs and I'm gonna want some prices.

And so at the moment in the model, the kind of midpoint for the unit cost is 45 cents and the midpoint for the price is $4.

So for the unit cost, if I say 0.450, um, sorry equals 0.45 even.

And um, I think what I'm gonna do is I'm gonna to the left, I'm gonna take that 0.45 and I'm gonna minus 0.05 from that.

So we get 0.4, I'm gonna copy and paste that and to the right, I'm gonna take that 0.45 and I'm gonna add 0.05 and we can control all that out on the left hand side, we've got the lowest possible cost.

So I think I want the top left corner to be the best possible outcome here, um, for the price. Then the, if we go back up to the model, the price, the midpoint for the price is $4.

So I'm gonna start with $4 and I think I'm gonna increase the price as we go up.

So if I say equals, gonna go and grab that $4 and add 0.5 to that, let's copy and paste that above.

And if I go below, I'm gonna take that four and I'm gonna deduct 0.5 from that and I'm gonna copy that down.

Now by doing it that way, laying it out, laying it out in that way, uh, the top left has got the lowest unit cost and the highest price.

So this would be the highest possible profit number.

If you look on the bottom right of the table, we've got the highest costs and the lowest price.

And so this would be the lowest profit number.

Okay, let's grab again the entire table.

You notice I'm grabbing the columns.

Excel's gonna know what we're trying to output because we've already linked that into the model.

On the top left, the keyboard shortcut you might remember is alt a WT.

So I'm gonna say alt a, w and T for data table.

And Excel says, Jonathan, you've got a row of headings here.

Yes I do. Well, I could take those numbers and I can go and push them into the model.

Where would they go? Well, those row of headings are the unit costs.

So I'm gonna point you excel towards the unit costs, which is in C seven.

And if we click with a mouse or use tab to go down to the column input cell, we have a column of headings here.

That's the price. So I want you excel to take these numbers and I want you to insert 'em into the price, which is at the top of the model.

It's at C 11. If I hit enter.

Now what Excel should do is it should show me all the various profit numbers for uh, different unit costs and different prices.

And if we look in the very middle, I'm just gonna make that bolt in the very middle.

We've got unit cost of 45 cents, price of $4 profit, 57, 9 50.

And that's exactly what we've got in our model. We've got unit cost Of 45 cents, we have price of $4, and we've got profit of 57, 9 50.

Just to check that this actually works.

What we could do is we could change the unit cost of 35 cents and we could change the price to $5.

So let's do that. Let's change the price up here to $5 and the unit costs to 0.35 and we get the, uh, uh, we get here the, the highest possible profit number.

So the highest possible profit number is 89 8 50.

And that is the number I'm getting in the model. 89, 8 50.

So we know that this is all working, but there is a problem.

And that's really the focus of, you know, where we're getting to on the session.

And the problem is that, um, there, there are other variables that I might want to change.

So we've explored changes in unit costs and changing in prices to see how good the position could get, but could it get even better? It might want to think about maybe changes in demand, all fixed costs, but let's stick with one additional variable. Let's say changes in demand.

Can we accommodate that in our data table? Um, functionality in Excel, if I select some random cells, alt a, w and t, we can't, we can't accommodate three variables in a data table 'cause there are only two input fields.

What we'd need to do is find a workaround for that.

And this is quite cool.

So we're gonna do, um, we next example.

If I can ask you guys to go to the three input tables left, right sheet, that would be great.

And what we have is, and I'm gonna zoom in on this, we actually have the same model and we've got the same numbers, but we wanna produce a data table that, um, has a bit more variety.

So we've got more than, uh, more than two um, variables in here.

And if you notice, we've got a sell for price and we've got a sell for cost slash demand.

Now if you look at that sell, it's actually a, it's sort of a number.

Uh, we we're gonna maybe try and rip a couple of numbers out of there, but there isn't a number there.

Um, what we have is a text string.

There's a space in here and a slash and another and another space.

We've actually got a text string.

And that text string just outta interest has 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15 characters in it.

And that is important for what we're about to do. Okay? So I'm gonna use, uh, I wanna use a function in Excel called right and the right function. We're gonna point the right, right function at that cell and it's gonna start counting from the right hand side. And I want it to grab 1, 2, 3, 4, 5, 6, 7, 8 characters.

I want to grab that 29,000 demand and I wanna insert that there.

So I wanna kind of like rip that out.

And I, I wanna grab, uh, the unit cost and to get the unit cost, I'm gonna use the left function.

So why, why do I want to do that? Well, the reason I wanna do it is I'm gonna create some headings here in the data table, um, that vary both the unit cost and the demand.

So they're gonna do both.

And then down the side we vary the price as always and we're gonna have Excel, um, whatever we have in these cells. We're gonna have Excel, pull the, the, the text string out of here, chuck it in, uh, this cell.

And then we're gonna have a bit of formula work that basically rips a couple of numbers outta that and reruns the profit.

And uh, so let's, let's have a, let's have a go at doing that now.

Now I'm gonna go to the side, I'm gonna break the calculation down first I think.

Um, and then we'll kind of nest it all together, uh, and build, build it properly.

I'm gonna show you my formulas as I, as I build this, okay? So the first thing we're gonna do, I'm gonna try and extract this 29,000.

Now that 29,000, uh, is separated from the 0.45 by this slash So the first thing I'm gonna say is in the text string, where is that slash where is the separator in the text string? It's gonna be useful as a marker to break out the right and the left functions.

So I'm gonna go just random cell, I'm gonna go into cell G six and I'm gonna say equals and I'm gonna use a function called find FIND and it says returns the starting position of one text string within another string.

So if I hit tab two, open the bracket, the first thing Excel says is what text do you want me to look for, Jonathan? Well this text has to be wrapped in double quotes, so I'm gonna open double quote slash close double quote.

So go and look for slash comma and Excel will say within text like where are we looking for that? So I'm gonna go and point to there.

So apparently from the left, the slash is the sixth character.

I think we can probably prove that.

So if I look at this texturing, what, 1, 2, 3, 4, 5, 6.

Okay, that's great, but I wanna use the right function.

So what I really wanna do is count all the characters that come after that slash what I'm gonna need to do is figure out the entire length of the text string and then subtract six from it. You know, if you think about it, I think we decided earlier the texturing was 15 characters and the slash appears at the at six.

So 15 minus six really is what we want to do. Okay? So the question is, well how long is the text string? And we need Excel to sort that for us.

So I'm gonna say equals LEN, I'm gonna hit tab.

And if we really simple just says, point me at some text Jonathan, and I'll tell you how many characters are in there. There are 15 and what I wanna do is take that 15 and knock off the first six.

So I'm gonna say equals really simple, 15 minus six is nine.

Now I'm gonna, finally, I'm gonna use the, the, the right function, I'm gonna say equals R-I-G-H-T returns a specified number of characters from the end of the string.

That's it. Tab Excel says, Hey Jonathan, show me the text string.

So we're gonna arrow it up to that comma and it says, how many characters from the right do you want me to count? Now I'm almost certainly gonna be slightly wrong here, but I'm gonna go for it anyway.

So I'm gonna arrow up to the nine that we've got here and I've got 29,000.

But, but hang on a minute.

From the right it's gonna grab nine characters.

And if you look at this, we've got 1, 2, 3, 4, 5, 6, 7, 8, 9.

I really don't wanna get that space at the very beginning.

So I'm gonna do something just fairly straightforward.

I'm gonna say Excel, let's use the right function.

Let's look at cell C six from the right count, maybe not nine cells, I'm gonna subtract one from that.

So count eight cells from the right, gonna use the same idea to extract the cost, but the unit cost is actually much easier to get.

So I'm gonna say equals LEFT, I'm gonna use the left function and hit tab and Excel says, Jonathan, show me the text.

So I'm gonna arrow up to that cell.

Now I want the zero, the period, the four and the five.

So I want four characters from the left.

I'm gonna say comma, I'm gonna arrow up to the position of that slash but I don't want it to grab the slash character or the zero.

So I don't want the sixth or the fifth character.

What I want is just the first four.

So from that I'm gonna deduct two, okay? And I've got 0.45.

Now what we could do is we could feed those numbers into the model, but it might be a little more fun for us to just build the, you know, the whole formula including those numbers.

So I'm gonna try and put this all together.

I'm gonna say equals R-I-G-H-T, right? And Excel says right of what? Where's your text? I'm gonna arrow up to C six comma and Excel will say right from the right, how many cells am I gonna account? Well, it's basically, wasn't it like 15 cells minus six cells minus one.

Um, that was really what we were using to get to that, the 15 cells.

I was gonna use the Len function, so Len of that bracket, but from 15, I don't want to count the whole lot obviously.

Uh, I'm gonna subtract the six and to get the six, I'm gonna say minus.

And I'm gonna say find, and we're gonna look for open quote slash close quote.

We comma in that text.

So at the moment that's 15 minus six, but I don't want you to grab the space.

So we're just gonna subtract at one from that and we're gonna close a bracket on that and hit enter. And that's our 29,000.

For the unit cost, I think it's much easier.

We're gonna say equal LEF we're and hit tab, we're gonna point at cell C six comma, Excel says how many characters in the left? Well, we're going to, uh, say find.

So we're gonna grab the find function, we're gonna look for that slash character in that cell.

C six. Let's close the bracket and minus one, uh, minus two, I think from that we said, and we get 0.45.

Does this really work well, if we figure out what we think the revenue is, let's just copy this down.

The revenue must be the product of the price and the demand seems to work.

The variable cost must be the product of the unit cost and the demand seems to work.

The profit must be the revenue minus the variable cost minus the fixed cost.

And it's a familiar 57, 9 50.

Gonna color that in yellow and also gonna go further down in the model and color this in yellow, let's feed that in, okay? And maybe just show the formula as well would be, would be useful.

There we go. Okay.

Now the idea is that, um, if I grab the cell here, it's a 0.45 space slash space, 29,000, that's a text string.

If I go to my headings here in the data table, I'm gonna say alt HVSV, which is paste as values, gonna paste those as values.

And we've got demand of 29,000.

But I'd quite like to change the unit cost.

This is gonna take me a moment, it's just a little bit, you know, uh, it's a little bit clunky there, but I'm gonna change from N 0.45 to N 0.4, and then we're gonna change that to N 0.35.

The demand is staying the same to the right hand side of that midpoint.

I'm gonna change to N 0.50 and N 0.55.

And uh, and so the idea is that if we grab something like, uh, N 0.35, 29,000, the data table function will grab that number and it will chuck it into cell C six. And then My formulas will rip that into two constituent parts.

It will rip out the 29,000, it rip out the 45 cents, and then the model will kind of combine those back together again to build, um, the profit number.

So, but that's not that revolutionary because you're still only changing the cost on the top and the price.

So the really cool bit is that if I copy those numbers and paste them over here, it's a bit dark.

So it's difficult to see with that dark blue. But if I grab my mouse, I'm gonna highlight those cells.

I'd like to take the 29,000 and change it to 30,000 because then the data table is changing the price, the unit cost and the demand.

And to do that, I'm gonna say control in H, which is find and replace.

I'm gonna change the 29 in the 29,000 for my selection to 30.

If I click replace all Excel says all done, we made five replacements.

Excel talks about itself in the plural. I always think about it as a singular, okay? Um, let's close that down.

And um, uh, you can see now that it's, it's changed them to 30,000.

So now what we could do, and this is really our final step, is if we grab the entire table, just as we've done before, exactly as we've done before, and I say alt a, w, and T exactly as we've done before, Excel would say, right, Jonathan, I've got a row of headings here.

I can grab each of these numbers all the way across.

Where do you want me to put them? And this is the sort of the key bit.

I want you to put them excel in C six because if you split them in C six, they get ripped apart and then they get combined back together to recalculate the profit.

So we can change the unit cost and uh, we can change the demand.

If we go down to the column and put Excel, there's nothing exciting happening here.

We have the prices, so we're gonna take the tele Excel to take those prices and chuck them into cell C five.

If I hit okay, then it will run all of those profit numbers.

So what's interesting is on the bottom right here, this is what we were doing in the first example on the bottom right, I'm saying that the, the best case scenario here is there, the best case scenario is you get 84, um, thousand 50 for profit.

And actually we are able now to say, well, there are potentially, there are other scenarios you might consider.

For example, if you sold more stuff, then maybe your profit could hit 88,500.

Okay? So what we've done is a bit of hopefully interesting formula work to overcome some of the inbuilt limitations in Excel.

Guys, I hope you found that interesting.

I hope you go forward and find it useful.

Um, on the desk, uh, we're running loads of these sessions and I'm, I'm involved in lots of these sessions.

So if you enjoyed the session, uh, I really hope to see you again in the future. Thanks ever so much guys. Have an incredible day. Thanks.

Bye now.