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 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_Ta ble 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: 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 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. Required: 1. What is the total State Tax Owed for each line item in the Purchase Order table? $687.21 $841.67 $5446.16 $8.41 2. What is the state tax owed on Purchase Order_ID 20510? $42.25 $14.29 $95.00 $21.80 3. What is the state sales tax owed on Purchase Order_ID 20525? (Note- there are multiple line items on this invoice Round if necessary) on Purchase_Order_ID 20525? (Note- there are multiple line items on this invoice. Round if necessary.) $14.62 $0.20 $31.78 $19.50 4. What is the state sales tax rate for Minnesota? 0.04 0.0725 006875 0.06875 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? One cell Full column It depends Entire table array Purchase Order ID Purchase Order Date Purchase Employee ID Supplier ID Quantity Purchased Raw Material Purchase Price raw materials description supplier w 20500 43770 1009 19.99 Malt Barley AR 20501 43774 1009 10 29 Hops WA 20502 43779 1009 2 6 29 Hops WA 20503 43782 1008 12 44.99 Malt Wheat MN 20504 43785 1009 4 19.99 Malt Barley MN 20505 43788 1008 2 12 19.99 Malt Barley WA 20506 43792 1009 5 19.99 Malt Barley MN 20506 43792 1009 29 Hops MN 20507 43795 1009 I 19.99 Malt Barley MN 20508 43798 1009 4 19.99 Malt Barley AR 20509 43803 1009 12 19.99 Malt Barley MN 20510 43808 1009 11 19.99 Malt Barley WA 20511 43811 1008 3 6 29 Hops AR 20512 43815 1009 7 19.99 Malt Barley AR 20513 43818 1008 2 11 44.99 Malt Wheat WA 20514 43823 1009 11 44.99 Malt-Wheat AR 20515 43827 1009 4 44.99 Malt-Wheat MN 20516 43832 1008 11 29 Hops MN 20517 43835 1009 21 10 19.99 Malt-Barley WA 20518 43839 1009 12 29 Hops MN 20519 43844 1008 2 1 19.99 Malt-Barley WA 20520 43848 1008 12 29 Hops MN 20521 43853 1008 3 6 19.99 Malt Barley AR 20522 43856 1009 10 44.99 Malt Wheat AR 20523 43861 1009 19.99 Malt Barley MN 20524 43865 1009 2 19.99 Malt-Barley WA 20525 43869 1008 5 44.99 Malt Wheat AR 20525 43869 1009 3 2 44.99 Malt- Wheat AR 20525 43869 1008 3 6 29 Hops AR 20526 43874 1009 2 11 19.99 Malt-Barley WA 20527 43877 1009 12 19.99 Malt- Barley WA 20527 43877 1009 2 6 29 Hops WA 20528 43882 1008 3 19.99 Malt- Barley MN 20529 43885 1008 3 10 29 Hops AR 20530 43889 1008 3 2 19.99 Malt Barley AR 20530 43889 1009 3 2 29 Hops AR 20531 43894 100K 3 7 29 Hops ART 205321 43897 1008 2 44.99 Malt Wheat MN 20533 43902 1009 12 44.99 Malt Wheat IMN 20534 43905 1008 2 S 44.99 Malt-Wheat WA 20535 43909 1009 31 7 44.99 Malt Wheat AR 20536 43913 1009 2 12 19.99 Malt Barley WA 20537 43917 1008 6 29 Hops WA 20538 43921 1009 5 44.99 Malt Wheat MN 20539 43925 1008 3 6 29 Hops AR 20540 43929 1008 3 19.99 Malt-Barley WA 20541 43932 1009 3 10 29 Hops AR 20541 43932 1008 3 3 29 Hops AR 20542 43935 1009 29 Hops MN 20542 43935 1009 44.99 Malt-Wheat MN 20543 43939 1009 44.99 Malt Wheat MN 20544 43942 1008 44.99 Malt Wheat WA raw materials_description Malt-Barley AR Hops WA Hops WA Malt Wheat MN Malt-Barley MN Malt-Barley WA Malt-Barley MN Hops MN Malt-Barley MN Malt Barley AR supplier st supplier city Fayetteville Mabton Mabton Minneapolis Minneapolis Mabion Minneapolis Minneapolis Minneapolis Fayetteville state State Tax Rate AL AK AZ 0.04 AR CA FL Malt Barley MN Minneapolis HI Malt Barley WA Mabton ID IL Hops AR Malt Barley AR Malt Wheat WA Malt Wheat AR Malt Wheat MN Hops MN Fayetteville Fayetteville Mabton Fayetteville Minneapolis Minneapolis IN IA Malt Barley WA Mabton Hops MN Malt Barley WA Minneapolis Mabton Hops MN Malt-Barley AR Minneapolis Fayetteville Malt Wheat AR Malt Barley MN Malt Barley WA Malt-Wheat AR Malt Wheat AR Hops AR Fayetteville Minneapolis Mabton Fayetteville Fayetteville Fayetteville Malt Barley WA Mabton Malt-Barley WA Mabton Hops WA Mabton Malt Barley MN Minneapolis Hops AR Fayetteville Malt-Barley AR Fayetteville Hops AR Fayetteville Hops AR Malt Wheat MN Fayetteville Minneapolis Malt-Wheat MN Minneapolis Malt Wheat WA Mabton Malt-Wheat AR Fayetteville Malt-Barley WA Mabton Hops WA Mabton Malt-Wheat MN Minneapolis Hops AR Fayetteville Malt Barley WA Mabton Hops AR Fayetteville Hops AR Fayetteville Hops MN Minneapolis Malt Wheat MN Minneapolis Malt Wheat MN Minneapolis * 58CHW=WZ 2 9 5 = 5 9 9 5 2 7 898 8 8 8 8 8 8 2 4 5 5 5 5 3 5 58 0 0.056 0.065 0.0725 CO 0.029 CT 0.0635 DE 0 0.06 GA 0.04 0.04 0.06 0.0625 0.07 0.06 KS 0.065 KY 0.06 LA 0.05 ME 0.055 MD 0.06 MA 0.0625 MI 0.06 MN 0.06875 MS 0.07 MO 0.04225 MT 0 NE 0.055 NV 0.0685 NH 0 NI NM 0.06625 0.05125 NY NC 0.04 0.0475 ND 005 0.0575 0.045 0 PA 0.06 RI 0.07 SC 0.06 SD 0.045 TN TX 0.07 0.0625 0.0595 0.06 VA 0.053 WA 0065 WV 0.06 WI 0.05 WY 0.04 DC 0.0575 Malt Wheat WA Mabton