Question

A marketing professor at Givens College is interested in the relationship between hours spent studying and total points earned in a course. Data collected on 10 students who took the course last quarter are in file HoursPts.xlsx.

Hours	Points
45	40
30	35
90	75
60	65
105	90
65	50
90	90
80	80
55	45
75	65

a) Use XLSTAT to create a scatterplot with hours spent studying on the horizontal axis and total points earned on the vertical axis. Include the fitted simple linear regression line on the plot and include the plot in your answer. What does the scatterplot indicate about the relationship between hours spent studying and total points earned?

Hint: Select Visualizing data > Scatter plots, select cells A1:A11 for X and select cells B1:B11 for Y. Click “Options” and check “Regression lines.”

b) Use XLSTAT to estimate a simple linear regression model using least squares. Report the estimated regression equation that could be used to predict the total points earned given the hours spent studying.

Hint: Select Modeling data > Linear regression, select cells B1:B11 for “Y / Dependent variables: Quantitative” and select cells A1:A11 for “X / Explanatory variables: Quantitative.”

c) Is there a significant linear relationship between the two variables based on a significance level α=0.05?

Hint: You can use either a t-test or an F-test to answer this question. In your answer state the hypotheses, test statistic, p-value, decision, and conclusion.

d) Use the estimated regression equation to predict the total points earned for a student who spends 95 hours studying.

e) Use XLSTAT to compute a 95% confidence interval for the average total points earned for students who spend 95 hours studying. Interpret the interval in the context of the application.

Answer 1

Ana a ) using excel

we have

b ) using excel

regression analysis output is

Simple Linear Regression Analysis
Regression Statistics
Multiple R	0.9369
R Square	0.8777
Adjusted R Square	0.8624
Standard Error	7.5231
Observations	10
ANOVA
	df	SS	MS	F	Significance F
Regression	1	3249.7208	3249.7208	57.4182	0.0001
Residual	8	452.7792	56.5974
Total	9	3702.5000
	Coefficients	Standard Error	t Stat	P-value	Lower 95%	Upper 95%
Intercept	5.8470	7.9717	0.7335	0.4842	-12.5358	24.2299
Hours	0.8295	0.1095	7.5775	0.0001	0.5771	1.0820

Confidence Interval Estimate
Data
X Value	95
Confidence Level	95%
Intermediate Calculations
Sample Size	10
Degrees of Freedom	8
t Value	2.306004
XBar, Sample Mean of X	69.5
Sum of Squared Differences from XBar	4722.5
Standard Error of the Estimate	7.523125
h Statistic	0.237692
Predicted Y (YHat)	84.65326
For Average Y
Interval Half Width	8.4580
Confidence Interval Lower Limit	76.1953
Confidence Interval Upper Limit	93.11121
For Individual Response Y
Interval Half Width	19.3003
Prediction Interval Lower Limit	65.3529
Prediction Interval Upper Limit	103.9536

simple linear regression model is

points = 5.847 +0.8295 *points

Ans c ) since p value of F stat is 0.0001 which is less than 0.05 so  there a significant linear relationship between the two variables based on a significance level .

Ans d) the estimated regression equation to predict the total points earned for a student who spends 95 hours studying is

= 5.847 +0.8295 *95 = 84.6495

Ans e ) a 95% confidence interval for the average total points earned for students who spend 95 hours studying is (76.1953,93.1112)

we are 95 % confident that average total points earned for students who spend 95 hours studying lies in between (76.1953,93.1112)