When Excel Has One Too Many What-If's Put the Scenario Manager To Work Helen Bradley
Wed 5/22/02 -- One of the fundamental uses of a spreadsheet is to ask and answer what-if questions -- questions like, "What will the effect be if our cost of goods goes up 5 percent?" (Or, if you live in California, "What will happen if our power bill doubles again this year?") With a simple model, it's possible to ask and answer such questions almost instantaneously. However, taking note of all the variable inputs and possible outcomes of your calculations can be time-consuming. Enter Microsoft Excel's scenario tool, which can do all the recording and reporting work for you.
How it works. Excel's Scenario Manager lets you test various inputs in a model and record the results as you make the calculations. If you're in the food business, for example, you can test variables such as markups and spoilage rates and save each set of figures for recall at a later date. You can also create a report to display the various inputs and results for side-by-side comparison and further analysis.
Create a scenario. You can create a scenario from almost any worksheet. Begin by saving the initial worksheet as the first scenario so you won't lose your original figures. To do this, select the area of your worksheet that contains the input values (cells C4:C7 in the screen below) and choose Tools/Scenarios/Add. Type a name for the scenario which describes its inputs (so you can identify it easily in future); we've chosen "60% Markup, 20% Spoilage." Click OK and the dialog will show you the input cells and their values; click OK again to accept these.
Naming input cells. You may have noticed that, when the Scenario Manager displays your input cells for you, they're referred to using cell references. As you can have up to 32 changing cells in any one scenario, checking values based on cell references quickly becomes confusing. A simple solution is to name the cells.
To do this, close Scenario Manager, select the input cells and those containing their descriptions (in our sample, cells B4:C9) and choose Insert/Name/Create. Select the "Left column" checkbox (or whichever option indicates the location of the cell names) and click OK. Now, instead of cell references, the contents of the cells will appear in the Scenario Values dialog and Summary report (see below).
Adding new scenarios. Once you've identified your original data as the starting scenario, you can go ahead can test other inputs. In this case, we'll see what happens to pricing if we reduce spoilage to 15 percent and markup to 50 percent. You can do this in one of two ways -- either change the data in the worksheet and create it as a scenario, as you did for the original data, or create the scenario using Scenario Manager.
For the latter, choose Tools/Scenarios/Add. Type a descriptive name and click OK; in the Scenario Values dialog, enter the inputs relevant to this new scenario. When you're done, click OK to end. You can now click Show to change the values in the worksheet to reflect the figures appropriate to this scenario.
Creating Scenario Reports. Once you have a few sets of input values entered into Scenario Manager, you'll appreciate how useful a report showing the different inputs and results can be. To make this report, choose Tools/Scenarios/Summary. Choose the "Scenario summary" option. In the Results area, enter the cell or cells containing the calculations you want to analyze (in our example, cell C9). Click OK.
You'll see a new sheet in your workbook called Scenario Summary, which shows (side by side, with one scenario per column) the scenario name; the input cell values; and the results. This lets you easily compare the scenarios you've created.
Other Scenario options. You can use the scenario tool alongside other Excel tools such as Goal Seek. In our example, we could use the Goal Seek tool to calculate the maximum spoilage that would allow us to maintain a certain sale price, given a certain markup percentage. When the Goal Seek calculation is complete, simply choose Tools/Scenarios/Add and add the scenario (the input data will be entered already) to the list.
If you're using the Solver Add-in, there's a Save Scenario option in the Solver Results dialog that you can use to save that tool's results as a new scenario. You can then use the Scenario Manager tools to examine and report on the results.
