Instructions Before doing anything, enter your student number (one digit per cell) in cells B2 through I2!!!! Student ID # 1 1 1 1 1 1 1 1 3 Points Extra Credit Due Date: 5:00 PM on 4/17/20 Normal Due Date: 5:00 PM on 4/24/20 Late Penalty: 7 points per weekday Requirements: 1) use the flat file in the Data tab to construct pivot tables to answer the 10 questions below 2) use the flat file to construct an E-R diagram and design a database 3) create the database in Excel (see the sample table below) READ THIS CLOSELY! Link cash receipt or cash disbursement to inventory in the E-R diagram (do not create a cash resource). Turn in the following on Blackboard: 1) Include your name and CWID on the first page of output. 2) the pivot tables (with grand totals) 3) the excel worksheet with the relational database (show all tables with data entered into each table) Questions: 1) Vend_name, vend#, total receiving quantity and total payable amount by vendor (sort by vend_name). 2) Recv#, payable amount and (receiving quantity x list_price) for each receiving transaction. (Mac users can't get 100% correct, produce the most accurate answer you can) 3) Item desc, item#, empl#, total purchase quantity for all purchase transactions handled by employee 200. 4) The average quantity per purchase transaction displayed at 2 decimals. Your answer should be one number. (Mac users can't get 100% correct, produce the most accurate answer you can) 5) Item desc, item#, smallest receiving quanity, largest receiving quantity, smallest payable amount and largest payable amount for each item. 6) Recv# and (receiving quantity - purchase quantity) for all receiving transactions where (receiving quantity - purchase quantity) 0. 7) Total receiving quantity and total disbursement amount for purchases from vendors 60 and 65. One set of totals for the two vendors combined. 8) The number of purchase transactions, the number of receiving transactions and the number of cash disbursment transactions. 9) Vend_name, vend# and purchase quantity for all purchases handled by employee 201. 10) Vend_name, vend#, payable amount and disbursement amount for all vendors with total disbursements greater than $60,000. item# desc 100 cavalier 101 ranger 102 lesabre 103 lancer 104 punto 105 taurus 101 ranger 100 cavalier 102 lesabre 104 punto 103 lancer 105 taurus 100 cavalier 101 ranger 105 taurus 102 lesabre 101 ranger 100 cavalier 104 punto 101 ranger list_price 251 307 904 406 69 603 307 251 904 69 406 603 251 307 603 904 307 251 69 307 Purchase Data Receiving Data po# date1 quantity1 empl# empl_name recv# date2 quantity2 300 1/1/2020 66 200 Schrute 500 1/2/2020 65 301 1/4/2020 73 201 Beesly 501 1/5/2020 73 301 1/4/2020 50 201 Beesly 501 1/5/2020 49 302 1/10/2020 56 200 Schrute 502 1/11/2020 56 302 1/10/2020 37 200 Schrute 502 1/11/2020 36 303 1/16/2020 33 200 Schrute 503 1/17/2020 33 304 1/19/2020 29 200 Schrute 504 1/20/2020 29 305 1/22/2020 79 200 Schrute 505 1/23/2020 78 305 1/22/2020 32 200 Schrute 505 1/23/2020 32 306 1/28/2020 41 201 Beesly 506 1/29/2020 41 307 1/31/2020 72 201 Beesly 507 2/1/2020 72 308 2/3/2020 44 201 Beesly 508 2/4/2020 43 308 2/3/2020 77 201 Beesly 509 2/4/2020 77 309 2/9/2020 26 200 Schrute 510 2/10/2020 26 310 2/12/2020 67 200 Schrute 511 2/13/2020 67 311 2/15/2020 57 200 Schrute 512 2/16/2020 56 312 2/18/2020 55 200 Schrute 513 2/22/2020 55 313 2/21/2020 71 201 Beesly 513 2/22/2020 71 314 2/22/2020 51 201 Beesly 514 2/23/2020 51 315 2/23/2020 77 200 Schrute 515 2/24/2020 77 $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ payable empl# empl_name 16,478 203 Howard 22,411 202 Hudson 43,853 202 Hudson 22,736 202 Hudson 2,509 202 Hudson 19,899 203 Howard 8,814 203 Howard 19,578 202 Hudson 29,217 202 Hudson 2,829 203 Howard 29,232 203 Howard 25,929 202 Hudson 19,327 203 Howard 7,982 203 Howard 40,401 202 Hudson 50,624 202 Hudson 16,885 202 Hudson 17,821 202 Hudson 3,519 203 Howard 23,639 203 Howard Cash Disbursement Data check# date3 700 1/9/2020 701 1/12/2020 701 1/12/2020 702 1/18/2020 702 1/18/2020 703 1/24/2020 704 1/27/2020 705 1/30/2020 706 1/31/2020 707 2/6/2020 708 2/8/2020 709 2/11/2020 710 2/11/2020 711 2/18/2020 712 2/21/2020 713 2/23/2020 714 2/29/2020 714 2/29/2020 715 3/1/2020 716 3/2/2020 $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ amount empl# empl_name vend# vend_name 16,478 204 Vance 50 Initech 22,104 205 Kapoor 55 Ewing 43,853 205 Kapoor 55 Ewing 22,330 204 Vance 65 Gekko 2,509 204 Vance 65 Gekko 19,296 205 Kapoor 75 Krusty 8,814 205 Kapoor 70 Virtucon 19,327 205 Kapoor 60 Bluth 29,217 205 Kapoor 60 Bluth 2,760 205 Kapoor 75 Krusty 29,232 204 Vance 75 Krusty 25,929 204 Vance 65 Gekko 19,327 204 Vance 65 Gekko 7,982 205 Kapoor 50 Initech 40,401 205 Kapoor 70 Virtucon 50,624 204 Vance 60 Bluth 16,885 204 Vance 55 Ewing 17,821 204 Vance 55 Ewing 3,519 205 Kapoor 50 Initech 23,639 205 Kapoor 50 Initech