What-If Analysis in Excel


What If Analysis

What-if analysis is the process of changing values to see how the changes affect other data. What-if analysis is a powerful tool of Excel.

Breakeven analysis is a type of what-if analysis that determines the point where revenues equal expenses or the point of zero profit. This breakeven analysis examines fixed costs and variable costs.

Sensitivity analysis is another type of what-if analysis that attempts to determine how sensitive the results of an analysis are to changes in the inputs and assumptions.

What if Analysis Menu

The What If analysis menu is on the Data ribbon. The three options under the button are: 

  1. Scenario Manager
  2. Goal Seek
  3. Data Tables

Scenario Manager

Scenario Manager allows substitute input values for multiple cells (up to 32). So, you can view the results of different input values (scenarios) at the same time. This could be “best case” or “worst case” scenarios.

Goal Seek

Goal Seek allows changing the value in one specific cell until the target result is returned by the formula. This is a simpler alternative to Solver. Solver can change multiple cells and includes constraints.

Goal Seek has three arguments:

  1. Target cell – the cell that you want to change to a target number; called “Set cell”
  2. Target Value – the value you want the set cell to be; called “To cell”
  3. Input cell – the input cell to change the target cell; called “By changing cell”

Goal seek can only change one cell to a given value. The target cell must contain a formula so that a change in the changing cell will cause it to be the target value. Goal seek menu looks like this:

When the goal seek is successful, the menu changes to Goal Seek Status:

Data Tables

A data table gives many results based on changing one or two variables. Data tables have input cells and result cells. Input cells have the values that may change and the result cells have use formulas to calculate the results.

  • One-Variable Data Table – showing a what if scenario if only one variable is changed, like the number of units sold
  • Two-Variable Data Table – showing a what if scenario if two variables are changed, like the number of units sold and the sales price

Analysis Tools – Excel Add-ins

There are two buttons that can be added to the Data tab by using the Add-ins option. These buttons are Data Analysis and Solver

Note: these buttons are included in Excel, but they are not turned on by default. You will have to add Data Analysis and Solver manually. Then the buttons are turned on in the future.

Analysis Tools without Data Analysis and Solver
Analysis Tools with Data Analysis and Solver

Solver

Solver allows changing several cells to reach a goal in a related cell called the objective cell. The changing values are called the variable cells or the decision variables. The result is called the solver model.

  1. Objective cell – The single cell that is the goal of the Solver analysis. This cell can be minimized, maximized, or set to a specific value.
  2. Variable cells – The cells that can be changed for the Solver analysis. These are also known as decision variables.
  3. Constraints – The restrictions on the values in the changing decision variables.
  4. Solver Model – Combination of the objective cell results based on the decision variables subject to the constraints.

The solver can use solving methods that are linear or non-linear.

Excel Video: Data Tables Explained


Excel Video: Goal Seek Function Explained


Jeff Mankin

Jeff Mankin teaches financial literacy. His website is FinallyLearn.com.

Recent Posts

link to Expense

Expense

What is an expense? Expense is a cost to operate a business to produce revenue. In accounting, an expense occurs when an asset is used. This could include a cash outflow or consuming an...