Reference no: EM132387570
Assignment - Data Mining, Analytics and Visualisation with EXCEL
'Should We Worry About Global Oil Prices?" A Regression Analysis
The Brief: The Longhaul Transport Company wishes to predict the price of petrol based on international crude oil prices. They record average monthly petrol prices and crude oil prices over a 12-month period. It is known that this data is normally distributed.
You will find this data file in the attached file. It is labelled as follows: Global Oil Prices_2_2019_STUDENT.xlsx and is to be used to answer the questions listed here.
Whilst the ability of multiple regression modelling to analyse the relationship between a dependent variable and numerous (multiple) independent variables, makes it the perfect tool for decision making in business, it is beyond the scope of this unit (worth doing a second year Stats unit just for this!). Instead, we will use EXCEL's simple linear regression, which restricts the modelling to using just one independent variable at a time.
NOTE: All relevant Excel output must be copied and pasted into a single Word document for submission.
QUESTION ONE:
(a) In order to investigate if a linear relationship between Petrol Price and Crude Oil Price is a reasonable assumption, use Excel's scatterplot option to produce a graph of these two variables. Include the line of best fit (DO NOT INCLUDE R2 - it is not to be discussed here).
Label this graph as EXHIBIT 1 with a relevant title and remember to optimise its presentation via the various formatting options available.
(b) Based ONLY on the scatterplot you produced as Exhibit 1, does a linear relationship seem reasonable? If so, is it a positive or negative slope? Provide evidence for your answer and interpret what this means in context of this question.
QUESTION TWO:
Regardless of your answer in Question One, now assume that a linear relationship is reasonable.
(a) Using the Regression Analysis procedure in Excel, produce a simple linear regression model with Petrol Price as the dependent variable and the following requirements:
- Select 99% Confidence Level in the Output Options.
- Report all values to 4 decimal places where relevant.
- Provide the Summary Output labelled as EXHIBIT 2 with an appropriate title.
(b) Based on this output, state the equation of this regression model (correct to 4 decimal places), remembering to define the variables.
QUESTION THREE:
Before interpreting this model, it is first essential to determine whether or not it is a true representation of the relationship that exists between Petrol Price and Crude Oil Price in the population. To do this, a hypothesis test of significance is required.
(a) Using a 5% level of significance, determine whether or not this relationship between the price of Petrol and the price of Crude Oil is a statistically significant, linear relationship. Remember to include ALL steps, show ALL working and interpret your conclusion IN CONTEXT of this question.
QUESTION FOUR:
Report to Management
Assuming now that the model you have identified is statistically significant, provide the following in a short report to management:
(a) State and interpret the coefficient of determination for this model.
(b) An interpretation of the Y intercept, b0 and the slope coefficient, b1.
(c) Use the regression model developed in (a) to predict the petrol price if crude oil is $72/barrel.
(d) Use any relevant information in the data file to comment on this prediction.
(e) Would it be appropriate to use this model to predict the petrol price if crude oil is $200/barrel? Explain.
(f) From the Summary Output provided in EXHIBIT 2, state and interpret the 99% confidence interval estimate of the population slope beta, correct to 4 decimal places.
QUESTION FIVE:
Now, in just a short paragraph, using the findings from this report, answer the original question, "Should we worry about global oil prices?" [HINT: NEWS FLASH! Saudia Arabia has hit the headlines in the last week or so.....it would be very impressive if you could relate this to your findings here as well!]
Attachment:- Data Mining, Analytics and Visualisation Assignment Files.rar