Excel 2010: How to Use Scenario Manager

show more Using Scenario Manager provides you with in-depth training on Business. Taught by Dennis Taylor as part of the Excel 2013 Essential Training show less
please wait ...

Using Scenario Manager

In this worksheet called, Scenario, we're looking at a 2014 Budget Projection. As I scroll to the right, we had monthly totals, quarterly totals, and a grand total in column R. And scrolling left toward and then downward, we see the items being tracked here. Sources of Revenue, Cost of Goods, our Expenses, and at the bottom in row 35 and Net Profit. And the lower right-hand corner cell R35, the Gross Profit for the entire 12-month period projected. That same number is reflected in the upper left-hand corner, as I press Ctrl+Home at 450,000.

We also see it here. A brief look at the formulas here, the February formulas for the entries we see here are based on the January entries with a 1% change. March at 1% change on February. Second quarter, our numbers are based on 3% changes. Ultimately, we might have a percent change in every cell here, but all the entries for example in the May column involve using the April numbers on a 3% change. April is based on March; March on February and so on.

Let's imagine that we might want to show these numbers with different results. And one option surely can be and relatively straightforward, we can make multiple copies of this sheet. A simple Ctrl+Drag, we could create a new sheet that's identical and then change the numbers. But it might be easier to work with if we had a quick, easy method of showing different scenarios within the same worksheet. So let's imagine that this is our first look at the numbers and we want to hold on to this, this 450,000 number.

But we're also thinking of showing different scenarios with lower sales or higher sales, maybe both or maybe a combination of what happens if we have a higher cost of goods, lower cost of goods. So we might have five or six different scenarios in mind. Let's create a couple. But let's first begin by storing or holding on to this scenario. And the feature that we're about to use is on the DATA tab, What-if Analysis, Scenario Manager. There are No Scenarios defined.

Now let's add a Scenario and let's just call this normal or typical or Normal is good enough. And we really don't want to change any cells here right now. But let's say looking ahead to our other scenarios, we might be changing cell B5 and also cell B10, put a comma we're going to change those two cells. And just those two although we certainly could change more, in fact we can change up to 31 different cells. Let's Click OK.

Now, right now we wanted to keep these settings 137,000; 76,000 for the two cells in question. We'll simply Click OK, simply to define the so called, Normal Scenario. Now, let's add a new scenario, and we'll call this one LowSales, or maybe LowSales-HighCost of Goods. Type it anyway you want. Changing cells, let's keep the same ones we had before although we didn't change them before, now we will, Click OK.

So the LowSales-HighCost of Goods model is going to project not 137,000 as our Sales, but let's say 130,000. And let's say our Cost of Goods have gone up to 80,000 under this scenario, probably the worst of all scenarios, these two numbers. We Click OK and we've just defined this. Now we're not seeing it yet, and we still see the 450,000 total, so let's show this scenario. Show and there it is 308,000.

We can show the Normal again, if we wish go back here; show that as we can bounce back and forth. There are other ways to do this too. Let's add a third scenario. This is going to go on the opposite direction, and we'll call this HighSales-LowCostofGoods. Perhaps the best of all possible worlds, Click OK. And this time, instead of 137,000, earlier we had 130,000 for the other direction; let's go up to 145,000.

And for the cost of goods, let them drop to 70,000. Click OK; we've just defined this scenario. Do you want to view it, of course, Click Show and there it is. Under this scenario we'll make $646,000. Now, we can always get to these scenarios, remember they are all on the same sheet, simply by going to the DATA tab and choosing What-if Analysis, go to Scenario Manager and pick the one we want to see and then Show it. But at any given time it could get a little slow, and what if we've got five or six of these? Having to keep this dialog box open or getting to it.

There might be a better way to do this. Oh, there is. If you Right-Click anywhere within the Quick Access Toolbar, typically this is located above the ribbon although it could be below, Right-Click anywhere there and go to Customize Quick Access Toolbar. And in the list here, you might see popular commands. Scrolling up and down, you don't see Scenario but there are other choices here. We also see commands not in the ribbon, of course they're alphabetized. And in this list here, you might see Scenario. There it is right there, Click this.

Because it has an arrow to the right that implies it's going to have a drop-arrow and it will. Then add this to the Quick Access Toolbar and then Click OK. So now without activating that command that we just saw, anytime we're on this worksheet, we've got this button here, we can Click the drop arrow. Let's view the HighSales-LowCostofGoods Scenario, there it is. Or at a different time, perhaps during a presentation, let's view the so called, Normal or Starting Point Scenario, there it is. Or of course the other one is there too.

And in some situations maybe we'll have five or ten a few more even. So this capability allows us to essentially store different scenarios within the same worksheet. So we don't need multiple worksheets here. And so in the example as we've just seen here, we've created three different scenarios easily accessible by a way of that button, or by a way of the command on the DATA tab.

Using Scenario Manager
Video duration: 6m 11s 6h 32m Appropriate for all


Using Scenario Manager provides you with in-depth training on Business. Taught by Dennis Taylor as part of the Excel 2013 Essential Training

Business Education + Elearning
please wait ...