What-If Analysis, Scenario Planning, and Sensitivity Analysis

Introduction

Business decisions are made under uncertainty. A manager may need to estimate profit when demand changes, determine the sales volume required to meet a target, compare alternative pricing strategies, or assess the effect of higher costs. Excel provides an integrated set of tools for these questions: Goal Seek, one- and two-variable data tables, Scenario Manager, and structured sensitivity analysis.

These tools do not predict the future automatically. They help decision-makers expose assumptions, calculate consequences consistently, and communicate trade-offs. The quality of a what-if analysis therefore depends not only on Excel technique but also on model design, variable selection, data quality, and interpretation.

Learning Objectives

By the end of this chapter, you should be able to:

• distinguish forecasting, what-if analysis, scenario planning, and sensitivity analysis;
• construct an assumption-driven Excel model;
• use Goal Seek to solve a one-variable target problem;
• create one- and two-variable data tables;
• build and compare scenarios with Scenario Manager;
• calculate and interpret sensitivity measures;
• communicate base, best, and worst cases responsibly; and
• identify limitations, governance risks, and appropriate professional practices.

1. The Logic of What-If Analysis

A business model converts assumptions into outputs. For example, a contribution-profit model may be written as:

Here, price and variable cost are measured in currency per unit, quantity is measured in units, and fixed costs and profit are measured in currency. The equation assumes that price and unit variable cost remain constant over the relevant volume range, that all units produced are sold, and that fixed costs do not change within that range.

A more general model can be represented as:

where Y is the output, X₁ through Xₙ are input assumptions, and f is the model relationship. A what-if question changes one or more inputs and observes the resulting output.

Related concepts

• Forecasting estimates what is likely to happen, usually using historical data, statistical methods, or expert judgment.
• What-if analysis asks what would happen if specified assumptions changed.
• Scenario planning evaluates internally consistent combinations of assumptions, such as a recession case or expansion case.
• Sensitivity analysis examines how strongly an output responds to changes in one or more inputs.
• Target-seeking analysis starts with a desired output and determines the input required to achieve it.

The distinction matters. A scenario is not a probability forecast unless probabilities have been assigned and justified. A sensitivity table shows model responses; it does not establish which input value is most likely.

2. Building a Structured Assumption Model

A reliable analysis separates assumptions, calculations, and outputs. A practical worksheet structure is:

Use consistent formatting: for example, blue font or a light fill for user inputs, black for formulas, and a distinct style for outputs. Assign meaningful names such as Price, Demand, VariableCost, and FixedCost. Named ranges make formulas easier to audit than references such as B7 and C12.

For example, with named cells, revenue may be written as:

and operating profit as:

A structured model should also identify units, period, currency, and timing. Mixing monthly demand with annual fixed costs produces a mathematically neat but economically invalid result. Assumptions should be documented with source, date, owner, and rationale where the model supports a real business decision.

Break-even as a supporting measure

When price exceeds variable cost per unit, the unit contribution is:

Under the assumptions of constant unit contribution and fixed costs, break-even quantity is:

The result is meaningful only when contribution per unit is positive and the business can sell the calculated quantity. If products have different margins, a weighted contribution margin or product-mix model is required instead of this single-product formula.

3. Goal Seek

Goal Seek answers a reverse question: what value of one input makes a formula reach a specified result? It changes one variable while holding other model inputs constant.

Procedure in Excel

1. Create a formula cell for the desired output, such as operating profit.
2. Select Data → What-If Analysis → Goal Seek.
3. In Set cell, select the output formula cell.
4. In To value, enter the target.
5. In By changing cell, select the one input Excel may change.
6. Review the proposed solution and retain it only if it is economically plausible.

Goal Seek changes the selected input; it does not create a permanent scenario record. Record the result or use a separate assumptions table if the result must be reproduced later.

Worked example: target profit

Suppose a retailer has:

• price = $80 per unit;
• variable cost = $50 per unit;
• fixed costs = $90,000; and
• target operating profit = $30,000.

The target quantity can be checked algebraically:

Thus:

Entering the profit formula in Excel and using Goal Seek should return 4,000 units. If demand capacity is only 3,500 units, the result is mathematically valid but operationally infeasible. Goal Seek has solved the equation, not the management problem.

Strengths and limitations

Goal Seek is useful for pricing, required sales volume, loan-payment targets, and minimum service levels. It is limited to one changing cell and one target cell. It may also return a solution outside sensible bounds, especially when formulas contain discontinuities, thresholds, or multiple possible solutions. Always examine the result and test it independently.

4. One-Variable Data Tables

A one-variable data table shows how one output changes as one input takes several possible values. It is especially useful for a range of prices, demand levels, interest rates, or costs.

Construction

Assume the model’s profit formula is in cell B10 and the changing input is B3:

1. Place possible values for the input vertically or horizontally.
2. In the cell at the top of the table, enter a reference to the output formula, such as =B10.
3. Select the entire range, including the output reference and input values.
4. Choose Data → What-If Analysis → Data Table.
5. If values are vertical, specify the Column input cell as B3; if horizontal, specify the Row input cell.
6. Excel calculates the output for each input value.

The table is not a collection of independent formulas. It is a compact sensitivity mechanism linked to the model. Because data tables can increase calculation time, use them selectively in large workbooks.

Interpretation

Suppose profit is evaluated at demand levels of 2,000, 3,000, 4,000, and 5,000 units. A manager can see the absolute profit change, the break-even region, and whether additional demand produces economically meaningful gains. The table does not show the likelihood of each demand level. That requires separate market analysis.

5. Two-Variable Data Tables

A two-variable data table varies two inputs simultaneously and displays the resulting value of one output. For example, a company may examine profit at combinations of price and demand, or loan payment at combinations of interest rate and term.

Construction

1. Place the first variable’s possible values across the top row.
2. Place the second variable’s possible values down the first column.
3. Put a reference to the output formula in the upper-left corner of the table.
4. Select the complete table range.
5. Choose Data → What-If Analysis → Data Table.
6. Assign the top-row variable to Row input cell and the first-column variable to Column input cell.

The row and column assignments must match the layout. Reversing them can produce a plausible-looking but incorrect table.

Worked interpretation

Consider the profit model with price values of $70, $80, and $90 across columns and demand values of 3,000, 4,000, and 5,000 down rows. With variable cost of $50 and fixed costs of $90,000:

• at $70 and 3,000 units, profit = ($70 − $50) × 3,000 − $90,000 = −$30,000;
• at $80 and 4,000 units, profit = ($80 − $50) × 4,000 − $90,000 = $30,000;
• at $90 and 5,000 units, profit = ($90 − $50) × 5,000 − $90,000 = $110,000.

The table reveals interaction: price and volume jointly determine the result. It does not prove that increasing price will leave demand unchanged. If demand depends on price, the model should include a demand relationship rather than treating the two variables as independent.

6. Scenario Manager

Scenario Manager stores named sets of values for several changing cells. It is appropriate when assumptions move together in a coherent narrative.

Creating scenarios

1. Build and test the base model.
2. Select Data → What-If Analysis → Scenario Manager.
3. Add changing cells, such as demand, price, variable cost, and fixed costs.
4. Create scenarios such as Base, Best case, and Worst case.
5. Enter the values for each scenario.
6. Use Summary to produce a comparison report, or use Show to display a scenario in the model.

The changing cells should be assumptions, not calculated outputs. A scenario definition should explain why the values belong together. For example, a recession case might combine lower demand, promotional pricing, and higher bad-debt expense; these are not arbitrary independent changes.

Comparing cases

A useful summary includes assumptions and outputs:

These figures are illustrative. The best case is not necessarily the most likely case, and the worst case is not necessarily the maximum possible loss. The labels should be defined by evidence, planning assumptions, or management policy.

7. Sensitivity Analysis

Sensitivity analysis asks how much an output changes when an input changes. The simplest measure is an absolute change:

where Y₀ is the base output and Y₁ is the output after the assumption changes.

A percentage change in output is:

This percentage is not meaningful when the base output is zero, and it can be misleading when the base output is close to zero or changes sign.

For comparing variables measured in different units, an elasticity-style measure is useful:

A value greater than 1 in absolute terms indicates that output changes proportionally more than the input. This interpretation is local or scenario-specific unless the relationship is constant over the whole range.

Local sensitivity and finite differences

For a small change in an input, a finite-difference estimate of the local rate of change is:

Its units are output units per input unit. For example, if profit rises from $30,000 to $34,000 when demand rises from 4,000 to 4,200 units, the estimated sensitivity is $4,000 ÷ 200 = $20 per additional unit. This should not be interpreted as a universal marginal contribution if costs or prices change at other volumes.

Tornado charts and ranking

A tornado chart ranks inputs by their effect on an output, commonly using a low and high value around the base case. It helps prioritize investigation. However, the ranking depends on the selected ranges, the order of calculations, and whether variables are changed independently. It should not be used to imply causality or probability.

8. Integrated Applied Case: NovaBrew Coffee Machines

Background

NovaBrew, an illustrative small manufacturer, is considering a six-month launch of a compact commercial coffee machine. Management wants to know whether the launch can meet a profit target and how exposed the decision is to demand and cost uncertainty.

Problem and assumptions

The base assumptions are:

The model is:

The contribution per machine is $500, and break-even sales are:

Analysis

Using Goal Seek with target profit of $100,000 and sales as the changing cell gives:

Thus, the base forecast has a 100-machine cushion above the target, assuming the other inputs remain unchanged.

A one-variable data table for sales from 600 to 1,200 machines shows the break-even point and the profit progression. A second table for variable cost from $650 to $800 identifies cost exposure. A two-variable table combining sales and variable cost shows that a decline in demand becomes more serious when supplier costs also increase.

Management then defines three scenarios:

For the worst case, profit is calculated as ($1,150 − $800) × 800 − $370,000 = −$90,000. For the best case, it is ($1,250 − $650) × 1,200 − $340,000 = $380,000.

Outcome and recommendation

The base case meets the target, but the worst case produces a substantial loss. The analysis suggests that management should investigate supplier contracts, demand validation, and staged marketing expenditure. A launch approval based only on the base case would conceal meaningful downside risk. A staged decision—release an initial production batch, monitor orders, and authorize later fixed spending only when demand evidence is obtained—would reduce commitment under uncertainty.

Learning points

1. Goal Seek identifies the required sales volume but does not establish whether that volume is achievable.
2. Data tables reveal continuous ranges and interactions more clearly than three named cases.
3. Scenario Manager communicates coherent narratives but depends on defensible scenario definitions.
4. The most important managerial question is often not “What is the expected profit?” but “What assumptions could make the decision fail?”

9. Communicating Decision Risk

A professional report should state:

• the decision being supported;
• the model period and currency;
• the base assumptions and their sources;
• the range or rationale for alternative assumptions;
• the output measures used;
• the threshold for success or failure;
• key sensitivities and dependencies; and
• actions that would reduce uncertainty.

Use clear labels such as “illustrative downside case” rather than presenting a worst case as a forecast. Report both absolute and percentage changes where meaningful. Charts should show units and avoid distorted axes. A concise management message might be: “The launch meets the target at 900 units; the base plan expects 1,000 units, but the downside case loses $90,000 because lower volume coincides with higher unit cost.”

10. Limitations, Governance, and Ethics

What-if tools are deterministic: identical inputs produce identical outputs, but the inputs may be uncertain. Common limitations include:

• False precision: displaying dollars to the nearest unit can imply more certainty than the assumptions justify.
• Omitted variables: taxes, working capital, capacity, competitor responses, and cash timing may be absent.
• Correlation ignored: demand, price, and marketing spend may not vary independently.
• Nonlinear effects: discounts, capacity limits, step costs, and commission thresholds can make simple sensitivity tables misleading.
• Model risk: incorrect cell references, sign errors, hidden hard-coded values, or stale links can invalidate results.
• Scenario bias: optimistic and pessimistic cases may reflect advocacy rather than evidence.

Governance practices include version control, protected formula cells, documented assumptions, independent review, error checks, and a change log. Test extreme but plausible values and verify key outputs outside Excel when the decision is material. Preserve the original base case before experimenting.

Ethically, analysts should not select assumptions merely to obtain an approved investment. Downside cases, uncertainty ranges, and limitations should be disclosed. If an analysis affects employees, customers, credit applicants, or suppliers, reviewers should consider fairness, privacy, and potential discriminatory effects. Excel should support accountable judgment, not replace it.

11. Contemporary Practice and AI

Modern Excel versions support dynamic formulas, Power Query, Power Pivot, and automated dashboards that can improve scenario workflows. These technologies are useful when assumptions come from multiple operational sources, but they increase the need for data lineage and access controls.

Generative AI can suggest formulas, explain model logic, or help draft scenario narratives. It can also generate incorrect references, silently change assumptions, or invent evidence. Therefore, AI-assisted workbooks require cell-by-cell validation, source verification, formula inspection, and human approval. AI should accelerate analysis, not become an unexamined authority.

Best-Practice Checklist

• Separate input, calculation, output, and documentation areas.
• Use named ranges or structured tables where appropriate.
• Record units, periods, currency, source, and date for assumptions.
• Validate formulas with independent arithmetic checks.
• Use Goal Seek for one target and one changing input.
• Use data tables for systematic ranges and interactions.
• Use Scenario Manager for coherent multi-input cases.
• Define best and worst cases transparently.
• Test feasibility, capacity, and operational constraints.
• Communicate uncertainty rather than hiding it behind precise numbers.
• Protect, review, version, and document important workbooks.

Chapter Summary

What-if analysis evaluates the consequences of changed assumptions; scenario planning compares coherent combinations of assumptions; and sensitivity analysis measures how strongly outputs respond to inputs. Goal Seek solves for one input that produces a specified output. One-variable data tables examine a range for one input, while two-variable tables show interactions between two inputs. Scenario Manager stores and compares named assumption sets such as base, best, and worst cases.

Effective analysis begins with a structured model that separates assumptions, calculations, and outputs. Equations such as contribution profit and break-even provide useful foundations, but their assumptions must be respected. Excel results are conditional, not forecasts by themselves. Good professional practice combines technical accuracy with feasibility checks, documentation, governance, ethical disclosure, and clear communication of risk and trade-offs.

Key Terms

What-if analysis: Examination of model outputs after changing one or more assumptions.

Scenario planning: Evaluation of coherent combinations of assumptions representing alternative futures.

Sensitivity analysis: Study of how changes in inputs affect an output.

Goal Seek: Excel tool that changes one input to achieve a specified formula result.

Data table: Excel what-if tool that calculates outputs for a range of one or two changing inputs.

Scenario Manager: Excel feature that stores and compares named sets of changing-cell values.

Assumption: An input value accepted for purposes of analysis.

Base case: The central or planning set of assumptions.

Best case: A favourable, explicitly defined combination of assumptions.

Worst case: An adverse, explicitly defined combination of assumptions.

Contribution per unit: Selling price minus variable cost per unit.

Break-even quantity: Sales volume at which profit equals zero under stated assumptions.

Target output: The result an analyst wants a model to achieve.

Elasticity: Percentage change in output divided by percentage change in input.

Finite difference: Approximation of a rate of change using two observed points.

Tornado chart: A chart ranking variables by their effect on an output.

Model risk: Risk of incorrect decisions caused by flawed model design, data, or implementation.

Data lineage: Documentation of where data and assumptions originate and how they are transformed.

Review Questions

Short Answer Questions

1. What is the difference between sensitivity analysis and scenario planning?
2. Why should assumptions, calculations, and outputs be separated in an Excel model?
3. What does Goal Seek change, and what does it hold constant?
4. When is a two-variable data table more useful than a one-variable data table?
5. State two limitations of deterministic what-if analysis.

Long Answer Questions

1. Explain how to construct and interpret a Scenario Manager comparison of base, best, and worst cases.
2. Discuss the assumptions behind the break-even quantity formula and explain when it may be inappropriate.
3. Compare Goal Seek, data tables, and Scenario Manager in terms of purpose, inputs, outputs, and limitations.
4. Explain how a manager should communicate uncertainty and downside risk in a decision report.
5. Describe governance practices that reduce model risk in an important Excel workbook.

Analytical / Application Questions

1. A firm sells a service for $120 per customer, has variable cost of $45, fixed costs of $150,000, and a target profit of $75,000. Calculate the required number of customers and explain how Goal Seek would be used to verify it.
2. Design a two-variable data table for a business whose profit depends on price and demand. Specify the row variable, column variable, output formula, and two limitations of interpreting the table.
3. Construct a base, best, and worst scenario for a product launch. Identify at least four changing assumptions, calculate or describe the key output, and recommend one action that would reduce downside risk.

Multiple Choice Questions with Answers

Question 1

Which Excel tool determines the value of one input needed to reach a specified output?

A. Goal Seek

B. Scenario Manager

C. Solver Table

D. Conditional Formatting

Correct Answer: A. Goal Seek

Question 2

A one-variable data table changes how many model inputs at a time?

A. None

B. One

C. Two

D. Any number

Correct Answer: B. One

Question 3

Which formula represents contribution per unit?

A. Fixed costs ÷ quantity

B. Price + variable cost per unit

C. Price − variable cost per unit

D. Revenue − fixed costs

Correct Answer: C. Price − variable cost per unit

Question 4

A two-variable data table is most appropriate for examining:

A. Two inputs and one output across combinations

B. Two outputs and one fixed input

C. Many scenarios with unrelated formulas

D. Only historical observations

Correct Answer: A. Two inputs and one output across combinations

Question 5

In the NovaBrew case, what sales volume was required for a $100,000 target profit?

A. 700 machines

B. 800 machines

C. 900 machines

D. 1,100 machines

Correct Answer: C. 900 machines

Question 6

What does Scenario Manager primarily store?

A. Sets of values for changing cells

B. Only chart formatting

C. External database records

D. A single target cell

Correct Answer: A. Sets of values for changing cells

Question 7

If profit rises from $30,000 to $34,000 when demand rises from 4,000 to 4,200 units, the finite-difference sensitivity is:

A. $4 per unit

B. $20 per unit

C. $200 per unit

D. $4,000 per unit

Correct Answer: B. $20 per unit

Question 8

Which statement about a worst-case scenario is most accurate?

A. It is automatically the statistically most likely outcome.

B. It must represent the maximum imaginable loss.

C. It is an explicitly defined adverse combination of assumptions.

D. It changes only one input.

Correct Answer: C. It is an explicitly defined adverse combination of assumptions.

Question 9

Why can a Goal Seek result be unsuitable for implementation?

A. It never changes an input.

B. The calculated input may be operationally infeasible.

C. It cannot use formula cells.

D. It always requires two changing cells.

Correct Answer: B. The calculated input may be operationally infeasible.

Question 10

Which practice most directly reduces Excel model risk?

A. Hiding all assumptions

B. Rounding every output to zero decimals

C. Using undocumented hard-coded values

D. Independent review and documented assumptions

Correct Answer: D. Independent review and documented assumptions