Excel 2016 All-in-One For Dummies (2016)
Book VII
Data Analysis
Explore your pivot table possibilities in the article “Using the Excel 2016 Quick Analysis Tool” online at www.dummies.com/extras/excel2016aio.
Contents at a Glance
1. Chapter 1: Performing What-If Scenarios
1. Using Data Tables
2. Exploring Different Scenarios
3. Hide and Goal Seeking
4. Using the Solver
2. Chapter 2: Performing Large-Scale Data Analysis
1. Creating Pivot Tables
2. Formatting a Pivot Table
3. Sorting and Filtering the Pivot Table Data
4. Modifying the Pivot Table
5. Creating Pivot Charts
6. Using the Power Pivot and Power View Add-Ins
7. Using the Power Map feature
8. Creating Forecast Worksheets
Chapter 1
Performing What-If Scenarios
In This Chapter
Doing what-if analysis in one- and two-variable data tables
Creating and playing with different scenarios
Performing goal seeking
Using the Solver utility
Because electronic spreadsheet formulas are so good at automatically updating their results based on new input, they have long been used (and sometimes, misused) to create financial projections based on all sorts of assumptions. Under the guise of what-if analysis, you will often find the number crunchers of the company using Excel as their crystal ball for projecting the results of all sorts of harebrained schemes designed to make the company a fast million bucks.
As you start dabbling in this form of electronic fortune-telling, keep in mind that the projections you get back from this type of analysis are only as good as your assumptions. So when the results of what-if analysis tell you that you’re going to be richer than King Midas after undertaking this new business venture, you still need to ask yourself whether the original assumptions on which these glowing projections are based fit in with real-world marketing conditions. In other words, when the worksheet tells you that you can make a million bucks of pure profit by selling lead-lined boxer shorts, you still have to question how many men really need that kind of protection and are willing to pay for it.
In Excel, what-if analysis comes in a fairly wide variety of flavors (some of which are more complicated than others). In this chapter, I introduce you to three simple and straightforward methods:
· Data tables enable you to see how changing one or two variables affects the bottom line. (For example, you may want to know what happens to the net profit if you fall into a 45 percent tax bracket, a 60 percent tax bracket, and so on.)
· Goal seeking enables you to find out what it takes to reach a predetermined objective, such as how much you have to sell to make a $20 million profit this year.
· Scenarios let you set up and test a wide variety of cases, all the way from the best-case scenario (profits grow by 20 percent) to the worst-case scenario (in which you don’t make any profit).
At the end of the chapter, I introduce you to the Solver add-in utility, which enables you to find solutions to more complex what-if problems involving multiple variables. You can use the Solver to help you with classic resource problems, such as finding the correct product mix in order to maximize your profits, staffing to minimize your general costs, and routing to minimize transportation costs.
Using Data Tables
In an Excel spreadsheet, you can see the effect of changing an input value on the result returned by a formula as soon as you enter a new input value in the cell that feeds into the formula. Each time you change this input value, Excel automatically recalculates the formula and shows you the new result based on the new value. This method is of limited use, however, when you are performing what-if analysis and need to be able to see the range of results produced by using a series of different input values in the same worksheet so that you can compare them to each other.
To perform this type of what-if analysis, you can use Excel’s Data Table command. When creating a data table, you enter a series of input values in the worksheet, and Excel uses each value in the formula that you specify. When Excel is finished computing the data table, you see the results produced by each change in the input values in a single range of the worksheet. You can then save the data table as part of the worksheet if you need to keep a record of the results of a series of input values.
When creating data tables, you can create a one-variable or a two-variable data table. In a one-variable data table, Excel substitutes a series of different values for a single input value in a formula. In a two-variable data table, Excel substitutes a series of different values for two input values in a formula.
Creating a one-variable data table
To create a one-variable data table, you need to set up the master formula in your worksheet and then, in a different range of the worksheet, enter the series of different values that you want substituted for a single input value in that formula. Figures 1-1 and 1-2 demonstrate how this is done.
Figure 1-1: Creating a one-variable data table.
Figure 1-2: The completed one-variable data table.
In Figure 1-1, cell B5 contains a simple formula for computing the projected sales for 2017, assuming an annual growth rate of 1.75% over the annual sales in 2016. The 2017 projected sales in this cell are calculated with the following formula:
=Sales_2106+(Sales_2016*Growth_2017)
This formula adds cell B2 (named Sales_2016) to the contents of B2 multiplied by the growth rate of 1.75% in cell B3 (named Growth_2017). Cell B5 shows you that, assuming an annual growth rate of 1.75% in the year 2017, you can project total sales of $890,312.50.
But what if the growth rate in 2017 is not as low as 1.75%, or what if the growth rate is even (heaven forbid) lower than anticipated? To create the one-variable table to answer these questions, you first bring forward the master formula in cell B5 to cell C7 with the formula =B5 (which appears as =Projected_Sales_2017 on the formula bar as this is the range name given to the cell). Then, you enter the series of different growth rates as the input values in column B, starting in cell B8. (Cell B7, at the intersection of the row with the master formula and the column with the input values, must be left blank in a one-variable data table.) This series of input values for the data table can be created with the AutoFill feature. (See Book II, Chapter 1 for details.) In this example, a data series that increments each succeeding value by 0.25% percent is created in the cell range B8:B17, starting at 1.00 percent and ending at 3.25% percent.
After bringing the formula in cell B5 forward to cell C7 with the linking formula =B5 and generating the growth rate series in the cell range B8:B17, you then select the cell range B7:C17 and click the What-If Analysis command button in the Data Tools group on the Data tab and then click Data Table on its drop-down menu (or press Alt+AWT) to open the Data Table dialog box shown in Figure 1-1.
In this dialog box, you specify the row input cell in the Row Input Cell text box and/or the column input cell in the Column Input Cell text box. The cell that you designate as the row or column input cell in the Table dialog box must correspond to the cell in the worksheet that contains the original input value that is fed into the master formula.
In the data table in this example, you need to designate only B3 as the column input cell. (In the case of Figure 1-1, when you click this cell or use an arrow key to select this cell, Excel enters the absolute cell reference, as in $B$3.) You choose cell B3 because this is the cell that contains the growth rate value used in the master formula.
After indicating the row or column input cells, Excel computes the data table when you click the OK button. In this example, the program creates the data table by substituting each input value in the data series in the range B8:B17 into the column input cell B3. The value of cell B3 is then used in the master formula to calculate a new result, which is entered in the corresponding cell in the cell range C8:C17. After the program has finished calculating the data table, Excel returns the original value to the row or column input cell (in this case, 1.75% in cell B3).
Figure 1-2 shows the completed data table. Here, you can see at a glance how changing a quarter percentage point for the growth rate affects the projected sales for 2017. After creating the data table, you can then format the results and save the table as part of the worksheet.
If you want to see how using a different range of variables affects the results in the table, you only need to enter the new input values in the existing range. By default, Excel automatically recalculates the results in the output range of a data table whenever you change any of its input values. If you want to control when each data table is recalculated while still allowing the formulas in the worksheet to be automatically recalculated, click the Automatic Except Data Tables option on the Options command button on the Formulas tab (Alt+MXE).
Excel computes the results in a data table by creating an array formula that uses the TABLE function. (See Book III, Chapter 1, for more information on array formulas.) In this example, the array formula entered into the cell range C8:C17 is as follows:
{=TABLE(,B3)}
The TABLE function can take two arguments, row_ref and/or column_ref, which represent the row input cell and column input cell for the data table, respectively. In this example, the data table uses only a column input cell, so B3 is the second and only argument of the TABLE function. Because Excel enters the results in a data table by using an array formula, Excel won’t allow you to clear individual result cells in its output range. If you try to delete a single result in the data table, Excel displays an Alert dialog box, stating that you can’t change part of a table.
If you want to delete just the results in the output range of a data table, you must select all the cells in the output range (cell range C8:C17, in the current example) before you press the Delete key or choose the Clear All option from the Clear button’s drop-down menu (or press Alt+HEA).
Creating a two-variable data table
When you have a master formula in a worksheet in which you want to see the effect of changing two of its input values, you create a two-variable data table. When you create a two-variable data table, you enter two ranges of input values to be substituted in the master formula: a single-row range in the first row of the table and a single-column range in the first column of the data table. When you create a two-variable data table, you place a copy of the master formula in the cell at the intersection of this row and column of input values.
Figure 1-3 shows the typical setup for a two-variable data table. This figure uses the projected sales worksheet shown previously in the section on a one-variable data table. Here, however, a second variable has been added to projecting the total sales in 2017. This worksheet contains a value in cell B4 (named Expenses_2017) that shows the projected percentage of expenses to sales, which is used, in turn, in the master formula in cell B5 as follows:
=Sales_2016+(Sales_2016*Growth_2017)-(Sales_2016*Expenses_2017)
Figure 1-3: Creating a two-variable data table.
Note that when you factor in the expenses of 10% of the annual sales, the projected sales at an annual growth rate of 1.75% falls in cell B5 from $890,312.50 to $802,812.50.
To determine how changing both the growth rate and the percentage of expenses to sales will affect the projected sales for 2017, you create a two-variable data table. In setting up this table, you still enter the variable growth rates down column B in the cell range B8:B17. Then, you enter the variable expense rates across row 7 in the range C7:F7. This time, you bring forward the master formula by entering the formula =B5 in cell B7, the cell at the intersection of the row and column containing the two input variables.
After setting up the two series of variables in this manner, you are ready to create the table by selecting the cell range B7:F17 and opening the Table dialog box, as shown in Figure 1-3. For a two-variable data table, you must designate both a row input and column cell in the worksheet. In this example, the row input cell is B4, which contains the original expense-to-sales percentage, and the column input cell remains B3, which contains the original growth rate. After these two input cells are entered in the Table dialog box, you are ready to generate the data table by clicking the OK button.
Figure 1-4 shows the completed two-variable data table with the results of changing both the projected growth rate and the projected expenses. As with a one-variable data table, you can save this two-variable data table as part of your worksheet. You can also update the table by changing any of the (two types) input variables.
Figure 1-4: The completed two-variable data table.
The array formula entered in the output range (C8:F17) to create this two-variable data table is very similar to the one created previously for the one-variable data table, only this time the TABLE function uses both a row_ref and column_ref argument as follows:
{=TABLE(B4,B3)}
Remember that because this data table used an array formula, you must select all the cells in the output range if you want to delete them.
Exploring Different Scenarios
Excel enables you to create and save sets of input values that produce different 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 worksheet, 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 worksheet, 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 spreadsheet 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 prevent users from making changes and/or enable you to hide the scenario when the worksheet is protected.
4. Type a descriptive name for the new scenario in the Scenario Name text box.
Now, you should check over the cell references in the Changing Cells text box to make sure that they’re correct. You can modify them if necessary by clicking the Changing Cells text box and then by clicking the cells in the worksheet while holding down the Ctrl key. You can also edit the note in the Comment box if you want to add more information about your assumptions as part of the new scenario.
By default, Excel protects a scenario from changes when you turn on protection for the worksheet (see Book IV, Chapter 1, for details) so that you can’t edit or delete the scenario in any way. If you want Excel to hide the scenario as well when worksheet protection is turned on, click the Hide check box. If you don’t want to protect or hide the scenario when worksheet protection is turned on, click the Prevent Changes check box to remove its check mark, and leave the Hide check box as it is.
5. In the Protection section of the Add Scenario dialog box, choose what kind of scenario protection that you need, if any, with the Prevent Changes and Hide check boxes.
Now you’re ready to specify the changing values for the new scenario.
6. Click OK in the Add Scenario dialog box.
This action closes the Add Scenario dialog box and then opens the Scenario Values dialog box, similar to the one shown in Figure 1-6. The Scenario Values dialog box numbers and shows the range name (assuming that you named each of the cells), followed by the current value for each of the changing values that you selected in the worksheet before starting to define different scenarios for your spreadsheet.
You can accept the values shown in the text box for each changing cell if it suits the current scenario that you’re defining, or you can increase or decrease any or all of them as needed to reflect the scenario’s assumptions.
7. Check the values in each changing cell’s text box and modify the values as needed.
Now you’re ready to close the Scenario Values dialog box, which completes the definition of the new scenario.
8. Click the Add button in the Scenario Values dialog box.
This action closes the Scenario Values dialog box and returns you to the Add Scenario dialog box, where you can define a new scenario name for the changing cells.
9. Repeat Steps 4 to 7 to add all the other scenarios that you want to create.
After you finish defining all the different scenarios you want to apply to the changing values in the spreadsheet, you can close the Scenario Values dialog box and then return to the Scenario Manager dialog box, where you can use the Show button to see how using different sets of changing values affects your spreadsheet.
10. Click OK in the Add Values dialog box and then click the Close button in the Scenario Manager dialog box.
Figure 1-5: Adding a new Most Likely scenario for the sales forecast.
Figure 1-6: Specifying the changing values in the Scenario Values dialog box.
When you return to the Scenario Manager dialog box, the names of all the scenarios that you added appear in the Scenarios list box. For example, in Figure 1-7, you see that three scenarios — Most Likely, Best Case, and Worst Case — are now listed in the Scenarios list box.
Figure 1-7: Spreadsheet after showing the Worst Case scenario.
To show a particular scenario in the worksheet that uses the values you entered for the changing cells, you simply double-click the scenario name in this list box or click the name and then click the Show command button. Figure 1-7 shows the results in the sample forecast worksheet after showing the Worst Case scenario.
If, after creating the scenarios for your worksheet, you find that you need to use different input values or you want to add or remove scenarios, you can edit the scenarios in the Scenario Manager dialog box. To modify the scenario’s name and/or the input values assigned to the changing cells of that scenario, click the scenario name in the Scenarios list box and then click the Edit button so that you can make the appropriate changes in the Edit Scenario dialog box. To remove a scenario from a worksheet, select the scenario’s name in the Scenarios list box and then click the Delete button. Note, however, that if you delete a scenario in error, you can’t restore it with the Undo command. Instead, you must re-create the scenario by using the Add command button as outlined previously.
You can also merge scenarios from other Excel workbook files that are open. (Of course, the workbooks must share the same spreadsheet layout and changing cells.) To merge a scenario into the current worksheet from another workbook, click the Merge button in the Scenario Manager dialog box and then select the workbook from the Book drop-down list box and the worksheet from the Sheet drop-down list box before you click OK. Excel then copies all the scenarios defined for that worksheet and merges them with any scenarios that you’ve defined for the current worksheet.
Producing a summary report
After creating the different scenarios for your worksheet, you can use the Summary button in the Scenario Manager dialog box to create a summary report that shows the changing values used in each scenario and, if you want, key resulting values that each produces. When you click the Summary button, Excel opens a Scenario Summary dialog box, similar to the one shown in Figure 1-8, where you may designate a cell selection of result cells in the Result Cells text box to be included in the report. After selecting the result cells for the report, click OK to have Excel generate the summary report and display it in a new worksheet window.
Figure 1-8: Designating the result cells in the Scenario Summary dialog box.
In the example shown in Figure 1-8, the cell range C7:G7, containing the projected income figures for the sales forecast, are designated as the result cells to be included in the summary report. Figure 1-9 shows the actual summary report generated for this sample worksheet in a new document window. Note that because all the changing and result cells in this worksheet are named, the summary report uses their range names in place of their cell references. Also, when the Scenario Manager generates a summary report, it automatically outlines the summary data, thus creating two vertical levels — one for the changing cells and another for the result cells.
Figure 1-9: A Scenario Summary report for the scenarios in the Sales Forecast table.
After generating a summary report, you can save it by clicking the Save command button on the Quick Access toolbar (Ctrl+S) and/or print it by clicking the Quick Print command button (Ctrl+P).
Note that the Scenario Summary dialog box contains an option, Scenario PivotTable Report, which enables you to view the scenario results as a pivot table. See Book VII, Chapter 2, for details on the uses of pivot tables.
Hide and Goal Seeking
Sometimes, you know the outcome that you want to realize in a worksheet, and you need Excel to help you find the input values necessary to achieve those results. This procedure, which is just the opposite of the what-if analysis that I’ve been examining in this chapter, is referred to asgoal seeking.
When you simply need to find the value for a single variable that will give the desired result in a particular formula, you can perform this simple type of goal seeking with the Goal Seek command. If you have charted the data and created a two-dimensional column, bar, or line chart, you can also perform the goal seeking by directly manipulating the appropriate marker on the chart. And when you need to perform more complex goal seeking, such as that which involves changing multiple input values to realize a result or constraining the values to a specific range, you can use the Solver command.
To use the Goal Seek command, simply select the cell containing the formula that will return the result that you are seeking (referred to as the set cell), indicate what value you want this formula to return, and then indicate the location of the input value that Excel can change to return the desired result. Figures 1-10 and 1-11 illustrate how you can use the Goal Seek command to find how much sales must increase to realize first quarter income of $475,000 (given certain growth, cost of goods sold, and expense assumptions).
Figure 1-10: Using goal seeking to find out how much sales must increase to reach a target income.
Figure 1-11: A spreadsheet showing a goal-seeking solution and the Goal Seek Status dialog box.
To find out how much sales must increase to return a net income of $475,000 in the first quarter, you first select cell B7, which contains the formula that calculates the first quarter income before you choose Goal Seek from the What-If Analysis button’s drop-down menu on the Ribbon’s Data tab or press Alt+AWG. This action opens the Goal Seek dialog box, similar to the one shown in Figure 1-10. Because cell B7 is the active cell when you open this dialog box, the Set Cell text box already contains the cell reference B7. You then select the To Value text box and enter475000 as the goal. Then, you select the By Changing Cell text box and select cell B3 in the worksheet (the cell that contains the first quarter sales).
Figure 1-11 shows you the Goal Seek Status dialog box that appears when you click OK in the Goal Seek dialog box to have Excel go ahead and adjust the sales figure to reach your desired income figure. As this figure shows, Excel increases the sales in cell B3 from $432,692.31 to $1,817,500.00 which, in turn, returns $475,000.00 as the income in cell B7. The Goal Seek Status dialog box informs you that goal seeking has found a solution and that the current value and target value are now the same. (If this were not the case, the Step and Pause buttons in the dialog box would become active, and you could have Excel perform further iterations to try to narrow and ultimately eliminate the gap between the target and current values.)
If you want to keep the values entered in the worksheet as a result of goal seeking, click OK to close the Goal Seek Status dialog box. If you want to return to the original values, click the Cancel button instead. If you change the value by clicking OK, remember that you can still switch between the “before” and “after” input values and results by clicking the Undo button on the Quick Access toolbar or by pressing Ctrl+Z.
To flip back and forth between the “after” and “before” values when you’ve closed the Goal Seek Status dialog box, press Ctrl+Z to display the original values before goal seeking and then Ctrl+Y to display the values engendered by the goal-seeking solution.
Using the Solver
Although the Data Table and Goal Seek commands work just fine for simple problems that require determining the direct relationship between the inputs and results in a formula, you need to use the Solver add-in when dealing with more complex problems. For example, use the Solver to find the best solution when you need to change multiple input values in your model and you need to impose constraints on these values and/or the output value.
The Solver add-in works by applying iterative methods to find the “best” solution given the inputs, desired solution, and the constraints that you impose. With each iteration, the program applies a trial-and-error method (based on the use of linear or nonlinear equations and inequalities) that attempts to get closer to the optimum solution.
When using the Solver add-in, keep in mind that many problems, especially the more complicated ones, have many solutions. Although the Solver returns the optimum solution, given the starting values, the variables that can change, and the constraints that you define, this solution is often not the only one possible and, in fact, may not be the best solution for you. To be sure that you are finding the best solution, you may want to run the Solver more than once, adjusting the initial values each time you solve the problem.
When setting up the problem for the Solver add-in in your worksheet, define the following items:
· Objective cell: The target cell in your worksheet whose value is to be maximized, minimized, or made to reach a particular value. Note that this cell must contain a formula.
· Variable cells: The changing cells in your worksheet whose values are to be adjusted until the answer is found.
· Constraint cells: The cells that contain the limits that you impose on the changing values in the variable cells and/or the target cell in the objective cell.
After you finish defining the problem with these parameters and have the Solver add-in solve the problem, the program returns the optimum solution by modifying the values in your worksheet. At this point, you can choose to retain the changes in the worksheet or restore the original values to the worksheet. You can also save the solution as a scenario to view later before you restore the original values.
You can use the Solver add-in with the Scenario Manager to help set up a problem to solve or to save a solution so that you can view it at a later date. The changing cells that you define for the Scenario Manager are automatically picked up and used by the Solver when you select this command, and vice versa. Also, you can save the Solver’s solution to a problem as a scenario (by clicking the Save Scenario button in the Solver dialog box) that you can then view with the Scenario Manager.
Setting up and defining the problem
The first step in setting up a problem for the Solver to work on is to create the worksheet model for which you will define the objective cell, variables cells, and the constraint cells.
Keep in mind that the Solver is an add-in utility. This means that, before you can use it, you need to make sure that the Solver add-in program is still loaded, as indicated by the appearance of the Solver button in the Analysis group at the end of the Data tab on the Ribbon. If this button is missing, you can load Solver by opening the Add-Ins tab of the Excel Options dialog box (Alt+FTAA) and then clicking the Go button after making sure that Excel Add-Ins is displayed in the Manage drop-down list box to its immediate left. Then, select the Solver Add-in check box in the Add-Ins dialog box to put a check mark in it before you click OK to close the dialog box and reload the add-in.
To define and solve a problem with the Solver add-in after you’ve loaded the add-in and have created your worksheet model, you follow these steps:
1. Click the Solver command button in the Analysis group at the end of the Ribbon’s Data tab.
Excel opens the Solver Parameters dialog box, which is similar to the one shown in Figure 1-12.
2. Click the target cell in the worksheet or enter its cell reference or range name in the Set Objective text box.
Next, you need to select the To setting. Click the Max option button when you want the target cell’s value to be as large as possible. Click the Min option button when you want the target cell’s value to be as small as possible. Click the Value Of option button and then enter a value in the associated text box when you want the target cell’s value to reach a particular value.
3. Click the appropriate option button option in the To section of the dialog box. If you select the Value Of option button, enter the value to match in the associated text box.
Next, designate the variable cells — that is, the ones Solver can change to reach your Equal To goal.
4. Click the By Changing Variable Cells text box and then select the cells to change in the worksheet or enter their cell references or range name in the text box.
Remember that to select nonadjacent cells in the worksheet, you need to hold down the Ctrl key as you click each cell in the selection. To have Excel choose the changing cells for you based on the target cell that you selected, click the Guess button to the right of this text box.
Before having Solver adjust your model, you may add constraints for the target cell or any of the changing cells that determine its limits when adjusting the values.
5. (Optional) Click the Add button to the right of the Subject to the Constraints list box in the Solver Parameters dialog box.
This action opens the Add Constraint dialog box. When defining a constraint, choose the cell whose value you want to constrain or select the cell in the worksheet or enter its cell reference in the Cell Reference text box. Then select the relationship (=, <=, >=, or int for integer or binfor binary) from the drop-down list box to the right and (unless you chose int or bin) enter the appropriate value or cell reference in the Constraint text box.
To continue adding constraints for other cells used by the Solver, click the Add button to add the constraint and clear the text boxes in the Add Constraint dialog box. Then, repeat Step 5 to add a new constraint. After you finish defining constraints for the target cell and changing values in the model, click OK to close the Add Constraint dialog box and return to the Solver Parameters dialog box (which now lists your constraints in the Subject to the Constraints list box).
6. (Optional) Deselect the Make Unconstrained Variables Non-Negative check box if you want to allow negative values when the variable cells are not subject to constraints.
By default, the Solver Add-in employs the GRG (Generalized Reduced Gradient) Nonlinear method in solving the model whose parameters you’re setting known as a very efficient way to solve smooth nonlinear problems. To use the LP Simplex method (for Linear Programming following the Simplex algorithm) or Evolutionary engine for solving non-smooth problems, you need to follow Step 7.
7. (Optional) Select LP Simplex or Evolutionary from the Select a Solving Method drop-down list to use either one of these methods solving nonsmooth problems.
8. Click the Solve button to have the Solver solve the problem as you’ve defined it in the Solver Parameters dialog box.
Figure 1-12: Specifying the parameters to apply to the model in the Solver Parameters dialog box.
Solving the problem
When you click the Solve button, the Solver Parameters dialog box disappears, and the status bar indicates that the Solver is setting up the problem and then keeps you informed of the progress in solving the problem by showing the number of the intermediate (or trial) solutions as they are tried. To interrupt the solution process at any time before Excel calculates the last iteration, press the Esc key. Excel then displays the Show Trial Solution dialog box, informing you that the solution process has been paused. To continue the solution process, click the Continue button. To abort the solution process, click the Stop button.
When Excel finishes the solution process, the Solver Results dialog box appears, similar to the one shown in Figure 1-13. This dialog box informs you whether the Solver was able to find a solution, given the target cell, changing cells, and constraints defined for the problem. To retain the changes that the Solver makes in your worksheet model, leave the Keep Solver Solution option button selected and click OK to close the Solver Results dialog box. To return the original values to the worksheet, click the Restore Original Values option button instead. To save the changes as a scenario before you restore the original values, click the Save Scenario button and assign a name to the current scenario before you click the Restore Original Values option and OK button.
Figure 1-13: The Solver Results dialog box showing that Solver found a solution to the problem.
Unlike when using the Goal Seek command, after clicking the Keep Solver Solution option button in the Solver Results dialog box, you can’t use the Undo command button on the Quick Access toolbar to restore the original values to your worksheet. If you want to be able to switch between the “before” and “after” views of your worksheet, you must save the changes with the Save Scenario button and then select the Restore Original Values option button. That way, you can retain the “before” view in the original worksheet and use the Scenario Manager to display the “after” view created by the Solver.
Changing the Solver options
For most of the problems, the default options used by the Solver are adequate. In some situations, however, you may want to change some of the Solver options before you begin the solution process. To change the solution options, click the Options button in the Solver Parameters dialog box. Excel then opens the Options dialog box with the All Methods tab selected, shown in Figure 1-14, where you can make all necessary changes. (See Table 1-1 for information on each option.)
Figure 1-14: Modifying the solution options in the Options dialog box.
Table 1-1 The Solver Option Settings
Option |
Function |
Constraint Precision |
Specifies the precision of the constraints. The number that you enter in this text box determines whether the value in a constraint cell meets the specified value or the upper or lower limit you have set. Specify a lower number (between 0 and 1) to reduce the time it takes the Solver to return a solution to your problem. |
Use Automatic Scaling |
Select this check box to have the Solver automatically scale the results when solving the problem. |
Show Iteration Results |
Select this check box to have the Solver show the results for the iterations followed in solving the problem. |
Ignore Integer Constraints |
Select this check box to have the Solver ignore any constraints you specify that use integers. |
Integer Optimality (%) |
Specifies the percentage of integer optimality criteria that the Solver applies in solving the problem. |
Max Time (seconds) |
Specifies the maximum number of seconds that the Solver will spend on finding the solution. |
Iterations |
Specifies the maximum number of times that the Solver will recalculate the worksheet when finding the solution. |
Max Subproblems |
Specifies the maximum number of subproblems that the Solver takes on when using the Evolutionary method to solve the problem. |
Max Feasible Solutions |
Specifies the maximum number of feasible solutions that the Solver will pursue when you select the Evolutionary method for solving the problem. |
After changing the options, click OK to return to the Solver Parameters dialog box; from here, you can then click the Solve button to begin the solution process with the new solution settings that you just changed.
When you use the default GRG (Generalized Reduced Gradient) Nonlinear or Evolutionary method, you can set additional Solver settings using the options on the GRG Nonlinear and Evolutionary tabs of the Options dialog box. These options include changing the Converge, Population Size, and Random Seed settings for either of these particular methods.
Saving and loading a model problem
The objective cell, variable cells, constraint cells, and Solver options that you most recently used are saved as part of the worksheet when you click the Save button on the Quick Access toolbar (Ctrl+S). When you define other problems for the same worksheet that you want to save, you must choose the Save Model button in the Solver Options dialog box and indicate the cell reference or name of the range in the active worksheet where you want the problem’s parameters to be inserted.
When you click the Load/Save button, Excel opens the Load/Save Model dialog box, containing a Select Model Area text box. This text box contains the cell references for a range large enough to hold all the problem’s parameters, starting with the active cell. To save the problem’s parameters in this range, click OK. If this range includes cells with existing data, you need to modify the cell reference in this text box before you choose OK to prevent Excel from replacing the existing data.
After you click OK, Excel copies the problem’s parameters in the specified range. These values are then saved as part of the worksheet the next time you save the workbook. To reuse these problem parameters when solving a problem, you simply need to open the Solver Options dialog box, click the Load/Save button to open the Load/Save Model dialog box, click the Load button, and then select the range containing the saved problem parameters. When you click OK in the Load Model dialog box, Excel loads the parameters from this cell range into the appropriate text boxes in the Solver Parameters dialog box. You can then close the Solver Options dialog box by clicking OK, and you can solve the problem by using these parameters by clicking the Solve command button.
Remember that you can use the Reset All button whenever you want to clear all the parameters defined for the previous problem and return the Solver options to their defaults.
Creating Solver reports
You can create three different types of reports with the Solver:
· Answer report: Lists the target cell and changing cells with their original and final values, along with the constraints used in solving the problem.
· Sensitivity report: Indicates how sensitive an optimal solution is to changes in the formulas that calculate the target cell and constraints. The report shows the changing cells with their final values and the reduced gradient for each cell. (The reduced gradient measures the objective per unit increase in the changing cell.) If you defined constraints, the Sensitivity report lists them with their final values and the Lagrange multiplier for each constraint. (The Lagrange multiplier measures the objective per unit increase that appears in the right side of the constraint equation.)
· Limits report: Shows the target cell and the changing cells with their values, lower and upper limits, and target results. The lower limit represents the lowest value that a changing cell can have while fixing the values of all other cells and still satisfying the constraints. The upper limit represents the highest value that will do this.
Excel places each report that you generate for a Solver problem in a separate worksheet in the workbook. To generate one (or all) of these reports, select the report type (Answer, Sensitivity, or Limits) from the Reports list box of the Solver Results dialog box (as shown previously inFigure 1-13). To select more than one report, just click the name of the report.
When you click OK to close the Solver Results dialog box (after choosing between the Keep Solver Solution and Restore Original Values options), Excel generates the report (or reports) that you selected in a new worksheet that it adds to the beginning of the workbook. (Report sheet tabs are named by report type, as in Answer Report 1, Sensitivity Report 1, and Limits Report 1.)