124 Excel for Accounting Chapter 4: Inventory Costing and Analysis Extend Your Skills EA4-e1 Edit an Inventory Listing and Create a Pivot Table for Gameroom Suppliers In this exercise, you will compile summary data based on an inventory listing for Gameroom Suppliers. You will determine the inventory balance for a product category using three methods, and you will create a Pivot Table based on the inventory listing, Open EA4-E1-Inventory. Use one of Excel's built-in functions to enter the total number of product categories in an appropriate location. For each product category, use functions to enter the total number of years for which an inventory balance is displayed (include zero balances) and the average, maximum, and minimum inventory balances in appropriate locations. Then, use a function to enter the total inventory for each of the five years in the appropriate locations. Determine the fifth-year inventory balance for air hockey tables 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 Air Hockey Tables tab; label each calculation. Enter the LIFO inventory balance on the Inventory Listing tab as appropriate. Last, create a Pivot Table that displays the Year 3 inventory balances for products located in the Huntington or Smithtown warehouses and receiving at least a thre new worksheet tab appropriately and use slicers to create the Pivot Table. Create a PivotChart displaying the results of the Pivot Table, using appropriate labeling and formatting throughout. Position the PivotChart and slicers in a logical manner. M-ES-inventory Home Insert X Page Layout alibri (Body Formulas - 11 C Data A- A- Review - = 5 View = Copy wrap Test Merge Center Deneral S. % B IU . . .A . ) a form a tie Format tis siifti 28 Ping Pong h ard 12 Vending Machine Tuiti Listing -El-Inventory(1) Home View Insert Page Layout Formulas Data .Xo c albri (Body) - 11 - A- A- Copy Format BIUENA x fx Review - EE E Wrap Text Merge & Center. General S . % ) AB7 9,940 $ 1,218 5 44 Outdoor Games Parts 45 Photo Booth Parts 46 Pinball Parts & Ping Pong Parts 4 Pollards Parts 49 Shuteboard Parts So Table Games Parts 51 Vending Machines Parts 52 Clearance Machines 53 Clearance Parts 54 Sale Machines 55 Sale Parts 56 Game Manual 57 Game Schematics 4,340 $ 99 Personed Deco 60 Personal Games 61 Personal Books 62 Personal Seating 63 Total Home Page Layout Formulas Data Review View Insert X Cut Copy Format 1 x Calibri (Body) - 11 - A- A BIVA Paste H16 x DEF 1 Air Hockey Tables 2 Inventory Costing Method Comparison Units in Ending Inventory: 4. Methods to be used: Last-In, First-Out 6 First-In, First-Out 7 Weighted Average Units 10 Beginning Inventory 11 Purchase #1 12 Purchase #2 13 Purchase 13 14 Purchase 4 Cost per Unit Total 140 S 2,100 142 $ 2,556 146 $ 1,168 147 $ 1,764 48 S 2,516 $ 10,104 uuuu 00 AM Inventory Listing Air Hockey Tables Gameroom Suppliers - Inventory Listing Total Categories Merve larwy w der w Rating Ending Beer Enlige for B . Ending Inventory Baler Ending Inventory Balas Tour Product Cairgery Air Hockey Tales Ending Inventory N Balears 100 2100 4.200 1960 ATM Machines Bubble Hockey Change Machines Dartboards Fooshall Huntinio 1.260 4 1,400 1.470 1.30 JO1 9900 9,240 2,400 7.350 4,760 3.710 5.460 5.110 Bar Chairs Blanches Home Bars Kitchen Characters Game Room Characters Pubbles Clame Table Sets Juk shares Logo Games Horseshoes 29 1.540 1,484 6.400 JONO P 6 1.710 6.000 360 2.520 icball Other Outdoor Games Photo Booth Pinball Ping Pong Pool Beads Shuttleboard Table Games Vending Machines Air Hockey Parts Arcade Games Parts R130 7,490 8.750 200 14 20 60 14630 20 12.330 16,044 Kings Park Kings Park Kings Park Kings Park Kings Park Kings Park Kings Park Kings Park Kings Park 109 6.090 Bubble Hockey Parts Darts Parts 1.610 2,086 1984 24 11.10 10 11. 3 JO 11.00 6.0.30 1.820 3,780 Jukishanes Parts Logo Games Parts Logo Accessories Parts Neon Clocks No Signs Outdoor Games Parts Photo Booth Parts Pinball Parts Ping Pong Parts Pool Lethands Parts Shuffleboard Parts The Games Parts Vending Machines Parts Clearance Machines Clearance Parts 25 1,022 370 7770 1.470 2.520 1.600 4,970 4339 4900 2390 1290 3220 SA SODO Sale Parts Game Manuals Game Schematics 4.340 1.00 1,07 1036) 12230 Personalized Decor Personalized Games Personalized Books Personalized Seating Totals: 1200 60 3,710 103 7600 3.360 10.220 7.70 3.350 Inventory Listing Air Hockey Tables Ending Intory Ending watery Ending wery Ending l y Ending Number of printy Ma Barer? Borr3 Bar Baru Be r y Minim Intary User Rating 5.250 3020 7430 6720 R750 8540 R130 8,400 19.040 560 1.190 1.610 11200 11.790 11.30 1,20 10,360 12.740 6930 7630 Inventory Listing Air Hockey Tables Air Hockey Tables Inventory Casting Method Comparison Beginning Purchase 124 Excel for Accounting Chapter 4: Inventory Costing and Analysis Extend Your Skills EA4-e1 Edit an Inventory Listing and Create a Pivot Table for Gameroom Suppliers In this exercise, you will compile summary data based on an inventory listing for Gameroom Suppliers. You will determine the inventory balance for a product category using three methods, and you will create a Pivot Table based on the inventory listing, Open EA4-E1-Inventory. Use one of Excel's built-in functions to enter the total number of product categories in an appropriate location. For each product category, use functions to enter the total number of years for which an inventory balance is displayed (include zero balances) and the average, maximum, and minimum inventory balances in appropriate locations. Then, use a function to enter the total inventory for each of the five years in the appropriate locations. Determine the fifth-year inventory balance for air hockey tables 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 Air Hockey Tables tab; label each calculation. Enter the LIFO inventory balance on the Inventory Listing tab as appropriate. Last, create a Pivot Table that displays the Year 3 inventory balances for products located in the Huntington or Smithtown warehouses and receiving at least a thre new worksheet tab appropriately and use slicers to create the Pivot Table. Create a PivotChart displaying the results of the Pivot Table, using appropriate labeling and formatting throughout. Position the PivotChart and slicers in a logical manner. M-ES-inventory Home Insert X Page Layout alibri (Body Formulas - 11 C Data A- A- Review - = 5 View = Copy wrap Test Merge Center Deneral S. % B IU . . .A . ) a form a tie Format tis siifti 28 Ping Pong h ard 12 Vending Machine Tuiti Listing -El-Inventory(1) Home View Insert Page Layout Formulas Data .Xo c albri (Body) - 11 - A- A- Copy Format BIUENA x fx Review - EE E Wrap Text Merge & Center. General S . % ) AB7 9,940 $ 1,218 5 44 Outdoor Games Parts 45 Photo Booth Parts 46 Pinball Parts & Ping Pong Parts 4 Pollards Parts 49 Shuteboard Parts So Table Games Parts 51 Vending Machines Parts 52 Clearance Machines 53 Clearance Parts 54 Sale Machines 55 Sale Parts 56 Game Manual 57 Game Schematics 4,340 $ 99 Personed Deco 60 Personal Games 61 Personal Books 62 Personal Seating 63 Total Home Page Layout Formulas Data Review View Insert X Cut Copy Format 1 x Calibri (Body) - 11 - A- A BIVA Paste H16 x DEF 1 Air Hockey Tables 2 Inventory Costing Method Comparison Units in Ending Inventory: 4. Methods to be used: Last-In, First-Out 6 First-In, First-Out 7 Weighted Average Units 10 Beginning Inventory 11 Purchase #1 12 Purchase #2 13 Purchase 13 14 Purchase 4 Cost per Unit Total 140 S 2,100 142 $ 2,556 146 $ 1,168 147 $ 1,764 48 S 2,516 $ 10,104 uuuu 00 AM Inventory Listing Air Hockey Tables Gameroom Suppliers - Inventory Listing Total Categories Merve larwy w der w Rating Ending Beer Enlige for B . Ending Inventory Baler Ending Inventory Balas Tour Product Cairgery Air Hockey Tales Ending Inventory N Balears 100 2100 4.200 1960 ATM Machines Bubble Hockey Change Machines Dartboards Fooshall Huntinio 1.260 4 1,400 1.470 1.30 JO1 9900 9,240 2,400 7.350 4,760 3.710 5.460 5.110 Bar Chairs Blanches Home Bars Kitchen Characters Game Room Characters Pubbles Clame Table Sets Juk shares Logo Games Horseshoes 29 1.540 1,484 6.400 JONO P 6 1.710 6.000 360 2.520 icball Other Outdoor Games Photo Booth Pinball Ping Pong Pool Beads Shuttleboard Table Games Vending Machines Air Hockey Parts Arcade Games Parts R130 7,490 8.750 200 14 20 60 14630 20 12.330 16,044 Kings Park Kings Park Kings Park Kings Park Kings Park Kings Park Kings Park Kings Park Kings Park 109 6.090 Bubble Hockey Parts Darts Parts 1.610 2,086 1984 24 11.10 10 11. 3 JO 11.00 6.0.30 1.820 3,780 Jukishanes Parts Logo Games Parts Logo Accessories Parts Neon Clocks No Signs Outdoor Games Parts Photo Booth Parts Pinball Parts Ping Pong Parts Pool Lethands Parts Shuffleboard Parts The Games Parts Vending Machines Parts Clearance Machines Clearance Parts 25 1,022 370 7770 1.470 2.520 1.600 4,970 4339 4900 2390 1290 3220 SA SODO Sale Parts Game Manuals Game Schematics 4.340 1.00 1,07 1036) 12230 Personalized Decor Personalized Games Personalized Books Personalized Seating Totals: 1200 60 3,710 103 7600 3.360 10.220 7.70 3.350 Inventory Listing Air Hockey Tables Ending Intory Ending watery Ending wery Ending l y Ending Number of printy Ma Barer? Borr3 Bar Baru Be r y Minim Intary User Rating 5.250 3020 7430 6720 R750 8540 R130 8,400 19.040 560 1.190 1.610 11200 11.790 11.30 1,20 10,360 12.740 6930 7630 Inventory Listing Air Hockey Tables Air Hockey Tables Inventory Casting Method Comparison Beginning Purchase