March 2011

The know zone

  • Setting parameters
    Goggles to play conkers, candy floss caution and banning flip-flops are some of the myths attributed to health and safety. In the end, says Richard Bird, there’s no danger in acting responsibly and thoughtfully. More
  • Hotline
    The ASCL hotline is a completely confidential service available to answer members’ questions on issues that arise in school/college. More
  • Pressing numbers
    Sam Ellis unveils a new spreadsheet to help leaders calculate their affordable pupil-teacher ratio. More
  • Lead vocals
    Quotes from Albert Einstein, Marry Browne, Anton Chekhov, Winston Churchill and Alan Autry More
  • A richer mix
    Whole Education brings together almost 30 charities and other bodies whose joint goal is to offer students a broader, deeper learning experience. More
  • Adding value
    Most of us look forward to spring and putting our clocks forward. Unfortunately our biological clocks take a little longer to reset. More
  • Smart choice
    Allison Crompton is headteacher of Middleton Technology School, Rochdale, Greater Manchester, which was singled out by Ofsted as one of 12 outstanding schools which excel against the odds. She was awarded a CBE in the New Year Honours List 2011. More
  • Sticking with CPD?
    With renewed emphasis on sharing good practice around the system – and with budget cuts to make – leaders share their thoughts on how they are ensuring value for money in CPD. More
  • Leaders' surgery
    The antidote to common leadership conundrums... More
  • The challenges ahead
    With the appearance of a Curriculum Review and Education Bill already in 2011, ASCL Council had a packed agenda in February. Not surprisingly, pensions, funding and the English Bac were high on the agenda. More
  • The gap years...
    Steps by the government to dismantle Connexions, abolish the EMA and allow universities to raise tuition fees threaten to lock the middle classes out of university, says Brian Lightman. And it undermines the coalition’s aim to improve social mobility. More
  • Busman's holiday...
    It requires team-working, careful monitoring, effective skills development and strong leadership. So how would Ofsted assess a joint family holiday asks Catherine Szabo. More
Bookmark and Share

Sam Ellis unveils a new spreadsheet to help leaders calculate their affordable pupil-teacher ratio.

Pressing numbers

Joe did not get five A*-C grades nor did he do much for the attendance target. His behaviour seldom entered the exclusion zone but there was no doubt about it, in a class that was p pointing in one direction, Joe tended to point somewhere else and was mostly, in the most affable way imaginable, a total pain in the neck.

He spent a lot of time super-glued to me just to keep him out of colleagues’ hair. One technique I used was to put him on an internet learning site in my office while I got on with the other interruptions of the day.

During one of these episodes I realised Joe was actually a bit of a whizz on a computer. When I tapped into his expertise, everything changed. Joe became the teacher and I became the student.

I learned a lot both about the computer and about managing Joe. The display graphs which proved quite popular at this year’s ASCL regional information conferences can be traced back to things I learned from Joe.

Following demand from members, Malcolm Trobe, ASCL’s policy director, suggested I put the graph spreadsheet on the ASCL website for members to use. I first had to clean out the spaghetti that lay hidden behind the original sheet but finally, on 28 January, two days before the 362nd anniversary of the execution of Charles I according to the Govian historical calendar, the sheet appeared on the site as a free download.

Description of workings

The next section of this article describes how to find the sheet and what it does. Following that there is a section just for enthusiasts that gives greater detail.

At www.ascl.org.uk, log in then select the link towards the foot of ‘current issues’ labelled ‘Funding and curriculum spreadsheets and data collection requests’.

On the next page select ‘Funding and curriculum spreadsheets’. On the next page click the second link down to download the spreadsheet. The password protecting the sheet is PASSWORD. If you need to modify it you can do so.

The DISPLAY sheet shows a graph on the left-hand side which displays the value of pupil-teacher ratio (PTR) you can afford given a level of funding, the average cost of a teacher and the proportion of the revenue budget you can allocate to teacher cost.

The value of the PTR is then used in the right-hand graph to show what teacher contact ratio you would need to work at for any given average class size and where that is in relation to a reference value of teacher contact ratio. The user can type input data into cells that have a blue font on a white background.

(It gets a bit tedious from here on so some readers may wish to leave now.)

There are three other spreadsheets in the workbook. CALCULATIONS takes the input data from the DISPLAY sheet, uses it to generate the points for the graphs and also has the copies of the original graphs which are then copied back to the DISPLAY sheet.

The ‘Notes (Read Me)’ sheet describes the formulae upon which the sheet is based. These are the same relationships I have been writing about in Leader in recent issues.

Because ‘average class size’ is a statistic that can be defined in more than one way I have included that sheet as a fourth spreadsheet, so a user can easily calculate the value from the number of teacher periods allocated to each area of the school in the curriculum plan. This sheet also has a detailed description of the way I am defining average class size and a cautionary note for those readers who may just extract a value from certain types of school timetabling software.

The vertical and horizontal lines on the graphs are plotted from data series linked to the results of the calculations.

If any member has any ideas for improvement on this type of display sheet I would be very interested to hear them. Please email me at sam.ellis@ascl.org.uk

More importantly, if any member is prepared to populate any of the free spreadsheets available from the website with data from their school I would be very interested to receive a confidential copy to assist in lobbying for better funding.

Finally, as with any computer software, please run an independent check on what it appears to be telling you before you go over the top with it.

Joe did not teach me that bit. I learned it the hard way!

  • Sam Ellis is ASCL’s funding specialist

Pressing numbers

LEADING READING