Question

Q. 34

Part A. Using the dataset below, calculate the covariance and correlation coefficient between X and Y. For ease, copy and paste the dataset onto your spreadsheet.

X	Y
21	35
24	27
23	31
30	38
22	36
18	40
20	37
26	28

a.) Covariance = 5.71. Correlation coefficient = -0.32

b.) Covariance = -5. Correlation coefficient = -0.32

c.) Covariance = -5.71. Correlation coefficient = -0.32

d.) None of the above

Part B. Refer to the above dataset. Using the appropriate spreadsheet function, calculate the sample standard deviation of ONLY variable Y.

a.) 4.78
b.) 4.47
c.) 5.71
d.) None of the above

Part C. Refer to the above dataset. Run a regression on the dataset. What is your conclusion?

a.) Reject H0 because R-square is only 10.2%

b.) Do not reject H0 because R-square is only 10.2%

c.) Do not reject H0 because F is 0.68 and p-value = 0.4406

d.) Reject H0 because b = -0.4082 and p-value = 0.4406

Part D. Refer to the above dataset. Give a 95% confidence interval for the slope parameter.

a.) 15.2482; 71.5273

b.) -1.6177; 0.8014

c.) -0.8257; 0.4406

d.) None of the above

Answer 1

The data set is typed in the spreadsheet in 2 columns named as X and Y.

Part A:

To find the covariance between X and Y :

Click on Data Analysis option under Data tab.From the dropdown menu select COVARIANCE and click OK.

Under the Input Range, select the entire data set including the header row and tick the checkbox for "Labels in the 1st row".

On clicking OK, the covariance matrix is obtained as :

	X	Y
X	12.25
Y	-5	20

The covariance X and Y is thus -5.

Proceeding similarly we obtain the correlation as by clicking the Correlation option under Data Analysis tab.

We obtain the correlation matrix as :

	X	Y
X	1
Y	-0.31944	1

Hence the correlation as -0.31944.

So, option B is correct.

PART B :

To calculate the Standard Deviation of Y, type the formula

   =STDEV(cell number of starting value of Y : cell number of ending value of Y) and press enter.

Covariance is 4.78 hence option A is correct.

PART C :

To run a regression on the above data set , we click on the Regression option from the Data Analysis menu under Data tab.

Under the Y value ,we select the column cell containing the y values including the header row and under the X value we select the column cells containing the X values including the header row.

Click for check box for Headers and also for Confidence intervals.

The regression output will be obtained as below :

Regression Statistics
Multiple R	0.319438282
R Square	0.102040816
Adjusted R Square	-0.047619048
Standard Error	4.893421935
Observations	8
ANOVA
	df	SS	MS	F	Significance F
Regression	1	16.32653061	16.32653061	0.681818182	0.440550613
Residual	6	143.6734694	23.94557823
Total	7	160
	Coefficients	Standard Error	t Stat	P-value	Lower 95%	Upper 95%	Lower 95.0%	Upper 95.0%
Intercept	43.3877551	11.50001962	3.772841834	0.009257786	15.24822081	71.52728939	15.24822081	71.52728939
X	-0.408163265	0.494310262	-0.825722824	0.440550613	-1.617696903	0.801370373	-1.617696903	0.801370373

The Significance F is 0.44055 which is greater than 0.05 so we may conclude that the linear regression is useful and so retain the null hypothesis.

Hence, option C is correct.

PART D :

From the table, at the extreme end, we note that correspoding to the "Intercept", the 95% confidence interval is obtained as (Lower 95% , Upper 95%) ie (15.248 ,71.527).

Hence the correct option is A.