These assignments are to assess your ability to use spreadsheets to aid in economic decision making. To complete the assignment, first review and complete the attached Assignment One Sample. 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. ECO 500: Excel Assignment One 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 DPI a) Plot the data using a scatterplot. b) Find the equation that shows the relatio c) Find the equation that shows the relatio d) What are the R squares for the equation e) Use the equations to calculate estimate f) If DPI increases at a rate of 2.2% in 2013 Data Year Questions: 1964. 462.3 25.8 1965. 497.8 29.6 1966. 537.4 29.9 1967. 575.1 29.6 1968. 624.7 35.4 1969. 673.8 37.4 1970. 735.5 34.5 1971. 801.4 43.2 1972. 869.0 49.4 1973. 978.1 54.4 1974. 1,071.7 48.2 1975. 1,187.3 52.6 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 Answers: a) b) c) d) e) f) Motor Vehicles and Parts 2013 2014 Housing and Utilities 2013 2014 1 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 2010. 11,127.1 342.7 2011. 11,549.3 373.6 2012 . 11,931.2 407.0 p Year Housing and Utilities 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 2 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 2010. 11,127.1 1,891.9 2011. 11,549.3 1,929.9 2012 p. 11,931.2 1,965.9 Source: Economic Report of the President, 2013. Appendix B. 3 ta using a scatterplot. uation that shows the relationship between motor vehicles and parts expenditure (PCE) and DPI (insert a linear trendline and show the eq uation that shows the relationship between housing and utilities expenditures (PCE) and DPI (insert a linear trendline and show the equati he R squares for the equations? Which one is better? Can you provide an economic rationale to explain the difference? uations to calculate estimated values for both types of expenditures for 2011 and 2012. How well did the equations predict values for 2011 ases 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 a Vehicles and Parts sing and Utilities 4 5 6 ure (PCE) and DPI (insert a linear trendline and show the equation on the scatterplot). (PCE) and DPI (insert a linear trendline and show the equation on the scatterplot). omic rationale to explain the difference? nd 2012. How well did the equations predict values for 2011 and 2012? predicted values for consumer spending on motor vehicles and parts? On housing and utilities? 7 8 9 n the scatterplot). e scatterplot). ? On housing and utilities? 10 11 12 ECO 500 Excel Assignment One PCE Insurance and Financial Services DPI 1964. 462 18 1965. 498 19 1966. 537 21 1967. 575 23 1968. 625 26 1969. 674 29 1970. 736 31 1971. 801 34 1972. 869 38 1973. 978 42 1974. 1,072 46 1975. 1,187 54 1976. 1,302 59 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 a) Find the equation of the trendline (line of best fit) that represents th Expenditures on insurance and financial services = -38.062 + 0.0758 DP b) What is the value of R2? 0.9943 c) What does the R2 represent? Over 99.4% of the variation in consumer expenditures on motor vehicl d) How well did the equation predict values for 2011 and 2012? The equation over estimated the demand for motor vehicles and parts e) If DPI increases at a rate of 2.2% in 2013 and then at a rate of 2.6% i consumer spending on insurance and financial services? 2013 2014 886 910 1125 843.75 f(x) = 0.0758284062x 38.0620906812 R = 0.9943114454 562.5 and Financial Services, Billions of Dollars PCE Insurance 281.25 0 0 3000 6000 Disposable Personal Income, Billio 1 2005. 9,277 698 2006. 9,916 733 2007. 10,424 790 2008. 11,025 807 2009. 10,722 742 2010. 11,127 796 2011. 11,549 807 2012 p. 11,931 829 2013 2014 12,194 12,511 837 866 886 910 Source: Economic Report of the President, 2013. Appendix B. 2 f best fit) that represents the data. vices = -38.062 + 0.0758 DPI penditures on motor vehicles and parts can be explained by changes in disposable personal income. for 2011 and 2012? or motor vehicles and parts for 2011 and 2012, but 3.75% and 4.55% respectively. and then at a rate of 2.6% in 2014, what are the predicted values for cial services? 0620906812 Dollars 6000 9000 12000 15000 posable Personal Income, Billions of Dollars 3 4 5 6