Refer to the EXCEL Worksheet ECO500MTP_6 for U.S. Disposable Personal Income (DP

Question

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

Explanation / Answer

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

Related Questions

Navigate

• Browse (All)
• Browse R
• Subjects

---