Question

PLEASE ANSWER EACH PART CLEARLY STATE THE ANSWERS AND PLEASE MAKE SURE THE FORMAT USED CAN BE COPIED AND PASTED.

Use the tools of Excel to analyze the following:
Blood Alcohol Content (BAC) was evaluated based on number of beers consumed over 19 subjects.
i)      what is your dependent variable?
ii)     what is your independent variable?
a)       Develop the correlation coefficient
b)      Is this correlation coefficient significant? Use the six steps of hypothesis testing.
c)       Develop the Regression Equation using the least squares method (excel data analysis)
d)      What is the prediction equation?

BEERS	BAC
5	0.08
6	0.11
7	0.12
4	0.09
6	0.08
3	0.07
4	0.08
7	0.1
6	0.09
4	0.07
3	0.06
8	0.11
2	0.04
5	0.09
2	0.06
8	0.12
1	0.05
1	0.02
1	0.03

Answer 1

i) BAC is dependent variable, as it varies with number of BEERS
ii) BEERS is independent variable
a. =CORREL(BEERS,BAC) = 0.93
b.

1. Null hypothesis: There is no correlation and it is zero
2. Alternate hypothesis: Yes there is correlation between BAC and BEERS
3. Critical value at 95 percent =0.456
4. Test statistics = 0.93*sqrt(19-2)/sqrt(1-0.93^2)=10.43
5. As the test statistic is greater than critical value, it has significant relation and hence the null hypothesis is rejected
6. There is a strong association between BAC and number of beers

c.

SUMMARY OUTPUT
Regression Statistics
Multiple R	0.932
R Square	0.868
Adjusted R Square	0.860
Standard Error	0.011
Observations	19
ANOVA
	df	SS	MS	F	Significance F
Regression	1.000	0.013	0.013	111.901	0.000
Residual	17.000	0.002	0.000
Total	18.000	0.015
	Coefficients	Standard Error	t Stat	P-value	Lower 95%	Upper 95%
Intercept	0.0268	0.0054	4.9831	0.0001	0.0155	0.0382
BEERS	0.0116	0.0011	10.5783	0.0000	0.0093	0.0139

d.  BAC=0.0268+0.0116*No of BEERS