Excel What-If Analysis
Test targets, compare alternative assumptions and measure how changes in price, cost, volume, interest rate or demand can influence business results before a decision is implemented.
After This Chapter, You Will Be Able To
Choose the right Excel decision tool, build a controlled model and convert assumptions into actionable business insight.
Build a Model
Separate assumptions, calculations and outputs so every What-If test remains reliable.
Find Targets
Use Goal Seek to determine the single input needed for a required result.
Compare Alternatives
Create scenarios for conservative, expected and optimistic business conditions.
Measure Sensitivity
Use Data Tables to study how one or two variables influence an important output.
What-If Analysis Begins with a Reliable Formula Model
Excel can calculate alternative outcomes only when the assumptions and formulas are correctly connected.
1 Understanding What-If Analysis
What-If Analysis is the process of changing one or more model assumptions to observe how a formula result changes. It supports planning and decision-making because managers can evaluate possibilities before committing money, people or resources.
Common Business Questions
| Business Question | Changing Input | Result to Study | Suitable Tool |
|---|---|---|---|
| What price produces a target profit? | Selling price | Profit | Goal Seek |
| What happens under low, expected and high demand? | Several assumptions | Profit and cash requirement | Scenario Manager |
| How does monthly payment change across interest rates? | Interest rate | Loan payment | One-variable Data Table |
| How does profit react to price and volume together? | Price and volume | Profit | Two-variable Data Table |
Practical Experiment 1: Build a Controlled Profit Model
Create a small model that can support all four What-If techniques.
Enter selling price, units, variable cost per unit and fixed cost in separate highlighted cells.
Calculate revenue, variable cost, total cost and profit using cell references.
Change every input manually and confirm that the output updates correctly.
2 Designing a Professional Decision Model
A professional workbook clearly separates assumptions from formulas. Hard-coded values hidden inside formulas make What-If analysis difficult to audit and easy to misunderstand.
Write the business question and required output.
List controllable and uncertain inputs.
Build formulas using input-cell references.
Check base-case results and extreme values.
Record assumptions, units and decision limits.
Example Profit Formula
Revenue = Selling Price × Units SoldUses two changing business assumptions.
Profit = Revenue − (Variable Cost × Units Sold) − Fixed CostProduces the result to be tested.
=45*1200-(27*1200)-10000. Put every important assumption in a labelled input cell so it can be changed and reviewed.Find One Input Required for One Target Result
Goal Seek works backwards from a formula result to one changing input cell.
3 Goal Seek Logic and Procedure
Goal Seek is suitable when there is one formula cell, one desired value and one input that Excel is allowed to change. The changing cell must influence the formula cell through the calculation chain.
| Goal Seek Box | Meaning | Example |
|---|---|---|
| Set cell | The formula result to control | Profit cell |
| To value | The required target result | $25,000 |
| By changing cell | The single input Excel may adjust | Selling price |
Steps
- Select Data → What-If Analysis → Goal Seek.
- Choose the formula cell in Set cell.
- Enter the desired number in To value.
- Select one input cell in By changing cell.
- Choose OK, review the solution and accept it only after checking feasibility.
Practical Experiment 2: Target Profit through Selling Price
Use the profit model created earlier.
Set units at 1,500, variable cost at $28 and fixed cost at $12,000.
Set the Profit cell to $25,000 by changing Selling Price.
Compare the required price with competitor pricing and customer expectations.
4 Break-Even and Target-Based Applications
Goal Seek is commonly used for break-even analysis, loan planning, examination targets, production requirements, commission planning and cost control.
Set Profit to 0 by changing Units SoldFinds the break-even sales volume.
Set Closing Balance to 0 by changing Monthly PaymentFinds a payment needed to fully repay a balance under the model assumptions.
Set Gross Margin % to 35% by changing CostFinds the maximum allowable cost for a required margin.
Set Final Score to 80 by changing Final Exam MarksFinds the result needed to reach a target overall score.
Practical Experiment 3: Calculate Break-Even Units
Use Goal Seek to determine the sales volume at which profit becomes zero.
Return the profit model to the approved base assumptions.
Set Profit to 0 by changing Units Sold.
Round up to a whole unit and compare the result with available capacity.
Save and Compare Alternative Sets of Assumptions
Scenarios are useful when several inputs must change together as one meaningful business condition.
5 Building Conservative, Expected and Optimistic Scenarios
Scenario Manager stores named sets of values for selected changing cells. A business can therefore compare low-demand, expected-demand and high-demand conditions without manually retyping every assumption.
Lower volume, reduced price, higher unit cost and additional operating expense.
The most realistic assumptions based on current evidence and approved targets.
Higher volume, better price realization, controlled cost and stronger productivity.
Creating Scenarios
- Open Data → What-If Analysis → Scenario Manager.
- Choose Add and provide a meaningful scenario name.
- Select the changing cells, preferably non-adjacent labelled input cells only.
- Enter the values for the scenario and add a comment identifying the source or owner.
- Repeat for all alternatives, then use Show to apply each set.
Practical Experiment 4: Create Three Operating Scenarios
Use Selling Price, Units Sold, Variable Cost and Fixed Cost as changing cells.
Prepare conservative, expected and optimistic assumptions with a clear rationale.
Add all three scenarios using exactly the same changing cells.
Show each scenario and record revenue, contribution and profit.
6 Scenario Summary and Decision Comparison
Scenario Manager can create a Scenario Summary report showing each scenario's changing values and selected result cells. This converts assumptions into a structured comparison for management review.
| Scenario | Price | Units | Unit Cost | Fixed Cost | Profit |
|---|---|---|---|---|---|
| Conservative | $43 | 1,100 | $30 | $14,000 | Calculated result |
| Expected | $47 | 1,500 | $28 | $12,000 | Calculated result |
| Optimistic | $50 | 1,900 | $27 | $12,500 | Calculated result |
Interpretation Questions
- Does every scenario remain profitable?
- Which assumption contributes most to the difference?
- What capacity, staffing or cash requirement supports the optimistic case?
- Which warning indicator should trigger corrective action?
- Which scenario should become the approved planning baseline?
Practical Experiment 5: Generate a Scenario Summary
Create a formal comparison sheet for management.
Select Revenue, Contribution, Profit and Profit Margin as result cells.
Create the Scenario Summary report and format numbers consistently.
Write a five-line recommendation identifying the preferred case and principal risk.
Calculate Many Outcomes from One Formula
Data Tables show sensitivity by substituting a list or grid of possible input values into a model.
7 One-Variable Data Tables
A one-variable Data Table evaluates one formula for many possible values of one input, or several formulas for the same list of input values. It is ideal for interest-rate, volume, price, discount or cost sensitivity.
Vertical List Procedure
- Enter possible input values vertically in one column.
- Place a direct reference to the result formula one row above and one column to the right of the first input value.
- Select the complete rectangular table range.
- Choose Data → What-If Analysis → Data Table.
- Use the model's original input cell as the Column input cell.
Practical Experiment 6: Profit at Different Sales Volumes
Create a vertical sensitivity table for units from 800 to 2,200.
Enter unit values in increments of 200.
Reference the Profit formula above the result column.
Use Units Sold as the Column input cell and identify the first profitable volume.
8 Two-Variable Data Tables
A two-variable Data Table studies one formula across combinations of two inputs. It produces a grid that is especially useful for price-volume, interest-term, cost-demand and rate-investment analysis.
| Grid Position | Required Content | Example |
|---|---|---|
| Top-left corner | Direct link to the result formula | Profit cell reference |
| Top row | Possible values for the row input | Selling prices |
| First column | Possible values for the column input | Sales volumes |
| Interior cells | Calculated by the Data Table | Profit for every price-volume combination |
Input Cell Mapping
The values placed across the top row are substituted into the Row input cell. Values listed down the first column are substituted into the Column input cell. Reversing these cells produces incorrect or confusing results.
Practical Experiment 7: Price-Volume Profit Matrix
Evaluate profit for five selling prices and eight unit levels.
Place prices across the top and unit quantities down the left.
Use Selling Price as Row input and Units Sold as Column input.
Apply a three-colour scale to identify loss, acceptable and high-profit combinations.
9 Sensitivity Interpretation and Calculation Control
A sensitivity table is valuable only when the learner explains what the pattern means. Look for thresholds, steep changes, safe operating ranges and assumptions that create the greatest risk.
Large Data Tables can slow workbook recalculation because Excel repeatedly recalculates the underlying model. For large analytical workbooks, review calculation settings and avoid unnecessary oversized grids.
Practical Experiment 8: Sensitivity and Risk Review
Analyze the completed price-volume matrix.
Mark all combinations meeting the minimum profit requirement.
Identify combinations where a small demand reduction creates a loss.
Recommend a price and minimum volume with a written safety margin.
Validate the Model Before Trusting the Answer
A sophisticated What-If output cannot repair an incorrect formula, unrealistic assumption or wrong input-cell mapping.
Base-Case Reconciliation
Confirm that the model reproduces known revenue, cost and profit results before running alternatives.
Input Boundaries
Define realistic minimum and maximum values for price, cost, volume, rate and capacity.
Formula Integrity
Trace precedents and confirm that changing cells genuinely influence the selected result.
Scenario Consistency
Use identical changing cells and clearly documented assumptions in every scenario.
Sensitivity Logic
Verify Row input and Column input cells and compare one grid point manually.
Decision Commentary
State the assumption, calculated result, feasibility constraint and recommended action.
Select the Right What-If Tool
Choose the decision requirement and model structure to receive a practical recommendation.
Small Business Pricing and Profit Decision Model
Build a complete workbook that supports target setting, scenario comparison and sensitivity analysis.
Define input cells for price, demand, unit cost, fixed cost and marketing expense.
Calculate revenue, total variable cost, contribution, total cost, profit and margin.
Find the price required for $30,000 profit and the units required for break-even.
Store conservative, expected and optimistic assumptions and generate a summary.
Create volume sensitivity and a price-volume profit matrix.
Prepare a one-page decision note with target, preferred scenario, risk and safety range.
Complete These Skill-Building Activities
Save each output as evidence for your Advanced Excel portfolio.
1. Loan Payment Target
Use Goal Seek to find the loan amount supported by a maximum monthly payment.
2. Break-Even Revenue
Find the revenue required for profit to become zero in a service-business model.
3. Budget Scenarios
Create Cost Control, Approved and Expansion scenarios using revenue and expense assumptions.
4. Scenario Summary
Compare cash surplus, profit margin and funding requirement across all scenarios.
5. Discount Sensitivity
Create a one-variable table showing profit for discount rates from 0% to 20%.
6. Interest Sensitivity
Calculate loan payment across a range of interest rates using a Data Table.
7. Price-Cost Matrix
Create a two-variable contribution-margin table for selling price and unit cost.
8. Capacity Check
Compare Goal Seek's required units with maximum monthly production capacity.
9. Manual Validation
Select one Data Table result and reproduce it by manually entering the same inputs.
10. Decision Note
Write the assumption, tool used, result, risk and recommended action in five lines.
Mistakes Learners Should Avoid
Most What-If errors come from weak model design or incorrect tool configuration.
Wrong Habits
- Using an output cell that does not contain a formula.
- Choosing a changing cell that does not influence the target result.
- Accepting a Goal Seek answer without checking feasibility.
- Using different changing cells across scenarios.
- Reversing Row input and Column input cells in a two-variable Data Table.
- Typing results manually instead of linking the formula cell.
- Using unrealistic assumption ranges.
- Presenting tables without interpretation or recommendation.
Correct Practices
- Separate inputs, formulas and outputs clearly.
- Test the base model before every analysis.
- Document units, sources and assumption owners.
- Use consistent changing cells and scenario names.
- Validate one sensitivity result manually.
- Apply realistic boundaries and capacity limits.
- Highlight thresholds and safe operating ranges.
- Convert calculations into a clear business decision.
Quick Quiz: What-If Analysis
Answer all 12 questions and submit the quiz to review your understanding.
1. What is the main purpose of What-If Analysis?
2. Which tool finds one changing input for one required target result?
3. In Goal Seek, what must the Set cell contain?
4. Which situation is most suitable for Scenario Manager?
5. Why should all scenarios normally use the same changing cells?
6. What does a one-variable Data Table calculate?
7. In a vertical one-variable Data Table, which input box is normally used?
8. What belongs in the top-left corner of a two-variable Data Table?
9. Values across the top row of a two-variable table are mapped to which box?
10. Why should a Goal Seek result be checked for feasibility?
11. What is the best way to validate a Data Table?
12. Which practice produces the strongest What-If decision report?
Remember These What-If Analysis Principles
Review these concepts before moving to Forecasting and Trend Analysis.
Model First
Separate assumptions, calculations, results and controls before testing alternatives.
Goal Seek
Use it for one target result and one changing input.
Scenarios
Use named sets when several assumptions must change together.
One-Variable Table
Use it to calculate many outcomes for different values of one input.
Two-Variable Table
Use it to analyze one result across combinations of two inputs.
Interpret and Validate
Check feasibility, boundaries, one manual result and the recommended action.