EA4-E2 Edit an Inventory Listing and Create a PivotTable for Brown's Toy Store In this exercise, you will compile summary data based on an inventory listing for Brown's Toy Store. You will determine the inventory balance for a product category using three methods, and you will create a PivotTable based on this inventory listing. Open EA4-E2-Inventory. Use one of Excel's built-in functions to enter the total number of product categ the to and the average, maximum, and m Then, use a function to enter the total inventory for each of the five years in the ap ories in an appropriate location. For each product category, use functions to enter tal number of years for which an inventory balance is displayed (include zero balances) inimum inventory balances in appropriate locations locations Determine the fifth-year inventory balance for balance bikes using the LIFO, FIFO, and weighted average methods. Use formulas to determine the inventory balance under each Ensure that the calculations are entered in appropriate locations on the Balance Bikes tab; label each calculation. Enter the LIFO inventory balance on the Inventory Listing tab as appropriate at create a PivotTable that displays the Year 4 inventory balances for products sold in-store or at trade shows and manufactured by Bouncing Balls or Toy Makerz. Label the new work hece taD aPpropriately and use slicers to create the PivotTable. Create a PivotChart displaying the results of the PivotTable, using appropriate labeling and formatting throughout. Position the PivotChart and slicers in a logical manner. Last, create a 1 Brown's Toy Store-Inventory Listing Ending Inventory Baiance-YarBafance-Year2Baance-Yer3Balance- Year4 Ending Inventory Ending inventory Ending inventoryEnding Inventory Number Avere Inventory Maximum Inventory Minimum Inventory Product CatogorySavles Lacation ManuctuerDepartmet Balance-Yar 5of Years Powered Roding Toys 19 Easels& Art Tables lewelry & Beac Kits Bouncing Balls 10-14 Years 18,928 18,564 30,576 S 46 Racetracks & Playsets $ 6-10 Years 6-10 Ycars 48 Trains &Train Sets 6-10 Years 52 Interactive Gamine Figures 59 Cards & Casino Games 64 Drurns & Percussion 65 Folk& World Instruments 66 Guitars & Guitar Accessorles 1,176 S D Musical Instruction 0 1 Balance Bikes 2 Inventory Costing Method Comparison 4 Methods to be used 5 Last-In, First-Out 6 First-In, First-Out 7 Weighted Average Units in Ending Inventory: 34 Cost per Unit Total 9 10 Beginning Inventory 11 Purchase #1 12 Purchase #2 13 Purchase #3 14 Purchase #4 15 Units 26 14 20 24 18 102 962,496 $97 $1,358 $99 $1,980 101 2,424 104$1,872 10,130 EA4-E2 Edit an Inventory Listing and Create a PivotTable for Brown's Toy Store In this exercise, you will compile summary data based on an inventory listing for Brown's Toy Store. You will determine the inventory balance for a product category using three methods, and you will create a PivotTable based on this inventory listing. Open EA4-E2-Inventory. Use one of Excel's built-in functions to enter the total number of product categ the to and the average, maximum, and m Then, use a function to enter the total inventory for each of the five years in the ap ories in an appropriate location. For each product category, use functions to enter tal number of years for which an inventory balance is displayed (include zero balances) inimum inventory balances in appropriate locations locations Determine the fifth-year inventory balance for balance bikes using the LIFO, FIFO, and weighted average methods. Use formulas to determine the inventory balance under each Ensure that the calculations are entered in appropriate locations on the Balance Bikes tab; label each calculation. Enter the LIFO inventory balance on the Inventory Listing tab as appropriate at create a PivotTable that displays the Year 4 inventory balances for products sold in-store or at trade shows and manufactured by Bouncing Balls or Toy Makerz. Label the new work hece taD aPpropriately and use slicers to create the PivotTable. Create a PivotChart displaying the results of the PivotTable, using appropriate labeling and formatting throughout. Position the PivotChart and slicers in a logical manner. Last, create a 1 Brown's Toy Store-Inventory Listing Ending Inventory Baiance-YarBafance-Year2Baance-Yer3Balance- Year4 Ending Inventory Ending inventory Ending inventoryEnding Inventory Number Avere Inventory Maximum Inventory Minimum Inventory Product CatogorySavles Lacation ManuctuerDepartmet Balance-Yar 5of Years Powered Roding Toys 19 Easels& Art Tables lewelry & Beac Kits Bouncing Balls 10-14 Years 18,928 18,564 30,576 S 46 Racetracks & Playsets $ 6-10 Years 6-10 Ycars 48 Trains &Train Sets 6-10 Years 52 Interactive Gamine Figures 59 Cards & Casino Games 64 Drurns & Percussion 65 Folk& World Instruments 66 Guitars & Guitar Accessorles 1,176 S D Musical Instruction 0 1 Balance Bikes 2 Inventory Costing Method Comparison 4 Methods to be used 5 Last-In, First-Out 6 First-In, First-Out 7 Weighted Average Units in Ending Inventory: 34 Cost per Unit Total 9 10 Beginning Inventory 11 Purchase #1 12 Purchase #2 13 Purchase #3 14 Purchase #4 15 Units 26 14 20 24 18 102 962,496 $97 $1,358 $99 $1,980 101 2,424 104$1,872 10,130