Regression with confidence interval
Open up Excel and Click on Data Analysis
Click on Regression
Input the Y values in the Y range & the X values in the X range
Make sure the confidence level box at 95%.
Verify the residuals box.
What information is provided?
|
Family
|
Weekly Expenditure On Recreation (Y)
|
Weekly Income (X)
|
|
1
|
90
|
900
|
|
2
|
60
|
800
|
|
3
|
45
|
600
|
|
4
|
70
|
650
|
|
5
|
30
|
300
|
|
6
|
40
|
350
|
|
7
|
50
|
400
|
|
8
|
55
|
700
|
|
9
|
85
|
950
|
|
10
|
75
|
700
|
|
11
|
90
|
800
|
|
12
|
70
|
750
|
|
13
|
60
|
500
|
|
14
|
40
|
300
|
|
15
|
25
|
250
|
|
16
|
65
|
700
|
|
17
|
60
|
600
|
|
18
|
50
|
450
|
|
19
|
35
|
250
|
|
20
|
35
|
200
|
Example of what information should look like:
|
Data:
|
|
Firm I
|
Annual Dividend (X)
|
Price(Y)
|
|
1
|
13
|
115
|
|
2
|
4
|
45
|
|
3
|
12
|
100
|
|
4
|
5
|
50
|
|
5
|
6
|
55
|
|
6
|
8
|
85
|
|
7
|
3
|
40
|
|
8
|
4
|
50
|
|
9
|
5
|
45
|
|
10
|
7
|
70
|
SUMMARY OUTPUT
|
Regression Statistics
|
|
Multiple R
|
0.978902011
|
|
R Square
|
0.958249147
|
|
Adjusted R Square
|
0.953030291
|
|
Standard Error
|
5.652649783
|
|
Observations
|
10
|
|
ANOVA
|
|
|
df
|
SS
|
MS
|
F
|
Significance F
|
|
Regression
|
1
|
5866.880403
|
5866.880403
|
183.6129
|
8.45093E-07
|
|
Residual
|
8
|
255.6195965
|
31.95244957
|
|
|
|
Total
|
9
|
6122.5
|
|
|
|
|
|
Coefficients
|
Standard Error
|
t Stat
|
P-value
|
Lower 95%
|
Upper 95%
|
Lower 95.0%
|
Upper 95.0%
|
|
Intercept
|
15.20172911
|
4.119924714
|
3.6898075
|
0.006132
|
5.701159537
|
24.70229868
|
5.70116
|
24.7023
|
|
X Variable 1
|
7.507204611
|
0.554021622
|
13.550382
|
8.45E-07
|
6.229627634
|
8.784781588
|
6.229628
|
8.784782
|
RESIDUAL OUTPUT
|
Observation
|
Predicted Y
|
Residuals
|
Standard Residuals
|
|
1
|
112.795389
|
2.204610951
|
0.413672016
|
|
2
|
45.23054755
|
-0.23054755
|
-0.043259819
|
|
3
|
105.2881844
|
-5.288184438
|
-0.99227209
|
|
4
|
52.73775216
|
-2.737752161
|
-0.513710346
|
|
5
|
60.24495677
|
-5.244956772
|
-0.984160874
|
|
6
|
75.25936599
|
9.740634006
|
1.827727338
|
|
7
|
37.72334294
|
2.276657061
|
0.427190709
|
|
8
|
45.23054755
|
4.76945245
|
0.894937498
|
|
9
|
52.73775216
|
-7.737752161
|
-1.451907663
|
|
10
|
67.75216138
|
2.247838617
|
0.421783232
|
PROBABILITY OUTPUT
|
Percentile
|
Y
|
|
5
|
40
|
|
15
|
45
|
|
25
|
45
|
|
35
|
50
|
|
45
|
50
|
|
55
|
55
|
|
65
|
70
|
|
75
|
85
|
|
85
|
100
|
|
95
|
115
|