Excel What-If Analysis — Goal Seek, Scenarios and Data Tables

Most Excel users build formulas that answer one question: "Given these inputs, what's the output?" What-If Analysis flips that around — it lets you ask "What inputs do I need to hit a target?" or "How does the output change across different assumptions?" These tools live under Data → What-If Analysis and are some of the most powerful yet underused features in Excel.

The Four What-If Analysis Tools

Excel gives you four distinct tools, each solving a different type of problem:

Tool	Best For	Direction
Goal Seek	Finding one input to hit a specific target	Backward (output → input)
Scenario Manager	Comparing named sets of assumptions side by side	Forward (inputs → outputs)
Data Tables	Sensitivity analysis across one or two variables	Forward (inputs → outputs)
Solver	Optimising a result subject to constraints	Backward (output → multiple inputs)

Goal Seek — Reverse-Engineer Any Formula

Goal Seek answers: "What value does one cell need to be for a formula to return a specific result?"

Example: Break-Even Price

Suppose you have a simple profit formula:

Cell	Label	Value
B2	Price per unit	₹200
B3	Units sold	500
B4	Fixed costs	₹80,000
B5	Profit	=(B2*B3)-B4 → ₹20,000

You want to know: what price per unit makes profit exactly zero?

1. Go to Data → What-If Analysis → Goal Seek
2. Set cell: B5 (the profit formula)
3. To value: 0
4. By changing cell: B2 (price per unit)

Excel iterates and finds that ₹160 per unit is the break-even price. This works with any formula — loan payments, margins, growth rates — as long as the target cell depends on the changing cell.

Limitations

• Only changes one input cell at a time
• The target cell must contain a formula (not a static value)
• It doesn't save results — write down the answer or undo to restore the original value

Scenario Manager — Compare Multiple Assumptions

Scenario Manager lets you define named sets of input values and switch between them instantly. Unlike Goal Seek, it works with multiple changing cells and generates a summary report.

Example: Sales Forecast Scenarios

Assume cells B2 (growth rate), B3 (ad spend), and B4 (conversion rate) drive your revenue formula in B8.

1. Go to Data → What-If Analysis → Scenario Manager
2. Click Add and name it "Base Case"
3. Select changing cells: B2:B4
4. Enter values: 10%, ₹50,000, 3%
5. Repeat for "Best Case" (15%, ₹75,000, 4.5%) and "Worst Case" (5%, ₹30,000, 1.5%)

Click Summary to generate a side-by-side report showing how each scenario affects your result cells. Excel creates a new worksheet with a clean comparison table.

When to Use It

• Presenting options to stakeholders ("here are three plans")
• Budget planning with optimistic/pessimistic assumptions
• Comparing hiring plans, pricing tiers, or marketing strategies

Data Tables — Sensitivity Analysis Made Visual

Data Tables automate the process of running a formula across a range of input values. They come in two flavours: one-variable and two-variable.

One-Variable Data Table

You want to see how profit changes as price varies from ₹100 to ₹300:

1. In column A, list your test values: 100, 125, 150, 175, 200, 225, 250, 275, 300
2. In cell B1, enter the formula =B5 (pointing to your profit formula)
3. Select the entire range (A1:B10)
4. Go to Data → What-If Analysis → Data Table
5. Column input cell: B2 (the price cell)

Excel fills column B with the profit at each price point. You immediately see the break-even threshold and the sensitivity of profit to price changes.

Two-Variable Data Table

Even more powerful — test two inputs simultaneously. Put price values across the top row and units sold down the first column. Excel fills the grid with the profit for every combination.

	₹150	₹200	₹250
300 units	-₹35,000	-₹20,000	-₹5,000
500 units	-₹5,000	₹20,000	₹45,000
700 units	₹25,000	₹60,000	₹95,000

This grid is invaluable for pricing decisions, capacity planning, and investment analysis. Apply conditional formatting to colour-code the results and the insights jump off the screen.

Solver — Optimisation with Constraints

Solver is the most advanced What-If tool. It finds the optimal value for a target cell by adjusting multiple input cells, subject to constraints you define.

Enabling Solver

Solver is an add-in that ships with Excel but isn't enabled by default:

1. Go to File → Options → Add-ins
2. At the bottom, select "Excel Add-ins" and click Go
3. Check Solver Add-in and click OK

You'll now find Solver under the Data tab on the far right.

Example: Maximise Profit with Constraints

You sell three products. Each has a different profit margin and requires different production hours. You have limited hours and minimum order quantities.

Product	Profit/Unit	Hours/Unit	Min Order
A	₹120	2	50
B	₹200	3.5	30
C	₹80	1	100

Total available hours: 1,000. Solver finds the quantity of each product that maximises total profit while respecting all constraints.

1. Set objective: Total Profit cell → Max
2. By changing: Quantity cells for products A, B, C
3. Subject to: Total hours ≤ 1,000; each quantity ≥ its minimum order

When to Use Solver

• Resource allocation — distributing budget, staff, or materials optimally
• Scheduling — minimising overtime while covering shifts
• Portfolio optimisation — maximising return for a given risk level
• Transportation problems — minimising shipping cost across warehouses

Choosing the Right Tool

Here's a quick decision guide:

• "What input gives me this exact output?" → Goal Seek
• "How do my results compare across 3-4 different plans?" → Scenario Manager
• "How sensitive is my output to changes in one or two inputs?" → Data Tables
• "What's the best possible outcome given these constraints?" → Solver

These tools complement each other. A common workflow: use Data Tables for initial sensitivity analysis, Scenario Manager to present shortlisted options, and Goal Seek or Solver to fine-tune the final numbers.

Related Posts

• Excel for Financial Modelling — A Beginner's Guide
• 15 Advanced Excel Formulas Every Professional Should Know
• Creating Dynamic Dashboards in Excel