Page 670 - Excel 2013 All-in-One For Dummies

652 Exploring Different Scenarios Exploring Different Scenarios
Excel enables you to create and save sets of input values that produce dif- ferent results as scenarios with the Scenario Manager option on the What-If Analysis button’s drop-down menu on the Data tab of the Ribbon. A scenario consists of a group of input values in a worksheet to which you assign a name, such as Best Case, Worst Case, Most Likely Case, and so on. Then, to reuse the input data and view the results that they produce in the work- sheet, you simply select the name of the scenario that you want to use, and Excel applies the input values stored in that scenario to the appropriate cells in the worksheet. After creating your different scenarios for a work- sheet, you can also use the Scenario Manager to create a summary report showing both the input values stored in each scenario and the key results produced by each.
Creating new scenarios
When creating a scenario for your worksheet, you create a spreadsheet
that uses certain cells that change in each scenario (appropriately enough, called changing cells). To make it easier to identify the changing cells in each scenario that you create (especially in any scenario summary reports that you generate), you should assign range names to the variables in the spread- sheet with the Name a Range or Create from Selection command buttons on the Formulas tab of the Ribbon before you create your scenarios.
To create your scenarios with the Scenario Manager, follow these steps:
1. Select the changing cells in the spreadsheet; that is, the cells whose values vary in each of your scenarios.
Remember that you can select nonadjacent cells in the worksheet by holding down the Ctrl key as you click them.
2. Click the What-If Analysis command button on the Ribbon’s Data tab and then click Scenario Manager on its drop-down menu or press Alt+AWS.
This action opens the Scenario Manager dialog box.
3. Click the Add button in the Scenario Manager dialog box.
This action opens the Add Scenario dialog box, similar to the one shown in Figure 1-5. The Add Scenario dialog box contains a Scenario Name text box, where you give the new scenario a descriptive name such as Best Case, Most Likely Case, and so on. This dialog box also contains a Changing Cells text box that contains the addresses of the variable cells that you selected in the worksheet, a Comment box that contains a note with your name and the current date, so you’ll always know when you created the particular scenario, and Protection check boxes that pre- vent users from making changes and/or enable you to hide the scenario when the worksheet is protected.