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. 417791009 200 J www.ban WA 19 Mak Barley MN WA Purchase Order Phase Order Purchase Elkayes Quantity Puchar Material, Purchase Pr 2000 43770 1009 WA Malton 2001 43774 1008 ww Ma 2050 N 201 20504 ww 2050 MN 2004 MN MN 20901 A 75009 UN 20009 WA 200-10 M AR 20542 20615 316 30517 2 20520 30521 30522 30523 20524 300 jess 20525 30536 30527 30527 11 4. Mak Whe 29 Hope how MN MON WA MN WA MN AR M MN WA AR AR 29 Hope M 13.99 Ma Barley WA 15 Marley WA 29 Ho WA Malton Malton The Order Phese ply 0 Seppler, Carly Punched Marial Price was design MN 30538 M 30539 AR 30530 20530 AK 20532 0001009 4400 Mall Whea MN 44.99 Melt Whet WA Manpo Malin 20534 M 2053 WA 205 WA Maltan 4400 Max Wheat UN M WA Malon 30540 M 20141 20541 29 Hope M 104) 29H MN 20543 44.99 Mak Whea MN 20541 44.99 Mak Wheat UN 30544 439421008 449 Mat Wheat WA Minneso Mato Required: 1. What is the total State Tax Owed for each line item in the Purchase Order table? multiple choice 1 . $8.41 Chegg Home Study tools v My courses v My books My folder Career Life 1. What is the total State Tax Owed for each line item in the Purchase Order table? multiple choice 1 . $8.41 $687.21 $841.67 $5446.16 2. What is the state tax owed on Purchase Order ID 20510? multiple choice 2 $42.25 $95.00 $21.80 $14.29 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.) multiple choice 3 $14.62 $31.78 $0.20 $19.50 4. What is the state sales tax rate for Minnesota? multiple choice 4 0.06875 . 0.04 . 0.065 . 0.0725 $95.00 $21.80 $14.29 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.) multiple choice 3 $14.62 $31.78 $0.20 $19.50 4. What is the state sales tax rate for Minnesota? multiple choice 4 0.06875 0.04 0.065 0.0725 5. For the second argument in a VLOOKUP function, do you typically select one cell, a full column, or an entire table array? multiple choice 5 . It depends .Entire table array Full column . One cell P A1 NIM-C xfx Purchase Order 10 A- 437701 20500 0500 305 43779 20504 350 39506 20508 43700 2009 20110 M 2011 42811100 20512 2013 STE 2014 J014 20 20538 +3 41044 34 30522 3052 30524 20 30525 29525 32 22 20527 2 20529 20630 29531 430141009 L General C 19 Ba 44 2H WA no " IN AN M M M ww M T A M W M M M O Cer P AL BIU- 4-A : xfx Purchase Order 10 Pure One 10 20516 10517 41413100 201 30519 305.20 438441008 24 30521 " 12 General % 9 29 M ww 20532 20535 20526 30525 20525 43853100 438561009 43063 009 43860 00 11 ww 30 A " " 1990 Ma ww 3 20525 30526 2 " 438741004 2 M " 438771009 18 Bate WA 30527 IF M 418771009 W 20538 AN 2052 APM 20 20530 M M 30530 70551 4184 100 29 20532 4307500 30533 430500 20534 43905500 30535 43009009 30536 49:31008 2017 439175008 20538 20539 43925 500 20540 43521-1000 435295008 20141 20543 30542 30643 20543 21935 2009 43919 1009 43542308 M N 405 M Wha MY Ma MA A WA MA ww M WA M M ALE W we HES s ail C Cones Required: 1. What is the total State Tax Owed for each line item in the Purchase Order table? $841.67 $8.41 $5446.16 $687.21 2. What is the state tax owed on Purchase Order ID 20510? $14.29 $21.80 Help Save & Exit Submit $42.25 $95.00 3. What is the state sales tax owed on Purchase Order ID 20525? (Note- there are multiple line items on this invoice. Round if Help Save & Exit Submit Required information 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) $14.62 $31.78 $19.50 $0.20 4. What is the state sales tax rate for Minnesota? 0.06875 0.0725 0.04 0065 Required information 0.06875 0.0725 0.04 0.065 Help Save & Exit Submit 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 Entire table array It depends Full column Required information Help Save & Exit Submit (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)