Cover lesson · Business · Year 10 · Intermediate
DCF: Data and computational thinking → Problem-solving and modelling
Name: Class: Date:
What this practises: GCSE Business (Made for Wales) Unit 1: Introduction to the Business World, section 1.7 Revenue, costs and break-even: fixed and variable costs, total costs, total revenue, profit or loss, and break-even through the contribution method. Pupils build the calculation as a spreadsheet model on paper.
Print: this sheet, one per pupil, and the stimulus sheet “Olwyn E-Bikes: the costs pack”, one between two. Pupils need a calculator. No computers are needed. If you are in a computer room, the costs are also in olwyn-ebike-costs.csv and pupils can type their Step 3 model into a spreadsheet after writing it on paper.
Run: 5 min read the costs pack in pairs · 8 min Step 2, sort the costs · 14 min Step 3, build the model · 8 min Step 4, test Option B · 8 min Step 5, what if? · 12 min Step 6, write the recommendation alone. Total 55 minutes.
Collect: this sheet. The answer key is the worked example; the break-even answers are 90 hires at £30 and 114 hires at £25.
Ffion runs a new e-bike hire business. Her bank wants to know her break-even point, and she has to choose between two prices. You are going to sort her costs, then build a model: a spreadsheet, on paper, where every answer is worked out by a formula from the costs. Once the model is built, you only change one or two cells to test a new price or a new cost. Then you will use the model to advise Ffion.
Read sections 1 to 5 of the costs pack with your partner. Underline every amount of money. Circle the words “per month” and “per hire” wherever you see them.
There are 10 costs in the pack (document 10 is the price, not a cost). Put each cost in the right column. For the charging cost, work out the cost per hire first.
| Fixed costs (per month) | £ | Variable costs (per hire) | £ |
|---|---|---|---|
| Total fixed costs | Total variable cost per hire |
Explain: the advertising budget is spent to win customers. Why is it still a fixed cost?
This is Ffion’s spreadsheet for Option A (£30). Column B is what you would type into the cell: either a number (an input) or a formula that starts with = and uses cell references such as B5. Column C is the value the cell would show.
| 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 (£) | ||
| 5 | kWh of charging per hire | 2.5 | 2.5 |
| 6 | Electricity price per kWh (£) | 0.20 | 0.20 |
| 7 | Charging cost per hire (£) formula | ||
| 8 | Clean and safety check per hire (£) | ||
| 9 | Snack pack per hire (£) | ||
| 10 | Variable cost per hire (£) formula | ||
| 11 | Bike lease per month (£) formula (12 bikes at £90) | ||
| 12 | Rent per month (£) | ||
| 13 | Insurance per month (£) | ||
| 14 | Booking system subscription per month (£) | ||
| 15 | Broadband and phone per month (£) | ||
| 16 | Advertising per month (£) | ||
| 17 | Total fixed costs (£) formula | ||
| 18 | Contribution per hire (£) formula | ||
| 19 | Break-even hires per month formula | ||
| 20 | Total revenue at the forecast (£) formula | ||
| 21 | Total costs at the forecast (£) formula | ||
| 22 | Profit or loss at the forecast (£) formula |
(a) Row 7 could just say 0.50. Why is a formula better for Ffion?
(b) Which rows are the outputs: the answers Ffion and the bank actually want to read?
Option B is a price of £25 with a forecast of 160 hires. Which two input cells change? Change them, then write the new values the model would show. Break-even must be a whole number of hires: think about whether to round up or down.
| Option A (£30) | Option B (£25) | |
|---|---|---|
| Input cells changed | none | |
| Contribution per hire (row 18) | ||
| Break-even hires (row 19) | ||
| Total revenue (row 20) | ||
| Total costs (row 21) | ||
| Profit or loss (row 22) | ||
| Forecast hires above break-even |
Use the model at Option A (£30, 120 hires). For each change, say which input cell you would change and what the new break-even and profit would be.
| What if… | Cell and new input | New break-even | New profit or loss |
|---|---|---|---|
| (a) electricity rises to 40p per kWh | |||
| (b) rent rises by £240 per month (electricity back at 20p) |
(c) Which change hurts Ffion more? Explain using contribution or fixed costs.
Write a short recommendation (100 to 150 words), on your own: Should Ffion charge £30 or £25? Use at least two figures from your model. Include one way the model could be wrong, for example where the forecast came from.