How to run multiple scenarios in you financial models using the scenario tool in Excel.
- For updated video clips in structured Excel courses with practical example files, have a look at our MS Excel online training courses . You can even try the Free MS Excel tips and tricks course.
- To see if this video matches your skill level (see the suggested skill score below) do our free MS Excel skills assessment.
- If you are based in South Africa look at the live courses we offer in Johannesburg and Cape Town.
- - 03:41
- - Skill Level: 6
- - What's my skill level
Multiple Scenarios in Excel
The SCENARIO feature allows you to quickly change between multiple scenarios in your spreadsheet.
In this simple example we have an amortisation table which reflects some Equipment that is purchased over a number of years. What we’d like to do is create a couple of scenarios, one being the Current situation and then a Low and a High one.
We can use the SCENARIO feature in the following way,
click on Tools,
SCENARIOS
and this will pop up
as you can se there are no scenarios defined so we need to add one
and we’ll give it the name Current
we’re asked what cells are going to change, as you can see its guessed at the moment and I clear that
and say we’re going to change that one
and using my control (Ctrl) we’re also going to change that one
and that one
what you’ll see is now were telling Excel that these three cells are going to change and I say Ok,
what I now get is a current cells that are going to change and the current values
and that is perfect for this situation so I’m going to say Ok,
and we have the Current situation set up,
Now we’re going to enter the Low case
I say Add again
give it a name in this case Low,
same cells are changing
we say Ok
and this case now I want my Low SCENARIO to be 80 000
number of years will stay at 5
interest will be .13
I say Ok
I Add another one
we’re going to call it high
say Ok
and 20 000
7 years
.19
and I say Ok
Now it is extremely easy to change between these cases so
at the moment we have a Current situation in,
if I click on Low
just watch here if I push Show
you’ll see that automatically changes and as a result all the numbers change
and we can quickly move to High,
Show
and there’s the new set of numbers
Once you have the SCENARIO setup you may not want to have this open all the time
so you can close it
and if you go to View
Toolbars,
customize
under the Commands you go to Tools
if you go down
you’ll see theres a SCENARIO button you can drag it
and put it into one of our current toolbars
and close this down
now you’ll see I’ve got a little toolbar here, where when I can click
it shows me all the available SCENARIOS,
when I switch it on
you’ll see it changes to the relevant SCENARIO.
Another useful function of the SCENARIO feature is the ability to summarise the results. So if we’ve setup our SCENARIOS
we can go to Tools
SCENARIOS
and at the bottom you’ll see a Summary button and if we push it
it asks us what report type we want, and in this case well have Scenario summary
and it asks us what the results cell needs to be and what we interested in is knowing how much is owed at the end of 5 years
which is what its go in there G12
and when we say Ok
it generates a separate sheet which gives us a summary of the cells that were used, what the current values are, and what the three cases were in this case, and what the end results were.