Here’s a way to help your senior leadership team find out about some of the interesting things you can do in Excel. I used Office 2007 for this, but I’m sure you can achieve the same in Office 2003. I find these kind of training examples are sometimes tedious, unless there’s a real purpose. But this example has got a real purpose that your senior managers will like – understanding the relationship between spend and achievement. If you can get them to do this, then I bet you’ll see charts like this popping up at lots of meetings!
Last week, I read a breakdown of local authority education budgets per pupil. (From a parliamentary answer on TheyWorkForYou). It’s a list of all of the Direct School Grants, per pupil, for all of England’s local authorities. (Because you’ll ask: London authorities account for the top 16, with Tower Hamlets top. Leicestershire are bottom)
And I wondered what the link was between spend and league tables. And 5 minutes in Excel gave me the answer:
The chart above compares each individual local authority’s Contextual Value Added score (CVA) to their spend per pupil. The trend line shows that overall, the higher the budget, the greater the CVA score. But, there are a couple of odd outliers in this chart, but you’ll need to follow the instructions below to see who they are!
Although I did it at a national level, I bet your head or deputy head would want to do it for schools in your own local authority!
Here’s my step-by-step guide to how I created the chart above.
Here’s a challenge: Who can be first to add a comment to say which way the trend line goes when you compare “BUDGET” and “5 A*-C”? (And only then will I share the SkyDrive link to my version to download)
And, as a bonus, who can improve these instructions so that you can hover over a dot on the chart, and see the name of the local authority?
Hi Ray. Bit worried about your analysis here. Trying to remember my stats, but if you change the Options for the trend line and add an R-squared value you get 0.2424. Basically, you've plotted a nice line, but it's fit is dodgy. A perfect fit would be 1.0. "There's lies, da..." Neil
I have to admit my knowledge of statistics stopped at what I did in A-Level Economics and Geography, so you're already well ahead of me.
I just looked at the chart, and thought "yes, it looks like CVA goes up when more money is spent", and so clicked on the chart with the trend line to see if the line went in that direction.
However, with your deeper knowledge (sorry, the 'R-squared' but went straight over my head) does that mean that there isn't a positive trend. (And, on the GCSE chart where the line seems to fall, is that also not correct?)
Here's my original worksheet:
And, back the original point, did most people in school SLT's know their way around Excel well enough to produce simple charts like this quickly? (I used this example because I thought it would attract SLT to the idea of comparing data in a chart - and because I wanted to know if there was a link)
There is a definite positive trend on the first chart, but the correlation is weak, so you cannot create strong conclusions on that. (And correlation is not, of course, same as causation.) As for second chart, negative correlation is so weak (R^2=0.0384) that I'd be careful about using it at all.
I'm not a statistician either - little further than you actually (degree Geog and A-level maths), and too long ago.
These tools are powerful and fairly simple, but we also need people to use them with a degree of understanding of what they represent, what confidence can be placed on what they appear to reveal and other factors involved in interpretation. Schools should be able to use them - and secondaries at least should have mathematicians (or others) quite capable of interpreting.
From those BBC statistics it's reassuring to see that there is a definite relationship between attendance and performance... otherwise I think it'd be time to pack up and go home!
The figures relating to cost/pupil are difficult to separate out from factors such as social deprivation and cost of living in each different authority so I would be surprised to see anything significant come from them. BSF will also muddy the figures as authorities running under BSF contracts will be spending vastly greater sums than those which aren't, with very little chance of it leading to proportionately improved results (as much of the increased spend is going to commercial companies rather than to schools themselves)