Could you please tell me how to solve this question on an excel sheet using the
ID: 3176613 • Letter: C
Question
Could you please tell me how to solve this question on an excel sheet using the regression feature?
After declining over the last century, the percentage of men aged 65 and older in the workforce has begun to rise in recent years, as shown by table below:
a.)Using the regression feature in Excel, find a cubic and a quartic function that model this data, letting t=0 correspond to the year 1900.
b.)Using each of your answers to part (a), find the rate that the percent of men aged 65 and older in the workforce was increasing in 2005.
c.(Discuss which model from part (a) best describes the data, as well as which answer from part (b) best describes the rate that the percent of men aged 65 and older in the workforce was increasing in 2005.
Year
Percent of Men 65 and Older in Workforce
1900
63.1
1920
55.6
1930
54.0
1940
41.8
1950
45.8
1960
33.1
1970
26.8
1980
19.0
1990
16.3
2000
17.7
2010
20.5
Year
Percent of Men 65 and Older in Workforce
1900
63.1
1920
55.6
1930
54.0
1940
41.8
1950
45.8
1960
33.1
1970
26.8
1980
19.0
1990
16.3
2000
17.7
2010
20.5
Explanation / Answer
First of all, we would calculate x^1, x^2 and also x^3 to find quartic and cubic regression.
Year
Percent of Men 65 and Older in Workforce (y)
x
x^1
x^2
x^3
1900
63.1
0
0
0
0
1920
55.6
20
20
400
8000
1930
54
30
30
900
27000
1940
41.8
40
40
1600
64000
1950
45.8
50
50
2500
125000
1960
33.1
60
60
3600
216000
1970
26.8
70
70
4900
343000
1980
19
80
80
6400
512000
1990
16.3
90
90
8100
729000
2000
17.7
100
100
10000
1000000
2010
20.5
110
110
12100
1331000
Quartic
SUMMARY OUTPUT
Regression Statistics
Multiple R
0.964726
R Square
0.930696
Adjusted R Square
0.913371
Standard Error
5.02695
Observations
11
ANOVA
df
SS
MS
F
Significance F
Regression
2
2714.887
1357.444
53.71712
2.31E-05
Residual
8
202.1618
25.27023
Total
10
2917.049
Coefficients
Standard Error
t Stat
P-value
Lower 95%
Upper 95%
Lower 95.0%
Upper 95.0%
Intercept
66.97975
4.315416
15.52104
2.96E-07
57.02839
76.93112
57.02839
76.93112
x^1
-0.65125
0.167876
-3.87933
0.004679
-1.03837
-0.26412
-1.03837
-0.26412
x^2
0.001589
0.001425
1.115218
0.297134
-0.0017
0.004874
-0.0017
0.004874
Cubic
SUMMARY OUTPUT
Regression Statistics
Multiple R
0.986467
R Square
0.973117
Adjusted R Square
0.961596
Standard Error
3.347021
Observations
11
ANOVA
df
SS
MS
F
Significance F
Regression
3
2838.631
946.2104
84.46383
7.34E-06
Residual
7
78.41786
11.20255
Total
10
2917.049
Coefficients
Standard Error
t Stat
P-value
Lower 95%
Upper 95%
Lower 95.0%
Upper 95.0%
Intercept
62.09612
3.227199
19.24149
2.55E-07
54.46501
69.72724
54.46501
69.72724
x^1
0.061522
0.24184
0.254389
0.806506
-0.51034
0.633382
-0.51034
0.633382
x^2
-0.01545
0.005213
-2.96326
0.021008
-0.02777
-0.00312
-0.02777
-0.00312
x^3
0.000103
3.11E-05
3.32356
0.012703
2.99E-05
0.000177
2.99E-05
0.000177
a)
Here we can see that the equations would be as follows:-
Quartic: 66.98-0.065125x^1+0.001589x^2
Cubic: 62.10+0.061522x^1-0.01545x^2+0.000103x^3
b)
Quartic (2005): 66.98-0.065125(105)+0.001589(11025)=66.98-6.838125+17.518725=77.66
Cubic (2005): 62.10+0.061522(105)-0.01545(11025)+0.000103(1157625)=62.10+6.45981-17.518725+119.235375=170.27646
c)
In my opinion, it would be quartic because cubic is giving too high results.
Year
Percent of Men 65 and Older in Workforce (y)
x
x^1
x^2
x^3
1900
63.1
0
0
0
0
1920
55.6
20
20
400
8000
1930
54
30
30
900
27000
1940
41.8
40
40
1600
64000
1950
45.8
50
50
2500
125000
1960
33.1
60
60
3600
216000
1970
26.8
70
70
4900
343000
1980
19
80
80
6400
512000
1990
16.3
90
90
8100
729000
2000
17.7
100
100
10000
1000000
2010
20.5
110
110
12100
1331000
Quartic
SUMMARY OUTPUT
Regression Statistics
Multiple R
0.964726
R Square
0.930696
Adjusted R Square
0.913371
Standard Error
5.02695
Observations
11
ANOVA
df
SS
MS
F
Significance F
Regression
2
2714.887
1357.444
53.71712
2.31E-05
Residual
8
202.1618
25.27023
Total
10
2917.049
Coefficients
Standard Error
t Stat
P-value
Lower 95%
Upper 95%
Lower 95.0%
Upper 95.0%
Intercept
66.97975
4.315416
15.52104
2.96E-07
57.02839
76.93112
57.02839
76.93112
x^1
-0.65125
0.167876
-3.87933
0.004679
-1.03837
-0.26412
-1.03837
-0.26412
x^2
0.001589
0.001425
1.115218
0.297134
-0.0017
0.004874
-0.0017
0.004874
Cubic
SUMMARY OUTPUT
Regression Statistics
Multiple R
0.986467
R Square
0.973117
Adjusted R Square
0.961596
Standard Error
3.347021
Observations
11
ANOVA
df
SS
MS
F
Significance F
Regression
3
2838.631
946.2104
84.46383
7.34E-06
Residual
7
78.41786
11.20255
Total
10
2917.049
Coefficients
Standard Error
t Stat
P-value
Lower 95%
Upper 95%
Lower 95.0%
Upper 95.0%
Intercept
62.09612
3.227199
19.24149
2.55E-07
54.46501
69.72724
54.46501
69.72724
x^1
0.061522
0.24184
0.254389
0.806506
-0.51034
0.633382
-0.51034
0.633382
x^2
-0.01545
0.005213
-2.96326
0.021008
-0.02777
-0.00312
-0.02777
-0.00312
x^3
0.000103
3.11E-05
3.32356
0.012703
2.99E-05
0.000177
2.99E-05
0.000177