Edit an Inventory Listing and Create a PivotTable for Zeke's Hardware In this exercise, you will compile summary data based on an inventory listing for Zeke's Hardware. Then you will determine the inventory balance for a product category using three methods, and you will create a PivotTable based on this inventory listing. Open EA4-E3-Inventory. Use one of Excel's built-in functions to enter the total number of product categories in an appropriate location. For each product category, use functions to enter 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 Determine the fifth-year inventory balance for sanding accessories using the LIFO, FIFO, and Ensure that the calculations are entered in appropriate locations on the Sanding Accessories tab; label the calculations. Enter the LIFO inventory balance on the Inventory Listing tab as appropriate Last,create a Pivot Table that displays the Year 1 inventory balances for products in depart ments 1A, 2B, or 3C and with internal classifications of Yellow or Blue. Label the new work- sheet 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 Zeke's Hardware -Inventory Listing Ending nvontory Ending nventoryEnding inventory Ending Inventory Ending Inventory Number Avorage inwentary Maximum Inventory Minimum Inventory Product Category Bafance-Year 2 Balance-Vear Balane-Year5 ofYears 7 Grinding Accessories 18 19 Specialty Power Accessuries Specialty Power Taols 936 S 3 Oiting Tools 14,329 19,106 $ 20,061$ 19,344 5,493 3,105 2,102 44 Sockets & Ratchets 14,568 $ 12,657$ 5,493 8,359 4,418 9 18,150 15,046 21,733 S 21,016$ 18,509$ 3,016 $ 59 Precision Hand Tools 5,732 13,852 2,152$ 2,381 1 Sanding Accessories 2 Inventory Costing Method Comparison 4 Methods to be used 5 Last-In, First-Out 6 First-In, First-Out 7 Weighted Average Units in Ending Inventory: 14 Cost per Units Unit Total 10 11 12 13 14 15 Beginning Inventory Purchase #1 Purchase #2 Purchase #3 Purchase #4 $58 522 $60720 $61 1,098 $63$ 252 $65$ 715 $3,307 12 18 4 54 Edit an Inventory Listing and Create a PivotTable for Zeke's Hardware In this exercise, you will compile summary data based on an inventory listing for Zeke's Hardware. Then you will determine the inventory balance for a product category using three methods, and you will create a PivotTable based on this inventory listing. Open EA4-E3-Inventory. Use one of Excel's built-in functions to enter the total number of product categories in an appropriate location. For each product category, use functions to enter 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 Determine the fifth-year inventory balance for sanding accessories using the LIFO, FIFO, and Ensure that the calculations are entered in appropriate locations on the Sanding Accessories tab; label the calculations. Enter the LIFO inventory balance on the Inventory Listing tab as appropriate Last,create a Pivot Table that displays the Year 1 inventory balances for products in depart ments 1A, 2B, or 3C and with internal classifications of Yellow or Blue. Label the new work- sheet 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 Zeke's Hardware -Inventory Listing Ending nvontory Ending nventoryEnding inventory Ending Inventory Ending Inventory Number Avorage inwentary Maximum Inventory Minimum Inventory Product Category Bafance-Year 2 Balance-Vear Balane-Year5 ofYears 7 Grinding Accessories 18 19 Specialty Power Accessuries Specialty Power Taols 936 S 3 Oiting Tools 14,329 19,106 $ 20,061$ 19,344 5,493 3,105 2,102 44 Sockets & Ratchets 14,568 $ 12,657$ 5,493 8,359 4,418 9 18,150 15,046 21,733 S 21,016$ 18,509$ 3,016 $ 59 Precision Hand Tools 5,732 13,852 2,152$ 2,381 1 Sanding Accessories 2 Inventory Costing Method Comparison 4 Methods to be used 5 Last-In, First-Out 6 First-In, First-Out 7 Weighted Average Units in Ending Inventory: 14 Cost per Units Unit Total 10 11 12 13 14 15 Beginning Inventory Purchase #1 Purchase #2 Purchase #3 Purchase #4 $58 522 $60720 $61 1,098 $63$ 252 $65$ 715 $3,307 12 18 4 54