If there’s a prize in advanced Excel modelling it probably shouldn’t be a prize for creating the longest most complex function imaginable (although you often see those ones in models)! Sometimes, especially with a small data set, Excel’s “Choose” function can provide a very simple alternative to other functions such as Vlookup. Choose can be really helpful for getting data in the right place when financial modelling: many of our course delegates love this function.
How does Excel’s Choose function work?
Please download the Excel spreadsheet to see how the Choose function works. Play with Cell B5, entering values for scenario 1, 2 or 3. Have a look at what’s happening in cells B7:C7. Click on cell B7, and then click on the “fx”/ function button in the Excel formula bar (or use the “Shift” “F3” keyboard shortcut to bring it up). That will give you some idea of the syntax for the function.
Structuring scenarios in financial modelling
Here Excel’s choose function has been applied to help structure scenarios. You can imagine that we have a separate “inputs area” in our model. We are using the choose function to get data into the correct place in our financial model (at the top of the inputs area), before using that data in the rest of the model (the “calculations” area). The beauty of this structure is that all of the scenarios are stored in one area of the Excel spreadsheet (where anyone can see, review and change them). We can flick between scenarios instantly just by changing what’s in cell B5.
Choose: adding another scenario
In our example, the Choose function takes the 1st, 2nd or 3rd value, depending on what’s entered in cell B5 (the “index number”). The function is very simple to use and apply. If you want to add a fourth value to the list, all you do is create a new scenario below the last and add another value on the end of the choose function.
Financial modelling course tips: structuring scenarios
We prefer the kind of structure here for structuring scenarios. Key inputs and assumptions are collected up, clearly laid out for review and can be easily changed. Scenarios can be changed very quickly. It’s much better than the alternatives of:
- Spreading your assumptions throughout your model, changing them as you go and probably leaving no permanent record of the changes you’ve made
- Excel’s scenario manager. The problem with Excel’s scenario manager is that it ‘hides’ inputs in the background. They’re not so visible for review. However, Excel scenario manager can have a useful role in outputting the results of scenario analysis – of which more later.
For these reasons, to run scenarios many experienced financial modellers use the same kind of structure as we have in our downloadable Choose function example. We’ve copied that structure based on our observation of best practice financial modelling.
Choose vs. VLookup
Although for a small data set Choose is likely to be the shortest neatest solution for getting data where you want it, for larger data sets Choose starts to become large and unwieldy. So with larger data sets, Vlookup is going to start winning the competition for the most efficient solution to getting your financial modelling data where you want it.
Coming up in the Excel financial modelling course extract
In the next lesson we look at using combo/drop-down boxes to automate scenarios in Excel financial modelling, allowing us to switch between alternatives instantly.
About this online financial modelling course material
You’re looking at a free online financial modelling course extract covering Excel functions.