Often, when you've built a worksheet, you'll want to experiment with different data in it. For example, if you're setting budgets, you may need to play around with different figures for the different departments to get the overall balance you require.
You can experiment with different data by changing the values directly in your worksheet—but then you may need to restore your original values afterward. Another approach is to create multiple copies of the worksheet (or of the workbook) and then change the values in the copies, leaving the original untouched. This works fine, but if you then need to change the original worksheet (for example, adding another column of data), the changes quickly become messy.
Instead of working in these awkward ways, you can use Excel's scenarios. A scenario is a way of entering different values into a set of cells without changing the underlying values. You can switch from one scenario to another as needed, and you can merge scenarios from different versions of the same worksheet into a single worksheet.
Start by creating the workbook and worksheet you'll use for your scenarios. If you have an existing workbook with the data in it, open it up. Set up the data and formulas in your worksheet as usual.
TIP: To make your scenarios easy to set up and adjust, define a name for each cell that you will change in the scenario. Click the cell, choose Insert Name Define to display the Define Name dialog box, and then work as described in the “Assigning a Name to a Cell or Range” section in Chapter 3. For example, type the name in the Names In workbook text box, and then click the Add button to add the name and leave the dialog box open so that you can click another cell and define a name for it, too.
Figure 11–7 shows the sample worksheet this section uses as an example. The worksheet summarizes the financial returns from a modest portfolio of rental properties.
Figure 11–7. To start using scenarios, create a worksheet containing your existing data and the formulas needed.
Here's what you see on the worksheet:
Times are bad, and the total profit is too low. So, we'll use scenarios to see how we can improve matters by raising rents and shaving costs.
When you're ready to start working with scenarios, choose Data Analysis What-If Scenario Manager from the Ribbon or Tools Scenarios from the menu bar to display the Scenario Manager dialog box. At first, when the workbook contains no scenarios, the Scenario Manager dialog box appears as shown in Figure 11–8.
Figure 11–8. At first, the Scenario Manager dialog box contains nothing but a prompt telling you to click the Add button. Do so.
After opening the Scenario Manager dialog box, you can create your scenarios.
TIP: First, create a scenario containing your original data. This gives you an easy way to go back to the original data when you've finished testing scenarios.
To create a scenario, follow these steps:
Figure 11–9. Use the Add Scenario dialog box to set up each scenario. First create a scenario for your original data so that you can easily return to it.
NOTE: When you enter a cell or a range of cells in the Changing Cells text box by clicking in the worksheet, Excel changes the name of the dialog box from Add Scenario to Edit Scenario. Odd though this seems, it's normal.
Figure 11–10. In the Scenario Values dialog box, enter the values to use for the new scenario you're creating. The cell names produce the labels (Rent_Lincoln and so on), which are much easier to refer to than cell addresses (for example, B5).
TIP: You can type formulas into the text boxes in the Scenario Values dialog box. For example, to increase the Rent_Lincoln value in Figure 11–10 by 25 percent, you could change the 1450 value to the formula =1.25*1450. After you enter formulas like this, Excel displays the dialog box shown in Figure 11–11 when you close the Scenario Values dialog box, telling you that it has converted names and results of formulas to values. Click the OK button.
Figure 11–11. After you enter formulas in the Scenario Values dialog box, Excel automatically converts them to values for you.
To add another scenario, click the Add button in the Scenario Manager dialog box, and then repeat the previousprocess.
If you selected either the Prevent Changes check box or the Hide check box in the Protection area of the Add Scenario dialog box or the Edit Scenario dialog box, you need to protect the worksheet to make the protection take effect.
To protect the worksheet, follow these steps:
After protecting the scenarios in the worksheet like this, you'll need to turn off the protection before you can edit the scenarios. To turn off the protection, choose Tools Protection Sheet from the Ribbon or Tools Protection Unprotect Sheet from the menu bar, type the password in the dialog box that Excel displays, and then click the OK button.
From the Scenario Manager dialog box, you can quickly edit a scenario by clicking it in the Scenarios list box, clicking the Edit button, and then working in the Edit Scenario dialog box. Excel automatically updates the scenario's comment for you with details of the modification (for example, “Modified by Jack Cunningham on 02/18/2011”), but you'll often want to type in more details, such as what you're trying to make the scenario show.
When you no longer need a scenario, delete it by clicking the scenario in the Scenarios list box and clicking the Delete button. Excel doesn't confirm the deletion; but if you delete a scenario by mistake, and recovering it is more important than losing any other changes you've made since you last saved the workbook, you can recover the scenario by closing the workbook without saving changes (assuming the scenario was already saved in the workbook).
Once you've created multiple scenarios for the same worksheet, you can switch among them by clicking the scenario you want in the Scenarios list box in the Scenario Manager dialog box (see Figure 11–12) and then clicking the Show button. Excel displays the scenario's figures in the worksheet's cells. The Scenario Manager dialog box stays open, so you can quickly switch to another scenario.
Figure 11–12. To switch to another scenario, click the scenario in the Scenarios list box in the Scenario Manager dialog box, and then click the Show button.
TIP: If you need to be able to switch quickly among scenarios, add the Scenario pop-up menu to the Standard toolbar or another convenient toolbar. To customize the toolbar, use the technique described in the “Customizing the Toolbars with the Commands You Need” section in Chapter 2. On the Commands tab of the Customize Toolbars and Menus dialog box, click the Tools item in the Categories list, and then scroll down the Commands list box to find the Scenario pop-up menu.
If you develop and share your scenarios in a single workbook, you can keep them all together. But sometimes you may need to develop your scenarios in separate workbooks and then combine them. You can do this easily by using the Merge command in the Scenario Manager dialog box.
To merge scenarios, follow these steps:
Figure 11–13. In the Merge Scenarios dialog box, choose the workbook and worksheet that contain the scenarios you want to merge into the active workbook.
NOTE: If any scenario you merge into the active worksheet has the same name as an existing scenario in the worksheet, Excel adds the current date to the incoming scenario to distinguish it.
Sometimes you can make the decisions you need by simply creating scenarios and looking at them in the worksheet. At other times, it's helpful to create a report from the scenarios so that you can compare them. Excel gives you an easy way to create either a summary report or a PivotTable report straight from the Scenario Manager dialog box.
To create a report from your scenarios, follow these steps:
Figure 11–14. In the Scenario Summary dialog box, choose between a scenario summary and a scenario PivotTable, and then select the result cells for the summary.
Figure 11–15. Excel places a summary report on a new worksheet named Scenario Summary at the beginning of the workbook.
18.189.186.167