Trina's Trinkets Inc. (Trina's) is a corporation incorporated and headquartered in Orem, Utah. Trina's sells more than 3,600 different types of small trinkets and gifts, primarily to the end consumer, but also to wholesalers. Trina's has operated in Utah for the past 15 years. The company made a strategic decision to target expansion of its sales into specific geographic regions outside of Utah as well. To that end, Trina's is now selling in several nearby states, including Arizona, Idaho, Montana, Washington and Wyoming. Trina's plans to expand further throughout the western United States in the coming years. 

Margins are slim for this business, ranging from 5% to 15% of sales before state sales taxes are determined. The industry is extremely competitive and Trina's faces competition from many online companies that are not required to pay state sales tax. Because of the high competition (which is different than most companies), Trina's does not feel that it can increase prices to collect sales tax, and instead sales taxes come out of the margins. Given the tight margins and relatively high costs to manufacture in the United States, Trina is considering moving some of her manufacturing to Mexico and then purchasing warehouse space in Arizona. She has also considered trying to sell her products through Amazon.com. She continues to consider how her operations can positively impact rural areas. 

Trina's has struggled to compute sales taxes correctly for each jurisdiction. Trina's hired you, a tax advisor, to answer a variety of sales-tax-related questions and create a system (or tool) to compute sales taxes for the business. Additionally, Trina is interested to know how the potential operational changes she is considering would affect her sales tax collection obligations. Trina's has already extracted the data and provided you with several tables to help address its questions. 

Data:

Project#5.xlsx is a Microsoft Excel file that contains six tables of output from Trina's sales system. The six tables are:

SalesTransactions: contains a listing of the items ordered for each invoice, the quantity ordered and the price per item. Item prices can fluctuate based on the sale (for example, sometimes discounts are given) so there is not a uniform price for each item. Note that negative numbers in the quantity field indicate returns. 

Invoice: contains the date and the customer ID associated with the invoice. Each invoice is to a single customer. 

Products: lists the 3,675 different products that the business sells. Each product has a unique stock code.

Customer: lists the 4,363 customers who have done business with Trina's. The file lists the name of the key contact person (whether individual or business) and their zip code. Trina did not send you the address for each customer or other information about their businesses to protect this sensitive information. 

ZipCodes: A file that contains information about each zip code in the states in which Trina's operates. 

CombinedTaxRate: lists the combined rate for the jurisdiction as well as the state tax rate, county rate, city rate and any additional special rates, if applicable.

Requirements:

1. Load Project#5.xlsx data into Tableau. On the Data Source tab, click Update Now to preview your data and verify that it loaded correctly. Adjust your data types so they will be correctly interpreted by Tableau.

Dates: InoviceDate

Geographic Role - State/Provice: State, TaxRegionName 

Geographic Role - Zip code: ZipCode 

Geographic Role - City: City

Number (decimal): StateRate, EstimatedCombinedRate, EstimatedCountyRate, EstimatedCityRate, EstimatedSpecialRate, SquareMiles, UnitPrice

Number (Whole): Population, Quantity

String: all other variables

1. To calculate sales tax for each transaction, we need to join SalesTransactions table and CombiendTaxRate table. However, we cannot directly join these two tables using foreign key. We will build the bridge via other tables. Fill the table below for foreign key to join related tables.

 

• 
  Using the foreign keys from Requirement 2, join these tables in Tableau. Screenshot and paste your bar chart of total sales by state onto answer sheet.
• Create a map tree to visualize average combined tax rate by state. (Hint: Name Sheet: Average Combined Tax Rate by State; Rows: Average of CombinedTaxRate; Columns: State; Show Me: Map Tree; Label: Show Mark Labels)
• Based on your visual created from Requirement 4, which state has the highest tax rate and which state has the lowest tax rate?
• Create a column chart to visualize total sales by state. (Hint: Name Sheet: Total Sales by State; Rows: Sum of SalesRevenue; Columns: State; Label: Show Mark Labels). You need to first create a calculated field, name it SalesRevenue, and create the calculation UnitPrice*Quantity.
• Based on the bar chard created from Requirement 6, list the top 2 states with the highest sales.
• Finally, create a side-by-side column chart to visualize sales tax payments to each state in each quarter. (Hint: Name Sheet: Sales Tax by State and Quarter; Rows: Sum of SalesTax; Columns: Quarter of Invoice Date, State; Label: Show Mark Labels). You need to first create a new calculated field, name it SalesTax, and create the calculation SalesRevenues*EstimatedCombinedRate.

Table Name Sales Transactions Invoice Customers Foreign Key Join Foreign Key Table Name Invoice Customers CombinedTaxRate