In this lab, you will: Required: 1. Create three pivot tables and pivot charts. a. PivotTable1 and column chart: Raw_Materials_Description and Sum of Quantity_Purchased b. PivotTable2 and column chart: Raw_Materials_Description and Count of Purchase_Order_ID c. PivotTable3 and line chart: Purchase_Order_Date and both measures (Count of Purchase_Order_ID and Sum of Quantity_Purchased) 2. Create slicers and modify them so that the first is dashboard-level and the second is report-level. a. Slicer1: Dashboard-level Supplier_St b. Slicer2: Report-level Raw_Materials_Description (matched with PivotTable 3) 3. Work with the slicers to present different versions of the reports in the dashboard. a. Filter for only AR b. Remove the filter c. Filter for only Malt-Wheat Ask the Question: What are the different reports and charts that would be useful to place together for regular analysis? Master the Data: Apply the same steps as Lab 10-1 to the Lab 10-1 Alt Data.xlsx dataset. Software needed Excel Screen capture tool (Windows: Snipping Tool; Mac: Cmd+Shift+4) A B C D F 1 Purchase Order ID 2 3 E Raw_Materials_Code 3 2 2 Quantity_Purchased 1001 1003 Raw_Material_Purchase_Price 3 10 4 5 1 6 7 8 Supplier_ID 1009 1009 1009 1008 1009 1008 1009 1009 1009 1009 1009 1009 1 2 2 2 1 1 1 1 1003 1002 1001 1001 1001 1001 1003 1001 1001 1001 12 4 4 12 S 5 5 1 1 1 1 4. 9 10 1 3 11 12 1 12 2 2 3 13 14 15 16 3 1008 1009 1008 1009 1009 1008 2 3 1 Purchase_Order_Date Purchase_Employee_ID 20500 11/1/19 20501 11/5/19 20502 11/10/19 20503 11/13/19 2050 20504 11/16/19 20505 11/19/19 20506 11/23/19 20506 11/23/19 20507 11/26/19 20508 11/29/19 20509 12/4/19 20510 12/9/19 20511 12/12/19 20512 12/16/19 20513 12/19/19 20514 12/24/19 20515 12/28/19 20516 1/2/20 20517 1/5/20 20518 1/9/20 20519 1/14/20 20520 1/18/20 20521 1/23/20 20522 1/26/20 20523 1/31/20 20524 2/4/20 20525 2/8/20 20525 2/8/20 20525 2/8/20 20526 2/13/20 20527 2/16/20 20527 2/16/20 20528 2/21/20 20529 2/24/20 20530 2/28/20 17 18 19 20 21 11 6 7 11 11 4 11 10 12 1 1 2. 19.99 29 29 44.99 19.99 19.99 19.99 29 29 19.99 19.99 19.99 19.99 29 19.99 44.99 44.99 44.99 29 19.99 29 19.99 29 19.99 44.99 19.99 19.99 44.99 44.99 29 19.99 19.99 29 19.99 29 19.99 1009 1009 22 23 24 25 26 27 28 29 30 1001 1003 1001 1002 1002 1002 1003 1001 1003 1001 1003 1001 1002 1001 100 1001 1002 1002 1003 1001 1001 1003 1001 1008 1008 1008 1009 1009 100 1009 1008 1009 1008 1009 1009 1009 1008 100R 1 2 1 1 3 3 3 1 1 7 2 3 3 3 3 3 12 6 10 8 1 5 31 2 32 33 34 2 2 2 2 6 11 12 6 3 3 10 z 1 35 36 3 3 1003 1001 1008 7 20530 1009 3 1003 2 29 8 20531 1008 3 1003 7 29 9 20532 1008 1 1002 2 44.99 0 20533 1009 1 1002 12 44.99 1 20534 1008 2 1002 5 44.99 2 20535 1009 3 1002 7 44.99 3 20536 1009 2 1001 12 19.99 4 20537 1008 2 1003 6 29 5 20538 2/28/20 3/4/20 3/7/20 3/12/20 3/15/20 3/19/20 3/23/20 3/27/20 3/31/20 14/4/20 4/8/20 4/11/20 4/11/20 4/14/20 4/14/20 4/18/20 4/21/20 1009 1 1002 5 44.99 6 20539 1008 3 1003 6 29 7 20540 2 1001 3 19.99 1008 1009 3 20541 3 1003 10 29 9 20541 1008 3 1003 3 29 0 20542 1 6 29 1009 1009 1003 1002 20542 1 5 44.99 1 2 20543 1009 1 1002 1 44.99 44.99 3 20544 1008 2 1002 4 4 A B 1 Supplier_ID Supplier_St MN 1 wn 2 WA 4 3 AR 5 4 AR Supplier_company_name Supplier_Address Supplier_City Northern Brewery Homebrew Sur 6021 Lyndale Ave S Minneapolis Hops Direct, LLC 686 Green Valley Road Mabton The Home Brewery 455 E. Township St. Fayetteville The Payroll Company 408 N. Walton Blvd Bentonville City of Fayetteville - Water 113 West Mountain Stree Fayetteville Southwestern Gas P.O. Box 98890 Las Vegas Bob's Repair 3541 Horse Meadow Driv Fayetteville Farmer's Insurance 1943 College Avenue Fayetteville Lindsey Management Co. 1200 East Joyce Blvd. Fayetteville Supplier_Zip Supplier_Phone 55419 612-843-4444 98935 509-837-3616 72703 479-587-1440 72712 1-866-899-8439 72701 479-521-1258 877-860-6020 72703 479-345-2345 72701 479-236-0013 72703 479-521-6686 Notes Bulk Malt Supplier Bulk Hops Supplier Small batch and Speciality Ingredient Supplier Payroll Water Electric and Gas Repairs Insurance Rent 6 5 AR 7 6 NV 8 7 AR 9 8 AR 10 9 AR 11 1 Raw_Materials_Code Raw_Materials_Description 2 1001 Malt - Barley 3 1002 Malt - Wheat 4 1003 Hops 5 Raw Materials_Price $19.99 $44.99 $29.00 4 6 Required: 1. Were there any products that weren't purchased in every state (Except for Nevada (NV))? O Yes, Malt-Barley O Yes, Malt-Wheat O Yes, Hops O No - all three products sold in each of the states (except for Nevada (NV). 2. When was the peak day for purchasing Malt-Wheat from Arkansas (AR) in terms of number of purchase orders created with Arkansas suppliers? 01/11/2019 O 12/24/2019 O 12/2/2019 O 2/8/2020 3. When was the peak day for purchasing Malt-Wheat from Arkansas (AR) in terms of Quantity of Malt-Wheat purchased? O 12/24/2019 O 12/2/2019 O 1/11/2019 O 2/8/2020 4. What was the total count of purchase orders created with suppliers from Minnesota (MN)? 05 06 O 18 07 5. What was the quantity of hops purchased from suppliers from Minnesota (MN)? 42 5 18 O 116