Excel Advanced - What-If Analysis 1

Microsoft Excel Advanced · Lesson 06

What-If Analysis

Follow the exact worksheet layouts and dialog settings below to use Goal Seek, Data Tables, Scenario Manager and Solver without invalid-reference errors.

Step-by-step lessonExact cell referencesVerified RM examplesMixed Excel versions

Profit modelGoal SeekOne-variable tableTwo-variable tableScenario ManagerSolver

Learning outcome

Build one profit model, find the input required for a target profit, compare alternative inputs and identify the best production plan within resource limits.

RM9,000base profit

729.17Goal Seek units

25two-variable results

RM13,620Solver optimum

Important: complete the steps in desktop Excel. The Data Table command may not be available in Excel for the web.

1. Build the profit model

Task: Calculate profit using Units, Price, Variable Cost and Fixed Cost.

STEP 1

1. Create a worksheet named Model.
2. Enter the labels, values and formula exactly as shown below.
3. Yellow cells are editable inputs. The green cell is the formula result.

Cell	Enter	Purpose
A4	Units	Input label
B4	500	Editable units
A5	Price per unit	Input label
B5	120	Editable price
A6	Variable cost per unit	Input label
B6	72	Editable variable cost
A7	Fixed cost	Input label
B7	15000	Editable fixed cost
A8	Profit	Output label
B8	=B4*(B5-B6)-B7	Profit formula

Check your work: B8 must show RM9,000. Do not continue until this result is correct.

2. Goal Seek

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

STEP 2

1. Stay on the worksheet containing the profit model.
2. Choose Data → What-If Analysis → Goal Seek.
3. Enter the three settings below.
4. Choose OK. When Excel finds a solution, choose OK again to keep it.

Goal Seek field	Enter	Reason
Set cell	$B$8	B8 contains the Profit formula.
To value	20000	This is the required profit.
By changing cell	$B$4	B4 contains editable Units.

Expected result: B4 becomes approximately 729.17 units and B8 becomes RM20,000. If units must be whole, enter 730; profit becomes RM20,040.

If Goal Seek reports an error: confirm that B8 contains a formula and B4 contains an ordinary number. The Set cell cannot be an input cell.

3. One-variable Data Table

Question: What profit will result from prices of RM100, RM110, RM120 and RM130?

Before starting: copy the profit model to a worksheet named One-Variable Table. Keep its inputs in B4:B7 and Profit in B8. A Data Table’s input cell must be on the same worksheet as the table.

Set up the practice area

Cell or range	Enter
A11	Leave blank
B11	=$B$8 — link to Profit
A12:A15	100, 110, 120, 130
B12:B15	Leave blank for Excel

STEP 3

1. Select the entire range A11:B15.
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.

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

Memory rule: values running downward use the Column input cell.

4. Two-variable Data Table

Question: What profit will result from different combinations of Units and Price?

Before starting: copy the profit model to a worksheet named Two-Variable Table. Keep Units in B4, Price in B5 and Profit in B8.

Set up the practice grid

Location	Enter
A11	=$B$8 — link to Profit
B11:F11	400, 500, 600, 700, 800 — Units across the top
A12:A16	100, 110, 120, 130, 140 — Prices down the first column
B12:F16	Leave blank for Excel

STEP 4

1. Select the entire range A11:F16.
2. Choose Data → What-If Analysis → Data Table.
3. Set Row input cell to $B$4 because Units run across the top row.
4. Set Column input cell to $B$5 because Prices run down the first column.
5. Choose OK.

Price \ Units	400	500	600	700	800
RM100	−RM3,800	−RM1,000	RM1,800	RM4,600	RM7,400
RM110	RM200	RM4,000	RM7,800	RM11,600	RM15,400
RM120	RM4,200	RM9,000	RM13,800	RM18,600	RM23,400
RM130	RM8,200	RM14,000	RM19,800	RM25,600	RM31,400
RM140	RM12,200	RM19,000	RM25,800	RM32,600	RM39,400

Memory rule: top-row values use Row input cell; first-column values use Column input cell.

Invalid input-cell reference? Do not select B4 or B5 from another worksheet. The model and Data Table must be together on this worksheet.

5. Scenario Manager

Question: How do Base, Optimistic and Conservative assumptions affect profit?

Use a worksheet named Scenario Manager containing the same model: changing cells B4:B7 and Profit in B8.

Scenario	Units	Price	Variable cost	Fixed cost	Expected profit
Base	500	120	72	15,000	RM9,000
Optimistic	650	130	68	14,500	RM25,800
Conservative	400	105	75	17,000	−RM5,000

STEP 5

1. Choose Data → What-If Analysis → Scenario Manager.
2. Choose Add and enter the scenario name.
3. Set Changing cells to $B$4:$B$7.
4. Choose OK, then enter values in this order: Units, Price, Variable Cost, Fixed Cost.
5. Repeat for all three scenarios.
6. Select a scenario and choose Show to check Profit in B8.

Create the Scenario Summary

1. Open Scenario Manager and choose Summary.
2. Select Scenario summary.
3. Set Result cells to $B$8.
4. Choose OK. Excel creates a new summary worksheet.

6. Solver optimisation

Question: How many units of Products A, B and C maximise profit without exceeding labour and material limits?

Build the Solver model

Product	Quantity	Profit/unit	Labour/unit	Material/unit
A	0	45	2	3
B	0	60	3	2
C	0	38	1	4

Use =SUMPRODUCT(Quantity,Profit_per_Unit) for Total Profit. Use similar SUMPRODUCT formulas for Labour Used and Material Used. Resource limits are 600 labour hours and 700 material units.

STEP 6

1. If Solver is missing, choose File → Options → Add-ins → Excel Add-ins → Go, select Solver Add-in and choose OK.
2. Choose Data → Solver.
3. Set the Total Profit formula cell as Set Objective and select Max.
4. Set the three Quantity input cells as By Changing Variable Cells.
5. Add Labour Used ≤ 600 and Material Used ≤ 700.
6. Add Quantity cells ≥ 0 and Quantity cells = integer.
7. Select Simplex LP, choose Solve, then choose Keep Solver Solution.

Expected optimum: A = 0, B = 170, C = 90. Maximum profit = RM13,620. Labour used = 600 and material used = 700.

7. Choose the right tool

Business question	Use
Which single 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
How do named sets of assumptions compare?	Scenario Manager
Which combination gives the best result within limits?	Solver

Essential checks

• Goal Seek’s Set cell contains a formula.
• Goal Seek’s changing cell contains an editable number.
• The Data Table and its model input cells are on the same worksheet.
• The Data Table corner contains a formula link, not typed text.
• Vertical test values use Column input cell.
• Top-row values use Row input cell.
• Solver’s changing cells are inputs, not formulas.

Completion checklist

• Base profit: RM9,000
• Goal Seek: 729.17 units
• One-variable results: −RM1,000 / RM4,000 / RM9,000 / RM14,000
• Two-variable table: 25 calculated results
• Scenario profits: RM9,000 / RM25,800 / −RM5,000
• Solver: A=0, B=170, C=90; RM13,620

Lesson 06 · What-If Analysis · Excel Advanced