Question

1) A business analyst wants to know if there is a correlation in a sales person’s intelligence and the average weekly sales. The Excel spreadsheet list the data for a random selection of 15 sales persons. The intelligence is measured by an inventory each employee takes.

a)Create the scatterplot on your Excel Spreadsheet. Does it appear to be a positive linear correlation? Why or why not?

b)Fill out the necessary cells in the spreadsheet to calculate the correlation coefficient ?. Use both the formula in the formula sheet and the built-in CORREL function in Excel to check your work. List the value here: __________

c)Conduct a 7-step hypothesis test as outlined in the PowerPoint to determine if the population correlation coefficient ? is statistically significant with ? = 0.05?

d)From the result of your hypothesis test should there be a regression line? Why or why not?

e)Use Excel to calculate the coefficient of determination ? 2 . List it here: ___________________ What does the value you found for ? 2 say about the explained and unexplained variations? Write the appropriate sentences. Use slides 8-10 in the section 9.3 PowerPoint as your guide

x values. y values. n=15

89	2625
93	2700
91	3100
122	3150
115	3175
100	3100
98	2700
105	2475
112	3625
109	3525
130	3225
104	3450
104	2425
111	3025
97	3625

Answer 1

a) Create the scatterplot on your Excel Spreadsheet. Does it appear to be a positive linear correlation? Why or why not?

From the above scatter plot, increase the value of the x, increases the corresponding value of y and vice versa. Hence, these two variables have a positive linear correlation.

b)Fill out the necessary cells in the spreadsheet to calculate the correlation coefficient ?. Use both the formula in the formula sheet and the built-in CORREL function in Excel to check your work.

Ans: The value of the correlation r= 0.3279.

#