What-If Analysis: Goal Seek, Scenario Manager, and Data Tables

In standard Excel modeling, you provide inputs and formulas to calculate a result. What-If Analysis allows you to flip that logic. These tools are used for sensitivity testing and strategic planning—answering questions like "What sales volume do I need to break even?" or "What happens to our profit if shipping costs rise by 15%?"

Part 1: Goal Seek (The "Backward" Solver)

Goal Seek is used when you know the result you want from a formula, but you aren't sure what input value is required to get there. It works by "guessing" values for a single variable until the target is met.

🛠 How to Use Goal Seek

1. Go to the Data tab > Forecast group > What-If Analysis > Goal Seek.
2. Set cell: Select the cell containing the formula you want to solve (e.g., Total Profit).
3. To value: Type the exact number you want that formula to reach (e.g., 0 for a break-even analysis).
4. By changing cell: Select the single input cell Excel should adjust (e.g., Unit Sales).
5. Click OK.

💡 Key Rules for Goal Seek

• Single Variable Only: It can only change one cell at a time. If you need to change multiple variables (like Price and Cost), you would need the "Solver" add-in.
• The "Set Cell" Must be a Formula: You cannot "Set" a cell that contains a hardcoded number.
• Official Docs: Use Goal Seek to find a result

Part 2: Scenario Manager (The "Comparison" Tool)

While Goal Seek finds one specific answer, Scenario Manager allows you to create, save, and switch between different "versions" of a model. This is perfect for contingency planning.

🛠 How to Use Scenario Manager

1. Go to Data > What-If Analysis > Scenario Manager.
2. Click Add to create a new scenario (e.g., name it "Best Case").
3. Changing cells: Select the cells that will vary (e.g., your Revenue and Expense inputs). You can select up to 32 cells.
4. Enter the specific values for that scenario and click OK.
5. Repeat for "Worst Case" and "Expected Case."

📊 Generating the Summary Report

The real power of this tool is the Summary button. * Click Summary inside the Scenario Manager. * Choose Scenario summary. * Excel will create a brand new sheet with a beautifully formatted table comparing all your scenarios side-by-side.

🌟 Pro-Tip: Use Named Ranges

Before using Scenario Manager, name your input and result cells (e.g., name cell B5 "Unit_Price" instead of just B5). If you do this, your Summary Report will use those names instead of cell coordinates, making it much easier to read.

• Official Docs: Switch between various sets of values using scenarios

Part 3: Data Tables (Sensitivity Grids)

While Goal Seek answers "what input gets me X?" and Scenario Manager compares discrete cases, a Data Table sweeps an input across a range of values and shows every result in a single grid. There is no need to copy formulas down or rebuild the model—Excel substitutes one (or two) inputs into the existing formula automatically.

One-Variable Data Table

Use this when you want to see how one input affects one or more formula outputs. Classic example: monthly loan payment across a range of interest rates.

🛠 Setup (Column-Oriented)

1. In a column, list the input values you want to test (e.g., interest rates 3%, 4%, 5%, … in B3:B10).
2. In the cell one row up and one column to the right of your input list (e.g., C2), enter the formula that already references the original input cell. For example: =PMT(C1/12, 360, -200000) where C1 is the "live" interest-rate input.
3. Select the entire rectangular range B2:C10 (the input column + the formula row).
4. On the Data tab, click What-If Analysis > Data Table….
5. Leave Row input cell blank. In Column input cell, click the original input cell (C1).
6. Click OK. Excel fills C3:C10 with one payment per rate.

Row-Oriented Variant

If your input values run across a row instead of down a column, put the formula one cell down-and-left of the row, select the rectangle, and fill Row input cell instead of Column input cell.

Two-Variable Data Table

Use this when two inputs vary simultaneously and you want a single output (you cannot have multiple outputs in a 2-variable table). Classic example: loan payment by interest rate × loan term.

Layout

The grid follows a strict three-zone shape: the formula sits in the corner cell, the values for one input run across the top row, and the values for the other input run down the left column. Excel substitutes the top row into one of your model cells and the left column into the other, then fills every interior cell with the resulting output.

              ┌──────── top row: input #2 values ────────┐
              │  (these substitute into Row input cell)  │
              ▼                                          ▼
    ┌────────┬────────┬────────┬────────┬────────┬────────┐
    │ =PMT(…)│ 10 yrs │ 15 yrs │ 20 yrs │ 25 yrs │ 30 yrs │  ← top row
    ├────────┼────────┼────────┼────────┼────────┼────────┤
    │  3%    │   ··   │   ··   │   ··   │   ··   │   ··   │
    ├────────┼────────┼────────┼────────┼────────┼────────┤
    │  4%    │   ··   │   ··   │   ··   │   ··   │   ··   │
    ├────────┼────────┼────────┼────────┼────────┼────────┤
    │  5%    │   ··   │   ··   │   ··   │   ··   │   ··   │
    ├────────┼────────┼────────┼────────┼────────┼────────┤
    │  6%    │   ··   │   ··   │   ··   │   ··   │   ··   │
    └────────┴────────┴────────┴────────┴────────┴────────┘
       ▲          ▲
       │          └── interior: Excel fills every cell with PMT(rate, term)
       │
       └── left column: input #1 values
           (these substitute into Column input cell)

   corner cell (B2)         = formula referencing the live input cells
   top row (C2:G2)          = values that substitute into Row input cell
   left column (B3:B10)     = values that substitute into Column input cell
   interior (C3:G10)        = Excel fills these with the formula's output

🛠 Setup

1. In the corner cell of the grid (top-left of the table area, e.g., B2), put the formula: =PMT(C1/12, C2*12, -200000).
2. Down the left column (e.g., B3:B10), list the values for input #1 (interest rates) — these will become the Column input cell values in the dialog.
3. Across the top row (e.g., C2:G2), list the values for input #2 (loan terms in years) — these will become the Row input cell values in the dialog.
4. Select the entire grid including the corner formula and both axes (B2:G10).
5. Data > What-If Analysis > Data Table….
6. Row input cell: the original model cell that the top-row values substitute into (C2, the term cell). Mnemonic: "Row input ← top Row of values."
7. Column input cell: the original model cell that the left-column values substitute into (C1, the rate cell). Mnemonic: "Column input ← left Column of values."
8. Click OK. Excel fills the entire interior of the grid with one payment per (rate, term) pair.

Why Data Tables Behave Specially in Recalculation

Data Tables are computationally expensive—each cell in the grid is a separate "what-if" calculation that re-runs the entire dependency chain. To prevent a 100×100 grid from freezing the workbook on every keystroke, Excel offers a dedicated calculation mode:

• Formulas tab > Calculation Options > Automatic Except for Data Tables.

In this mode, every other formula recalculates instantly, but Data Tables only refresh when you press F9 (or click Calculate Now). See lesson 1.8 Calculation Options for the broader recalculation model.

• Official Docs: Calculate multiple results by using a data table

Beyond the exam: Solver

Goal Seek only changes one cell to hit one exact target with no constraints. For real optimization with multiple decision variables, constraints (>=, <=, integer, binary), or maximize/minimize objectives, Excel ships a Solver add-in (disabled by default). Enable it via File → Options → Add-ins → Manage: Excel Add-ins → Go… → Solver Add-in → OK, then find it on the Data tab. Solver is not on the MO-211 spec — see the official Solver docs if you need it in production.

Cheat Sheet: Which Tool to Use?