When you are building decision spreadsheets, the focus is often on getting the spreadsheet right. However, right at the end you can bet someone will come up with a number of scenarios they would like to run through the spreadsheet. So is there a simple way to create scenarios in Excel?
All too often we have seen hundreds of formula being changed to accommodate this, when you should be able to make minor changes to your spreadsheet and achieve the same thing.
How to create scenarios in an Excel spreadsheet that is already built
So in the example below we have a spreadsheet and as you can see, the cells in rows 2, 4 and 5 use the inputs in column B to work out the gross profit per month. But now someone wants to be able to use the 4 different scenarios shown in the model, and easily switch between them.
The first thing most people do is go to the detailed calculations (rows 2,4 and 5) and try and change these formula to look at the new scenarios with VLOOKUP’s, OFFSETS, CHOOSE’s, etc. But this means that you are changing the formula of multiple cells, generally under time pressure, and making already complex formula even more complex. This is how spreadsheet errors are made.
Rather, you should realise that the inputs in B12 to B20 already work through the model and, if well built, have been tested.
So why not change ONLY these cells to look at the new options. This way you have a minimal impact on the formulas but you should be able to get all the scenarios in. Furthermore, it will be much easier to follow what is happening in your spreadsheet.
Steps to follow to create these scenarios
- The first thing we would do is create a cell in C11 with a number in it, and colour it blue because it is now an input cell (see below).
- Then, in B12, where the previous input cell was, we use the CHOOSE formula to tell Excel where to look if cell C11 has a 1, 2, 3 etc in it.
- This formula can then be copied down to B13 to B20.
- For the sake of good spreadsheet practice we have also removed the blue colouring from cells B12 to B20 as these are no longer input cells, but calculation cells and should not be changed by users.
So with minimal changes to the spreadsheet, we can now change cell C11 to any number from 1 to 4, and the appropriate inputs will be pulled through to cell B12 to B20.
As these cells were always correctly connected to the detailed model, it will automatically work through the model and give you the desired results.
Much easier than building nested formula into all the other detailed calculations!
You could similarly use the Scenario Manager in Excel, but we find that this is a good, transparent way to include scenarios into a spreadsheet that has already been built.
For more on sensitivities, scenarios and what if’s have a look at our Online What If course.