How to Build Drop-Down Scenarios in Your Financial Model

The most commonly used method of building scenarios is to use a combination of formulas and drop-down boxes. In the financial model, you create a table of possible scenarios and their inputs and link the scenario names to an input cell drop-down box. The inputs of the model are linked to the scenario table. If the model has been built properly with all the inputs flowing through to the outputs, then the results of the model will change as the user selects different options from the drop-down box.

Data validation drop-down boxes are used for a number of different purposes in financial modeling, including scenario analysis.

Using data validations to model profitability scenarios

Download File 0801.xlsx. Open it and select the tab labeled 8-1-start.

The way this has been modeled, the inputs are lined up in column B. You could perform sensitivity analysis simply by changing one of the inputs — for example, change the customers per call operator in cell B3 from 40 to 45, and you’ll see all the dependent numbers change. This would be a sensitivity analysis, because you’re changing only one variable. Instead, you’re going to change multiple variables at once in this full scenario analysis exercise, so you’ll need to do more than tweak a few numbers manually.

To perform a scenario analysis using data validation drop-down boxes, follow these steps:

1. Take the downloaded model and cut and paste the descriptions from column C to column F. You can do this by highlighting cells C6:C8, pressing Ctrl+X, selecting cell F6, and pressing Enter.

   The inputs in cells B3 to B8 are the active range that drives the model and will remain so. However, they need to become formulas that change depending on the drop-down box that you’ll create.

2. Copy the range in column B across to columns C, D, and E.

   You can do this by highlighting B3:B8, pressing Ctrl+C, selecting cells C3:E3, and pressing Enter. These amounts will be the same for each scenario until you change them.

3. In row 2 enter the titles Best Case, Base Case, and Worst Case.
   Setting up the model for scenario analysis.

   Note that the formulas still link to the inputs in column B, as you can see by selecting cell C12 and pressing the F2 shortcut key.

4. Edit the inputs underneath each scenario.

   You can put whatever you think is likely, but in order to match the numbers to those in this example, enter the values. Ignore column B for now.

   Inputs for scenario analysis.

   Now you need to add the drop-down box at the top, which is going to drive your scenarios. It doesn’t really matter where exactly you put the drop-down box, but it should be in a location that’s easy to find, usually at the top of the page.

5. In cell E1, enter the title Scenario.
6. Select cell F1, and change the formatting to input so that the user can see that this cell is editable.

   The easiest way to do this is to follow these steps:

   1. Click one of the cells that are already formatted as an input, such as cell E3.
   2. Press the Format Painter icon in the Clipboard section on the left-hand side of the Home tab. Your cursor will change to a paintbrush.
   3. Select cell F1 to paste the formatting.

      Format Painter is normally for single use. After you’ve selected the cell, the paintbrush will disappear from the cursor. If you want the Format Painter to become “sticky” and apply to multiple cells, double-click the icon when you select it from the Home tab.

7. Now, in cell F1, select Data Validation from the Data Tools section of the Data tab.

   The Data Validation dialog box appears.

8. On the Settings tab, change the Allow drop-down to List, use the mouse to select the range =$C$2:$E$2, and click OK.
   Creating the data validation drop-down scenarios.
9. Click the drop-down box, which now appears next to cell F1, and select one of the scenarios (for example, Base Case).

Applying formulas to scenarios

The cells in column B are still driving the model, and these need to be replaced by formulas. Before you add the formulas, however, you should change the formatting of the cells in the range to show that they contain formulas, instead of hard-coded numbers. Follow these steps:

1. Select cells B3:B8, and select the Fill Color from the Font group on the Home tab.
2. Change the Fill Color to a white background.

   It’s very important to distinguish between formulas and input cells in a model. You need to make it clear to any user opening the model that the cells in this range contain formulas and should not be overridden.

Now you need to replace the hard-coded values in column B with formulas that will change as the drop-down box changes. You can do this using a number of different functions; an HLOOKUP, a nested IF statement, an IFS, and a SUMIF will all do the trick. Add the formulas by following these steps:

1. Select cell B3, and add a formula that will change the value depending on what is in cell F1.

   Here is what the formula will be under the different options:

   • =HLOOKUP($F$1,$C$2:$E$8,2,0)

     Note that with this solution, you need to change the row index number from 2 to 3 and so on as you copy the formula down. Instead, you could use a ROW function in the third field like this: =HLOOKUP($F$1,$C$2:$E$8,ROW(A3)-1,0)

   • =IF($F$1=$C$2,C3,IF($F$1=$D$2,D3,E3))
   • =IFS($F$1=$C$2,C3,$F$1=$D$2,D3,$F$1=$E$2,E3)
   • =SUMIF($C$2:$E$2,$F$1,C3:E3)

     As always, there are several different options to choose from and the best solution is the one that is the simplest and easiest to understand. Any of these functions will produce exactly the same result, but having to change the row index number in the HLOOKUP is not robust, and adding the ROW may be confusing for a user. The nested IF statement is tricky to build and follow, and although the new IFS function is designed to make a nested IF function simpler, it’s still rather unwieldy. The SUMIF is quite simple to build and follow, and it’s easy to expand if you need to add extra scenarios in the future.

     Note that IFS is a new function that is only available with Office 365 and Excel 2016 or later installed. If you use this function and someone opens this model in a previous version of Excel, she can view the formula, but she won’t be able to edit it.
2. Copy the formula in cell B3 down the column.
   The completed scenario analysis.

   By using an ordinary copy and paste, you’ll lose all your formatting. It’s important to retain the formatting of the model so that you can see at a glance which inputs are in dollar values, percentages, or customer numbers. Use Paste Formulas to retain the formatting. You can access it by copying the cell onto the clipboard, highlighting the destination range, right-clicking, and selecting the Paste Formulas icon to paste formulas only, and leave the formatting intact.

   Now for the fun part! It’s time to test the scenario functionality in the model.

3. Click cell F1, change the drop-down box, and watch the model outputs change as you toggle between the different scenarios.