MBA5430 Problem set 2

In this problem set you will take the EXCEL spreadsheet from problem set 1 and perform a correlation analysis for the variables CQTOT and UITOT that you calculated and make a scatterplot of the data.

Steps to Calculating the Correlation Coefficient and Plotting Data

1. Open the EXCEL spreadsheet. Highlight the column title and all data under CQTOT. Hold down the Crtl key and highlight the UITOT title and all data under UITOT.

1. Click on “Insert” “Scatter” and select the random points or “Scatter with only markers” option.

1. A scatterplot will appear on your screen. Examine the data. Does it look like a correlation exists?

1. Calculate the correlation coefficient. Type in cell R2 to the right of data “Correlation” to label that you will be calculating the correlation coefficient for CQTOT and UITOT. Move to the cell S2 to the right and type =Correl (N2:N258, O2:O258).

1. The correlation coefficient will be calculated and displayed.

1. To find out if this correlation is statistically significant (in other words really a relationship according to the numbers) we need to calculate a t value.

1. Enter this formula into EXCEL. Enter “t=” in cell R4 you calculated. Then move to cell S4 and enter =(S2*sqrt(257-2))/(sqrt(1-S2^2)) . Now calculate p or the probability. Go to cell R8 and type “p=”

1. Go to cell S8 and type =T.DIST.RT(S4,258,1) to calculate the probability. If the probability is less than .05, then the correlation is statistically significant.

Reporting your results

You could then write the following describing your results.

An example for writing this is:

There is a positive relationship between service received and the likelihood of returning to the business (r = .62). This relationship is statistically significant (t = 5.64, p < .05).

Answer the following questions on this document and attach the spreadsheet in the Canvas upload area.

1. Does the graph of CQTOT and UITOT represent a linear relationship between the variables?

2. What is the correlation coefficient for these variables?

3. Is there a positive or negative relationship? What does this mean?

4. What is the t-value that you calculated?

5. What is the p-value?

6. Write two sentences as shown in the example above explaining your results.