Managing scenarios is a key skill for any advanced financial modeler.
In this video, Ian Schnoor, FMI’s Executive Director walks through essential techniques to help you build a flexible, clear, and professional scenarios page, whether you're preparing for the AFM exam or improving your day-to-day modeling.
Watch to sharpen your skills and take your financial modeling to the next level.
Transcription (Edited for Clarity)
We have now covered three critical items and there are two more to go. We talked about centering titles, we covered formatting and advanced formatting, and we discussed how to manage text and footnotes. Now I will show you scenarios and switching mechanisms.
I introduced this briefly in the earlier video on modeling best practices. In this session I will review the core approach and then add a few additional skills beyond what I covered before.
First, skills for managing scenarios. In the best practices video I emphasized that every model needs a scenarios page. The scenarios page lets you run upside and downside cases so you can answer questions like: what happens if a variable is higher or lower than expected, what happens if cost inflation is very high or very low, and how those changes affect the forecast and results.
We call these variables key drivers because they are hard to forecast and hard to control, yet they can have a big impact. Common examples include inflation rates, interest rates, exchange rates, commodity prices, growth rates, and discount rates used in valuation.
Here is a quick recap of how the scenarios page works, and then I will show a few additional techniques to run it. It is important to run both upside and downside cases, so every model you build or review should include a scenarios page. The goal is to stress test the variables that are difficult for management to predict.
There are three steps to build any scenarios page. I will summarize them here, then demonstrate and move on to a few other useful tools.
Step one: build a large rectangle of blue numbers. These are your assumptions.
Step two: add a switch.
Step three: create a skinny row that shows the live case. This live case is what the model is actually using.
Many people implement the live row with an IF statement, but that is not ideal because nested IFs get long and hard to follow. Instead, I recommend the CHOOSE function. After that I will show other Excel options that are flexible and clean.
We will also look at building a user switch, but I will come back to the switching controls in a moment. For now, let us build the mechanics.
Step one in the model: if you want base, best, and worst cases for cost inflation, create the large rectangle and color those inputs blue. Blue signals data inputs. How do you set the numbers? Do research, review reports, speak with your boss and your team, and enter what you believe are the most likely base, best, and worst values for each driver. Repeat this for any variable you want to include in scenarios, such as growth rates or sales volume.
Step two: go to a blank cell and enter a switch value. Typically this is the number 1. The switch tells the model which case to run.
Step three: build the live case row. When the switch is 1, the live row pulls the base case. If the switch is 2, it pulls the best case. If the switch is 3, it pulls the worst case. The live row is the set of values that flows into the model.
As noted, many users write a nested IF that checks the switch and picks base, then best, then worst. It works, but nested IFs get long and are not easy to audit. I strongly encourage you to avoid them. Use CHOOSE instead. CHOOSE asks for the switch value and then the sequence of choices. You click the base option, then the best option, then the worst option. It is elegant, simple, and easy for clients to understand. If the switch is 1 it returns the first choice, if it is 2 it returns the second, and if it is 3 it returns the third. The order follows the sequence you typed.
If you want more flexibility, you can also use INDEX or OFFSET. Here is the idea.
INDEX option one: make the array the entire blue rectangle of assumptions. Then provide two indexes: the row and the column within that rectangle. The row comes from the switch, since 1 means the first row, 2 means the second, and 3 means the third. For the column, you can reference a helper row that contains the column numbers for each year. Then INDEX returns the correct value and you can copy it across. This approach works but requires maintaining the column references.
INDEX option two: make the array a single column under the current year. Use a relative reference with no dollar signs so the array shifts as you copy across. Provide only the row number, which comes from the switch. Because the array is one column wide, you do not need a column index at all. This is simpler and cleaner.
I like CHOOSE because it is transparent, but INDEX is also fine. Be cautious though, because INDEX formulas can become complex and that can confuse some users.
OFFSET is another option. It is similar in concept to INDEX but instead of passing a range as the array, you pass a single anchor cell as the reference and then tell Excel how many rows down and how many columns over to move. For example, set the reference as the blank cell above the rectangle, then move down by the switch value and over by zero columns to return the correct item for the current year. OFFSET works, but like INDEX it can become harder to audit if it gets too clever.
The key point is that it is important to build scenarios, and there are several clean ways to drive the live case. My preference is CHOOSE for simplicity, with INDEX or OFFSET as alternatives. There are other functions you can try as well, but in this session I wanted you to see how INDEX and OFFSET work so you understand the options.
That is how you manage scenarios.
