Display Scenarios Using a Drop-Down List

Posted on September 23, 2007

In my finance classes at Metro State, I frequently use Excel as a presentation program. I prefer it to PowerPoint because it allows me to do a live demonstration of concepts, rather than showing static slides with static pictures.

One tool that is very often useful is Excel’s Scenario Manager. If you aren’t familiar with it, the Scenario Manager is a tool that lets you specify values to place into certain cells when you choose to display a scenario. So, you can have one worksheet, but display any of several scenarios (best base, base case, worst case, etc). The problem with this tool is that you have to load it (Tools --> Scenarios in Excel 2003 and earlier, or under “What If Analysis” on the Data Ribbon in Excel 2007) in order to change the scenario. It isn’t that difficult, but it really interrupts the flow of a presentation. The purpose of this post is to show a way to use the Scenario Manager, but use a drop-down list to display the scenarios.

My example spreadsheet is a very simple NPV calculator, as shown below:

I set up three scenarios using the Scenario Manager:

1. “Low WACC” with an 8% required return.
2. “Base WACC” with a 10% required return.
3. “High WACC” with a 12% required return.

I’ve purposely kept this simple for demonstration purposes. There is only one changing cell (the required return in B1). I can now change the spreadsheet by launching the Scenario Manager, choosing a scenario, and pressing the Show button. However, the Scenario Manager’s dialog box is modal. That is, you can’t do anything to the worksheet until you dismiss the dialog box. It just sits there blocking part of the worksheet and preventing you from doing anything else.

We can fix that problem by using the Scenario Manager as a database for our scenarios (that is, use it to store them). To switch between scenarios, we add a drop-down list to the worksheet, and add a very tiny amount of VBA code.

You can see the the drop-down list in the upper-right corner of the picture above. To add one to the worksheet, go to View --> Toolbars and turn on the Forms tool bar (in Excel 2007, choose Insert on the Developer Ribbon). Click on the Combo Box control, and then click and drag in the worksheet to place it. Now, we need to format the control so that it knows where to get the strings to put in the list, and where to put the number of the selected item. The picture below shows how I have it formatted:

The “Cell link” is where the control lets you know which item has been selected. We can use that information for all kinds of purposes. In this case, we are going to write a VBA sub that will read that and then change the scenario that is displayed, without launching the Scenario Manager.

Open the VBA Editor by pressing Alt-F11 and then go to the code module for the worksheet (it will be listed under the file name and have the same name as the worksheet). Paste this code:

Sub ChangeScenario()
' Changes the scenario on the worksheet according to the choice made
' in the drop-down list.
Scenarios(Range(ActiveSheet.Shapes("DropDown").ControlFormat._
LinkedCell).Value).Show
End Sub

(Note that the underscore indicates a line continuation. The second line of code should be on the same line, but I had to make it fit into a limited width for the blog.)

That code will be run when the user chooses a new item from the drop-down list. It simply tells Excel to display a certain scenario whose number is found in the “LinkedCell” of the drop-down list. Note that I have renamed the drop-down list to DropDown. To do this in any version of Excel, right-click the control and change the name in the Name Box to the left of the Formula Bar.

That’s it. A very simple way to view scenarios without having to launch the Scenario Manager. This example is very simple, and could have been done without using Scenario Manager at all. However, if you have more complicated scenarios (with many changing cells), then this is a great technique and will improve your presentations.

You can download my example spreadsheet if you want to give it a try.

Posted by on September 23, 2007 at 07:06 PM

Categories: Controls Combo Box Functions Financial Functions NPV Scenario Manager VBA