ESS 2 44. M 19.99 Malt RON 6 ORE 19.99 Malt-Barler 44.99 Malt-Whea 44.99 Malt Whe 1999 Mali Battery 19.99 Malt-Barley 19. moNNN alles 19.99 Malt-Barley 29 Hops 69 Malt Whea 3 44.99 Malt 2 o io 19 w 3 AR AR 3 1 MN 20541 20541 20542 20542 20543 20544 43932 1009 43932 1008 43935 1009 43935 1009 43939 1009 43942 1008 Awowo 29 Hops 29 Hops 29 Hops 44.99 Malt - Wheat 44.99 Malt - Wheat 44.99 Malt - Wheat Fayetteville Fayetteville Minneapolis Minneapolis Minneapolis Mabton 1 MN 1 MN WA 2 29 Hops supplier_st AR WA WA MIN MN WA MN TIR MN AR MN WA supplier_city Fayetteville Mabton Mabton Minneapolis Minneapolis Mabton Minneapolis Minneapolis Minneapolis Fayetteville Minneapolis Mabton Fayetteville Fayetteville Mabton Fayetteville Minneapolis AR AR Minneapolis 1 Purchase_Order_ID Purchase_Order_Date Purchase_Employee_ID Supplier_ID Quantity_Purchased Raw_Material_Purchase_Price raw_materials_description 20500 437701009 19.99 Malt-Barley 20501 43774 1009 2 10 29 Hops 20502 43779 1009 5 20503 43782 1008 1 12 44.99 Malt - Wheat 20504 43785 1009 1 19.99 Malt-Barley 7 20505 43788 1008 2 12 19.99 Malt-Barley B 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 s 29 Hops 15 20512 43815 1009 3 7 19.99 Malt-Barley 16 20513 43818 1008 Z 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 20519 43839 1009 1 12 29 Hops 22 20519 43844 1008 Z 1 19.99 Malt-Barley 20520 43848 1008 1 12 29 Hops 24 20521 43853 1008 3 b 19.99 Malt-Barley 25 20522 43856 1009 3 10 44.99 Malt Wheat 26 20523 43861 1009 1 19.99 Malt-Barley 27 20524 43865 1009 2 1 19.99 Malt. Harley 28 20525 43869 1008 3 5 44.99 Malt - Wheat 29 20525 43869 1009 3 2 44.99 Malt Wheat SU 20525 43869 1008 3 6 29 Hops 31 20526 43874 1009 2 11 19.99 Malt - Rarley 32 20527 43877 1009 2 12 19.99 Malt-Barley 33 20527 43877 1009 2 6 29 Hops 34 20528 43882 1008 1 3 19.99 Malt-Barley 35 20529 43885 1008 3 10 29 Hops 36 20530 43889 1008 3 2 19.99 Malt-Barley 37 20520 43889 1009 3 2 29 Hops 38 20531 43894 1008 s 7 29 Hops 39 20532 43897 1008 1 2 44.99 Malt Wheat 40 20533 43902 1009 1 12 44.99 Malt - Wheat 41 20534 43905 1908 2 5 44.99 Malt - Wheat 42 20535 43909 1009 S 7 44.99 Mall - Wheat 43 20526 43913 1009 2 12 19.99 Malt Barley 44 20537 43917 1008 Z 45 20529 439211009 5 44.99 Malt Wheat 20539 43925 1008 s 6 29 Hops 20540 43929 1008 2 3 19.99 Malt Barley 40 20541 43932 1009 3 10 29 Hops WA AR MIN MIN WA MIN WA MIN AR AR MIN WA AR AR AR WA WA WA MN AR AR . AR Mabton Minneapolis Mabton Minneapolis Fayetteville Fayetteville Minneapolis Mabtan Fayetteville Fayetteville Fayetteville Mabtan Mabton Mabtan Minneapolis Fayetteville Fayetteville Fayetteville Fayetteville Minneapolis Minneapolis Mabtan Fayetteville Mabtan Mabton Minneapolis Fayetteville Mabtan Fayetteville MN MN WA AR 29 Hops WA WA MIN AR WA AR 3 10 20541 20541 20542 AR AR 3 3 1 6 MN 43932 '1009 43932 1008 43935 1009 43935 1009 43939 1009 43942 1008 29 Hops 29 Hops 29 Hops 44.99 Malt - Wheat 44.99 Malt - Wheat 44.99 Malt - Wheat Fayetteville Fayetteville Minneapolis Minneapolis Minneapolis Mabton 5 20542 20543 20544 1 1 1 MN MN WA 2 4 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_Range for PO Amount, and copy it down the column (keep in mind that the columns are in a different order than they were in the first dataset). 2. Create a new column in the Purchase _Orders_Table for PO Amount, and ensure it copies down automatically. Ask the Question: What is the best format to work with the data? Master the Data: The 4-1 Alt Data is purchasing data rather than sales, so instead of working with Sales_Order_Quantity_Sold, you will work with Quantity_Purchased, and instead of working with Products, you will work with Raw_Materials. Software needed Excel Screen capture tool (Windows: Snipping Tool; Mac: Cmd+Shift+4) Data: Excel File Lab 4-1 Alt Data.xlsx. Perform the Analysis: Refer to Lab 4-1 Alternate in the text for instructions and Lab 4-1 steps for each the of lab parts. Share the Story: You have now worked with data in Excel in Ranges and in Tables and learned how Excel interacts differently with data depending on the format in which it is stored. Part 1: Upload Your Files Part 2: Assessment 1 62 SS- 5 AN AAE H . J supplier_st AR WA WA MN MN WA MN MIN MN AR MN WA AR AR WA AR MIN MN WA MN WA MIN AR AR 1 Purchase_Employee_ID 2 1009 3 1009 4 1009 5 1008 1009 7 1008 8 1009 9 1009 10 1009 11 1009 12 1009 13 1009 14 1008 15 1009 16 1008 17 1009 18 1009 19 1008 20 1009 21 1009 22 1008 23 1008 24 1008 25 1009 26 1009 27 1009 28 1008 29 1009 30 1008 31 1009 32 1009 33 1009 34 1008 35 1008 36 1008 37 1009 38 1008 39 1008 40 1009 41 1008 42 1009 43 1009 44 1008 45 1009 46 1008 47 1008 48 1009 49 1008 50 1009 51 1009 52 1009 53 1008 54 Supplier_ID Quantity_Purchased Raw_Material_Purchase_Price raw materials_description 19.99 Malt-Barley 2 10 29 Hops 2 6 29 Hops 1 12 44.99 Malt - Wheat 1 4 19.99 Malt-Barley 2 12 19.99 Malt-Barley 1 5 19.99 Malt-Barley 1 1 29 Hops 1 1 19.99 Malt-Barley 3 4 19.99 Malt-Barley 1 12 19.99 Malt-Barley 2 11 19.99 Malt-Barley 3 6 29 Hops 3 7 19.99 Malt-Barley 2 11 44.99 Malt - Wheat 3 11 44.99 Malt - Wheat 1 44.99 Malt - Wheat 1 11 29 Hops 2 10 19.99 Malt-Barley 1 12 29 Hops 2 1 19.99 Malt-Barley 1 12 29 Hops 3 6 19.99 Malt-Barley 10 44.99 Malt - Wheat 1 8 19.99 Malt-Barley 2 1 19.99 Malt-Barley 3 5 44.99 Malt - Wheat 3 2 44.99 Malt - Wheat 29 Hops 2 11 19.99 Malt-Barley 2 12 19.99 Malt-Barley 2 b 29 Hops 1 3 19.99 Malt-Barley 3 10 29 Hops 3 2 19.99 Malt-Barley 3 2 29 Hops 3 7 29 Hops 1 2 44.99 Malt - Wheat 1 12 44.99 Malt - Wheat 2 5 44.99 Malt - Wheat 3 7 44.99 Malt - Wheat 2 12 19.99 Malt-Barley Z 6 29 Hops 1 5 44.99 Malt - Wheat 3 b 29 Hops 2 3 19.99 Malt-Barley 3 10 29 Hops 3 3 29 Hops 1 b 29 Hops 1 5 44.99 Malt - Wheat 1 1 44.99 Malt - Wheat 2 4 44.99 Malt - Wheat 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 Minneapolis Fayetteville Fayetteville Fayetteville Fayetteville Minneapolis Minneapolis Mabton Fayetteville Mabton Mabton Minneapolis Fayetteville Mabton Fayetteville Fayetteville Minneapolis Minneapolis Minneapolis Mabton AR AR AR WA WA WA MN AR AR AR AR MIN MIN WA AR WA WA MN AR WA AR AR MN MN MN WA Required: 1. How many transactions have Supplier_ID 3 on them? O 18 01 7 O 98 2. One of the benefits of a table format is O it is the default format once you enter data into Excel. it automatically creates grand totals at the end of each column. O it automatically provides an absolute reference for formulas you enter manually. O it defaults to providing a filter button for each column. 3. What is the sum of the Purchase Order_Amount column? 344 O 10,358 O 1,568 O 54 4. What is the sum of the Raw_Material_Purchase_Price column for only Supplier_ID 1? 579.87 O 116 O 1,567.65 0 54 5. What is the average of the Purchase Order_Amount column? 3,722.26 199.19 206.79 O 10,357.76 ESS 2 44. M 19.99 Malt RON 6 ORE 19.99 Malt-Barler 44.99 Malt-Whea 44.99 Malt Whe 1999 Mali Battery 19.99 Malt-Barley 19. moNNN alles 19.99 Malt-Barley 29 Hops 69 Malt Whea 3 44.99 Malt 2 o io 19 w 3 AR AR 3 1 MN 20541 20541 20542 20542 20543 20544 43932 1009 43932 1008 43935 1009 43935 1009 43939 1009 43942 1008 Awowo 29 Hops 29 Hops 29 Hops 44.99 Malt - Wheat 44.99 Malt - Wheat 44.99 Malt - Wheat Fayetteville Fayetteville Minneapolis Minneapolis Minneapolis Mabton 1 MN 1 MN WA 2 29 Hops supplier_st AR WA WA MIN MN WA MN TIR MN AR MN WA supplier_city Fayetteville Mabton Mabton Minneapolis Minneapolis Mabton Minneapolis Minneapolis Minneapolis Fayetteville Minneapolis Mabton Fayetteville Fayetteville Mabton Fayetteville Minneapolis AR AR Minneapolis 1 Purchase_Order_ID Purchase_Order_Date Purchase_Employee_ID Supplier_ID Quantity_Purchased Raw_Material_Purchase_Price raw_materials_description 20500 437701009 19.99 Malt-Barley 20501 43774 1009 2 10 29 Hops 20502 43779 1009 5 20503 43782 1008 1 12 44.99 Malt - Wheat 20504 43785 1009 1 19.99 Malt-Barley 7 20505 43788 1008 2 12 19.99 Malt-Barley B 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 s 29 Hops 15 20512 43815 1009 3 7 19.99 Malt-Barley 16 20513 43818 1008 Z 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 20519 43839 1009 1 12 29 Hops 22 20519 43844 1008 Z 1 19.99 Malt-Barley 20520 43848 1008 1 12 29 Hops 24 20521 43853 1008 3 b 19.99 Malt-Barley 25 20522 43856 1009 3 10 44.99 Malt Wheat 26 20523 43861 1009 1 19.99 Malt-Barley 27 20524 43865 1009 2 1 19.99 Malt. Harley 28 20525 43869 1008 3 5 44.99 Malt - Wheat 29 20525 43869 1009 3 2 44.99 Malt Wheat SU 20525 43869 1008 3 6 29 Hops 31 20526 43874 1009 2 11 19.99 Malt - Rarley 32 20527 43877 1009 2 12 19.99 Malt-Barley 33 20527 43877 1009 2 6 29 Hops 34 20528 43882 1008 1 3 19.99 Malt-Barley 35 20529 43885 1008 3 10 29 Hops 36 20530 43889 1008 3 2 19.99 Malt-Barley 37 20520 43889 1009 3 2 29 Hops 38 20531 43894 1008 s 7 29 Hops 39 20532 43897 1008 1 2 44.99 Malt Wheat 40 20533 43902 1009 1 12 44.99 Malt - Wheat 41 20534 43905 1908 2 5 44.99 Malt - Wheat 42 20535 43909 1009 S 7 44.99 Mall - Wheat 43 20526 43913 1009 2 12 19.99 Malt Barley 44 20537 43917 1008 Z 45 20529 439211009 5 44.99 Malt Wheat 20539 43925 1008 s 6 29 Hops 20540 43929 1008 2 3 19.99 Malt Barley 40 20541 43932 1009 3 10 29 Hops WA AR MIN MIN WA MIN WA MIN AR AR MIN WA AR AR AR WA WA WA MN AR AR . AR Mabton Minneapolis Mabton Minneapolis Fayetteville Fayetteville Minneapolis Mabtan Fayetteville Fayetteville Fayetteville Mabtan Mabton Mabtan Minneapolis Fayetteville Fayetteville Fayetteville Fayetteville Minneapolis Minneapolis Mabtan Fayetteville Mabtan Mabton Minneapolis Fayetteville Mabtan Fayetteville MN MN WA AR 29 Hops WA WA MIN AR WA AR 3 10 20541 20541 20542 AR AR 3 3 1 6 MN 43932 '1009 43932 1008 43935 1009 43935 1009 43939 1009 43942 1008 29 Hops 29 Hops 29 Hops 44.99 Malt - Wheat 44.99 Malt - Wheat 44.99 Malt - Wheat Fayetteville Fayetteville Minneapolis Minneapolis Minneapolis Mabton 5 20542 20543 20544 1 1 1 MN MN WA 2 4 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_Range for PO Amount, and copy it down the column (keep in mind that the columns are in a different order than they were in the first dataset). 2. Create a new column in the Purchase _Orders_Table for PO Amount, and ensure it copies down automatically. Ask the Question: What is the best format to work with the data? Master the Data: The 4-1 Alt Data is purchasing data rather than sales, so instead of working with Sales_Order_Quantity_Sold, you will work with Quantity_Purchased, and instead of working with Products, you will work with Raw_Materials. Software needed Excel Screen capture tool (Windows: Snipping Tool; Mac: Cmd+Shift+4) Data: Excel File Lab 4-1 Alt Data.xlsx. Perform the Analysis: Refer to Lab 4-1 Alternate in the text for instructions and Lab 4-1 steps for each the of lab parts. Share the Story: You have now worked with data in Excel in Ranges and in Tables and learned how Excel interacts differently with data depending on the format in which it is stored. Part 1: Upload Your Files Part 2: Assessment 1 62 SS- 5 AN AAE H . J supplier_st AR WA WA MN MN WA MN MIN MN AR MN WA AR AR WA AR MIN MN WA MN WA MIN AR AR 1 Purchase_Employee_ID 2 1009 3 1009 4 1009 5 1008 1009 7 1008 8 1009 9 1009 10 1009 11 1009 12 1009 13 1009 14 1008 15 1009 16 1008 17 1009 18 1009 19 1008 20 1009 21 1009 22 1008 23 1008 24 1008 25 1009 26 1009 27 1009 28 1008 29 1009 30 1008 31 1009 32 1009 33 1009 34 1008 35 1008 36 1008 37 1009 38 1008 39 1008 40 1009 41 1008 42 1009 43 1009 44 1008 45 1009 46 1008 47 1008 48 1009 49 1008 50 1009 51 1009 52 1009 53 1008 54 Supplier_ID Quantity_Purchased Raw_Material_Purchase_Price raw materials_description 19.99 Malt-Barley 2 10 29 Hops 2 6 29 Hops 1 12 44.99 Malt - Wheat 1 4 19.99 Malt-Barley 2 12 19.99 Malt-Barley 1 5 19.99 Malt-Barley 1 1 29 Hops 1 1 19.99 Malt-Barley 3 4 19.99 Malt-Barley 1 12 19.99 Malt-Barley 2 11 19.99 Malt-Barley 3 6 29 Hops 3 7 19.99 Malt-Barley 2 11 44.99 Malt - Wheat 3 11 44.99 Malt - Wheat 1 44.99 Malt - Wheat 1 11 29 Hops 2 10 19.99 Malt-Barley 1 12 29 Hops 2 1 19.99 Malt-Barley 1 12 29 Hops 3 6 19.99 Malt-Barley 10 44.99 Malt - Wheat 1 8 19.99 Malt-Barley 2 1 19.99 Malt-Barley 3 5 44.99 Malt - Wheat 3 2 44.99 Malt - Wheat 29 Hops 2 11 19.99 Malt-Barley 2 12 19.99 Malt-Barley 2 b 29 Hops 1 3 19.99 Malt-Barley 3 10 29 Hops 3 2 19.99 Malt-Barley 3 2 29 Hops 3 7 29 Hops 1 2 44.99 Malt - Wheat 1 12 44.99 Malt - Wheat 2 5 44.99 Malt - Wheat 3 7 44.99 Malt - Wheat 2 12 19.99 Malt-Barley Z 6 29 Hops 1 5 44.99 Malt - Wheat 3 b 29 Hops 2 3 19.99 Malt-Barley 3 10 29 Hops 3 3 29 Hops 1 b 29 Hops 1 5 44.99 Malt - Wheat 1 1 44.99 Malt - Wheat 2 4 44.99 Malt - Wheat 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 Minneapolis Fayetteville Fayetteville Fayetteville Fayetteville Minneapolis Minneapolis Mabton Fayetteville Mabton Mabton Minneapolis Fayetteville Mabton Fayetteville Fayetteville Minneapolis Minneapolis Minneapolis Mabton AR AR AR WA WA WA MN AR AR AR AR MIN MIN WA AR WA WA MN AR WA AR AR MN MN MN WA Required: 1. How many transactions have Supplier_ID 3 on them? O 18 01 7 O 98 2. One of the benefits of a table format is O it is the default format once you enter data into Excel. it automatically creates grand totals at the end of each column. O it automatically provides an absolute reference for formulas you enter manually. O it defaults to providing a filter button for each column. 3. What is the sum of the Purchase Order_Amount column? 344 O 10,358 O 1,568 O 54 4. What is the sum of the Raw_Material_Purchase_Price column for only Supplier_ID 1? 579.87 O 116 O 1,567.65 0 54 5. What is the average of the Purchase Order_Amount column? 3,722.26 199.19 206.79 O 10,357.76