Slide 1 of 7 · Settling starter · History · Year 8
5 minutesDesigning a spreadsheet for a slate quarry ledger
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 the Chwarel y Foel ledger, March 1895, into a spreadsheet. You will plan it on paper.
While the register is called: write your name, class and date on your worksheet, then read the background box and the ledger page on the stimulus sheet with your partner.
Starter question: How was a crew’s gross pay worked out, and what was taken off it to give net pay?
Slide 2 of 7 · Today’s brief and key words · History · Year 8
3 minutesYour brief: plan it, check it, write the formulas, prove them
You will plan every column of the spreadsheet, find the clerk’s 2 mistakes before they get into it, write the formulas, check them by working the answers out yourself, then sort and filter to answer a historical question: was the bargain fair?
Data or calculated
A data cell is typed in from the ledger. A calculated cell holds a formula that starts with = and uses cell references such as C2.
Data validation
A rule that refuses a value that breaks it, such as “must not be blank” or “must be between 1 and 100”.
The plan
Row 1 headings, rows 2 to 9 the 8 crews in ledger order, row 10 totals, row 11 means. Columns A to D from the ledger, E gross pay, F to H deductions, I to K totals and net pay, L remarks.
Slide 3 of 7 · Step 2: design the layout · History · Year 8
8 minutesStep 2: plan every column, A to L
For each column write the heading (with its unit), whether it is data or calculated, the data type (text, whole number or number, which could have a decimal part), and the value in row 2 for Griffith Jones. Then (b): why does the unit go in the heading, not in each cell?
| Column | Heading in row 1 (with unit) | Data or calculated? | Data type | Value in row 2 |
|---|---|---|---|---|
| A | Crew leader | Data | Text | Griffith Jones |
| B | Men in crew | Data | Whole number | 4 |
Slide 4 of 7 · Steps 3 and 4: check the data, write the formulas · History · Year 8
16 minutesStep 3 & Step 4: find the clerk’s 2 mistakes, then write the formulas
Step 3 (8 min): one number on the ledger is impossible and one value is missing. Use common sense and the month-end totals (weighbridge 236 tonnes, storeroom 2,930 pence) to find and fix them, showing your working. Then write a validation rule for each, and answer (c).
Step 4 (8 min): write the 8 remaining formulas. Start with =, use cell references not numbers, * to multiply, / to divide.
| Cell | What it works out | Formula |
|---|---|---|
| E2 | Gross pay for Griffith Jones’s crew | =C2*D2 |
Slide 5 of 7 · Step 5: check the formulas by hand · History · Year 8
15 minutesStep 5: calculate by hand, with working, on your own
Use your corrected data: 27 tonnes for John Williams, 430 pence powder for Robert Hughes. Write the calculation in each box, not just the answer, for Robert Hughes, John Williams, Hugh Pritchard and Thomas Parry. Then (b) the 5 totals and the mean, and (c) check one answer a different way.
| 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 |
Slide 6 of 7 · Step 6 and self check · History · Year 8
9 minutesStep 6: sort, filter and explain, then check your work
(a) Sort the 8 crews by column K, largest to smallest: top 3 and bottom crew. (b) Filter for “Good rock”, then for a rate of 220 or more. (c) Why could a crew with good rock earn more than a hard-working crew with poor rock? (d) Why might quarrymen have thought the bargain unfair? Mention the Penrhyn dispute of 1900 to 1903. Then tick the success criteria and correct 1 thing.
- I can plan a spreadsheet with clear headings and units, and say which cells are data and which are calculated.
- I can find impossible and missing values, correct them using other evidence, and write validation rules.
- I can write formulas using cell references, including SUM and AVERAGE.
- I can multiply, add, subtract and divide accurately with my working shown, and check an answer a second way.
- I can sort and filter data to answer a question, and explain why the bargain system could be unfair.
Slide 7 of 7 · Plenary review · History · Year 8
2 minutesQuick-check answers before handing in
Check your main answers before your worksheets are collected.
| Question | Correct answer |
|---|---|
| What were the clerk’s 2 mistakes, and what are the correct values? | John Williams’s 270 tonnes should be 27; Robert Hughes’s missing powder should be 430 pence. |
| What is the formula for net pay per man in row 2? | =J2/B2 |
| Which crew earned the most per man, and which the least? | Hugh Pritchard, 1,638 pence, on good rock; Thomas Parry, 1,015 pence, on poor rock despite the highest rate. |