Chapter 9
Working with Data Analysis Tools
Create a Data Table
If you want to study the effect that different input values have on a formula, one solution is to set up the worksheet model and then manually change the formula’s input cells. For example, if you are calculating a loan payment, you can enter different interest rate values to see how this affects the payment.
The problem with this method is that you see only a single result at one time. A better solution is to set up a data table, which is a range that consists of the formula you are using and multiple input values for that formula. Excel automatically creates a solution to the formula for each different input value.
Create a Data Table
Type the input values:
To enter the values in a column, start the column one cell down and one cell to the left of the cell containing the formula, as shown here.
To enter the values in a row, start the row one cell up and one cell to the right of the cell containing the formula.
Select the range that includes the input values and the formula.
Click the Data tab.
Click What-If Analysis.
Click Data Table.
The Data Table dialog box appears.
Specify the formula cell you want to use as the data table’s input cell:
If the input values are in a column, type the input cell’s address in the Column Input Cell text box.
If you entered the input values in a row, type the input cell’s address in the Row Input Cell text box.
Click OK.
A Excel displays the results.
Create a Two-Input Data Table
If your worksheet model is a data table that varies only one of the formula’s input cells, then this is an example of a one-input data table. However, Excel also enables you to set up a two-input data table that varies two formula inputs at the same time. For example, in a loan payment worksheet, you could set up a two-input data table that varies the interest rate and the term.
To set up a two-input data table, you must set up two ranges of input cells. One range must appear in a column directly below the formula, and the other range must appear in a row directly to the right of the formula.
Create a Two-Input Data Table
Type the input values:
A To enter the column values, start the column one cell down and one cell to the left of the cell containing the formula.
B To enter the row values, start the row one cell up and one cell to the right of the cell containing the formula.
Select the range that includes the input values and the formula.
Click the Data tab.
Click What-If Analysis.
Click Data Table.
The Data Table dialog box appears.
In the Row Input Cell text box, type the cell address of the input cell that corresponds to the row values you entered.
In the Column Input Cell text box, type the cell address of the input cell you want to use for the column values.
Click OK.
C Excel displays the results.
Skip Data Tables When Calculating Workbooks
You can reduce the time it takes for Excel to calculate a workbook if you configure Excel to bypass data tables when it is recalculating the workbook. Because a data table is an array, Excel treats it as a unit, so a worksheet recalculation means that the entire data table is always recalculated. This is not a big problem for a small data table with a few dozen formulas. However, it is not uncommon to have data tables with hundreds or even thousands of formulas, and these larger data tables can really slow down worksheet recalculation. To avoid this problem, you can configure Excel to skip data tables when it calculates worksheets.
Skip Data Tables When Calculating Workbooks
Click the File tab.
Click Options.
The Excel Options dialog box appears.
Click Formulas.
Click the Automatic Except for Data Tables option (
changes to 
).
Click OK.
The next time you calculate a workbook, Excel bypasses the data tables.
Analyze Data with Goal Seek
If you already know the formula result you want, but you must find an input value that produces that result, you can use Goal Seek to solve the problem. For example, you might know that you want to have $50,000 saved to purchase new equipment five years from now, so you need to calculate how much to invest each year.
You must do three things to set up your worksheet for Goal Seek. First, you need to set up one cell as the changing cell, which is the value that Goal Seek will manipulate to reach the goal. Second, you must set up the other input values for the formula and give them proper initial values. Third, you need to create a formula for Goal Seek to use to reach the goal.
Analyze Data with Goal Seek
Set up your worksheet model.
Click the Data tab.
Click What-If Analysis.
Click Goal Seek.
The Goal Seek dialog box appears.
Click in the Set Cell box.
Click the cell that contains the formula you want Goal Seek to work with.
In the To Value text box, type the value that you want Goal Seek to find.
Click in the By Changing Cell box.
Click the cell that you want Goal Seek to modify.
Click OK.
A Goal Seek adjusts the changing cell value until it reaches a solution.
B The formula now shows the value you entered in step 7.
Click OK.
Analyze Data with Scenarios
You can analyze the result of a formula by creating sets of values that enable you to use those values quickly as the inputs for the formula. Many formulas take a number of input values to produce a result. When you are analyzing the formula’s results, it helps to manipulate the input values in some systematic way. For example, one set of values might represent a best-case approach, while 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 easily apply these different value sets to analyze how the result of a formula changes under different conditions.
Analyze Data with Scenarios
Create a Scenario
Set up your worksheet model.
Click the Data tab.
Click What-If Analysis.
Click Scenario Manager.
The Scenario Manager dialog box appears.
Click Add.
The Add Scenario dialog box appears.
Type a name for the scenario.
Click in the Changing Cells box.
Select the cells you want to change in the scenario.
Type a description for the scenario.
Click OK.
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 mouse 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.
Analyze Data with Scenarios
The Scenario Values dialog box appears.
Use the text boxes to specify a value for each changing cell.
A To add more scenarios, click Add and then repeat steps 6 to 11.
Click OK.
Click Close.
Display Scenarios
Click the Data tab.
Click What-If Analysis.
Click Scenario Manager.
The Scenario Manager dialog box appears.
Click the scenario you want to display.
Click Show.
B Excel enters the scenario values into the changing cells and displays the formula result.
Repeat steps 4 and 5 to display other scenarios.
Click Close.
Understanding Solver
Spreadsheet tools such as Goal Seek that change a single variable are useful, but unfortunately, most problems in business are not so easy. You will 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, or minimizes costs, or matches other criteria). For these bigger challenges, you need a more muscular tool. Excel has just the answer: Solver. Solver is a sophisticated optimization program that enables you to find the solutions to complex problems that would otherwise require high-level mathematical analysis.
Solver Advantages
Solver, like Goal Seek, uses an iterative method to perform its calculations. This means that Solver tries a solution, analyzes the results, and tries another solution, and so on. However, this cyclic iteration is not just guesswork on Solver’s part. The program looks at how the results change with each new iteration and through some sophisticated mathematical processes, can usually tell in what direction it should head for the solution. However, the fact that Goal Seek and Solver are both iterative does not 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 at less than $100,000. These conditions are said to be constraints on the solution.
• Solver seeks not only a desired result (the goal in Goal Seek), but also the optimal one. This means you can find a solution that is the maximum or minimum possible.
• For complex problems, Solver can generate multiple solutions. You can then save these different solutions under different scenarios.
Using Solver
Solver is a powerful tool that most Excel users do not need. It would be overkill, for example, to use Solver to compute net profit given fixed revenue and cost figures. Many problems, however, require nothing less than the Solver approach. These problems cover many different fields and situations, but they all have the following characteristics in common:
• They have a single objective cell (also called the target cell) that contains a formula you want to maximize, minimize, or set to a specific value. This formula could be a calculation, such as total transportation expenses or net profit.
• The objective cell formula contains references to one or more variable cells (also called unknowns or changing cells). Solver adjusts these cells to find the optimal solution for the objective cell formula. These variable cells might include items such as units sold, shipping costs, or advertising expenses.
• Optionally, one or more constraint cells must satisfy certain criteria. For example, you might require that advertising be less than 10 percent of total expenses, or that the discount to customers be an amount between 40 and 60 percent.
Understanding Solver’s Messages
When Solver finishes its calculations, it displays the Solver dialog box and a message that tells you what happened. Some of these messages are straightforward, but others are more cryptic. This section looks at the most common messages and gives their translations.
Solver Finds a Solution
If Solver finds a solution successfully, you see one of the messages shown in the following table.
|
Message |
Description |
|
Solver found a solution. All constraints and optimality conditions are satisfied. |
This is the message you hope to see. It means that the value you wanted for the objective cell has been found, and Solver was able to find the solution while meeting your constraints within the precision and integer tolerance levels you set. |
|
Solver has converged to the current solution. All constraints are satisfied. |
Solver normally assumes that it has a solution if the value of the objective cell formula remains virtually unchanged during a few iterations. This is called converging to a solution. Such is the case with this message, but it does not necessarily mean that Solver has found a solution. The iterative process might just be taking a long time, or the initial values in the variable cells might have been set too far from the solution. You should try rerunning Solver with different values. You can also try using a higher precision setting (that is, entering a smaller number in the Constraint Precision text box). |
|
Solver cannot improve the current solution. All constraints are satisfied. |
This message tells you that Solver has found a solution, but it might not be the optimal one. Try setting the precision to a smaller number or, if you are using the GRG Nonlinear engine, try using the central differencing method for partial derivatives. |
Solver Does Not Find a Solution
If Solver does not find a solution, you see one of the messages in the following table.
|
Message |
Description |
|
The Set Cell values do not converge. |
This means that the value of the objective cell formula has no finite limit. For example, if you are trying to maximize profit based on product price and unit costs, Solver will not find a solution; The reason is that continually higher prices and lower costs lead to higher profit. You need to add (or change) constraints in your model, such as setting a maximum price or minimum cost level (for example, the amount of fixed costs). |
|
Solver could not find a feasible solution. |
Solver could not find a solution that satisfied all your constraints. Check your constraints to make sure they are realistic and consistent. |
|
Stop chosen when the maximum x limit was reached. |
This message appears when Solver reaches either the maximum time limit or the maximum iteration limit. If it appears that Solver is heading toward a solution, click Keep Solver Solution and try again. |
|
The conditions for Assume Linear Model are not satisfied. |
Solver based its iterative process on a linear model, but when the results are put into the worksheet, they do not conform to the linear model. You need to select the GRG Nonlinear engine and try again. |
Load the Solver Add-In
An add-in is software that adds one or more features to Excel. Installing add-ins gives you additional Excel features that are not available in the Ribbon by default. Bundled add-in software is included with Excel but is not automatically installed when you install Excel. Excel comes with several standard add-ins, including Solver, which enables you to solve optimization problems.
You install the bundled add-ins by using the Excel Options dialog box; you can find them in the Add-Ins section. Once installed, add-ins are available right away. They usually appear on a tab related to their function. For example, Solver appears on the Data tab.
Load the Solver Add-In
Click the File tab.
Click Options.
The Excel Options dialog box appears.
Click Add-Ins.
Click the Manage down arrow and select Excel Add-Ins from the drop-down list.
Click Go.
Excel displays the Add-Ins dialog box.
In the Add-Ins Available list, click the Solver Add-In check box (
changes to 
).
Click OK.
Excel adds a Solver button to the Data tab’s Analysis group.
EXTRA
When you click OK in the Add-Ins dialog box, Excel first checks to see if Solver is installed on your computer. This is usually the case, but if Excel finds that Solver is not installed, it displays a dialog box to let you know. In this case, click Yes to have Excel install the add-in. If you find that you no longer use Solver, you should remove it to save memory and reduce clutter in the Ribbon’s Data tab. Follow steps 1 to 5 to display the Add-Ins dialog box. In the Add-Ins Available list, deselect the Solver Add-In check box ( changes to ), and then click OK.
Optimize a Result with Solver
You can set up your Solver model by using the Solver Parameters dialog box. You use the Set Objective field to specify the objective cell, and the To group to tell Solver what you want from the objective cell: the maximum possible value, the minimum possible value, or a specific value. Finally, you use the By Changing Variable Cells box to specify the cells that Solver can use to plug in values to optimize the result.
When Solver finds a solution, you can choose either Keep Solver Solution or Restore Original Values. If you choose Keep Solver Solution, Excel permanently changes the worksheet. You cannot undo the changes.
Optimize a Result with Solver
Click the Data tab.
Click Solver.
The Solver Parameters dialog box appears.
Type a reference to the objective cell.
In the To group, select an option:
A Click Max to return the maximum possible value.
B Click Min to return the minimum possible value.
C Click Value Of and type a number to set the objective cell to that number.
In the By Changing Variable Cells box, type the cells you want Solver to change while it looks for a solution.
Click Solve.
As Solver works on the problem, you might see one or more Show Trial Solution dialog boxes.
Click Continue.
Repeat step 7 as often as necessary.
When the optimization is complete, Excel displays the Solver Results dialog box.
D The Solver result message appears here.
Click the Keep Solver Solution option ( changes to ).
E If you do not want to accept the result, click the Restore Original Values option instead ( changes to ).
Click OK.
Add Constraints to Solver
The real world puts restrictions and conditions on formulas. A factory might have a maximum capacity of 10,000 units a day, the number of employees in a company has to be a number greater than or equal to zero, and your advertising costs might be restricted to 10 percent of total expenses. Similarly, if you are running a break-even analysis on two products, without restrictions Solver might set one product at a slight loss and the other at a slight profit, whereas in your analysis you might prefer to set both products at the break-even point. These are all examples of what Solver calls constraints. Adding constraints tells Solver to find a solution so that these conditions are not violated.
Add Constraints to Solver
Click the Data tab.
Click Solver.
The Solver Parameters dialog box appears.
Specify the objective cell, optimization value, and variable cells.
Click Add.
Excel displays the Add Constraint dialog box.
In the Cell Reference box, type the cell you want to constrain.
Click here and then click the operator you want to use.
If you chose a comparison operator in step 6, use the Constraint box to enter the value by which you want to restrict the cell.
To specify more constraints, click Add and repeat steps 5 to 7.
Click OK.
Excel returns to the Solver Parameters dialog box.
A Excel displays your constraints in the Subject to the Constraints list box.
Click Solve.
If you see one or more Show Trial Solution dialog boxes (not shown), click Continue in each.
When the optimization is complete, Excel displays the Solver Results dialog box.
B The Solver result message appears here.
Click the Keep Solver Solution option ( changes to ).
C If you do not want to accept the result, click the Restore Original Values option instead ( changes to ).
Click OK.
Save a Solver Solution as a Scenario
If you use Solver frequently to try out different types of analyses on the same set of data, you can make your analysis faster and more efficient by saving your solutions as scenarios that you can recall quickly. Whenever you have a spreadsheet model that uses a coherent set of input values — known as changing cells — you have what Excel calls a scenario. With Solver, these changing cells are its variable cells, so a Solver solution amounts to a kind of scenario.
However, Solver does not give you an easy way to save and rerun a particular solution. To work around this problem, you can save a solution as a scenario that you can later recall using the Excel Scenario Manager feature.
Save a Solver Solution as a Scenario
Click the Data tab.
Click Solver.
The Solver Parameters dialog box appears.
Specify the objective cell, optimization value, variable cells, and constraints.
Click Solve.
If you see one or more Show Trial Solution dialog boxes (not shown), click Continue in each.
When the optimization is complete, Excel displays the Solver Results dialog box.
Click Save Scenario.
Excel displays the Save Scenario dialog box.
Type a name for the scenario.
Click OK.
Excel returns you to the Solver Results dialog box.
Click the Keep Solver Solution option ( changes to ).
A If you do not want to accept the result, click the Restore Original Values option instead ( changes to ).
Click OK.