Chapter 6  What-If Analysis

Create a One-Variable Data Table

• What-if analysis - allows you to see how changing variables impacts calculated results
• A variable - a value that can be changed to see how it affects other values
• A one-variable data table - a data analysis tool that provides various results based on changing one variable
• A substitution value - replaces the original value of a variable in a data table

1. List substitution values in the left column or first row
2. Enter formulas in the first row or left column (whichever was not used above)
3. Create the one-variable data table
4. Format the results of the data table
5. Create custom number formats to disguise the formulas as headings

To complete a one-variable data table:

1. Select entire table starting in the blank cell in the top-left corner
2. Click What-If Analysis in the Data Tools group on the Data tab and select Data Table
3. Enter address of the cell to be changed in the Data Table dialog box
4. Click OK

Create a Two-Variable Data Table

• A two-variable data table - a data analysis tool that provides results based on changing two variables
• Creating a two-variable data table - similar to creating a one variable data table; however, you are limited to comparing one result.Recommendations include:
• Use the top row for one variable’s substitution values
• Use the first column for the other variable’s values
• Apply a custom number format to the formula cell in the top-left cell

Identify an Input Value with Goal Seek

• Goal Seek - a tool when you know the desired end result but not the value needed to meet the goal
• Enables you to work backwards to solve a problem
• Excel can enter the input value in the variable cell

To use Goal Seek:

1. Click What-If Analysis in the Data Tools group on the Data tab
2. Select Goal Seek to open the Goal Seek dialog box
3. Enter the cell reference for the cell to be optimized in the Set cell box
4. Enter the result you want to achieve (such as $300) in the To value box
5. Enter the cell reference that contains the value of the variable to adjust (such as cost of car) in the By changing cell box
6. Click OK
7. When an answer appears, click OK to accept the change or Cancel to return to the original data

Use Scenario Manager

• Scenarios take detailed sets of input values and determine possible results
• Scenario Manager enables you to define and manage up to 32 scenarios
• Scenarios are maintained separately for each worksheet in a workbook
• Use Scenario Manager

To create a scenario:

1. Click What-If Analysis in the Data Tools group on the Data tab
2. Select Scenario Manager to open the Scenario Manager dialog box
3. Click Add to open the Add Scenario dialog box
4. Enter a meaningful name in the Scenario name box
5. Enter the input cells for the scenarios in the Changing cells box
6. Click in the Comment box to display you name and the date the scenario is created
7. Click OK to open the Scenarios Values dialog box
8. Click Add to add another scenario and specify its values OR click OK to return to the Scenario Manager dialog box

To view your scenarios:

1. Click What-If analysis in the Data Tools group on the Data tab
2. Select Scenario Manager
3. Select the name of the Scenario you want to view in the Scenarios list
4. Click Show

Generate Scenario Summary Reports

• Scenario summary report - a worksheet that compares the scenario results
• Do not automatically update

To create a scenario summary report:

1. Open the Scenario Manager dialog box
2. Click Summary to open the Scenario Summary dialog box
3. Click  Scenario Summary or Scenario PivotTable report and enter the reference for the cell(s) whose values change in the scenarios in the Result cells box
4. Click OK

Load the Solver Add-in

• Solver - a separate program that must be installed or added in to Excel
• Once loaded - appears in the Analysis group on the Data tab
• Solver - manipulates variables based on constraints to find the optimal solution to a problem

To load Solver:

1. Click the File tab, and then select Options
2. Click Add-Ins
3. Click the Manage arrow, select Excel Add-ins, and then click Go to open the Add-Ins dialog box
4. Click the Solver Add-in check box in the Add-Ins available list and click OK

Optimize Results With Solver

• Solver requires three parameters:
• Objective cell contains a formula-based value that you want to optimize that relates directly or indirectly to the changing cells and constraints
• Changing cells are the cells whose values are adjusted until the constraints are satisfied
• Constraints specify the restrictions

To specify the objective and changing cells:

1. Click Solver in the Analysis group on the Data tab to open the Solver Parameters dialog box
2. Enter the cell containing the formula for which you want to optimize its value in the Set Objective box
3. Click an option in the To section to specify what type of value you need to find for the target cell (such as Max, Min, or Value Of with a specified value)
4. Enter the cell references that contain variables in the By Changing Variable Cells box

To specify the constraints:

1. Click Add to the right of the Subject to the Constraints list to open the Add Constraint dialog box
2. Enter the cell reference, the operator to test the cell references, and the constraint the cell needs to match (such as the down payment must be less than or equal to $7,000)
3. Click OK to add the constraint and return to the Solver Parameters dialog box, or click Add to add the constraint and create another constraint

Optimize Results With Solver

•  Solver uses an iterative process to find an optimal solution
•  A summary report can be generated if a solution is found
•  The report specifies:
•  Binding Constraints which are rules that Solver enforced to reach the target value
• Nonbinding Constraints do not restrict the target value found