What is the Excel Drop-Down List?
The Excel Drop-Down List provides the user with a predefined set of options to select from, rather than via manual data entry.
How to Create Drop-Down List in Excel (Step-by-Step)
Creating a drop-down list in an Excel spreadsheet is a quick method to make a financial model more intuitive for the end-user.
Instead of manually needing to enter a value into a cell, with a drop-down list, the user picks the desired value from the provided list.
Since the user is constrained to selecting an option on the list, the chance of a data entry error from an invalid input or typing mistake is reduced.
The steps to create a drop-down list are as follows:
- Step 1: Select the Cell to Create the Drop-Down List
- Step 2: Click on Data Validation (“Data” → “Data Tools” → “Data Validation”)
- Step 3: Select “List” as the Criteria from the Settings Tab
- Step 4: Enter the Cell Value Options for the List
The cell values contained within the list can be either manual inputs or cell references.
Drop-Down List Keyboard Shortcut: Open Data Validation Box
To create the drop-down list more efficiently by using keyboard shortcuts, press the following keys to open the data validation box.
Note that each key must be pressed separately in the order above, as opposed to all at once.
Excel Drop-Down List: Circularity Switch in Financial Modeling
One practical use-case of integrating a drop-down list in a financial model is the circularity switch, i.e. “circuit breaker”.
The most common source of circularity stems from modeling a company’s debt schedule, in which the interest expense is calculated.
In short, the incurred interest expense is a function of the average debt balance between the beginning and ending values.
However, the paydown (or drawdown) of a credit facility, such as the revolver, is determined by the company’s levered free cash flow, which is in part affected by the interest expense via net income on the income statement, thus creating “circularity”.
Furthermore, financial models built with intentional circularities can often “break”—i.e. the cells become populated with error messages—which the circularity switch is meant to circumvent, akin to a reset button.
In practice, the circularity switch is most often integrated into the model using the following convention:
- “0” → ON
- “1” → OFF
The interest expense formula for each debt tranche will use an “IF” function, where if the circularity switch cell is set to “0”, either a value of zero is returned or the beginning of period debt balance is used in the calculation, rather than the average balance.
If the circularity causes a financial model to “break”, the user can scroll up and toggle the switch to “0” to cut off the circularity, before switching it back to “1”. The model’s error messages should subsequently be removed and the calculations should return to normal as intended, assuming all the relevant affected formulas were enclosed with the “IF” function.
A circularity switch cell should either contain a value of “0” or “1”, and creating a drop-down list here restricts the cell to contain only one of those values. Otherwise, an error message appears stating that the entered value is invalid.
Drop-Down List Calculator – Excel Model Template
We’ll now move on to a modeling exercise, which you can access by filling out the form below.
Excel Drop-Down List Tutorial: Integrating Operating Cases
Suppose we’re tasked with creating a drop-down list as part of integrating operating scenarios into a financial model.
The three operating scenarios which we want to restrict the user to select from are as follows:
- “Base”
- “Upside”
- “Downside”
The first step is to select which specific cell to be our operating scenario switch. Once chosen, we’ll name that cell “Case” to make references to the cell easier when entering formulas later on.
In the next step, we’ll open the data validation dialogue box using the keyboard shortcut from earlier and enter our three cases into the “Source:” section.
After pressing enter, our “Case” cell should now display a drop-down box to select one of the three options.
To better conceptualize how an operating case switch can be utilized in a financial model, we’ve included a simple revenue projection section.
The company in our hypothetical scenario generated $100 million in Year 0, which will increase (or decrease) each subsequent year by the following growth rate assumptions under each respective operating case.
- Base = +2.5% YoY Growth
- Upside = +5.0% YoY Growth
- Downside = -2.5% YoY Growth
The revenue growth assumption driving our revenue forecast adjusts based on the active selection in our drop-down list, resulting in a more structurally sound financial model.
