Part II solutions:extract, transform and load relevant data

• Based on Trina's operations and state sales tax collection requirements, consider how the tax rate data might need tobe transformedconsidering the origin-based or destination-based implications.
• Utah is an origin-based state and, therefore, the tax rates for all ZIP codes listed for Utah need tobe transformedto reflect the tax rates for Ephraim, Utah, where the sales transactions originate for Utah-based transactions (ZIP code 84627).

• Therefore, the state and ZIP code field should remain the same, but the following fieldsshould be modifiedto reflect the Ephraim rates shown above:TaxRegionName,StateRate,EstimatedCombinedRate,EstimatedCountyRate,EstimatedCityRateandEstimatedSpecialRate.
• An important missionofTrina's is to serve individuals in rural areas. As such, theBoard ofDirectors and important stakeholders would like more information abouttheirinfluence inrural areas. For this case, ruralis definedas an area with less than 100,000 people. To simplify this analysis, areasare definedas having the same first three digits of theirZIPcode.
• Add a ruralvariable to theZipCodestab indicating whether eachZIPcode isassociated with a rural city (1) oranurban city (0).
• First, identify and groupZIPcodes as areas with the same first three digits using the Left function: =Left(A2, 3)
• Second, although you have optionsforassigningcategories, one easy way to do it is to pivot table using the three-digit code as your row label and summing the population. Youthencan sort your pivot table (right-click on theRowLabels arrow and selectMore Options and selectDescending by Sum of Population.

• Next,assign the category "Rural" to anythree-digit code with the sum ofitspopulationless than100,000 and "Urban" to all others. Use this categorization as avlookuptable to assign the rural vs. urban category to the entire dataset.
• If desired, remove the space before the name of each state in the State field in theZipCodestab. We have left this space in for the solution set provided. However, following are a couple of options
• You can use the Data/Text to Columns tool.
• You can use the TRIM function. However, note that this is a non-breaking space,so you will need to use the following formula: =TRIM(SUBSTITUTE (TEXT, CHAR(160), CHAR(32)))
• Once loadedin Tableau, the data source screen should show the following diagramshown below.
• Only one student in each group should upload the Tableau file to Blackboard. All students will upload their completed Tableau project at the end of the semester.

• A nexus determinationreflectswhether the state has a connection with an out-of-state(remote)taxpayer such that the state has a constitutional right to impose a tax.

Utah-nexus applies

Arizona-nexusdoesn'tapply

Idaho-nexusdoesn'tapply

Montana-nexusdoesn'tapply

Washington-nexus applies

Wyoming-nexus applies