PART II Calculate, Manipulate, and Analyze Data
CHAPTER 12 Solve Problems by Performing What-If Analysis
Color profile: Generic CMYK printer profile Composite Default screen
How to…
■ Create data tables to assess the impact of variables
■ Explore alternative data sets with scenarios
■ Solve problems using Goal Seek
■ Use the Solver to manipulate two or more values
In Chapter 11, you learned how to create PivotTables and PivotCharts to manipulate and analyze data in a database so that it yielded the answers to particular questions. In this chapter, you’ll learn how to perform what-if analysis to examine the outcome of particular results or circumstances.
First, you’ll learn how to create data tables that enable you to assess what impact one or two variables have on a calculation. Then you’ll learn how to use Excel’s scenarios to explore the effects of alternative data sets within the same worksheet. At the end of the chapter, I’ll discuss how to solve one-variable problems using Goal Seek and how to use the Solver to solve multivariable problems.
Create Data Tables to Assess the Impact of Variables
If you need to assess the impact of a single variable or two variables on a calculation, the tool to use is adata table.A data table is an automated way of entering an array formula in a range of cells so as to display the results of using different values in one formula or multiple formulas.
Data tables are sometimes also calledsensitivity tables.
Create a Single-Variable Data Table
The easiest type of data table to create is a single-variable data table. You must lay out a single- variable data table so that its input values (the values you want to test) either run down a column or run across a row. In other words, you can’t place the input values in a range of cells that spans multiple rowsandmultiple columns.
Excel feeds input values to a data table through a cell called theinput cell. You enter the input cell in the formula (or formulas) in place of one of the values or references for which you want to test the input values. The input cell must be blank (otherwise, Excel uses the cell’s value in the formula, which defeats the point of the exercise) and can be anywhere on the worksheet. In most cases, using a cell adjacent to the range that contains the input values is clearest and least confusing.
Following is an illustrated example of creating a single-variable data table. The example uses the =DB() function, which (as you saw in “Financial Functions,” in Chapter 7) calculates the depreciation of an asset over a specified year in its life by using the fixed-declining balance method. The example uses a data table to display the depreciation for each year of the asset’s life instead of displaying only one year.
12
CHAPTER 12: Solve Problems by Performing What-If Analysis 241
HowTo-Tght (8)/ How to Do Everything with Microsoft Office Excel 2003 / Hart-Davis / 3071-1 / Chapter 12
To create the single-variable data table, follow these steps:
1. Enter the supporting data for the calculation in the range B1:B4, together with identifying labels in the range A1:A4:
2. Enter the input values down one column or across one row:
■ Leave at least one blank row before the first input value in a column. Leave at least one blank column before the first input value in a row.
■ Entering the input values down a column creates acolumn-orienteddata table. Entering the input values across a row creates arow-orienteddata table. This example creates a column-oriented data table.
■ You can type or paste the values as usual, but if the values vary by a consistent amount, you may also be able to use AutoFill instead to enter them quickly:
3. Enter the formula in the appropriate cell:
■ For a column-oriented data table, enter the formula in the row immediately above the first input value and in the next column to the right:
Color profile: Generic CMYK printer profile Composite Default screen
■ For a row-oriented data table, enter the formula in the column to the left of the first input value and the next row down.
■ Enter the input cell in place of the appropriate argument in the formula. Be warned that the formula will likely display an error value (as in this example) or an obviously incorrect value. This is because the input cell is blank and, therefore, receives a zero value. As you’ll see in a moment, the data table works fine even with an error value appearing as the formula result.
4. Select the range of cells that contains the formula (or formulas) and the input values:
5. Choose Data | Table to display the Table dialog box:
6. Enter the cell reference for the input cell by typing or by selecting the cell in the worksheet:
■ For a column-oriented data table, enter the cell reference in the Column Input Cell text box. In this example, enter the cell reference$A$5.
12
CHAPTER 12: Solve Problems by Performing What-If Analysis 243
HowTo-Tght (8)/ How to Do Everything with Microsoft Office Excel 2003 / Hart-Davis / 3071-1 / Chapter 12
■ For a row-oriented data table, enter the cell reference in the Row Input Cell text box.
7. Click the OK button to close the Table dialog box. Excel creates the data table, entering the array formula{=TABLE(,A5)}in each results cell:
Notice that, in this example, the cell that contains the formula still displays the #NUM! error because the input cell is blank. However, the range B7:B12 displays the correct output.
Add Further Formulas to a Data Table
If necessary, you can use two or more formulas in a single-variable data table. If you need to use more than one formula in a data table, place them as follows:
■ For a column-oriented data table, enter the formulas in the cells to the right of the first formula.
■ For a row-oriented data table, enter the formulas in the cells below the first formula.
Here’s an example of adding a second formula, DDB, to the data table created in the previous section. As you’ll remember from “Financial Functions,” in Chapter 7, DDB calculates depreciation using the double-declining balance method, so the resulting data table lets you compare the depreciation in each year of the asset’s life using each depreciation method.
To add the second formula to the data table, follow these steps:
1. Enter the formula=DDB(B1,B2,B3,A5)in cell C6, the cell to the right of the first formula:
Color profile: Generic CMYK printer profile Composite Default screen
2. Select the range that contains the input range and the two formulas. In this case, select the range A6:C12:
3. Choose Data | Table to display the Table dialog box.
4. Enter the cell reference for the same input cell as for the previous formula—in this case,
$A$5—in the Column Input Cell text box.
5. Click the OK button to close the Table dialog box. Excel adds the second formula to the data table, entering the array formula{=TABLE(,A5)}in each results cell:
Create a Two-Variable Data Table
Single-variable data tables can help you assess what happens when a single piece of information in a calculation changes. But often you’ll need to assess what happens when two pieces of information change. For example, when calculating the depreciation of an asset, you might need to assess what happens not only for different periods of its life but also for different salvage values of the asset at the end of its life.
To create a two-variable data table, you enter the second set of input data in the other dimension from the first set: if the first set of input data is in a column, you enter the second set of input data in a row across the top of the results area, and vice versa. You place the formula at the intersection of the input data row and the input data column.
12
Here’s an example of creating a two-variable data table along the same lines as the single- variable data table created earlier in this chapter. As you can see, I’ve moved some of the items and dressed up the table so it’s easier to read, but the principle is the same:
To create this two-variable data table (without all the formatting and labels), follow these steps:
1. Enter the first series of input data (1, 2, 3, 4, 5, 6) in the range D4:D9. This range will be linked to the column input cell.
2. Enter the second series of input data ($1000, $2000, $3000, $4000, $5000, $6000) in the range E3:J3. This range will be linked to the row input cell.
3. Enter the formula=DB(B1,B6,B3,B7)in cell D3, at the intersection of the input column and the input row. B6 is the row input cell, and B7 is the column input cell. As before, the formula produces an error result (#NUM!) in its cell because it receives zero values from the two input cells.
4. Choose Data | Table to display the Table dialog box.
5. EnterB6in the Row Input Cell text box.
6. EnterB7in the Column Input Cell text box.
7. Click the OK button to close the Table dialog box. Excel creates the data table, entering the array formula{=TABLE(B6,B7)}in each results cell.
Change, Copy, or Move a Data Table
Once you’ve created a data table, you can manipulate its contents only by changing the input values or the formula. You can’t directly change the contents of any results cell, because Excel implements the data table as an array formula. So to change the contents of a data table, you need to clear the data table (as described in “Clear a Data Table,” next) and then create it again from scratch.
However, you can copy the results of the data table to a different location by using Copy and Paste. When you do so, Excel copies not the array formulas themselves but the results of the formulas.
You can move a data table in its entirety by selecting it and using drag and drop. When you move a data table, Excel changes the references in the formulas but otherwise leaves the array formulas intact.
CHAPTER 12: Solve Problems by Performing What-If Analysis 245
HowTo-Tght (8)/ How to Do Everything with Microsoft Office Excel 2003 / Hart-Davis / 3071-1 / Chapter 12 Color profile: Generic CMYK printer profile
Composite Default screen
Clear a Data Table
Because a data table consists of an array formula, you have to clear the whole of it at once rather than just part of it. (See “Work with Array Formulas,” in Chapter 8, for an explanation of array formulas.) If you try to clear just part of the data table, Excel displays this error message box:
To clear the values from the data table, select the range of cells that contains the values, and then choose Edit | Clear | Contents or pressDELETE. Make sure you don’t select any formula cells.
To clear a data table entirely, select every cell in the range it occupies, including all cells that contain formulas, and then choose Edit | Clear | All.
Explore Alternative Data Sets with Scenarios
Excel’s scenarios feature lets you define and use alternative data sets within the same workbook.
Instead of creating a separate version of a workbook and using it to experiment with different values or different formulas, you can use scenarios to experiment more comfortably without damaging your main workbook. Better yet, you can create a what-if model in a workbook, share it with your colleagues so that they can admire your scenarios and perhaps create their own, and track the results of the changes your colleagues make to the scenarios.
Create the Worksheet You Want to Manipulate with Scenarios
The first step in using scenarios is to create the worksheet you want to manipulate and to define names for the cells whose values will be manipulable in the scenarios. Defining names isn’t necessary, because you can refer to cells by their references instead, but names make the process so much clearer that you’ll almost always want to define them.
Create the worksheet by using the methods you’ve learned so far in this book. Figure 12-1 shows the worksheet I’ll use for examples in the following sections. It summarizes the sales, costs, profit, profitability, and contribution to profitability of the six categories of products that the microbrewery we’ve visited already in this book makes.
The worksheet is relatively straightforward:
■ The figures in the Sales column are total sales figures drawn from the underlying worksheets. The total at the bottom of the column adds the sales figures together.
■ The figures in the Costs column are total costs figures (production and distribution costs) drawn from the underlying worksheets. The total at the bottom of the column adds the costs figures together.
12
CHAPTER 12: Solve Problems by Performing What-If Analysis 247
HowTo-Tght (8)/ How to Do Everything with Microsoft Office Excel 2003 / Hart-Davis / 3071-1 / Chapter 12
■ The figures in the Profit column are calculated by subtracting each product’s costs from its sales. The total at the bottom of the column adds the profit figures together.
■ The percentages in the Profitability column are calculated by dividing each product’s profit by its total sales. The figure at the bottom of the column is the overall profitability, calculated by dividing the total profit by the total sales.
■ The percentages in the Contribution column are calculated by dividing each product’s profit by the company’s total profit (cell D11). The total at the bottom of the column adds the contribution figures to confirm that they represent 100 percent and that nothing is missing.
The brewery’s management team will use scenarios to examine what happens when they change the figures in the Sales column and the Costs column. To help the team see instantly which value they’re manipulating, each of the figures in the Sales column and the Costs column (apart from the totals) has a descriptive name defined for it: Health_Sales, Health_Costs, Feedstuffs_Sales, Feedstuffs_Costs, and so on. The longer names are shortened a little (Health instead of Health Products, Std Lager instead of Standard Lager, and so on) because the Scenario Values dialog box truncates longer labels.
After creating the worksheet, save it (pressCTRL-S) before proceeding.
Open the Scenario Manager Dialog Box
To work with scenarios, you use the Scenario Manager dialog box (choose Tools | Scenarios).
Figure 12-2 shows the Scenario Manager dialog box as it first appears when you display it in a FIGURE 12-1 The sample worksheet used for scenarios
Color profile: Generic CMYK printer profile Composite Default screen
Create a Scenario for Your Starting Point
Before you add any other scenarios, create a scenario that represents the starting point for the worksheet. This scenario enables you and other users to easily return to the starting values and assumptions for the worksheet.
To create a scenario for your starting point, follow these steps:
1. If the Scenario Manager dialog box isn’t already displayed, choose Tools | Scenarios to display it.
2. Click the Add button to display the Add Scenario dialog box (Figure 12-3).
3. Enter the name (for example,Starting Scenario) in the Scenario Name text box.
4. Click in the Changing Cells text box, and then select the cells in the spreadsheet that will be changeable in the scenario:
■ Click and drag to select contiguous cells.CTRL-click to add noncontiguous cells to the current selection.
■ Excel automatically collapses the Add Scenario dialog box while you select cells in the worksheet, so you don’t need to click the Collapse Dialog button to collapse the dialog box manually. Excel restores the dialog box after you finish making a selection.
■ After you make a selection, Excel changes the dialog box’s title from Add Scenario to Edit Scenario. Otherwise, the dialog box remains the same.
FIGURE 12-2 Use the Scenario Manager dialog box to create and manipulate scenarios.
12
CHAPTER 12: Solve Problems by Performing What-If Analysis 249
HowTo-Tght (8)/ How to Do Everything with Microsoft Office Excel 2003 / Hart-Davis / 3071-1 / Chapter 12
5. Enter a comment (if appropriate) in the Comment text box.
6. Select or clear the Prevent Changes check box and the Hide check box as necessary.
See the next section, “Add Further Scenarios,” for details on these check boxes.
7. Click the OK button to close the Edit Scenario dialog box. Excel displays the Scenario Values dialog box, displaying the current values for each of the changeable cells:
8. For the starting scenario, you don’t need to change the existing values. Click the OK button to close the Scenario Values dialog box. Excel returns you to the Scenario Manager dialog box.
FIGURE 12-3 Start by using the Add Scenario dialog box to create a scenario that represents the starting point for the worksheet.
Color profile: Generic CMYK printer profile Composite Default screen
Add Further Scenarios
To add another scenario, repeat the steps you took to create the starting scenario, but with these differences:
■ Enter a different (and descriptive) name for the scenario in the Add Scenario dialog box.
■ Change the selection of changeable cells only if necessary. Excel automatically suggests those cells that are defined in the first scenario you defined.
■ Change the appropriate values in the Scenario Values dialog box to effect changes in the worksheet. As well as typing values, you can enter formulas in the Scenario Values dialog box to change the existing cell contents. For example, to see what effect a 25 percent decrease in costs would look like, enter=.75*before the existing value. Excel displays this message box to tell you that it has converted the formula result to a value;
click the OK button:
■ Select the Prevent Changes check box if you want to prevent changes to the scenario. After selecting this check box, you need to implement protection by using a Tools | Protection | Protect Sheet command.
See “Protect Cells, a Worksheet, or a Workbook,” in Chapter 14, for an explanation of protecting worksheets and workbooks.
■ Select the Hide check box if you want to hide the scenario from other users. After selecting this check box, you need to implement protection by using a Tools | Protection | Protect Sheet command.
Edit and Delete Existing Scenarios
To edit an existing scenario, select its entry in the Scenarios list box in the Scenario Manager dialog box, click the Edit button, and then work in the Edit Scenario dialog box. Excel automatically adds details of the modification to the comment attached to the scenario—for example,Modified by Jason Acme on 11/22/2003. After making such edits as are needed, click the OK button.
12
CHAPTER 12: Solve Problems by Performing What-If Analysis 251
HowTo-Tght (8)/ How to Do Everything with Microsoft Office Excel 2003 / Hart-Davis / 3071-1 / Chapter 12
To delete a scenario, select its entry in the Scenarios list box in the Scenario Manager dialog box and click the Delete button. Excel deletes the scenario without confirmation.
Switch from One Scenario to Another
To switch from one scenario to another, follow these steps:
1. Choose Tools | Scenarios to display the Scenario Manager dialog box.
2. Select the scenario in the Scenarios list box.
3. Click the Show button to display the scenario in the workbook.
4. Click the Close button to close the Scenario Manager dialog box.
That’s easy enough, but it takes a handful of clicks or keystrokes. If you need to switch more easily from one scenario to another, add the Scenario drop-down list to a toolbar that you keep displayed on screen. You’ll find the Scenario drop-down list in the Tools category on the Commands tab of the Customize dialog box. (See “Customize Toolbars,” in Chapter 17, for instructions on customizing toolbars.) You can then switch instantly from one scenario to another by using the Scenario drop-down list:
Merge Scenarios into a Single Worksheet
Often, you’ll need to share workbooks containing scenarios with your colleagues so that they can create new scenarios. When you receive the workbooks back, you can merge the scenarios they contain back into your master workbook. You can also use Excel’s scenario-merging capability to merge scenarios from one worksheet into another worksheet.
To merge scenarios, follow these steps:
1. Open each workbook that contains scenarios you want to merge.
2. Activate the workbook and worksheet into which you want to merge the scenarios.
3. Choose Tools | Scenarios to display the Scenario Manager dialog box.
Color profile: Generic CMYK printer profile Composite Default screen