ChatGPT or Claude · Practical walkthrough

Build a simple scenario model with editable assumptions

“What if we sell fewer?” is a useful question. A spreadsheet with the answer typed into it is less useful.

Task by Task6 min read

What you'll make

a small spreadsheet with one set of editable assumptions, base, low and high cases side by side, and a break-even volume, all calculated by formulas.

What you'll need

your assumptions with clear units, a ChatGPT or Claude work account that can create spreadsheet files (or a spreadsheet app to enter formulas yourself), and about 20 minutes.

Example result.

	Volume	Surplus after stated costs
Low	30 units	£160
Base	50 units	£400
High	70 units	£640

Break-even: £200 fixed cost ÷ £12 contribution per unit = 16.67, so 17 whole units.

A scenario shows what the stated arithmetic implies, not what will happen.

1. Ask for a model from four labelled inputs

Use one batch, currency and unit. The example is 50 units, £20 price per unit, £8 variable cost per unit and £200 fixed cost for the batch, excluding tax and returns.

Create a simple spreadsheet model for one batch.

Inputs: [your four inputs with units].

Put these inputs in clearly labelled cells. Calculate revenue (volume × price), variable costs (volume × variable cost), contribution (revenue minus variable costs) and surplus (contribution minus fixed cost). Use working formulas, not typed results, and show each step.
If a required input is blank or not a number, show the result as unavailable, not zero. Keep a real zero. State what the model includes and excludes beside the inputs.
If you can create files, give me an XLSX. Otherwise give me a cell map and formulas I can enter.

[your four inputs with units] is, for the practice batch: “volume 50 units; unit price £20; variable cost £8 per unit; fixed cost £200 for the batch”.

Why this works: named input cells make every change traceable, and the intermediate steps make a wrong reference easy to find.

What you should see: a file or cell map with four input cells and four calculated cells.

2. Open the file and check the base case

Open the file itself. Click the revenue, variable-cost and surplus cells and read the formulas. Each should point at an input cell or an earlier step.

Check the base case by hand: 50 × £20 = £1,000 revenue; 50 × £8 = £400 variable costs; £600 contribution; minus £200 gives £400. In the practice workbook, the inputs are B11:B14 on the Scenario sheet, the steps are B18:B21, and B5 shows the result.

Why this works: a typed 400 is the right number and the wrong model. Only the formula bar tells you which you have.

What you should see: £400, with a formula behind it.

3. Add low and high cases from the same inputs

Add low, base and high scenarios as three side-by-side columns: volume [low], [base] and [high] units.
Every column must use the same price, variable cost and fixed cost cells. Don't copy those values into each column.
Add a break-even row: fixed cost ÷ (price − variable cost), and the whole units needed to reach it.
List the cells each scenario column refers to.

[low], [base] and [high] are the volumes you want to compare, such as 30, 50 and 70.

Why this works: shared inputs mean one change to price or cost updates every scenario at once, so the cases stay comparable.

What you should see: £160, £400 and £640 for the practice inputs, and a break-even of 16.67, or 17 whole units.

Check the result

Change the volume to 0. The base case should show −£200: no sales, but the fixed cost remains.
Clear the volume. The result should say unavailable, not −£200. A blank is unknown, not zero.
Change the price from £20 to £19. All three scenario columns and the break-even must change.
Restore the base inputs and confirm £400 returns.

The usual failure: one scenario column has its own typed copy of the price or costs, so it looks right until an input changes and that column doesn’t move. Spot it by changing one shared input and watching every column.

When I change [input], the [scenario] column doesn't update. Point every scenario column at the shared input cells instead of typed values. Don't type expected answers. List the cells you changed so I can check them.

[input] is the cell you changed; [scenario] is the column that stayed put.

Worked example: a price cut and a higher fixed cost

Fictional example. The batch is being re-quoted: price falls to £19 and the fixed cost rises to £260. Variable cost stays at £8, so contribution falls to £11 a unit.

Inputs after change: price £19, variable cost £8, fixed cost £260
Low (30):  model shows £70
Base (50): model shows £290
High (70): model shows £640   ← unchanged
Break-even: model shows 23.6 units

The high column hadn’t moved. Its formula used typed values, =70*(20-8)-200, not the input cells. After the repair prompt, with [input] as price and [scenario] as High:

	Calculation	Surplus
Low	30 × £11 − £260	£70
Base	50 × £11 − £260	£290
High	70 × £11 − £260	£510

Break-even: £260 ÷ £11 = 23.6, so 24 whole units.

£640 looked plausible. It was the old high case, and nothing on the sheet flagged it. Only the changed-input test exposed it.
Contribution explains the whole shift. Each unit now covers £11 instead of £12, against a bigger fixed cost, so break-even moves from 17 to 24 units.
Base and low were right first time, which is why checking only the base case isn’t enough.

Optional example file: scenario practice workbook (XLSX), with the inputs and formulas described above.

Go deeper

Use Excel's own what-if tools for bigger models

Once the model has more than a handful of inputs, desktop Excel’s What-If Analysis tools offer Scenarios, Goal Seek and Data Tables. Goal Seek answers “what price breaks even at 30 units?” directly. A Scenario Summary report is a snapshot: it doesn’t recalculate when the model changes, so date it and rerun it after any edit.

Ask which assumption matters most

A formula can be right while an assumption is weak. Ask which input would change the decision fastest, then check where that figure came from.

For this model, show how the base surplus changes if each input moves by 10% while the others stay the same. Rank the inputs by effect. Don't suggest new costs or change the formulas.

Keep blank and zero apart in larger sheets

The blank-versus-zero rule gets harder to keep as sheets grow. The downloadable spreadsheet guide covers live formulas and empty inputs in more depth, and the formula-testing guide shows how to test edge cases before sharing.