Worked example · Business · Year 10
DCF: Data and computational thinking → Problem-solving and modelling
A secure Year 10 response, annotated so you can see which success criterion each part meets. It is also the answer key. Key figures: fixed costs £2,160 a month; variable cost £6.00 a hire; at £30, contribution £24, break-even 90 hires, forecast profit £720; at £25, contribution £19, break-even 114 hires, forecast profit £880. What-ifs: electricity at 40p gives break-even 92 and profit £660; rent up £240 gives break-even 100 and profit £480. This practises GCSE Business Unit 1, section 1.7 Revenue, costs and break-even.
| Fixed costs (per month) | £ | Variable costs (per hire) | £ |
|---|---|---|---|
| Bike lease (12 × £90) | 1,080 | Booking fee | 1.00 |
| Rent for Unit 3 | 540 | Charging (2.5 kWh × £0.20) | 0.50 |
| Insurance | 240 | Clean and safety check | 2.50 |
| Booking system subscription | 60 | Snack pack | 2.00 |
| Broadband and phone | 40 | ||
| Social media advertising | 200 | ||
| Total fixed costs | 2,160 | Total variable cost per hire | 6.00 |
Explain: The advertising is a fixed cost because Ffion spends the same £200 every month whether she sells 10 hires or 200. A cost is fixed or variable because of how it behaves when sales change, not because of what it is for.
All ten costs are in the right column, including the booking system, which is split: the £60 subscription is fixed and the £1.00 fee is variable. Evidences criterion 1.
| Row | A: label | B: what you type | C: value shown |
|---|---|---|---|
| 2 | Price per hire (£) | 30 | 30.00 |
| 3 | Forecast hires per month | 120 | 120 |
| 4 | Booking fee per hire (£) | 1 | 1.00 |
| 5 | kWh of charging per hire | 2.5 | 2.5 |
| 6 | Electricity price per kWh (£) | 0.20 | 0.20 |
| 7 | Charging cost per hire (£) | =B5*B6 | 0.50 |
| 8 | Clean and safety check per hire (£) | 2.5 | 2.50 |
| 9 | Snack pack per hire (£) | 2 | 2.00 |
| 10 | Variable cost per hire (£) | =B4+B7+B8+B9 or =SUM(B7:B9)+B4 | 6.00 |
| 11 | Bike lease per month (£) | =12*90 | 1,080 |
| 12 | Rent per month (£) | 540 | 540 |
| 13 | Insurance per month (£) | 240 | 240 |
| 14 | Booking system subscription per month (£) | 60 | 60 |
| 15 | Broadband and phone per month (£) | 40 | 40 |
| 16 | Advertising per month (£) | 200 | 200 |
| 17 | Total fixed costs (£) | =SUM(B11:B16) | 2,160 |
| 18 | Contribution per hire (£) | =B2-B10 | 24.00 |
| 19 | Break-even hires per month | =B17/B18 | 90 |
| 20 | Total revenue at the forecast (£) | =B2*B3 | 3,600 |
| 21 | Total costs at the forecast (£) | =B17+B10*B3 | 2,880 |
| 22 | Profit or loss at the forecast (£) | =B20-B21 | 720 |
(a) If row 7 says =B5*B6, Ffion only has to type the new electricity price into B6 when it changes, and the charging cost, variable cost, contribution, break-even and profit all update by themselves. If she typed 0.50, she would have to work it out again by hand and could forget to change it.
(b) The outputs are rows 19 to 22: break-even hires, total revenue, total costs and profit. Rows 2 to 6, 8, 9 and 11 to 16 are inputs. Rows 7, 10, 17 and 18 are calculations in the middle that the outputs depend on.
Every formula uses cell references, so the model still works when an input changes. Row 21 is total costs = fixed costs + (variable cost per hire × hires): 2,160 + 6 × 120 = 2,880. Check: 3,600 − 2,880 = 720. Evidences criteria 2, 3 and 4.
| Option A (£30) | Option B (£25) | |
|---|---|---|
| Input cells changed | none | B2 to 25 and B3 to 160 |
| Contribution per hire (row 18) | £24.00 | £19.00 |
| Break-even hires (row 19) | 90 | 2,160 ÷ 19 = 113.7, so 114 |
| Total revenue (row 20) | £3,600 | 25 × 160 = £4,000 |
| Total costs (row 21) | £2,880 | 2,160 + 6 × 160 = £3,120 |
| Profit or loss (row 22) | £720 profit | £880 profit |
| Forecast hires above break-even | 120 − 90 = 30 | 160 − 114 = 46 |
Break-even is rounded up to 114, because at 113 hires Ffion would still make a small loss: 113 × 19 = £2,147, which is £13 short of £2,160.
Only two inputs change and the rest of the model is reused. The rounding is explained, which shows the pupil understands what break-even means, not just the formula. Evidences criteria 4 and 5.
| What if… | Cell and new input | New break-even | New profit or loss |
|---|---|---|---|
| (a) electricity rises to 40p per kWh | B6 to 0.40 | Charging £1.00, variable cost £6.50, contribution £23.50. 2,160 ÷ 23.50 = 91.9, so 92 | 3,600 − (2,160 + 6.50 × 120) = £660 |
| (b) rent rises by £240 per month | B12 to 780 | Fixed costs £2,400. 2,400 ÷ 24 = 100 | 3,600 − (2,400 + 720) = £480 |
(c) The rent rise hurts more. It adds £240 to fixed costs every month however many hires Ffion sells, so profit falls by £240 and she needs 10 more hires to break even. The electricity rise only cuts contribution by 50p a hire, which is £60 at 120 hires.
Each what-if names the single input cell that changes, which is the point of a model. The explanation in (c) links the result back to contribution and fixed costs. Evidences criteria 3 and 5.
I recommend Option B, charging £25. Each hire earns less contribution, £19 instead of £24, so Ffion needs 114 hires to break even instead of 90. But the forecast is 160 hires, so she would make £880 profit a month compared with £720 at £30. She would also be 46 hires above break-even instead of 30, so a wet month with fewer riders is less likely to push her into a loss. However, the forecast comes from asking only 80 visitors, and people often say they will buy something and then do not. If only 110 people hire at £25, she would make a loss, while 110 hires at £30 would still make a profit. Ffion should try £25 for one month and put the real number of hires into her model.
132 words. A clear recommendation, three figures from the model, a balancing point, and a named weakness of the model (the forecast) with a figure that shows why it matters: 110 × 19 = £2,090, below £2,160; 110 × 24 = £2,640, above it. Evidences criterion 5.
Break-even at 80 hires needs a contribution of 2,160 ÷ 80 = £27, so a price of £27 + £6 = £33.
Maximum hires: =12*3*20 gives 720 a month, so neither forecast (120 or 160) is near the limit. Better still, put 12, 3 and 20 in their own input cells and multiply the cells.