Well welcome everybody.

Happy Friday. My name is Alistair Matchett.

I was a banker at JP Morgan and I did oil and gas deals and financial institution transactions.

Then I worked in private equity for a company called three i a fund called three I in the UK and then went into the trading business and we teach all across Wall Street.

So we've got a short, sharp session on data tables today and our main aim is to go through three variable data tables.

Yes, they do exist, however, I'll be honest with you, there's a little bit of a workaround and I'm gonna show you three solutions to that problem where, where you can have three variable data tables.

But first I'm gonna give you a little treat.

I'm gonna give you a little example of how you can link the main assumptions and the variable of data table to the actual main assumptions in the model.

'cause I think a lot of people dunno how to do that.

And it's a really, really helpful work around during the session please ask questions through the q and a.

I'll keep an eye on it and I'm happy to answer any questions that you may have.

Also, what I'm going to do is I'm going to give you a copy of the files I'm gonna be using so you can work alongside me if you'd like to. There's no um, requirement for you to do that, but I'm just gonna share with you the empty version of the files and I'm gonna put it in the chat.

Okay, well actually need to do it for everyone. Apologies.

Let me just put it into the chat again. There we go.

So you should sit in the chat if you want to download the file.

Um, you can do that.

I will give you the answer files in the end of the session as well.

Okay, so let's get started.

So the first thing to do is go through the mechanics of a data table and this issue of connecting the assumptions in the model to the variables in the data table.

I'm not gonna go through a simple data table or standard requirements of data table. What I want to show you is this little workaround as a little treat before we get onto three variable data tables. And we should get everything done within 30 minutes.

The first issue, if you look on the screen right now, and I'm just gonna show you a slide example first is that we've got a TA data table down at the bottom here and you've got some variables across the top and that's one variable and you've got some variables across the left as well.

And in the model you've got your main assumptions here, but you'll notice you've got some what, what is called here copied cells. But I'm gonna call this when I do it for real clone assumptions.

And what's happening here is that the midpoints of the data table here and here are actually linked to the main assumptions in the model.

But, and this is a critical thing, the model doesn't use the main assumptions.

It uses these clone assumptions and the midpoints of the, and sorry the data table itself is calculated off these clone assumptions.

And all the clone assumptions is doing is referencing the original model assumptions.

Now let me explain what's going on here.

The reason why a lot of people will tell you don't link the midpoints of your data table variables to the assumptions in the model is because it creates a circular reference.

Now it doesn't flash up with a circular reference, but lemme explain what happens if you link the midpoint of your variable in the data table to the assumptions.

What excel will do is it will pick up the variable at the top of the data table, place it in the assumptions.

But if the variable in the data table is already referencing the assumptions, you create a kind of circuit and that means the data table will give you garbage.

What's the workaround? Well the workaround is actually using a kind of two stage assumption.

Having your main hardcoded assumptions here.

These assumptions are what the midpoints of the data table linked to, but the model and the calculation of the data table linked to the clone assumptions.

And the reason this works and a lot of people don't understand this, is that when you are doing data tables, you don't need to link the variables in a data table to hard numbers.

You can link the variables in a data table to formulas.

So for example, if you're doing a discounted cash evaluation, you can link the data table to your WAC calculation cell as opposed to the underlying components of wac.

And all Excel will do is destroy the formula first, run the data table with all the variables and then replace the formula when it's done.

Let's just see an example of this 'cause it's kind of the best way of explaining this is to do an example.

So let's go to Excel and what we've got here is got a little um, example of a day table.

Let me zoom in a little bit. There we go.

So the first thing I'm going to do is I'm gonna create these clone assumptions.

So these clone assumptions are here and I'm not gonna call them copper cells.

I'm gonna call these clone assumptions because they are assumptions but they are just formulas linking to the underlying hard numbers and it's these clone assumptions that the model will run from and the data table will calculate from.

Okay, the only thing that won't link to them is the midpoints of the data table.

So in this case what I'm going to do is I'm gonna do the model and the model needs to use the clone assumptions.

I noticed a few people have just come in.

So what I'm going to do is I'm just going to reload the file I'm working off so you can download it if you want to.

And the the files are in the chat rather than the question and answer.

So if you just open the chat, you should be able to see the files there.

So it was just had a question.

So I'm gonna do the model now and the model, I'm gonna calculate the revenue and I'm gonna tick the clone assumption for price times the demand.

Okay? So I'm using the clone assumptions to run the model and the variable costs.

I'm going to use the clone unit cost times the demand.

And then what I'm going to do is calculate the profit, which is just revenue minus the variable cost minus the fixed cost and I get the profit there.

So in this case we have a calculation using the clone assumptions and the demand.

Okay? So once you've got that we can now do the data table.

Okay, so let me just um, do the data table.

So in the top left I'm going to link the data table to the answer in my model.

There we go. And then what I'm going to do is I'm gonna show you that the data table is not linking to the clone assumptions, it's linking to the underlying hard numbers.

I'm gonna do the same with the price. There we go.

And I'm going to do the data table.

And if you're been in the business a long time like me, you can use the old guard shortcut for data tables ALT dt.

So alt dt.

And what that will do is it will pull up the data table.

So the row input cell, I'm not going to select the clone assumptions, I'm gonna select the underlying assumptions and the top of the data table relates to the top of that dialogue box.

And then the column input cell relates to the underlying assumptions, not the clone cell.

So what happens when I run this, the data table works now because the data table is calculating off the clone assumptions, but the midpoint in the reference are picking up the hard number assumptions.

There's effectively a break between these two when the data table is running and that means there'll be no circularity.

So watch this. If I now change the price from four to five, the data table, oh I'm getting a little bit of an error.

No, that's, I think that's, oh I'm getting a bit of an error there. Lemme just double check this is working and that's fine, that's fine.

Yeah. And have I done do al dt? That shouldn't happen. The rot input cell is that, oh sorry, row input cell is the clone assumption and the com column input cell is a clone assumption as well. Lemme just, there we go. Sorry, I just obviously had not done that correctly. Lemme just show you that. There we go.

So let me move that again.

So if I, so the data table must use the clone assumptions.

If I change this to four, you can see the data table updates, change it to five, the data table updates among those assumptions.

So now you have a data table that is keeping track of your underlying assumptions.

So the variance is always around the underlying assumptions in the model.

So that is how to fix that issue to do with data tables keeping up with the assumptions.

Okay, now the clones, can the clones be on a separate tab from the data table? I've been asked and the answer is um, no the clones cannot be on a separate tab.

However the assumptions can be.

So if I move this assumption now sometimes this breaks it when you first do this, but if I just move this to another page, you can put the assumptions on a different page but you can't put the clone assumptions on the different page.

Okay? Um, so that's how this works.

Yeah, but if you change, if you move the clone assumptions, then you would get an error.

Okay? So you can't move the clone assumptions.

Okay? Now, um, what I would like to do is now go onto the main event, which is three input data tables.

Okay? Now for three input data tables, there are actually gonna be three ways we're going to do this.

The first way is using text strings.

Okay? Now this is when you first see this, this actually is probably not the most efficient way of doing this, but I do it this way because this kind of gives you an understanding of how most of the methods actually work.

So I'm doing this method first because it's just more understandable.

In this case what we have is we've got a data table and we've got a series of assumptions of price that's pretty standard, but our assumptions on the along the top are actually text strings of two variables, unit cost and demand.

Now this is probably not the most efficient way of actually doing this, but it just un gets you to understand what's going on.

So we've got two assumptions here. We've got a price and we've got a text string, which is cost space slash space demand.

What we need to do for the model to run, 'cause the model can't run off a text string, we need to disaggregate that texturing into its component parts.

Now in the older versions of Excel, you have to use the lens function, the right function and the find function.

And in the answer that I'll upload at the end of the session, it does do those older functions but there is a newer function in Excel three six or office 3 6 5 which I want to show you.

So in this case what I'm gonna do is I'm gonna do text after and I'm gonna go up and pick up the text string and then comma, it's going to ask me what the delimiter del eliminator is when that's the kind of point at which it should be picking up text in that string.

So I'm gonna put open quotes and it's gonna be slash space hit enter.

Now when you first do that you'll find that if I just copy this to the right, I paste that in, you'll find it left aligns it, it still works as a number and I guess it's just 'cause it's pulling in what it thinks is text, but it will still actually work as a number.

So if I just take that times two, you can see it does.

So I just write aligned it and then what I'm going to do is I'm gonna use another text function, which is text before.

I'm gonna take the text string comma and then the delineator and the delineator here is gonna be changed slightly because I'm due space for slash close close parentheses and that pulls in the second, pulls out the second number.

So what we've done is we've taken that text string and we've disaggregated it.

Now I'm going to do the little model.

So my revenue is going to be the price we've got at the top times the demand and we've got the variable cost unit cost times the demand as well.

And then I've got my profit, which is just revenue minus the fixed costs minus the variable costs.

So here what we've got is a model that is working from that text string to numbers to the calculation.

Now you can probably see how this is gonna work because I'm now going to do the data table and in the top left hand corner I'm gonna pick up the profit and then I'm going to select the data table control shift down arrow and right arrow and then alt dt.

And in this case my top row of the data table, it's going to be the text string because you can see all those different text strings and then the column in itself it's going to be the price hit enter, ah, boom, oh my gosh, look at this, let's check it works.

So I've got my initial text string set 45 cents and 29,000 and my price sector four and I get 5, 7, 9 50.

And that's the correct answer.

So what you can do here is you can change these to let's say 35 and the data table will change.

Now you can probably look at this and say that's a real pain to have to go and change the texturing individually because actually if I'm doing this I just want to do a range or do um, 29,000 plus a thousand, et cetera.

How could you do that? Well there are two solutions.

One is I'm gonna show you on this sheet and then the next one is using an offset function and it has other dynamics.

Um, yeah, so be dynamic versus can you make row 16 values dynamic? Yeah, I'm just gonna show you that right now.

So, um, um, in a, in a, just give me a moment there, I'm gonna give you two ways in which you can do that.

So one way is you can kind of reverse the disaggregation.

So I know this sounds a bit kind of convoluted, but you can take the assumptions, reaggregate them, put them into the texturing, which then disaggregates them, lemme show you how you can do that.

So let's just say we wanted to have point, um, 0.35 and I'm just gonna increase the decimal to that and then I'm gonna have 29,000.

And then what I want to do is I'm just gonna make those blue and then I'm gonna make say that and say plus 0.1.

So now that is a, um, there's more and then here I'm do that plus a thousand.

I shouldn't really do a hard number, but I'm just gonna do that for now just for purposes of this example.

So I've got that. Now what I can do is I can con those two together.

So and if you've used concatenation, but ca concatenation is kind of like crafting in Excel.

So if I do equals and I go and pick up the first reference, which is the 0.35 and I may need to do this to do decimal places, but I can fix that in a moment.

I'm then going to dab a bit of glue down and a glue in crafting and excel is the amper sound.

And then I'm going to put open quotes space forward slash space, close quotes and then dab another bit of glue.

And then I'm going to go and pick up that second number.

Now here, um, oh it has actually done two dec places.

That's good. And then you should be able to copy that, right? And now you can see that you have a dynamic range based off those two values.

So we've taken the underlying values, we have concatenated them together to generate a texturing, and then that texturing then goes into the assumption runs, um, gets disaggregated to run the model, but allows a data table to run.

So that's the, that's, that's um, one way of making this a little bit easier.

The second way I'm gonna show you is through a choose function, okay? And this is a concatenation method, but if I go to the next tab now, and I'm gonna zoom in a bit, um, I'm gonna show you how to do the same thing but using an offset function.

So let me just explain how this works.

At the bottom here we've got a data table and we have a range of prices here and then we've got a range of numbers from one to 10.

And what the data table is going to do is going to sensitize the price and the number except the number is going to put these selection of cells into the model.

Now the, the issue with doing it this way is that you actually want the model to give your current answer.

So what we have to do is we have to do a little bit of a workaround, which is a little unintuitive and that's why I did the prior method first and then this method.

But I have seen this method used.

So the first thing we're going to do is we're going to do some calculations here.

So this is this area that I've selected on the screen is what the data table is going to be.

Um, um, the data is actually going to use and this is what we want the model to use if the data table is not running.

So these are the kind of data table assumptions and this is the default, I'll just call it default assumption.

I can type today. There we go. So I'm doing this.

So what I'm gonna do here for the unit cost is I'm gonna do equals offset I if the data table is running, it's going to first go to the reference for the unit cost, which is gonna start in cell C 22.

And the way the offset works is it's not a count, it's an offset.

So it says walk three paces west or sorry eastern this case.

So I'm gonna start there comma, I don't want to go down any rows and put zero and then comma again and the number of offsets which is going to be in eventually when this data table is running in cell C 14.

So I'm just gonna hit um, those prints there and it's zero.

But if you come up to here and type one, it's gonna pick 0.35, 2.4, 3.45, 4.50.

Let me just put my formula to the right just so you can see that.

And then for the demand I'm gonna do another offset offset and it's going to be next to the demand row comma zero rose down comma four offsets to the right to pick up the right assumption.

And in this case you can see if the data table is running and it's put four in the offset, it's going to use 0.5 as unit cost and 29,000 for the demand.

And that will just be momentarily while the data table is running.

It won't be constant just while the data table is running.

The problem with this of course is that actually we do want to have an answer in the model when the data table is not running.

And this is where a lot of people get a little bit confused because in the calculations what we need to do is we need to have if statements that will sense whether the data table is running or we want to use the default assumptions.

'cause otherwise if we are not running the data table, we'll just get zeros.

So what I'm gonna do first is in the price sell to an if statement, if the price for the data table assumption is zero, in other words there's nothing in there, the data table's not running, then just use the default assumption because we want the model to give us an answer.

If not, then what we want to do is use the number that the data table sensitivity is giving us.

Okay? So it's going to use four in this case because we've got nothing in the cells C 14 and C 15.

So there's nothing being picked out because we want the model to give us an answer.

And in the demand I'm gonna do something similar.

So that says if and I go down to the demand cell, if that is equal to zero, Then what I want to do is I want to pick up the default demand.

If not, I want to pick up what the data table is giving me.

Now I've actually realized I've just made a slight mistake here, this rather than zero it should be double quotations. There we go. Just double quotations, apologies, because there's, there's always going to be blank, there's never going to be any numbers in there.

Then for the unit cost, I'm gonna do another if statement that says if my unit cost, if that is equal to zero, then use the default assumptions to gimme an answer.

If not, use the data table number.

Now I know this is a bit odd because actually as as an Excel modeler you are never going to see the data table number.

And the reason for that is Excel works so quickly, it's like a nanosecond that that's going. You won't actually see it but you actually have to model it in so the excel can actually run the model.

The fixed cost, there's no nothing sensitizing that.

So I'm just going to pick up the number for the fixed cost and then I'll calculate the model.

So in this case for the revenue I'll take the demand multiplied by the price and then for the variable cost, I'm gonna take the unit cost times the demand and then I'll calculate the profit by taking the revenue minus the fixed cost minus the variable cost.

Now this means that the model is giving us an answer currently but when the offset's working, so if I put one in here and I put um four in the price, okay it will give me a slightly different number because it will be using 0.35 as unit cost 29,000 as the demand.

Okay? But we never actually see this 'cause these numbers are gonna come from the data table below.

Okay? So we are never actually see these working but Excel needs them 'cause otherwise it can't populate the data table.

Let's see this baby working.

So on the top left hand corner of the data table, I'm going to pull in the profit and then I'm going to select the data table, which includes the price and the column on the left and the offset numbers from one to 10 along the top of the data table.

And then alt dt, ALT dt.

And what we want to do here, the row input cell is going to be put into that offset cell in cell C 14 and the price is going to be put into the price cell in cell C 15.

So what will happen is that Excel will be putting a offset number in and a price and the offset number will drive the results in cells C 16 and 17 from the range of numbers in row 21 and row 22.

Fingers crossed that this works, it looks good.

So our current assumptions are 29045 cents and a price of $4 and that gives me 5 7 9 5 oh.

So this is a second way that you can use a hard number variables on the top of the data table.

Okay? Okay.

Alright, um, I think we are pretty much done.

So let me just recap what we have done in the session.

Firstly, we did data tables that have a workaround, which means the center point of view variables can link to the underlying assumptions using clone assumptions because data tables can work with formulas as well as hard numbers.

Then we did a three variable input table, firstly using a text string and then we made it more efficient by using concatenation.

So we concatenated to numbers together.

Then the texturing changed the texturing assumption, which then got de aggregated to run the model.

And then the third thing is we did the same method or the same idea of using hard number assumptions in the model.

But instead of using concatenation we just used the offset key to run the assumptions and we had to use a few if statements.

So probably personally I would like the concat, I prefer the concatenation method 'cause it's a bit simpler and you don't have to do all these if statements, but I've seen multiple versions.

So we are i'd half past, so unfortunately time is up.

But thank you very much for being here.

What I'm going to do right now, before I forget, let me just save this file.

Um, and I'm gonna use my little shortcut, um, beauty save.

Let me just move you down.

Alt S Alt s oh get into Excel Alt S and go to worksheet control.

And what I'm going to do is just activate this so when I save it, there we go.

It goes to the very front page, which is great and I'll save this as a new name.

Um, I'm gonna save it in downloads 'cause my Excel got a little and I'm just gonna do this as 5:00 PM and I'll check this as class.

So what I'm gonna save here is I'm gonna save the file and this is going to be in the chat function.

It's going to be my answer.

Lemme just try that again. Hold on one second.

Oh may need to close that down.

Let me just do that right now.

Yeah, I think it was getting upset 'cause I didn't close it down. There we go. That's the answer from today.

But I'm also going to give you the answer solution as well.

'cause the answer solution has a, um, a, a fourth method of doing this using a um, um, using a pivot table.

Okay, so a few people have said they can't see the chat, so what I will do is I will get these emailed to everybody who attended class.

So just watch for your email.

Um, so I will make sure that everybody, uh, everybody gets the files, um, by email as well.

Um, one very last thing next week on Friday we are doing chat GBT prompting and I promise you there are some golden nuggets in there that everybody's using chat DP to create text content.

But there's some really, really beautiful ways in which you can just do formatting and you can take headings out of documents or there's just some really, really juicy stuff.

You will love it. Anyhow, thank you very much indeed for um, attending.