I need help with this Excel assignment EA4-D4 and EA4-D5. Perform on Excel and send the screenshot please. Please help!!!
EA4-D5 IS HERE:
Develop Your Skills EA4-D4 In this exercise, you will create a Pivot Table based on the inventory listing for City Music World. You will then modify the Pivot Table to examine the inventory data. 1. Save your file as: EA4-D4-Inventory-[YourName] 2. Click cell A5 and choose Insert-> Tables-Pivot Table. 3. Modify the contents of Table/Range box so $M$84 is replaced with $1$83 and then click OK This modification excludes certain columns and the total row from the Pivot Table. 4. Right click the tab for the new sheet that has appeared and choose Rename, type Inventory PivotTable, and tap Enter 5. In the PivotTable Fields task pane, click in the checkboxes, in order, for Inventory Category, Country of Manufacture, and Ending Inventory Balance - Year 2. The Pivot Table displays each inventory category and then lists the country in which they are manufactured. A grand total is displayed at the bottom of the Pivot Table 110 Excel for Accounting Chapter 4: Inventory Casting and Analysis 6. In the Rows area in the Pivot Table Fields task pane, drag the Country of Manufactur field above Inventory Category The Pivot Table now displays each country of manufacture and the inventory categories for goods produced in these countries. Note that the Ending Inventory - Year 2 Balance column displays a suhtotal beside each country (which adds the total inventory balance for all inventory categories manufactured in that country). 7. Drag the Inventory Category field from the Rows area to the Columns area You have now pivoted the table to display a column for each inventory category. Totals for the inventory balance within each country are displayed in the final column in the table. 8. Return the Inventory Category field to the Rows area. 9. Click the Product Manager checkbox near the top of the task pane and then drag that field from the Rows area to the Filters area. Although the Product Manager field is included within the filter area, the Pivot Table currently displays the inventory balances for all product managers. Next you will exclude some of the managers from the displayed data. 10. Click the drop-down arrow in cell B1 and then click the Select Multiple Items checkbox. 11. Click to remove the checkboxes beside Clancy, Jacobs, Pouncey, and Williams and then click OK Search (AM Abbott Brown Clancy Howard Jacobs Noone Pouncey Williams Select Multiple Items OK Cancet You have now filtered the Pivot Table to display only the inventory balances associated with the four product managers whose checkboxes were not unchecked. 12. Close the Pivot Table Fields task pane and then select cell A4. By selecting cell A4, the Pivot Table Tools tabs become visible. Next you will filter the data using slicers, which are accessed through these tabs. Use Slicers to Filter Data 13. Choose Pivot Table Tools --Analyze-Filter-Insert Slicer, click in the checkboxes for Primary End User and Product Manager, and click OK. To view both slicer windows at the same time, you may need to drag one window beside the other. Notice that the Product Manager slicer highlights only the four product managers whose inventory figures are currently reflected within the Pivot Table. Creating PivotCharts 111 Multi-Select (Alt+S) 14. In the Product Manager slicer, choose Clancy. Choosing one product manager automatically deselects the others. 15. Still in the Product Manager slicer, click the Multi-Select button and then choose Pouncey Product Manager and Williams. Abbott 16. In the Primary End User slicer, choose Brown Intermediate and click the Clear Filter button. The Clear Filter button removed the Intermediate filter Clancy and restored data for all end users in the Pivot Table. Howard 17. In the Primary End User slicer, choose Jacobs Beginner Noone 18. Click the AutoFilter button in cell A3 and then click the drop-down arrow box under Select Field. Pouncey You can filter either field included in the Pivot Table Williams rows. 19. Close the AutoFilter menu by clicking outside of it. 20. Save your file. Develop Your Skills EA4-D5 Develop Your Skills EA4-D5 In this exercise, you will create a PivotChart based on your Pivot Table. You will then modify the PivotChart to display the exact inventory data that you would like to review, 1. Save your file as: EA4-D5-Inventory-[YourName] 2. Click cell A4 and choose Pivot Table Tools-AnalyzeTools --PivotChart. Creating PivotCharts 113 3. Choose Column and Clustered Column; click OK. All Charts Recent de AH AH ki Templates Column A Line Clustered Column Pie Total Bar Area XY (Scatter) i Stock Surface PivotChart Fields 4. Click a blank area of the PivotChart and drag it below the Pivot Table. 5. Choose PivotChart ToolsAnalyze+Show/ Hide->Field List to display the PivotChart Fields Choose fields to add to report: task pane. Search 6. Drag the Inventory Category field from the Axis area to the Legend area. Notice that both the Pivot Table and the Pivot Chart are updated based on this change. 7. Close the task pane and then drag each slicer below the Pivot Table 8. Save and close the file. Inventory Category Product Manager Y Country of Manufacture Primary End User Ending Inventory Balance - Year 1 Ending Inventory Balance - Year 2 Ending Inventory Balance - Year 3 Develop Your Skills EA4-D4 In this exercise, you will create a Pivot Table based on the inventory listing for City Music World. You will then modify the Pivot Table to examine the inventory data. 1. Save your file as: EA4-D4-Inventory-[YourName] 2. Click cell A5 and choose Insert-> Tables-Pivot Table. 3. Modify the contents of Table/Range box so $M$84 is replaced with $1$83 and then click OK This modification excludes certain columns and the total row from the Pivot Table. 4. Right click the tab for the new sheet that has appeared and choose Rename, type Inventory PivotTable, and tap Enter 5. In the PivotTable Fields task pane, click in the checkboxes, in order, for Inventory Category, Country of Manufacture, and Ending Inventory Balance - Year 2. The Pivot Table displays each inventory category and then lists the country in which they are manufactured. A grand total is displayed at the bottom of the Pivot Table 110 Excel for Accounting Chapter 4: Inventory Casting and Analysis 6. In the Rows area in the Pivot Table Fields task pane, drag the Country of Manufactur field above Inventory Category The Pivot Table now displays each country of manufacture and the inventory categories for goods produced in these countries. Note that the Ending Inventory - Year 2 Balance column displays a suhtotal beside each country (which adds the total inventory balance for all inventory categories manufactured in that country). 7. Drag the Inventory Category field from the Rows area to the Columns area You have now pivoted the table to display a column for each inventory category. Totals for the inventory balance within each country are displayed in the final column in the table. 8. Return the Inventory Category field to the Rows area. 9. Click the Product Manager checkbox near the top of the task pane and then drag that field from the Rows area to the Filters area. Although the Product Manager field is included within the filter area, the Pivot Table currently displays the inventory balances for all product managers. Next you will exclude some of the managers from the displayed data. 10. Click the drop-down arrow in cell B1 and then click the Select Multiple Items checkbox. 11. Click to remove the checkboxes beside Clancy, Jacobs, Pouncey, and Williams and then click OK Search (AM Abbott Brown Clancy Howard Jacobs Noone Pouncey Williams Select Multiple Items OK Cancet You have now filtered the Pivot Table to display only the inventory balances associated with the four product managers whose checkboxes were not unchecked. 12. Close the Pivot Table Fields task pane and then select cell A4. By selecting cell A4, the Pivot Table Tools tabs become visible. Next you will filter the data using slicers, which are accessed through these tabs. Use Slicers to Filter Data 13. Choose Pivot Table Tools --Analyze-Filter-Insert Slicer, click in the checkboxes for Primary End User and Product Manager, and click OK. To view both slicer windows at the same time, you may need to drag one window beside the other. Notice that the Product Manager slicer highlights only the four product managers whose inventory figures are currently reflected within the Pivot Table. Creating PivotCharts 111 Multi-Select (Alt+S) 14. In the Product Manager slicer, choose Clancy. Choosing one product manager automatically deselects the others. 15. Still in the Product Manager slicer, click the Multi-Select button and then choose Pouncey Product Manager and Williams. Abbott 16. In the Primary End User slicer, choose Brown Intermediate and click the Clear Filter button. The Clear Filter button removed the Intermediate filter Clancy and restored data for all end users in the Pivot Table. Howard 17. In the Primary End User slicer, choose Jacobs Beginner Noone 18. Click the AutoFilter button in cell A3 and then click the drop-down arrow box under Select Field. Pouncey You can filter either field included in the Pivot Table Williams rows. 19. Close the AutoFilter menu by clicking outside of it. 20. Save your file. Develop Your Skills EA4-D5 Develop Your Skills EA4-D5 In this exercise, you will create a PivotChart based on your Pivot Table. You will then modify the PivotChart to display the exact inventory data that you would like to review, 1. Save your file as: EA4-D5-Inventory-[YourName] 2. Click cell A4 and choose Pivot Table Tools-AnalyzeTools --PivotChart. Creating PivotCharts 113 3. Choose Column and Clustered Column; click OK. All Charts Recent de AH AH ki Templates Column A Line Clustered Column Pie Total Bar Area XY (Scatter) i Stock Surface PivotChart Fields 4. Click a blank area of the PivotChart and drag it below the Pivot Table. 5. Choose PivotChart ToolsAnalyze+Show/ Hide->Field List to display the PivotChart Fields Choose fields to add to report: task pane. Search 6. Drag the Inventory Category field from the Axis area to the Legend area. Notice that both the Pivot Table and the Pivot Chart are updated based on this change. 7. Close the task pane and then drag each slicer below the Pivot Table 8. Save and close the file. Inventory Category Product Manager Y Country of Manufacture Primary End User Ending Inventory Balance - Year 1 Ending Inventory Balance - Year 2 Ending Inventory Balance - Year 3
