View the step-by-step solution to:

Question

To complete the assignment, first review and complete the attached Assignment One Sample and watch this video

tutorial. This spreadsheet works through a sample analysis, finding the relationship between personal consumption expenditures on financial services and insurance and disposable personal income. Next, download and open the attached Excel worksheet titled Excel Assignment One. In this assignment, you will be finding similar relationships for two other areas of consumer spending: motor vehicles and parts and housing and utilities. Place your answers in the Excel spreadsheet. Save your answers and give your spreadsheet a new name in the form, following this naming convention (use your last name and first initial): SmithCExcelAssgn1.

ECO 500 Excel Assignment One DPI 1964. 462 18 1965. 498 19 a) Find the equation of the trendline (line of best fit) that represents the data. 1966. 537 21 Expenditures on insurance and financial services = -38.062 + 0.0758 DPI 1967. 575 23 b) What is the value of R2? 1968. 625 26 0.9943 1969. 674 29 c) What does the R2 represent? 1970. 736 31 Over 99.4% of the variation in consumer expenditures on motor vehicles and parts can be explained by changes in disposable personal income. 1971. 801 34 d) How well did the equation predict values for 2011 and 2012? 1972. 869 38 The equation over estimated the demand for motor vehicles and parts for 2011 and 2012, but 3.75% and 4.55% respectively. 1973. 978 42 1974. 1,072 46 1975. 1,187 54 2013 886 1976. 1,302 59 2014 910 1977. 1,435 68 1978. 1,607 81 1979. 1,791 88 1980. 2,003 96 1981. 2,237 102 1982. 2,413 116 1983. 2,600 146 1984. 2,892 157 1985. 3,079 181 1986. 3,259 197 1987. 3,435 207 1988. 3,726 219 1989. 3,991 236 1990. 4,254 253 1991. 4,445 282 1992. 4,737 312 1993. 4,922 341 1994. 5,184 349 1995. 5,457 365 1996. 5,760 394 1997. 6,075 431 1998. 6,499 470 1999. 6,803 514 2000. 7,327 570 2001. 7,649 563 2002. 8,010 576 2003. 8,378 603 2004. 8,889 652 2005. 9,277 698 2006. 9,916 733 2007. 10,424 790 2008. 11,025 807 2009. 10,722 742 11,127 796 11,549 807 837 11,931 829 866 2013 12,194 886 2014 12,511 910 Source: Economic Report of the President, 2013. Appendix B. PCE Insurance and Financial Services e) 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 insurance and financial services? 2010. 2011. 2012 p . 0 2,000 4,000 6,000 8,000 10,000 12,000 14,000 0 100 200 300 400 500 600 700 800 900 f(x) = 0.0758284062x - 38.0620906812 R² = 0.9943114454 US Consumer Expenditures on Insurance and Financial Services Disposable Personal Income, Billions of Dollars PCE Insurance and Financial Services, Billions of Dollars
Background image of page 1
ECO 500: Excel Assignment One Questions: a) Plot the data using a scatterplot. b) Find the equation that shows the relationship between motor vehicles and parts expenditure (PCE) and DPI (insert a linear trendline and show the equation on the scatterplot). c) Find the equation that shows the relationship between housing and utilities expenditures (PCE) and DPI (insert a linear trendline and show the equation on the scatterplot). d) What are the R squares for the equations? Which one is better? Can you provide an economic rationale to explain the difference? e) Use the equations to calculate estimated values for both types of expenditures for 2011 and 2012. How wel did the equations predict values for 2011 and 2012? Data f) 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 Answers: 1964. 462.3 25.8 a) 1965. 497.8 29.6 b) 1966. 537.4 29.9 c) 1967. 575.1 29.6 d) 1968. 624.7 35.4 e) 1969. 673.8 37.4 f) 1970. 735.5 34.5 Motor Vehicles and Parts 1971. 801.4 43.2 2013 1972. 869.0 49.4 2014 1973. 978.1 54.4 Housing and Utilities 1974. 1,071.7 48.2 2013 1975. 1,187.3 52.6 2014 1976. 1,302.3 68.2 1977. 1,435.0 79.8 1978. 1,607.3 89.2 1979. 1,790.9 90.2 1980. 2,002.7 84.4 1981. 2,237.1 93.0 1982. 2,412.7 100.0 1983. 2,599.8 122.9 1984. 2,891.5 147.2 1985. 3,079.3 170.1 1986. 3,258.8 187.5 1987. 3,435.3 188.2 1988. 3,726.3 202.2 1989. 3,991.4 207.8 1990. 4,254.0 205.1 1991. 4,444.9 185.7 1992. 4,736.7 204.8 1993. 4,921.6 224.7 1994. 5,184.3 249.8 1995. 5,457.0 255.7 1996. 5,759.6 273.5 1997. 6,074.6 293.1 1998. 6,498.9 320.2 1999. 6,803.3 350.7 2000. 7,327.2 363.2 2001. 7,648.5 383.3 2002. 8,009.7 401.3 2003. 8,377.8 401.0 2004. 8,889.4 403.9 2005. 9,277.3 408.2 2006. 9,915.7 394.8 2007. 10,423.6 399.9 2008. 11,024.5 339.3 2009. 10,722.4 316.0 11,127.1 342.7 11,549.3 373.6 11,931.2 407.0 Year DPI 1964. 462.3 72.1 1965. 497.8 76.6 1966. 537.4 81.2 1967. 575.1 86.3 1968. 624.7 92.7 1969. 673.8 101.0 1970. 735.5 109.4 1971. 801.4 120.0 1972. 869.0 131.2 1973. 978.1 143.5 1974. 1,071.7 158.6 1975. 1,187.3 176.5 1976. 1,302.3 194.7 1977. 1,435.0 217.8 1978. 1,607.3 244.3 1979. 1,790.9 273.4 1980. 2,002.7 311.8 1981. 2,237.1 352.0 1982. 2,412.7 387.0 1983. 2,599.8 421.2 1984. 2,891.5 458.3 1985. 3,079.3 500.7 1986. 3,258.8 535.7 1987. 3,435.3 571.8 1988. 3,726.3 614.5 1989. 3,991.4 655.6 1990. 4,254.0 696.4 1991. 4,444.9 735.5 1992. 4,736.7 771.2 1993. 4,921.6 814.5 1994. 5,184.3 866.5 1995. 5,457.0 913.8 1996. 5,759.6 961.2 1997. 6,074.6 1,009.9 1998. 6,498.9 1,065.2 1999. 6,803.3 1,125.0 2000. 7,327.2 1,198.6 2001. 7,648.5 1,287.7 2002. 8,009.7 1,334.8 2003. 8,377.8 1,393.9 2004. 8,889.4 1,462.4 2005. 9,277.3 1,582.6 2006. 9,915.7 1,686.2 2007. 10,423.6 1,756.2 2008. 11,024.5 1,831.0 2009. 10,722.4 1,871.6 11,127.1 1,891.9 11,549.3 1,929.9 11,931.2 1,965.9 Source: Economic Report of the President, 2013. Appendix B. Below are data for U.S. DPI and 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. Using scatter diagrams and trendlines, generate graphs showing the relationship of each of the consumption categories to DPI and find the equation of the graph and the R squared. Use your results to answer the questions to the right. Motor Vehicles and Parts 2010. 2011. 2012 p . Housing and Utilities 2010. 2011. 2012 p .
Background image of page 1
Sign up to view the entire interaction

Top Answer

Here is the solution... View the full answer

ECO 500 Excel Assignment One.xlsx

ECO 500: Excel Assignment One DPI vs Motor Vehicles and Parts
500.0 f(x) = 0.0359881315x + 34.6065411994
Below are data for U.S. DPI and PCE on two major components of
400.0 R² = 0.904166776...

Sign up to view the full answer

Why Join Course Hero?

Course Hero has all the homework and study help you need to succeed! We’ve got course-specific notes, study guides, and practice tests along with expert tutors.

  • -

    Study Documents

    Find the best study resources around, tagged to your specific courses. Share your own to gain free Course Hero access.

    Browse Documents
  • -

    Question & Answers

    Get one-on-one homework help from our expert tutors—available online 24/7. Ask your own questions or browse existing Q&A threads. Satisfaction guaranteed!

    Ask a Question