Required information [The following information applies to the questions displayed below.) NOTE: Throughout this lab, every time a screenshot is requested, use your computer's screenshot tool, and paste each screenshot to the same Word document. Label each screenshot in accordance to what is noted in the lab. This document with all of the screenshots included should be uploaded through Connect as a Word or PDF document when you have reached the final step of the lab. In this lab, you will: Required: 1. Create a new column in the Purchase_Orders_Table for State_Sales_Tax. Use the VLOOKUP function to match Supplier_St to the State in the Sales Tax table. 2. Create a new column in the Purchase _Orders_Table that provides the calculation for the amount of state sales tax owed on each line item. Ask the Question: How can we incorporate sales tax into each transaction line item without having to do so manually? Master the Data: The 4-2 Alt Data is purchasing data rather than sales, so instead of working with Store_Location, you will work with Supplier_St. Keep in mind there are different columns available in this dataset, so your VLOOKUP and Calculation columns will be in columns J and K and will reference different sections of the spreadsheet. Software needed Excel Screen capture tool (Windows: Snipping Tool; Mac: Cmd+Shift+4) . Data: Lab 4-2 Alt Data.xlsx. Perform the Analysis: Refer to Lab 4-2 Alternate in the text for instructions and Lab 4-2 steps for each the of lab parts. Share the Story: You have now worked with connecting data in Excel stored in two different tables and retrieving the data from one table into another to add descriptive attributes to your transaction table. Part 1: Upload Your Files Part 2: Assessment bas azesinos A. 43815 100 438... 1.1881 43842 103 4385000 1986 Sincasa .... F... Fratell 1:18 4387410 4387720 4703 Mutton . Winne Minneacole 19.99 Man 29 Hops 14.29 MinWe Required: 1. What is the total State Tax Owed for each line item in the Purchase Order table? O $687.21 O $841.67 O $8.41 O $5446.16 2. What is the state tax owed on Purchase_Order_ID 20510? O $42.25 O $95.00 O $21.80 O $14.29 3. What is the state sales tax rate owed on Purchase_Order_ID 20525? O $0.20 O $31.78 O $19.50 O $14.62 4. What is the state sales tax rate for Minnesota? O 0.06875 O 0.04 O 0.0725 O 0.065 5. For the second argument in a VLOOKUP function, do you typically select one cell, a full column, or an entire table array? O It depends One cell O Entire table array O Full column Required information [The following information applies to the questions displayed below.) NOTE: Throughout this lab, every time a screenshot is requested, use your computer's screenshot tool, and paste each screenshot to the same Word document. Label each screenshot in accordance to what is noted in the lab. This document with all of the screenshots included should be uploaded through Connect as a Word or PDF document when you have reached the final step of the lab. In this lab, you will: Required: 1. Create a new column in the Purchase_Orders_Table for State_Sales_Tax. Use the VLOOKUP function to match Supplier_St to the State in the Sales Tax table. 2. Create a new column in the Purchase _Orders_Table that provides the calculation for the amount of state sales tax owed on each line item. Ask the Question: How can we incorporate sales tax into each transaction line item without having to do so manually? Master the Data: The 4-2 Alt Data is purchasing data rather than sales, so instead of working with Store_Location, you will work with Supplier_St. Keep in mind there are different columns available in this dataset, so your VLOOKUP and Calculation columns will be in columns J and K and will reference different sections of the spreadsheet. Software needed Excel Screen capture tool (Windows: Snipping Tool; Mac: Cmd+Shift+4) . Data: Lab 4-2 Alt Data.xlsx. Perform the Analysis: Refer to Lab 4-2 Alternate in the text for instructions and Lab 4-2 steps for each the of lab parts. Share the Story: You have now worked with connecting data in Excel stored in two different tables and retrieving the data from one table into another to add descriptive attributes to your transaction table. Part 1: Upload Your Files Part 2: Assessment bas azesinos A. 43815 100 438... 1.1881 43842 103 4385000 1986 Sincasa .... F... Fratell 1:18 4387410 4387720 4703 Mutton . Winne Minneacole 19.99 Man 29 Hops 14.29 MinWe Required: 1. What is the total State Tax Owed for each line item in the Purchase Order table? O $687.21 O $841.67 O $8.41 O $5446.16 2. What is the state tax owed on Purchase_Order_ID 20510? O $42.25 O $95.00 O $21.80 O $14.29 3. What is the state sales tax rate owed on Purchase_Order_ID 20525? O $0.20 O $31.78 O $19.50 O $14.62 4. What is the state sales tax rate for Minnesota? O 0.06875 O 0.04 O 0.0725 O 0.065 5. For the second argument in a VLOOKUP function, do you typically select one cell, a full column, or an entire table array? O It depends One cell O Entire table array O Full column
