60.1K

Verified Solution

Link Copied!

For this final project, you will need to work through the following 5 questions through Microsoft Excel and submit a file at the end of this document with your responses to each individual question. There is a separate sheet within the file for each question with a text box included to write the responses for each question. Please be sure to answer the questions thoroughly for maximum credit. Question 1 You will visualize the relationship between a county's median income (Medinc) and the percent of the county's population with at least a bachelor's degree (BaDegree). One should probably expect a positive relationship between Medinc and BaDegree: on average, counties with a greater percent of the population with a bachelor's degree should have relatively more workers in higher-paying jobs and thus should have a higher median income. Click Insert, then look for the XY (Scatter) group and click on this. A blank rectangular plotting area will appear on your sheet. Right-click on the blank area and then click Select Data, In the Series area, add a new series by clicking the "+"button. Add BaDegree as the x values and Medinc as the Y values. Click OK. Paste your figure onto the sheet titled "Question 1" along with a brief discussion of the relationship between BaDegree and Medinc. A B C D E F G H 1 WV County Income and Demographic Characteristics (2010-2014 2 3 County 4 Barbour County 5 Berkeley County 6 Boone County 7 Braxton County 8 Brooke County 9 Cabell County 10 Calhoun County 11 Clay County 12 Doddridge County 13 Fayette County 14 Gilmer County 15 Grant County 16 Greenbrier County 17 Hampshire County 18 Hancock County 19 Hardy County 20 Harrison County 21 Jackson County 22 Jefferson County 23 Kanawha County 24 Lewis County 25 Uncoln County 26 Logan County 27 Marion County 28 Marshall County 29 Mason County 30 McDowell County 31 Mercer County 32 Mineral County Medinc ($) 36,351 55,100 42,740 31,984 44,067 37,716 31,017 32,933 40,329 34,914 35,625 41,600 40,256 26,828 39,342 36,465 43,130 40,733 66,205 46,583 38,006 35,623 37,312 23,607 43,085 41,978 38,297 35,678 30.713 HSDegree BaDegree (%) (%) 66.8 12.3 66.1 20.3 68.5 9.1 68.7 10.8 73.3 17.1 59.7 26.4 66.9 8.9 61.1 9.4 69.2 12.7 68.4 11.8 60.9 16.6 69.0 12.6 66.0 18.2 67.5 8.7 70.2 16.8 68.0 12.2 66.9 20.2 68.0 16.2 59.8 28.3 62.7 24.9 70.4 14.9 67.1 9.1 69.0 8.5 58.7 5.8 67.4 20.9 73.0 15.9 73.3 10.1 63.0 19.0 73.7 19 FemHead MedAge (%) (years) 15.1 40.8 19.1 38.3 17.6 41.6 12.5 44.0 18.5 45.7 23.2 38.5 18.6 46,3 11.2 42.7 15.2 43.4 21.0 42.8 12.5 37.9 10.5 44.9 16.5 45.2 18.4 43.8 16.8 45.3 12.7 44.1 18.0 42.1 16.6 423 15.4 39.4 20.6 42.5 16.6 43.8 16.2 41.8 17.8 42.3 21.7 43.2 17.9 41.3 44.1 20.1 42.7 19.0 42.3 13.8 10 A B C D E F H 31 Mercer County 35,678 63.0 19.0 19.0 42.3 32 Mineral County 30,713 73.7 12.8 19.0 42.71 33 Mingo County 34,495 61.9 10.5 17.9 41.4 34 Monongalia County 46,166 52.4 38.8 15.4 29.9 35 Monroe County 38,239 67.2 13.8 19.2 46.1 36 Morgan County 37.406 65,6 16.5 11.7 45.6 37 Nicholas County 38,755 70.2 13.6 12.4 44,00 38 Ohio County 40,342 62.6 28.6 20.1 43.1 39 Pendleton County 36,052 67.0 14.0 10.2 48.7 40 Pleasants County 43,831 75.7 13.3 12.5 43.4 41 Pocahontas County 34,761 68.9 15.8 9.6 48.2 42 Preston County 45,806 69.8 12.9 11.3 42.3 43 Putnam County 55,939 66.6 23.9 14.2 41.3 44 Raleigh County 41,152 64.7 18.2 17,9 40.9 45 Randolph County 40,146 65.0 18.1 14.8 43.3 46 Ritchie County 39,118 70.1 10.4 14.5 45.1 47 Roane County 30,104 66.8 10.3 16.2 43.6 48 Summers County 35,040 68.1 13.0 13.0 46.4 49 Taylor County 39,933 68.9 15.8 19.6 42.5 50 Tucker County 38,663 73.0 13.8 8.3 51 Tyler County 39,974 76.3 10.0 15.3 45.7 52 Upshur County 39,188 67.9 15.8 13.4 41.3 53 Wayne County 37,491 65.4 12.6 19.7 42.6 54 Webster County 28,907 62.6 9.1 17.1 45.7 55 Wetzel County 38,066 72.9 10.2 16.7 45.5 56 Wirt County 37,117 76.0 10.9 17.9 44.8 57 Wood County 42,471 70.3 18.7 16.2 42.3 58 Wyoming County 34,620 64.9 9.0 13.9 42.4 59 60 All data are five year averages (2010-2014) 61 Medine - Median income of families in the county for the prior 12 months (adusted for inflation) 62 HSDegree Percent of population, aged 25 and over, with at least a high school diploma, but not a Bachelor's degree 63 BaDegree Percent of population, aged 25 and over, with at least a Bachelor's degree 473 124 F A B C D E F G H 43 Putnam County 55,939 66.6 23.9 14.2 41.31 44 Raleigh County 41,152 64.7 18.2 17.9 40.9 45 Randolph County 40,146 65.0 18.1 14.8 43.3 46 Ritchie County 39,118 70.1 10.4 14.5 45.1 47 Roane County 30,104 66.8 10.3 16.2 43.6 48 Summers County 35,040 68.1 13.0 13.0 46.4 49 Taylor County 39,933 68.9 15.8 19.6 42.5 50 Tucker County 38,663 73.0 13.8 8.3 47.3 51 Tyler County 39,974 76.3 10.0 15.3 45.7 52 Upshur County 39,188 67.9 15.8 13.4 41.3 53 Wayne County 37.491 65.4 12.6 19.7 42.6 54 Webster County 28,907 62.6 9.1 17.1 45.7 55 Wetzel County 38,066 729 10.2 16.7 45.5 56 Wirt County 37,117 76.0 10.9 17.9 44.8 57 Wood County 42,471 70.3 18.7 16.2 42.3 58 Wyoming County 34,620 649 9.0 13.9 42.4 59 60 All data are five year averages (2010-2014) 61 Medinc Median Income of families in the county for the prior 12 months (adusted for inflation) 62 HSDegree Percent of population, aged 25 and over, with at least a high school diploma, but not a Bachelor's degree 63 BaDegree = Percent of population, aged 25 and over, with at least a Bachelor's degree 54 FemHead Percent of families headed by a female, with no spouse present 65 MedApe - Median age of the county's population 56 67 Source: U.S. Census Bureau, 2010-2014 American Community Survey 68 69 70 For this final project, you will need to work through the following 5 questions through Microsoft Excel and submit a file at the end of this document with your responses to each individual question. There is a separate sheet within the file for each question with a text box included to write the responses for each question. Please be sure to answer the questions thoroughly for maximum credit. Question 1 You will visualize the relationship between a county's median income (Medinc) and the percent of the county's population with at least a bachelor's degree (BaDegree). One should probably expect a positive relationship between Medinc and BaDegree: on average, counties with a greater percent of the population with a bachelor's degree should have relatively more workers in higher-paying jobs and thus should have a higher median income. Click Insert, then look for the XY (Scatter) group and click on this. A blank rectangular plotting area will appear on your sheet. Right-click on the blank area and then click Select Data, In the Series area, add a new series by clicking the "+"button. Add BaDegree as the x values and Medinc as the Y values. Click OK. Paste your figure onto the sheet titled "Question 1" along with a brief discussion of the relationship between BaDegree and Medinc. A B C D E F G H 1 WV County Income and Demographic Characteristics (2010-2014 2 3 County 4 Barbour County 5 Berkeley County 6 Boone County 7 Braxton County 8 Brooke County 9 Cabell County 10 Calhoun County 11 Clay County 12 Doddridge County 13 Fayette County 14 Gilmer County 15 Grant County 16 Greenbrier County 17 Hampshire County 18 Hancock County 19 Hardy County 20 Harrison County 21 Jackson County 22 Jefferson County 23 Kanawha County 24 Lewis County 25 Uncoln County 26 Logan County 27 Marion County 28 Marshall County 29 Mason County 30 McDowell County 31 Mercer County 32 Mineral County Medinc ($) 36,351 55,100 42,740 31,984 44,067 37,716 31,017 32,933 40,329 34,914 35,625 41,600 40,256 26,828 39,342 36,465 43,130 40,733 66,205 46,583 38,006 35,623 37,312 23,607 43,085 41,978 38,297 35,678 30.713 HSDegree BaDegree (%) (%) 66.8 12.3 66.1 20.3 68.5 9.1 68.7 10.8 73.3 17.1 59.7 26.4 66.9 8.9 61.1 9.4 69.2 12.7 68.4 11.8 60.9 16.6 69.0 12.6 66.0 18.2 67.5 8.7 70.2 16.8 68.0 12.2 66.9 20.2 68.0 16.2 59.8 28.3 62.7 24.9 70.4 14.9 67.1 9.1 69.0 8.5 58.7 5.8 67.4 20.9 73.0 15.9 73.3 10.1 63.0 19.0 73.7 19 FemHead MedAge (%) (years) 15.1 40.8 19.1 38.3 17.6 41.6 12.5 44.0 18.5 45.7 23.2 38.5 18.6 46,3 11.2 42.7 15.2 43.4 21.0 42.8 12.5 37.9 10.5 44.9 16.5 45.2 18.4 43.8 16.8 45.3 12.7 44.1 18.0 42.1 16.6 423 15.4 39.4 20.6 42.5 16.6 43.8 16.2 41.8 17.8 42.3 21.7 43.2 17.9 41.3 44.1 20.1 42.7 19.0 42.3 13.8 10 A B C D E F H 31 Mercer County 35,678 63.0 19.0 19.0 42.3 32 Mineral County 30,713 73.7 12.8 19.0 42.71 33 Mingo County 34,495 61.9 10.5 17.9 41.4 34 Monongalia County 46,166 52.4 38.8 15.4 29.9 35 Monroe County 38,239 67.2 13.8 19.2 46.1 36 Morgan County 37.406 65,6 16.5 11.7 45.6 37 Nicholas County 38,755 70.2 13.6 12.4 44,00 38 Ohio County 40,342 62.6 28.6 20.1 43.1 39 Pendleton County 36,052 67.0 14.0 10.2 48.7 40 Pleasants County 43,831 75.7 13.3 12.5 43.4 41 Pocahontas County 34,761 68.9 15.8 9.6 48.2 42 Preston County 45,806 69.8 12.9 11.3 42.3 43 Putnam County 55,939 66.6 23.9 14.2 41.3 44 Raleigh County 41,152 64.7 18.2 17,9 40.9 45 Randolph County 40,146 65.0 18.1 14.8 43.3 46 Ritchie County 39,118 70.1 10.4 14.5 45.1 47 Roane County 30,104 66.8 10.3 16.2 43.6 48 Summers County 35,040 68.1 13.0 13.0 46.4 49 Taylor County 39,933 68.9 15.8 19.6 42.5 50 Tucker County 38,663 73.0 13.8 8.3 51 Tyler County 39,974 76.3 10.0 15.3 45.7 52 Upshur County 39,188 67.9 15.8 13.4 41.3 53 Wayne County 37,491 65.4 12.6 19.7 42.6 54 Webster County 28,907 62.6 9.1 17.1 45.7 55 Wetzel County 38,066 72.9 10.2 16.7 45.5 56 Wirt County 37,117 76.0 10.9 17.9 44.8 57 Wood County 42,471 70.3 18.7 16.2 42.3 58 Wyoming County 34,620 64.9 9.0 13.9 42.4 59 60 All data are five year averages (2010-2014) 61 Medine - Median income of families in the county for the prior 12 months (adusted for inflation) 62 HSDegree Percent of population, aged 25 and over, with at least a high school diploma, but not a Bachelor's degree 63 BaDegree Percent of population, aged 25 and over, with at least a Bachelor's degree 473 124 F A B C D E F G H 43 Putnam County 55,939 66.6 23.9 14.2 41.31 44 Raleigh County 41,152 64.7 18.2 17.9 40.9 45 Randolph County 40,146 65.0 18.1 14.8 43.3 46 Ritchie County 39,118 70.1 10.4 14.5 45.1 47 Roane County 30,104 66.8 10.3 16.2 43.6 48 Summers County 35,040 68.1 13.0 13.0 46.4 49 Taylor County 39,933 68.9 15.8 19.6 42.5 50 Tucker County 38,663 73.0 13.8 8.3 47.3 51 Tyler County 39,974 76.3 10.0 15.3 45.7 52 Upshur County 39,188 67.9 15.8 13.4 41.3 53 Wayne County 37.491 65.4 12.6 19.7 42.6 54 Webster County 28,907 62.6 9.1 17.1 45.7 55 Wetzel County 38,066 729 10.2 16.7 45.5 56 Wirt County 37,117 76.0 10.9 17.9 44.8 57 Wood County 42,471 70.3 18.7 16.2 42.3 58 Wyoming County 34,620 649 9.0 13.9 42.4 59 60 All data are five year averages (2010-2014) 61 Medinc Median Income of families in the county for the prior 12 months (adusted for inflation) 62 HSDegree Percent of population, aged 25 and over, with at least a high school diploma, but not a Bachelor's degree 63 BaDegree = Percent of population, aged 25 and over, with at least a Bachelor's degree 54 FemHead Percent of families headed by a female, with no spouse present 65 MedApe - Median age of the county's population 56 67 Source: U.S. Census Bureau, 2010-2014 American Community Survey 68 69 70

Answer & Explanation Solved by verified expert

See Answer