Guys, good day.

My name's Gerald Kelly. Welcome to this session. Brought to you by Financial Edge training.

Today we're looking at sensitivity data tables.

Now we're going to be looking at using data tables. We're gonna do lots of examples with you.

We're going to look at one input, many outputs, and we're look at two inputs, one output, I think that's the most common, two inputs, one output.

And we'll look at common problems throughout.

Now the first thing to discuss is what does a data table do? Well, imagine you have a little model and has an output and that output is maybe profit and your boss has just said, Hey, but what if we change some of the variables that go in? Gerard, I'd like you to run 25 models with 25 sets of variables.

Oh, that's gonna, that's gonna ruin my day.

A data table can do it all for you.

Just have one table at the bottom of your model and it will give you 25 outputs, give you many more, give you 250 outputs if you want.

So we're going to do lots of examples of that.

Now what we need to get you guys is some materials for this course.

So if I just go put a link into the chats.

So guys, if you go into the Zoom chat, there is a link to our website right there.

If you click on that, that will take you to the website and then we'll download some materials from there.

Okay, here it is opening up on my screen here.

Now it says coming soon, sensitivity data tapes and then Nvidia, we're not doing Nvidia, we're doing data tables.

If I scroll down, there are the files and it's just the first one I want.

So I'll give you guys a quick five seconds.

Can you guys please download that file and open it up? So I, fantastic guys, can someone just write in the chat and just say, yes, we've got it Gerard, or No problem or working fine, or guys can you just jump in the chat please and so on. Just say, yeah, yeah, it's all working fine. Say quick. Hello to me.

Cool, thank you. There we go. Lots of people replied. Thank you very much guys. Awesome. Cool.

We're gonna go straight into that file and up there it is.

We're going to start in the simple data tables tab.

Okay, let's have a quick look at what we've got at the top here.

We have a simple sales model.

We've got price per unit of $4.

We've got demand for units, 29,000 units and I need to come up with my revenue.

Well that's relatively easy.

I can take the price for one unit, multiply it by the number of units demanded and I've got my revenue 116,000 great.

But I also need to work up my variable cost.

So we just need this little model, just a teeny tiny model. This will help us out. The variable cost is going to be 45 cents per unit, 45 cents.

And I'm going to multiply that by the number of units demanded 29,000.

So I've almost worked out my profit now and my profit will be my revenue minus the variable cost, but also minus one other item.

I'm also going subtract the fixed costs of 45,000.

Now you might be saying to me, Gerard, this is all very random.

Why do I need this? I came here for data tables and we're doing a random little sales model.

Well, this is the kind of very simple model that my boss might now say, Hey Jared, could we just change some of the inputs? Jared, I'd like you to change that $4.

I'd like you to change it to $3 50, 3 75, 4 4 25 and four 50.

And the boss then says, Gerald, go and create five models, five files, save them in five folders. And this is ridiculous.

Data tables are gonna save you doing all that work.

What we're going to do is we are going to have our desired output in this cell here.

Now the output I would like to see change is the profit.

The boss has said to me, if we change the price, what happens to the profit? Okay, so we need to keep an eye on the profit.

So that has to go.

So we're now getting into mechanics of data savers here that has to go in this cell here.

C 14, I'll press equals and I link up to C 11.

There it is. Great. Now after this we're going to have a little kind of gremlin that lives in Excel Gremlin who does all the work in the background powering Excel.

We need to give Excel's gremlin some instructions.

So what I need to do is I need to select all of these new input cells and my new output and the white cells.

So can you guys please select those cells? And what we're now gonna do is we're gonna tell the Excel gremlin what to do.

So I go up to the data ribbon with my mouse and on the right hand side in the forecast section is this what if analysis.

I click on that and finally it says data table.

In just a minute. I'll tell you the shortcut to get there. There's two shortcuts to get there, but for now, let's just use our mouse.

And this dialogue box pops up.

So SOL's Gremlin says to me, Jerry J or Jared, do you have any inputs which are in a row? And I have a look at my table and I don't have any inputs in a row.

A row would be going out to the right side like this.

We have inputs which are in a column.

So we move down to the next box and I've clicked into that white box and Excel's gremlin says, Jared, do you have any inputs in the column? I do. So Excel's gremlin says to me, Hey Jared, which cell shall I put the column inputs into so that three 50 is gonna go up and be put into cell C five.

That's the important one.

So I'm gonna click on C five and I'm gonna say common problem number one is people clicking on the wrong box.

Okay? Common problem number one.

We're gonna see an an example later on.

Very easy to get these the wrong way around.

But anyway, all I need to do now after I press, uh, okay, is Excel's Gremlin will take that three 50.

Excel's gremlin will delete that four.

So you'll have a blank cell and replace it with the three 50, which will then create a new profit and a new profit will spit out underneath.

So that three 50 there, we'll go into that and that, oh, and that will then create a new profit to a bit here.

So let's do it guys. Let's press, okay, amazing.

And our five scenarios have now been done.

I've got five new profits each with a different um, input of price.

It's worth beautifully.

Now top tip number one, the original price of $4.

Have that in your data table somewhere because now you can check ah, 57, 9 50, yep. 57, 9 50 and 57. Nine 50.

It acts as a check.

So the steps we've been through so far, let's just write 'em out.

We build a model, we did that up here.

Rates, we then had to link the output from that model down into our data table.

So output from model goes into the data table done.

And then step three, we had to decide how many inputs we were gonna have.

We were gonna have one input.

So one input in our example that was price.

We then had to decide if that was gonna go in a column or a row.

We decided it was going to be in a column, but it could have been in a in a row.

We then selected the data table and we then clicked on the data ribbon.

What if analysis and then data table.

And in our case a column input cell was C five.

Okay? So we've got lovely instructions there to take us through.

Now if you've got any questions guys jump on the chat.

Chat and zoom. Ask me a question more than happy to answer you guys.

As long as there's no questions appearing though, I'm gonna carry on now that was one input being price and one output being profit.

If I scroll down to the next one, we've still got our one input being the price, but we've now got multiple outputs.

We're going to have revenue, variable costs and profits.

I know that I need these orange cells here to link up to my model.

I'm gonna get rid of that orange sell there profit's. The easy one, I know where that's gonna link to.

That links up to the model C 11 and I say the easy one 'cause we did it in the previous example, But I still need to work out where the variable costs and the revenue are going to link to.

So let's do the revenue.

I press equals and I see my revenue. There it is, it's in C nine as my variable costs.

Ah, I can see that as well. It's in C 10. Great.

Now we just follow the steps as before.

So we built a model output from the model goes in the data table done, we have one input price.

Great. We decide it's in a column or a row.

It's in a column. Great. Now we select the data table.

So let's do that. I select B 23.

Include all of those inputs on the left, include those orange outputs.

I now go to the data ribbon.

What if data, what if a data table, if you are someone who likes their shortcuts, you can use the accelerator keys.

You can get that by pressing the alt button.

So alt and then an A appears above data or below data.

And then what if analysis A W appears.

And then for data table A T appears.

So alt A WT, but you can also do alt dt.

It's old shortcuts.

So we've got here Excels gremlin pipes up.

Jared, I've got a question. Do you have any inputs in a row? Jared, another question. Do you have any inputs in a column? Yes we do. That's the price.

So I'm gonna make sure I've clicked into the column input cell and I'm gonna link up to the price at the top.

What Excel's gremlin will do, it will take that three 50 and it will replace that $4 up here and we'll spit out lots of new numbers.

Let's press, okay, fantastic.

That's worked nicely.

Let's just check at the original price of $4 just here.

Did we get the same figures in orange here as we had the top? Yes, we've got the same figures. Great.

Now I want to do a little sense check.

When the price is going up, revenue goes up.

Yep, that's great. That works fine.

When the price is going up, the profit also goes up. That's great. The variable cost would normally be symptomatic of an error.

When you see the numbers not changing, that is very often assigned. Something's gone wrong in your model, you've linked, maybe it's the wrong sell up here, you've done something wrong, but in this case it's actually okay.

The reason is my price three 50 and 3 75 affects this sell here.

It doesn't affect the variable costs.

The variable costs get rid of some of this color.

The variable costs were just coming from these two cells.

Demand and unit cost.

So in this case, that's actually okay.

The fact those numbers haven't changed, that is completely fine.

Cool. So we've done one input and multiple outputs.

Now we move on to what I think is the most common that we see in banking.

If you're a, if you're a banker, which is two inputs, one output.

So my first input is gonna be the price.

I want to take these prices from here and just copy them down.

So I'm going to select these five prices here, copy them and paste into these cells here.

And I just copied and pasted with control C, control V.

But what about the unit cost? Well, let's have a quick reminder.

We like the original figures to appear somewhere for price.

The original price was four.

We like to have the four. That's good.

So what's about the unit cost? My unit cost, the original unit cost was 45 cents.

I'd like to include that in my table, but massively common problem.

I see this all the time in data tables.

What you must not do, you must not do this, is you must not press equals and then link up to this 45.

You must not link up to that model of that. Don't do it, don't do it.

The reason is the XL gremlin in just a minute is going to empty out that cell, that 45 and replace it with this 45 cents.

But if they're linked together, this cell will now be a zero and you're putting the wrong numbers in.

So don't, don't link this cell here up the model instead guys, can you please type it in 0.45 and let's type a few others in 0.4, 0.35, 0.5 and 0.55. There we go.

Much easier to hard code the numbers.

So they are my two inputs, my two inputs in this case for price and unit cost.

Okay, I now need my one output and that has to fill this cell in the top left.

I need to decide which output to look at.

We could look at revenue. That change, that would be reasonable, but I'd like to go with profit.

It includes revenues, it includes costs, it includes everything.

This is the one cell that is allowed to link to the model cypress equals and I'm gonna go up to my model and link to cell C 11 read.

Cool. Now if I could just have a look through my steps.

We've built model output, one input, we're going with two inputs. This time in our case we're going with a column and a row.

I now need to select the data table.

So I start in cell C 32 and I want to select all of these blue inputs on the left, all of the blue inputs along the top and then I go up to the data ribbon.

On the right hand side we have what if analysis and then data table or if you like your shortcuts, alt A WT or alt dt.

Cool. So my pop-up box appears the dialogue box and my XL gremlin says, Jared, do you have any row inputs? Yes, this time we do got unit costs.

So the XL gremlin says, where shall I put these blue numbers? Where shall I put them? And I say, XL gremlin, go up and put them into this cell here.

Then the gremlin says, Jared, do you have any inputs which are in a column? Yes I do. I have these blue numbers here.

So I click in the white box, I link up to C five.

Now common problem is to get those two the wrong way around.

So make sure you have your original unit cost of 45, your original price of four, and then check where they intersect that you get the right number.

Let's press. Okay.

Ah, Very good.

Let's just double check when the price is going up.

Ah, the profit goes up, profit goes up, a profit goes up.

Great. When the unit cost goes up to the right profit goes down, down, down, down. That makes sense. Lastly, our original number, the 0.45 and the four, I want to see where they intersect with each other.

So I'm gonna make this pink, make that one pink as well.

And where they intersect, get the original profit.

That was worked really nicely.

Great guys, any questions for me? Jump in the chat if you've got any.

Silence is golden though. You can treat em mean. You can keep me keen. It's all right.

I'm absolutely fine with it. I'll keep an eye on the chat in case anything comes through.

Now we've mentioned already have your original figures in in in these red cells included in the data table.

So you can check the number in the middle In the last few minutes.

I want to talk about the most common error.

Okay, again, already mentioned it.

It was linking this 45 cents up to the model.

Now let's get rid of some of this color. Let's getting a bit colorful in here.

I am going to copy and paste those perfect numbers underneath here.

I'm now going to link that 45 cents to the model. This is an error, don't do this.

I'm gonna link that up to here.

Keep an eye on the numbers underneath and they all just change.

Here are the correct numbers, correct numbers 43, 4 50.

Unfortunately we've now got 44, 900.

It's the same as the 0.4, same as the 0.4.

It's the same as the column to the left.

This column is now not working at all because we linked this cell up to the top.

Get around all that issue. Just type the numbers in.

Hardcoded is by far and away the simplest, the easiest way to get around that error.

You can automate, you can automate, but makes for an awful lot of work.

Fantastic. So guys, that gets us through all that we want to do with our data tables. If you've got any questions for me, please do jump on the chat right now.

But guys, we are done. We are done.

You've done really, really well.

Thank you very much for coming along.

I hope you've enjoyed it and found it really useful.

We'll be here every Friday, run three times a day.

Do make sure that you are making the most that you can outta Felix.

Felix, there's so many more videos to be had here.

If I go down to, thanks George.

If I go down to Microsoft Office, okay and Excel, just have a look at all the other things that are in here everyday.

Excel. If you are using Excel but no one's ever trained, you start with that one.

It's a really, really good playlist and it just gives you all good habits, okay? Advanced formatting, Excel charts, scenarios, scenarios allow, enables you to do like best case, base case, worst case, stuff like that.

There's all kinds of goal seek all kinds of good stuff in here.

There's loads, loads more.

And of course if you want access to all of our knowledge, just come into the analyze section on the right hand side here. Type in an company you want to look at Nike, maybe on the valuation tab.

This is upstate information, the EV bridge for Nike.

We've got a comps builder for Nike. We've got FactSet.

FactSet provide the forecast earnings here for Nike and any other company you can think of.

Uh, fever treats. Go Fever Tree. There you go. There's Fever Tree, it's there as well. Uh, Keurig, Dr. Pepper, whatever you want. We got it. Apple.

Everyone's got Apple. There you go.

Make sure you type in Apple. So guys, do use Felix.

Have a great one. I'll see you on another session, Susan. Goodbye.