Essay Policymakers – The Balance Between Revenue Needs and Adequate Service Provision – Gross Migration – Economics Assignment Help

Assignment Task:

Project Background
Policy makers at all levels must attempt to optimize the balance between revenue needs and adequate service provision. To help illustrate the dilemma faced by policy makers, consider the extremes. In one extreme, a government could avoid collecting taxes by providing no services to the citizens. In this case, citizens would hold the responsibility for determining how to privately fund services that support civilization, including infrastructure, education, libraries, health care, support for low income citizens, protection from natural and man-made disasters, regulatory oversight, etc. On the other extreme, a government could collect all monies made by constitutions, but provide all services citizens require, including housing, food, clothing, transportation, etc. Currently, governments and citizens operate somewhere in the middle, thus the need to balance between revenue needs and adequate service provision.

One major item that influences policy makers’ abilities to achieve an optimal balance between revenue needs and adequate service provision is migration patterns. The current and future constituency base determines both how much tax revenue an area could reasonably collect as well as the types of services that the government should seek to provide. For example, assume that a local jurisdiction attracts a high number of families, just starting out, with dependent minor children. This jurisdiction may not be able to generate large levels of tax revenue if these families are in the early stages of their career and have significant obligations typically incurred by young families (e.g., mortgages, student loans, car loans, etc.). The policy makers, who face constrained budgets need to support services typically desired by families with young children and limited financial freedom, including parks, daycares, and public education institutions.

At the other extreme, assume that a local jurisdiction attracts a high number of retirees. This jurisdiction may or may not be able to generate large levels of tax revenue, depending on the adequacy of the constituents’ fixed-income and savings. However, the policy makers of this jurisdiction need to use its’ tax revenue to provide services desirable to retirees, including medical facilities, transportation, leisure activities, etc.

Astute policy makers understand that the optimal balance between revenue needs and adequate service provision depends on both the current constituency base and anticipated changes to the constituency base. Data analysis of migration patterns based on income tax data can aid policy makers in getting a better understanding of the potential tax revenue that could be collected as well as in guiding their spending decisions. In this case, you have the opportunity to consider how you would assist policy makers, through data analytics.

Data Sets Resource
The data sets used in this project are available on the website of the Statistics of Income (SOI) Tax Stats, U.S. Population Migration Data.

This project requires students to extract, transform, load, and then analyze publicly available data sets from 2011 through 2018. You will find the specific data sets in each of the seven year hyperlinks (2011- 2012, 2012-2013, 2013-2014, 2014-2015, 2015-2016, 2016-2017, and 2017-2018). For each of the SEVEN years, you will need to download: (1) the Migration Data by State State’s file; and, (2) the Gross Migration File Individual Excel File. You will find it beneficial to store all of these files in a single folder for this project.

Data Sets Description
The IRS Individual Master File (IMF) data was extracted from all returns filed by late September of the filing (calendar) year. The file includes data from 95 to 98 percent of the individual income tax filing population. While migration data is available from 1990 forward, the SOI took over the responsibility of managing the migration data from the U.S. Census Bureau. In doing so, the SOI changed the method of collecting and reporting data. As a result, we limit the analysis to only the years where the migration data was being collected and reported by the SOI. Migration Data Users Guide and Record Layouts are available by year for those that wish to understand more about the collection methods, data reported, and record layouts. A brief description of these files is mentioned below.

Migration Data by State State’s Excel File
The Migration Data by State State’s Excel File data sets are separated by the direction of migration, inflow or outflow, and by state level or county level. We will be using ONLY the state level inflow and outflow files for this project. Each of these datasets contains seven main variables.

The first two variables pertain to Federal Information Processing Standards (FIPS) codes regarding State of Destination and State of Origin. The other five variables are State Abbreviations, State Name of Origin, Number of Returns, Number of Exemptions, and Aggregate Adjusted Gross Income (AGI). We will use these variables to evaluate the direction of migration between states and the potential impact that it has financially.

Gross Migration File
The Gross Migration File Individual Excel File contains data by year on Gross Migration for Selected Income Items by State, Size of Adjusted Gross Income, and Age of Taxpayer by state. The Size of Adjusted Gross Income is reported for the state as a whole and 7 sub-groupings for each state: $1 under $10,000; $10,000 under $25,000; $25,000 under $50,000; $50,000 under $75,000; $75,000 under $100,000; $100,000 under $200,000; and $200,000 or more. The data is reported for five groups of taxpayers: Total returns; Non-migrant returns (those citizens who remained in the state and did not move within the state that year); Outflow returns (those citizens who moved to another state that year); Inflow returns (those citizens who moved into the state that year); and Same States returns (those citizens who remained in the state but moved within the state that year).

The Age of Taxpayer is reported in aggregate and 6 subgroupings: Under 26; 26 under 35; 35 under 45; 45 under 55; 55 under 65; and 65 and over. There are four main variables reported under each age grouping, number of returns, number of exemptions, adjusted gross income (AGI) for the years covered in the dataset. We will use these variables to evaluate the pattern of net migration rates from 2011 through 2018 for each of the states, by age group AND AGI.

Requirements
1) Required Technology
To complete this project, you will need the following. Obtaining and installing the necessary programs can take a week or more; hence, please plan accordingly.

1. Access to a PC or Mac computer. Mac users will need to install programs that allow them to run Windows environment on their machines for Alteryx.
2. Excel.
3. Tableau. Free licenses can be obtained through Tableau academics.
4. Alteryx. Free licenses can be obtained through Alteryx for Good.

2) Required Data
You need to pick ONE state ONLY. In general, each state should be used by ONE student ONLY; thus, choice of the state will be on a first come – first serve basis. You need to send me the name of the state that has been selected as early as possible. The latest date to inform me about the state name is on June 10th at 11:30PM. States will be reserved for those who send me their emails first.

Instructions
Part (1)
Imagine that you are helping the policy makers of a particular state understand the migration patterns of their constituents. They have first asked you to understand both where their constituents are migrating to, and where they are immigrating from. To do so, use the Migration Data by State State’s Excel Files that you downloaded. Make sure to use ONLY the state level inflow and outflow files (NOT the county inflow and outflow).

Using Alteryx & Excel
1. Combine the State Outflow and State Inflow tabs for each year. Hint: You will have to clean up headers and rename variables using Alteryx in order to accomplish this combination.
2. Create three new variables:
a. Net Migration: Compute the net migration using the following adaptation of the SOI’s formula. Subtract the number of out-migrant returns from the number of in- migrant returns.
b. Net Exemption: Repeat step (a) for number of exemptions.
c. Net AGI: Repeat step (a) for Adjusted Gross Income (AGI).

3. Keep the following variables: State code, State, Net Migration, Net Exemption, and Net AGI.
4. Combine the data that was retained by year.
5. Compute 7 year averages for the Net Migration, the Net Exemption, and the Net AGI.
6. Export this file to Excel.

Using Tableau & Excel
1. Develop a heat map visualization that illustrates the 7 year average Net Migration, the Net Exemption, and the Net AGI for each state.
a. Color code the heat map such that states with positive variables (e.g., net migration, net exemption, and net AGI) are blue, negative variables are red, and neutral rates (those with relatively small positive/negative rates) are beige.
b. Ensure appropriate labeling for each of these three heat maps.
c. Combine all heat maps into one dashboard.

2. In Excel, develop a trend line showing the variables (one per chart) for the 5 states that had the highest seven-year average net migration.
3. In Excel, develop a yearly trend line showing the variables for the 5 states that had the lowest sevenyear average net migration.

Part (2)
Imagine that the policy makers are also interested in understanding migration patterns by age and AGI categories for the state you have selected. To do so, use the Gross Migration Files that you downloaded

Using Alteryx & Excel
1. Limit the Gross Migration File for each year to the state you have selected.
2. Create three new variables in total, by age groups, and by AGI:
a. Net Migration: Compute the net migration using the adaptation of the SOI’s formula by subtracting the number of out-migrant returns from the number of in- migrant returns.
b. Net Exemption: Repeat step (a) for number of exemptions.
c. Net AGI: Repeat step (a) for Adjusted Gross Income (AGI).

3. Combine the data for all years.
4. Compute 7 year averages for the Net Migration, the Net Exemption, and the Net AGI in total, by age groups, and by year.
5. Export this file to Excel.

Using Tableau
1. Develop visualizations and dashboards to effectively summarize and communicate this information to the policy makers.

Part (3)
1. Now that you have done the work, it is time to present the information to the policy makers. Prepare
a presentation, using only Tableau, to communicate relevant information to the policy makers. Be prepared to answer questions during your presentation.

2. Prepare a two-page executive summary report (not including visualizations or references) for the policy makers.

Potential Considerations and Research Questions to be answered as a portion of Part (3)
1. Explore other relationships and posit causation by correlation with a catalyst event.
2. Identify economic impact on your state by year. These relationships will show where the returns flowed and the magnitude of the flows by state. By further comparing our analysis year over year, we will be able to fit a visualization that could identify trends in the migration by state.
3. What, if any, is/was the economic impact of major natural disasters that have impacted your state between the years of 2011 and 2018?
4. Which direction of migration has/had the greatest economic impact on your state? Inflow or Outflow?
5. What households migrate based on age and household income?
6. What might this mean for policy makers in terms of tax revenue collections and service provisions?

Grading Rubric
The grading rubric is located on the following page.