Excel Advanced - What-If Analysis

0 of 9 activities completed

Microsoft Excel Advanced · Lesson 06

What-If Analysis

Explore alternative assumptions, find the input required for a target result and optimise decisions while respecting real business limits.

150-minute lessonMixed Excel versions9 guided activitiesProfit and production models

Learning outcomes

Build Data TablesCompare one or two changing inputs without rewriting formulas.

Find target inputsUse Goal Seek to reverse a formula from a desired result.

Compare scenariosStore named sets of assumptions and generate a summary report.

Optimise with SolverMaximise profit using decision variables, constraints and integer requirements.

Download the practice model

The embedded dataset contains a base profit model, three business scenarios and a three-product optimisation problem.

RM9,000base profit

729.17Goal Seek units

3named scenarios

RM13,620Solver optimum

Recommended workflow: download the Excel-compatible workbook and save a working copy. It contains Model, Scenarios, Solver and Controls sheets. Recreate the formulas if your Excel version does not retain imported SpreadsheetML formulas.

1. Build the profit model

ACTIVITY 1

Verify the base calculation

On the Model sheet, enter 500 units, RM120 selling price, RM72 variable cost per unit and RM15,000 fixed cost.

Revenue = Units × Price

Profit = (Units × Price) − (Units × Variable Cost) − Fixed Cost

Checkpoint: Revenue RM60,000; total variable cost RM36,000; contribution RM24,000; profit RM9,000.

My base model returns RM9,000 profit.

2. One-variable Data Table

ACTIVITY 2

Test alternative prices

1. List RM100, RM110, RM120 and RM130 vertically.
2. In the top-right corner of the result block, enter a direct reference to the Profit cell.
3. Select the complete block, including prices and the formula reference.
4. Choose Data → What-If Analysis → Data Table.
5. Set Column input cell to the Price input cell; leave Row input blank.

Price	Expected profit
RM100	−RM1,000
RM110	RM4,000
RM120	RM9,000
RM130	RM14,000

My four price results match the controls.

ACTIVITY 3

Diagnose Data Table problems

• The corner cell must reference the output formula, not type the current result.
• Because test values run down a column, use the Column input cell.
• Do not edit one result cell inside a Data Table array; select the whole table to delete it.

Performance: large Data Tables recalculate repeatedly. Use Formulas → Calculation Options → Automatic Except for Data Tables when a large workbook becomes slow.

I can identify the formula corner and correct input cell.

3. Two-variable Data Table

ACTIVITY 4

Compare price and quantity together

1. Place prices RM100–RM130 across the top row.
2. Place quantities 400, 500, 600 and 700 down the first column.
3. Put a reference to Profit in the upper-left corner.
4. Select the whole grid and open Data Table.
5. Set Row input cell to Price and Column input cell to Units.

Check selected intersections: 400 units at RM100 = −RM3,800; 500 at RM120 = RM9,000; 700 at RM130 = RM25,600.

My two-variable grid responds to both inputs.

4. Goal Seek

ACTIVITY 5

Find units for RM20,000 profit

1. Choose Data → What-If Analysis → Goal Seek.
2. Set cell: Profit.
3. To value: 20000.
4. By changing cell: Units.
5. Accept the result and record it.

Expected result: approximately 729.17 units. If sales must be whole units, test 730; profit becomes RM20,040.

Why is Goal Seek suitable?

There is one target formula and one unknown input. Goal Seek changes only one input and does not handle multiple constraints.

Goal Seek returns approximately 729.17 units.

5. Scenario Manager

ACTIVITY 6

Create three named scenarios

Use Units, Price, Variable Cost and Fixed Cost as the four changing cells.

Scenario	Units	Price	Variable cost	Fixed cost	Profit
Base	500	120	72	15,000	9,000
Optimistic	650	130	68	14,500	25,800
Conservative	400	105	75	17,000	−5,000

1. Open Scenario Manager and choose Add.
2. Name each scenario and keep the same four changing cells.
3. Enter its values, then use Show to place them into the model.

All three scenarios show the expected profit.

ACTIVITY 7

Create a Scenario Summary

1. Open Scenario Manager and choose Summary.
2. Select Scenario summary.
3. Choose Profit as the result cell.
4. Rename input and output cells before generating the report so the labels are readable.

The new Scenario Summary sheet is static. Recreate it after changing scenario definitions.

My summary compares all assumptions and profits.

6. Solver optimisation

ACTIVITY 8

Prepare the production model

Product	Profit/unit	Labour/unit	Material/unit
A	RM45	2	3
B	RM60	3	2
C	RM38	1	4

Available resources: 600 labour hours and 700 material units.

Total profit = SUMPRODUCT(Quantity, Profit per unit)

Resources used = SUMPRODUCT(Quantity, Resource per unit)

My objective and resource formulas respond to quantities.

ACTIVITY 9

Configure and solve

1. Enable Solver through File → Options → Add-ins if it is missing.
2. Set the Total Profit cell as the objective and choose Max.
3. Use the three Quantity cells as changing variables.
4. Add Labour Used ≤ 600 and Material Used ≤ 700.
5. Require quantities to be non-negative integers.
6. Select Simplex LP, solve and keep the solution.

Expected optimum: A=0, B=170, C=90; maximum profit RM13,620. Both resource limits are fully used.

Model choice: Simplex LP is appropriate because the objective and constraints are linear. Integer constraints prevent fractional products.

Solver returns 0, 170, 90 and RM13,620.

Choose the right What-If tool

Question	Tool
How does one or two inputs affect one formula?	Data Table
Which single input produces a target result?	Goal Seek
How do named sets of assumptions compare?	Scenario Manager
Which combination gives the best result under constraints?	Solver

Troubleshooting

Data Table repeats one value
Check the formula corner and row/column input-cell assignment.

Goal Seek cannot find a solution
Confirm the target cell contains a formula dependent on the changing cell.

Scenario labels are cell addresses
Name the changing and result cells before generating the summary.

Solver is missing
Enable Solver Add-in through Excel Options; it is not always active by default.

Knowledge check

1. Can Goal Seek change several inputs?

No. It changes one input to reach one target. Use Solver for several variables or constraints.

2. Why does a two-variable Data Table use both row and column input cells?

Values across the top replace one model input, while values down the first column replace the other.

3. Are Scenario Summary reports dynamic?

No. Generate a new summary after changing scenarios.

4. Why add integer constraints in Solver?

Products or people may need whole-number decisions even when the mathematical model permits fractions.

Completion checklist

• Base profit: RM9,000
• One-variable profits: −RM1,000 / RM4,000 / RM9,000 / RM14,000
• Goal Seek: approximately 729.17 units
• Scenario profits: RM9,000 / RM25,800 / −RM5,000
• Solver quantities: A=0, B=170, C=90
• Solver maximum profit: RM13,620

Next lesson: combine arrays, lookups, logical functions, links, consolidation and workbook navigation.

Lesson 06 · What-If Analysis · Excel Advanced