Transcripts for the above video clip:

SCENARIO

The SCENARIO feature allows you to quickly change between various 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.