ICTLady Posted June 3, 2014 Posted June 3, 2014 Does anyone use this? User Guide and resources page for 16-18 Schools/Colleges - The Department for Education Just looking at extracting the data from SIMS to feed into the report and would love to hear from anyone who has done it before I muddle through
gazskeltz Posted June 4, 2014 Posted June 4, 2014 Hi there! I have (albeit with an earlier version) reverse engineered this. Because it is designed to work out VA from results vs KS4 PI, I reversed it to calculate Estimated Grades from PI... It was very tricky to do, but the first step is to unhide all the sheets in the spreadhseet. It can also be used to calculate VA based on Predictions rather than results. I don't pretend to know how it works (Have a look at the Coefficients page)! but it is possible to see the logic it uses as all the formulas are visible.... Good Luck! Regards Gary
jimbojames136 Posted June 4, 2014 Posted June 4, 2014 We use this, and has been a very effective tool to show staff progress for their subject. We update it each time we collect data from staff, to get a "where we are now picture", but this is a time consuming process. I have also looked at the hidden sheets and formula's but there are some complex calculations that I couldn't get to the end of. I did however add in some extra fields to aid in group analysis.
MattMitchell Posted June 5, 2014 Posted June 5, 2014 We use the coefficients from it to track value-added for each data collection. You could actually work out the value-added estimate and difference for a given assessment in SIMS - it's a polynomial calculation, based on KS4 average points per entry (including BTECs, etc): x = KS4 APS estimate (i.e. the value-added 0 point) = c0 + c1 * x + c2 * (x * x) + c3 * (x * x * x) + c4 * (x * x * x * x) (where c0, c1, ... are the coefficients) This figure is subtracted from the assessment value, to arrive at the contribution for that student and grade. The catches that come up are: - the estimated value-added is always capped at the highest possible score for a given qualification type, so that (for example) this won't be higher than A* for an A2 qualification - points are re-based, so that, for example, instead of runnning 0, 150, 180, 210, etc for A2 points, they are converted to be 0, 30, 60, 90, ... This is done so that a failing grade is one grade width below the lowest pass mark - the big catch, is that the value-added for a given subject or qualification type is then "shrunk" by applying the matrix formulae you see in the hidden sheets. Essentially, the fewer students you have for a given course, the more the average value-added is "shrunk" towards zero. A negative figure will always stay negative, and a positive remains positive. I can't think of a straightforwards way of doing this within SIMS, but you could do it in Excel (or SQL server/access/etc) We use the average value-added for interim tracking, as it still conveys the right qualitative message: if student X's value-added for subject Y is lower than you'd like it to be, increasing it will always be a good thing. Also, it's easier to explain the meaning of an average value-added when it agrees with the average of the numbers on a tracking sheet. I've looked at sharing scatter graphs at each data collection, but explaining how to interpret these to teaching staff is quite a challenging task!
LosOjos Posted June 5, 2014 Author Posted June 5, 2014 I use it and added a couple of sheets to help. I created a report in SIMS that pulls out all the results for a given Year/Resultset/Date Range, with aspect name, student ID and grade. Next, I compiled a list of all the aspects we use and found their subject QANs, putting them in to another sheet on the ready reckoner. Finally, another new sheet on the ready reckoner has been set up so I can copy/paste my SIMS report in to it, then it looks up the aspect name to find a QAN, which is in turn looked up against the VA sheet that contains subject codes. This can then be copy/pasted in to the "Input Data" sheet and job done! Took some time to get the right workflow, but now I can update it in around 10 minutes. Very useful!
ICTLady Posted June 5, 2014 Posted June 5, 2014 Wow, interesting stuff @MattMitchell - I am going to need several coffees I think before I attempt to understand these calculations! It does look useful though once I can get to grips with it. Can I ask the most basic of questions please? (I feel a bit silly asking this after the high-level data work above ). The User Guide describes a painstaking process by which every student's prior attainment is entered manually using the many and various expanding columns for their KS4 results, and then every L3 prediction or result is also entered manually using the qualification look-up tool. On the assumption that I am not going to do things this way (!), can I ask how you get your data into the tool and how long it takes? EDITED because I'm a numpty and got the KS4 attainment bit wrong. Can I ignore the PA Calc page if I have a KS4 APS for each student? So then for the Input Data sheet I enter the Qualification and Subject code, the student's forename, surname and gender, their KS4 APS. Then a number of re-based points based on their grade or predicted grade? Do I need to carry out a look-up in my own sheet to get the re-based points, or does the reckoner do it for you? Can't see any reference to that anywhere in the user guide.
ICTLady Posted June 5, 2014 Posted June 5, 2014 Thanks @Losojos - cross posted with you! I think I have that side of things covered, or just about. I have a SIMS report based on Assessment Results, which pulls out all of the predictions. I am thinking if I order them by subject, then I can look up the subject codes one at a time.
jimbojames136 Posted June 6, 2014 Posted June 6, 2014 @LosOjos I'd be interested to see the additions you have made as that would save me a lot of time when entering the data next year.
mark80 Posted June 6, 2014 Posted June 6, 2014 I have done the same as @MattMitchell, pulling apart readyreacknors is part of the Data Managers regular diet
MattMitchell Posted June 6, 2014 Posted June 6, 2014 @ICTLady the KS4 calculation sheet is just there to calculate the points score from individual qualifications, but doesn't have to be used for the rest of the exercise. I calculate ours from our exam results output (export results from the "performance tables" report - detailed style), and manually where a student has joined us from elsewhere. You can just enter your KS4 APS right in with the KS5 data, you don't have to use the KS4 sheet if you don't want. Don't forget to include all BTECs (with full weightings), as these *are* used to calculate this KS4 APS figure at present. If you do have the time (and inclination) to calculate value-added on your assessment marksheets, you could use this figure in performance analysis/discover to calculate an average within SIMS. Obviously, this won't be the full value-added figure, as it doesn't take the shrinkage related to cohort sizes into account.
LosOjos Posted June 6, 2014 Author Posted June 6, 2014 @jimbojames136 - if I get time later today I'll put a copy on my Dropbox with a bit of annotation for you (with dummy data of course!)
ICTLady Posted June 6, 2014 Posted June 6, 2014 @LosOjos any chance I could see that too please? Thanks all for your help. I have now managed to load in our Y12 predictions and it is starting to make a lot more sense
LosOjos Posted June 6, 2014 Author Posted June 6, 2014 @ICTLady - no problem at all, I'll post the link up here. Forgot to mention the KS4 APS calculation; as we use ALPS, I create another lookup table from that so that we are comparing like with like. However, due to the nature of my myriad of lookups, you can calculate your APS however you like - just drop the figures in to the APS sheet when they're ready
ICTLady Posted June 6, 2014 Posted June 6, 2014 Ah, so do you use the ALPS APS figure? As I thought that included GCSEs only and excluded L2 BTECs and the like. We also use ALPS so we have those APS figures for our sixthformers who have joined us from other schools, but we don't have their full APS including BTECs and other vocational qualifications.
LosOjos Posted June 6, 2014 Author Posted June 6, 2014 Yeah we do, and you're right it doesn't count BTECs but the school of thought from our SLT was that it would be better to compare like with like... for students who we don't have KS4 info on, we assume an APS of 40/C. Not ideal, but it gets the job done!
LosOjos Posted July 1, 2014 Author Posted July 1, 2014 Just a follow up to say I haven't forgotten about you, just been pulled down by an under current of end of year reports! Will post up my sheets soon for you to play with
Recommended Posts