What-If Analysis: Excel Business Analysis Chapter 8

Why this chapter matters

Leaders need to understand best case, expected case, and risk case before committing resources.

What you will learn

Use Goal Seek to solve for required input values.
Build one-variable and two-variable data tables for sensitivity checks.
Compare scenarios with clear assumptions and output deltas.

Understand the core ideas

What-if analysis in Excel helps you move from reporting to decision support. Begin with a transparent model where key inputs such as price, variable cost, fixed cost, and volume are isolated in an assumptions block. Output metrics like profit or margin should reference those assumptions directly. Goal Seek is useful when you know the desired output and need one required input, for example required units to hit target profit. It changes one input at a time, so it is ideal for simple back-solving but not for multi-variable optimization. For broader planning, data tables show how output shifts across ranges of one or two inputs. Build these only after base formulas are stable, because sensitivity grids amplify any formula error.

Scenario comparisons should be explicit about assumptions and limits. Store baseline, optimistic, and risk cases with clear labels so leadership can see what changed. Avoid mixing scenario changes with manual edits scattered through the workbook, because that breaks traceability. Present output deltas in both absolute and percentage terms when relevant. If your model includes nonlinear behavior, validate a few points manually to confirm expectations. Keep calculation options on automatic unless there is a known performance reason, and recalc before exporting results. Scenario Manager availability can vary by platform experience, so if teams use mixed environments, maintain a simple assumptions table approach as a universal fallback.

Key terms

Goal Seek: An Excel tool that adjusts one input value until a formula reaches a specified target result.
Data table: A what-if analysis grid that recalculates an output formula across one or two changing input ranges.
Scenario: A labeled set of input assumptions used to compare possible business outcomes.
Sensitivity analysis: A method for measuring how output changes when one or more assumptions vary.

Solve Required Sales Volume for Profit Target

Assumptions: Price in B2, VariableCost in B3, FixedCost in B4, Units in B5. Profit formula in B6 is =(B2-B3)*B5-B4. Target profit is 250000.

Enter baseline assumptions and confirm B6 updates correctly when B5 changes.
Run Goal Seek: set cell B6 to 250000 by changing cell B5.
Record the solved unit volume and sanity check by plugging it into the formula manually.
Create a one-variable data table varying Price around baseline and linking output to B6 to see profit sensitivity.
Create a two-variable data table for Price and VariableCost to map where profit stays above target.

Result: You obtain a concrete required unit target and a sensitivity map that shows how pricing or cost changes affect the chance of hitting profit goals.

A common misconception

Claim: Goal Seek can replace full scenario planning.

Correction: Goal Seek solves only one changing input. Real planning usually needs multi-input sensitivity and explicit scenario comparison.

Lessons in this chapter

Goal Seek basicsBack-solve a target metric by changing one driver.
Sensitivity tablesEvaluate output changes across parameter ranges.
Scenario managerStore and compare assumption sets for planning discussions. Read the full guide →
Decision framingPresent assumptions and confidence limits clearly.

Study task

Find the required sales volume to hit a quarterly profit target, then test sensitivity to price and cost changes.

Chapter checkpoint

When is Goal Seek not enough for planning?

Goal Seek handles one changing input at a time, so multi-input tradeoffs need data tables or scenario comparisons.

What-If Analysis