Cover lesson · History · Year 8 · Intermediate

Designing a spreadsheet for a slate quarry ledger

DCF: Data and computational thinking → Data and information literacy

Name: Class: Date:

Cover teacher: you need no subject knowledge

Print: this sheet, one per pupil, and the stimulus sheet “Chwarel y Foel: one month of the bargain ledger, March 1895”, one between two. No computers are needed: pupils design the spreadsheet on paper. Pupils need a pen, a ruler and a calculator.

Run: 5 min read the background box on the stimulus aloud, then pupils read the ledger in pairs · 10 min Step 2, designing the layout · 8 min Step 3, checking the data · 8 min Step 4, writing formulas · 15 min Step 5, calculating with working, on their own · 12 min Step 6, sorting, filtering and explaining. Total 58 minutes.

Collect: this sheet. Steps 2, 3, 4 and 6 are the digital competence work: planning a spreadsheet, checking and validating data, and writing formulas. Step 5 is also numeracy evidence you can assess: each pupil multiplies, adds, subtracts and divides for 4 crews and works out 5 totals and a mean, with the working written in the boxes. Mark it against the answer key; the working shows whether a wrong answer is a slip or a misunderstanding. The quarry and its figures are invented; the background on the stimulus is real history.

What you are doing

In the late 1800s, slate quarrymen in north-west Wales were paid by the “bargain”: each crew was paid for the finished slate it produced, at a price agreed with the steward each month. A museum wants to put one month of an (invented) quarry ledger into a spreadsheet so that visitors can explore it. You will plan that spreadsheet on paper, find the clerk’s mistakes before they get into it, write the formulas, check them by working the answers out yourself, and then use the finished spreadsheet to ask a historical question: was the bargain fair?

Your spreadsheet will use this plan, so that everyone’s formulas match:

Step 1: read the stimulus

Read the background box, the ledger page, the month-end totals and “How to read the ledger”.

Step 2: design the layout

Complete the plan for every column. Column A is done for you. A data cell is typed in from the ledger; a calculated cell holds a formula. For the data type, choose text, whole number or number (a number that could have a decimal part).

Column Heading in row 1 (include the unit) Data or calculated? Data type Value in row 2 (Griffith Jones)
ACrew leaderDataTextGriffith Jones
B
C
D
E
F
G
H
I
J
K
L

(b) Why should the unit (tonnes, pence) go in the heading and not in each cell? What would go wrong if a clerk typed “30 tonnes” into cell C2?

Step 3: check the data before it goes in

The clerk made 2 mistakes on the ledger page: one number is impossible and one value is missing. Use common sense and the month-end totals to find and fix them. Show how you worked out each correct value.

Error Crew and column What is wrong, and how you know Correct value and your working
1
2

(b) A spreadsheet can use data validation: a rule that refuses a value that breaks it. Write one rule that would have stopped each error.

ColumnValidation ruleWhich error it would stop

(c) If the missing value were simply left blank, what would =SUM(F4:H4) give for that crew? Why is a wrong answer with no warning more dangerous than an error message?

Step 4: write the formulas

A formula starts with = and uses cell references, not numbers, so it updates if the data changes. Use * for multiply, / for divide, + and −. The first one is done for you.

CellWhat it works outFormula
E2Gross pay for Griffith Jones’s crew=C2*D2
I2Total deductions for Griffith Jones’s crew
J2Net pay for Griffith Jones’s crew
K2Net pay per man for Griffith Jones’s crew
E7Gross pay for Thomas Parry’s crew (after E2 is filled down)
C10Total finished slate for all 8 crews
J10Total net pay for all 8 crews
C11Mean finished slate per crew
J11Mean net pay per crew

(b) Why is =C2*D2 better than typing the answer, 6000, into E2?

Step 5: check the formulas by hand, on your own

Before the museum trusts the spreadsheet, someone must check what the formulas should produce. Use your corrected data from Step 3. Write the calculation in each box, not just the answer. Griffith Jones’s row is done for you. The spreadsheet has already worked out the last 3 rows.

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 Jones30 × 200 = 6,000360 + 84 + 56 = 5006,000 − 500 = 5,5005,500 ÷ 4 = 1,375
Robert Hughes
John Williams
Hugh Pritchard
Thomas Parry
William Owen4,8303814,4491,483
Owen Thomas5,0403964,6441,548
David Ellis7,4805906,8901,378

(b) Now work out the totals and the mean, showing your working.

Cell and what it showsWorking and answer
C10: total finished slate (tonnes). Does it match the weighbridge book?
E10: total gross pay (pence)
I10: total deductions (pence)
J10: total net pay (pence). Check: E10 − I10 should give the same answer.
J11: mean net pay per crew (pence)

(c) Check one of your own answers a different way, for example by multiplying your net pay per man by the number of men. Show the check.

Step 6: sort, filter and explain

(a) Sort the 8 crews by column K, largest to smallest. Write the top 3 and the bottom crew.

PositionCrewNet pay per man (pence)
1st
2nd
3rd
8th

(b) Filter to show only crews whose remarks say “Good rock”. Which crews are left, and where are they in your sorted list? Then filter to show only crews with an agreed rate of 220 pence per tonne or more. Is any of them in your top 3?

(c) Thomas Parry’s crew had the highest rate and worked every day, but earned the least per man. Hugh Pritchard’s crew had the lowest rate but earned the most. Use the ledger to explain why a crew with good rock could earn more than a hard-working crew with poor rock.

(d) Using your results and the background box, explain why quarrymen might have thought the bargain system was unfair at times. Mention the Penrhyn dispute of 1900 to 1903.

Step by step

  1. 5 min Read the stimulus with a partner.
  2. 10 min Step 2: plan every column of the spreadsheet.
  3. 8 min Step 3: find and fix the 2 errors and write validation rules.
  4. 8 min Step 4: write the formulas.
  5. 15 min Step 5: calculate by hand with working, on your own.
  6. 12 min Step 6: sort, filter and explain.

Success criteria

If you finish early