OCR History
Teacher Resources for OCR History
PLEASE NOTE
This page no longer reflects the OCR coursework or controlled assessment requirements. It is kept here in case teachers wish to adapt the spreadsheet for other purposes.
Coursework and paperwork and the monitoring of target grades against current student performance seems to take for ever, and so to help speed up the completion of cover sheets, labels, and candidate authentication forms, I’ve created a set of mail-merges that will do it all for you.
This Excel file is designed to be used to input Year 11 GCSE mock grades (Levels and Marks) and will calculate total percentages, and map it against a rough set of grade boundaries. There are separate sheets for each of the 2 papers, and a third sheet for coursework marks. The coursework calculator is designed for the “off-the-peg” coursework on Russia which I currently teach. Assignment 1 is marked out of a straight 25 marks, while Assignment 2 is marked out of 50 marks and then halved to get the mark out of 25 for submission to OCR. The sheet is easy to edit if you need to.
The fourth sheet pulls in marks from Paper 1, Paper 2 and the coursework element and does a quick calculation to work out the rough ratio between each part before then mapping it onto a final grade. I show this to my students to show how the different elements of their course work together, and it is a good indicator of predicted marks.
The Word documents load data from the coursework data sheet of the Excel file and once mail-merged produce all the relevant paperwork to be sent off to OCR. Here are the Coursework Cover Sheet, the Candidate Authentication Form, Coursework Labels and the Coursework Summary Form which outputs student data in ascending candidate number order as required by OCR.
To make the files work for you, right-click and select “save target as” to a location on your computer – ideally downloading all the files to the same directory.
Load the Excel file “OCR History B marksheet” and after selecting the “Paper 1” tab at the bottom of the screen, fill in your class’s names and candidate numbers as indicated. I’ve put in the imaginary “Bob Bobson, candidate number 1234” to give you an idea. The name and number will be updated automatically on the other sheets, so now you just need to mark the student papers and coursework (groan) and input the marks on the spreadsheet. There are spaces both for the answer level and the mark. The level is not required to make the sheet work, but I like to use it for my own reference.
As you fill in the marks, the columns at the far-right of the spreadsheet will begin to update themselves with the total mark, total percentage, and an indicative grade mapped against rough grade boundaries which are set in the “Grades” sheet. You can change the grade boundaries on this sheet if you need to, by simply changing the percentage.
The coursework sheet is completed in the same way with levels and marks, but is set to process Assignment 1 (AO 1) to be marked out of 25, and Assignment 2 (AO 2 and 3) to be marked out of 50. The spreadsheet then halves the mark for Assignment 2 to get a mark out of 25 and gives an overall mark and percentage.
The total marks from Paper 1, Paper 2, and the Coursework sheets then appear in the “Overall” sheet, which works out a rough mark ratio of exam-to-coursework and overall grade.
To process the coursework paperwork first load the Word document you wish to work on. You will be warned that “opening this document will run…” Click “Yes“. You will now need to point Word to the location of your saved Excel spreadsheet. Once you’ve located it and clicked “Open” you need to make sure that “Coursework$” is selected, and click “OK“. The sheet will now load.
You’ll notice some strange codes in some boxes such as «F1» – these load the names and candidate numbers from your spreadsheet, so leave them as they are. However, you will need to fill in the blank boxes for your school and examination year along with the teaching class/set if appropriate.
Go to the menu bar and click Tools > Letters and Mailings > Mail Merge… This will open a window at the right-hand side of yoru screen. Click “Edit recipient list” and ensure that the boxes at the side of all your candidates are ticked. Click “OK” and then in the main mail-merge window click “Next: write your letter“. You don’t need to change anything here, so just click “Next: preview your letters“. If you scroll down the main window you will see that the «F1» codes have been replaced with your students’ information.
As long as everything looks right, click “Next: complete the merge“. If you want to double-check all the merged sheets, you can select to “Edit individual letters…”, otherwise just click “Print…” and the sheets will update themselves with individualised information on each of your students, ready to be sent to OCR and hopefully saving you an ENORMOUS amount of time writing them out by hand!
