Excel: Learn Excel from MrExcel Episode 909 - Scenario Manager

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 ...

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.

Keywords for this video: accounting, business, excel, microsoft, mrexcel, scenarios, spreadsheets, technology, tutorial

This video is current as of December 19, 2008

For more resources for Microsoft Excel