Note: as of February 2012 I published a much simpler method for calculating expected progress KS2 – KS4. I’ll keep this post available because it does show several techniques that are useful elsewhere.
Recently several schools have approached me asking if it’s possible to identify the pupils who are expected to make 3 whole levels of progress between KS2 and KS4. It’s not easy within SIMS Assessment Manager, but it is possible, as I hope this example shows.
Some time ago, the DfE produced this table to help schools identify pupils who had made ‘expected’ progress:
| GCSE ‘E’ | GCSE ‘D’ | GCSE ‘C’ | GCSE ‘B’ | GCSE ‘A’ | GCSE ‘A*’ | |
| Level 2 | Yes | Yes | Yes | Yes | Yes | Yes |
| Level 3 | No | Yes | Yes | Yes | Yes | Yes |
| Level 4 | No | No | Yes | Yes | Yes | Yes |
| Level 5 | No | No | No | Yes | Yes | Yes |
(See latest DfE guidance document (new for 2011) for the full table)
Converting the GCSE Grades into points we have a ‘threshold table’ like this:
| KS2 Level | GCSE Grade Threshold | GCSE Points |
| 2 | E | 28 |
| 3 | D | 34 |
| 4 | C | 40 |
| 5 | B | 46 |
From the table above we can see that anyone with a level 3 at GCSE needs at least 34 GCSE points to be classified as having achieved 3 levels progress.
Now we can convert this table into some Assessment Manager formulae:
- Create a new gradeset called ‘KS2-KS4 Thresholds’
- Add grades as follows:
- Create an aspect called ‘KS2-KS4 Thresholds’. Make it a grade aspect and allocate the ‘KS2-KS4 Thresholds’ gradeset created above.
- Create a new template
- Add the aspect that holds the KS2 results you want to analyse. Make sure it uses a gradeset that uses the official national curriculum points value for each level.
- Add a column that contains the GCSE results you wish to compare it with. Make the aspect uses a gradeset that uses the official GCSE points value for each grade.
- Next, use a new ‘Column for data entry using a formula’ and use an EQUALS formula to copy the KS2 result into the ‘KS2-KS4 Threshold’ column using an equals formula.
- Now we have the KS2 result in a gradeset that converts the level into the threshold GCSE points score. In other words, we can ‘compare like with like’
- Add a column that calculates the VALUE of the ‘KS2-KS4 Threshold’ column. This will display the equivalent GCSE points value of the KS2 grade.
- Add a column that calculates the VALUE of the GCSE result. This will display the GCSE points value of the actual GCSE result.
- All we need to do now is check the actual GCSE points score, and make sure it’s at least the same score as the threshold score, or more…
- Create a final ‘if then else’ column that compares the value of the GCSE result with the value KS2-KS4 Threshold column. If the GCSE value is greater than the threshold value, then good, we have achieved three levels of progress.
If you need help, feel free to contact me.
Got a better one
it’s a little more complicated than that
give me a call
JH has my number
Dave
If all they want to do is identify the pupils then wouldn’t it be easier – and prettier – to use a tracking grid set out like the DfE KS2-KS4 table? This doesn’t allow for as much later analysis as the method shown but would give them a good visual idea of the state of play.
Tracking grids are certainly easier but it’s useful to be able to see a colour coded Yes or No from within a marksheet. Tracking grids are great, but you con’t save the results of a tracking grid back to an aspect…
Query: In paragraph 4 – level 3 needs 34 points to gain 4 levels progress, is that 4 levels or 3 levels as per title?
Isn’t it possible to do GCSE grade MINUS KS2 level and get a difference in values from the underlying points? Then wrap an “If > 12 = Green” around it.
You’re right! typo now corrected!
The problem with the 12 points is that GCSE grades is that they don’t integrate with NC points. For example, A GCSE grade D is worth 34 points, but a level 3 NC grade is worth 21 points. So there’s notionally 13 ‘points’ between them. It’s comparing apples with oranges, as they say!
Our targets are based on 3 levels progress and we use BRAG to show B>=Aspire Target + ‘Y’ for On Target, G>=Min Target + ‘Y’ for OT, A<Min Target + 'Y' for OT, R='N' for OT or OT/Assessment not entered (the colours are also shown on reports). This works fine but if the grades are imported then the BRAG colours aren't updated. It's a pain to have to re-calculate 205 individual marksheets prior to running the reports. Is there any way to work around this?
Hi Les, Could you create a single marksheet based on the ‘all year groups’ option. I.e. instead of creating separate marksheets for each class, reg group or year, just create a single marksheet for the whole school?
You might still have to create templates for each subject though.
Generally, adding colours to a marksheet is very laborious and time-consuming. Capita have added a couple of time saving clone options to the nested-if-then-else statements that control the colours, but it still takes too long!
Hi David, thanks for the reply. We do have the marksheets set up and yes they take ages + getting kicked out after completing 8 or 9 makes it a nightmare. The total number of subject templates is 205 and I have no idea if one teacher has imported some grades. I did create a teplate for a single KS3 year group and it took 1.5hrs to load recalculate and save. I can’t set up a template for the whole school as it will flood fill the BRAG colour with red for KS5 & KS4 pupils if they are not taking a course and this would then create havoc with the reports as the subject would think there was a BRAG grade so the KS5 students would have 60+ lines on an interim report. The other problem is that there are also formulas to convert the grades entered for 4 other fields into a report comment (Excellent, Good, Satisfactory or Poor) so I soon exceed the 255 aspect limit. I really just wondered if there was an easy way of identifying any class that had grades imported. I think my only work around to this would be to use the reports I run overnight and prduce a printout of any class with an incorrect BRAG colour and then just recalculate these classes.