Extend Your Skills EA4-E1 Edit an Inventory Listing and Create a PivotTable for Gameroom Suppliers In this exercise, you will compile summary data based on an inventory listing for Gameroom Suppliers. You will determine the inventory balance for a product category using three methods, and you will create a PivotTable based on the inventory listing Open EA4- E1-Inventory. Use one of Excel's built-in functions to enter the total number of product categories in an ap the total number of years for which an inventory balance is displayed (include zero balances) and the average, maximum, and minimum inventory balances in appropriate locations Then, use a function to enter the total inventory for each of the five years in the appropriate locations propriate location. For each product category, use functions to enter Determine the fifth-year inventory balance for air hockey tables using the LIFO, FIFO, and weighted average methods. Use formulas to determine the inventory balance under each. Ensure that the calculations are entered in appropriate locations on the Air Hockey Tables tab; label each calculation. Enter the LIFO inventory balance on the Inventory Listing tab as appropriate Last, create a PivotTable that displays the Year 3 inventory balances for products located in the Huntington or Smithtown warehouses and receiving at least a three-star rating. Label the new worksheet tab appropriately and use slicers to create the PivotTable. Create a PivotChart displaying the results of the PivotTable, using appropriate labeling and formatting throughout. Position the PivotChart and slicers in a logical manner 1 Gameroom Suppliers -Inventory Listing rna Inventory Ending Inventory Ending inventory EndInginventory Endin Inventory Number Avera einventory Moormum Inventory Minimum nw ta Balance- Year 1 Number Balace-Year5 of Years Air Hockey Tables Huntingtan Stars Stars 5 Stars 17 Gare Room Characters 5 Star 4Stars 532 5 Stars $ 3 Sta$ Stars Star Stars 10,920 5 3Stars Stars S 7,140 Kines Park 11,340 Stars S 10,360 $ 3Stars 2 ClearanceMachines Stars S Stars 10,360 12,040 10,850 6,020$ StarsS 4 Stars 1,690 ,360 S 1 Air Hockey Tables 2 Inventory Costing Method Comparison 4 Methods to be used 5 Last-In, First-Out 6 First-In, First-Out 7Weighted Average Units in Ending Inventory 18 Cost per Unit Total Units 15 18 10 Bceinning 11 Purchase #1 12 Purchase #2 13 Purchase#3 14 Purchase R4 15 $140 2,100 $142 2,556 5 1465 1,168 5147$ 1,764 5148 2,516 $ 10,104 12 17 7D Extend Your Skills EA4-E1 Edit an Inventory Listing and Create a PivotTable for Gameroom Suppliers In this exercise, you will compile summary data based on an inventory listing for Gameroom Suppliers. You will determine the inventory balance for a product category using three methods, and you will create a PivotTable based on the inventory listing Open EA4- E1-Inventory. Use one of Excel's built-in functions to enter the total number of product categories in an ap the total number of years for which an inventory balance is displayed (include zero balances) and the average, maximum, and minimum inventory balances in appropriate locations Then, use a function to enter the total inventory for each of the five years in the appropriate locations propriate location. For each product category, use functions to enter Determine the fifth-year inventory balance for air hockey tables using the LIFO, FIFO, and weighted average methods. Use formulas to determine the inventory balance under each. Ensure that the calculations are entered in appropriate locations on the Air Hockey Tables tab; label each calculation. Enter the LIFO inventory balance on the Inventory Listing tab as appropriate Last, create a PivotTable that displays the Year 3 inventory balances for products located in the Huntington or Smithtown warehouses and receiving at least a three-star rating. Label the new worksheet tab appropriately and use slicers to create the PivotTable. Create a PivotChart displaying the results of the PivotTable, using appropriate labeling and formatting throughout. Position the PivotChart and slicers in a logical manner 1 Gameroom Suppliers -Inventory Listing rna Inventory Ending Inventory Ending inventory EndInginventory Endin Inventory Number Avera einventory Moormum Inventory Minimum nw ta Balance- Year 1 Number Balace-Year5 of Years Air Hockey Tables Huntingtan Stars Stars 5 Stars 17 Gare Room Characters 5 Star 4Stars 532 5 Stars $ 3 Sta$ Stars Star Stars 10,920 5 3Stars Stars S 7,140 Kines Park 11,340 Stars S 10,360 $ 3Stars 2 ClearanceMachines Stars S Stars 10,360 12,040 10,850 6,020$ StarsS 4 Stars 1,690 ,360 S 1 Air Hockey Tables 2 Inventory Costing Method Comparison 4 Methods to be used 5 Last-In, First-Out 6 First-In, First-Out 7Weighted Average Units in Ending Inventory 18 Cost per Unit Total Units 15 18 10 Bceinning 11 Purchase #1 12 Purchase #2 13 Purchase#3 14 Purchase R4 15 $140 2,100 $142 2,556 5 1465 1,168 5147$ 1,764 5148 2,516 $ 10,104 12 17 7D