Join Curt Frye for an in-depth discussion in this video Creating substitute data sets using scenarios, part of Excel 2013: Shortcuts.
Excel is the terrific program for analyzing and storing large datasets, and you can also use it for forecasting. For example, you might want to forecast growth per quarter for an upcoming fiscal year. In this movie, I will show you how to extend your analytical abilities by creating scenarios. I will use the Scenario sample file, which you can find in the Chapter 4 folder of your Exercise Files archive. This workbook contains a single worksheet with projections for growth for the four quarters of the next fiscal year.
What I want to do is define scenarios for an optimistic scenario and a pessimistic scenario and also to record the starting values. I can do that by creating scenarios. I'll start by clicking the Data tab on the Ribbon, then click What-If Analysis, and click Scenario Manager. In the Scenario Manager dialog box, I'll click the Add button, and now I can type, in the Scenario name box, a name for this first scenario, and I'll call it Optimistic.
I want to affect a particular group of cells, so I'll delete the reference to cell B6, and then in the body of the worksheet I'll select cells B6 through E6. In other words, I want to select every cell in that particular range. If I wanted to select individual cells, I could hold down the Ctrl key and click those cells in the worksheet. But in this case I want to work with them all, so that's fine. I'll click OK to move to the next screen, which allows me to enter scenario values.
So for cell B6, I'll make it 20% or .2, press the Tab key. C6 is now .25. I'll make that .3. Press Tab. D6 is .15. I'll make that .3. I'll press the Tab key and I'll make cell E6 .4, and click OK. So now my scenario appears inside of the Scenario Manager dialog box. Now I'll create a second scenario called Pessimistic, so I click the Add button, and I'll type Pessimistic in the Scenario name box.
Again, I want to affect cells B6 through E6, so I can leave the value in the Changing cells box constant and click OK. Now I can type in my worst-case scenario values. So for cell B6 I'll type in .05 for 5%, and for C6 I'll type in .1, press Tab, .1, and .1. So basically 5% in first quarter and 10% every quarter thereafter and click OK.
Now I have two scenarios with separate sets of values. There is one thing you need to watch out for when you work with scenarios. If you apply a scenario to a worksheet and then save and close your workbook, then the scenario values overwrite the original workbook values. In other words, if you haven't done anything to retain your original data, then overwriting those cells with the scenario, saving and closing your workbook, gets rid of the original data and you can't bring it back. Therefore, what I like to do is to create a scenario that contains my original values.
So, to do that, I'll click Add, and my scenario name is Original. Again, it's cells B6 through E6. Click OK and now I don't have to change any of the values. The original values are .1, .25, .15, and .3, so I can just click OK, and doing so adds that scenario. So now, as long as I don't erase it, I have my original values saved. If you want to apply a scenario to your worksheet, you can just click the scenario we want to apply--in this case Optimistic--and click Show, and I have the new values of 20, 30, 30, and 40%.
If I want to display Pessimistic scenario, click Pessimistic and Show. And if I want to get back to my Original values, because I created an original scenario, I can click Show, after I click Original and those values come back. Now let's say then I apply the Pessimistic scenario values by clicking it and clicking Show and clicking Close to get rid of the Scenario Manager dialog box. Now if I want to undo the change--in other words go back to the previous state of the worksheet before I applied the Pessimistic scenario-- I can press Ctrl+Z to undo the change.
So what are some other things you can do with scenarios? Well, if I go back into the Scenario Manager dialog box, again on the Data tab, clicking What-If Analysis, and clicking Scenario Manager, I can delete a scenario just by clicking Delete. To edit the scenario, if you say click Pessimistic and then click Edit, then that you can edit the Scenario name, the cells it applies to. If you click OK, you can edit the values that are in the scenario and so on. And if you click Cancel, you don't make any changes.
You can also create a scenario summary, and this is extremely useful. Now, before you create a scenario summary, make sure that your original values are displayed in your worksheet. Whether you've undone any changes that you've made because of scenarios or you've applied your original scenario doesn't matter; just to make sure your original values are there. You'll see why in a second. To create a scenario summary worksheet, click Summary, and then in the Report type area, make sure the Scenario summary option button is selected and click OK.
Doing so displays a summary of all the scenarios you've created on a new worksheet called Scenario Summary. So here you have the current values, the cells that are changed, and the scenarios here. And again, the current values reflect the current state of your worksheet. So that means that if you have your original values displayed, those values will show there. But like I said, it's important to keep your original data in your workbook, and for that reason, I always create a copy of the original workbook and work on the copy instead of the original data.
That way if I do make a mistake and override some of the data, at least the original is preserved, and I can go back and work with it.
- Entering a data series using the fill handle
- Creating hyperlinks
- Setting a print area
- Applying a table style
- Creating substitute data sets using scenarios
- Wrapping and shrinking text
- Creating a data entry form
- Removing duplicate values
- Inserting the current date or time
- Generating a list of unique random numbers
- Calculating running totals
- Analyzing a formula for errors
- Summarizing data with charts and PivotTables