Reinforce Your Skins 119 EA4-R2 Edit an Inventory Listing and Create a PivotTable for Housewares Kingdom In this exercise, you will compile summary data based on an inventory Listing for Housewares Kingdom. Then you will determine the inventory balance for a product category using three methods. Last, you will create a Pivot Table and a Pivot Chart. 1. Open EA4-R2-Inventory from your Chapter 04 folder and save it as: EA-R2-Inventory- [Your Name) To begin, you will work with the inventory Listing tab. 2. Click in cell B2, type -COU and then double-click the Counta function, highlight the range A5:A75, and then type ) and tap Enter 3. In cell JS, type -COUNT( and then highlight the range E5:15, and then type) and tap Enter 4. Click cell KS and choose Formulas Function Library-Insert Function. 5. Click the drop-down arrow for Or Select a Category, choose Statistical and then Average, and click OK. 6. Highlight the range E5:15 and click OK 7. Select cell L5, choose Formulas-Function Library-Insert Function, type Max in the search box and click Go, choose Max and click OK, highlight the range E5:15, and then click OK 8. Select cell M5 and choose Home Editing-AutoSum -Min, highlight the range E5:15, and tap Enter 9. Highlight the range JS:M5, click the fill handle, and drag down to row 75. 10. Highlight the range E76:176 and select Home-Editing - AutoSum. Use Inventory Costing Methods 11. Choose the Wall Art tab. 12. Enter the data as indicated: Cell A17 -CONCATENATE (LEFT(AS, 1), MIDAS, 6, 1). MIDAS, 10, 1), MIDAS, 16. 1). Inventory Balance) Cell B17 -010+(4-310*c11 Cell A18 -CONCATENATR (LEFTIA, 1). MID(A6. 7. 1), MIDAS, 11, 1), MIDAS, 17, 1), - Inventory Balancer) Cell 818 -14.(4-314)*013 Cel A19 -CONCATENATE (LETT (AT, 1), NID (A7, 10, 1), - Inventory Balancer") Cell B19 (15/11594 LU Excel for Accounting Chapter 4: Inventory Costing and Analysis 13. Switch to the Inventory Listing tab and select cell 15 14. Type and then click cell B17 on the Wall Art tab and tap Eres aught the range K5:K75 and choose Home-Styles Conditional Formatting Data Bars-Gradient Fill Orange Data Bar. refree to experiment with the conditional formatting options, including those for color scale and icon sets. Clear those rules before completing step 15. Insert a Pivot Table 16. Select cell AS and choose Insert-Tables-Pivot Table 17. In the Table/Range box, replace SM576 with $1$75 and click OK. 18. Double-click the new worksheet tab and rename it Inventory Pivottable 19. In the Pivot Table Fields task pane, click in order the checkboxes for Product Turnover, Classification, and Ending Inventory Balance - Year 1. 20. In the Rows area, drag the Classification field above the Product Turnover field 21. Click the checkbox for Level 1 Department and then drag that field to the Filters area. 22. Click the drop-down arrow in cell B1, click the checkbox beside Select Multiple Items, uncheck the boxes for Lighting and Dining and click OK 23. Close the task pane and then select cell A4. 24. Choose Pivot Table Tools-Analyze-Filter-Insert Slicer, click the checkboxes for Product Turnover and Level 1 Department, and click OK 25. In the Level 1 Department slicer, choose Dcor and then use the Multi-Select button to add Furniture and Bedroom 26. In the Product Turnover slicer, choose High. Create a PivotChart 27. Click cell A4 and choose Pivot Table Tools-Analyze-Tools-PivotChart. 28. In the Bar category, choose 3-D Clustered Bar and click OK. 29. Click on a blank area of the PivotChart and drag it below the Pivot Table. 30. Choose PivotChart Tools-Analyze+Show/HideField List to display the PivotChart Fields task pane. 31. Drag the Level 1 Department field to the Legend area, position the PivotChart below the Pivot Table, and then close the task pane. 32. Position the slicers beside the Pivot Table 33. Save and close the file. Reinforce Your Skins 119 EA4-R2 Edit an Inventory Listing and Create a PivotTable for Housewares Kingdom In this exercise, you will compile summary data based on an inventory Listing for Housewares Kingdom. Then you will determine the inventory balance for a product category using three methods. Last, you will create a Pivot Table and a Pivot Chart. 1. Open EA4-R2-Inventory from your Chapter 04 folder and save it as: EA-R2-Inventory- [Your Name) To begin, you will work with the inventory Listing tab. 2. Click in cell B2, type -COU and then double-click the Counta function, highlight the range A5:A75, and then type ) and tap Enter 3. In cell JS, type -COUNT( and then highlight the range E5:15, and then type) and tap Enter 4. Click cell KS and choose Formulas Function Library-Insert Function. 5. Click the drop-down arrow for Or Select a Category, choose Statistical and then Average, and click OK. 6. Highlight the range E5:15 and click OK 7. Select cell L5, choose Formulas-Function Library-Insert Function, type Max in the search box and click Go, choose Max and click OK, highlight the range E5:15, and then click OK 8. Select cell M5 and choose Home Editing-AutoSum -Min, highlight the range E5:15, and tap Enter 9. Highlight the range JS:M5, click the fill handle, and drag down to row 75. 10. Highlight the range E76:176 and select Home-Editing - AutoSum. Use Inventory Costing Methods 11. Choose the Wall Art tab. 12. Enter the data as indicated: Cell A17 -CONCATENATE (LEFT(AS, 1), MIDAS, 6, 1). MIDAS, 10, 1), MIDAS, 16. 1). Inventory Balance) Cell B17 -010+(4-310*c11 Cell A18 -CONCATENATR (LEFTIA, 1). MID(A6. 7. 1), MIDAS, 11, 1), MIDAS, 17, 1), - Inventory Balancer) Cell 818 -14.(4-314)*013 Cel A19 -CONCATENATE (LETT (AT, 1), NID (A7, 10, 1), - Inventory Balancer") Cell B19 (15/11594 LU Excel for Accounting Chapter 4: Inventory Costing and Analysis 13. Switch to the Inventory Listing tab and select cell 15 14. Type and then click cell B17 on the Wall Art tab and tap Eres aught the range K5:K75 and choose Home-Styles Conditional Formatting Data Bars-Gradient Fill Orange Data Bar. refree to experiment with the conditional formatting options, including those for color scale and icon sets. Clear those rules before completing step 15. Insert a Pivot Table 16. Select cell AS and choose Insert-Tables-Pivot Table 17. In the Table/Range box, replace SM576 with $1$75 and click OK. 18. Double-click the new worksheet tab and rename it Inventory Pivottable 19. In the Pivot Table Fields task pane, click in order the checkboxes for Product Turnover, Classification, and Ending Inventory Balance - Year 1. 20. In the Rows area, drag the Classification field above the Product Turnover field 21. Click the checkbox for Level 1 Department and then drag that field to the Filters area. 22. Click the drop-down arrow in cell B1, click the checkbox beside Select Multiple Items, uncheck the boxes for Lighting and Dining and click OK 23. Close the task pane and then select cell A4. 24. Choose Pivot Table Tools-Analyze-Filter-Insert Slicer, click the checkboxes for Product Turnover and Level 1 Department, and click OK 25. In the Level 1 Department slicer, choose Dcor and then use the Multi-Select button to add Furniture and Bedroom 26. In the Product Turnover slicer, choose High. Create a PivotChart 27. Click cell A4 and choose Pivot Table Tools-Analyze-Tools-PivotChart. 28. In the Bar category, choose 3-D Clustered Bar and click OK. 29. Click on a blank area of the PivotChart and drag it below the Pivot Table. 30. Choose PivotChart Tools-Analyze+Show/HideField List to display the PivotChart Fields task pane. 31. Drag the Level 1 Department field to the Legend area, position the PivotChart below the Pivot Table, and then close the task pane. 32. Position the slicers beside the Pivot Table 33. Save and close the file