Excel What-If Analysis

What-If Analysis is a set of tools in Excel that allows exploration of different possible outcomes by changing input values. Instead of manually editing cells one by one to see how results change, these tools automate the process. What-If Analysis is widely used in financial planning, budgeting, forecasting, and business modeling.

Excel provides three main What-If Analysis tools:

• Data Tables: Show how one or two input variables affect a formula's result.
• Goal Seek: Find the input value needed to reach a specific result.
• Scenario Manager: Compare multiple sets of input values (scenarios) side by side.

All three tools are found under Data → What-If Analysis.

Goal Seek

What It Does

Goal Seek works backwards. Instead of asking "what is the result if I change this input?", it asks "what input value do I need to achieve this specific result?" Excel then automatically adjusts the input cell to reach the target.

How to Use Goal Seek

1. Set up a formula in a cell (the formula cell must contain the output to target).
2. Go to Data → What-If Analysis → Goal Seek.
3. In the dialog:
   • Set cell: Select the cell containing the formula (the result cell).
   • To value: Enter the desired target result.
   • By changing cell: Select the input cell that Excel should adjust.
4. Click OK. Excel calculates and fills in the required input value.

Example: Loan Repayment

  Setup:
  Cell B1: Loan Amount = 100000
  Cell B2: Interest Rate = 8% (annual)
  Cell B3: Number of Months = 12
  Cell B4: Monthly Payment = =PMT(B2/12, B3, -B1)
  Result: B4 shows monthly payment ≈ 8699

  Goal: Achieve a monthly payment of exactly 7500.
  What interest rate makes this happen?

  Goal Seek Settings:
  Set cell: B4
  To value: 7500
  By changing cell: B2

  After running: Excel adjusts B2 to the interest rate that results in a payment of 7500.

Important Notes

• Goal Seek can only change one input cell at a time.
• The solution found is an approximation — Excel works iteratively to converge on the answer.
• The result permanently changes the input cell. Use Ctrl + Z to undo if needed.

Data Tables

What It Does

A Data Table shows how changing one or two input variables affects the result of a formula. Instead of testing one value at a time, a whole range of possibilities is displayed in a structured table format simultaneously.

One-Variable Data Table

A one-variable data table tests how one changing input (placed in a row or column) affects one or more formulas.

Example: Monthly Loan Payment at Different Interest Rates

  Setup:
  Cell B1: Loan Amount = 200000
  Cell B2: Interest Rate = 6% (the variable)
  Cell B3: Term (months) = 120
  Cell B4: =PMT(B2/12, B3, -B1)

  Data Table Setup:
  Enter different rates in column D (D2:D7):
  D2: 4%
  D3: 5%
  D4: 6%
  D5: 7%
  D6: 8%
  D7: 9%

  In E1: Enter =B4 (the formula to evaluate)

  Select D1:E7 → Data → What-If Analysis → Data Table
  Row input cell: (blank)
  Column input cell: B2 (because rates are in a column)
  Click OK.

  Result:
  | D    | E              |
  | Rate | Monthly Payment|
  | 4%   | 2024.77        |
  | 5%   | 2121.31        |
  | 6%   | 2220.41        |
  | 7%   | 2322.06        |
  | 8%   | 2426.55        |
  | 9%   | 2533.43        |

Two-Variable Data Table

A two-variable data table tests how two inputs — one in a row and one in a column — affect a single formula.

Example: Loan Payment for Different Rates and Terms

  In D1: Enter =B4 (the formula)
  Row headers (E1:H1): 60, 84, 120, 180 (loan terms in months)
  Column headers (D2:D6): 4%, 5%, 6%, 7%, 8% (interest rates)

  Select D1:H6 → Data → What-If Analysis → Data Table
  Row input cell: B3 (term)
  Column input cell: B2 (rate)
  Click OK.

  Result: A full grid showing the monthly payment for every combination of rate and term.

Scenario Manager

What It Does

Scenario Manager saves multiple named sets of input values (called scenarios) and allows switching between them to compare different outcomes. This is useful for comparing best-case, worst-case, and expected-case financial projections.

How to Create Scenarios

1. Go to Data → What-If Analysis → Scenario Manager.
2. Click Add.
3. Enter a scenario name (e.g., "Best Case").
4. In the Changing Cells field, select the input cells to vary.
5. Click OK. Enter the values for each input cell in this scenario.
6. Click Add again to add more scenarios (e.g., "Worst Case", "Expected Case").
7. Click Close when all scenarios have been entered.

Viewing a Scenario

1. Open the Scenario Manager.
2. Select a scenario name from the list.
3. Click Show. The spreadsheet updates to reflect that scenario's input values.

Example: Profit Projection

  Changing cells:
  B1 = Units Sold
  B2 = Price per Unit
  B3 = Cost per Unit

  Formula: =B1*(B2-B3)  → Profit

  Scenarios:
  Best Case:     Units=1000, Price=150, Cost=80
  Expected Case: Units=700,  Price=130, Cost=90
  Worst Case:    Units=400,  Price=110, Cost=95

Scenario Summary Report

1. In Scenario Manager, click Summary.
2. Choose Scenario Summary or Scenario PivotTable.
3. Select the result cell(s) to display (e.g., the profit formula cell).
4. Click OK. A new summary sheet is created automatically.

Summary Output Example

  | Scenario     | Units | Price | Cost | Profit  |
  | Best Case    | 1000  | 150   | 80   | 70000   |
  | Expected Case| 700   | 130   | 90   | 28000   |
  | Worst Case   | 400   | 110   | 95   | 6000    |

Choosing the Right What-If Tool

  Tool             | Use When...
  -----------------|--------------------------------------------
  Goal Seek        | A specific result is needed and the input is unknown
  Data Table       | Multiple values of 1-2 inputs need to be compared
  Scenario Manager | Multiple complete sets of inputs need to be saved and compared

Summary

• Goal Seek works backwards — it finds the input value needed to achieve a desired result.
• A one-variable Data Table shows results for multiple values of a single input.
• A two-variable Data Table shows results for every combination of two inputs.
• Scenario Manager saves named input sets and allows switching between them for comparison.
• A Scenario Summary Report generates a comparison table of all saved scenarios on a new sheet.