LINK TO DATA: https://drive.google.com/file/d/1iEnkkjt6M7DGwuhvu6HAhgNff3EeZL0H/view?usp=sharing

The file TaxData contains information from federal tax returns filed in 2007 for all counties in the United States (3,142 counties in total). Use the data-visualization methods presented in this chapter to explore these data on CH3 file and discover relationships between the variables. Include the following in your report: 1. First add two new columns to compute Average Adjusted Gross Income and Average Wages and Salaries Income per return (report/family), both in thousands, for each county (Hint: average income would be total income divided by total number of tax returns). 2. Create a frequency distribution, cumulative relative frequency distribution, and histogram for Average Adjusted Gross Income per return in U.S. counties. Use bin sizes of 5. Interpret the results. Do any data points appear to be outliers in this distribution (Hint: if you find any row with measurement errors, you should remove them and update the data set)? 3. Create a PivotTable in Excel to answer the questions below. The PivotTable should have State Abbreviation as Row Labels. The Values in the PivotTable should be the sum of adjusted gross income for each state. a. Sort the PivotTable data to display the states with the smallest sum of adjusted gross income on top and the largest on the bottom. Which states had the smallest and largest sum of adjusted gross income, respectively? What is the total adjusted gross income for federal tax returns filed in these two states? (Hint: To sort data in a PivotTable in Excel, right-click any cell in the PivotTable that contains the data you want to sort, and select Sort.) b. Show the values in your Pivot Table as percentage of grand total. How many percent of the total adjusted gross income in the United States was provided by the state of California? What percentage by Texas? c. Add the County Name to the Row Labels in the PivotTable. Sort the County Names by Sum of Adjusted Gross Income with the lowest values on the top and the highest values on the bottom. Filter the Row Labels so that only the state of Arizona is displayed. Which county had the smallest sum of adjusted gross income in the state of Arizona? Which county had the largest sum of adjusted gross income in the state of Arizona? 4. Create a bar chart to compare the top 10 U.S. states in terms of their Total Adjusted Gross Income (in thousands). Sort the data for a nicer demonstration. The file TaxData contains information from federal tax returns filed in 2007 for all counties in the United States (3,142 counties in total). Use the data-visualization methods presented in this chapter to explore these data on CH3 file and discover relationships between the variables. Include the following in your report: 1. First add two new columns to compute Average Adjusted Gross Income and Average Wages and Salaries Income per return (report/family), both in thousands, for each county (Hint: average income would be total income divided by total number of tax returns). 2. Create a frequency distribution, cumulative relative frequency distribution, and histogram for Average Adjusted Gross Income per return in U.S. counties. Use bin sizes of 5. Interpret the results. Do any data points appear to be outliers in this distribution (Hint: if you find any row with measurement errors, you should remove them and update the data set)? 3. Create a PivotTable in Excel to answer the questions below. The PivotTable should have State Abbreviation as Row Labels. The Values in the PivotTable should be the sum of adjusted gross income for each state. a. Sort the PivotTable data to display the states with the smallest sum of adjusted gross income on top and the largest on the bottom. Which states had the smallest and largest sum of adjusted gross income, respectively? What is the total adjusted gross income for federal tax returns filed in these two states? (Hint: To sort data in a PivotTable in Excel, right-click any cell in the PivotTable that contains the data you want to sort, and select Sort.) b. Show the values in your Pivot Table as percentage of grand total. How many percent of the total adjusted gross income in the United States was provided by the state of California? What percentage by Texas? c. Add the County Name to the Row Labels in the PivotTable. Sort the County Names by Sum of Adjusted Gross Income with the lowest values on the top and the highest values on the bottom. Filter the Row Labels so that only the state of Arizona is displayed. Which county had the smallest sum of adjusted gross income in the state of Arizona? Which county had the largest sum of adjusted gross income in the state of Arizona? 4. Create a bar chart to compare the top 10 U.S. states in terms of their Total Adjusted Gross Income (in thousands). Sort the data for a nicer demonstration