Question

Statistical Analysis for Business Applications I
Consider the following data representing the total time (in hours) a student spent on reviewing for the Stat final exam and the actual score on the final. The sample of 10 students was taken from a class and the following answers were reported.
time score
0 23
4 30
5 32
7 50
8 45
10 55
12 60
15 70
18 80
20 100
Part 1: Use the formulas provided on the 3rd formula sheet to compute the following quantities. Open an Excel spreadsheet and write the table with data given above. Add columns for x2, y2, and xy, as well as the last row for Σ. For each of the following quantities, write the formula for it in a cell and evaluate it.

(e) Find the predicted final exam score for a student who spent 14 hours reviewing before the final.
(f) Find the predicted final exam score for a student who spent 15 hours reviewing before the final.
(g) What is the difference between the observed and the predicted final exam score for a studentwho spent 15 hours reviewing before the final?
(h) Find the total sum of squares SST.
(i) Find the sum of squares error SSE.
(j) Find the sum of squares regression SSR.
(k) Use the answers from (h)-(j) to confirm that SST = SSR + SSE.
(l) Find the coefficient of determination R2.
(m) Use your answers for (a), (b) and (l), to confirm that r .
(n) What proportion of variation is explained using the regression model?
(o) Find the standard error of the estimate se.
(p) Find the standard error of the regression slope sb.
(q) Does the amount of time spent reviewing for the final affect the final exam score? Use the sample provided above and the significance level of 0.05.
(hint: perform the hypothesis test for H0 : β1 = 0 vs. H1 : β1 = 0.)6
Part 2: Find and use Excel built-in-functions to check your answers for r, b1, and b0. Next to each cell from Part 1, calculate these three quantities using Excel built-in-functions and confirm your answers from Part 1.
(hint: for example, for r the Excel built-in function is ”CORREL”)
Part 3: Bellow your answers from Parts 1 and 2, perform the regression analysis using Excel built-in-module which can be found under ”DATA” → ”Data Analysis” → ”Regression” and double check your answers from Part 1. Draw the scatter plot of the data and, by visually observing the graph, determine if there is a linear relationship between the amount of time a student spent reviewing for the final exam and the actual score on the final.

Answer 1