Excel scenario manager

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.

Image showing how to use Excel's scenario manager

Click on scenario “summary” and select the result cells G6:I6. Click on “OK”.

Image showing how to set result cells in the Excel scenario manager

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.

Image showing example output for the Excel scenario manager

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.

1 comment

...join the conversation...

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

  1. interesting lesson