Appendix 2 Interactive Target Setting Spreadsheet – Key Stage 2

Following consultations with schools and advisers, the guidance provided by the LA for target setting will this year be based on individual pupil attainment using an interactive spreadsheet. This will allow targets to be set based on each pupil’s prior performance and your own knowledge.

The LA suggested targets are shown for every pupil and the probability tables used to calculate these are included. Any pupil that we do not have results for does not have a target set for them. However it is possible for you to set a target based on your knowledge of the pupil and their previous performance. You can also add/delete pupils who have joined/left your school and set targets for those who have joined the school. Any changes made will affect the overall school targets, therefore it is essential that all pupils have a probability attributed to them. All the pupil data in the spreadsheet is taken from PLASC.

Appearance of the Spreadsheet

You will have received your spreadsheet via AVCOANYCOMMS in mid October. This needs to be copied/saved onto your C: Drive or Desktop. The file name is <DfES No>KS2Targets<Lancs School No>.

The spreadsheet consists of seven worksheets. The tabs at the bottom of the spreadsheet are labelled as follows:

  1. Read Me
  2. Current Pupils
  3. Protected Data
  4. Proforma
  5. Historic Data
  6. Probability Tables
  7. PIVATS Probabilities

Read Me

This worksheet contains brief instructions on how to complete your Key Stage 2 Targets.

Current Pupils

This worksheet should be used to set your school targets. The LA suggested targets are already entered but can be amended if there are any individual pupils whose targets you wish to amend. To print off all the information on this page, from the Excel menu select File, Print and press OK.

Protected Data

This worksheet contains all the same data as the ‘Current Pupils’ worksheet; however this page is protected so the data cannot be altered. This can be used as a reference page or to copy and paste any data that may have been changed incorrectly.

Proforma

This worksheet contains the proforma with the LA recommended targets already completed. This needs to be printed, completed, signed by the Governors, and returned to the Professional Support Teamby Tuesday, 6 December 2005. To print the proforma, from the Excel menu select File, Print and press OK.

Historic Data

This is a summary of targets from previous years. This allows comparisons between your school’s targets, the LA suggested targets and the actual % of pupils reaching level four or above, and level five. It also contains Past Performance Indicators. This sheet also includes Fisher Family Trust (FFT) estimates for your school for reference purposes only. An explanation of the different types is given below:

PA model: based on pupil prior attainment, gender and month of birth. This provides an estimate of what might be expected if pupils were to make progress similar to that made, last year, by similar pupils nationally.

SE model: based on the data in the PA model but also including a range of school context indicators (% FSM, mean level and distribution of intake). This provides an estimate of what might be expected if pupils were to make progress similar to that made last year by similar pupils in similar schools.

TQ model: based on the SE estimate but adjusted to be consistent with the performance, last year, of schools in the ‘top 25%’ of schools nationally in terms of their value added score (schools with the highest KSKS value added).

Probability Tables

This worksheet shows the various tables used by the LA to produce targets for your school. There are two sets of tables, a set based on PIPS scores and a set based on Key Stage 1 results. The suggested targets are based on whichever results produce the highest level. The table shows the average points gained and the corresponding percentage of pupils previously reaching level four or five for each subject. There are separate tables for pupils whose first language is not English.

PIVATS Probabilities

This shows the possible progress that pupils working well below the expected levels may attain using PIVATS/P Scales. This is for reference purposes only and does not influence the calculation of targets in this spreadsheet.

How the suggested LA Targets Have Been Produced

The tables used to produce the LA targets are based on prior attainment in Lancashire over the last three years. The percentages relate to the number of pupils that have reached the level over the last 3 years. The targets are grouped by subject, with a probability percentage of the pupils reaching level 4 and 5 based on past performance at PIPS Year 4 or Key Stage 1.

How to Set Your School Targets

Target setting should be carried out using the ‘Current Pupils’ worksheet.

  • Copy the file from AVCO ANYCOMMS onto your C: Drive or Desktop.
  • Select the ‘Current Pupils’ worksheet as shown below. The main body of this worksheet gives details of pupils in year 5 followed by their PIPS and Key Stage 1 results. The LA’s suggested targets are shown in the colour coded columns in the centre of the worksheet; English is yellow, maths is blue and science is orange.

Explanation of ‘Current Pupils’ worksheet and how to complete:

  1. This shows the number of pupils in the cohort and the number of pupils with targets set. This will automatically update as additional pupils are added or more targets are set.
  1. The targets set by your school are shown here, colour coded by subject. These figures are the overall percentage of pupils expected to reach both level four and level five in each subject. They will automatically update as the data for individual pupils is amended. You do not have to manually update these.
  1. The original LA suggested targets are shown here as the overall percentage of pupils expected to reach both level four and level five in each subject. This allows you to compare with your own targets.
  1. The pupil data is listed here. The targets are set based on either PIPs Year 4 or KS1 results (whichever gives the highest probability is used). If there is any missing data the targets will be set using the data available. If there are no scores available no suggested target will have been set. If there are any pupils listed who no longer attend your school they should be deleted from the list. To do this highlight the row, select Edit, then Delete from the menu. If there are any pupils missing from the list add these pupils at the end. There are two ways of setting targets for these pupils. If you have PIPS and Key Stage 1 results for these pupils you can use the ‘Probability Tables’ worksheet to work out the percentage probability of the pupil reaching each level, or you can predict their levels based on your own knowledge of the pupil. Whichever method you use you need to key in a figure as indicated in 7 below.
  1. The targets set by your school are shown, colour coded, above the respective column. This is so you can always see the targets you have set as you scroll across the worksheet. These will automatically update as you update your targets.
  1. Recalculate button - If a pupil currently has a target but the data is incorrect or there is more data to add you need to delete the current targets before new targets can be calculated using the 'Recalculate' button. If there is no data and therefore no target you can add the results and then click 'Recalculate'.
  1. These columns are the only columns that you need to change, by simply overwriting the figure. The probability shown is the greater of the two probabilities for PIPS Year 4 and Key Stage 1. The probability tables are based on the previous 3 years data. They map pupils’ attainments from PIPS Year 4 and Key Stage 1 to actual results at Key Stage 2, which generate the probabilities. It is recommended that you use values of 0, 25, 50, 75 and 100. Should you need to revert back to the original information please refer to the protected data worksheet. Information from this worksheet can be copied onto the ‘Current pupils’ worksheet if necessary.
  1. Maximise buttons for Excel and the current worksheet.

Returning the Proforma

The LA’s suggested targets are already completed on the Proforma. Please fill in your own targets then print the form which should be signed by a representative of the Governing Body or its sub-committee. Please keep a copy for your records and return the original proforma to the Professional Support Teamby Tuesday, 6 December 2005by Fax to 01772 531525 or by postat the following address:

Professional Support Team

Room A39

PO Box 61

County Hall

PRESTON

PR1 8RJ

If you require any further help in interpreting the spreadsheet or setting your school targets please contact the Professional Support Team Helpdesk on (01772) 531555.