Scenarios and What If Analysis in Microsoft Excel

/ Uncategorized / By

What If Analysis in Excel

Microsoft Excel’s “What If” analysis features provide answers to a variety of mathematical problems. For example, you can use Goal Seek to find what principal amount, based on a specific interest rate, will require you to make a £1,000 mortgage payment. You can use a data table to determine how much your monthly mortgage payments would increase by if interest rates were to rise or fall in predictable increments. If you need to optimize your data, such as maximizing profits while changing or limiting costs, you can use Microsoft Excel‘s Solver.

What-If Analysis tools can be found in the Data Tools group, on the Data tab, click the What-If Analysis button.

Using Goal Seek

If you know the result of a formula but not the value of a specific variable on which the formula depends, you can use Goal Seek in Microsoft Excel to adjust the variable to display the desired result. Goal Seek can only adjust one variable in a formula. If a formula depends on more than one variable, you will need to decide which variable to adjust.

You use the Goal Seek dialog box to input the required data. If you select the cell containing the formula, and then open the Goal Seek dialog box, the correct cell reference is automatically entered in the Set cell text box.

Once Goal Seek has returned a result, Excel will display the result on the worksheet and in the Goal Seek Status dialog box. You can choose OK to accept the result on the worksheet, or you can choose Cancel to restore the original value.

To use Goal Seek:

  1. Select the cell containing the formula.
  2. In the Data Tools group, on the Data tab, click the What-If Analysis button. Then click Goal Seek.
  3. In the Goal Seek dialog box, in the Set cell text box, make sure the cell containing the formula is displayed.
  4. In the To value text box, enter the value of the desired result.
  5. In the By changing cell text box, enter the cell that contains the data you want to vary.
  6. Choose OK.
  7. In the Goal Seek Status dialog box, choose OK to accept the solution or choose Cancel to restore the original value.

Using Data Tables

A Microsoft Excel data table is a range of cells that displays the results of substituting a number of different values into one or two variables in a formula. A data table lets you view and compare the results of many different combinations of changing variables in a formula, as opposed to Goal Seek, which lets you change only one variable and view only one result at a time.

For example, in the mortgage example, if you know that interest rates will range from 7.0% to 10.5%, while keeping the mortgage amount constant, you can create a one-variable data table to show you how this would affect your monthly payments. A one-variable data table calculates the changes in a formula when one of the variables in that formula, such as the interest rate, is changed. By default, all input values in a one‑variable Microsoft Excel data table must be listed down a column or across a row.

You can also generate a two-variable data table which displays the results of changing two formula variables. For example, you can see what your monthly payments would be if you varied the interest rate and the term of the loan.

To create a one‑variable data table:

  1. If necessary, enter the original data you want to analyze.
  2. In an area on the worksheet several rows and columns away from the original data, enter the input values in a column.
  3. In the cell directly above the input values and one cell to the right, enter the desired formula.
  4. Select the range of cells containing the list of input values and the formula.
  5. In the Data Tools group, on the Data tab, click the What-If Analysis button, then click Data Table.
  6. In the Table dialog box, in the Column input cell text box, enter the cell reference for the column input cell.
  7. Choose OK.

To create a two‑variable data table:

  1. If necessary, enter the original data you want to analyze.
  2. In an area on the worksheet several rows and columns away from the original data, enter the desired formula in a cell.
  3. Beginning with the cell to the right of the formula, enter the list of row input values.
  4. Beginning with the cell below the formula, enter the list of column input values.
  5. Select the range of cells containing the list of input values and the formula.
  6. In the Data Tools group, on the Data tab, click the What-If Analysis button, then click Data Table
  7. In the Table dialog box, in the Column input cell text box, enter the cell reference for the column input cell.
  8. In the Row input cell text box, enter the cell reference for the row input cell.
  9. Choose OK.

Using Solver

Solver is an Excel feature that allows you to find the optimal value for a particular cell by adjusting the value of related cells, and, if desired, by applying specific limitations, or constraints, to the related cells. For example, you can use Solver to determine the optimal use of resources so that profits are maximized or costs are minimized.

Solver options appear in a group called Analysis, on the Data tab. Because Solver is an Excel add-in­, it is not loaded into Excel in a typical installation. Therefore, it must be added to Excel using a custom installation.  If the Analysis tab does not appear in the Data tab, ask your instructor for assistance.

Before you can use Solver, you need to define a problem by identifying the parameters to be entered in the Solver Parameters dialog box. You add constraints in the Add Constraint dialog box. Solver adjusts the target and changing cells to find the best solution to your problem.

ParameterDescription
Target cellContains the result you want to minimize, maximize, or set to a certain value.
Changing cell(s)Affect the value of the target cell.
ConstraintA limitation placed on a cell value. Constraints may be applied to either the target cells or the changing cells. They can be added, changed, or deleted.

To use Solver:

  1. In the Analysis group on the Data tab, click the Solver button.
  2. In the Solver Parameters dialog box, in the Set Target Cell text box, enter the target cell.
  3. In the Equal To area, select the desired option button.
  4. If the Value of option button is selected, in the Value of text box, enter a value.
  5. In the By Changing Cells text box, enter the cells that will change.
  6. In the Subject to the Constraints area, choose Add.
  7. In the Add Constraint dialog box, in the Cell Reference text box, enter the cells whose values you want to constrain.
  8. From the logical operator drop‑down list, select the desired logical operator.
  9. In the Constraint text box, enter a value.
  10. Choose Add to accept the constraint and enter another constraint.
    or
  11. Choose OK to accept the constraint and return to the Solver Parameters dialog box.
  12. Choose Solve.
  13. In the Solver Results dialog box, select the Keep Solver Solution or Restore Original Values option button.
  14. Choose OK.

Working with Scenarios

In Excel, you use scenarios to determine results based on different sets of changing values. For example, you might want to build a report that analyzes a sales worksheet for three scenarios: best case (high sales), worst case (very low sales), and expected case (reasonable projection). You can create and store multiple scenarios, display the results of your scenarios for comparison and examination, and edit and delete scenarios.

Creating a Scenario

To create a scenario, you choose Add in the Scenario Manager dialog box to display the Add Scenario dialog box. Each scenario must have a name. If you plan on using multiple scenarios, you should give each scenario a meaningful name to make it easy for you to identify the scenario in the future.

The cells you enter in the Changing cells text box of the Add Scenario dialog box are the cells on which a formula depends. If you select these cells in the worksheet before choosing Scenarios from the Tools menu, the correct cells will automatically be inserted into the Changing cells text box. You enter the values for the changing cells in the Scenario Values dialog box. By default, the values in the text boxes in the Scenario Values dialog box are the values in the cells referenced in the Changing cells text box. To create a new scenario, simply change the values in the text boxes in the Scenario Values dialog box.

To create a scenario:

  1. In the Data Tools group, on the Data tab, click the What-If Analysis button, then click the Scenario Manager button.
  2. In the Scenario Manager dialog box, choose Add.
  3. In the Add Scenario dialog box, in the Scenario name text box, enter a scenario name.
  4. In the Changing cells text box, enter the cells you want to modify.
  5. In the Comment text box, enter a description for the scenario.
  6. Choose OK.
  7. In the Scenario Values dialog box, in the text boxes, enter the values for the changing cells.
  8. Choose OK.
  9. Repeat steps 2 through 8 for additional scenarios.
  10. Choose Close.

Displaying the Results of a Scenario

When you display a scenario, the cells in the worksheet that depend on the changing cells display the results of the scenario. You display the results of a scenario using the Scenario Manager dialog box. Viewing the results of each scenario while the dialog box is open lets you quickly compare your results, making it easier to see which scenario yields which results. You should create a scenario that uses the current data in the changing cells, so you can display the original data at any time.

To display the results of a scenario:

  1. In the Data Tools group, on the Data tab, click the What-If Analysis button, then click the Scenario Manager button.
  2. In the Scenario Manager dialog box, in the Scenarios list box, select the desired scenario.
  3. Choose Show.

Editing a Scenario

In a scenario, you can change the scenario name, enter different changing cells and/or values, and amend the comments. You edit a scenario in the Edit Scenario dialog box, which is similar to the Add Scenario dialog box. To edit the changing cells values, you must use the Scenario Values dialog box, which appears automatically when you choose OK in the Edit Scenario dialog box.

To edit a scenario:

  1. In the Data Tools group, on the Data tab, click the What-If Analysis button, then click the Scenario Manager button.
  2. In the Scenario Manager dialog box, in the Scenarios list box, select the scenario you want to edit.
  3. Choose Edit.
  4. In the Edit Scenario dialog box, make the desired changes.
  5. Choose OK.
  6. In the Scenario Values dialog box, make any changes, and then choose OK.
  7. In the Scenario Manager dialog box, choose OK.

Ceating a Scenario Summary Report

A scenario summary report lists the different assumptions you use and the results you obtain for each scenario. You create a scenario summary report using the Scenario Summary dialog box. Excel automatically creates a formatted report based on your information in a new worksheet in your workbook and adds outline buttons to the report so that you can expand or collapse the data.

To create a scenario summary report:

  1. From the Tools menu, choose Scenarios.
  2. In the Scenario Manager dialog box, choose Summary.
  3. In the Scenario Summary dialog box, in the Report type area, select the Scenario summary option button.
  4. In the Result cells text box, enter the cell references for the cells in the worksheet that contain the results you want to include in the report.
  5. Choose OK.

Note: To make the scenario summary report easier to understand, use named ranges in your worksheet for the changing cells and the result cells.