# 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

• pg 1
```									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
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
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