CAMBRIDGE NATIONALS / R060 overview
Offline-ready learning resource
CAMBRIDGE OCR R060 · SAMPLE ASSESSMENTWestwood Dog Training
Westwood Dog Training
Worked Exemplar & Step-by-Step Walkthrough
Teacher-guided learning resource for the supplied practice assessment, not a live NEA submission. Covers Task 1 (23 marks), Task 2 (23 marks), Task 3 (14 marks).
Included resources:
📊 Excel exemplar
📄 Printable teacher guide
📝 Editable teacher guide
📚 Original sample brief
Start here
The attached ZIP contains the OCR sample assignment and Westwood assets. This website explains one worked, teachable approach to the entire brief and links to a formula-driven spreadsheet built from those assets.
Assessment boundary: this is only the published sample assessment material, which the brief says must not be used for live assessment. The workbook is an illustrative teacher-built solution, not an official mark-awarded Cambridge OCR exemplar. No authentic screenshots have been invented, and no test results are represented as witnessed without the user opening the workbook.
35Dogs imported
122Recorded visits
22Promotion eligible
£7.88Buddy income
Primary sources: OCR R060 sample assignment v3, supplied asset CSVs, supplied eight-test plan, and J836 specification pages 26–32. The walkthrough is original teaching commentary, not text from an OCR mark scheme.
Assessment map
Three tasks, four topic areas, 60 marks in total. Use these headings to organise evidence clearly.
| Task | Specification | Maximum | Practical evidence / discussion |
|---|---|---|---|
| 1. Plan and design | Topic Area 1 | 23 | Requirements, design documents, flowchart, wireframe, navigation plan, calculations and output designs |
| 2. Create | Topic Area 2 | 23 | Working spreadsheet using supplied files, validation, formulas, lists, charts and separate business models |
| 3. Test & evaluate | Topic Areas 3 and 4 | 14 (7 + 7) | Only the eight prescribed tests with authentic evidence; evidence-based comparison against client needs and HCI principles |
The published mark bands describe effective planning, accurate and efficient creation, thoroughly documented tests and comprehensive evaluation at the upper band. This is guidance for discussion, not a predicted mark for this exemplar.
Task 1 · Worked planning and design exemplar
Explain requirements first, then show what is designed, how users move between screens, and how calculations become outputs.
1. Requirements-to-solution design
| Client requirement | Proposed design response | Implemented example | Design judgement |
|---|---|---|---|
| Easy-to-use HCI, calculations not on landing screen | START is the first sheet; seven clearly labelled links and a return link on each functional sheet | START; HYPERLINKs | First sheet and sheet tabs must be checked in Excel |
| Details of owners and dogs | OWNERS stores names and telephone; MONDAY stores dog, ID and F/R; lookup matches by OwnerID | OWNERS / MONDAY | Duplicate dog names mean ID must identify a record |
| Data integrity | F/R and class-code dropdowns; colour-coded inputs; invalid owner lookup warning | MONDAY C7:G41, H7 | Protect formula cells in a production version |
| Income per dog | Four per-class subtotals add into dog total | MONDAY J:N | Buddy £7.88; Daisy £13.00 |
| Income by class and over four weeks | Class totals, grand total and period attendance reports | REPORTS | Class income totals reconcile to £413.90 |
| Two-week attendance / non-attendance | COUNTIF by class and period; selectable list distinguishes class-specific no-visit from zero visits anywhere | REPORTS / LISTS | Clearly define what no attendance means |
| Special promotion | Four recorded visits across four weeks -> YES | MONDAY O:P | 22 dogs eligible |
| Charts and lists | Grouped period attendance chart and filterable named lists | REPORTS / LISTS | Chart labels and filtered output should be screenshotted |
| Remove discount / increase fees 50p | Separate future-revenue calculations per owner, without changing original data | MODELS; MONDAY Q:R | £449.50 / £469.88 |
2. Data flow / processing flowchart
INPUT
Owners.csv • Fees.csv • Monday Dogs.csv
Owners.csv • Fees.csv • Monday Dogs.csv
VALIDATE
Check OwnerID, F/R, P/B/S/G or blank
Check OwnerID, F/R, P/B/S/G or blank
PROCESS
Lookup fees → discount → class subtotals → total
Lookup fees → discount → class subtotals → total
OUTPUT
Reports • chart • lists • scenarios
Reports • chart • lists • scenarios
Functional logic (no cell references in original design plans): for each dog and each class category, count recorded visits; multiply by class fee; apply 25% off if charge status is R; round the class-category subtotal to pennies; sum subtotals for dog income. If exactly four valid weekly class entries are recorded, output “YES” for pet-shop promotion. Combine dog subtotals for each class-income report.
3. Annotated HCI wireframe
WELCOME
Logo + staff guidance
Logo + staff guidance
MONDAY
Inputs clearly labelled
Inputs clearly labelled
REPORTS
KPIs + chart + labels
KPIs + chart + labels
HOME LINK
Visible on every sheet
Visible on every sheet
- Learnability: seven direct, descriptive links; people do not have to guess abbreviations.
- Memorability: the same Home action appears on every operational worksheet.
- Accessibility: high-contrast navy headings, text labels as well as colours, readable column widths and wrapped headings.
- Purpose and layout: staff view the clean START screen first; data entry is yellow, calculated results are blue; charts are separate from input areas.
- Messages: missing OwnerID displays “OWNER NOT FOUND”; drop-down rules reduce invalid class codes.
Design limitation to discuss: calculation columns remain viewable on MONDAY if a user scrolls. A stronger final system would hide helper calculations, protect formula cells, and limit editing to the yellow inputs; the workbook does not claim to enforce those protections.
Task 2 · Step-by-step creation walkthrough
Follow these in Excel or a compatible spreadsheet editor. The exemplar already contains the worked formulas, so the sequence can be demonstrated, recreated, altered and checked.
Workshop progress: tick each stage when you have carried it out in your own practice copy. Saved to this browser only.0 / 15 steps
01 · Check the provided assets
Open Owners.csv, Fees.csv and Monday Dogs.csv. Do not delete entries or treat blank attendance cells as sessions. The supplied four classes are Puppy (P, £3.00), Bronze (B, £3.50), Silver (S, £4.00) and Gold (G, £4.50). Note F = full, R = 25% reduced; the original files do not supply ages or benefit status.
02 · Plan the HCI before creating cells
Sketch START with seven navigation buttons (MONDAY, OWNERS, FEES, REPORTS, LISTS, MODELS, TEST PLAN), a visible logo and clear instructions. Add a back-to-home button on each page. Keep calculation sheets off the landing page and separate input areas from formula results.
03 · Import the lookup data
Paste the 35 owner records into OWNERS!A6:E40 and class codes, class names and fees into FEES!A6:C9. Keep telephone numbers formatted as text. In OWNERS!F6 enter =B6&" "&C6&" "&D6 then copy down to F40. In FEES!G5 enter 25% and in G6 enter 0.50 as the fixed scenario increment.
04 · Import dog attendance data
Use MONDAY!A7:G41 for the 35 dogs; enter their OwnerID, dog name, F/R status and week 1–4 class codes. In the source CSV, the real column headers are on its third row. Blank cells mean no recorded visit; do not replace blanks with a class code.
05 · Add meaningful validation and formatting
Set MONDAY!C7:C41 to a dropdown list F,R. Set D7:G41 to P,B,S,G (allow blanks). Colour input cells pale yellow and formula cells pale blue. Apply currency formatting to J:N and Q:R. Add a visible warning for unknown owner IDs, and use the same house style on the main menu and reports.
06 · Link owner information using lookup functions
In MONDAY!H7 enter =IFERROR(VLOOKUP(A7,OWNERS!$A$6:$F$40,6,FALSE),"OWNER NOT FOUND"). In I7 enter =IFERROR(VLOOKUP(A7,OWNERS!$A$6:$F$40,5,FALSE),"MISSING"). Fill both down to row 41. A fixed lookup range is important when formulas are copied.
07 · Calculate one class-category subtotal per dog
For Puppy in MONDAY!J7 use =ROUND(COUNTIF(D7:G7,"P")*FEES!$C$6*IF(C7="R",1-FEES!$G$5,1),2). In K7 change P to B and C6 to C7; in L7 use S and C8; in M7 use G and C9. Use absolute references for the fee/discount parameters, relative row references for each dog. Fill J7:M7 down to row 41.
08 · Generate totals and promotion flags
Enter =SUM(J7:M7) in N7. Enter =COUNTIF(D7:G7,"P")+COUNTIF(D7:G7,"B")+COUNTIF(D7:G7,"S")+COUNTIF(D7:G7,"G") in O7. In P7 enter =IF(O7=4,"YES","NO"). Fill N7:P7 down. Buddy (ID 103) should show £7.88; Daisy (ID 110) £13.00. Four visits qualify for the promotion.
09 · Build the four-week finance report
In REPORTS, use =SUM(MONDAY!$N$7:$N$41) for four-week income. For Puppy use =SUM(MONDAY!J7:J41); Bronze uses K, Silver L and Gold M. Use COUNTIF(MONDAY!D7:E41,"P") and COUNTIF(MONDAY!F7:G41,"P") for Puppy attendance in each two-week block; repeat for B/S/G.
10 · Build the interactive class lists
In LISTS set B5 to chosen class code, B6 to selected period. For a dog row (LISTS row 9) enter =IF($B$6="Weeks 1–2",COUNTIF(D9:E9,$B$5),COUNTIF(F9:G9,$B$5)) in H9; =IF(H9>0,B9,"") in I9; =IF(H9=0,B9,"") in J9. Fill down to row 43. Filter I to nonblank for attendees; J to nonblank for dogs with no visit to that class. The latter can still attend another level.
11 · Separate actual attendance from missing weeks
For each dog, a blank in BOTH weeks means zero attendance at ANY class in that period. Example in LISTS!K9: =IF(AND(D9="",E9=""),"NO VISITS","HAS VISIT"). For weeks 3–4 use columns F and G. The report has 2 dogs with no visits in weeks 1–2, and 3 in weeks 3–4.
12 · Create the chart with a clear purpose
On REPORTS, use the summary table A14:D18 for a clustered column chart titled “Class attendance by period”. Use Puppy, Bronze, Silver and Gold as categories and Weeks 1–2 / Weeks 3–4 as two named series. Check the vertical scale counts visits, not distinct dogs; include a legend.
13 · Model the two changes independently
No discount: for each dog sum ROUND(COUNTIF(week-codes,"P")*PuppyFee,2) and repeat for B/S/G; never apply R discount. +£0.50: for each class sum ROUND(COUNTIF(week-codes,code)*(class fee+£0.50)*(1−discount if R),2). Use MONDAY columns Q and R, then MODELS to sum each scenario. Never overwrite the baseline fees.
14 · Test only the supplied eight items
Copy the eight descriptions from the supplied Westwood Test Plan.rtf. Before testing, enter test data and precise expected results. Use Excel to carry out opening, navigation, validation, Buddy/Daisy calculations, chart and list checks. Fill actual results and take genuine screen captures. If a test fails, record what was changed and retest.
15 · Evaluate against the brief and HCI
Write an evidence-led evaluation: relate each feature to Zac’s needs, quote actual output values, explain clarity of layout/navigation, discuss colour contrast, learnability, labels and meaningful messages. Identify improvements you can justify (e.g. lock formulas, hide helper calculations). Do not claim user testing or invent feedback.
Practical formula workshops
Click “Copy” to paste an example into Excel. Change row 7 to a different dog, then discuss what changes and what remains fixed.
Owner lookup · MONDAY!H7
=IFERROR(VLOOKUP(A7,OWNERS!$A$6:$F$40,6,FALSE),"OWNER NOT FOUND")
Exact-match VLOOKUP matches OwnerID to the sixth column in OWNERS; dollar signs lock the owner table when copying down.
Bronze subtotal · MONDAY!K7
=ROUND(COUNTIF(D7:G7,"B")*FEES!$C$7*IF(C7="R",1-FEES!$G$5,1),2)
COUNTIF counts Bronze sessions, fee is £3.50, IF applies 75% to reduced-rate records; ROUND finishes each per-dog/per-class subtotal at pennies.
Dog income · MONDAY!N7
=SUM(J7:M7)
Sum four separately rounded class-category subtotals, so class totals and dog totals reconcile.
Sessions recorded · MONDAY!O7
=COUNTIF(D7:G7,"P")+COUNTIF(D7:G7,"B")+COUNTIF(D7:G7,"S")+COUNTIF(D7:G7,"G")
Counts only valid training codes, excluding blanks and any accidental invalid code.
Promotion · MONDAY!P7
=IF(O7=4,"YES","NO")
Only four recorded weekly sessions qualify, regardless of class changes.
Puppy, weeks 1–2 · REPORTS!C15
=COUNTIF(MONDAY!$D$7:$E$41,B15)
The criteria cell is P for Puppy. Counts visits, not distinct dogs.
Four-week income · REPORTS!B7
=SUM(MONDAY!$N$7:$N$41)
Aggregates already rounded per-dog category totals, consistent with individual records.
Class attendance choice · LISTS!H9
=IF($B$6="Weeks 1–2",COUNTIF(D9:E9,$B$5),COUNTIF(F9:G9,$B$5))
Uses a chosen class and two-week period, so staff can filter to visited / not visited lists.
No visits at ANY level, weeks 1–2 · LISTS!K9
=IF(AND(D9="",E9=""),"NO VISITS","HAS VISIT")
This measures a different meaning of absence than “no visits to Puppy”.
Higher fees scenario · one class element of MONDAY!R7
=ROUND(COUNTIF(D7:G7,"B")*(FEES!$C$7+FEES!$G$6)*IF(C7="R",1-FEES!$G$5,1),2)
Repeat for P/S/G and add the four class elements to model a £0.50 increase with discounts retained.
Rounding and full vs reduced charges: why the pennies matter
Buddy has three Bronze sessions and an R status. The unrounded amount is 3 × £3.50 × 0.75 = £7.875. This exemplar rounds at the subtotal for each dog within each class category, so Buddy is £7.88. Rounding each individual Bronze visit instead would give £2.63 × 3 = £7.89. These are not the same policy.
This selected policy gives consistent row and class totals for the supplied data. It is an exemplar design choice, not a special OCR rounding rule stated in the task. Document the choice in a real solution and apply it consistently.
Common formula-error workshop:
#N/A from missing OwnerID → verify the lookup range and input; #REF! after deleting a fee column → repair references; #NAME? from misspelt COUNTIF → correct the function; accidental £ typed as text → use currency formatting instead.Expected outputs · independent answer key
Values obtained from the original 35 dog records and the explicit per-dog/per-class subtotal rounding policy. Use these to check formula results after making a copy of the workbook.
35Dogs imported
122Recorded visits
22Promotion eligible
£7.88Buddy income
£13.00Daisy income
£413.90Baseline income
£449.50No-discount income
£469.88Fees +£0.50 income
Class attendance and income
| Class | Weeks 1–2 visits | Weeks 3–4 visits | Four-week class income |
|---|---|---|---|
| Puppy (P) | 16 | 17 | £90.75 |
| Bronze (B) | 14 | 23 | £114.65 |
| Silver (S) | 18 | 8 | £96.00 |
| Gold (G) | 10 | 16 | £112.50 |
| All classes | 58 | 64 | £413.90 |
Two types of non-attendance
At any level: no sessions in either of the two weeks: 2 dogs in weeks 1–2 (Roscoe and Cooper, OwnerID 197) and 3 dogs in weeks 3–4 (Remi, Maya and Sadie). For a specific class: choosing Puppy in weeks 1–2 finds 10 distinct dogs that attended at least one Puppy session and 25 dogs with no Puppy session. Those 25 may still have attended Bronze, Silver or Gold.
Business discussion
Remove the discount: projected revenue increases by £35.60 to £449.50. Increase every class fee by £0.50, keeping discount: projected revenue increases by £55.98 to £469.88. This does not show future profit, because costs and changed demand are absent from the data; it is a fixed-attendance revenue comparison.
Task 3 · Complete the supplied eight-test plan
The original RTF includes exactly eight tests. Use those eight descriptions only: do not invent extra numbered formal tests for this sample. The workbook TEST PLAN sheet gives a worked expected result and screenshot prompt for each.
| No. | Prescribed test | Suggested input | Expected result | Actual evidence to capture |
|---|---|---|---|---|
| 1 | Spreadsheet opens on main menu | Open the saved .xlsx | START first with menu and brand | Capture the loaded workbook with START selected. |
| 2 | Navigate main menu → Monday | Click OPEN MONDAY CLASSES | MONDAY opens at A1 | Capture MONDAY sheet after clicking the link. |
| 3 | Navigate Monday → menu | Click ← STAFF HOME | START returns | Capture START after returning. |
| 4 | Data integrity | Try invalid X in F/R; Z in week-code | Allowed choices F/R and P/B/S/G; invalid inputs rejected/flagged | Screenshot a validation message; if pasting bypasses validation, note a limitation. |
| 5 | Buddy income | Owner 103; R; 3 Bronze visits | £7.88 using subtotal rounding | Capture MONDAY Buddy row and FEES Bronze amount. |
| 6 | Daisy income | Owner 110; F; 2 P and 2 B | £13.00 | Capture MONDAY Daisy row and corresponding fee references. |
| 7 | Correct chart data | REPORTS class attendance chart | P 16/17; B 14/23; S 18/8; G 10/16 | Capture chart with the REPORTS class-period table. |
| 8 | Correct list data | LISTS choose P + Weeks 1–2 | 10 Puppy attendees, 25 with no Puppy visit | Capture the dropdowns and filtered I/J lists. |
Authenticity: The example plan describes what should happen, not a claim that a person has carried out the test in Excel. Fill in the Actual Result and Evidence columns after doing each action; record real remedial action only if a test fails. The ZIP does not include fabricated test screenshots.
Test 5 worked example
Test data: Buddy (OwnerID 103), reduced price (R), no week 1 visit and Bronze visits in weeks 2–4. Expected: £7.88. Actual (student must observe): check MONDAY!N8. If different: verify the correct rate, IF discount, absolute references, and rounding position; rerun the test and record the final actual result and evidence.
Task 3 · Model evaluation and critical discussion
The paragraphs below are an illustrative, evidence-led evaluation of the supplied worked practice spreadsheet. Students should discuss their own actual system and real tests in independent assessment work.
Fitness for purpose
Strength: the solution connects 35 dogs to the supplied owner records by OwnerID rather than relying on dog names, which recur in the data. The MONDAY register retains all four weekly entries and calculates £413.90 in income using the documented policy. The report confirms 122 recorded visits, separated into 58 in weeks 1–2 and 64 in weeks 3–4. Class subtotals reconcile to the overall income, supporting Zac’s requirement for accurate revenue information.
Strength: the LISTS interface responds to the chosen level and period. Its outputs clearly separate “no class-specific visit” from “no visit at any level”. This distinction reduces misleading conclusions when a dog moves from Puppy to Bronze or Silver to Gold. The promotion formula identifies 22 dogs with all four visits, using the supplied rule rather than adding unsupported eligibility rules.
Strength and limitation: MODELS predicts £449.50 without the discount, or £469.88 if each fee increases by £0.50 while the reduced charge remains. These are relevant to Zac’s decisions, but they assume the same dogs continue to attend; the scenario does not predict changes in customer demand or profit. For a real business this should be labelled “projected revenue under fixed attendance”, not “guaranteed extra profit”.
Effectiveness of the HCI
The START sheet puts navigation and key KPIs in one place instead of opening directly onto a dense calculation grid. Descriptive links and the consistent return action support learnability and memorability. Dark high-contrast headings, clear fonts, space between major sections and distinct yellow editable input cells support accessibility and efficient staff use. However, sheet tabs and calculation columns remain available; this weakens the ideal of hiding processing and increases the risk that someone can edit a formula.
Specific, justified improvements
- Protect formula columns and lookups, allowing staff to edit only approved data cells. This would reduce accidental overwriting and make the integrity measures stronger. Sheet protection is not enforced in this illustrative workbook.
- Hide back-end helper columns and provide a dedicated data-entry view, so that staff see only the fields needed for recording attendance. This would better match the requirement that calculations are out of sight during normal use.
- Use a stricter OwnerID validation list or named-range dropdown rather than simply flagging unknown IDs. This would avoid bad IDs before a user submits a record.
- Add a printable promotion list and clear report print areas, so Zac can share compact outputs without working through all 35 rows.
To discuss upper-band quality, connect a requirement to a specific implemented feature, a real result or test and an explained judgement. “The chart looks good” is much weaker than explaining why labelled two-week class comparisons support staffing decisions.
Interactive checkpoint questions
Use the worked example to calculate each answer. Feedback appears immediately; checking does not change the Excel workbook.
Answers and explanations can also be revisited in the Expected outputs section.
Teaching and assessment discussion
What the sample can teach
- Different sources play different roles: CSV imports are data assets, not an excuse to retype records; OwnerID is the stable join key. Dog name alone is not unique.
- A complete design precedes formulas: the brief specifically calls for flowcharts, wireframes, navigation and output designs, not just a finished spreadsheet.
- Explain the rounding decision: reduced Bronze charges introduce fractional pennies. Consistency between dog-income and class-income totals matters more than silently hiding the issue.
- Define the attendance measure: sessions, distinct dogs attending a class, and dogs absent from all levels are different metrics.
- Evaluate what the model cannot answer: changes in price may alter attendance, and revenue is not profit.
- Use only the supplied eight formal tests: students may trial formulas as they develop them, but the completed test plan should reflect the specified list and contain genuine observations and evidence.
Discussion prompts for a class
- Why is an owner ID needed when the sheet already has dog names?
- How does a relative reference differ from an absolute reference in the Bronze calculation?
- Which charts communicate class attendance most clearly? Would a pie chart be suitable for comparing two periods?
- If Puppy sessions increased but another class fell, how might Zac plan staffing?
- What could go wrong if formula cells were editable by every member of staff?
- What specific evidence supports (or challenges) a claim that the HCI is accessible?
Practical classroom delivery
Use one lesson for Task 1 annotated design; two or three lessons for CSV importing, lookups, conditional formulas and validation; one lesson for class summaries, lists and charts; one for scenario modelling; and one or two for prescribed testing and evaluation writing. This is a flexible practice sequence—not a replacement for the official SAM’s suggested 10–12 guided hours.
Important boundary: The teacher can display and discuss this sample solution because the published SAM is explicitly for practice. Do not distribute it as a template for a separate live assignment, copy its evaluation into a student NEA, or imply that these worked answers are an official OCR grading standard.