Question

The statistics department is interested in the different factors that might influence a student’s score on the final exam. They identify three factors to test: the student’s midterm exam score, the number of hours the student reports having studied for the final exam, and the average number of drinks of alcohol the student reports having on a night out. They collect data on all of these factors and want to estimate the following model:

FinalExamScore=β0+β1 MidtermExamScore +β2HourStudied +β3AverageAlcohol +ε

Variable Descriptions:

Final Exam Score: A student’s final exam score, measured in % on scale from 0 – 100

Midterm Exam Score: A student’s midterm exam score, measured in % on scale from 0 – 100

Hour Studied: The number of hours the student reports studying for the final exam

Average Alcohol: The average number of alcoholic drinks the student reports having on a typical night out.

For this question, you will need to download the Student Grade Data and then use the data analysis tool pack in Excel to run a regression. Note, you will need to install the data analysis tool pack on your computer. How to do this differs based on the type of computer you are using, but instructions can be found on google for both Mac and PC.

https://arizona.grtep.com/core/uploadfiles/components/282918/files/Student%20Grade%20Data.xlsx (Student Grade Data)

Estimate the sample regression equation above and fill in the estimated coefficients. Round the coefficient estimates to 4 decimals.

FinalExamˆScore=_______+_______MidtermExamScore+_____HourStudied−______AverageAlcohol

Predict a student’s final exam score who scored a 79% on the midterm, studied for 15 hours, and who only drinks 2 drinks on an average night out. Round your answer to 2 decimals. Hint, be sure to use your coefficients that were rounded to 4 decimal points.

Answer 1

In the Excel sheet Student Grade Data.xlsx,

1. Go to Data->Data Analysis->Regression

2. Select Column E (without header) as the Y-range and columns B-D as the X-range in the regression window. Click Ok. We get the following regression output:

	Coefficients	Standard Error	t Stat	P-value	Lower 95%	Upper 95%	Lower 95.0%	Upper 95.0%
Intercept	61.94450208	4.774682548	12.9735331	4.70816E-24	52.48408066	71.40492351	52.48408066	71.40492351
Midterm Exam (%)	0.255280196	0.052624245	4.850999688	3.99773E-06	0.151012	0.359548391	0.151012	0.359548391
Hours Studied	1.001458762	0.148987603	6.721759023	7.86573E-10	0.706258926	1.296658599	0.706258926	1.296658599
Average Alcohol Use	-2.882248037	0.351040694	-8.210580965	4.19857E-13	-3.577790174	-2.186705901	-3.577790174	-2.186705901

From the above output,

FinalExamScore = 61.9445 + 0.2553*MidtermExamScore + 1.0015*HourStudied - 2.8822*AverageAlcohol

Now, given a student who scored a 79% on the midterm, studied for 15 hours, and who only drinks 2 drinks on an average night out. Plugging these values into the above regression equation,

Final Exam Score of the student = 61.9445 + 0.2553*79 + 1.0015*15- 2.8822*2 = 91.37%