What is the value of the correlation coefficient for your chosen data? What does it mean?

DATA ANALYSIS FOR BUSINESS

 

Assessment 2 – PORTFOLIO

This assessment is worth 70% of the total module marks.

 

 

Complete ALL FOUR PARTS of this assessment. Each part is worth 25% of the total marks.

 

Your answers must be presented on this template document. Your answers should be contained in the spaces provided.

 

You are advised to complete this portfolio as you progress through the module.

It is strongly recommended that you do not leave all the work until the last minute. For example, part 1 is based on the first three weeks of lectures and seminars, and it is recommended that you complete it by the end of week 4.

There will be time set aside in some of your seminars to work on your portfolio.

 

 

 

 

Student ID number:

PART 1: Presentation and Display of Survey Data

1 (a) Averages in the news

Find an example of a mainstream media report that includes the word “average” in the headline. The report should be on a subject broadly related to your degree title.

Provide the link to the article here:

 

 

 

Paste a screenshot of the headline and relevant parts of the report mentioning the average below:

 

 

 

Answer the following questions. If you cannot find an answer for a question, explain what effect this might have on the information in the article.

 

Which kind of average has been used?

 

 

What was the size of the sample used to find the average?

 

 

What is the source of the data? How was the data collected?

 

 

Do you think the average was chosen to give valid and reliable results, or to attract the most readers?

 

 

 

 

1 (b) Charts in the news

Find an example of what you consider to be a GOOD chart about the Covid-19 pandemic, and an example of a BAD one.

Include the charts on this page, together with links to the source.

For each chart, give THREE reasons why you consider it to be good or bad.

“GOOD” CHART

 

LINK

 

THREE REASONS YOU THINK IT IS “GOOD”

 

1.

 

 

2.

 

 

3.

 

“BAD” CHART

 

LINK

 

THREE REASONS YOU THINK IT IS “BAD”

 

1.

 

 

2.

 

 

3.

 

 

 

 

PART 2: Correlation and Regression

2 (a) Correlation and causation

Using examples, outline the differences between correlation and causation.

 

 

2 (b) Correlation and regression

For this part of your portfolio, you are required to find some data from Statista.com (email address and password required off campus), a leading provider of market and consumer data. You should find some data broadly related to your degree title. (If you do not have access to Statista, your lecturer will advise you on where to find some data).

For the correlation and regression analysis, you will need TWO columns of data so that you can have X and Y variables. You made need to download two different files and then combine them in Excel. You need two columns of the same length, with at least 10 pairs of (X, Y) data.

 

Write down the titles of the data file(s) you have chosen to use:

 

 

 

 

 

Write down which variable you have chosen as X and which as Y, and give a reason for your choice.

 

 

 

 

 

Use Excel to create a fully-formatted scatter chart of your data, showing the linear regression line, R-squared value, and the equation of the regression line.

 

 

 

 

 

 

 

 

What is the value of the correlation coefficient for your chosen data? What does it mean?

 

 

 

Using the values in the regression equation, explain what your analysis shows.

 

 

 

Explain the concepts of interpolation, extrapolation, validity and reliability when using a regression model for forecasting.

INTERPOLATION:

 

 

 

 

EXTRAPOLATION:

 

 

 

 

VALIDITY:

 

 

 

 

RELIABILITY:

 

 

 

PART 3: Index numbers and Percentages

3 (a) Excel functions and percentages

 

The table below shows selected price details for unleaded petrol and diesel published by the Department for Business, Energy and Industrial Strategy. (https://www.gov.uk/government/statistics/weekly-road-fuel-prices)

 

  A B C D E
1   Petrol price (pence/litre) Diesel price (pence/liter) Duty rate (pence/litre) VAT

(% rate)
2 November 2018 125.8 135.4 57.95 20
3 November 2019 125.3 130.1 57.95 20
4 November 2020 112.4 117.4 57.95 20
5 November 2021 146.9 150.1 57.95 20

 

Using the example spreadsheet above, explain how the Excel VLOOKUP function can be used to find items in an Excel table.

 

 

The price of petrol and diesel includes fuel duty (levied at a flat rate of 57.95p per litre). VAT at 20% is then charged on both the product price and the fuel duty. Complete columns C and D, and rows 6 and 7 in the table below, and give an explanation of your calculations:

 

  A B C D
1   Petrol price (pence/litre) Petrol Price before VAT

(pence/litre)

 Petrol Price before duty and VAT

(pence/litre)
2 November 2018 125.8    
3 November 2019 125.3    
4 November 2020 112.4    
5 November 2021 146.9    
6 % Change, 2018 to 2021      
7 % Change, 2020 to 2021      

 

Explanation:

3 (b) Rebasing and Descriptive Statistics

 

The table below shows the average price of petrol and diesel in November from 2012 to 2021. Fill in all the missing values and give an explanation of your method.

 

Year  Petrol Price (pence/litre) Petrol Price Index 2012=100 Diesel Price (pence/litre) Diesel Price Index 2012=100
2012

2013

2014

2015

2016

2017

2018

2019

2020

2021

 134.4

 

122.3

107.3

 

120.1

125.8

125.3

112.4

146.9

 100.0

96.7

 

 

85.3

 

 141.1

137.7

127.2

110.3

117.4

 

135.4

130.1

117.4

150.1

 100.0

 

 

 

 

87.7

 

Explanation:

 

Use the information in the table above, together with any descriptive statistics you may calculate, to make FIVE relevant comments and/or comparisons about Petrol and Diesel prices since 2012.

1.

 

 

2.

 

 

3.

 

 

4.

 

 

5.

 

 

 

 

 

PART 4: Time Series Analysis

The table below shows information about visits to the UK by overseas residents, and visits abroad by UK residents. The data is also available as an Excel file on Blackboard.

 

  Number of visitors
to UK from overseas		 Spending by overseas
visitors to UK		 Number of UK
residents’ visits abroad
  Thousands £ Millions Thousands
Q2 2016 10138 6533 21787
Q3 2016 10892 8235 27548
Q4 2016 9900 6200 17450
Q1 2017 8847 5075 15934
Q2 2017 11012 7153 23744
Q3 2017 11899 10088 28699
Q4 2017 9322 6080 18865
Q1 2018 8547 5194 16592
Q2 2018 10521 6939 24646
Q3 2018 11536 8401 29923
Q4 2018 9679 5974 19409
Q1 2019 8332 4805 18159
Q2 2019 10364 6896 25760
Q3 2019 11864 9193 30000
Q4 2019 10297 7555 19167
Q1 2020 6994 4344 13891
Q2 2020 398 218 939
Q3 2020 2322 1037 6191
Q4 2020 1386 611 2806
Q1 2021 195 248 774
Q2 2021 277 386 1000

Source: https://www.ons.gov.uk/

 

Select some of the data above and complete a time series analysis.

 

You do not need to analyse all of the data.

 

Clearly state and interpret all seasonal effects that you have calculated.

 

Use your model to make relevant predictions.

Use calculator or Excel (see your seminar on time series analysis).

 

Present your results on the next pages.

 

 

 

 

 

 

 

 

Assessed intended learning outcomes

Knowledge and Understanding

1.    Recognise, recall and apply specific quantitative facts and techniques/tools.

2.     Select an appropriate technique for a business-related problem.

3.    Recognise common sources of bias and limits to accuracy in quantitative results.

4.    Introduce academic reading and writing in the context of mathematical modelling and data analysis.

5.    Critique articles and news stories that include statistical and quantitative information, recognising that variability is natural, predictable and quantifiable, and that association is not the same as causation.

Practical, Professional or Subject Specific Skills

1.    Use computer software to carry out quantitative analysis and present results.

2.    Select or collect data for use in quantitative problem-solving or descriptive analysis.

Transferable Skills and other Attributes

1.    Select, organise and present relevant information clearly and logically.

2.    Discuss and interpret results and present a well-structured report (written and verbal) in support of findings.

3.    Present data in a variety of formats.

4.    Formulate, derive and assess quantitative modelling approaches to solving problems.

 
Module Aims

1. Develop quantitative literacy in students, including the ability to make numbers meaningful, focussing on conceptual understanding and recognising the demands for dealing with big data.

2. Provide students with a sound grasp of the fundamental concepts needed to use and interpret quantitative methods which are used to analyse business data and hence aid informed decision-making.

3. Develop the concept of mathematical and statistical modelling in business, finance and economics, and appreciate that modelling can be more subjective than commonly assumed.

4. Prepare students for entry to more advanced modules dealing with quantitative analysis in business, finance and economics.

5. Develop skills in the use of spreadsheet software and calculators.