Intermediate level · Module 3: What-If Analysis and Financial Modelling · Lesson 1 of 3

Goal Seek, Data Tables and Scenarios

Answer 'what if' questions by changing inputs and seeing results.

In this lesson you will

Use Goal Seek to find a required input
Build data tables and named scenarios

Key concepts

Goal Seekone-variable data tabletwo-variable data tableScenario Manager

The lesson

Goal Seek finds the input needed for a target: what selling price gives a profit of 200,000 SSP a month?

Data tables show results for many input values at once — profit at prices from 16,000 to 22,000 SSP, or across prices and sales volumes together.

Scenario Manager stores named sets of inputs, such as pessimistic, expected and optimistic, and produces a summary report comparing them.

Step by step

Use Goal Seek on a profit target.
Build a one-variable data table of profit by price.
Build a two-variable table of price and volume.
Create three scenarios and a summary.

Data and chart

Which uniform price gives the most profit?

A one-variable data table tests four prices for school uniforms using estimated monthly sales.

Tailor's monthly profit by uniform price: cost 12,000 SSP per uniform, fixed costs 300,000 SSP per month, estimated sales at each price (illustrative)
Price (SSP)	Uniforms sold per month	Profit (SSP)
16000	100	100,000
18000	80	180,000
20000	62	196,000
22000	45	150,000

What the data shows

Profit = price × units − 12,000 × units − 300,000. At 20,000 SSP: 62 × 8,000 − 300,000 = 196,000 SSP, the highest of the four prices tested.

The cheapest price sells the most uniforms but earns the least, because each uniform makes only 4,000 SSP above its cost. The dearest price loses too many customers.

Recommendation

Test prices around 19,000–21,000 SSP and treat the sales estimates as the weakest assumption: a sensitivity check with 10% fewer sales at each price shows how robust the choice is.

Practice activity

Analyse the shop's pricing decision with Goal Seek, data tables and scenarios.

Expected result: Correct what-if results with a clear recommendation.

Check your understanding

What does Goal Seek find?

Show answer
The input value needed to reach a target result.
What does a two-variable data table show?

Show answer
Results for combinations of two inputs.

Taught live by an instructor — online or in person. Free course lessons are for self-study and do not lead to a certificate — see registration options.