Just take a look around this blog and you will find a little bit of work I have done on Excel slicers: introduced in Excel 2010. Two days ago I came across some fascinating information on the cost of supporting teams in the English and Scottish Football Leagues and a women’s league that I have used to prepare a series of Pivot Tables and Slicers.

I also decided to prepare a basic dashboard for this information by using the Camera Tool rather than just leaving the Pivot Charts in their native format.

I used an Excel Table to organise the data, by the way and rearranged the data a little bit to optimise the look of the worksheet and to make it a little bit easier to work with.

I cannot make the spreadsheet available because it contains information that is not mine. You can find the information for yourself here, though.

The data series provided by the BBC link are:

  • Cheapest season ticket
  • Most expensive season ticket
  • Cheapest match-day ticket
  • Most expensive match-day ticket
  • Cheapest day out
  • Programme
  • Tea
  • Pie

And the leagues included are:

  • Premier League
  • Championship
  • League One
  • League Two
  • Blue Square Bet Premier
  • Scottish Premier League
  • Scottish First Division
  • Scottish Second Division
  • Scottish Third Division
  •  Women’s Super League

Here is the dashboard:

 

Clearly, this is just one aspect of the entire database: just three of the eight data series available. Never mind, download and work with the data and see the rest for yourself.

One other aspect of the analysis that I did was to prepare a correlation matrix:

 

Take a look at some of the correlation coefficients, I have set up two conditional forecasting rules for you:

  • red font greater than r = 70%
  • green font less than r = 50%

Most of the correlations coefficients are simple to understand:

  • high correlation between the most expensive season ticket and the most expensive match day ticket
  • very high correlation between the  cheapest match day ticket and the most cheapest day out
  • there’s even a reasonably high level of correlation between the price of a tea and the price of a pie!

Finally, I even carried out a regression analysis of the data set: in this case I set up the regression in this way:

Y = cost of a pie

X = all other variables

Here is the regression output for you to analyse:

 

The conclusion from this regression analysis is that this overarching model is too much and it needs to be redesigned … go to! See  if you can find a model that helps football fans to predict the price of a pie!

Well, there you are, some insights into the data relating to the cost of supporting a football team in England and Scotland, including a women’s league!

 

Duncan Williamson

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: