Okay guys. Welcome, welcome. My name's Gerard Kelly and welcome to this session brought to you by Financial Edge Training.

In today's session, we're going to be looking at data tables.

I've got lots of things I want to cover with you. They're on screen right now.

I want to look at one input, one output, one input, multiple outputs.

And the third one here, probably the most common one that we see in the world of work is the two inputs, one output, that's like the default data table that everyone's got to get good at.

I'll also look at some common problems or common pitfalls. I'll give you some top tips and then we'll definitely do conditional formatting.

And if we've got time, we'll try to get inputs to auto update. That's gonna take you to the next level.

Can you guys please download this file here where my mouse is moving right now? Just that one file. That's all we'll need.

The solution or full file is just below it.

However you need me to explain how to get to the solutions. So that's what I'm here for. Cool, let's get into it then.

So I've opened up my Excel file. Here it is.

And I want to start on the simple data tables tab.

Let's go there. Now what we've got is we've got a simple model here, really simple, really short, and we're going to calculate the profit and then we'd like to use a data table to say what if some of the inputs were different, but we need the model before we can do the data table.

There's only three cells to do though.

So let's have a look at revenue.

For my revenue, I've got a price per unit of four, and I'm going to multiply that by the demand for units 29,000 giving us revenue of 116,000.

Next up we're asked for the variable cost.

I'll press equals, I'm going to link up to that unit cost of 0.45 and I'll multiply that by the number of units.

That's the demand, 29,000.

And lastly, I need to calculate my profit.

Now you would initially think revenue minus variable cost and you're correct, but there's just one extra item we need to include and it's the fixed cost.

So pardon me, let's subtract off the variable cost and the fixed cost and we get to our profit.

Great, the model is done.

What I now want to do is I want to start doing some scenarios.

What if the price was 3.50? What if the price was 3.75? And so we've got that first scenario down here.

You might notice it says that there is one input and our input is going to be the price and then one output.

We're going to change that one input from four to 3.50, et cetera.

But what output do we want to change? I think in this model, the most important single number there is profit.

I'd love to see how the profit changes.

So in this data table, we're going to put profits always, always, always to the right hand side of the inputs and up one cell has to be in that cell.

So I'm gonna press equals and then I'll link to the profits.

Great.

So what we're now going to do is we're going to get Excel to put these prices here up into this cell up here and Excel will then spit out a new profit figure.

So can I get you guys please to select the same sales as me? You have to select the price sell and all of these blue prices underneath and then select your output profit on the right.

Now I like to imagine that it's kinda like a gremlin who lives in Excel and the gremlin is there going, Gerard, Gerard, Gerard, what do you want me to do? I've got no idea. So we're gonna tell the gremlin to create a data table.

Data tables can be found in the data ribbon over on the right hand side here.

But what if analysis? And at the bottom of that we've got data table, let's click on that and we get this dialogue box popup.

And my Excel gremlin is now asking me a question.

The Excel gremlin is saying, Gerard, Gerard, Gerard, do you have any inputs to in a row? I'm looking at my data table and no, I don't.

I only have inputs in a column that blew the blue cells.

So I'm gonna click into the next white cell.

But Excel's gremlin says, Gerard, Gerard, Gerard, do you have any inputs which are in a column? Yes, yes I do. It's right here.

The Excel gremlin now asks Gerard, the inputs that are in a column, what cell, what cell shall I put them into? Well, I'm gonna tell Excel, go up to cell C5.

I've just clicked on that. And what Excel's gremlin will do, it will delete out that four and it will replace it with the 3.50 and it will crank the model, spit out a new profit.

So let's press, okay, let's watch it do that. Amazing.

The model's done.

If the price is 3.50, we get a lower profit than 4.

If it's 3.75, we get a Middly profit.

If it's four, we get the original profit. Great.

Now it may not have worked for you.

You may be looking at the same number every time.

You may be looking at zeros.

So I know this can be disheartening.

There is a way around it.

If it's a particular area you've got some people's Excel is not set to automatic calculation.

It may instead be set to partial or manual.

So what you can do to force Excel to do that to to do the calculation is press F9.

If F9 is not working for you, then you can go up to the formula's ribbon.

And over on the right hand side here, I've got this little calculator and it's calculate now, but that's also just F9.

So few top tips.

If your Excel is not set to auto calculation, press F9.

That will help you out. If it's not helping you, then you've got something wrong in either your model or your data table.

So you'd have to start again.

Okay guys, that's the first one done.

Oh my God, that that only took us like seven minutes. That was really quick. So the next one we scroll down is now looking at one input.

Again, that's just the price. There we go.

But we're now going to have multiple outputs.

A little bit more common. This data table, our multiple outputs are going to be revenue and variable costs and profits.

Just like last time, I need to fill in a cell to the right of my inputs and above.

But this time we're gonna have three.

I need to link these orange cells up to the model.

So I'm going to press equals, scroll up, scroll up, scroll up, find my revenue.

Where's my rep? There it is in C9.

Great. That one's linked up. Variable cost.

I press equals, link that up to my model.

I've linked to cell C10.

And lastly the profit I press equals and I link up to C11.

That's the only thing that's really different.

The rest is going to be very similar to what we did before.

I'm going to select the table.

So I start on the word price, select the blue cells underneath and select those three output numbers.

Now I need to go up to the data ribbon again, I need to go to what if analysis and data table.

There are some shortcuts to get you there.

If you are on a Windows computer, you can press the alt button that gets you to your accelerator keys.

The data ribbon is A, and then W appears over what if analysis.

And then T. Alternatively, there are some older shortcuts that I remember using when I was like when I was young and they still work today.

And the only reason I mention it is because this shortcut is really easy to remember.

It's alt D T for data tables.

Such a good shortcut data tables is alt D T.

So I'm going to do now, so alt and then D T and I get my dialog box pop up if you on a Mac, don't worry about it, just go up to that data room and click through.

Now Excel's gremlin pops up again and it says, Gerard, Gerard, Gerard do you have any inputs in a row? We do not. I've still just got these blue inputs in a column.

So then Excel gremlin says, do you have any inputs in the column? Yep, I do. Which cell should the gremlin put them into? We're going to get Excel's gremlin to put them into C5 press.

Okay. Hey, and it's worked again. Fantastic.

This is looking really good.

I'm gonna bring my top tips down so we can read them.

Remember, if it hasn't quite worked for you, you might just need to press F9.

Now this is really useful. I can do some analysis here.

When the price is really low, I get a low revenue.

When the price is really low, I get a low profit. Great.

But now I see something a bit suspicious.

My variable cost, low price, no low variable cost.

That makes me feel a little suspicious.

Maybe my model's not working.

Maybe I've done something wrong.

I cry and I weep and I'm ugh. That's awful. I hate Excel.

Guys don't hate Excel. Love Excel.

Variable cost in our model is not linked to the price.

Why? Just scroll back up again, my variable cost here.

Let's get rid of some of this color, then we can see a bit better.

Get rid of all of them. There we go.

My variable cost here is only linking up to these two cells.

So price doesn't go into it, which means variable cost is not meant to change.

So this table is actually correct.

The variable cost is a bit redundant.

Should probably not have done it in the first place. Great.

So that is working out really nicely now looking really good.

So just that shortcut really quickly. That's writes it down to alt D T that got us to your data ribbon.

Then what if analysis and then to data tables.

Awesome. Now we come to the main event.

The main event is two inputs, one output.

And I'm gonna go so far as to say this is by far and away the most common data table that we see used for our two inputs.

We're going to stick with the price.

So I'll tell you what, let's just copy these prices we've got up here. Let's, let's select them, copy them with Ctrl C, select C33.

Let's paste with control V or command V if you wanna Mac.

That's looking good. That's my first input done.

My second input the unit costs.

I'm going to start with the original unit cost that we had in the model.

It was 45 or 0.45.

I'm gonna put that 0.45 in my central cell there.

And you might notice I've got the original unit cost there and I've got the original price there.

That is not by mistake.

I will come back and I'll explain why it's really important to do that in just a moment.

Now my unit costs to the right of this.

I'd like to see it go up.

So what I'm gonna do is I'm gonna press equals and I'm going to link to that 0.45.

I'll just add on 0.05, add on 5p or 5 cents, whatever.

And I can just drag that to the right or copy it to the right, useful to the left of the 45.

I want to start going down.

So let's first equals 0.45.

I want to go down, I want to subtract 0.05.

So sense or pence or whatever.

And I'm just going to copy control C goes to the left control V.

Great, okay, I've got my two inputs in.

So I'm gonna put a great big tick through my two inputs. That's great. But I now need my one output.

What was that one output that I cared about so much? I went back up to the top. What was the one that I really cared about? It was the profits. Yeah, I profit is everything.

So let's go to the top left hand corner and this is where your outputs has to be placed.

It can't be put anywhere else.

It has to be where the two things join. The two inputs join.

So I press equals and I've got to link to the model.

I can't just type in the profit. Got to link to the model.

So scroll up. C11, great.

My two inputs and my one output are all done.

I now just need to run the data table.

So I'm going to select that output, select all of those prices just there and select all of the unit costs in blue.

Pardon me, let me do that again. There we go.

Use your shortcuts alt D T if you're on a Windows computer.

If you're not, that's okay. Got data.

What if data table.

Now this time it's a bit different.

The Excel gremlin says Gerard, Gerard, Gerard, do you have any inputs which are in a row? Yes, we do. My unit costs 35, 40 pounds, 45 cents.

That's that's my row input.

So Excels gremlin says Gerard, Gerard, Gerard, which cell, which cell shall I put these numbers into? Ah, we want Excel to go and put them into C7.

And now Excel's gremlin says, Gerard, do you have any inputs which are in a column? Yes, the price, let's link the price up to C5.

I can now press. Okay. And it's worked amazing.

Excel winning for us and it's Friday. Amazing.

Now a couple of top tips.

Remember this is the main event at this data table. We need to make sure we're getting this right.

First top tip, have your original inputs that were used in your real model.

Up at the top, have the original input of 0.45 and for have them in the middle of the row and the column.

So have the original input numbers in the data table.

I said in particular, put them in the middle. So why? Why is that important? Well, the thing is, if I've got that 0.45 included in the data table and I've got this four included in the data table, then where they join, I should get the same profits as we had in the top left hand corner.

So this acts as a check, this acts as a check.

It's a bit like a balance sheet balancing.

I can see that the numbers are the same.

Makes me feel calm, makes me feel relaxed.

The numbers are working amazing.

If it's not working, it's a clear indicator that something's gone wrong and you should either delete all the numbers, start your data table again, or something has gone wrong in your model.

Now the other reason it's really good if you put that data table into a presentation, you take it to a client, you take it to your boss, whatever, your client or your boss will look at it and they'll check the middle, check the corner.

Yeah. And they now have some confidence in your work as well.

So it's a lovely little check, little check to include.

That's awesome. So let me get rid of some of this color very quickly.

Now my boss has just rung up and said Gerard I'd really like to understand which scenarios give us a profit above a certain level.

I'd like to have profits that are above 57,000.

Jared, can you make sure that only the scenarios above 57,000 are kind of highlighted to me? Well, Howard, we do this and the answer is conditional formatting. Conditional formatting is gonna be your savior here.

What I'd like you guys to do, let's select just the white cells, not the blue cells, but just these white cells here please.

And let's go do some conditional formatting.

Up at the top, I go to the home ribbon and in the style section I can see conditional formatting is here.

I'm going to click on the dropdown and I'd like to create a new rule.

Now I find the most difficult bit about conditional formatting, working out which of these to use.

So I like to work out in my head first I only want profits above 57,000. That's it. The number has to be greater than, so my second line item says, only format sells that contain.

Ooh, that might be it.

So let's click on that format only sells with a cell value greater than that's what we want.

Sell value is greater than now I'm gonna type my number in here, 57,000.

But instead I could link to a cell out here and I could type the 57,000 into the cell instead.

Next up, I'm going to format the cells that meet this rule.

I'd like to go with a lovely green, I think.

I think green means go bit bit, bit of paw patrol for you there.

Green means go. It means that we've got a really high profit.

Yeah, the project's looking good.

So I press okay now when I press, okay, it should highlight just the cells that meet our criteria.

And there it does fantastic.

I can see when I've got a higher unit cost where less likely to make the profits great.

And when we've got a lower price, we're less likely to make to to make the profit.

And now I can see specifically in green the scenarios that work for us.

Amazing. Really good.

Now a couple of other top tips when we link to cell.

So, hang on, hang on, hang on, hang on.

Let's do this one more time. When my Excel gremlin says which cells do you want to link to my row input cell, I linked up to C7.

These links have to be on the same sheets that you are on.

Now that can often be quite annoying because I've got a huge great big model over here and then I've got a lovely output sheet with beautiful data tables and a dashboard and things that the client will see.

How can I fix this? Well, the inputs that are currently in the model tab and are flowing down through the model, what you could have is you could just have those inputs hidden away just in the up in the corner of your data table tab and they link through to the model and they go down through the model and then spit out.

Okay? So that makes it much easier.

You can, you can work around these things, but these links that I'm doing now, they have to be on the same tab as the data table itself.

So let's just, right, there we go. Oh, it's worked out. It sets high, accidentally went a little bit too far to the right. Don't worry about that. Let's add these to my top tips.

So my input cells in the data Excel gremlin in the Gremlin dialogue box, they must be on the same tab or same sheet as the data table.

Fantastic, right? One more thing, one more thing. We've got about four minutes left. Okay? I'd love this. 45 here.

I'd love this to be linked up to my model, but beware this is very dangerous and it often doesn't work.

Imagine this, imagine this 45 here.

If I proceed calls, imagine that was linked up to here.

Please don't do this. Please don't do this. This is so wrong. Please don't do this.

If I link that up to there, what Excel's gremlin will do, it'll say, Hey Gerard, let's get rid of this. Let's do it. Let's show you this.

What Excel's gremlin will do, it'll say, do you have any inputs in a row? Yep. I'm gonna say to Excel's gremlin, delete out this cell here, empty out and replace it with the numbers down the bottom here.

But of course, if I delete this cell or Excel, gremlins deletes that cell, these numbers will all just go to zero.

So that's not gonna work. It's not gonna work at all.

So what can we do instead? Well, first of all, you must never, never, never, never, never link this cell up to the model.

You must never link this up to the model.

But what you can do is you can kind of get around it slightly.

I'm going to have this unit cost here, linking to the cell to the right.

So equals that there.

And then my variable cost here, I'll have that linking to this new 45 cents feels a bit random.

I know it feels a bit random. So that's linking to there and that's linking down to there. What I'm now going to do down here, I'm gonna make sure I get rid of that.

For starters, this cell here, I am going to link that, I'm going to link that to cell C7.

Okay? But I'm gonna make a really important distinction when I do my Excel gremlin dialogue box. Now let's do it alt D T, My row input cell must not, must not link to C7, but it can link to D7.

Excel's gremlin will empty out this cell.

It will temporarily be a zero, but C7, that 45 that will, that will persist, which means this 45 will persist.

Everything's gonna be okay.

My column inputs out, no issues there.

Just think up to where it was And Everything works.

So Gerard, why are you finishing on this? Why? Why is this the big reveal? I don't understand.

Well, imagine your boss comes to you and says, Gerard, Gerard, Gerard, can you please get rid of this 45? We don't like it anymore. We want to replace it with 0.44.

I have to update all my data tables so that 0.44 is in the data tables and I've got hundreds of them.

Ah, but watch, if you set it up, like I've set it up, five percenter.

Ah, The 44 updates, great.

The number in the top left profit is still the number in the middle.

Yes, winning at data tables.

And guys, that's it. That's a really short, sharp, but really good session in data tables.

If I could make a suggestion, if you're brand new to data tables and you want to be building these two input, one outputs, don't start by doing this, linking up to the model, okay? Get there, get there.

But baby steps, start by just hard coding the numbers in like we've done here.

And then, you know, maybe once you've done a hundred data tables, then look into this a bit more linking up.

But otherwise guys, we're done.

I hope that's been really useful to you.

A short, sharp, half an hour.

We're completely finished guys. Have a great rest of your day. Enjoy yourselves.

Don't work too hard. I'll see you on another session too.

Bye-bye.