What-If Analysis in Excel: Goal Seek, Scenarios, Data Tables and Solver | Free Excel Course: From Beginner to Advanced

What you'll learn

Find the input you need to hit a target with Goal Seek.
Compare pessimistic, base and optimistic scenarios with the Scenario Manager.
See at a glance how a result changes with a two-variable data table.
Solve an optimization problem with Solver.
Read the break-even point on a chart and avoid the classic mistakes with these tools.

An Excel model isn't only for calculating what happened: its real value is answering questions about what could happen. "How many units do I need to sell to make $5,000?" "What if the price drops a dollar?" "Which product mix earns the most?" Excel groups these tools under Data → What-If Analysis and adds a very powerful add-in: Solver.

4tools: Goal Seek, data tables, scenarios and Solver

1,200loaves a month is the break-even point for the bakery in this lesson

+$150a month that Solver finds compared with the "gut feeling" decision

Build the model
Goal Seek
Data table
Scenarios
Solver

The starting model

An artisan bakery in Minneapolis sells sourdough loaves for $12 each. Each loaf costs $5 in ingredients and packaging (variable cost), and fixed costs (rent, salaries, utilities) are $8,400 a month. It sells 1,500 loaves a month. Build this model, with inputs kept separate from calculations:

Cell	Item	Value or formula
B1	Price	12
B2	Variable cost per unit	5
B3	Units sold	1500
B4	Fixed costs	8400
B6	Contribution margin	`=(B1-B2)*B3` → 10,500
B7	Profit	`=B6-B4` → 2,100

The golden rule of modeling: no numbers typed inside formulas. If the price lives in B1 and every formula reads it from there, you can change it and everything recalculates. Every tool in this lesson depends on that discipline.

Goal Seek: the target drives the input

How many loaves does the bakery need to sell to make $5,000 a month? Instead of trying numbers by hand:

Data → What-If Analysis → Goal Seek.
Set cell: B7 (profit). To value: 5000. By changing cell: B3 (units).
Click OK. Excel writes 1,914.29 in B3.

Since you can't sell a fraction of a loaf, the practical answer is 1,915 loaves. Ask for a profit of 0 and you get the break-even point: 1,200 loaves. Goal Seek works with one changing cell and one target; when you need more, you'll use Solver.

The break-even point is easy to see on a chart: it's where the revenue line crosses the total-cost line.

Revenue $12 × unitsAt 1,500 loaves: $18,000.
Total costs $8,400 + $5 × unitsThe dashed line is fixed costs: you pay them even if you sell nothing.
Break-even 1,200 loavesRevenue = costs = $14,400: zero profit.
Profit at 1,500 loaves $2,100The gap between the two lines: $18,000 − $15,900.

Left of the crossing you lose money; right of it you make money. Every loaf above 1,200 adds $7 of profit ($12 − $5).

For more on contribution margin and pricing from the accounting side, see the cost accounting and pricing lesson in our bookkeeping course.

1. What does Goal Seek need in order to work?

A cell with a formula, the value you want it to reach and an input cell it depends on.
Several changing cells and constraints.
A PivotTable with the data.

Goal Seek adjusts a single input cell until the chosen formula returns the target value. The formula must depend, directly or indirectly, on that cell.

Data tables: every combination at once

You want to see profit at several prices ($10 to $14) and several volumes (1,200, 1,500 and 1,800 loaves). A two-variable data table calculates them all without copying the model:

In D2 type =B7 (the result cell). That's the corner of the table.
To its right, in E2:G2, the units: 1200, 1500, 1800. Below it, in D3:D7, the prices: 10 to 14.
Select D2:G7 and go to Data → What-If Analysis → Data Table.
Row input cell: B3 (units). Column input cell: B1 (price). Click OK.

Profit	1,200 loaves	1,500 loaves	1,800 loaves
$10	−$2,400	−$900	$600
$11	−$1,200	$600	$2,400
$12	$0	$2,100	$4,200
$13	$1,200	$3,600	$6,000
$14	$2,400	$5,100	$7,800

Add a color-scale conditional format (lesson 4) and the loss zone jumps out. The business takeaway: cutting the price to $10 to sell more only makes sense if volume climbs to around 1,800 loaves; at 1,500, the bakery would lose money.

One-variable data table: what if rates go up?

It works with a single input too. Take Ashley's loan from the last lesson ($15,000 over 24 months, with the monthly rate in B2 and the payment in a cell with =PMT(B2, 24, -15000)). Type the monthly rates in a column, put = the payment cell one row above and one column to the right of the first rate, select the block and use Data Table, leaving the row input cell blank and B2 as the column input cell:

APR	Monthly rate (input column)	Payment
12%	1.00%	$706.10
15%	1.25%	$727.30
18%	1.50%	$748.86
21%	1.75%	$770.78
24%	2.00%	$793.07

Every 3 points of APR changes the payment by about $21 to $22 a month. Before you negotiate with a lender, you already know what each rate cut is worth to you.

If the workbook slows down

Data tables recalculate with every change. In large models, go to Formulas → Calculation Options → Automatic Except for Data Tables and press F9 when you want to update them.

Scenario Manager: pessimistic, base and optimistic

A scenario is a set of values for several inputs at once. For example:

PessimisticProfit: −$1,250

Price: $11
Variable cost: $5.50
Units: 1,300

Loss

BaseProfit: $2,100

Price: $12
Variable cost: $5
Units: 1,500

The plan

OptimisticProfit: $4,690

Price: $12.50
Variable cost: $4.80
Units: 1,700

If all goes well

In Data → What-If Analysis → Scenario Manager, click Add, name the scenario, choose the changing cells (B1:B3) and type their values. Repeat for each scenario. Show applies one to the model, and Summary creates a sheet that compares every scenario against the result cell (B7). It's an ideal format for a manager, an investor or an SBA loan officer: it shows plainly what happens if things go worse than planned.

2. You want a report comparing three combinations of price, cost and volume. Which tool fits best?

Goal Seek
VLOOKUP
Scenario Manager, with its summary

Scenarios store several input values at once, and the summary compares the results in one table.

Solver: the best decision under constraints

Solver finds the optimal (maximum or minimum) value of a cell by changing several cells at once while respecting constraints. Turn it on once under File → Options → Add-ins → Manage: Excel Add-ins → Go → Solver Add-in (on a Mac: Tools → Excel Add-ins), and it appears on the Data tab.

The case: a custom furniture shop in Nashville makes tables ($120 profit each) and chairs ($50 each). Each table needs 4 hours of woodworking and 2 of finishing; each chair, 1.5 and 1.5. This month there are 400 woodworking hours and 260 finishing hours, and the shop can sell at most 80 tables. How many of each should it build?

B2  Tables to build           (changing cell)
B3  Chairs to build           (changing cell)
B5  Woodworking hours         =4*B2+1.5*B3
B6  Finishing hours           =2*B2+1.5*B3
B8  Total profit              =120*B2+50*B3

In Data → Solver:

Set Objective: B8, To: Max.
By Changing Variable Cells: B2:B3.
Subject to the Constraints: B5 <= 400; B6 <= 260; B2 <= 80; B2:B3 = integer.
Check Make Unconstrained Variables Non-Negative and choose the Simplex LP method (the model is linear).

Solver's answer: 70 tables and 80 chairs, for a profit of $12,400, using exactly all 400 woodworking hours and all 260 finishing hours. The gut-feeling plan ("tables earn more, let's build 80") gives 80 tables and 53 chairs: $12,250. Solver found $150 more a month just by combining resources better.

Pick the right solving method

Simplex LP is for linear models (sums and products by constants). If your model has nonlinear formulas, like prices that depend on volume, use GRG Nonlinear; if it has IF, VLOOKUP or other functions with jumps, use Evolutionary. The wrong method can return a solution that isn't the best one.

3. Why add the constraint "B2:B3 = integer" in the furniture shop case?

Because Solver only works with whole numbers.
Because you can't build a fraction of a table or chair.
To make the calculation faster.

Without it, Solver could suggest 70.4 tables. The integer constraint forces solutions you can actually carry out.

Common what-if analysis mistakes

Running Goal Seek on a cell that holds a number, not a formula.

The target cell must contain a formula that depends on the cell you change; otherwise Excel shows an error.

Putting a data table's input cell on another sheet.

Excel requires it to be on the same sheet as the table. If the model lives elsewhere, link it through a helper cell.

Forgetting the integer or non-negative constraints in Solver.

Without them, Solver may suggest 70.4 tables or −5 chairs.

Assuming Solver's answer is the only one.

There can be ties: check that the answer meets every constraint and ask whether another one is just as good.

4. If the bakery's fixed costs rise to $9,100 (price $12, variable cost $5), what's the new break-even point?

1,200 loaves
1,300 loaves
1,820 loaves

Each loaf leaves $12 − $5 = $7 to cover fixed costs: $9,100 / 7 = 1,300. Dividing by the variable cost ($9,100 / 5 = 1,820) is the classic mistake.

Practice exercise: your own what-if model

Build the bakery model and use Goal Seek to find the price that earns a $4,000 profit at 1,500 loaves.
Create the three scenarios above and generate the summary.
Solve the furniture shop case with Solver. Then change the chair profit to $40 and solve again: does the best mix change?

See solution

1) Price for a $4,000 profit:
   (P − 5) × 1,500 − 8,400 = 4,000  →  P = $13.27 (Goal Seek returns 13.2667)

2) Profits in the summary: pessimistic −$1,250 · base $2,100 · optimistic $4,690
   Pessimistic: (11 − 5.50) × 1,300 − 8,400 = −1,250
   Optimistic: (12.50 − 4.80) × 1,700 − 8,400 = 4,690

3) Chair at $40: the best mix changes, and there's a tie between two answers:
   80 tables and 53 chairs: 80 × 120 + 53 × 40 = $11,720
   79 tables and 56 chairs: 79 × 120 + 56 × 40 = $11,720
   (70 tables and 80 chairs would now earn $11,600)

Solver may return either one: both meet the constraints and earn the same. With a lower chair profit, it now pays to build (almost) the maximum number of tables and use the leftover hours on chairs. That's why models get revisited when prices or costs change: this month's best decision may not be next month's.

Summary

Keep inputs separate from calculations: a good model never has numbers typed inside formulas.
Goal Seek finds the input value that hits a target.
A data table shows the result for many combinations of one or two variables.
The Scenario Manager compares complete sets of inputs and produces a presentable summary.
Solver optimizes with several variables and constraints; choose Simplex LP for linear models.
Break-even = fixed costs ÷ (price − variable cost).

Next lesson: Power Pivot and the Excel Data Model: Relationships and DAX Measures. You'll break the one-table limit: relate several tables and write DAX measures inside Excel.