One way to bring your analysis to the next level is to incorporate multiple scenarios and see the possible range of outcomes versus one answer.
After you build out some spreadsheet analysis, which will inevitably have some input, some data manipulation and mathematical operation, and then some output (the answer you get from why you did the analysis in the first place), the next step is often running some scenario analysis. For example if you built out a model on how much home you could afford based on certain assumptions or inputs like purchase price, downpayment percentage, mortgage rate, property taxes, etc, you may want to see what happens if some of these assumptions change. Perhaps you have a range of house price you can afford (bottom of the range, middle of the range, and top of the range) and you want to see what the payments are for each scenario.
There’s multiple ways to run scenario analysis. The most rudimentary is to just hardcode in one of the new inputs to override an older input to see what the answer is in real time.
Like everything else in a spreadsheet, there are infinite ways to accomplish the same task. This post specifically walks through how you can utilize the CHOOSE and OFFSET functions in order to model different scenarios. There are other techniques such as utilizing data tables or self referencing cells as well, but those deserve its own post.
In layman’s terms, the CHOOSE function allows you to reference a list in order, and then allows you to call upon one of the items in the list based on a number; 1 is the first item on the list, 2 is the second item on the list, so on and so forth. Due to this functionality, the way people use a CHOOSE function to create scenarios is the following:
- The CHOOSE function is: (Index_Number, Value1, Value2...)
- You can create multiple scenarios by putting different inputs in various rows or columns.
- Then you can create a toggle cell (which usually is just 1, 2, 3, 4, etc.)
- Then you can reference the toggle cell and then the different scenarios in the order you want using a CHOOSE function
The OFFSET function is slightly more tricky because you can not see the exact cell that is referenced. However, in my opinion, it’s even more flexible.
The OFFSET function is: (Reference, Rows, Columns, Height, Width)
For example, even though the two formula’s above are slightly different, they yield the same result: “Scenario 1.”
In the real world financial analysis can look something like this where you create multiple columns or rows of scenarios that you want to reference.
This analysis above is looking at what revenue will look like depending on the yearly growth. There are three different scenarios we want to look at. This is where we can utilize the OFFSET or the CHOOSE function in row 7 which references the toggle in cell C2 (scenario we want to choose) and the various scenarios in row 8-10.
The OFFSET formula in cell D7 is =OFFSET(D7,$C$2,,) whereas a CHOOSE Function there would be =CHOOSE($C$2,D8,D9,D10)
When you are building a complex model where multiple things are affected by the scenario, a key pitfall you have to watch out for is forgetting to link something up to the toggle.
