Hey, welcome back to the Mr. Excel netcast. I’m Bill Jelen. A question sent by Roseanne. Roseanne says, “What do you know about the scenario manager? All right Roseanne, I want you to go back and watch Podcast 794. And Podcast 794 talks about how to use the data table command.
Basically, if you have two variables that are going to change you can set up the data table into many scenarios very, very quickly. Back in Podcast 794, I had four different values for the term, five 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. Let’s take a look at that.
All right, so here similar spreadsheet I have price term rate. Calculate the payment total of all principal payments, total of all interest payments. And we want to take a look at changing the price, the term and the rate, three variables. Well, now we’re stocked using two scenarios. And I’ll show you what we have to go through the set-up one in the visual scenario. Remember Podcast 794, I should have 20 in a few seconds. Well, to add a new scenario, I will come here and call 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 formula’s just a nightmare so, type in new values and click okay. All right 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 is we can say we want to see a summary. And with the summary, we can click okay. 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 setup, literally dozens or hundreds of scenarios in a couple of seconds. To set up a hundred scenarios here, you’d be in for maybe you could do to a minute. So, you-re looking at least an hour to set up what would take a couple of seconds using a data table. So, it’s there or you have complex situations where many cells are changing and you have many result cells you want to track, good way to go but most of the time I’m trying to mention to my manager to limit the scenario down to just two input cells and then one output cell and then I can do as many scenarios as he wants using the data table.
I want to thank Roseanne for sending that question and I want to thank you for stopping by. We’ll see you next time for another netcast from Mr. Excel.
Transcription by:
Scribe4you Transcription Services