Excel Advanced - What-If Analysis (Goal Seek, Data Tables, and Solver)

Excel What-If Analysis

A practical guide to Goal Seek, one-variable and two-variable Data Tables, and Solver.

Learning outcome: Build one profit model, then use Excel to find a target, compare possible results, and optimise decisions.

1. Build the profit model

Use one worksheet for the profit model and both Data Tables. Enter labels in column A and values or formulas in column B.

Row	Column A — label	Column B — value or formula
2	A2: Selling price per unit	B2: 50
3	A3: Variable cost per unit	B3: 30
4	A4: Fixed costs	B4: 5000
5	A5: Units sold	B5: 500
6	Leave blank.
7	A7: Revenue	B7: =B2*B5
8	A8: Total variable cost	B8: =B3*B5
9	A9: Profit	B9: =B7-B8-B4

Check: B7 = RM25,000, B8 = RM15,000 and B9 = RM5,000.

2. Goal Seek

Question: How many units must be sold to earn RM20,000 profit?

Goal Seek changes one input until a formula reaches a target.

1. Choose Data → What-If Analysis → Goal Seek.
2. Enter the settings below.
3. Choose OK, then keep the solution.

Goal Seek

Set cell:$B$9

To value:20000

By changing cell:$B$5

Answer: B5 becomes 1,250 units. Profit becomes RM20,000.

Remember: Set cell is the formula result. Changing cell is the input Excel may adjust.

3. One-variable Data Table

Question: What profit will result from several sales quantities?

Keep the Data Table on the same worksheet as B5 and B9.

Set up the table

Cell	Enter
D3	Leave blank
E3	=B9
D4:D8	500, 750, 1000, 1250, 1500
E4:E8	Leave blank for Excel to calculate

Create the table

1. Select D3:E8.
2. Choose Data → What-If Analysis → Data Table.
3. Leave Row input cell blank.
4. Set Column input cell to $B$5.
5. Choose OK.

Units sold	Profit
500	RM5,000
750	RM10,000
1,000	RM15,000
1,250	RM20,000
1,500	RM25,000

Why Column input cell? The test values run downward in a column.

4. Two-variable Data Table

Question: What profit will result from different combinations of selling price and units sold?

Keep this table on the same worksheet as B2 and B5.

Set up the table

Location	Enter
H3	=B9
I3:L3	45, 50, 55, 60 — selling prices
H4:H7	500, 750, 1000, 1250 — units sold
I4:L7	Leave blank for Excel to calculate

Create the table

1. Select H3:L7.
2. Choose Data → What-If Analysis → Data Table.
3. Set Row input cell to $B$2 because prices run across the top row.
4. Set Column input cell to $B$5 because quantities run down the first column.
5. Choose OK.

Units \ Price	RM45	RM50	RM55	RM60
500	RM2,500	RM5,000	RM7,500	RM10,000
750	RM6,250	RM10,000	RM13,750	RM17,500
1,000	RM10,000	RM15,000	RM20,000	RM25,000
1,250	RM13,750	RM20,000	RM26,250	RM32,500

Memory rule: Top-row values use Row input cell. First-column values use Column input cell.

5. Solver

Question: How many units of Product A and Product B should be produced to maximise profit without exceeding labour and material limits?

Create a new worksheet named Solver Model.

Build the model

Cell	Enter
B2:C2	Product A, Product B
A3	Quantity
B3:C3	0, 0 — Solver changes these cells
A4 and B4:C4	Unit profit; 40, 55
A5 and B5:C5	Labour per unit; 2, 4
A6 and B6:C6	Material per unit; 3, 2
E3	=SUMPRODUCT(B3:C3,B4:C4) — total profit
E5	=SUMPRODUCT(B3:C3,B5:C5) — labour used
E6	=SUMPRODUCT(B3:C3,B6:C6) — material used
F5:F6	400, 300 — available resources

Run Solver

If Solver is missing, enable Solver Add-in from File → Options → Add-ins → Excel Add-ins → Go.

1. Choose Data → Solver.
2. Set Objective to $E$3 and select Max.
3. Set Changing Variable Cells to $B$3:$C$3.
4. Add $E$5<=$F$5 for labour.
5. Add $E$6<=$F$6 for material.
6. Add $B$3:$C$3>=0 and $B$3:$C$3 = int.
7. Select Simplex LP, then choose Solve.

Optimal answer: Produce 50 units of Product A and 75 units of Product B. Maximum profit is RM6,125. Labour used = 400 hours; material used = 300 units.

6. Which tool should you use?

Question	Tool
What one input produces a target result?	Goal Seek
What results come from several values of one input?	One-variable Data Table
What results come from combinations of two inputs?	Two-variable Data Table
What combination gives the best result within limits?	Solver

Essential checks

• Goal Seek’s Set cell must contain a formula.
• Data Table input cells must be on the same worksheet as the table.
• The Data Table corner cell must link to the result formula, such as =B9.
• Solver’s changing cells must be editable inputs, not formulas.

...