Pies"R"Us is a family run company located in Barrie, Ontario engaged in the manufacture and sale of various baked goods, primarily apple pies and other pies. The company has a modern 110,000 square foot bakery (manufacturing facility). With 200 dedicated full-time employees, it has been making desserts for customers across North America and beyond since 1975. Pies"R"Us has a manufacturing facility that makes various kinds of baked goods under its own brand [Pies"R"Us] and under brand name of large stores such as Valle Mart, ValleMart US, People Choice, Wholesum Meals, Trader Stan, Cost Less Co, being some of them. Refer to Pies"R"Us HANDOUT (Dataset) for listing of closing inventory of baked goods as at October 31, 2022. The Dataset provided contains the following fields: Item # Unique Stock Keeping Unit (SKU) number for each category of inventory Item Description Name of inventory SKU, including name of retailer for which packaged Customer Name of the retailer Quantity Number of cases of each type of inventory Cost Cost per case Value Total carrying cost of inventory (Quantity x Cost) Batch Made Date on which the batch of inventory was manufactured Required: 1) Using the Inventory by Item #tab, determine the total quantity and value of the inventory (using the SUM function) 2) Insert a column between Item # (column A) and Quantity (column C) on the Inventory Item # tab. In cell B6 type Item Description. Using the Vlookup function, insert the product name from the Inventory list tab into column B based on the Item # in column A on the Inventory by item# tab. Cell B6 should say Pastry Disks Pies"R"Us 70G. Copy the vlookup formula to cell B98. 3) Insert a column between the Item Description (column B) and Quantity (column C) on the Inventory Item # tab. In cell C6 type Customer. Using the Vlookup function, insert the customer name from the Inventory list tab into column C based on the Item # in column A on the Inventory by item# tab. Cell C7 should be Pies"R"Us. Copy the vlookup formula to cell C98. Submit a copy of your Excel file. 4) Using Pivot tables, answer the following questions. Include a screen shot of your Pivot table in your submission for each question. Microsoft's snipping tool can be used for screen shots. a) Which Item has the highest inventory value? Sort the value highest to lowest in the pivot table. State the item description, quantity and value of the item in your submission. Take a screen shot of the pivot table results. b) What is the value of inventory by customer? Take a screen shot of the pivot table results. c) insert a bar chart next to the pivot table answer chart based on b) value of inventory by customer above. Take a screen shot of your bar graph. 5) Based on inventory listing, state a question you want to answer. Create a pivot chart to answer your questions. In your submission, include the question you have and a copy of the pivot table you created to solve answer your question 1 2 3 4 5 A Pies"R"Us B C D PBC Year ended October 31, 2022 Closing inventory listing-Baked Goods Provided by client Item # Quantity Cost Value Batch made 7 10422 30 27.07 $ 812.04 11-Sep-22 8 10425 163 22.47 $ 3,662.61 25-Jul-22 9 10426 61 10.58 $ 645.38 9-Oct-22 10 10430 480 20.99 $ 10,074.72 28-Sep-22 11 10432 489 24.58 $ 12,018.64 20-Sep-22 12 10434 300 17.66 $ 5,297.10 8-Oct-22 13 10456 287 30.77 $ 8,830.13 13-Oct-22 14 10460 6,218 15.44 $ 95,993.48 9-Sep-22 15 10468 1,091 22.71 $ 24,779.88 8-Oct-22 16 10469 323 16.44 $ 5,311.41 10-Oct-22 17 10471 486. 10.65 $ 5,175.90 10-Oct-22 18 10479 518 10.39 $ 5,380.98 9-Sep-22 19 10515 50 15.39 $ 769.40 15-Oct-22 20 10528 2,548 18.16 $ 46,271.68 21-Oct-22 21 10537 4,124 21.57 S 88,954.68 24-Oct-22 22 10539 3,743 23.18 S 86,773.97 14-Oct-22 23 10570 660 22.25 $ 14,685.66 26-Sep-22 24 10587 48 31.40 $ 1,507.01 28-Jul-22 25 10589 2,786 24.98 $ 69,585.92 9-Sep-22 26 10596 892 10.72 $ 9,562.24 11-Oct-22 27 10600 83 33.32 $ 2,765.23 18-Sep-22 28 10631 39 11.43 $ 445.58 18-Jul-22 29 10632 667 30 10687 69 31 10821 205 12.66 $ 8,444.89 28-Aug-22 31.62 $ 2,181.71 11.35 $ 2,327.57 30-Jul-22 15-Oct-22 32 10866 20 10.88 $ 217.60 16-Oct-22 33 11434 100 16.53 $ 1,653.40 10-Sep-22 34 11443 3,521 14.78 $ 52,054.46 10-Oct-22 35 11445 240 21.73 $ 5,214.96 30-Jul-22 36 11446 885 21.86 $ 19,346.99 11-Oct-22 37 11448 900 16.47 $ 14,822.10 12-Oct-22 38 11451 2,084 14.81 S 30,870.29 17-Oct-22 39 11454 7 18.55 $ 129.87 16-Jul-22 40 11460 502 41 11469 596 14.71 $ 7,381.91 17.58 $ 10,475.30 19-Aug-22 10-Oct-22 42 11486 2,301 19.00 S 43,716.70 19-Oct-22 4 B D 43 11530 515 22.19 $ 11,427.34 17-Oct-22 44 11554 2 23.38 $ 46.75 12-Oct-22 45 11596 1,514 11.78 $ 17,836.43 10-Sep-22 48 11628 1,559 23.59 $ 36,781.49 12-Sep-22 47 11632 892 12.29 $ 10,961.79 16-Oct-22 48 11728 4,417 12.81 $ 56,590.60 15-Sep-22 49 11730 369 32.21 S 11,885.49 14-Sep-22 50 12426 32 11.40 S 364.80 12-Oct-22 51 12434 217 19.82 $ 4,301.81 7-Sep-22 52 12436 74 26.13 $ 1,933.47 11-Jul-22 53 12459 41 15.72 $ 644.48 17-Oct-22 54 12460 697 16.09 $ 11,215.43 11-Oct-22 55 12468 332 25.60 $ 8,500.201 6-Oct-22 58 12538 3,530 24.98 $ 88,186.46 18-Sep-22 57 12630 342 21.66 $ 7,407.38 29-Oct-22 58 12632 650 12.56 $ 8,164.65 11-Oct-22 50 12821 611 11.72 $ 7,162.14 29-Jul-22 60 13430 356 26.63 $ 9,478.86 13-Oct-22 61 14530 181 39.62 $ 7,170.50 22-Oct-22 82 14579 79 20.18 $ 1,594.22 21-Oct-22 63 14821 631 20.43 $ 12,889.44 14-Sep-22 64 14906 622 19.32 $ 12,017.04 20-Oct-22 05 15006 999 18.03 $ 18,012.97 12-Sep-22 86 15429 49 25.23 $ 1,236.27 27-Jul-22 87 15430 810 41.11 5 33,297,48 15-Oct-22 68 15441 274 24.49 $ 6,709.44 17-Sep-22 60 15447 411 24.49 $ 10,063.34 13-Sep-22 70 15476 78 20.29 S 1,582.46 9-Sep-22 71 15485 141 28.65 $ 4,039.09 26-Oct-22 72 15488 2,773 36.94 $ 102,429.07 9-Oct-22 73 15496 386 33.43 $ 12,902.44 18-Oct-22 74 15514 204 27.67 5 5,645.09 23-Oct-22 75 15530 1,079 29.68 S 32,025.80 14-Oct-22 76 15596 1,432 19.28 $ 27,603.23 24-Oct-22 77 15632 610 21:26 $ 12,971.04 10-Sep-22 78 15694 79 21.095 1,666.43 27-Oct-22 79 15694 16 33.88 $ 542.14 16-Oct-22 80 15821 504 18.25 $ 9,200.02 30-Oct-22 81 15934 417 18.47 $ 7,702.41 17-Oct-22 82 16006 302 20.07 $ 6,059.93 11-Oct-22 83 16096 892 37.82 $ 33,734.55 15-Oct-22 84 16494 5,743 24.63 $ 141,432.86 12-Sep-22 84 16494 5,743 24.63 $141,432.86 12-Sep-22 85 16539 18 26.65 $ 479.77 10-Oct-22 86 16721 1,200 18.67 $ 22,401.60 25-Oct-22 87 18434 300 88 18471 28 89 18539 91 14.23 $ 24.00 $ 2,184.18 13-Oct-22 18.04 $ 5,413.20 16-Oct-22 398.33 15-Oct-22 828& 22328 90 18621 356 24.00 $ 8,543.64 14-Oct-22 91 18632 181 20.95 S 3,791.59 13-Oct-22 92 18866 300 21.93 $ 6,578.10 15-Oct-22 93 19423 120 94 19424 1,812 95 19431 642 96 19441 90 29.44 $ 3,532.32 39.27 $ 71,155.43 1-Sep-22 37.16 $ 23,859.29 23-Aug-22 28.61 $ 2,575.26 5-Aug-22 31-Aug-22 97 19596 1,537 22.77 $ 34,992.88 22-Aug-22 98 19632 200 23.46 $ 4,691.80 4-Aug-22