What-If Analysis and Scenario Planning

Introduction

Managerial decisions are made under uncertainty. Sales volume, price, labour cost, exchange rates, interest rates and capacity may all differ from the assumptions used in a business plan. A spreadsheet becomes strategically valuable when it does more than calculate one forecast: it allows managers to examine how outcomes change when assumptions change.

Excel’s What-If Analysis tools support this process. Goal Seek works backward from a desired result; Data Tables show the effect of changing one or two assumptions; and Scenario Manager stores and compares complete sets of assumptions. PivotTables and charts then help managers summarise, visualise and communicate the results. Solver extends the analysis by identifying the best feasible decision under constraints.

These tools do not predict the future automatically. They expose the logical and quantitative consequences of assumptions. Their usefulness therefore depends on sound modelling, relevant assumptions, transparent governance and careful interpretation.

Learning Objectives

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

1. Explain the purpose and logic of Excel’s principal What-If Analysis tools.
2. Construct one-variable and two-variable Data Tables for sensitivity analysis.
3. Use Goal Seek to determine an input required to achieve a target output.
4. Build, save and compare scenarios using Scenario Manager.
5. Distinguish What-If Analysis from optimisation with Solver.
6. Summarise scenario results using PivotTables and communicate them with column, line and pie charts.
7. Evaluate model limitations, assumptions, governance risks and managerial implications.

1. The Logic of What-If Analysis

A business model typically contains three categories of cells:

• Input cells: assumptions controlled by the user, such as price, units sold or variable cost.
• Calculation cells: formulas that transform inputs into intermediate results.
• Output cells: decision measures such as profit, cash flow, return on investment or break-even volume.

A simple contribution-profit model is:

where P is selling price per unit, V is variable cost per unit, Q is quantity sold and F is fixed cost. The equation assumes constant unit price and variable cost, a defined relevant range, and no interaction among products.

For a baseline with P = $50, V = $30, Q = 10,000 units and F = $150,000:

A single answer can create false confidence. Sensitivity analysis asks questions such as: What if volume falls by 10%? What if price rises but demand declines? Which assumption has the greatest influence on profit? Scenario planning combines assumptions into coherent alternative futures, such as “downside,” “base” and “growth.”

The central modelling principle is to separate assumptions from formulas. If a formula contains repeated hard-coded numbers, the model becomes difficult to audit and unsuitable for systematic analysis.

2. Goal Seek: Working Backward from a Target

Goal Seek is a one-variable reverse-calculation tool. It changes one input cell until a formula cell reaches a specified value. It is appropriate when:

• there is one target output;
• one input is unknown or adjustable; and
• the relationship between the input and output can be recalculated by Excel.

It does not compare multiple assumptions, change several cells simultaneously or necessarily find an economically feasible answer.

Worked Example: Required Sales Volume

Suppose a firm earns a contribution of $20 per unit and has fixed costs of $150,000. Management requires a profit of $90,000. Using the profit equation:

In Excel, the model might contain:

Select Data → What-If Analysis → Goal Seek and specify:

• Set cell: B8
• To value: 90000
• By changing cell: B4

Excel returns 12,000 units, subject to rounding and model constraints. If only whole units are possible, the manager should verify the rounded result in the original model. If capacity is 11,000 units, Goal Seek identifies a mathematical requirement that is operationally infeasible; management must then reconsider price, cost, capacity or the target.

Interpretation and Limitations

Goal Seek is a diagnostic tool, not a strategy. It answers “what input is required?” rather than “what input should management choose?” Results may be misleading when formulas are discontinuous, inputs have limits, demand responds to price, or multiple solutions exist. Always test the result against business rules and independent calculations.

3. Data Tables and Sensitivity Analysis

A Data Table evaluates a formula repeatedly for alternative values of one or two input cells. It is especially useful for sensitivity analysis because it displays the output pattern rather than a single revised forecast.

One-Variable Data Table

A one-variable table changes one assumption while holding all other assumptions constant. For example, management may test profit at volumes from 8,000 to 12,000 units.

Assume P = $50, V = $30 and F = $150,000:

The increase is $20,000 per additional 1,000 units because contribution per unit is $20. In Excel, place a reference to the output formula above a vertical list of test values. Select the entire range, choose Data → What-If Analysis → Data Table, and identify the relevant Column input cell. Excel substitutes each test value into the input cell and records the output.

A one-variable table can also test price, variable cost, interest rate or exchange rate. The interpretation is conditional: it shows the effect of that variable while other assumptions remain fixed.

Two-Variable Data Table

A two-variable table changes two inputs simultaneously. For example, profit may be examined under alternative prices and sales volumes. With V = $30 and F = $150,000:

The row input cell is price and the column input cell is volume, depending on the table orientation. The table reveals interaction: a price increase is more valuable when volume is high, but it may not be realistic to assume volume remains unchanged when price changes. A more sophisticated model should link volume to price through an explicit demand assumption or estimate.

Sensitivity Interpretation

Sensitivity analysis can identify:

• High-impact assumptions: changes produce large output movements.
• Low-impact assumptions: changes produce relatively small movements.
• Thresholds: points at which profit becomes zero or a covenant is breached.
• Asymmetry: downside may be greater than upside.
• Robust decisions: alternatives that remain acceptable across many assumptions.

Data Tables are not probability forecasts. Unless probabilities are assigned, each tested value is an analytical case, not a statement about likelihood.

4. Scenario Manager: Comparing Coherent Alternatives

Scenario Manager stores groups of values for selected changing cells. Unlike a Data Table, which usually varies one or two dimensions systematically, Scenario Manager can change many related assumptions at once.

For a retail expansion model, changing cells might include:

• selling price;
• monthly units sold;
• variable cost per unit;
• marketing expenditure; and
• fixed operating cost.

A coherent scenario might be:

To use Scenario Manager:

1. Build and test the baseline model.
2. Select Data → What-If Analysis → Scenario Manager.
3. Add a scenario and identify its changing cells.
4. Enter the values for that scenario.
5. Add additional scenarios using the same changing cells.
6. Select one or more result cells, such as profit, cash flow and margin.
7. Generate a Scenario Summary or Scenario PivotTable report.

The changing cells should be assumptions, not formulas. Result cells should be outputs. Scenario names should communicate managerial meaning, such as “Supplier disruption” or “Aggressive expansion,” rather than merely “Scenario 1.”

Scenario Planning versus Forecasting

A forecast is generally an expectation about what is most likely. A scenario is a structured representation of a plausible condition. Good scenario planning does not assign certainty to a single number; it encourages managers to prepare actions for different conditions. Scenarios should be internally consistent. For example, a recession scenario might reasonably include lower volume, higher bad debts and tighter credit, rather than changing only sales.

Scenario Evaluation

A scenario report may be enhanced with performance indicators:

The ratio is meaningful only when revenue and operating profit use consistent periods and definitions. Managers should examine both outcomes and required actions. A high-profit scenario may require unrealistic capacity, while a lower-profit scenario may be more resilient and strategically attractive.

5. Solver: From What-If Analysis to Optimisation

Goal Seek asks what single input achieves a target. Solver addresses a different question: which combination of decision variables maximises or minimises an objective while satisfying constraints?

A Solver model contains:

• an objective cell, such as total profit;
• variable cells, such as quantities allocated to products;
• constraints, such as labour hours, budget, minimum demand or non-negativity; and
• a selected solving method.

For example, a firm producing products A and B may maximise:

subject to:

Here A and B are production quantities, and the coefficients represent resource use and unit contribution. Solver searches for feasible values of the decision variables that optimise the objective. The result depends on the model, constraints and solving method; it is not automatically a realistic recommendation.

Solver is therefore complementary to What-If Analysis. Scenario Manager compares predefined alternatives, while Solver searches for an alternative. A manager may first use Solver to identify a feasible optimum and then use Data Tables or scenarios to test its robustness under changed prices, costs or capacity.

6. PivotTables for Scenario Data Analysis

A PivotTable reorganises a dataset into meaningful summaries without requiring a separate formula for every category. In scenario planning, a scenario output table might contain Scenario, Region, Product, Revenue, Cost, Profit and Margin.

A PivotTable can place:

• Scenario in Rows;
• Region in Columns;
• Profit in Values, summarised by Sum or Average; and
• Product or Year in Filters.

This allows managers to compare total profit, regional contribution or average margin across scenarios. The aggregation must match the question. Summing profit is appropriate for total profit; averaging margins may be misleading if regions have very different revenue levels. A weighted margin can be calculated as:

Data should be tabular: one header row, no merged cells, consistent data types and one observation per row. Refresh the PivotTable after changing source data. A PivotTable is a summary mechanism, not a substitute for validating source data or defining the correct measure.

7. Charts for Managerial Communication

Charts convert scenario results into patterns that decision-makers can interpret quickly. Chart selection should reflect the analytical question.

Column Charts

Column charts compare discrete categories. They are suitable for comparing profit under downside, base and growth scenarios, or comparing regional revenue. Categories should be clearly labelled and the vertical axis should begin at zero when visual magnitude is being compared, unless a justified alternative is explicitly disclosed.

Line Charts

Line charts display movement across an ordered sequence, especially time. They are appropriate for monthly cash flow under alternative scenarios or a sensitivity curve across sales volumes. A line connecting unrelated categories can imply continuity that does not exist, so a column chart may be better for unrelated scenarios.

Pie Charts

Pie charts show parts of a single whole, such as a product’s share of total revenue. They should not be used to compare multiple unrelated totals or values that do not sum to a meaningful whole. Too many slices reduce interpretability. A bar or column chart is often more precise.

Chart design should avoid distorted axes, excessive decoration, unexplained abbreviations and inconsistent units. A chart should state the measure, period, scenario and unit of measurement. Managers should be able to identify whether a visual represents dollars, percentages, units or index values.

8. Integrated Illustrative Case: Northstar Home Devices

The following is a fictional illustrative case.

Background

Northstar Home Devices is considering a smart thermostat launch. The finance team develops a monthly model with a price of $180, variable cost of $95, expected volume of 4,000 units and fixed launch costs of $220,000.

Problem

The executive committee wants to know:

1. What volume is required to earn $100,000 monthly profit?
2. How sensitive is profit to price and volume?
3. What happens under coherent downside, base and growth conditions?
4. Which assumptions deserve management attention?

Analysis

Unit contribution is:

Required volume for $100,000 profit is:

Because units are whole numbers, at least 3,765 units are required under the stated assumptions. A Goal Seek result confirms this value, subject to the model’s rounding convention.

A two-variable Data Table tests prices of $160, $180 and $200 against volumes of 3,000, 4,000 and 5,000 units. It reveals that a higher price improves contribution only if volume is not reduced excessively. The finance team therefore adds a separate demand-risk discussion rather than treating price and volume as independent facts.

Scenario Manager stores three scenarios:

Profit outcomes are:

• Downside: (165 − 105) × 3,000 − 230,000 = −$50,000.
• Base: (180 − 95) × 4,000 − 220,000 = $120,000.
• Growth: (190 − 92) × 5,000 − 235,000 = $255,000.

A column chart compares scenario profit. A line chart can show monthly profit if the launch ramp is modelled over time. A PivotTable summarises profit by scenario and distribution region when regional assumptions are added.

Approach and Decision

The executive committee does not select the growth scenario merely because it has the highest profit. It asks whether production capacity, marketing resources and channel demand support 5,000 units. It approves the base plan with trigger points: expand production if orders exceed 4,500 units and activate cost controls if forecast volume falls below 3,500 units.

Learning Points

The case demonstrates that:

• Goal Seek answers a target question;
• Data Tables reveal sensitivity;
• Scenario Manager compares coherent conditions;
• PivotTables organise multiple scenario records;
• charts communicate patterns; and
• managerial judgement is required to assess feasibility and risk.

9. Model Governance, Limitations and Ethical Issues

Model risk

A spreadsheet may produce a precise result from inaccurate assumptions. Common risks include hard-coded numbers, inconsistent time periods, hidden formulas, accidental overwriting, incorrect cell references and stale PivotTable data.

Correlation and behavioural response

Independent changes may be unrealistic. Price, volume, marketing expenditure and variable cost can be correlated. Data Tables show mechanical effects unless the model explicitly represents those relationships.

Uncertainty and probability

A scenario is not a probability distribution. If probabilities are available, an expected value may be calculated:

where pᵢ is the probability of outcome i, xᵢ is its value, and the probabilities satisfy ∑pᵢ = 1. Expected value is useful for repeated decisions under risk, but it can conceal severe downside exposure and should not replace analysis of the full range of outcomes.

Governance practices

Professional models should include an assumptions register, clear input formatting, formula checks, version control, documented scenario definitions and review by an independent person. Protecting formula cells can reduce accidental changes, but access controls should not prevent legitimate audit.

Managers should disclose assumptions and avoid presenting a selected scenario as an objective forecast. Ethical reporting requires showing material downside cases, not only attractive results. Sensitive commercial data should be access-controlled, and outputs used in lending, employment or customer decisions should be reviewed for bias and compliance implications.

Contemporary practice and AI

Modern Excel features can automate data refresh, recommend charts and assist with formula creation. AI-assisted spreadsheet functions may improve productivity, but generated formulas and interpretations require human verification. Confidential data should not be uploaded to unapproved services. A responsible workflow preserves the source data, records prompts or transformations where material, validates outputs and retains managerial accountability.

Best-Practice Workflow

1. Define the decision and the output measure.
2. Separate inputs, calculations and outputs.
3. Establish a tested baseline.
4. Use Goal Seek for a single target requirement.
5. Use Data Tables for systematic one- or two-variable sensitivity analysis.
6. Use Scenario Manager for coherent multi-variable alternatives.
7. Use Solver only when optimisation and constraints are genuinely required.
8. Summarise detailed results with PivotTables.
9. Select charts that fit the data structure and decision question.
10. Test feasibility, document assumptions and communicate uncertainty.

Chapter Summary

What-If Analysis transforms a spreadsheet from a static forecast into a decision-support model. Goal Seek works backward from a target by changing one input. One-variable Data Tables show the effect of changing one assumption, while two-variable Data Tables examine the interaction of two assumptions. Scenario Manager stores coherent sets of assumptions and supports structured comparison of alternative futures. Solver differs by searching for an optimum subject to constraints.

PivotTables summarise scenario records and support flexible aggregation. Column charts compare categories, line charts display ordered or time-based movement, and pie charts show parts of a meaningful whole. These tools are powerful only when assumptions are explicit, formulas are controlled, source data is valid and results are interpreted with managerial judgement. Sensitivity and scenario analysis should inform decisions, not create false certainty.

Key Terms

Goal Seek: Excel tool that changes one input until a formula reaches a specified target.

What-If Analysis: Examination of how outputs change when model assumptions are altered.

Data Table: Excel structure that calculates an output for alternative values of one or two inputs.

One-variable Data Table: Sensitivity table changing one input at a time.

Two-variable Data Table: Sensitivity table changing two inputs simultaneously.

Scenario: A coherent set of assumptions representing a plausible condition.

Scenario Manager: Excel tool for storing and comparing multiple assumption sets.

Sensitivity analysis: Study of output responsiveness to changes in assumptions.

Input cell: Cell containing a user-controlled assumption.

Calculation cell: Cell containing a formula derived from inputs.

Output cell: Cell containing a decision result or performance measure.

Solver: Excel optimisation tool that searches for decision values subject to constraints.

Objective cell: Solver cell to maximise, minimise or set to a value.

Constraint: Rule limiting feasible values in an optimisation model.

PivotTable: Interactive Excel summary of structured data.

Column chart: Chart for comparing discrete categories.

Line chart: Chart for displaying trends across an ordered sequence.

Pie chart: Chart showing components of one meaningful total.

Model governance: Controls that support spreadsheet accuracy, transparency, security and accountability.

Review Questions

Short Answer Questions

1. What is the principal difference between Goal Seek and Scenario Manager?
2. When is a one-variable Data Table appropriate?
3. Why might a two-variable Data Table produce an unrealistic conclusion if price and volume are related?
4. What are the objective cell and constraints in Solver?
5. Identify one appropriate use for each of a column chart, line chart and pie chart.

Long Answer Questions

1. Explain how a manager could combine Goal Seek, Data Tables and Scenario Manager in a capital investment decision.
2. Describe the assumptions, construction and interpretation of a two-variable Data Table.
3. Compare scenario planning with forecasting and explain why a business should use both downside and growth scenarios.
4. Explain how PivotTables and charts can improve communication of scenario results, including their limitations.
5. Discuss spreadsheet governance practices that reduce the risk of misleading What-If Analysis.

Analytical / Application Questions

1. A firm sells a product for $75, has variable cost of $45 and fixed cost of $240,000. Use the profit model to determine the volume required for a target profit of $60,000. Explain how you would verify the result using Goal Seek and test it with a Data Table.
2. Design three coherent scenarios for a business facing possible currency depreciation. Identify at least five changing cells, the expected direction of each change and two output measures.
3. A Solver model recommends an output level that exceeds warehouse capacity. Diagnose why the result is unacceptable and explain how constraints, scenario analysis and a PivotTable report could support a revised decision.

Multiple Choice Questions with Answers

Question 1

Which Excel tool changes one input cell until a formula reaches a specified target?

A. Goal Seek

B. Scenario Manager

C. PivotTable

D. Solver

Correct Answer: A. Goal Seek

Question 2

A two-variable Data Table is primarily used to examine:

A. Two worksheets at once

B. The effect of two changing inputs on an output

C. Two PivotTable filters only

D. Two chart types

Correct Answer: B. The effect of two changing inputs on an output

Question 3

Which tool stores complete sets of assumptions such as price, volume and cost?

A. Goal Seek

B. Scenario Manager

C. Pie Chart

D. Conditional Formatting

Correct Answer: B. Scenario Manager

Question 4

For Profit = (P − V)Q − F, what is profit when P = 50, V = 30, Q = 10,000 and F = 150,000?

A. $20,000

B. $30,000

C. $50,000

D. $200,000

Correct Answer: C. $50,000

Question 5

Which Excel tool is designed to optimise an objective subject to constraints?

A. Data Table

B. Solver

C. Line Chart

D. Scenario Summary

Correct Answer: B. Solver

Question 6

Which chart is generally most suitable for showing monthly profit over a year?

A. Pie chart

B. Line chart

C. Doughnut chart

D. Radar chart

Correct Answer: B. Line chart

Question 7

What is a major limitation of an ordinary sensitivity table?

A. It cannot display numbers

B. It automatically assigns probabilities

C. It may hold other assumptions constant even when they are related

D. It cannot use formulas

Correct Answer: C. It may hold other assumptions constant even when they are related

Question 8

Which PivotTable value calculation is most directly appropriate for total profit?

A. Sum

B. Count of colours

C. Maximum column width

D. Text concatenation

Correct Answer: A. Sum

Question 9

If contribution per unit is $20, fixed cost is $150,000 and target profit is $90,000, required volume is:

A. 4,500 units

B. 7,500 units

C. 10,000 units

D. 12,000 units

Correct Answer: D. 12,000 units

Question 10

Which practice best supports spreadsheet governance?

A. Hard-code assumptions inside many formulas

B. Hide all downside scenarios

C. Document assumptions and independently review material formulas

D. Use a pie chart for every comparison

Correct Answer: C. Document assumptions and independently review material formulas