Roseanne asks about the Scenario Manager in Excel. I hope that she will be able to use the Data Table discussed in Episode 794, because the Scenario Manager is relatively very difficult to use. Episode 909 will show you how.
This video is a companion to the book Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This video is a companion to the book Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Today's question sent in by Roseanne.
Roseanne says, "What do you know about the Scenario Manager?" Alright, Roseanne, I want you to go back and watch Podcast 794.
In Podcast 794, I talked about how to use the data table command.
Basically, if you have 2 variables that are going to change, you can set up a data table and do many scenarios very, very quickly.
Back in Podcast 794, I had 4 different values for the term, 5 different values for the loan amount, and was able to calculate those 20 scenarios in a matter of seconds.
This, in contrast, is so easy compared to the Scenario Manager.
Scenario Manager-- let's take a look at that.
Alright, so here, similar Spreadsheet.
I have Price, Term, Rate; calculate the Payment, Total of all principal payments, Total interest payments; and we want to take a look at changing the Price, the Term, and the Rate-- 3 variables.
Well, now we're stuck using Tools, Scenarios, and I'll show you what we have to go through to set up one individual scenario.
Remember, in Podcast 794, I set up 20 in a few seconds.
Well, to add a new scenario, we'll come here and I'll call it the Podcast scenario.
We're going to change B1 to B3, and now I have to go through and type in, individually, the values for each one of those.
I can't use formulas; it's just a nightmare so...
Type in new values and click OK.
Alright.
And now, I've created a brand new scenario.
And to see the different scenarios, of course I can double-click here and see the values.
The one cool thing that the Scenario Manager can do is, we can say we want to see a summary, and with the summary, we can click OK, and we get a brand new report that shows the name of each scenario, the input cells, and then the Result Cells.
So it's not just a matter of having one Result Cell and two input cells; you can have as many as you want.
But once you've seen Podcast 794, and how easy it is to set up, literally, dozens or hundreds of scenarios in a couple of seconds, to set up a hundred scenarios here, you'd be in for, you know, maybe you could do two a minute.
So you're looking at at least an hour to set up what would take a couple of seconds using a data table.
So it's there when you have complex situations where many cells are changing, and you have many Result Cells that you want to track; good way to go.
But most of the time, I try and convince my manager to limit this scenario down to just two input cells and one output cell, and then I can do as many scenarios as he wants using the data table.
Want to thank Roseanne for sending that question in, and I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel.
Today's question sent in by Roseanne.
Roseanne says, "What do you know about the Scenario Manager?" Alright, Roseanne, I want you to go back and watch Podcast 794.
In Podcast 794, I talked about how to use the data table command.
Basically, if you have 2 variables that are going to change, you can set up a data table and do many scenarios very, very quickly.
Back in Podcast 794, I had 4 different values for the term, 5 different values for the loan amount, and was able to calculate those 20 scenarios in a matter of seconds.
This, in contrast, is so easy compared to the Scenario Manager.
Scenario Manager-- let's take a look at that.
Alright, so here, similar Spreadsheet.
I have Price, Term, Rate; calculate the Payment, Total of all principal payments, Total interest payments; and we want to take a look at changing the Price, the Term, and the Rate-- 3 variables.
Well, now we're stuck using Tools, Scenarios, and I'll show you what we have to go through to set up one individual scenario.
Remember, in Podcast 794, I set up 20 in a few seconds.
Well, to add a new scenario, we'll come here and I'll call it the Podcast scenario.
We're going to change B1 to B3, and now I have to go through and type in, individually, the values for each one of those.
I can't use formulas; it's just a nightmare so...
Type in new values and click OK.
Alright.
And now, I've created a brand new scenario.
And to see the different scenarios, of course I can double-click here and see the values.
The one cool thing that the Scenario Manager can do is, we can say we want to see a summary, and with the summary, we can click OK, and we get a brand new report that shows the name of each scenario, the input cells, and then the Result Cells.
So it's not just a matter of having one Result Cell and two input cells; you can have as many as you want.
But once you've seen Podcast 794, and how easy it is to set up, literally, dozens or hundreds of scenarios in a couple of seconds, to set up a hundred scenarios here, you'd be in for, you know, maybe you could do two a minute.
So you're looking at at least an hour to set up what would take a couple of seconds using a data table.
So it's there when you have complex situations where many cells are changing, and you have many Result Cells that you want to track; good way to go.
But most of the time, I try and convince my manager to limit this scenario down to just two input cells and one output cell, and then I can do as many scenarios as he wants using the data table.
Want to thank Roseanne for sending that question in, and I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel.
