Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
Excel's Goal Seek and Data Tables features are really great, but there are times when you might want to change more than just one or two variables at a time or maybe you want a nicely formatted report of all your possibilities. That's what the Scenario Manager is and it's also under the What-If button that we've been looking at in this chapter. So, go to the Data Tab and over here click What-If Analysis and choose the Scenario Manager. Now, we haven't created any scenarios yet so that's why this is blank. And the way it works is you create sets of data.
Each set of data has a name that you give it. It's a friendly name. It's not a file name. You can call it just about anything you want. You typically give it names like low, medium, high, very high, whatever it is that suits your particular need. So let's start by clicking Add and let's say the scenario name we'll call this as Low amount. This will be perhaps the lowest amount that we would borrow. So, go over here for Changing cells and just delete whatever is in there and we want to select these three numbers here on the worksheet.
Now, keep in mind you don't want to select that Monthly payment because that is a calculated amount. So we don't change the calculated amount directly. We change the inputs that create it. And delete the comment here. Let's call this something like Lowest amount and click OK. So, now we can change those amounts. So, let's say the lowest amount we would borrow is maybe 80,000. So I type in 80,000. Maybe we figure that if we borrow 80,000 perhaps we could get a lower interest rate than 6%.
So, let's change that to maybe 5.5%. Maybe we could pay this off over fewer than 10 years. I'll just press the Tab key this time and say maybe we could pay that off over 8 years. Now, click OK and you see that Low amount scenario was put in the Scenario box. We have the cells were changing and that comment. Well, let's add a few more. So click Add, and maybe let's call this Original amount. The cells that we are changing should be the same, and let's change the comment here and we'll call this Original amount to borrow and click OK.
Now, we don't have to change anything of course because this is the original amount. Now, rather than clicking OK, just click Add. So, that saves us the step of going back into the main Scenario Manager dialog. So we can just add one scenario after another. It's just a little easier. So let's call this scenario A little more. I'll just press the Tab key, Changing cells, that's the same, the comment and let's say Borrowing slightly more and click OK. So let's say maybe for this we'll borrow 125,000. Maybe we'll estimate that we will get maybe a .0625% interest and because we are borrowing a little more, maybe we'll have to pay this off over 12 years and click Add. And let's say add just one more and we'll call this a High amount to borrow.
I'll just press Tab, leave the Changing cells alone, press Tab again and the comment is Highest amount, click OK. Let's say the highest amount we might borrow is 175,000 and I'll leave the interest rate alone for this. There is nothing that says that you have to change all of the values of all of your input cells. So I'll leave that 6% alone, but because we are borrowing a lot more maybe we'll have to pay this off over let's say 18 years and click OK. So now we have all of our scenarios.
So how do we go and use them? Just double-click any one. Double-click the Original amount, the Low amount, A little more, High amount. Now, let's say if you just want to leave it the way it is you can click Close. And at any time, you could back to the Data tab, back to What-If Analysis and bring up the Scenario Manager and just continue looking at them. But wouldn't it be great if we could see all of these all at the same time? Click Summary and this asks us, what kind of summary do we want? We could have a traditional scenario summary or we could take everything and put it into a PivotTable report.
We'll talk about PivotTables later in this course. So, let's choose Scenario summary. Now the Result cell is correctly guessing that it's that calculated amount, so click OK, and here we have our Scenario Summary and it shows us all of these named scenarios, and if you click any of these you notice in the Formula bar these are not linked to the original. These are simply copied and pasted. What you might want to do over here into Changing cells is instead of putting the cell references, give those meaningful names also.
See I not going to call this something like Present Value, Interest, Years and so forth, and you can adjust those column widths if you need to. Well, this will print out pretty much the way you see it. So either press Ctrl+P or go to your File tab, choose Print, and here's your preview. It kind of cutoff as you see over here. This is two pages. You might want to go instead of Portrait Orientation choose Landscape Orientation. Of course, that all depends on the size of the scenario you are creating. Now, let's just press the Escape key and come back out.
Now, just one more thing. These scenarios are not dynamic. So if you go back let's say into your original and you change these some more or you add or delete scenarios, this Scenario Summary sheet will not change. You'll have to create an additional scenario report, but that's okay because you could have as many as you like.
Get unlimited access to all courses for just $25/month.Become a member
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.
Your file was successfully uploaded.