Worked example · IT · Year 11
DCF: Data and computational thinking → Problem-solving and modelling
A secure Year 11 response, annotated so you can see which success criterion each part meets. It is also your answer key. Key figures: contribution £1.80 a cup, break-even 400 cups; what-ifs 360, 437, 450 and 390 cups; yearly profit £1,656, or £1,056 once the helper is paid.
Computer route: if a pupil builds B4 as =SUM(...) of the five pence amounts instead of typing 1.20, a spreadsheet can store the total as 1.2000000000000002 and ROUNDUP may then show 401. That is a real floating-point effect, not a pupil error; typing 1.20, or using =ROUNDUP(ROUND(B2/(B3-B4),2),0), fixes it.
(a) £3.00 − £1.20 = £1.80. Every cup puts £1.80 towards the £720 of fixed costs, and once those are paid, £1.80 of every cup is profit.
(b) £720 ÷ £1.80 = 400 cups. B6: =ROUNDUP(B2/(B3-B4),0). The brackets matter: without them the spreadsheet would work out B2/B3 first and then subtract B4.
(c) At 436.36 cups he has not quite broken even, and he cannot sell part of a cup. ROUND would give 436, and at 436 cups he is still 60p short (436 × 1.65 = £719.40). Only ROUNDUP gives 437, the first whole number of cups that covers his costs.
The formula uses references, so it updates when an input changes; the brackets are explained; and the ROUNDUP choice is proved with a calculation, not just asserted. Evidences criterion 1.
| Cell | Formula | Why |
|---|---|---|
| B9 — Revenue | =A9*$B$3 | A9 must move down the rows; the price must not. |
| C9 — Total costs | =$B$2+A9*$B$4 | Fixed costs and variable cost are both inputs that stay put. |
| D9 — Profit | =B9-C9 | Both references should move with the row, so no $ signs. |
| E9 — Result | =IF(D9>0,"Profit",IF(D9=0,"Break even","Loss")) | Three outcomes need two tests. |
| Row | A: Cups sold | B: Revenue (£) | C: Total costs (£) | D: Profit (£) | E: Result |
|---|---|---|---|---|---|
| 9 | 0 | 0.00 | 720.00 | −720.00 | Loss |
| 10 | 100 | 300.00 | 840.00 | −540.00 | Loss |
| 11 | 200 | 600.00 | 960.00 | −360.00 | Loss |
| 12 | 300 | 900.00 | 1,080.00 | −180.00 | Loss |
| 13 | 400 | 1,200.00 | 1,200.00 | 0.00 | Break even |
| 14 | 500 | 1,500.00 | 1,320.00 | 180.00 | Profit |
| 15 | 600 | 1,800.00 | 1,440.00 | 360.00 | Profit |
| 16 | 700 | 2,100.00 | 1,560.00 | 540.00 | Profit |
Every $ sign is justified, not sprinkled everywhere, and the table checks itself: profit rises by exactly £180 (100 cups × £1.80) each row, and row 13 agrees with the break-even formula. Evidences criteria 1 and 2.
Both lines are drawn from the table, the fixed-cost line shows where total costs start, the crossing point is circled and labelled with both values, and the lines differ by dash pattern as well as colour. Evidences criterion 3.
(a) Every reference in =B2+A9*B4 was relative, so dragging it down one row moved all of them down one: C10 became =B3+A10*B5. That is the price plus 100 cups times the forecast: 3.00 + 100 × 520 = 52,003. By row 12 it has become =B5+A12*B7, and B7 is empty, so it counts as zero: 520 + 300 × 0 = 520, which is why 300 cups falsely shows a £380 profit. The fix is =$B$2+A9*$B$4.
(b) Row 9 is the one row where a wrong formula can still look right, because A9 is 0, so the variable-cost part is zero whatever cell it points at. He should check at least one row he can work out in his head, such as 100 cups costing 720 + 120 = £840, and check that the row where profit is zero matches B6.
(c) Row 13. At 400 cups D13 is exactly 0, and 0 is not greater than 0, so =IF(D9>0,"Profit","Loss") calls break-even a loss. The nested IF in Step 2 fixes it.
Traces the exact formula the drag produced and reproduces Rhys's wrong number from it, explains why his test missed it, and catches the boundary case that most people skip. Evidences criterion 4.
| What if… | Cell and new value | Working | New break-even |
|---|---|---|---|
| 1. Price up to £3.20 | B3 = 3.20 | 720 ÷ (3.20 − 1.20) = 720 ÷ 2.00 = 360 | 360 cups |
| 2. Milk up 15p a cup | B4 = 1.35 | 720 ÷ 1.65 = 436.36, rounded up | 437 cups |
| 3. Licence up £90 a month | B2 = 810 | 810 ÷ 1.80 = 450 | 450 cups |
| 4. Changes 1 and 2 together | B3 = 3.20, B4 = 1.35 | 720 ÷ 1.85 = 389.19, rounded up | 390 cups |
The licence rise hurts most: it adds 50 cups a month to break-even, against 37 for the milk. The one thing Owain controls is his price, and scenario 4 shows that a 20p rise more than cancels out the milk increase (390 cups instead of 437). The risk is that fewer people buy at £3.20, and this model cannot tell him that.
Changes one input at a time from the original figures, names the cell, and turns the numbers into advice while admitting what the model cannot predict. Evidences criterion 5.
(a) He makes a loss in the five months below 400 cups: January (300), February (320), March (380), November (340) and December (380). Total cups: 300 + 320 + 380 + 460 + 520 + 600 + 700 + 720 + 560 + 440 + 340 + 380 = 5,720. Profit = 5,720 × £1.80 − 12 × £720 = £10,296 − £8,640 = £1,656 for the year.
(b) C9: =IF(A9>600,$B$2+300,$B$2)+A9*$B$4
(c) June, 600 cups: 1,800 − (720 + 720) = £360. July, 700 cups: 2,100 − (1,020 + 840) = £240. The 601st cup triggers the whole £300, and 100 extra cups only bring in £180 of contribution, so July earns £120 less than June. He needs 300 ÷ 1.80 = 166.7, so 167 cups above 600 — 767 cups — just to get back to June's profit. July and August both pay the helper, so the year's profit falls by £600 to £1,056.
The IF formula is written to be copied down, and the step in costs is explained with numbers that show why more sales can mean less profit. Evidences criterion 5.
It assumes he sells every cup at £3.00, but in reality a price rise could lose customers, so the model could make a price rise look safer than it is. It also assumes the forecast is right, but one wet August on the Aberystwyth seafront could cut sales well below 720, so he should test a bad-weather version before he signs a loan or hires anyone. A third problem is that the cost per cup never changes, when in real life buying milk and cups in bulk in summer would make each one cheaper.
Each assumption is linked to a specific decision it could get wrong, not just listed. Evidences criterion 6.