What are scenarios and how to use the INDEX function to do scenarios.

Well, onscreen, we've got three growth rates for revenue. We've got a base case, an upside case, and a downside case. These are scenarios and scenarios allow modelers to see different outcomes given changes in events, both favorable and unfavorable. We've got three different numbers we could choose from here. We could have revenue growth base case of 5.3%, an upside of 6.3, and a downside of 4.3. In row 12, the growth rate we've actually selected is 5.3% and we can see that that's the base case or the first of our scenarios. But how did we select that first case? How did the 5.3 end up in row 12? Well, in rows 4 and 5, what we've got here is a scenario picker. I can see in columns C, D, and E, I've got Case 1 Base, Case 2 Upside, Case 3 Downside, but the scenario we're picking is in column B. I can see in B4, we've gone with case number one and that's an input that we would change. That has resulted in the word base appearing in B5 and it's resulted in 5.3% down in the bottom right corner. So how have we done that? How did the 5.3 actually end up there? Well, we've used the INDEX function and the INDEX function returns a value from a table or array. The table that we're gonna go for is in cells F9, F10, and F11.

Now, the syntax for the INDEX function has three parts within the brackets. We're only gonna use two of them. It firstly asks, where is your array or where's the table that you want to choose from? Well, that was the F9, F10, and F11. The next part is the row number, and it says, which row from your array do you want to use? Well, we want to use the first one, the 5.3%. So the formula that we'd write would be this. It would be =INDEX, the array is F9, colon F11, and then the row number, we've put that in B4. B4 is the number one, so we want the first case or the first row from the table. We didn't have to worry about the column number because we're just staying within the column that we're already using. The INDEX function is awesome. There are other functions we could use. We could use a LOOKUP, we could use a CHOOSE or an OFFSET or INDEX MATCH, but all them do very similar things to this. So scenarios allow modelers to quickly put different assumptions into models and see the outcomes.