We are not a great fan of Excel’s scenario manager in financial modelling. Scenario manager ‘hides’ inputs in the background. We would rather you laid our your assumptions clearly and visibly as described when we were illustrating drop-down boxes.
However, we do think scenario manager could have a role outputting the results of financial modelling (although, even then, a single variable data table would probably provide a neater solution).
Using scenario manager in financial modelling: getting started
To illustrate how scenario manager works, and how you could use it to output key results, here’s an example spreadsheet for download where we’ve got our scenarios working with dropdown boxes and Excel names. If you’ve been following this material through, you’ve seen the spreadsheet before. To get started with scenario manager, go to the “Data” tab, click on “Data Tools”, “What-If Analysis”, “Scenario Manager”. To add a scenario click on “Add”. In this case our “Scenario Name” is going to be “Base”. Our changing cell is going to be cell B5. Click on “OK” and then enter “1” as the scenario value. Repeat the process for the second (“Growth”) and third “Stress” scenarios.
Click on scenario “summary” and select the result cells G6:I6. Click on “OK”.
Here you can download an example where it has been done for you: Excel scenario manager. If you download the spreadsheet and go to the “scen mgr” tab, you can output scenarios now very quickly and easily. You need to go “Data”, “What-If Analysis”, “Scenario Manager”, “Summary”, “OK”. You will notice that the outputs of all three scenarios in our financial model are outputted very quickly. That’s what we like about scenario manager.
Benefits of scenario manager for financial modelling
Once scenario manager is set up, it’s really quick to output and display the results of all our three financial modelling scenarios against each other. We wouldn’t use scenario manager for storing our detailed scenario inputs (they end up hidden). But we can see scenario manager’s benefit for outputting and displaying financial modelling results.
Coming up in the Excel financial modelling course extract
In the next lesson we look at using timing flags switches to build flexibility into an Excel financial model.
About this online financial modelling course material
You’re looking at a free online financial modelling course extract covering Excel functions.