Excel modelling extract: dropdown boxes

Dropdown (or combo) boxes are helpful for automating scenarios in financial modelling, and pointing the user to the key section of the model they should vary.  

Financial modelling with drop down boxes

Here we have a copy of the Excel spreadsheet where we were using the choose function to structure financial modelling scenarios. This version has been automated with a dropdown/ combo box. You can download the Excel spreadsheet here: dropdown boxes.

Screenshot image showing how to apply dropdown boxes in Excel financial modelling

Getting started with dropdown boxes

The best way to get started is to download the spreadsheet: dropdown boxes.

Right mouse click on the dropdown box, and copy and paste it into a blank spreadsheet model of your own. List out say five scenarios of your own. Right mouse click over the dropdown box and select “format control”. The “input range” should be your text-based list of scenarios. The “cell link” can be any cell you like e.g. B5. Make the “drop down lines” match your number of scenarios (5 in this case). Click on “OK”. Click outside the dropdown box. Now you should be able to start using it. You can also download an example where the exercise has been done for you: dropdowns. We’ve just shown you the really easy way to get going with dropdown boxes – once you have one in a spreadsheet you can copy and paste it and use it anywhere.

Image showing how to modify dropdown boxes in Excel

Financial modelling course tips: running scenarios using drop down boxes

If you look again at the first dropdown boxes download you may notice that the dropdown box isn’t core to our analysis.  It doesn’t affect the results of our financial modelling.  So what have we achieved with the use of the dropdown box?  What the dropdown box does is make it obvious which part of the model a new user should modify.   So here are our tips for scenario modelling with dropdown boxes:

  • Use dropdown boxes to “tell” a new user which part of the model they should be changing.
  • Lay out your scenarios as per the spreadsheet example.  That leaves a clear record of the scenarios you’ve been running.  You can modify them any time.  You can add a new scenario easily (e.g. by taking a copy of scenario 3, pasting it underneath, creating a new scenario 4, and modifying the drop down box and the choose function so that it copes with the extra scenario).  Those scenarios are highly visible within the model, and can easily be printed for review.  This is a much better alternative to using Excel’s own “scenario manager” function.  If you want to play with that, on the “Data” tab select “What-If Analysis” and “Scenario Manager”.  However, laying out your scenarios within your spreadsheet like we have here provides a much clearer record of the scenarios you have chosen to run.  You’ll find most experienced financial modelling analysts setting out scenarios like we have here (albeit on a much bigger scale!).

Financial modelling course challenge

If you have followed through the course example above, you have already practised copying and pasting drop down boxes from one spreadsheet into another.  Did you know that you can copy and paste drop down boxes from one tab to another, within the same model?  For example, the same drop down box can appear on an inputs/ assumptions tab, on a tab in a financial model that is being used for calculations, and on a tab in a model that is being used to deliver key outputs.  If you set them up correctly (after pasting them into the tab, hover over the drop down box, right click, “format control” and rewire the box back to the tab you copied it from) using the drop down box when you’re working on one part of the financial model will change the scenario and the drop down boxes in the rest of the model.  This can be useful for example when you are looking at the model key outputs and want to change scenario without flicking back to the inputs/ assumptions area of the financial  model.  Also, if you print the outputs and hand them to your boss, a copy of the drop down box at the top of the outputs sheet will tell your boss which scenario you have been running.

Creating dropdowns from scratch

You can create dropdown boxes completely from scratch. If you would like to do that (you shouldn’t need to) then full instructions are contained in this downloadable Excel spreadsheet: dropdown instructions. To get started, you’ll have to open Excel’s developer tab.

Other forms apart from dropdowns

If you go down the route of creating forms from scratch you will start to realise that, along with the dropdown box there are a few other forms that might be helpful (under the “Developer” tab, “Controls”, “Insert”, “Form Controls”: see the dropdown instructions). Sometimes you can see other forms used to switch parts of a model on and off. But of course, if you download our other forms spreadsheet, you can just copy and paste these new forms into your model, just like you did earlier with the dropdown box! Right mouse click and select “format control” and you should be able to get started.

Image containing examples of forms being used in Excel financial modelling

Other forms: scroll bar

Amongst the other forms, perhaps the scroll bar is the most useful or helpful. If you identified a few key variables in your model, you could use the scroll bar to highlight those variables, allowing the user to scale up and down smoothly. If you download the other forms, hover over the scroll bar and select “Format Control” – you should be able to work out what the key inputs are.

Displaying modelling results

Here you can download an example scrollbar where we’ve identified a key variable in a model and linked it to a very simple graph (the graph is only plotting six data points – three at the start and three at the end).

Image showing an example of linking a scrollbar to a graph in Excel

Financial modelling tips for scroll bars

Don’t get too carried away with scrollbars and linked graphs though! Too many scroll bars and the “Hey this is important!” message will be lost. Linking to fluid graphs is pretty memory hungry too, and your Excel spreadsheet could get very large, slow and difficult to email.

Continuing with the Excel financial modelling course extract

In the next lesson we look at naming cells in an Excel financial model, allowing us to navigate quickly to any remote part of spreadsheet.

About this online financial modelling course material

You’re looking at a free online financial modelling course extract covering Excel functions.

 

 

...join the conversation...

Your email address will not be published. Required fields are marked *