In financial modeling, switching mechanisms are used to toggle between different assumptions, scenarios, or outcomes.
In this video, FMI’s Executive Director, Ian Schnoor, CFA, CFM, explains how to make models more dynamic and flexible for analysis and decision-making using switching mechanisms.
Transcript
Scenarios. The last major topic that I want to show you is switching mechanisms. I showed you one in the first video on modeling best practices if you watched that one. In this video, I’m going to repeat that one and then show a second one that you might like as well. But the point with the switching mechanism is this: let’s go back to the model. I don’t ever want to leave this value exposed. I don’t want anyone to ever see this number one, because it doesn’t mean anything. I don’t want my client to see it. I don’t want anyone to touch it or change it. It doesn’t mean much. What I want to do is have a little elegant, user-friendly mechanism like this one that says Base, Best, and Worst. When I open it up and click Base, it runs the base case; if I click Best, it runs the best case; and if I click Worst, it runs the worst case.
Now, all this little control does—this is not a macro, it’s not VBA—is, if I click the first option it puts a number 1 into this cell; if I click the second option it puts a number 2; and if I click the third option it puts a number 3. So I don’t have to type in a number—the little switch puts the correct number for me. How did I do that? I had to go to my Developer tab. Some of you may not have the Developer tab by default, so to turn it on you go to the File menu, then File Options, then Customize Ribbon, and on the right-hand side you put a checkmark in Developer and click OK. You’ll then have a new menu in Excel called Developer. From there, you click Developer, then click Insert, and under Form Controls you choose the Combo Box control. Your cursor turns into a plus; you draw the combo box on the sheet, and that’s it. All you did was draw it on the screen.
The very last thing is to right-click the combo box, choose Format Control, and set two properties: the Input Range (the three words Base, Best, Worst) and the Cell Link (the cell that will receive 1, 2, or 3). Click OK. Initially you’ll get error messages because it put a zero into the linked cell and your CHOOSE functions are unhappy with zero—that’s fine. Once you open the combo box, you’ll see Base, Best, and Worst; clicking Base writes a 1, clicking Best writes a 2, clicking Worst writes a 3. Finally, right-click the control again, choose Activate, resize and move it to cover the underlying cell, and now you have a beautiful, elegant, user-friendly combo box to control the scenario.
That’s one switching mechanism. Some people, however, prefer not to use the combo box but instead use Excel’s Data Validation feature. Let’s take a look at that. Data Validation lives in the Data menu; the keystroke is Alt, A, V, V. Alt A opens Data, V opens the Data Validation dialog, and V again selects Validation. Once the dialog is open, Data Validation lets you restrict what’s allowed in a given cell. By default anything is allowed; to create a switch, choose the List option and point to the range containing the words Base, Best, Worst. Click OK, and the cell now shows a small drop-down arrow. Click it and select Base, Best, or Worst.
When you select Base, the cell contains the word “Base”; when you select Best, it contains “Best”; and when you select Worst, it contains “Worst.” This is another way to build a switch, but I caution you against using it on a scenario page for two reasons. First, what’s actually in the cell is text, not a number. Your CHOOSE or INDEX functions expect a numeric index, so you’d have to replace them with something like VLOOKUP, which looks for the word “Base” in the leftmost column of a lookup table and returns the corresponding value from a specified column. That’s more complex than a simple CHOOSE. Second, with a combo box it’s obvious that you’re using a switch; with Data Validation you can’t tell it’s a switch until you click the cell. It isn’t immediately clear to your client or boss that they can control the model that way.
That said, Data Validation is still very powerful. I often use it for inputs like tax rates: restrict the tax-rate cell to be a decimal between 0.25 and 0.40. If someone tries to enter 0.20 or 0.50, Excel shows an error alert saying “The tax rate you entered is outside the allowable range,” and they can retry. You can also configure input messages and error messages in the Data Validation dialog—stop, warning, or information alerts—to guide your user. While it’s not my favorite for scenario switches, it’s an excellent tool for enforcing input rules and keeping your model robust.
