Ay Yourse 121 Apply Your Skills EA4-A1 Edit an Inventory Listing and Create a PivotTable for Educational Endeavors this exercise, you will complesummary data based on an inventory Listing for Educational d vors. Then you will determine the entry balance for a product category using three methods. Last, you will create a Pivot Table and a PivotChart. 1. Open EA4-A1-Inventory from your Chapter 04 folder and save it as EA4-A1-Inventory Your Name) 2. Use a function to display the total number of product categories in cell B2. 3. Populate the cells as described for the Construction Paper product category. In this cell Use a function to display the Cell 15 Number of years for which an inventory balance is displayed Cell Average inventory balance for the five years shown Maximum inventory balance for the five years shown Cell M5 Minimum inventory balance for the five years shown Hint: Make sure the formula in cell J5 will update property of any currently blank cells are later filled in 4. Copy the formulas in the range JS:MS and paste them such that the calculations will be performed for all product categories 5. Enter formulas in the range 163-163 to add the inventory balances for each year. Cells Use Inventory Costing Methods 6. Switch to the Construction Paper tab. 7. Use the CONCATENATE function to enter LIFO Inventory Balance in cell A17 8. Enter a formula in cell B17 to calculate the ending inventory balance for construction paper under the LIFO method. 9. In cell A18, use the CONCATENATE function to enter FIFO Inventory Balance 10. Enter a formula in cell B18 to calculate the ending inventory balance for construction paper under the FIFO method. 11. In cell A19, use the CONCATENATE function to enter WA Inventory Balance 12. Enter a formula in cell B19 to calculate the ending inventory balance for construct paper under the weighted average method formula in the appropriate location on the Inventory Listing tab to die fifth-year LIFO inventory balance for construction paper 14. Apply the 3 Symbols (Uncircled) icon set and Red - White - Green colores range K5:K62 2 Excel for Accounting Chapter 4: Inventory Costing and Analysis Insert a Pivot Table and a PivotChart 15. Create a Pivot Table on the Inventory Listing tab based on the inventory balances displayed for all five years. 16. Rename the new worksheet tab containing the PivotTable. 17. Display the Year 5 Inventory Balances within the Pivot Table with: Country of Manufacture in the Rows area Product Category in the Columns area A filter that excludes the Active product department 18. Add a slicer that limits the data to product categories that earned at least a 3 of 5 online rating 19. Create a 3-D Clustered Column PivotChart and display the Online Rating category in the legend. 20. Position the PivotChart and slicer in a logical manner. 21. Save and dose the file. Ay Yourse 121 Apply Your Skills EA4-A1 Edit an Inventory Listing and Create a PivotTable for Educational Endeavors this exercise, you will complesummary data based on an inventory Listing for Educational d vors. Then you will determine the entry balance for a product category using three methods. Last, you will create a Pivot Table and a PivotChart. 1. Open EA4-A1-Inventory from your Chapter 04 folder and save it as EA4-A1-Inventory Your Name) 2. Use a function to display the total number of product categories in cell B2. 3. Populate the cells as described for the Construction Paper product category. In this cell Use a function to display the Cell 15 Number of years for which an inventory balance is displayed Cell Average inventory balance for the five years shown Maximum inventory balance for the five years shown Cell M5 Minimum inventory balance for the five years shown Hint: Make sure the formula in cell J5 will update property of any currently blank cells are later filled in 4. Copy the formulas in the range JS:MS and paste them such that the calculations will be performed for all product categories 5. Enter formulas in the range 163-163 to add the inventory balances for each year. Cells Use Inventory Costing Methods 6. Switch to the Construction Paper tab. 7. Use the CONCATENATE function to enter LIFO Inventory Balance in cell A17 8. Enter a formula in cell B17 to calculate the ending inventory balance for construction paper under the LIFO method. 9. In cell A18, use the CONCATENATE function to enter FIFO Inventory Balance 10. Enter a formula in cell B18 to calculate the ending inventory balance for construction paper under the FIFO method. 11. In cell A19, use the CONCATENATE function to enter WA Inventory Balance 12. Enter a formula in cell B19 to calculate the ending inventory balance for construct paper under the weighted average method formula in the appropriate location on the Inventory Listing tab to die fifth-year LIFO inventory balance for construction paper 14. Apply the 3 Symbols (Uncircled) icon set and Red - White - Green colores range K5:K62 2 Excel for Accounting Chapter 4: Inventory Costing and Analysis Insert a Pivot Table and a PivotChart 15. Create a Pivot Table on the Inventory Listing tab based on the inventory balances displayed for all five years. 16. Rename the new worksheet tab containing the PivotTable. 17. Display the Year 5 Inventory Balances within the Pivot Table with: Country of Manufacture in the Rows area Product Category in the Columns area A filter that excludes the Active product department 18. Add a slicer that limits the data to product categories that earned at least a 3 of 5 online rating 19. Create a 3-D Clustered Column PivotChart and display the Online Rating category in the legend. 20. Position the PivotChart and slicer in a logical manner. 21. Save and dose the file