Tutorials›Excel›What-If Analysis
🟢 Free Demo
Excel TutorialTopic 27 of 31

What-If Analysis

Use Goal Seek, Scenario Manager, and Data Tables to answer "what if?" business questions directly in Excel.

✅ What You Will Learn

›How to use Goal Seek to reverse-calculate a target input
›How to model multiple scenarios with Scenario Manager
›How to build one-variable and two-variable Data Tables
›When to use each What-If tool for different business questions
›How to combine Scenario Manager with Scenario Summary reports

What-If Analysis tools answer questions that formulas alone cannot: "How many units do we need to sell to break even?", "What EMI corresponds to a loan I can afford?", "How does profit change if costs rise by 10% or revenue falls by 15%?" These are the real questions analysts are asked, and they require working backwards from a target output to the required inputs.

Goal Seek finds the input value needed to achieve a specific output — it is Excel built-in equation solver for one-variable problems. Scenario Manager saves multiple named sets of inputs so you can switch between Base, Best Case, and Worst Case with a dropdown. Data Tables systematically calculate outputs across a range of input values, producing a sensitivity table.

Together, these three tools form the core of Excel modelling for finance, operations, and strategic planning.

Examples

Example 1Goal Seek — find the required sales volume to break even
Setup:
  B1: Fixed costs = 500000
  B2: Variable cost per unit = 250
  B3: Selling price per unit = 400
  B4: Units sold = 2000 (this is what we want to find)
  B5: Profit = =B4*(B3-B2)-B1   (currently shows -200000)

Goal Seek:
  Data tab → What-If Analysis → Goal Seek
  Set cell: B5 (Profit)
  To value: 0 (break even)
  By changing: B4 (Units sold)
  Click OK

Result: B4 → 3,334 units needed to break even
💡

Goal Seek works backwards from a target value to find the required input. It changes only the "By changing" cell — all other inputs remain fixed.

Example 2Data Table — one-variable sensitivity: how profit changes at different price points
Setup:
  Row 1: Price points as column headers (350, 375, 400, 425, 450)
  A2: Link to Profit formula: =B5

Steps:
  Select A1:F2 (headers + profit cell reference)
  Data → What-If Analysis → Data Table
  Row input cell: B3 (Selling price — headers feed into it)
  Column input cell: (blank — one-variable table)
  Click OK

Result: Profit at each price point appears in B2:F2 instantly
💡

Data Tables automatically recalculate when source data changes. They cannot be partially edited — they are generated as a block. To edit, delete the entire output range and run the Data Table again.

📌 Key Points to Remember

  • ✓Goal Seek: one unknown, one target — find the input needed to achieve a specific output
  • ✓Scenario Manager: save named sets of inputs, switch between them, generate comparison reports
  • ✓Data Table: systematically vary 1 or 2 inputs across ranges to build sensitivity tables
  • ✓Data Tables use special array formula {=TABLE(row_input,col_input)} — never edit cells inside manually
  • ✓Solver (add-in) extends Goal Seek to optimise with multiple variables and constraints

🏢 Real-World Application

CFOs use Scenario Manager to model base/bull/bear cases for annual budgets — switching between scenarios during board presentations to show the range of outcomes. Sales managers use Goal Seek at the start of each quarter to calculate the exact revenue target needed to hit the annual plan. Investment analysts use two-variable Data Tables to build IRR sensitivity matrices showing how returns change across different entry prices and growth rates.

⚠️ Common Mistakes to Avoid

WRONGRunning Goal Seek on a cell that does not depend on the "By changing" cell
FIXGoal Seek requires a direct or indirect formula link between the Set cell and the By changing cell. If the Profit formula does not reference the Units cell, Goal Seek has nothing to solve.
WRONGTrying to edit individual cells inside a Data Table output
FIXData Table outputs are array formulas — Excel prevents editing individual cells. To change the table, delete all output cells first, adjust inputs, and regenerate.
WRONGScenario Manager scenarios not updating when base formulas change
FIXScenarios store input cell values, not output values. Change the scenarios inputs via Edit Scenario if the model structure changes.
✏️Test Yourself

Goal Seek is most appropriate for which situation?

❓ Frequently Asked Questions

What is the difference between Goal Seek and Solver?

Goal Seek handles one variable, one target, no constraints. Solver is a full optimisation tool that handles multiple variables, multiple constraints, and can maximise, minimise, or target a specific value. Solver is an Excel add-in (File → Options → Add-ins → Solver Add-in).

Can Scenario Manager output all scenarios side by side?

Yes — Scenario Manager → Summary generates a Scenario Summary or Scenario PivotTable showing all scenario inputs and outputs in a comparison table. This is useful for presenting alternatives to stakeholders.

How do I build a two-variable Data Table?

Put the formula in the corner cell (e.g. B1). Put row input values across row 1 (C1:H1) and column input values down column A (B2:B8). Select B1:H8, Data → Data Table, set Row input cell and Column input cell to the two variables in your formula.

✏️ Practice Exercise

Build a break-even analysis model. Inputs: Fixed costs, Variable cost per unit, Selling price, Current units. Outputs: Revenue, Total cost, Profit. (1) Use Goal Seek to find the break-even units, (2) Build a one-variable Data Table showing profit at 10 different price points, (3) Create a two-variable sensitivity table showing profit across 5 price levels and 5 volume levels, (4) Set up Scenario Manager with Base, Optimistic, and Pessimistic scenarios.

← PreviousCharts & Graphs BasicsNext →Excel Tables (Format as Table)
🎓 Level Up Faster

Learn Excel with Live Trainer Guidance

These tutorials give you the foundations. Our live Excel course at EVIKA Academy, Noida teaches you to build real dashboards on actual business data — with a trainer who uses Excel professionally every day.