Homework SourseRefer to the EXCEL Worksheet ECO500MTP6 for US Disposable Pe
Refer to the EXCEL Worksheet ECO500MTP_6 for U.S. Disposable Personal Income (DPI) and Personal Consumption Expenditures (PCE) on two major components of consumer spending, motor vehicles and parts and housing and utilities. One is a durable good and the other is comprised of housing and other services and non-durables, such as natural gas purchases.
Eco500MTP_6:
Part a - Use EXCEL’s Regression tool to generate a Regression Output Matrix for each relationship
Part b – Which component of consumer spending is a better predictor of DPI? Be specific and be sure to incorporate the coefficient of determination and coefficient P-values into your answer.
Part c – If DPI increases at a rate of 2.2% in 2013 and then at a rate of 2.6% in 2014, what are the predicted values for consumer spending on motor vehicles and parts? On housing and utilities?
| Year | DPI | Motor Vehicles and Parts | Year | DPI | Housing and Utilities | |
| 1964. | 462.3 | 25.8 | 1964. | 462.3 | 72.1 | |
| 1965. | 497.8 | 29.6 | 1965. | 497.8 | 76.6 | |
| 1966. | 537.4 | 29.9 | 1966. | 537.4 | 81.2 | |
| 1967. | 575.1 | 29.6 | 1967. | 575.1 | 86.3 | |
| 1968. | 624.7 | 35.4 | 1968. | 624.7 | 92.7 | |
| 1969. | 673.8 | 37.4 | 1969. | 673.8 | 101.0 | |
| 1970. | 735.5 | 34.5 | 1970. | 735.5 | 109.4 | |
| 1971. | 801.4 | 43.2 | 1971. | 801.4 | 120.0 | |
| 1972. | 869.0 | 49.4 | 1972. | 869.0 | 131.2 | |
| 1973. | 978.1 | 54.4 | 1973. | 978.1 | 143.5 | |
| 1974. | 1,071.7 | 48.2 | 1974. | 1,071.7 | 158.6 | |
| 1975. | 1,187.3 | 52.6 | 1975. | 1,187.3 | 176.5 | |
| 1976. | 1,302.3 | 68.2 | 1976. | 1,302.3 | 194.7 | |
| 1977. | 1,435.0 | 79.8 | 1977. | 1,435.0 | 217.8 | |
| 1978. | 1,607.3 | 89.2 | 1978. | 1,607.3 | 244.3 | |
| 1979. | 1,790.9 | 90.2 | 1979. | 1,790.9 | 273.4 | |
| 1980. | 2,002.7 | 84.4 | 1980. | 2,002.7 | 311.8 | |
| 1981. | 2,237.1 | 93.0 | 1981. | 2,237.1 | 352.0 | |
| 1982. | 2,412.7 | 100.0 | 1982. | 2,412.7 | 387.0 | |
| 1983. | 2,599.8 | 122.9 | 1983. | 2,599.8 | 421.2 | |
| 1984. | 2,891.5 | 147.2 | 1984. | 2,891.5 | 458.3 | |
| 1985. | 3,079.3 | 170.1 | 1985. | 3,079.3 | 500.7 | |
| 1986. | 3,258.8 | 187.5 | 1986. | 3,258.8 | 535.7 | |
| 1987. | 3,435.3 | 188.2 | 1987. | 3,435.3 | 571.8 | |
| 1988. | 3,726.3 | 202.2 | 1988. | 3,726.3 | 614.5 | |
| 1989. | 3,991.4 | 207.8 | 1989. | 3,991.4 | 655.6 | |
| 1990. | 4,254.0 | 205.1 | 1990. | 4,254.0 | 696.4 | |
| 1991. | 4,444.9 | 185.7 | 1991. | 4,444.9 | 735.5 | |
| 1992. | 4,736.7 | 204.8 | 1992. | 4,736.7 | 771.2 | |
| 1993. | 4,921.6 | 224.7 | 1993. | 4,921.6 | 814.5 | |
| 1994. | 5,184.3 | 249.8 | 1994. | 5,184.3 | 866.5 | |
| 1995. | 5,457.0 | 255.7 | 1995. | 5,457.0 | 913.8 | |
| 1996. | 5,759.6 | 273.5 | 1996. | 5,759.6 | 961.2 | |
| 1997. | 6,074.6 | 293.1 | 1997. | 6,074.6 | 1,009.9 | |
| 1998. | 6,498.9 | 320.2 | 1998. | 6,498.9 | 1,065.2 | |
| 1999. | 6,803.3 | 350.7 | 1999. | 6,803.3 | 1,125.0 | |
| 2000. | 7,327.2 | 363.2 | 2000. | 7,327.2 | 1,198.6 | |
| 2001. | 7,648.5 | 383.3 | 2001. | 7,648.5 | 1,287.7 | |
| 2002. | 8,009.7 | 401.3 | 2002. | 8,009.7 | 1,334.8 | |
| 2003. | 8,377.8 | 401.0 | 2003. | 8,377.8 | 1,393.9 | |
| 2004. | 8,889.4 | 403.9 | 2004. | 8,889.4 | 1,462.4 | |
| 2005. | 9,277.3 | 408.2 | 2005. | 9,277.3 | 1,582.6 | |
| 2006. | 9,915.7 | 394.8 | 2006. | 9,915.7 | 1,686.2 | |
| 2007. | 10,423.6 | 399.9 | 2007. | 10,423.6 | 1,756.2 | |
| 2008. | 11,024.5 | 339.3 | 2008. | 11,024.5 | 1,831.0 | |
| 2009. | 10,722.4 | 316.0 | 2009. | 10,722.4 | 1,871.6 | |
| 2010. | 11,127.1 | 342.7 | 2010. | 11,127.1 | 1,891.9 | |
| 2011. | 11,549.3 | 373.6 | 2011. | 11,549.3 | 1,929.9 | |
| 2012. | 11,931.2 | 407.0 | 2012. | 11,931.2 | 1,965.9 |
Solution
Part - a
(1) Regression between DPI and Motor Vehicles
(2) Regression between DPI and Housing
Part - b
Housing and Utilities is a better predictor. The reason is two-fold:
(1) Coefficient of determination, R2 is higher for Housing & Utilities compared to Motor Vehicles, indicating a better goodness of fit, and
(2) The lower the P-value, the more significant the coefficient is. The P-values are lower for Housing & Utilities compared to Motor Vehicles.
NOTE: The 1st sub-question is very lengthy. The 1st 2 sub-questions are answered in full.
| SUMMARY OUTPUT | ||||||||
| Regression Statistics | ||||||||
| Multiple R | 0.950876846 | |||||||
| R Square | 0.904166776 | |||||||
| Adjusted R Square | 0.902127771 | |||||||
| Standard Error | 1132.075239 | |||||||
| Observations | 49 | |||||||
| ANOVA | ||||||||
| df | SS | MS | F | Significance F | ||||
| Regression | 1 | 568304227.4 | 568304227.4 | 443.435342 | 1.41303E-25 | |||
| Residual | 47 | 60234934.29 | 1281594.347 | |||||
| Total | 48 | 628539161.7 | ||||||
| Coefficients | Standard Error | t Stat | P-value | Lower 95% | Upper 95% | Lower 95.0% | Upper 95.0% | |
| Intercept | -429.1252747 | 288.2236203 | -1.488862274 | 0.143203517 | -1008.956399 | 150.7058496 | -1008.956399 | 150.7058496 |
| Motor Vehicles and Parts | 25.1240267 | 1.193092442 | 21.0579045 | 1.41303E-25 | 22.72383434 | 27.52421907 | 22.72383434 | 27.52421907 |
