Using Scenario Manager

- In this worksheet called Scenario in workbook 13 - DataAnalysis, we've got some budget projections. All the numbers, although it's not obvious at first, are really based on what's happening in January. Formulas in February, March, April, May, and all the other months ultimately are based on the January numbers. Now, if we'd like to show alternate budget projections, we could easily make a copy of this worksheet, make three or four copies, and on each worksheet labeled differently we'd have a different set of numbers. And nothing wrong with that.

But Scenario Manager, a feature found on the Data tab in the ribbon it's under What-If Analysis allows us to essentially store all of these kinds of projections in the same worksheet. It begins with the idea that we might want to hold on to the projections we've already done. Maybe we'll call this our Standard projection. So What-If Analysis, Scenario Manager. No scenarios have been defined. Let's add a scenario that simply defines the current one. We'll give it a name something like Standard or Normal, and we're not going to be changing any cells here but we will for the other scenarios.

So we have to provide some cells to refer to and it could be quite a few of them. I'm going to highlight cells B5 and B6. Now remember, I said other monthly entries are based on the January entries. We're also going to, with a comma first, select this cell down here and simply click OK. We're not going to be changing these cells so we'll simply OK. We are defining what the standard scenario is. Now, we might have in mind four or five different variations.

I'm only going to show two. Let's imagine we want to show some other projections where the sales are lower, and in this worksheet cell A2 contains our so-called bottom-line number. It's the same number that we would see if I closed the Scenario Manager here momentarily scrolling rightward and going to the lower right corner of this worksheet. There's the number. That's our profit based on this scenario. It's also displayed in the upper left corner so we can see it more easily.

Let's go back to Scenario Manager under What-If Analysis. Let's now define a low sales scenario we could call Low or Low Sales something like that. So we're going to add a new scenario. I'll just call it Low, click OK. It's going to prompt me to change these other cells. Under the Low scenario, instead of Sales in January being 1.6 million, maybe they're going to be just 1.5 million. And the Shipping Income, instead of being $320,000 maybe is only going to be $275,000.

The Cost of Goods $918,000, maybe that's going to go up maybe to a million. We could call this the Pessimistic scenario, but we've called it Low and we click OK. We've defined that but we're not seeing it. Remember, the current projection shows, or the Standard projection is showing $5,127,000. Let's show the Low scenario, go a negative one. We might want to reconsider that so we could come back here, choose Low right now, and edit the scenario.

So in our Low scenario here we click OK. We go back here and we've got to reconsider some of these numbers. Perhaps it's the Sales number and looks like I put in here $15,000. That's really low. I meant to put in $1.5 million. There we are. Now let's take a look at this. Come back here and show this. So the Low scenario, $1.6 million. Go back to Standard, show that, $5.1 million.

Quite a difference. Let's add another one. We'll call this High. Click OK. It's going to ask us to change the same cells. The High scenario instead of $1.64 million in sales is going to be, let's say, $1.8 million So that's 1-8-0-0-0-0-0. The Shipping Income instead of $320,000 which it is under the Standard scenario is going to be $400,000, and the Cost of Goods instead of being $918,000 we're going to drop this to be $850,000.

So instead of $5.1 million roughly for the projection which is Standard, we're now defining the High scenario. But we haven't shown it yet so let's click Show. Instead of $5 million, we're up to $9 million. So we can come back here at any time and show these other scenarios just by clicking Show and all the numbers on the worksheet that are relevant are changing. So at the moment we're showing one or the other. Now, that means we've got to come back to this dialog box each time though. Nothing really wrong with that, but that might get a little cumbersome.

Maybe we'll have five or six different kinds of projections. And we're not using different sheets so we've got the advantage here of sticking on this same worksheet as we view these different scenarios. But there's another feature that could make this even easier to read. I'm going to right-click on the Quick Access toolbar. It's above the ribbon menu. Right-click and Customize the Quick Access Toolbar. And under Popular Commands, we're not going to find the feature we need. But if we click the drop arrow here and choose Commands Not in the Ribbon, we've got quite a few choices here.

Scroll down into the letter S, we'll see an option eventually in here called Scenario. It's got an arrow to the right of it. Click it, and then add this to the Quick Access Toolbar. Click OK. It doesn't take up a lot of space, but drop arrow, let's look at the Low projection, or click it again, let's look at the High projection, and that's handy. Jump back and forth. Now the button takes up a little bit of space and it's going to be there all the time unless you remove it so there's a slight downside to that, but any time we come to a worksheet like this or any worksheet that has scenarios, we can click this and depending upon which worksheet it is that has different scenarios, we can make the choice we want.

And you could imagine having more than just three projections here too. So we have these at our fingertips and we can easily click back and forth as we watch the numbers change. And you can imagine how powerful this might be for certain kinds of presentations. Scenario Manager, one of the tools found in What-If Analysis on the Data tab.