From the course: Excel Tips Weekly

Use Scenario Manager to store and rapidly recall different sets of values - Microsoft Excel Tutorial

From the course: Excel Tips Weekly

Start my 1-month free trial Buy for my team

Use Scenario Manager to store and rapidly recall different sets of values

“

- [Speaker] Excel has a number of capabilities under the category of What-If analysis. One of 'em is called a scenario manager. And on this very busy worksheet that we're seeing in the background here, there are three different scenarios. And the idea behind this is maybe these numbers are interrelated and what you'd like to be able to do is to show how these numbers are going to change based on just a few cells changing and maybe have those different projections quickly available. Now one approach could be, we could make a copy of this worksheet, make three or four copies and have each one be different. We could bounce back and forth that way. The scenario manager technique makes sense and it's easier to get to and easier for display purposes as well. And the image that you're seeing off to the right here that I'm moving around is what we will see on the data tab up in the ribbon menu system. Way up to the right under What-If analysis. You will see Scenario manager if the active cells within the data, we see it there and in this worksheet, it's already set up. Here's the summary of what was set up. We don't have to go through every option, but if right now, I go to What-If analysis and Choose Scenario Manager, there are three different scenarios there. And the key number that we're seeing in the background, we'll talk more about it in a bit, is the number we see in the upper-left hand corner in cell A-2, almost 9.6 million. If I click in the Scenario manager, lower revenue and click Show, that number has changed. And a lot of the numbers in the worksheet in the background have changed too. So there are total, and that's by the way, our net profit is only 7 million. If I click the other option here under Scenario manager for higher revenue and then show it's 12 million, 12.8 million. So I've got another worksheet that looks exactly like this. We'll show you how to set up Scenario Manager. Next worksheet over budget 2026. Now I'm going to zoom in a little bit here and also point out a couple things. All the numbers except for those in column B are by way of formulas. Some of you may know that on the formulas tab in the ribbon, if you press show formulas off to the right, this case everything seems to be a formula except for most of the data in column B. Some of those are formulas there too, but a lot of initial entries. But there's other changes there. There's other numbers in the columns to the right of column B are going to be based on formulas that have to do with the data in column B. And by the way, on that show formulas button, you can either click it again or press control tilde. Don't use the shift key, just that Tilde symbol, Ctrl tilde, that's really handy. So we're going to set up scenario manager here and the focus is going to be at least momentarily here on the data in column B, these two entries right here. And the entry here, in other words, this is our income here, revenue and down here are expenses. We could focus on all three of them, but to make it simpler, I'll just focus on the cost of goods. So let's start the process here on the data tab off to the right. What-if analysis, scenario manager, we're going to add a scenario and because we want to be able to bounce back and forth and get to the current scenario, we'll give that a name as well too. And you could call it, you know, first look or standard or give it some kind of a name that makes sense. I'll just call it standard. In other words, the way the data looks now. By changing which cells, now we're not going to change any cells here for the standard scenario, but we do want to highlight the cells that will be changing for the other ones. Highlight these two cells, here, comma, and the one down here, in other words, B4, B5, and B9. Click OK, and we see it here. Sales, shipping and goods. Click OK and we can show it. Nothing really has changed. And that number, by the way, in the upper-left hand corner is actually calculated in the lower-right and simply being displayed here, but it hasn't changed because of standard. Let's add another scenario and we'll call this one, low revenue or low sales. Use different terms, we can always come back and change it or edit it too. We click OK. And here, we're going to be saying the sales, instead of 1.6 million, they're going to be 1.5 million. And the shipping here is going to be lower too. Our shipping income instead of being 400,000 is going to be 350,000. We'll leave the cost of goods the same for this one and click OK. And let's show that low sales and that number in the upper-left hand corner is the key one that we're looking at. Instead of nine point, almost 6 million, now it's 7.6 million. So we've got two different scenarios here. We can jump back and forth. There's standard, we could show it, there's low sales, we can show it. Let's add another one. You can imagine how many times we might want to do this too. We can do up to 32 of them, believe it or not. I'm going to add another one that says high sales. Now granted, this is pretty simplistic what we're doing here and the worksheet model is pretty simplistic too, but it gives the basic idea, we're going to use high-sales now, click OK. And here if it's high-sales, remember the standard or the beginning was 1.6 million. We're going to make this be 1.7 million. We're also going to up our shipping income. It had been for the standard of 400,000, now it's going to be 450,000. And the cost of goods, let's say it's lowered, that's going to make our profit be even better. We'll take that from 800,000 down to 700,000. Click OK, haven't seen the results yet, but here's high-sales, show and it goes up to 12 million. And so what we have at our fingertips then is this ability to show these. And you could imagine coming up with different combinations here perhaps of what if we had high revenue and also cost of goods going up at the same time, come up with some kind of a name. The name could be quite longer too. But let's close this and also show you a different way to get to it. And you could imagine doing this with different worksheets if you're in a financial environment and if you need to evaluate different scenarios here, if you right click up in the quick access toolbar that's above the menu. If you've altered this yourself, you probably are seeing more icons and I'm seeing up here right now. But if I right click anywhere up there, I can customize the quick access toolbar. And in the category right here over on the left-hand side, choose commands from, click the drop arrow here. If we choose all commands and then click in here, press the letter S, first of all, and then scroll down a bit till we see scenario in here. It's alphabetical, of course, scenario manager, add, and OK, that's now the top of our screen. We can just click it. It activates the scenario manager. We don't have to go through the data tab necessarily. I want to see the low-sales option, can double click it. There's the low-sales option, double click, that's the high-sales option or back to standard. And again, we can give these different names by editing and so on. So I think, you can see the potential value of this. This icon, by the way, is going to stay there if you have another workbook that you open and it has scenarios. These scenarios will adjust properly. In other words, the button will properly show you the scenarios in that workbook as well too. So it's a feature worth-exploring that worksheets don't necessarily have to be as complex or involved as this one is. But nevertheless, I think you could see from the example how this is probably a better way to store different scenarios than having different worksheets. The scenario manager initially found on the data tab under what-if analysis.

Contents