If you don t have Data Analysis listed in your tools see Excel help Install and use the Analysis ToolPak by HC120915004738

VIEWS: 7 PAGES: 16

									This worksheet contains the data and output for the
Simple Linear Regression Example.
          Health Care Expense Example
                   Y - Dependents X-Xbar     Y-Ybar (X-Xbar)(Y-Ybar) (X-Xbar)sq
          X - Number of $ 1000 Annual Health Care Expense                                   X       Yhat
                 0           3         -2         -2              4           4             1        3.7
                 1           2         -1         -3              3           1             2          5
                 2           6          0          1              0           0             3        6.3
                 3           7          1          2              2           1             4        7.6
                 4           7          2          2              4           4             5        8.9
                                                                                            6       10.2
Sum              10         25                                   13           10
Average           2          5

                                                  b             1.30
                                                  a             2.40




                   Y - Dependents Yhat e            SSE e2
          X - Number of $ 1000 Annual Health Care Expense              Ybar        y-Ybar        SSTO


                  0          3        2.4       0.6             0.36          5             -2          4


                  1          2        3.7       -1.7            2.89           5            -3           9
                  2          6          5          1               1           5             1           1
                  3          7        6.3        0.7            0.49           5             2           4
                  4          7        7.6       -0.6            0.36           5             2           4
SUM              10         25         25          0             5.1          25             0          22
                                  Line 1       Line 2         Line 3         Line 4          e1        e2
                              0            3              2            2.4              5          0          -1
                              1            4            3.2            3.7              5          2         1.2
                              2            5            4.4              5              5         -1        -1.6
                              3            6            5.6            6.3              5         -1        -1.4
                              4            7            6.8            7.6              5          0        -0.2
                              5            8              8            8.9              5
                        Sum                                                                       0          -3




                                                                   -0.6               0.36


                                                                    1.7               2.89


Yhat -Ybar SSR                                                       -1               1.00


     -2.6        6.76                                              -0.7               0.49


     -1.3        1.69                                               0.6               0.36
        0           0                                                                 5.10
      1.3        1.69
      2.6        6.76
        0        16.9
                      sq
e3          e4         e1       e2          e3          e4
     -0.6         2         0           1        0.36        4
      1.7         3         4        1.44        2.89        9
       -1        -1         1        2.56           1        1
     -0.7        -2         1        1.96        0.49        4
      0.6        -2         0        0.04        0.36        4

       0         0          6          7          5.1        22
                                                   Scatterplot

                                    8

                                    7
Y - Annual Health Care Cost $1000




                                    6

                                    5

                                    4

                                    3

                                    2

                                    1

                                    0
                                        0   1      2              3        4   5
                                                X - Number of Dependents
                                  10

                                   9

                                   8
Y-Annual Health Care Cost $1000




                                   7

                                   6

                                   5                                              y=5

                                   4

                                   3

                                   2

                                   1

                                   0
                                       0   1   2             3            4   5         6
                                                   X - Number of Dependents
                                                                  Scatterplot

                                    10
                                     9                                                     y = 1.3x + 2.4
Y - Annual Health Care Cost $1000




                                     8
                                     7
                                     6                                             y=x+3
                                     5                                                                  y=5
                                             y = 1.2x + 2
                                     4
                                     3
                                     2
                                     1
                                     0
                                         0              1   2             3            4            5         6
                                                                X - Number of Dependents
                                    10

                                     9                                                     y = 1.3x + 2.4
Y - Annual Health Care Cost $1000




                                     8

                                     7

                                     6
                                                                                 y=x+3
                                     5                                                                   y=5
                                             y = 1.2x + 2
                                     4

                                     3

                                     2

                                     1

                                     0
                                         0             1    2             3            4             5         6
                                                                X - Number of Dependents
                                                 Explained and Unexplained Error

                                     8
Y - Annual Health Care Costs $1000




                                     7

                                     6

                                     5

                                     4
                                                                 ----- explained
                                     3
                                                                 ___ unexplained
                                     2

                                     1

                                     0
                                         0   1             2               3       4   5
                                                        X - Number of Dependents
                                     8
Y - Annual Health Care Costs $1000




                                     7

                                     6

                                     5

                                     4

                                     3

                                     2

                                     1

                                     0
                                         0   1      2              3        4   5
                                                 X - Number of Dependents
                                     8
Y - Annual Health Care Costs $1000



                                     7

                                     6

                                     5

                                     4

                                     3
                                                           ----- explained
                                     2

                                     1                     ___ unexplained
                                     0
                                         0   1      2              3         4   5
                                                 X - Number of Dependents
SUMMARY OUTPUT

   Regression Statistics
Multiple R          0.8765
R Square            0.7682
Adjusted R
Square              0.6909
Standard
Error               0.8790
Observations             5

ANOVA
                              df                       SS           MS          F     Significance F
Regression                              1                7.6818     7.6818     9.9412          0.0511
Residual                                3                2.3182     0.7727
Total                                   4                    10

                         Coefficients Standard Error               t Stat   P-value       Lower 95%    Upper 95% Lower 90.0%
Intercept                     -0.9545         1.0162                -0.9393   0.4169           -4.1885     2.2794     -3.3460
Y - $ 1000
Annual
Health Care
Expense                        0.5909                    0.1874     3.1530     0.0511                                 -0.0055          1.1873       0.1499

RESIDUAL OUTPUT                                                                         PROBABILITY OUTPUT
             Predicted X -                                                                              X - Number
              Number of                                       Standard                                       of
Observation Dependents                           Residuals    Residuals                   Percentile    Dependents
           1       0.8182                             -0.8182   -1.0747                              10          0
           2       0.2273                              0.7727    1.0150                              30          1
           3       2.5909                             -0.5909   -0.7762                              50          2
           4       3.1818                             -0.1818   -0.2388                              70          3
           5       3.1818                              0.8182    1.0747                              90          4

                 Y - $ 1000 Annual Health Care Expense Residual                                                                 Normal Probability Plot
                                       Plot
                                                                                          X - Number of Dependents




                                                                                                                     4.5
               1.0000                                                                                                  4
                                                                                                                     3.5
               0.5000                                                                                                  3
   Residuals




                                                                                                                     2.5
                                                                                                                       2
               0.0000                                                                                                1.5
                         0                   2             4          6          8                                     1
               -0.5000                                                                                               0.5
                                                                                                                       0
               -1.0000                                                                                                     0      20        40      60

                             Y - $ 1000 Annual Health Care Expense                                                                       Sample Percentile


                                                     Y - $ 1000 Annual Health Care Expense Line Fit Plot
                                 Number of
                               Dependents




                                                 5
                                                 4
                                                 3                                                                              X - Number of
X - Number of
 Dependents
                4
                3                                             X - Number of
                2                                             Dependents
                1
                0
                    0                 5               10     Predicted X -
                                                             Number of
                        Y - $ 1000 Annual Health Care ExpenseDependents
               Upper 90.0%
                      1.4369




                      1.0320




ability Plot




               80     100
e Percentile
To get this sheet, go to Tools -> Data Analysis -> Regression. If you don't
listed in your tools see Excel help "Install and use the Analysis ToolPak.
SUMMARY OUTPUT
                                                                                            5




                                                                            X - Number of
                                                                             Dependents
   Regression Statistics
                                                                                            4
Multiple R          0.8765
R Square            0.7682
                                                                                            3
Adjusted R                                                                                  2
Square              0.6909                                                                  1
Standard                                                                                    0
Error               0.8790                                                                      0
Observations             5                                                                          Y - $ 1000 Annual Heal
ANOVA
                  df            SS          MS          F     Significance F
Regression              1         7.6818    7.6818     9.9412          0.0511
Residual                3         2.3182    0.7727
Total                   4             10                                                                           1.0000
                                                                                                                   0.5000




                                                                                                       Residuals
              Coefficients Standard Error   t Stat   P-value      Lower 95%    Upper 95% Lower 90.0%
                                                                                                0.0000
Intercept          -0.9545         1.0162    -0.9393   0.4169          -4.1885     2.2794     -3.3460 0
                                                                                               -0.5000
Y - $ 1000
Annual                                                                                                             -1.0000
Health Care
Expense            0.5909         0.1874     3.1530    0.0511          -0.0055              1.1873                 0.1499

RESIDUAL OUTPUT                                                 PROBABILITY OUTPUT
             Predicted X -                                                      X - Number




                                                                                                        X - Number of
                                                                                                         Dependents
              Number of                   Standard                                   of                                 5
                                                                                                                        4
Observation Dependents       Residuals    Residuals               Percentile    Dependents                              3
                                                                                                                        2
           1       0.8182         -0.8182   -1.0747                          10          0                              1
                                                                                                                        0
           2       0.2273          0.7727    1.0150                          30          1                                   0
           3       2.5909         -0.5909   -0.7762                          50          2
           4       3.1818         -0.1818   -0.2388                          70          3
           5       3.1818          0.8182    1.0747                          90          4
on. If you don't have Data Analysis
lysis ToolPak.
             Line Fit Plot

                                                 X - Number of
                                                 Dependents




                                                 Predicted X -
           5                10                   Number of
 $ 1000 Annual Health Care Expense               Dependents



                         Residual Plot


    1.0000
    0.5000
    0.0000 Upper 90.0%
                  1.4369
    0.5000 0         2                   4       6           8

    1.0000
                 Y - $ 1000 Annual Health Care Expense
                        1.0320

                  Normal Probability Plot

        5
        4
        3
        2
        1
        0
             0                       50                   100
                             Sample Percentile

								
To top