LearnLive

Excel scenario analysis

What-If Analysis with Goal Seek and Solver

Learn when to use Goal Seek, Data Tables, or Solver in Excel, with a break-even example and checks that keep the model honest.

How this page is maintained

Written for learners, checked against the sources below, and reviewed every year. Last reviewed July 22, 2026.

Short answer

Use Goal Seek when one formula result must reach a target by changing one input. Use a Data Table to compare many possible values for one or two inputs. Use Solver when several inputs, constraints, or an optimization objective must be handled together.

  • A what-if tool changes inputs; it cannot repair a weak model or unrealistic assumption.
  • Name the objective, changing cells, and constraints before opening Solver.
  • Check whether the result is feasible in the real world, not only mathematically valid.

Use the smallest tool that fits

Goal Seek works backward through a formula. If profit equals revenue minus cost, Goal Seek can find the unit volume needed for profit to equal zero. It changes only one input and returns one solution.

Solver handles questions such as maximize contribution margin by choosing quantities of several products while respecting labor, material, and demand constraints. The model must express those relationships in worksheet formulas first.

Treat output as a scenario, not a forecast

The result is conditional on every assumption in the sheet. Test nearby values and identify the assumptions that change the answer most. A precise-looking optimum can be fragile if price, demand, or capacity is uncertain.

  • Keep assumptions in labeled cells.
  • Add realistic lower and upper bounds.
  • Save the baseline before running a scenario.

Find break-even unit volume

Price is $50, variable cost is $30, fixed cost is $20,000, and Profit is calculated from Units sold.

  1. Confirm the profit formula: Units*(Price-Variable cost)-Fixed cost.
  2. Open Goal Seek, set the Profit cell to 0, and choose Units sold as the changing cell.
  3. Check the returned volume by substituting it into the formula and rounding only if whole units are required.
Result: The break-even volume is 1,000 units before any whole-unit or capacity adjustment.

Common mistakes

  • Using Goal Seek when more than one input must change.
  • Optimizing a result without constraints that represent capacity or policy.
  • Accepting a fractional result where only whole units are possible.
  • Presenting a scenario output as a prediction of what will happen.

Try one

You need to maximize profit by changing three product quantities under two capacity limits. Which tool fits?

Solver, because the problem has several changing cells, an objective, and constraints.

Sources

Excel dashboard guideDashboard designExcel automation guidePower Query basicsBeginner Excel guideExcel basics

Learn this with a tutor

Tell LearnLive what you already know and what you need to do with goal seek and solver.

Build this course