Introduction
In my previous post I showed you how to install the new templates and map your assessment data into the standard aspects provided by Capita. In this post I want to emphasise the importance of getting your data just right. In part 3 we’ll look at the analysis provided by the marksheets, the excel report and by SIMS Discover.
Getting Your Data Sorted
You’ve probably already read the latest P8 Technical Guidance document from the DfE. Now is a good time to read it again. Remember, at its heart, the P8 calculation is all based on estimates. For schools that expect to calculate a 100% accurate P8 score this should set alarm bells ringing – it’s just not possible to offer a P8 score to two decimal places and expect it to be the actual P8 score a school receives.
However, to give you the best chance of getting a P8 output score that is as accurate as possible, you need to make sure that each of the input values is accurate. Remember: garbage in, garbage out!
Mapping your Termly Teacher Assessments
Before you start, read Capita’s guidance on mapping your assessments (see the link in part 1). There are so many GCSE and equivalent qualifications that it can be very difficult to identify exactly which qualification variant your school is offering. In my experience, data managers should team up with their exam officers to match in-house assessments to external exam qualifications.
This is from Capita’s KS4 Resources Mini Guide:
There are several hundred possible courses that could potentially be offered by a school, and every course must be mapped accurately for the results to calculate correctly. Many schools may only collect one result for English or Science even though students may be taking two or more examinations in these subjects. For every examination that a student is taking, there must be an equivalent aspect mapped. Special care must be taken when mapping to vocational qualifications, and where fine grades are in use.
Photography was missing from the list of GCSE subjects in earlier version of the KS4 resources, but is included from Spring 2016.
The KS2 Fine Level
The most important part of the P8 calculation is the KS2 starting point, which the DfE refer to as the KS2 Average Fine Level. This is a new measure and is not the same as the average points score. If you attempt to use the average point score you will get very inaccurate results.
It is surprisingly difficult to get hold of the KS2 Average Fine Level for each pupil. You can calculate it for yourself, but you need to know the pupils’ raw scores from the KS2 papers they took (unlikely). Or you can use Ben Fuller’s excellent Excel spreadsheets (here’s a link to the 2015 version). In the school I’ve visited, the best and easiest source of the KS2 Fine Level data is FFT Aspire (export the data for all your cohorts into an excel file) although I have heard of schools getting the data from NCA Tools, Key To Success and RAISEOnline.
Make sure you identify the right value. The KS2 Average Fine Level will be in the range 3.1 to 5.9 for most pupils. It needs to be expressed to one decimal place. Meanwhile the KS2 Average Fine Points Score will be in the range 21 to 33 points (the points scores of an old level 3 and level 5).
Tip: If you can only obtain the KS2 Average Fine Point Score you can calculate the KS2 Average Fine level from it by dividing by 6.
Note that when you export your data into an XML Spreadsheet file (which you have to do before you can import it into SIMS), Excel actually exports the original, long-form number, even if you have previously rounded the number. I use a combination of the Excel’s ROUND formula (to round to one decimal place) and the use the TRUNC formula to trim off the invisible (but still exported) decimal places. The truncated version of the number is the one that I actually import into SIMS. See the comments section below where the LEFT formula is used and recommended
Import the KS2 Data Into SIMS
Capita provides special aspects to hold the KS2 Average Fine level and KS2 Fine Points Score. If the KS4 Resources are to work correctly you will need to populate these two aspects/resultset combinations (result set name in brackets):
- KS2: Average Fine Point Score (Key Stage 2 Validated Results)
- KS2: Average Fine Level (Key Stage 2 Validated Results)
If you also want the levels of progress calculations to work you’ll also need to populate these two aspects:
- EN KS2: English Test Lev (Key Stage 2 Validated Results)
- MA KS2: Maths Test Level (Final) (Key Stage 2 Validated Results)
Note that you cannot use the mapping tool to do this. Use Routines | Data In | Import from Spreadsheet or cut and paste from one marksheet column to another.
Time to Test
Load up your new KS4 marksheet and press calculate. Be patient, it can be slow. As soon as the marksheet has calculated, save it. Remember that although loading, calculating and saving the data takes a long time, you only need to press the calculate button once each term, so 5-10 minutes isn’t long to wait. Now is a good time to check that all you data is present and correct. Look for missing data for individual pupils and look for analysis columns that haven’t calculated – investigate why the columns have not calculated looking particularly for missing KS2 fine levels.
If your marksheet is working properly here are some screenshots of the kind of analysis columns you’ll now have access to:
Well done! In part 3 we’ll investigate all the data analysis possibilities that the new marksheet provides.
still can’t get rid of those pesky decimals… rounding and trunc and they are still there…
It’s funny – at some schools I have this problem but at other schools I don’t. Maybe someone who reads this blog has the answer?
I also have the same problem with the TRUNC function not working, can anyone help?
We have just discovered if you use the =LEFT(Number,3) this works!
Well done and thanks for sharing!
Will try that! Ages ago, in some of our local support team documents, they stated that you had to change the date of the entry of these values to approximately the time that they would have been given,, eg July 2011 for current year 11s. Is this so critical do you know?
DH
Hi Deb,
I think that was back in the days before result sets. The only time I ever change the result entry date is when calculating reading agesome Otherwise it’s not used.
David
Thanks David, it was last summer though,, not that long ago! The documentation said that the SIMS report wouldn’t work if the result date was not set to the date of the actual testing period.
I won’t worry too much then, I will just put the result set as KS2 validated result,, which is the other thing we were told to do!
Deb
Hi Deb,
I think that was specific to the School Report rather than the new KS4 resources. I was looking at the Assessment information that forms part of the School Report and my first impression was that the KS4 resources would be more useful than the simple A8/P8 data reported in the school report. Let me know what you think!
David.
Hi David
well so far it looks encouraging… we had a trial of 4Matrix and the figures for P8 and Att8 from that matched what i got out of the SIMS KS4 analysis for year 10 which was encouraging to say the least. I didn’t ever set up the School Report for P8 as the estimates were already out of date and the baseline data used wasn’t fine levels, I seem to recall, and we were happy with the output we got from a template set up according to your post 2014 post.
Current year 10 data in KS4 SIMS and in 4Matrix uses 2017 point values SO P8 values are very different from previous .
Year 11 data was complicated as some of our students do Science A or B in year 10 and so there is a result that needed to be included with the rest of the predictions. That took a bit of resolution but I think it’s ok now.
I really like the report output, the mapping is fairly straightforward as long as you do it methodically and you don’t need to know Qan numbers ( good for year 10 as I don’t yet know the new Qans for specs for English and Maths or History for next year for example) .
I have yet to examine the output in detail for year 11 but P8 is lower than the value our basic P8 calculations generated and I am not sure why. Maybe the dreaded 2dp issue is at work yet again but as these calculations are both based on 2015 estimates and the same baseline KS2 figures, I would have thought they would be more similar– 0.04 difference. I am about to see if I can get the data to 1dp and see the change!
I like the output, it is clear and each sheet is easy to compare with the previous data collection . I like the National values comparisons but don’t like them being 2014 values! One sheet without any formatting issues and once the calculations are done behind the scenes, it runs easily. I don’t like the fact that anyone can run the reports without knowing whether new data or mapping has been done. But if new data is always number crunched straight away, shouldn’t be too much of an issue. I think the mapping is easier.
None of these systems are ever transparent to set up and I would hate to have to explain the set up to someone else! . I still like your P8 calculations the best. That also took a while to set up but I understood the whole thing so much better at the end of it and now can use it to check other reports.However the KS4 analysis gives lots of other PI values and the new indicators too.
there,, you did ask my thoughts!
Deb
Thanks Deb,
I hadn’t realised that the national comparisons on the Excel report were based on 2014 rather than 2015 results. I will try and find out when the 2015 averages will be included.
Also interesting is that the points change for GCSE equivalence in 2017 (where, for example, a C grade will be demoted from 5 points to 4 points) is making such a big impact. Many people feared that it unduly penalised schools with lots of pupils on C, D and E as these grades had a one point drop in value.
We shall see!
David.
I can’t see KS2 Average Fine Level as an actual column heading in FFT Aspire – only KS2RWMFineLevel – is that effectively the same thing for current year 11??
From memory yes that’s the one. Is it to two decimal places?
The KS2RWMFinePoints is 4 decimal places! (that’s the double figure one eg 26.3077). I’ve rounded and pasted as value etc to get to 2dp….And divided by 6 rounded to 1dp to get KS2RWMFineLevel (eg 4.4), Have I got everything in the right place and order?
Yes that’s exactly right.
for the 2017 exam cohort DFE October 2016 P8 update says use KS2 reading and KS maths test scores
On Aspire should i now ignore the column “KS2RWMFineLevel” and import into sims the average of “KS2ReadingFineLevel” and “KS2MathsFineLevel”
cheers
Chris
Hi Chris, I’m not up to date with FFT Aspire but my understanding is that writing is no longer part of the average so yes, that’s what I’d do.
Hi, its definitely just the KS2 reading and KS2 Maths from now on so FFT KS2RWMFineLevel is out. However what im struggling with is order of the calculation – to calculate Average FGL do I average the reading and maths point scores then divide total by six OR divide the reading and maths point score each by six to get individual reading and maths FGL then average. This does produce quite different results and does move some pupils between prior attainment groups. Anybody got any ideas.
Andy
I think there is sample calc in the latest Progress 8 doc from gov.uk. you get a different number whichever way or method you use in most cases but its another way of tracking groups of students. No one really knows what value they will end up with for P8 next summer!