ETB1100 Business Statistics
Tutorial 10
Driving Business Decisions Using Regression: Exploring Relationships and Predictive Power Through Correlation and Linear Regression Analysis
Download files from the Tutorial TEN Questions folder on Moodle
If you have not already done so, download the following files for use in these Week 10 Tutorial questions which are found in the Tutorial TEN Questions folder on Moodle .
• Random Sample_DEMO.xlsx
• Burgers.xlsx
• Car Dealership.xlsx Q10.1
This question uses data in the worksheet labelled Burger, in the file labelled Burgers.xlsx
It gives real data on the SALES and PRICE for franchises of a (unnamed) burger chain in a selection of different cities across the US. SALES is in thousands of dollars and is the dependent variable (Y), while PRICE is an index over all products sold in a given month and is expressed as a notional number of dollars for a meal, and is the independent variable (X).
(a) What do you expect the relationship to be between PRICE and SALES?
(b) Use Excel to plot a scatter diagram [Insert>Scatter and select unjoined dots] of SALES(Y) against PRICE(X), (with SALES on the vertical axis).
Remember to follow the approach that you practiced in your Pre-Class Exercises on the ice cream sales data and display the R2 on each chart.
Comment on how this visual relationship compares with your expectations.
(c) Estimate a model for the relationship between SALES and PRICE, by using Excel to
produce the simple linear regression output. Remember to follow the approach that you practiced in your Pre-Class Exercises on the ice cream sales data.
Include the following extra feature:
In addition to checking “Labels”, also check “Confidence level” and in the adjacent field, type “99”
(This will provide a 99% confidence interval for population coefficients in addition to the 95% confidence interval that is always provided.)
As a check that your output is correct, make sure that that the fourth number from the top of the output, Standard Error, is 5.096858
(d) Based on the output produced in (c), state the estimated linear regression equation for this data, being sure to define the variables.
(e) Using a 5% level of significance, conduct a hypothesis test to determine whether there is evidence that a linear relationship exists between PRICE and SALES. (Remember, only the p-value approach is used in regression analysis). Remember to show ALL working, ALL steps AND interpret the conclusion in context of the question.
(f) What is the slope of the estimated regression line? Provide an interpretation of this value.
(g) State the value of the intercept of the regression line. Give an interpretation of this value and discuss whether it is meaningful in this case.
[Note that when interpreting the intercept and slope, it is important to take account of the units in which the data is specified. In the current case, in particular, the sales level is in thousands of dollars.]
Please have this output ready to discuss in the tutorial.
(h) State the sample value of the correlation coefficient between PRICE and SALES, and interpret this value.
(i) State the coefficient of determination and interpret this value.
(j) Using your estimated regression equation, predict the average/expected sales amount a franchise could expect if the cost of a meal was set to $6.25.
(k) Is this prediction likely to be reasonable/valid? Explain briefly.
(l) State and interpret the 95% and 99% confidence intervals for the slope coefficient. Compare and comment on the width of these intervals.
Q10.2
This question uses the data file Car Dealership.xlsx
A used car dealership is considering the factors that determine the sale price of used Toyota Camry passenger vehicles. As a first attempt at predicting the price, it is assumed that the main factor affecting the resale value is the distance the car has travelled (i.e. the odometer reading). For a sample of 13 cars, the following data is obtained:
(a) What do you expect the relationship to be between Price and Odometer Reading?
(b) Use Excel to plot a scatter diagram against Odometer Reading vs Price.
Remember to follow the approach that you practiced in your Pre-Class Exercises on the ice cream sales data and display the R2 on each chart.
Comment on how this visual relationship compares with your expectations.
(c) Based on the scatter plot, comment on whether it is appropriate to fit a regression line to the data.
A regression analysis was performed using Excel, with the following result:
(Since you have the data, you should also produce this regression output and check your output against that which is provided here.)
(d) Using the Excel output provided, state the estimated linear regression equation for this data, being sure to define the variables.
(e) Using a 5% level of significance, conduct a hypothesis test to determine if a linear
relationship exists between ODOMETER READING and SALE PRICE. (Remember, only the p-value approach is used in regression analysis). Remember also to show ALL working, ALL steps AND interpret the conclusion in context of the question.
Given that a linear relationship DOES NOT exist, in the workplace, there is no point in continuing with this model, interpreting the slope coefficient etc.
HOWEVER, IN THE CLASSROOM, WE WILL TAKE ADVANTAGE OF THIS EXAMPLE AND STILL USE IT FOR SOME EXTRA PRACTICE HERE .
(f) What is the slope of the estimated regression line? Provide an interpretation of this value.
(g) State the value of the intercept of the regression line? Give an interpretation of this value and discuss whether it is meaningful in this case.
(h) Look again at the output. The first number at the top of the output is labelled “Multiple
R” . This number is the absolute value of the sample correlation coefficient r between the two variables. In order to find the sign of the correlation coefficient, you must look at the sign of the slope.
So, state AND interpret the value of the correlation coefficient between odometer reading and price?
(i) State the coefficient of determination and interpret this value.