How to Build Drop-Down Scenarios in Your Financial Model

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

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

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.

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.