80.2K

Verified Solution

Link Copied!

Apply Your Skills EA4-A1 Edit an Inventory Listing and Create a PivotTable for Educational Endeavors In this exercise , you will compile summary data based on an inventory listing for Educational ndeavors. Then you will determine the inventory balance for a product category using three methods. Last, you will create a PivotTable and a PivotChart. 1. Open EA4-A 1-Inventory from your Chapter 04 folder and save it as: EA4-Al-Inventory-[YourName] 2. Use a function to display the total number of product categories in oe 3. Populate the cells as described for the Construction Paper product category ll B2 In this cell: Cell J5 Cell K5 Cell L5 Cell M5 Use a function to display the: Number of years for which an inventory balance is displayed Average inventory balance for the five years shown Maximum inventory balance for the five years shown Minimum inventory balance for the five years shown Hint: Make sure the formula in cell J5 will update properly if any currently blank cells are later filled in. 4. Copy the formulas in the range J5:M5 and paste them such that the calculations will be 5. Enter formulas in the range E63:163 to add the inventory balances for each year. performed for all product categories. 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 construction Enter a formula in the appropriate location on the Inventory Listing tab to display the fifth-year LIFO inventory balance for construction paper 13. ite -Green color scale to the 4. Apply the 3 Symbols (Uncircled) icon set and Red - Wh range K5:K62. Insert a PivotTable and a PivotChart 15. Create a PivotTable 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 PivotTable with: .Country of Manufacture in the Rows area . Product Category in the Columns area 18. Add a slicer that limits the data to product categories that earned at least a 3 of 5 online 19. Create a 3-D Clustered Column PivotChart and display the Online Rating category in 20. . A filter that excludes the Active product department rating the legend. Position the PivotChart and slicer in a logical manner. 21. Save and close the file. 1 Educational Endeavors-Inventory Listing Ending inventoryEnding iowentory Endling aventoryEnding nwentory Ending InwentorY Number Awerage Inventory Maximum inventary Minmum ioventary Bolance-rear1 Manu orture Department OnlineRuting lance-Year2 Balance-year 3 Balance-year 4 Bafance-Year 5 ofrears 5 Construction Pape Saft Acive Play ddiion & Subtrattion 39 Caurting&Numerey 1 Explorire Manipulative 3 Measurement&Cassitying 57 Notebooks & Pape 47,699 40 1 Construction Paper 2 Inventory Costing Method Comparison 4 Merhods to be used: 5 Lastn, Firt-out Unitsin Ending Inventory 571 t-In, First-Out 7Wcighted was Cost pe Jnits Total 5 14 6,860 15 1,505 5 17 S4,080 $18 S 1,530 $19 S 9,595 S23,9TU ning Inventory 11 | Purchase#1 2 Purchasc # 13 Purchase 13 14 Purchasc n 15 Apply Your Skills EA4-A1 Edit an Inventory Listing and Create a PivotTable for Educational Endeavors In this exercise , you will compile summary data based on an inventory listing for Educational ndeavors. Then you will determine the inventory balance for a product category using three methods. Last, you will create a PivotTable and a PivotChart. 1. Open EA4-A 1-Inventory from your Chapter 04 folder and save it as: EA4-Al-Inventory-[YourName] 2. Use a function to display the total number of product categories in oe 3. Populate the cells as described for the Construction Paper product category ll B2 In this cell: Cell J5 Cell K5 Cell L5 Cell M5 Use a function to display the: Number of years for which an inventory balance is displayed Average inventory balance for the five years shown Maximum inventory balance for the five years shown Minimum inventory balance for the five years shown Hint: Make sure the formula in cell J5 will update properly if any currently blank cells are later filled in. 4. Copy the formulas in the range J5:M5 and paste them such that the calculations will be 5. Enter formulas in the range E63:163 to add the inventory balances for each year. performed for all product categories. 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 construction Enter a formula in the appropriate location on the Inventory Listing tab to display the fifth-year LIFO inventory balance for construction paper 13. ite -Green color scale to the 4. Apply the 3 Symbols (Uncircled) icon set and Red - Wh range K5:K62. Insert a PivotTable and a PivotChart 15. Create a PivotTable 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 PivotTable with: .Country of Manufacture in the Rows area . Product Category in the Columns area 18. Add a slicer that limits the data to product categories that earned at least a 3 of 5 online 19. Create a 3-D Clustered Column PivotChart and display the Online Rating category in 20. . A filter that excludes the Active product department rating the legend. Position the PivotChart and slicer in a logical manner. 21. Save and close the file. 1 Educational Endeavors-Inventory Listing Ending inventoryEnding iowentory Endling aventoryEnding nwentory Ending InwentorY Number Awerage Inventory Maximum inventary Minmum ioventary Bolance-rear1 Manu orture Department OnlineRuting lance-Year2 Balance-year 3 Balance-year 4 Bafance-Year 5 ofrears 5 Construction Pape Saft Acive Play ddiion & Subtrattion 39 Caurting&Numerey 1 Explorire Manipulative 3 Measurement&Cassitying 57 Notebooks & Pape 47,699 40 1 Construction Paper 2 Inventory Costing Method Comparison 4 Merhods to be used: 5 Lastn, Firt-out Unitsin Ending Inventory 571 t-In, First-Out 7Wcighted was Cost pe Jnits Total 5 14 6,860 15 1,505 5 17 S4,080 $18 S 1,530 $19 S 9,595 S23,9TU ning Inventory 11 | Purchase#1 2 Purchasc # 13 Purchase 13 14 Purchasc n 15

Answer & Explanation Solved by verified expert

See Answer