We're going to incorporate some scenario analysis into our Burberry model and you can see that we've been given some scenarios on the following tab. That's the scenario's tab that we're going to use for this. And these scenarios are for the retail and wholesale segments and the licensing segment. If we scroll down we can see that the base case scenarios are just linked to our original forecasts whilst the downside scenarios or 1% below this and the upside scenarios are 1% above this. In reality this will be based on your knowledge and expectations for the company.

You can also see that we've been given a cell here which contains a scenario number this is going to be a crucial cell and I'm going to refer to this as our scenario cell. Now, let's go back to our segments tab and we'll start to build our scenarios into our model. Now for this we're going to demonstrate three Excel functions, which are commonly used for scenario analysis. That's the choose function, the offset function, and the index function and we'll demonstrate each of these in turn and we're going to use retail revenue growth to demonstrate this.

Let's start with the choose function. I'm going to start by typing that in here.

Now the choose function allows us to choose a scenario from a list of scenario inputs each separated by a comma the first argument needed for the choose function is the scenario number and that's my scenario cell. So if we scroll to the scenarios tab and then navigate to the scenario cell and then we're going to lock this cell reference so we can reuse the formula.

The next thing we need to do is to select a scenario inputs.

That's the downside scenario, the base case scenario and the upside scenario. When we hit enter we can see that we have the base case scenario pulling through into our model still. That's because our scenario cell is set to 2. So it is the second scenario in our list and that's the base case scenario.

If we wanted we could copy the formula to the right and the formula would roll forward appropriately but locking that scenario cell. However, I'm not going to do this. I'm going to move straight on to our next Excel function and that's the offset function. So I'm going to start by typing in the offset function.

Now the offset function works by telling Excel to find a cell a certain number of rows or columns away from a reference cell. Now the first argument needed is the reference cell and I'm going to take this from the scenarios tab. And it's going to be the blank cell above the first forecast years scenario assumptions. So that's cell F7.

Now don't lock this cell because we want this reference cell to move if we copy the formula to the right when we finish building our formulas. The second argument then tells Excel how many rows to look below the reference cell? For scenario 2 we want Excel to look two rows below the reference cell. Remember, we have two showing in our scenario cell so we can now just reference this scenario cell here.

And again, we're going to lock this cell reference so we can reuse the formula.

The third argument tells Excel how many columns to look to the right of the reference cell we want Excel to look directly below the reference cell. So we're going to enter a 0 here.

If we then press enter again, we're still pulling in the base case assumption and that is because we're telling Excel to look two rows below our reference cell and that's the base case scenario assumption.

Now, let's demonstrate the index function and we'll start off by typing the index function in.

Now the index function tells Excel to look into an array and identify a value a certain number of rows down that array. The first argument needed for the index function is the array and I'm going to take this from the scenario assumptions.

So there we have our array. The second argument is the position in the array. For example for scenario 2 we want Excel to look at the second row in the array. So again our scenario cell shows it's worth here. We simply need to navigate to the scenario cell and again, we'll lock the reference to that.

And then we can close brackets.

As before we're pulling in the base case scenario, but this time it's because we're telling Excel to look to the second item in the array and that's the base case scenario.

So those are three Excel functions that we can use for scenario analysis. The last thing I'm going to do is I'm going to copy this formula over to the right. Just to demonstrate that this formula will copy across as we would expect. And that would be the case for the other two functions that I demonstrated as well. Two things I would recommend that you do on your own is first of all to demonstrate yourself, if you change the scenario number in your model that this will indeed change the assumptions in your model. And secondly, I would suggest that you have a go at replacing the other growth rate and margin assumptions in your model with either the index offset or choose functions. In the solution file you'll see that we've done this using the index function because that uses the least number of keystrokes.