Worked example · History · Year 8
DCF: Data and computational thinking → Data and information literacy
A secure Year 8 response, annotated to show which success criterion each part meets. It is also your answer key: every number has been checked against the ledger. The key facts: the 2 errors are John Williams’s 270 tonnes (should be 27, because the weighbridge total is 236) and Robert Hughes’s missing powder (should be 430 pence, because the storeroom total is 2,930). With the corrected data, total net pay is 46,168 pence, the mean net pay per crew is 5,771 pence, Hugh Pritchard’s crew earned the most per man (1,638 pence) and Thomas Parry’s the least (1,015 pence). Step 5 is the numeracy (calculation) evidence: give credit for correct working even where one slip carries through.
| Column | Heading in row 1 (with unit) | Data or calculated? | Data type | Value in row 2 (Griffith Jones) |
|---|---|---|---|---|
| A | Crew leader | Data | Text | Griffith Jones |
| B | Men in crew | Data | Whole number | 4 |
| C | Finished slate (tonnes) | Data | Number (accept whole number) | 30 |
| D | Agreed rate (pence per tonne) | Data | Whole number | 200 |
| E | Gross pay (pence) | Calculated | Number | 6000 |
| F | Powder (pence) | Data | Whole number | 360 |
| G | Candles (pence) | Data | Whole number | 84 |
| H | Tools sharpened (pence) | Data | Whole number | 56 |
| I | Total deductions (pence) | Calculated | Number | 500 |
| J | Net pay (pence) | Calculated | Number | 5500 |
| K | Net pay per man (pence) | Calculated | Number (it could have a decimal part) | 1375 |
| L | Remarks | Data | Text | Fair rock |
(b) If the unit is in the heading, every cell holds only a number, so the spreadsheet can calculate with it. “30 tonnes” is text, not a number: =C2*D2 would give an error (such as #VALUE!), and =SUM(C2:C9) would skip that cell and give a total that is 30 tonnes too small, with no warning.
Every heading names the quantity and its unit, columns that come from the ledger are marked as data and the 4 worked-out columns (E, I, J, K) as calculated, and the pupil spots that net pay per man may not be a whole number. Part (b) explains the real problem (text cannot be calculated with) rather than only saying “it looks neater”. Evidences criterion 1.
| Error | Crew and column | What is wrong, and how you know | Correct value and working |
|---|---|---|---|
| 1 | John Williams, column C (finished slate) | 270 tonnes is impossible: 4 men would each have made 67.5 tonnes, about 10 times as much as any other crew (the others made 4.5 to 9.2 tonnes per man). The ledger adds up to 479 tonnes but the weighbridge book says 236. The clerk probably wrote an extra 0. | Other 7 crews: 30 + 21 + 36 + 46 + 18 + 24 + 34 = 209 tonnes. 236 − 209 = 27 tonnes. Check: 27 ÷ 4 = 6.75 tonnes per man, in line with the other crews. |
| 2 | Robert Hughes, column F (powder) | The powder box is empty. Every crew used powder for blasting, so it cannot really be 0. The storeroom book gives the total for all 8 crews. | Other 7 crews: 360 + 270 + 320 + 380 + 470 + 290 + 410 = 2,500 pence. 2,930 − 2,500 = 430 pence. Check: a 5-man crew on fair rock, between 360 and 470, so it is sensible. |
(b) Validation rules:
| Column | Validation rule | Which error it would stop |
|---|---|---|
| C | Must be a number between 1 and 100 (no crew on this page made more than 46 tonnes). A check cell that compares =SUM(C2:C9) with the weighbridge total of 236 would also catch it. | Error 1: 270 tonnes would be refused. |
| F | Must not be left blank; must be a whole number from 0 to 1,000. A check cell that compares =SUM(F2:F9) with the storeroom total of 2,930 would also catch it. | Error 2: the empty powder cell would be refused. |
(c) =SUM(F4:H4) would give 105 + 70 = 175, because SUM treats a blank cell as 0. Robert Hughes’s net pay would show as 7,560 − 175 = 7,385 pence instead of 6,955: 430 pence too much. An error message makes you stop and fix the problem. A wrong answer that looks normal gets trusted, and it spreads into the totals, the means and the sorted list without anyone noticing.
Both errors are found and explained using 2 kinds of evidence: common sense (tonnes per man, every crew uses powder) and the independent totals from the other books. Each correct value is worked out and then checked for reasonableness. The validation rules are specific (a range, “not blank”, a check against a known total) and are matched to the error they would stop. Evidences criterion 2.
| Cell | What it works out | Formula |
|---|---|---|
| E2 | Gross pay for Griffith Jones’s crew | =C2*D2 |
| I2 | Total deductions for Griffith Jones’s crew | =F2+G2+H2 or =SUM(F2:H2) |
| J2 | Net pay for Griffith Jones’s crew | =E2-I2 |
| K2 | Net pay per man for Griffith Jones’s crew | =J2/B2 |
| E7 | Gross pay for Thomas Parry’s crew (after E2 is filled down) | =C7*D7 |
| C10 | Total finished slate for all 8 crews | =SUM(C2:C9) |
| J10 | Total net pay for all 8 crews | =SUM(J2:J9) |
| C11 | Mean finished slate per crew | =AVERAGE(C2:C9) |
| J11 | Mean net pay per crew | =AVERAGE(J2:J9) |
(b) =C2*D2 updates by itself if the tonnes or the rate are changed, for example when a mistake is corrected. A typed 6000 would stay the same and become wrong without anyone noticing. The formula can also be filled down to every crew, and it shows how the answer was worked out.
Every formula starts with =, uses cell references rather than numbers, uses * and / correctly, and uses ranges (F2:H2, C2:C9) with SUM and AVERAGE. E7 shows the pupil understands that filling down changes the row number. Evidences criterion 3.
| Crew | E: Gross pay C × D | I: Total deductions F + G + H | J: Net pay E − I | K: Net pay per man J ÷ B |
|---|---|---|---|---|
| Griffith Jones | 30 × 200 = 6,000 | 360 + 84 + 56 = 500 | 6,000 − 500 = 5,500 | 5,500 ÷ 4 = 1,375 |
| Robert Hughes | 36 × 210 = 7,560 | 430 + 105 + 70 = 605 | 7,560 − 605 = 6,955 | 6,955 ÷ 5 = 1,391 |
| John Williams | 27 × 220 = 5,940 | 320 + 80 + 60 = 460 | 5,940 − 460 = 5,480 | 5,480 ÷ 4 = 1,370 |
| Hugh Pritchard | 46 × 190 = 8,740 | 380 + 100 + 70 = 550 | 8,740 − 550 = 8,190 | 8,190 ÷ 5 = 1,638 |
| Thomas Parry | 18 × 260 = 4,680 | 470 + 88 + 62 = 620 | 4,680 − 620 = 4,060 | 4,060 ÷ 4 = 1,015 |
| William Owen | 4,830 | 381 | 4,449 | 1,483 |
| Owen Thomas | 5,040 | 396 | 4,644 | 1,548 |
| David Ellis | 7,480 | 590 | 6,890 | 1,378 |
(b) Totals and mean:
| Cell and what it shows | Working and answer |
|---|---|
| C10: total finished slate (tonnes) | 30 + 21 + 36 + 27 + 46 + 18 + 24 + 34 = 236 tonnes. This matches the weighbridge book, so the correction in Step 3 was right. |
| E10: total gross pay (pence) | 6,000 + 4,830 + 7,560 + 5,940 + 8,740 + 4,680 + 5,040 + 7,480 = 50,270 pence |
| I10: total deductions (pence) | 500 + 381 + 605 + 460 + 550 + 620 + 396 + 590 = 4,102 pence |
| J10: total net pay (pence) | 5,500 + 4,449 + 6,955 + 5,480 + 8,190 + 4,060 + 4,644 + 6,890 = 46,168 pence. Check: 50,270 − 4,102 = 46,168. The same. |
| J11: mean net pay per crew (pence) | 46,168 ÷ 8 = 5,771 pence |
(c) Check for Hugh Pritchard: 1,638 × 5 = 8,190, which is his net pay, so the division was right. (Any similar inverse check is fine, for example 1,015 × 4 = 4,060 for Thomas Parry.)
Each box shows the calculation as well as the answer, so the method can be checked. Multiplication, addition, subtraction and division are all accurate, and the pupil uses the corrected values (27 tonnes, 430 pence). Totals are checked 2 ways: against the weighbridge book and by E10 − I10, and a division is checked by multiplying back. Evidences criterion 4 (the numeracy evidence: calculation).
Formula view (row 2 shown; rows 3 to 9 are the same formulas filled down, so row 5 has =C5*D5 and so on):
| Cell | E | I | J | K |
|---|---|---|---|---|
| Row 2 | =C2*D2 | =SUM(F2:H2) | =E2-I2 | =J2/B2 |
| Row 10 | =SUM(E2:E9) | =SUM(I2:I9) | =SUM(J2:J9) | left blank |
| Row 11 | =AVERAGE(E2:E9) | =AVERAGE(I2:I9) | =AVERAGE(J2:J9) | =AVERAGE(K2:K9) |
Columns B, C and F to H use =SUM(B2:B9), =AVERAGE(B2:B9) and so on in rows 10 and 11. D10 and K10 are left blank: adding up rates, or adding up “pay per man” for crews of different sizes, gives a number that means nothing.
Value view (corrected data; all money in pence):
| A | B | C | D | E | F | G | H | I | J | K | L | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Crew leader | Men in crew | Finished slate (tonnes) | Agreed rate (pence per tonne) | Gross pay (pence) | Powder (pence) | Candles (pence) | Tools sharpened (pence) | Total deductions (pence) | Net pay (pence) | Net pay per man (pence) | Remarks |
| 2 | Griffith Jones | 4 | 30 | 200 | 6000 | 360 | 84 | 56 | 500 | 5500 | 1375 | Fair rock |
| 3 | William Owen | 3 | 21 | 230 | 4830 | 270 | 66 | 45 | 381 | 4449 | 1483 | Fair rock, very wet |
| 4 | Robert Hughes | 5 | 36 | 210 | 7560 | 430 | 105 | 70 | 605 | 6955 | 1391 | Fair rock |
| 5 | John Williams | 4 | 27 | 220 | 5940 | 320 | 80 | 60 | 460 | 5480 | 1370 | Fair rock |
| 6 | Hugh Pritchard | 5 | 46 | 190 | 8740 | 380 | 100 | 70 | 550 | 8190 | 1638 | Good rock, splits clean |
| 7 | Thomas Parry | 4 | 18 | 260 | 4680 | 470 | 88 | 62 | 620 | 4060 | 1015 | Poor rock, many faults. Crew worked every day of the month. |
| 8 | Owen Thomas | 3 | 24 | 210 | 5040 | 290 | 60 | 46 | 396 | 4644 | 1548 | Good rock |
| 9 | David Ellis | 5 | 34 | 220 | 7480 | 410 | 100 | 80 | 590 | 6890 | 1378 | Fair rock |
| 10 | Total | 33 | 236 | 50270 | 2930 | 683 | 489 | 4102 | 46168 | |||
| 11 | Mean | 4.125 | 29.5 | 217.5 | 6283.75 | 366.25 | 85.375 | 61.125 | 512.75 | 5771 | 1399.75 |
This is the whole design the worksheet builds towards. F10 (2,930) now matches the storeroom book and C10 (236) matches the weighbridge book, so both corrections are confirmed by the spreadsheet itself. Pupils do not need to produce this full grid; use it to check any value. Supports criteria 1 to 4.
(a) Sorted by column K, largest to smallest:
| Position | Crew | Net pay per man (pence) |
|---|---|---|
| 1st | Hugh Pritchard | 1,638 |
| 2nd | Owen Thomas | 1,548 |
| 3rd | William Owen | 1,483 |
| 8th | Thomas Parry | 1,015 |
(The full order is Hugh Pritchard 1,638; Owen Thomas 1,548; William Owen 1,483; Robert Hughes 1,391; David Ellis 1,378; Griffith Jones 1,375; John Williams 1,370; Thomas Parry 1,015.)
(b) The “Good rock” filter leaves Hugh Pritchard and Owen Thomas: they are 1st and 2nd, the top 2 places. The filter for a rate of 220 or more leaves William Owen (230), John Williams (220), Thomas Parry (260) and David Ellis (220). Only William Owen is in the top 3, and only just (3rd). Thomas Parry, with the highest rate of all, is last. So good rock went with high pay, but a high rate did not.
(c) Pay was tonnes × rate, so the amount of finished slate mattered as much as the price. Hugh Pritchard’s crew made 46 tonnes: 46 × 190 = 8,740 pence. Thomas Parry’s crew made only 18 tonnes, because poor rock full of faults produces a lot of waste however hard you work: 18 × 260 = 4,680 pence. Their rate was 70 pence per tonne higher, but that did not make up for producing less than half as much slate. Poor rock also needed more blasting, so they paid the most for powder (470 pence) and had the biggest deductions (620 pence). To earn the same 1,638 pence per man as Hugh Pritchard’s crew, Thomas Parry’s crew would have needed about 398 pence per tonne (1,638 × 4 = 6,552; 6,552 + 620 = 7,172; 7,172 ÷ 18 = 398.4), not 260.
(d) The price was agreed at the start of the month, before anyone knew how the rock would turn out, and the steward worked for the quarry owner, so he had more power in the bargaining than the crew. A crew that worked every day on a poor bargain could take home much less than a crew on good rock: on this page, 1,015 pence per man against 1,638. Crews also had to pay for their own powder, and poor rock needed more of it. Men could feel that who got the good bargains, and at what price, depended on the steward. This was part of the anger behind the Penrhyn dispute of 1900 to 1903, when about 2,800 men walked out and were locked out for 3 years. The bargain system and the right to have a union to speak for them were at the heart of it. However, this is one invented month: the steward did give Thomas Parry the highest rate, so the system tried to be fair, and a crew’s luck could change the next month.
The sort and both filters are carried out correctly and the results are used to answer a question (good rock, not a high rate, went with high pay). Part (c) explains the pattern with the formula and numbers from the spreadsheet. Part (d) links the evidence to the real history in the background box, mentions the Penrhyn dispute and gives a balanced judgement that notes the limits of one invented month. Evidences criterion 5.