Paul McFedries - Excel Data Analysis For Dummies

You can get a more accurate solution by increasing the number of iterations that Goal Seek can use, by reducing the value that Goal Seek uses to mark a solution as close enough, or both. Choose File ⇒ Options and then choose Formulas. Increase the value of the Maximum Iterations spin button, decrease the value in the Maximum Change text box, or both, and then click OK.

Many formulas require a number of input values to produce a result. Previously in this chapter, in the “ Working with Data Tables” section, I talk about using data tables to quickly see the results of varying one or two of those input values. Handy stuff, for sure, but when you’re analyzing a formula’s results, manipulating three or more input values at a time and performing this manipulation in some systematic way often help. For example, one set of values might represent a best-case approach, whereas another might represent a worst-case approach.

In Excel, each of these coherent sets of input values — known as changing cells — is called a scenario. By creating multiple scenarios, you can quickly apply these different value sets to analyze how the result of a formula changes under different conditions.

Excel scenarios are a powerful data-analysis tool for a number of reasons. First, Excel enables you to enter up to 32 changing cells in a single scenario, so you can create models that are as elaborate as you need. Second, no matter how many changing cells you have in a scenario, Excel enables you to show the scenario’s result with just a few taps or clicks. Third, because the number of scenarios you can define is limited only by the available memory on your computer, you can effectively use as many scenarios as you need to analyze your data model.

When building a worksheet model, you can use a couple of techniques to make the model more suited to scenarios:

Group all your changing cells in one place and label them.

Make sure that each changing cell is a constant value. If you use a formula for a changing cell, another cell could change the formula result and throw off your scenarios.

If scenarios sound like your kind of data-analysis tool, follow these steps to create a scenario for a worksheet model that you’ve set up:

1 Choose Data ⇒ What-If Analysis ⇒ Scenario Manager.The Scenario Manager dialog box appears.

2 Click Add.The Add Scenario dialog box appears.

3 In the Scenario Name box, type a name for the scenario.

4 In the Changing Cells box, enter the cells you want to change in the scenario.You can type the address of each cell or range, separating each by a comma, or you can select the changing cells directly in the worksheet.

5 In the Comment box, enter a description for the scenario. Your scenarios appear in the Scenario Manager dialog box, and for each scenario, you see its changing cells and its description. The description is often very useful, particularly if you have several scenarios defined, so be sure to write a detailed description to help you differentiate your scenarios later on.Figure 2-9 shows a worksheet model for a mortgage analysis and a filled-in Add Scenario dialog box. FIGURE 2-9:Creating a scenario for a mortgage analysis.

6 Click OK.The Scenario Values dialog box appears.

7 In the text boxes, enter a value for each changing cell.Figure 2-10 shows some example values for a scenario. FIGURE 2-10:Example values for a scenario’s changing cells.

8 To add more scenarios, click Add and then repeat Steps 3 through 7.

9 Click OK.The Scenario Values dialog box closes and then the Scenario Manager dialog box returns, showing the scenarios you’ve added.

10 Click Close.

The real value of a scenario is that no matter how many changing cells you’ve defined or how complicated the formula is, you can apply any scenario with just a few straightforward steps. Don’t believe me? Here, I’ll prove it:

1 Choose Data ⇒ What-If Analysis ⇒ Scenario Manager.The Scenario Manager dialog box appears.

2 Select the scenario you want to display.

3 Click Show.Without even a moment’s hesitation, Excel enters the scenario values into the changing cells and displays the formula result.

4 Feel free to repeat Steps 2 and 3 to display other scenarios. When it’s this easy, why not?

5 When you’ve completed your analysis, click Close.

If you need to make changes to a scenario, you can edit the name, the changing cells, the description, and the scenario’s input values. Here are the steps to follow:

1 Choose Data ⇒ What-If Analysis ⇒ Scenario Manager.The Scenario Manager dialog box appears.

2 Select the scenario you want to modify.

3 Click Edit.The Edit Scenario dialog box appears.

4 Modify the scenario name, changing cells, and comment, as needed.

5 Click OK.The Scenario Values dialog box appears.

6 Modify the scenario values, as needed.

7 Click OK.Excel returns you to the Scenario Manager dialog box.

8 Click Close.

If you have a scenario that has worn out its welcome, you should delete it to reduce clutter in the Scenario Manager dialog box. Here are the steps required:

1 Choose Data ⇒ What-If Analysis ⇒ Scenario Manager.The Scenario Manager dialog box appears.

2 Select the scenario you want to remove. Excel does not ask you to confirm the deletion, so double- (perhaps even triple-) check that you’ve selected the correct scenario.

3 Click Delete.Scenario Manager gets rids of the scenario.

4 Click Close.

Spreadsheet tools such as Goal Seek that change a single variable are useful, but unfortunately most problems in business are not so simple. You’ll usually face formulas with at least two and sometimes dozens of variables. Often, a problem will have more than one solution, and your challenge will be to find the optimal solution (that is, the one that maximizes profit, minimizes costs, or matches other criteria). For these bigger challenges, you need a more muscular tool. Excel has just the answer: Solver, a sophisticated optimization program that enables you to find the solutions to complex problems that would otherwise require high-level mathematical analysis.

Solver, like Goal Seek, uses an iterative method to perform its calculations. Using iteration means that Solver tries a solution, analyzes the results, tries another solution, and so on. However, this cyclic iteration isn’t just guesswork on Solver’s part. That would be silly. No, Solver examines how the results change with each new iteration and, through some sophisticated mathematical processes (which, thankfully, happen way in the background and can be ignored), can usually tell in what direction it should head for the solution.

Yes, Goal Seek and Solver are both iterative, but that doesn’t make them equal. In fact, Solver brings a number of advantages to the table:

Solver enables you to specify multiple adjustable cells. You can use up to 200 adjustable cells in all.

Solver enables you to set up constraints on the adjustable cells. For example, you can tell Solver to find a solution that not only maximizes profit but also satisfies certain conditions, such as achieving a gross margin between 20 and 30 percent or keeping expenses less than $100,000. These conditions are said to be constraints on the solution.