Using the cohort distribution spreadsheet

Using the cohort distribution spreadsheet

This article is for English schools, who would normally use KS2 as their baseline score. 

In 2023/24, due to disruption during the Covid-19 pandemic, the Year 9 and Year 10 year groups have no KS2 Scaled Scores to use as a prior attainment. Prior attainment scores are fundamental to Alps analysis as they provide the baseline from which to measure your students' progress. More information can be found in this article: https://support.alps.education/portal/en/kb/articles/alternative-prior-attainment-baselines-for-alps-connect-at-ks4-england-only

If you do not have alternative scores, such as CAT4 scores, we have created a spreadsheet to help you to:
  1. Estimate the distribution of your students in:
    1. High/Mid/Low prior attainment categories
    2. Each Alps band
  2. Assign bands and estimated prior attainment scores to your students, based on these distributions and a student ranking.
 

Using the spreadsheet

You can download the spreadsheet from the attachment section at the bottom of this article.

Step 1 - export from Connect Data

We have designed the spreadsheet to work from exports from the Students page in Connect Data. You will need information from three cohorts:
  1. Year 11 in 2021/22
  2. Year 11 in 2022/23
  3. Year 10 in 2022/23
To export the information, please login to Connect and go to Connect Data. From there, find each Students page for the relevant year groups and use the 'Export' button to export to Excel or CSV.


From there you can open the file and copy and paste into the relevant 'Data Entry' tab of the Cohort Distribution spreadsheet.


Step 2 - estimate your High/Mid/Low distribution

Once you have populated the data entry tabs on the spreadsheet, the pink cells will populate with your high, middle and low prior attainment distributions.


You can now populate the green cells in the 'My estimation of percentage' column with your estimate of students in the high, middle and low prior attainment categories for your incoming year group. This can be informed by your previous cohort profiles as shown in the table, and any additional information specific to your school, such as a change of intake.

In the example below I have estimated my HML percentages as 25, 40 and 35. My total number of students is 240, and entering this allows calculation of the number of students expected in each HML category.



Step 3 - estimate the number of students in each Alps band

Once you are happy with your HML categories, you can look in more depth at the percentage and number of students expected in each Alps band. This will help you to assign bands to your ranked students.

The pink cells have populated automatically, based on your previous cohorts. The right hand columns will reflect the total student number and HML table from Step 2, and as in Step 1, the green 'My Estimation Percentage' column is for you to populate.


Please note - as we have rebanded our KS4 analysis for the 2023/24 Academic Year, the bands used are different to the 2022/23 bands you will see in Connect. Therefore your current student distribution will look different to that in Connect, but given the national differences between percentages of students in each band, it is most relevant to use the new bands for all cohorts.

In the example below I have filled in my estimated percentage for each band, and the number of students in each band has been calculated:



Step 4 - Rank your students and assign Alps bands

On the second table of the spreadsheet, you will find a reference table, containing your estimated percentages and student numbers. The right hand column will populate as you complete the second table. It is there to help you to check the number of students actually assigned to a band.

These are intended to be a useful guide and reference point only. You may find as you begin to assign bands, that you wish to shift the numbers and percentages slightly.

The second table, 'My Students', is for you to populate with your student list and their ranking. Once you have the students ranked, you can assign an Alps band by populating the highlighted column. This will automatically assign a KS2 score to place them into that band. 

For ease of import, please ensure you include the Student ID you wish to use in Connect.



Step 5 - import your data

Now that you have a prior attainment score for each of your students, you can import these into Connect Data. 

If you are adding them into a year group, please follow this article: Editing student data
If you are creating a year group from scratch, please refer to this: Setting up your Year Group