Question

Please provide excel formulas Stat 3309 - Statistical Analysis for Business Applications I Consider the following data representing the total time in hours) a student spent on reviewing for the Stat 3309 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 r?, y? and ry, as well as the last row for E. For each of the following quantities, write the formula for it in a cell and evaluate it. (a) Find the sample correlation coefficient r. (b) Find the slope by of the sample regression line. (c) Find the y-intercept by of the sample regression line. (d) What is the equation of the sample regression line? (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 student who spent 15 hours reviewing before the final? (h) Find the total sum of squares SST. (i) Find the sum of squares error SSE. 6) Find the sum of squares regression SSR. (k) Use the answers from (h)-G) to confirm that SST = SSR + SSE. (1) Find the coefficient of determination R. (m) Use your answers for (a), (b) and (1), to confirm that r=; VR (n) What proportion of variation is explained using the regression model? (0) Find the standard error of the estimate se. (p) Find the standard error of the regression slope Sb.

Answer 1

XI - Book1 - Microsoft Excel (Product Activation Failed) File Home Insert Page Layout Formulas Data Review View Add-Ins O x Cut Σ AutoSum και Calibri 11 A Wrap Text General . HH Fill BI U E Copy Paste Format Painter Clipboard А Merge & Center $ - % +.0 .00 .00.0 Insert Delete Format Clear - Conditional Format Cell Formatting as Table - Styles Styles Sort & Find & Filter - Select Editing Font Alignment Number Cells L6 A B. с D E F G H 1 J K L M N o Р 0 R S T U 1 2 timex score y x^2 y^2 ху 3 4 0 23 0 529 0 5 4 30 6 Сл 32 120 160 350 360 O 7 7 50 8 8 45 64 125 343 512 1000 1728 3375 5832 900 1024 2500 2025 3025 3600 4900 9 55 10 12 10 60 11 15 70 550 720 1050 1440 2000 12 18 80 6400 10000 13 20 100 8000 14 15 average 9.9 54.5 2097.9 3490.3 675 16 17 corr 0.981 18 19 slope 3.692 20 21 intercept 17.952 22 23 24 25 Activate Windows Go to Settings to activate Windows. Sheet2 Sheet3 KI Sheet1 Ready BO 100% + e e W X g 11:52 PM 07/30/2020

1. correlation coefficient is calculated using the formula

${\color{Red} r=\frac{average(xy)-average(x)*average(y)}{\sqrt{[average(x^2)-(average(x))^{2}]*[average(y^2)-(average(y))^{2}]}}}$

Substituting the values, we get r = 0.981

Using the method of least squares, the estimated regression line is given by

Y=b₀+b₁X, where b₀  is the intercept and b₁ is the slope estimate.

and ${\color{Red} b_{1}= \frac{average(xy)- average(x)*average(y)}{average(x^2)-(average(x))^2)}}$

and ${\color{Red} b_{0}=average(y)-b_{0}* average(x)}$

Substituting the values, we get ,

2. Slope = b₁ = 3.692 and

3. Intercept = b₀ = 17.952

4. The sample regression line is given by Y=17.952+3.692X

5. When X=14, Y =17.952+3.692*14 = 69.64

6. when X= 15, Y =17.952+3.692*15 =73.33

7.The observed score forX=15 is 70, thus the difference is y-Y, ie, 70-73.33= -3.33

This is a very long question. Please give the remaining parts as another question

thank you :)