Hi there, I'm Dan Stockdale! So if you've watched Part 1 & 2 of this series on INDEX then welcome to the final part! If you haven't seen Part 1 & 2...then why not?! [My angry face :/ ] The links are in the description below. [Click them and all is forgiven :D ]
We started with the basics of INDEX. We then added some structure to our modelling by introducing a Timeline making use of its counters in our INDEX calculations. We're now going to add more structure with row counters and labelling to create some quite sophisticated and very dynamic calculations.
In the process we'll see how we build up a complex calculation. If you're interested in downloading this example file, see the link to my website in the description below. And as always do Like and Subscribe if you want to keep seeing more.
In this final example we're going to build in some of the techniques that we saw in the previous example but things are going to get a little bit more complex. We've got more structure in this example. We've still got the Timeline across the top which we saw before but now also some structure down the side, revolving around different gyms and capital expenditures in gyms. I'm going to explain as I go along, what's going on.
We'll start with these cells. You can see around here I've got the structure/formatting of a lot of what I'll be doing. I'm going to fill in the blanks or fill in the formula with how we do this. I'll start in this grey cell - we're just entering a 1 and then the cell below equals the cell above and add 1. We get a count of 1, 2, 3. I'll copy this down to these cells also. I'll do something similar here - enter a 1 and then equal the adjacent cell and add 1 to get 1, 2, 3 again.
This section is concerned with lists. I'll list the name of some gyms - it'll be Gym 1 and then I'll drag that down. So we've got Gym 1, 2 & 3. Then here I'll put the names of some capital expenditure the 1st is Equipment and the 2nd is Maintenance. We'll just have 2 for the moment. We'll grey out this cell, it kind of means there's not a 3rd to input here.
Here I'll start filling in the labelling of these cells. This is where we start to create some structure down the side. In these 3 blank cells we'll use an INDEX and we'll reference the capital expenditure list. You can see we're referencing all 3 cells as good practice. I'll anchor the rows, reference this count, and press ENTER. If I drag that down we've got Equipment, Maintenance, and then this just represents this blank.
I'll do similar in these cells below. I'll shift this INDEX array to the gym names. If I copy that down - we've got Gym 1, 2, 3. I'll do similar here - I'll add the gym names going across to the right. I'm just shifting the row coordinate to this 1. I'll copy across... we've got a problem here where I didn't anchor the columns in the INDEX - so I need to go back. If I double anchor this and then copy across I've got Gym 1, 2, 3. Now what's cool about this is I can just change these names and it'll update these structures.
So if I call that Dan's Gym, we see it changes the labelling. I'll undo though. [End of my gym empire :'( ] I'll put in here some pre-populated spend figures. Gym 1 spends 100k on Equipment. Gym 2 spends 200k Gym 1 spends 75k on Maintenance. Gym 3 spends 225k on Maintenance.
Next, these rows will be concerned with when these gyms actually spend this capital expenditure. These 9 cells here are going to be inputs which are a 1 to flag if there's spend in this year. They relate to these years up here - 2019 to 2021. I'll put some data validation into these cells. It'll be a whole number that's minimum 1 and maximum 1. This basically means that only a 1 can be entered. For this example I'll say Gym 1 spends in 2019 and 2021. Gym 2 just in 2020. Gym 3 spends in 2019 and 2021. Again, these relate to these years above and gyms down the side. So when I talk about structure at the top and down the side - that's what I'm talking about.
In this Spend Years Flag I'll put another INDEX. It'll be similar to the INDEX we saw in the previous example. It'll reference these 3 cells and we'll anchor the columns because we'll be copying this to the right. We won't anchor the rows because we'll be copying this down and want this array to shift down as we do. For my column coordinate I need to reference this 1 here, anchor the row and press ENTER. I copy this.
What we get are these Spend Year flags. 1 means there's spend in the year, 0 there's no spend. So these 1s have been plotted out in these cells to create flags which we'll be using in our calculations later. I'll also define the quarter that this spend actually happens within the year. So this spend can happen in Quarter 1, 2, 3 or 4. Again I'll use some data validation and this time add a list. So I go here to Lists and for my source I'll reference 1 to 4 up here. So this is Quarter 1, 2, 3, 4. I press OK and now I'll copy this down. If I select Quarter 1 for Gym 1, Quarter 2 Gym 2 and Quarter 3 for Gym 3.
Note how I'm creating dynamic labelling in this next section. With these row counters and INDEX it can be done very quickly. And we're recycling formula - we're taking existing formula, copying and pasting, then slightly amending it.
Next I'll create more of these counters. This time I'll put a 1 in each of these. This will reference the 1st and add 1. Then I'll copy that down. We get these counters 1 and 2 - that will relate to these capital expenditures. Here I'll put this 1 to 3. I'll copy this over and this is going to relate to the gyms. Similarly to before, I'll take this and copy it down. This is again referencing the capital expenditure names list and this counter 1. I drag that down - we've now got Equipment, Equipment, Equipment, Maintenance, Maintenance, Maintenance. I'll take this, copy it here and shift that back over to the gyms. It's referencing this 1. I'll copy that down. We've got the name of Gyms 1, 2, 3, and then Gyms 1, 2, 3 repeated, as it's a different type of capital expenditure.
I'll use another INDEX, and then because this is Spend Quarter I'll reference these figures here. I'll anchor the rows, reference this 1 and press ENTER. This is going to be a Helper Cell in a later calculation. This is saying for Gym 1 we spend in Quarter 1, Gym 2 in Quarter 2 etc. Then it just repeats it - when we get Gym 1, 2, 3 again we get Quarter 1, 2, 3.
Now we move on to the final calculation - what it's all about. We're going to plot out these spend figures into the appropriate years and quarters. Now there's going to be a few parts to this calculation and we wouldn't normally just code it start to finish. We're going to be building it up in stages, testing components as we go. This is an example of formula construction.
I'll start with an INDEX and I'll reference this flag. I'll anchor just the rows and then I'll reference this 1 here. I anchor the column and press ENTER. I'm now going to copy this down and across and what we've done is effectively taken these flags and we've chucked them into these end calculations here.
For Gym 1 we've got this flag in the 1st year, not in the 2nd but we do in the 3rd, in the same way as above. And so on for Gyms 2 & 3 for both types of spend. Now I'm going take this formula we've just created, copy it, go to the cell above and put an apostrophe in and then paste it. That apostrophe at the start turns it into text. So I'm going to save that formula there and I'll get rid of this formula.
Now I'll put another INDEX in and with this INDEX I'll reference these spend figures above. So I'm referencing all 9 of those cells and I'll double anchor that - so anchor the columns and the rows. I need a row coordinate which is going to relate to this down the side - Equipment, Maintenance or this blank. For that I'll reference this 1 here and anchor the column. Then I need a column coordinate which is going to relate to Gym 1, 2 or 3. For that I'll reference the counter 1 here. Again I'll anchor the column, close the brackets and if I copy this across you can see for Equipment Gym 1 we get the 100k, Equipment Gym 2 we get 200k etc and that is essentially taking these inputs and plotting them out into these calculations.
Now I'll return to this formula, copy it, and then go into here and multiply it by the 1st INDEX. If I copy that formula down and across you can see we've plotted the 100k spend into the 1st and 3rd year because that's when we defined that this gym spends on capital expenditure. Gym 2 only spends in 2020, the 2nd year, and so you can see for Equipment Gym 2 we're spending 200k here and so it's plotted that into Year 2 here.
Now the only problem is we've got this repeating for each of these quarters and we've defined here that Gym 1 spends in Quarter 1, Gym 2 in Quarter 2 etc. So I need to add a last bit to this formula and that is going to be an IF function. I'm simply going to put IF - and it's going to be this quarter here - and I'm going to anchor the column if that figure there, the 1, equals this quarter counter - and I'm going to anchor the row number, the 12 then I put this INDEX. If not, I'll put 0, close that bracket, press ENTER and copy that down and across.
We can see this is Quarter 1 here, and so it is returning the INDEX we just produced, which gives us the 100k and that is repeated in Quarter 1 of Year 3, when their spend happens. So if I just take this formula away… What we've created here is a really dynamic model with different types of cost - I could change this to 1 million and it's going to update there. I could change this to 500k and we can see it's updated there and there we could change the years when spend happens so we get more spend in Year 2 here.
If I take that away you can see the 100k and 75k disappeared. Then I could change the quarters when this spend happens - so I could change this to 4 and you'll see that these figures just jumped across to Quarter 4. I could change it to 1 and they jump back to Quarter 1.
I can update the name of these gyms to, for example, Notting Hill and Chelsea...and wherever and we can see the labelling updates. And I could change this to Aircon...and the labelling here changes.
I can even go a little bit further and add a 3rd type of capital expenditure actually quite easily. If I select these rows and copy and paste them down here, then we can see this has automatically updated because it just references the cells above and adds a 1 and that has picked up this 0. If I replace that and call this Heating, then it's going to change these to Heating. Then I could enter new costs here - for example 20k - and you can see it's populated 20k. Change this to 40k and this to 10k and we've very quickly added in more costs.
So you can see I've really used the structure above - the counters - and figures down the side, in my calculations. So we can see that's being referenced there and that's being referenced there. I've used Helper Cells here and then I've used flags here as well.
And that is how we use INDEX in dynamic modelling. What's actually quite cool about this is of all those calculations the only thing I'm using is INDEX except for 1 calculation here where I've used an IF function at the start as well. So it shows it's actually really dynamic and it's really kind of absolutely essential for advanced modelling.
I think you'll agree - INDEX is super powerful in modelling! Check the link below to my website to download this example. I'm hoping to soon also have an online modelling course, so check for a link to that too if you're interested. In the meantime, if you haven't already, then I suggest you check out my MATCH and INDEX & MATCH videos. Thank you for watching!
