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.

Required:

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

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

H 1 supplier_st AR WA WA MN MN WA MN MN MN AR MN WA AR AR WA A B F G 1 Purchase_Order_IC - Purchase_Order_Date Purchase_Employee_II - Supplier_IC- Quantity_Purchased Raw Material_Purchase_Pric raw_materials_description 2 20500 43770 1009 3 3 19.99 Malt - Barley 3 20501 43774 1009 2 10 29 Hops 4 20502 43779 1009 2 6 29 Hops 5 20503 43782 1008 1 12 44.99 Malt - Wheat 6 20504 43785 1009 1 4 19.99 Malt - Barley 7 20505 43788 1008 2 12 19.99 Malt - Barley 8 20506 43792 1009 1 5 19.99 Malt - Barley 9 20506 43792 1009 1 1 29 Hops 10 20507 43795 1009 1 1 19.99 Malt - Barley 11 20508 43798 1009 3 4 19.99 Malt - Barley 12 20509 43803 1009 1 12 19.99 Malt - Barley 13 20510 43808 1009 2 11 19.99 Malt - Barley 14 20511 43811 1008 3 6 29 Hops 15 20512 43815 1009 3 7 19.99 Malt - Barley 16 20513 43818 1008 2 11 44.99 Malt - Wheat 17 20514 43823 1009 3 11 44.99 Malt - Wheat 18 20515 43827 1009 1 4 44.99 Malt - Wheat 19 20516 43832 1008 1 11 29 Hops 20 20517 43835 1009 2 10 19.99 Malt - Barley 21 20518 43839 1009 1 12 29 Hops 22 20519 43844 1008 2 1 19.99 Malt - Barley 23 20520 43848 1008 1 12 29 Hops 24 20521 43853 1008 3 6 19.99 Malt - Barley 25 20522 43856 1009 3 10 44.99 Malt - Wheat 26 20523 43861 1009 1 8 19.99 Malt - Barley 27 20524 43865 1009 2 1 19.99 Malt - Barley 28 20525 43869 1008 3 5 44.99 Malt - Wheat 29 20525 43869 1009 3 2 44.99 Malt - Wheat 30 20525 43869 1008 3 6 29 Hops 31 20526 43874 1009 2 11 19.99 Malt - Barley 32 20527 43877 1009 2 12 19.99 Malt - Barley 33 20527 43877 1009 2 6 29 Hops AR MN MN WA MN WA MN AR AR MN WA supplier_city Fayetteville Mabton Mabton Minneapolis Minneapolis Mabton Minneapolis Minneapolis Minneapolis Fayetteville Minneapolis Mabton Fayetteville Fayetteville Mabton Fayetteville Minneapolis Minneapolis Mabton Minneapolis Mabton Minneapolis Fayetteville Fayetteville Minneapolis Mabton Fayetteville Fayetteville Fayetteville Mabton Mabton Mabton AR AR AR WA WA WA Supplier_ID - Quantity_Purchased 1 3 3 3 supplier_st MN AR AR AR AR MN MN WA AR 3 1 1 2 3 2 Purchase_Order_ID - Purchase_Order_Date Purchase_Employee_ID 34 20528 43882 1008 35 20529 43885 1008 36 20530 43889 1008 37 20530 43889 1009 38 20531 43894 1008 39 20532 43897 1008 40 20533 43902 1009 41 20534 43905 1008 42 20535 43909 1009 43 20536 43913 1009 44 20537 43917 1008 45 20538 43921 1009 46 20539 43925 1008 47 20540 43929 1008 48 20541 43932 1009 49 20541 43932 1008 50 20542 43935 1009 51 20542 43935 1009 52 20543 43939 1009 53 20544 43942 1008 54 Raw_Material_Purchase_Price raw_materials_description 3 19.99 Malt - Barley 10 29 Hops 2 19.99 Malt - Barley 2 29 Hops 7 29 Hops 2 44.99 Malt - Wheat 12 44.99 Malt - Wheat 5 44.99 Malt - Wheat 7 44.99 Malt - Wheat 12 19.99 Malt - Barley 6 29 Hops 5 44.99 Malt - Wheat 6 29 Hops 3 19.99 Malt - Barley 10 29 Hops 3 29 Hops 6 29 Hops 5 44.99 Malt - Wheat 1 44.99 Malt - Wheat 4 44.99 Malt - Wheat supplier_city Minneapolis Fayetteville Fayetteville Fayetteville Fayetteville Minneapolis Minneapolis Mabton Fayetteville Mabton Mabton Minneapolis Fayetteville Mabton Fayetteville Fayetteville Minneapolis Minneapolis Minneapolis Mabton 2 1 WA WA MN AR WA AR AR MN MN MN WA 3 2 3 3 1 1 1 2 H 1 supplier_st AR WA WA MN MN WA MN MN MN AR MN WA AR AR WA A B F G 1 Purchase_Order_IC - Purchase_Order_Date Purchase_Employee_II - Supplier_IC- Quantity_Purchased Raw Material_Purchase_Pric raw_materials_description 2 20500 43770 1009 3 3 19.99 Malt - Barley 3 20501 43774 1009 2 10 29 Hops 4 20502 43779 1009 2 6 29 Hops 5 20503 43782 1008 1 12 44.99 Malt - Wheat 6 20504 43785 1009 1 4 19.99 Malt - Barley 7 20505 43788 1008 2 12 19.99 Malt - Barley 8 20506 43792 1009 1 5 19.99 Malt - Barley 9 20506 43792 1009 1 1 29 Hops 10 20507 43795 1009 1 1 19.99 Malt - Barley 11 20508 43798 1009 3 4 19.99 Malt - Barley 12 20509 43803 1009 1 12 19.99 Malt - Barley 13 20510 43808 1009 2 11 19.99 Malt - Barley 14 20511 43811 1008 3 6 29 Hops 15 20512 43815 1009 3 7 19.99 Malt - Barley 16 20513 43818 1008 2 11 44.99 Malt - Wheat 17 20514 43823 1009 3 11 44.99 Malt - Wheat 18 20515 43827 1009 1 4 44.99 Malt - Wheat 19 20516 43832 1008 1 11 29 Hops 20 20517 43835 1009 2 10 19.99 Malt - Barley 21 20518 43839 1009 1 12 29 Hops 22 20519 43844 1008 2 1 19.99 Malt - Barley 23 20520 43848 1008 1 12 29 Hops 24 20521 43853 1008 3 6 19.99 Malt - Barley 25 20522 43856 1009 3 10 44.99 Malt - Wheat 26 20523 43861 1009 1 8 19.99 Malt - Barley 27 20524 43865 1009 2 1 19.99 Malt - Barley 28 20525 43869 1008 3 5 44.99 Malt - Wheat 29 20525 43869 1009 3 2 44.99 Malt - Wheat 30 20525 43869 1008 3 6 29 Hops 31 20526 43874 1009 2 11 19.99 Malt - Barley 32 20527 43877 1009 2 12 19.99 Malt - Barley 33 20527 43877 1009 2 6 29 Hops AR MN MN WA MN WA MN AR AR MN WA supplier_city Fayetteville Mabton Mabton Minneapolis Minneapolis Mabton Minneapolis Minneapolis Minneapolis Fayetteville Minneapolis Mabton Fayetteville Fayetteville Mabton Fayetteville Minneapolis Minneapolis Mabton Minneapolis Mabton Minneapolis Fayetteville Fayetteville Minneapolis Mabton Fayetteville Fayetteville Fayetteville Mabton Mabton Mabton AR AR AR WA WA WA Supplier_ID - Quantity_Purchased 1 3 3 3 supplier_st MN AR AR AR AR MN MN WA AR 3 1 1 2 3 2 Purchase_Order_ID - Purchase_Order_Date Purchase_Employee_ID 34 20528 43882 1008 35 20529 43885 1008 36 20530 43889 1008 37 20530 43889 1009 38 20531 43894 1008 39 20532 43897 1008 40 20533 43902 1009 41 20534 43905 1008 42 20535 43909 1009 43 20536 43913 1009 44 20537 43917 1008 45 20538 43921 1009 46 20539 43925 1008 47 20540 43929 1008 48 20541 43932 1009 49 20541 43932 1008 50 20542 43935 1009 51 20542 43935 1009 52 20543 43939 1009 53 20544 43942 1008 54 Raw_Material_Purchase_Price raw_materials_description 3 19.99 Malt - Barley 10 29 Hops 2 19.99 Malt - Barley 2 29 Hops 7 29 Hops 2 44.99 Malt - Wheat 12 44.99 Malt - Wheat 5 44.99 Malt - Wheat 7 44.99 Malt - Wheat 12 19.99 Malt - Barley 6 29 Hops 5 44.99 Malt - Wheat 6 29 Hops 3 19.99 Malt - Barley 10 29 Hops 3 29 Hops 6 29 Hops 5 44.99 Malt - Wheat 1 44.99 Malt - Wheat 4 44.99 Malt - Wheat supplier_city Minneapolis Fayetteville Fayetteville Fayetteville Fayetteville Minneapolis Minneapolis Mabton Fayetteville Mabton Mabton Minneapolis Fayetteville Mabton Fayetteville Fayetteville Minneapolis Minneapolis Minneapolis Mabton 2 1 WA WA MN AR WA AR AR MN MN MN WA 3 2 3 3 1 1 1 2