How to Use What-If Analysis in Excel: Goal Seek and Scenarios

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:

Follow me on Instagram@sagnikteaches Connect on LinkedInSagnik Bhattacharya

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?"

Subscribe on YouTube@sagnikteaches

Example: Break-Even Price

Suppose you have a simple profit formula:

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. For a real-world application, see how to use this in building complete financial models.

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.

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. You can also embed these sensitivity grids into interactive dashboards for live decision-making.

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. A solid understanding of advanced Excel formulas helps you set up effective Solver constraints.

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.

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. You can also use Claude AI to generate the complex formulas these tools depend on.

Related Posts

• Excel for Financial Modelling — A Beginner's Guide
• 15 Advanced Excel Formulas Every Professional Should Know
• Creating Dynamic Dashboards in Excel
• The Complete Excel Guide 2026 — hub for every Excel tutorial on the site.

Sources

• Microsoft Excel Documentation
• Microsoft 365 Blog
• ExcelJet Function Reference