Reinforce Your Skills

EA4-R1 EDIT AN INVENTORY LISTING AND CREATE A PIVOTTABLE FOR XL SPORTS EMPORIUM

In this exercise, you will compile summary data based on an inventory listing for XL Sports Emporium. Then you will determine the inventory balance for a product using three methods. Last, you will create a PivotTable and a PivotChart.

1. Open EA4-R1-Inventory from your Chapter 04 folder.

To begin, you will work with the Inventory Listing tab.

1. Click in cell B2, type =COUNTA( and highlight the range A5:A70, and then type )and tap Enter.
2. In cell J5, type =COUand then double-click the Count function, highlight the range E5:I5, and then type )and tap Enter.
3. Click cell K5 and choose FormulasFunction LibraryInsert Function.
4. Type Average in the Search for a Function field and click Go, choose Average and click OK, highlight the range E5:I5, and then click OK.

You may need to move the Function Arguments dialog box by dragging its title bar in order to highlight the specified range.

1. Click cell L5, choose FormulasFunction LibraryInsert Function, click the arrow for Or Select a Category, choose Statistical and then Max, and click OK.
2. Highlight the range E5:I5 and click OK.
3. Select cell M5 and choose HomeEditingAutoSum Min, highlight the range E5:I5, and tap Enter.
4. Highlight the range J5:M5, click the fill handle, and drag down to row 70.
5. Beginning with cell E71, highlight the range E71:I71 and select Home

EditingAutoSum.

CHECK FIGURE Cell E71 = $851,419

Use Inventory Costing Methods

1. Switch to the Treadmills tab.
2. Enter the data as indicated:

Cell A17	=CONCAT(LEFT(A5, 1), MID(A5, 6, 1), MID(A5, 10, 1), MID(A5, 16, 1), " Inventory Balance:")
Cell B17	=D10+(F4-B10)*C11
Cell A18	=CONCAT(LEFT(A6, 1), MID(A6, 7, 1), MID(A6, 11, 1), MID(A6, 17, 1), " Inventory Balance:")
Cell B18	=D14+(F4-B14)*C13
Cell A19	=CONCAT(LEFT(A7, 1), MID(A7, 10, 1), " Inventory Balance:")
Cell B19	=(D15/B15)*F4

1. Switch to the Inventory Listing tab and select cell I5.
2. Type = and then click cell B17 on the Treadmills tab and tap Enter.
3. Highlight the range K5:K70 and choose HomeStylesConditional FormattingData BarsSolid FillGreen Data Bar.

Feel free to experiment with the conditional formatting options, including those for color scales and icon sets. Clear those rules before completing step 15.

CHECK FIGURE LIFO Inventory Balance = $13,368

Insert a PivotTable

1. Select cell A5 and choose InsertTablesPivotTable.
2. In the Table/Range field, replace $M$71 with $I$70 and click OK.
3. Right-click the new worksheet tab and rename it: Inventory PivotTable
4. In the PivotTable Fields task pane, click in order the checkboxes for Warehouse Location, Manufacturer, and Ending Inventory Balance Year 3.
5. In the Rows area, drag the Manufacturer field above the Warehouse Location field.
6. Click the checkbox for Department and then drag that field to the Filters area.
7. Click the drop-down arrow in cell B1, click the checkbox beside Select Multiple Items, uncheck the boxes for Cardio Equip. and Recovery, and click OK.
8. Close the task pane and then select cell A4.
9. Choose PivotTable AnalyzeFilterInsert Slicer, click the checkboxes for Warehouse Location and Department, and click OK.
10. In the Department slicer, choose Strength Train and then use the Multi-Select button to add Bikes/Skates and Camping/Hiking.
11. In the Warehouse Location slicer, choose Oakland.

CHECK FIGURE PivotTable Grand Total = $118,762

Create a PivotChart

1. Click cell A4 and choose PivotTable AnalyzeToolsPivotChart.
2. In the Column category, choose Clustered Column and click OK.
3. Click on a blank area of the PivotChart and drag it below the PivotTable.
4. Choose PivotChart AnalyzeShow/HideField List to display the PivotChart Fields task pane.
5. Drag the Department field to the Legend area and then close the task pane.
6. Position the slicers beside the PivotTable.
7. Save and close the file.