Here we've got three cases. Case 1 is the base, then Case 2 is the upside, Case 3 is the downside. We're going to need to choose one of them. In this example, we're gonna be using Case 1, but you might notice in cell B4, we've actually got a one written in the formula bar, but it's displayed as Case 1. We'll come back and we'll show that at the end.

Underneath it in rows 9, 10, and 11, we've got our three growth rates that we need to select from, but our base case is actually missing the first three years of growth rates. Luckily though, we've got the consensus estimate numbers, so what we'll do is we'll calculate the growth rates from them. Let's do that now. So to calculate my base case growth rate in the first year, I'll take my consensus estimates and then divide that by the previous year's historical number. That will get me a figure something like 105%. If I then subtract the one, that will give me just the 5%, in this case, 5.3. I can then press the Shift button, hold the Shift button, then press the right arrow a couple of times, and now, to copy that to the right, I can press Control and then the letter R. Fantastic, I've now got my three growth rates that I want to select from. And I'm gonna use the INDEX function to do that selecting. So I write in equals INDEX open brackets, and the first thing it asks me is for an array or a table to choose from. I'm going to select the three cells above, those three growth rates, 5.3, 6.3, and 4.3%. I'll then press the comma, although depending on where you are, you may have to press a semicolon, and it now asks me for a row num, a row number. This is asking me which of the cases do I want. Do I want the first row, the base case, or the second, the upside, or the third, the downside? I'm going to choose the first row. And if we remember, cell B4, even though it says Case 1, that's really just a number one. I want to lock onto that. Because if I copy this to the right, I don't want that B4 to change to C4 and D4 and that kind of thing. So making sure that I've got my cursor blinking somewhere around the B4, I'm going to press the F4 button on my keyboard, and that puts those dollar signs in there and locks it. The last thing in the INDEX function is the column number. This is an optional item. I don't want a column number here, so I'm just going to close the brackets and press Enter. I now want to select the cells to the right and then copy it to the right. So to select them, I'll press the Shift button, then press the right arrow a few times, and once I get to the end, I'll press Control and letter R to copy to the right. Fantastic. Now, with EBITDA, we need to do very much the same thing. The first thing we need is the EBITDA margin in the base case. We haven't been given this, but we do have the EBITDA consensus estimates. Let's use the consensus estimates to work out the margin from them. So in the first year, I'll take EBITDA, divide that by the revenue figure. That's already given me my EBITDA margin, so I can just press Enter, and my first margin's 20.2%. Let's copy that to the right, so Shift, right arrow, right arrow, and Control + R. I want to do my INDEX function again. It's very similar to the first one we did with revenue. So I type in equals INDEX open brackets. My array is the first three cells, but then the row number that I want to choose, that's up in B4.

Don't forget, we need to lock onto that. So with my cursor blinking in the B4, I press the F4 button on my keyboard to get those dollar signs, or alternatively, you can just type those dollar signs in.

That's given me 20.2%, so it's chosen the base case, which is fantastic. I can then copy that to the right, and my numbers are all filled in.

There is one more use of INDEX we could use here. If I go back up to the top left-hand corner, I can see that Case 1 says Base underneath it, and Case 2 says Upside, et cetera. I'd really like to change cell B5 so that it says Base underneath the words Case 1.

I could use an INDEX function again to help me do this. So I type in the INDEX function open brackets. It asks me for the array. My array this time is from C5 to E5. It then asks me, which row number would you like within that? That's a bit of a crazy question because I've only got one row. I can't choose row two or three here, so I can just hard-code in the number one. We don't normally hard-code into formulas, but in this case, it's absolutely fine. Lastly, then it asks me for the column number, and I have to decide, do I want column one or two or three? I'm going to get that information from cell B4. And I could lock onto that if I was copying this anywhere, but this cell is standalone, so I don't even need to lock it with the dollar signs. Press Enter and Base appears. Let's just change B4. Let's change that to a two. Let's see what happens. Yes, and it changes to Upside. Great. The last thing then was, how is B4 showing me Case 1? Well, I'm gonna go into the ribbon to help me here. I'm gonna go to the Home ribbon, and then, for the Number section, I want to press the down arrow, and I want to then expand that out. You may not have to press that down arrow depending on how big your screen is. So I want to expand this out by pressing this arrow here. And what I can see here is I'm on the Number tab, Custom, and the Type has now got that word Case written in. The number zero will be replaced with whatever number I type in, but whatever number is typed in will also then have the word Case written in front of it. If I wanted to, I could change that to the word Scenario. Press OK, and it now says Scenario 1 or Scenario 2 or Scenario 3. I'm gonna undo that because I like it saying Case 1.