Skip to content
Educora
EN
AzərbaycancaEnglishРусскийTürkçe
My learningEducora StudioSettings
Advanced22 min21 / 27

What-if analysis: Goal Seek, scenarios, Data Tables and Solver

Answer “what if…?” questions in a model: find the input that hits a target with Goal Seek, compare scenarios, calculate hundreds of variants at once with a Data Table and find the optimal plan with Solver.

Check yourself
In this lesson you will learn
  • Build a simple model with input, calculation and output parts
  • Find the break-even point and a target price with Goal Seek
  • Compare variants with Scenario Manager and one- and two-variable Data Tables
  • Set up and solve a constrained optimisation problem with Solver

Leyla is opening a small coffee shop in Baku. A cup sells for ₼4, the variable cost (coffee, milk, cup) is ₼1.50 and fixed costs (rent, wages) are ₼3000 a month. What is the profit if she sells 1,500 cups a month? How many cups does she need to avoid a loss? What if the price is ₼4.50? Excel's What-If Analysis tools answer these questions without rewriting any formulas.

The model and Goal Seek

Profit = Q · (P − V) − F
where:
  • Qcups sold per month
  • Pprice per cup, ₼
  • Vvariable cost per cup, ₼
  • Ffixed costs per month, ₼

Setting Profit = 0 gives the break-even point: Q* = F / (P − V). (P − V) is the contribution margin per cup.

  1. 1
    Build the model

    B1 — price (4), B2 — variable cost (1.5), B3 — fixed costs (3000), B4 — units (1500), B5 — profit: =B4*(B1-B2)-B3 → ₼750. Inputs must be plain numbers and the output a formula.

  2. 2
    Open Goal Seek

    Choose Data › Forecast › What-If Analysis › Goal Seek…. Set cell (the output): B5, To value (the target): 0, By changing cell (the input to change): B4.

  3. 3
    Accept or cancel the result

    Excel changes B4 to 1200. Clicking OK overwrites the old value in B4 (1500); if you only want to look, click Cancel.

Two Goal Seek questions

1) Leyla wants ₼2000 profit a month (price ₼4). How many cups must she sell? 2) If sales stay at 1,500 cups, what price gives ₼1500 profit? Check both answers with the formula too.

Show solution
1) Goal Seek: B5 → 2000, changing B4 → 2,000 cups.
Check: Q = (F + Profit) / (P − V) = (3000 + 2000) / 2.5 = 2000.
2) Goal Seek: B5 → 1500, changing B1 → ₼4.50.
Check: 1500 · (P − 1.5) − 3000 = 1500 ⇒ P − 1.5 = 3 ⇒ P = 4.5.
In a linear model Goal Seek is exact; in complex models you may get an approximation such as 1199.9999 — round it with ROUND.

Scenario Manager and Data Tables

With Data › Forecast › What-If Analysis › Scenario Manager… › Add, name each scenario and enter the changing cells (B1, B4): Pessimistic (₼3.50, 1,100 cups), Base (₼4, 1,500), Optimistic (₼4.50, 1,800). Summary… › Result cells: B5 builds a table on a new sheet: profit of −800, 750 and ₼2400 respectively. Scenarios show management the worst, expected and best cases at a glance.

  1. 1
    Prepare the table frame

    A two-variable table: =B5 in D1 (a link to the output), prices in E1:G1 (3.5; 4; 4.5), units in D2:D6 (1000–2000 in steps of 250).

  2. 2
    Run Data Table

    Select D1:G6 › Data › Forecast › What-If Analysis › Data Table…. Row input cell: B1 (price), Column input cell: B4 (units) › OK. Excel recalculates the model for every intersection and writes the array {=TABLE(B1,B4)}.

Interactive
Loading simulation…
The coffee shop model (in manat) and a price × units table.

Solver: the best plan under constraints

Goal Seek changes one input to hit one target. Solver changes several inputs at once, maximises or minimises the result and respects constraints. It is an add-in: File › Options › Add-ins › Manage: Excel Add-ins › Go… › tick Solver Add-in. After that Data › Analyze › Solver appears.

A bakery's daily plan

A bakery makes bread (profit ₼2) and cakes (₼5). A loaf needs 0.1 oven-hours and 0.5 kg of flour, a cake 0.4 oven-hours and 1 kg of flour. Each day there are 20 oven-hours and 80 kg of flour. Which plan maximises profit?

Show solution
Model: B2 — loaves, C2 — cakes (the variable cells).
Profit (D6): =SUMPRODUCT(B2:C2,{2,5}); oven-hours (D4): =SUMPRODUCT(B2:C2,{0.1,0.4}); flour (D5): =SUMPRODUCT(B2:C2,{0.5,1}).
Solver: Set Objective D6, To: Max, By Changing Variable Cells B2:C2; Subject to the Constraints: D4 <= 20, D5 <= 80; tick Make Unconstrained Variables Non-Negative; Select a Solving Method: Simplex LP › Solve.
Answer: 120 loaves and 20 cakes, profit 2 · 120 + 5 · 20 = ₼340.
Check: oven 12 + 8 = 20 hours, flour 60 + 20 = 80 kg — both resources are fully used. For comparison: bread only (160 loaves) gives ₼320, cakes only (50) gives ₼250.
Goal Seek (press in sequence: Alt, A, W, G)Alt+A+W+G
Scenario ManagerAlt+A+W+S
The Data Table dialogAlt+A+W+T
Recalculate the workbook (when Data Tables are set to manual)F9

Key points

  • Split a model into inputs (numbers), calculations and outputs (formulas).
  • Goal Seek changes one input to bring one output to a target; break-even Q* = F / (P − V).
  • Scenario Manager stores named scenarios and builds a summary report.
  • A Data Table calculates every combination of one or two inputs as a {=TABLE()} array.
  • Solver changes several inputs, maximises or minimises the objective and respects constraints.

Check yourself

10 questions. Every correct answer earns XP.

1 / 10
Price ₼5, variable cost ₼2, fixed costs ₼4500. How many units is the break-even point?