EA4-R1 Edit an Inventory Listing and Create a PivotTable for XL Sports EmporiumIn 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. Open EA4-R1-Inventory from your Chapter 04 folder.To begin, you will work with the Inventory Listing tab.Click in cell B2, type =COUNTA( and highlight the range A5:A70, and then type ) and tap Enter.In cell J5, type =COU and then double-click the Count function, highlight the range E5:I5, and then type ) and tap Enter.Click cell K5 and choose FormulasFunction LibraryInsert Function.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.Click cell L5, choose FormulasFunction LibraryInsert Function, click the arrow for Or Select a Category, choose Statistical and then Max, and click OK.Highlight the range E5:I5 and click OK.Select cell M5 and choose HomeEditingAutoSum Min, highlight the range E5:I5, and tap Enter.Highlight the range J5:M5, click the fill handle, and drag down to row 70.Beginning with cell E71, highlight the range E71:I71 and select HomeEditingAutoSum.CHECK FIGURE Cell E71= $851,419Use Inventory Costing Methods Switch to the Treadmills tab.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)*C11Cell A18=CONCAT(LEFT(A6,1), MID(A6,7,1), MID(A6,11,1), MID(A6,17,1)," Inventory Balance:")Cell B18=D14+(F4-B14)*C13Cell A19=CONCAT(LEFT(A7,1), MID(A7,10,1)," Inventory Balance:")Cell B19=(D15/B15)*F4Switch to the Inventory Listing tab and select cell I5.Type = and then click cell B17 on the Treadmills tab and tap Enter.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,368Insert a PivotTable Select cell A5 and choose InsertTablesPivotTable.In the Table/Range field, replace $M$71 with $I$70 and click OK.Right-click the new worksheet tab and rename it: Inventory PivotTableIn the PivotTable Fields task pane, click in order the checkboxes for Warehouse Location, Manufacturer, and Ending Inventory Balance Year 3.In the Rows area, drag the Manufacturer field above the Warehouse Location field.Click the checkbox for Department and then drag that field to the Filters area.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