a Make an Excel worksheet to calculate SSxx SSyy and SSxy Le

(a) Make an Excel worksheet to calculate SSxx, SSyy, and SSxy. (Leave no cells blank - be certain to enter \'\'0\'\' wherever required. Negative values should be indicated by a minus sign. Round your answers to 2 decimal places.) (b) Use the formulas to calculate the slope and intercept. (Round your answers to 3 decimal places.) (c) Use your estimated slope and intercept to make a worksheet to calculate SSE, SSR, and SST. (Leave no cells blank - be certain to enter \'\'0\'\' wherever required. Negative values should be indicated by a minus sign. Do not round intermediate calcuations, but final answers should be rounded to 3 decimal places.)

Solution

we have 5 pairs of data in X and Y

Xbar is given as 19.8

now mean of Y is Ybar=(100+185+219+157+280)/5=188.2

we have the 5 values of X as X1,X2,X3,X4,,X5

and the 5 values of Y as Y1,Y2,Y3,Y4,Y5.

a)    X          Y               (Xi-Xbar)²                        (Yi-Ybar)²                   (Xi-Xbar)(Yi-Ybar)

        8          100           (8-19.8)²=139.24          (100-188.2)²=7779.24          (8-19.8)(100-188.2)=1040.76

     18         185           (18-19.8)²=3.24          (185-188.2)²=10.24                (18-19.8)(185-188.2)=5.76

     17          219            (17-19.8)²=7.84         (219-188.2)²=948.64             (17-19.8)(219-188.2)=-86.24

       20          157             (20-19.8)²=0.04         (157-188.2)²=973.44             (20-19.8)(157-188.2)=-6.24

       36           280              (36-19.8)²=262.44    (280-188.2)²=8427.24          (36-19.8)(280-188.2)=1487.16

Xbar=19.8 Ybar=188.2

so SSxx=(139.24+3.24+7.84+0.04+262.44)=412.8

SSyy=(7779.24 +10.24+948.64+973.44+8427.24)=18138.8

SSxy=(1040.76+5.76-86.24-6.24+1487.16)=2441.2

b) slope is b1=SSxy/SSxx=2441.2/412.8=5.914 [answer]

so the intercept is b0=ybar-b1*xbar=188.2-5.91376*19.8=71.108 [answer]

so the regression equation is Y(estimated)=71.1075+5.91376X

c)   X       Y        Yhat(estimated)            Yi-Yhati                 (Yi-Yhati)²                   (Yhati-Ybar)²           (Yi-Ybar)²

      8       100              118.418      100-118.418=-18.418    (-18.418)²=339.223      4869.59                     7779.24

      18      185              177.555     185-177.555=7.445       (7.445)²=55.428              113.31                       10.24

       17       219            171.641      219-171.641=47.359    (47.359)²=2242.875        274.19                      948.64

       20      157              189.383     157-189.383=-32.383    (-32.383)²=1048.658      1.40                          973.44

       36       280              284.003      280-284.003=-4.003      (-4.003)²=16.024            9178.19                 8427.24

so Xbar=19.8    Ybar=188.2      SSE=sum of (Yi-Yhati)² =(339.223+55.428+2242.875+1048.658+16.024)=3702.208 [answer]

SSR= sum of (Yhati-Ybar)² =(4869.59+113.31+274.19+1.40+ 9178.19)=14436.684 [answer]

SSE=sum of (Yi-Ybar)²=SSyy=18138.831 [answer]

d) the value of coefficient of determination=R²=[COV(X,Y)/sqrt[V(X)*V(Y)]]²=0.892²=0.7957 [answer]