Worked example · History · Year 8

Designing a spreadsheet for a slate quarry ledger

DCF: Data and computational thinking → Data and information literacy

What this is

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.

Step 2: the layout plan

ColumnHeading in row 1 (with unit)Data or calculated?Data typeValue in row 2 (Griffith Jones)
ACrew leaderDataTextGriffith Jones
BMen in crewDataWhole number4
CFinished slate (tonnes)DataNumber (accept whole number)30
DAgreed rate (pence per tonne)DataWhole number200
EGross pay (pence)CalculatedNumber6000
FPowder (pence)DataWhole number360
GCandles (pence)DataWhole number84
HTools sharpened (pence)DataWhole number56
ITotal deductions (pence)CalculatedNumber500
JNet pay (pence)CalculatedNumber5500
KNet pay per man (pence)CalculatedNumber (it could have a decimal part)1375
LRemarksDataTextFair 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.

Step 3: checking the data

ErrorCrew and columnWhat is wrong, and how you knowCorrect value and working
1John 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.
2Robert 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:

ColumnValidation ruleWhich error it would stop
CMust 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.
FMust 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.

Step 4: formulas

CellWhat it works outFormula
E2Gross pay for Griffith Jones’s crew=C2*D2
I2Total deductions for Griffith Jones’s crew=F2+G2+H2 or =SUM(F2:H2)
J2Net pay for Griffith Jones’s crew=E2-I2
K2Net pay per man for Griffith Jones’s crew=J2/B2
E7Gross pay for Thomas Parry’s crew (after E2 is filled down)=C7*D7
C10Total finished slate for all 8 crews=SUM(C2:C9)
J10Total net pay for all 8 crews=SUM(J2:J9)
C11Mean finished slate per crew=AVERAGE(C2:C9)
J11Mean 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.

Step 5: calculating by hand

CrewE: Gross pay
C × D
I: Total deductions
F + G + H
J: Net pay
E − I
K: Net pay per man
J ÷ B
Griffith Jones30 × 200 = 6,000360 + 84 + 56 = 5006,000 − 500 = 5,5005,500 ÷ 4 = 1,375
Robert Hughes36 × 210 = 7,560430 + 105 + 70 = 6057,560 − 605 = 6,9556,955 ÷ 5 = 1,391
John Williams27 × 220 = 5,940320 + 80 + 60 = 4605,940 − 460 = 5,4805,480 ÷ 4 = 1,370
Hugh Pritchard46 × 190 = 8,740380 + 100 + 70 = 5508,740 − 550 = 8,1908,190 ÷ 5 = 1,638
Thomas Parry18 × 260 = 4,680470 + 88 + 62 = 6204,680 − 620 = 4,0604,060 ÷ 4 = 1,015
William Owen4,8303814,4491,483
Owen Thomas5,0403964,6441,548
David Ellis7,4805906,8901,378

(b) Totals and mean:

Cell and what it showsWorking 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).

The finished spreadsheet: formulas and values

Formula view (row 2 shown; rows 3 to 9 are the same formulas filled down, so row 5 has =C5*D5 and so on):

CellEIJK
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):

ABCDEFGHIJKL
1Crew leaderMen in crewFinished 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
2Griffith Jones4302006000360845650055001375Fair rock
3William Owen3212304830270664538144491483Fair rock, very wet
4Robert Hughes53621075604301057060569551391Fair rock
5John Williams4272205940320806046054801370Fair rock
6Hugh Pritchard54619087403801007055081901638Good rock, splits clean
7Thomas Parry4182604680470886262040601015Poor rock, many faults. Crew worked every day of the month.
8Owen Thomas3242105040290604639646441548Good rock
9David Ellis53422074804101008059068901378Fair rock
10Total33236502702930683489410246168
11Mean4.12529.5217.56283.75366.2585.37561.125512.7557711399.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.

Step 6: sort, filter and explain

(a) Sorted by column K, largest to smallest:

PositionCrewNet pay per man (pence)
1stHugh Pritchard1,638
2ndOwen Thomas1,548
3rdWilliam Owen1,483
8thThomas Parry1,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.

If you finish early: answers

Why this response is secure