As mentioned in my previous blog (Year 6), here is the Excel data sheet, free for your school to use. The sole purpose of this design was to make data input easier for teachers whilst providing all the mock SAT’s data for leaders to analyse over time, as well as a handy overview in prep for the big day!
There are 4 files required (see Downloads at the bottom):
- Overview – SLT’s edition;
- Reading – Teacher edition;
- Maths – Teacher edition;
- and GPS – Teacher edition.
This will contain all the scores for all the tests across the year.
In this workbook, you will need to input all the pupils’ names, class name or number, KS1 data and additional contextual information e.g. PP, SEND, and EAL. I would suggest arranging the children in alphabetical order for each class just for ease.
The above information will automatically transfer to the Teacher edition workbooks.
Once the Teacher edition workbooks calculate the scaled scores, you will need to input these directly onto this overview sheet – it will calculate the percentages as soon as the data is inputted.
The writing data and the official SATs’ data will also need to be inputted directly onto this sheet. Once you have all the data, in the combined column, you will need to enter the Y (Yes) or N (No) whether they have achieved combined or not; it will then work out the percentages for you. Note – the GDS combined will need to be worked out manually.
Each cell must have a number inputted to calculate all pupils. For example, if you have 30 children in the class and all 30 children are meant to take the test, then even if they were absent, you MUST give them a score. On this overview sheet, input a 0 and this pupil will be used in the calculation.
If you do not want a pupil to be calculated for your percentages, then leave the cell BLANK.
This is useful if one class has 20 children for example, then leave the other 10 cells blank and it will only calculate the percentages for the 20 children you have data for.
You will notice that the headings are colour-coded. Each colour code represents 1 half term’s worth of tests. For no particular reason, I have designated the following:
- 2017 test to Autumn 1;
- 2018 test to Autumn 2,
- 2019 test to Spring 1;
- and 2022 test to Spring 2.
The yellow headings stand for predicted. This is where you may wish to update your predictions or targets across the year.
This tab does exactly what it says. It automatically provides the percentages for each subject, of each class, for each term, and the overall cohort percentage.
End of Year Final tab
This is for official SATs only. It saved us so much time understanding our results once we inputted our data. You will need to input the scores and Writing data on the Scores sheet and then the overall percentages will be shown on this page.
Use only Class 1, Class 2, Class 3 and Class 4 tabs
Once the SLT edition form is initially completed, teachers will be able to see the pupils on each of the Reading, Maths and GPS workbooks.
All that is required here is for teachers to input their raw data scores for each part of the Mock SATs e.g. raw score for Arithmetic Paper 1, Reasoning Paper 2 and Reasoning Paper 3, and that’s it! The scaled scores are worked out for you already. These scores now need to be inputted on the SLT overview sheet to be calculated.
Note – for no particular reason, the scaled scores conversions are for 2017 onwards.
For these documents, if you do not want any pupils or cells calculated in the percentages, then leave the cells blank (only the total column should say 0 which will make the scaled score column blank).
I hope this proves useful and saves you and your colleagues some valuable time.
Using the link below, download all four files. To download, either click download in the top left corner, or highlight (by clicking in the space and dragging the cursor over the four documents) all four excel documents > right click > download.
In your download folders, it will save to a folder called Onedrive. From here, you will be able to drag this folder where ever you need it and all four documents (still within it) should still correspond when you open them all.
Click “Grant Access” when prompted.
Click “Update” if prompted.
If you find any issues or there is anything I can be of help with, feel free to get in contact.