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 (Edited for Clarity)
The last major topic I want to show you is switching mechanisms. I showed you one in the first video on modeling best practices. In this video I will repeat that one and then show you a second option you might like. The purpose of a switching mechanism is simple: I do not want to leave a raw value exposed. If the number 1 is sitting in a cell, it does not mean anything to a client and I do not want them to see it or touch it. Instead, I want to give them an elegant and user-friendly mechanism like this one that displays Base, Best, and Worst. When I select Base it runs the base case, when I select Best it runs the best case, and when I select Worst it runs the worst case.
Here is how it works. This control is not a macro and it is not VBA. All it does is insert a number. If I choose the first option it places a 1 in the cell, if I choose the second it places a 2, and if I choose the third it places a 3. I never have to type a number manually. The switch does the work for me.
To build it, you first need the Developer tab. If you do not see it by default, turn it on by going to File > Options > Customize Ribbon, check Developer on the right-hand side, and click OK. You will now see a new menu called Developer. From there, click Developer, then Insert, and under Form Controls choose the Combo Box. Your cursor will turn into a plus sign. Draw the combo box on the sheet, and that is it.
The final step is to right-click the combo box, choose Format Control, and set two properties. The Input Range is the three words Base, Best, and Worst. The Cell Link is the cell that will receive 1, 2, or 3. Click OK. At first you may see error messages because the linked cell shows zero and the CHOOSE function is not designed for that, but once you open the combo box and select an option, the correct numbers will appear. Clicking Base writes a 1, clicking Best writes a 2, and clicking Worst writes a 3. To finish, right-click the control again, choose Activate, resize and move it to cover the underlying cell, and now you have a clean and user-friendly combo box to control the scenario.
That is one switching mechanism. Another option is to use Excel’s Data Validation feature. You can find it in the Data menu or use the keystroke Alt A V V. Alt A opens the Data menu, V opens the Data Validation dialog, and the second V selects Validation. Once the dialog is open, Data Validation allows you to restrict what is permitted in a given cell. By default anything is allowed, but if you want to create a switch, choose the List option and point to the range containing Base, Best, and Worst. Click OK, and the cell will now display a small drop-down arrow. When you select Base, the cell contains the word "Base." When you select Best it contains "Best." When you select Worst it contains "Worst."
This approach works, but I recommend caution when using it for scenarios for two reasons. First, what is stored in the cell is text rather than a number. CHOOSE or INDEX functions expect numeric indexes, so you would have to replace them with something like VLOOKUP that searches for the word in a lookup table and returns the corresponding value. That is more complex than a simple CHOOSE. Second, a combo box makes it obvious that the model has a switch. With Data Validation you cannot tell there is a switch until you click the cell, so it is not as clear to your client or manager that they can control the model this way.
That said, Data Validation is still very useful. I often use it for inputs such as tax rates. For example, restrict the tax rate cell to accept only a decimal between 0.25 and 0.40. If someone enters 0.20 or 0.50, Excel shows an error message saying "The tax rate you entered is outside the allowable range," and they must try again. You can also configure input messages and error alerts as stop, warning, or information types to guide the user. While I do not prefer Data Validation for scenario switches, it is excellent for enforcing input rules and keeping a model reliable.
