Use of Maths Summer Task Part 1

Look at data collected on the Trips to UK spreadsheet.

It gives the number of passengers entering the UK every month from Jan 2011 to April 2013 and where they are from. It also gives the number of passengers leaving the UK every month from Jan 2011 to April 2013 and where they are going. It also shows the statistics of why people enter or leave the UK.

What do the following stand for?

SA

NSA

EU27

A12

EU15

Who has collected the data?

(Hint try googling the terms)

In order to investigate this data you must have an idea you are trying to prove or disprove. This is your HYPOTHESIS.

Examples might be

  • Over the last two years more people have entered the UK than left
  • Throughout the year the number of people entering and leaving the UK every month fluctuates, but there is a correlation between the two groups
  • The proportion of people visiting the UK who come for a holiday remains constant throughout the year

You are going to look at how you use Excel to produce some statistics to help investigate these hypotheses and how you can place the charts in your coursework.

1. First you are going to look at people visiting and leaving the UK in 2011 and 2012. It’s a good idea to produce a table that just contains the information you are going to look at

  • Open a new page in Excel and copy the table headings below

Total number of overseas residents visiting UK 2011 / Total number of UK residents leaving UK 2011 / Net balance 2011 / Total number of overseas residents visiting UK 2012 / Total number of UK residents leaving UK 2012 / Net balance 2012
Jan
Feb
Mar
Apr
May
Jun
Jul
Aug
Sep
Oct
Nov
Dec

All figures in thousands

  • Fill columns 1, 2, 4 and 5 from the spread-sheet (use the NSA totals) and work out columns 3 and 6. Paste the table below

Use the =AVERAGE( and =STDEV.P( to calculate the means and standard deviations of your data and fill in the table below. Give your answers to 3 significant places.

Total number of overseas residents visiting UK 2011 / Total number of UK residents leaving UK 2011 / Net balance
2011 / Total number of overseas residents visiting UK 2012 / Total number of UK residents leaving UK 2012 / Net balance
2012
Mean
Standard deviation

What can you say about the figures for 2011 and 2012?

2. You are now going to produce a graph that displays the information in your table. A useful graph when comparing discrete data is a clustered column chart

  • Select the 2011 net balance column (including the title) and then using the Ctrl key also select the 2012 net balance column (including the title)
  • Insert a clustered column chart (click on the column icon then hover over the pictures)
  • With graph selected go into Chart Layout to put axes labels and a heading.
  • Paste your chart below

What can you say about the net balance figures for 2011 and 2012?

3. Now you are going to investigate whether there is a correlation between UK residents leaving the country and overseas residents entering the country in 2011

  • On a scatterplot plot Total number of overseas residents visiting UK (x) against Total number of UK residents leaving UK (y)
  • You need to label the axes and give it a title
  • The axes will default to starting from (0, 0), you need to change the axes so that the x axis goes from 2000 to 4000 and the y axis from 2000 to 8000. (You do this by double clicking on a number on the axes)
  • Now right click a point and add a linear trendline, display equation on chart and display R2 value on chart
  • The correlation coefficient r is just the square root of the R2 value

Paste your chart below

Repeat the above process for 2012.

Paste your graph below and complete the table

Year / Correlation coefficient between Total number of overseas residents visiting UK (x) against Total number of UK residents leaving UK (y)
2011
2012

What conclusions can you make from the table above?