Engagement HubYear 12 Enterprise Computing · Library data case

Section 2: Data Analysis

Task requirements

Verbatim · Assessment Task Notification
  1. Analyse data tables using spreadsheet formulas (lookups, filtering, per-capita normalisation, and ratios).
  2. Compare Penrith against similar councils (e.g. similar size).
  3. Investigate and explain key data inconsistencies or reporting inconsistencies discovered across the tables.
  4. Construct and integrate at least one data chart (e.g., comparative scatter plot) that supports your findings.
Marking Criteria 2 · top band (13-15)

Critically analyses complex relational datasets using spreadsheet formulas (lookups, normalisation, ratios) with thorough calculation accuracy. Evaluates service performance by comparing metrics against a well-justified, defined peer cohort, identifying critical efficiency gaps. Investigates and explains key data inconsistencies across reporting tables through rigorous verification. Constructs an original, highly effective data chart that directly substantiates analytical findings.

Activities

1

Per-capita normalisation

Totals reward size. Divide by population (or by loans) before any comparison, and keep the formula in your workbook so a reader can audit it.

Formula pattern · practice extract field names
Loans per resident
= XLOOKUP(council, extract[council], extract[total_circulation])
  / XLOOKUP(council, extract[council], extract[population_2024])

Cost per loan
= XLOOKUP(council, extract[council], extract[total_expenditure_aud])
  / XLOOKUP(council, extract[council], extract[total_circulation])
2

Peer group

Work through the core worksheet on the practice data, then make the same decision, with a written reason, for Penrith.

Core worksheet · all students
PEER GROUP DECISION — whose figures can we fairly compare?

Council        | Population | Loans/resident
---------------|------------|---------------
Cessnock       | 69,352     | 2.65
Shoalhaven     | 110,803    | 3.83
Maitland       | 98,163     | 3.86
Wagga Wagga    | 68,951     | 4.13
Willoughby     | 79,634     | 10.70
Kiama          | 23,173     | 6.45

1. Compute the median loans/resident for all six councils. ______
2. One council's figure is far higher than the rest, and one is far smaller in population. Which one changes the comparison most if removed? Why?
3. Decide your peer group. Justify in two sentences: why these, why not others?
4. Recompute the median. Did the benchmark move? ______
Modified worksheet on the Inclusion Bench
3

Data inconsistencies

Run every table through these checks before you write about it.

Do the categories add up to the total?A total that matches its parts can still hide a category that is filed in the wrong place.
Is a zero plausible?A zero can be behaviour, or it can be a recording or configuration artefact. Decide which before you interpret it.
Does one council behave unlike every peer?
Can you name two explanations and choose one?
4

Data chart

Whatever chart you choose, it has to back a specific finding.

Title states the finding Population (residents) Loans per resident 1 2 3 4 SCHEMATIC — illustrative positions
Chart checklist
1The title states what the reader should conclude.
2Both axes are labelled, with units, and use normalised figures.
3Your peer group is visibly marked; the focus council is highlighted.
4Excluded councils stay on the chart, marked as excluded.

Help on request

Ask at the lowest level that gets you moving.

Level 1A question back“What would change if you removed the smallest council?”
Level 2A worked step on the practice dataOne move shown on the practice data. You repeat it on the Penrith data.
Level 3Five-minute conferenceBring your workbook. We look at one decision together.
End of Week 6The GateTwo open questions. Your answers set your Week 7 kit.
Go to the Gate