Advanced financial modelling course extract: drop down boxes

Drop down boxes are helpful for automating scenarios in financial modelling, and pointing the user to the key section of the model they should vary.  Instructions are contained in this Excel spreadsheet: advanced financial modelling course drop down box.

Financial modelling with drop down boxes

Whatever version of Excel you are using (2003, 2007, 2010), the quickest way to get started with drop down boxes is to download the spreadsheet, then copy and paste the drop down box into a new blank spreadsheet of your own.  The Excel spreadsheet contains full instructions for creating drop-down boxes from scratch, but you can take a massive short cut here with a quick copy and paste.

Getting started with drop down boxes: a short cut

Download and open the Excel spreadsheet: advanced financial modelling course drop down box.  Copy and paste the drop down box into a new spreadsheet of your own.  Right mouse click on top of the drop down box in the spreadsheet and select "Copy".  Open a new Excel spreadsheet of your own, right mouse click and select "Paste".

Financial modelling with drop down boxes: list out the scenarios

Write out the name of say 3 scenarios in each of say cells A4:A6 giving them names like "1 management case", "2 management stress case", "3 bank stress case"

Link the scenarios to the drop down boxes

Right mouse click on the drop down box.  Select "Format Control".  "Input range" should be the area of your spreadsheet where you entered the scenarios e.g. $A$4:$A$6.  Cell link can be any cell in the model e.g. $A$22.  Drop down lines should match the number of scenarios e.g. 3.  Click on "OK".

Getting the drop down box working

The drop down box won't work straight away.  Click somewhere in the spreadsheet, outside of the drop down box.  Click towards the right of the drop down box (on the small triangle).  It should now start working.  Notice, depending on which scenario you select, the drop down box will deliver the value 1, 2 or 3 to the "Cell link" - in our example A23.

Using the combo box to drive scenarios - the choose function

The next stage is to use the combo box to actually drive scenarios in the model.  Download the spreadsheet: advanced financial modelling course drop down box to see how this has been done.  Have a look at cells B28:B30.  Note the use of a Choose function.  Depending on which scenario is selected, the Choose function delivers the 1st, 2nd or 3rd input into cells B:28:B:30.  If you are unfamiliar with the choose function, click on the "fx" button near to the left of the formula bar in Excel.  Then you'll get a few more clues about that function's lay out.

Advanced course tips: modelling scenarios using drop down boxes

You may have noticed that the drop down box isn't core to our analysis.  It doesn't affect the results of our financial model.  So what have we achieved with the use of the drop down box?  What the drop down 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 drop down boxes:

• Use drop down boxes to "tell" a new user which part of the model they should be changing.
• Lay out your scenarios as per the spreadsheet example above.  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 advanced financial modelling analysts setting out scenarios like we have here (albeit on a much bigger scale!).

Advanced 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") 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.

Return to the advanced financial modelling training course extract

More advanced modelling training course material is available.  If you would like more information regarding our taught program, please see the full outline for our advanced modelling course.