2 pages to this question:

Excel information

AC 121 Apply Your Skills ng and Create a Pivottable for EA4-A1 Edit an Inventory Listing and Create a Educational Endeavors summary data based on an inventory listing for Educational the inventory balance for a product category using three In this exercise, you will compile summary data based on an inventarul Buders. Then you will determine the inventory balance for and methods. Last, you will create a Pivot Table and a PivotChart. 1 One A4-A1-Inventory from your Chapter 04 folder and EA4-A1-Inventory-[YourName) 2. Use a function to display the total number of product categories in cell B2 3. Populate the cells as described for the Construction Paper product category In this cell: Use a function to display the Cells Number of years for which an inventory balance is displayed Cell KS Average inventory balance for the five years shown Cell L5 Maximum inventory balance for the five years shown Cell M5 Minimum inventory balance for the five years shown Hint: Make sure the formula in cell J5 will update properly if any currently blank cells are later filled in 4. Copy the formulas in the range J5:M5 and paste them such that the calculations will be performed for all product categories 5. Enter formulas in the range E63:163 to add the inventory balances for each year. Use Inventory Costing Methods 6. Switch to the Construction Paper tab. 7. Use the CONCATENATE function to enter LIFO Inventory Balance in cell A17. 8. Enter a formula in cell B17 to calculate the ending inventory balance for construction paper under the LIFO method. 9. In cell A18, use the CONCATENATE function to enter FIFO Inventory Balance 10. Enter a formula in cell B18 to calculate the ending inventory balance for construction paper under the FIFO method. 11. In cell A19, use the CONCATENATE function to enter WA Inventory Balance 12. Enter a formula in cell B19 to calculate the ending inventory balance for construction paper under the weighted average method. 13. Enter a formula in the appropriate location on the Inventory Listing tab to display the fifth-year LIFO inventory balance for construction paper 14. Apply the 3 Symbols (Uncircled) icon set and Red - White - Green color scale to the range K5:K62 122 Excel for Accounting Chapter 4: Inventory Costing and Analysis Insert a Pivot Table and a PivotChart 15. Create a Pivot Table on the Inventory Listing tab based on the inventory balances displayed for all five years. 16. Rename the new worksheet tab containing the Pivot Table. 17. Display the Year 5 Inventory Balances within the Pivot Table with: Country of Manufacture in the Rows area Product Category in the Columns area A filter that excludes the Active product department 18. Add a slicer that limits the data to product categories that earned at least a 3 of 5 online rating 19. Create a 3-D Clustered Column PivotChart and display the Online Rating category in the legend. 20. Position the PivotChart and slicer in a logical manner. 21. Save and close the file. e 5 s es Product Department Arts & Crafts Arts & Crafts Arts & Crafts Arts & Crafts Arts & Crafts Arts & Crafts Arts & Crafts Arts & Crafts Arts & Crafts Arts & Crafts Arts & Crafts Arts & Crafts Arts & Crafts Arts & Crafts Active Active Active Ending Inventory Ending Inventory Ending Inventory Ending Inventory Ending Inventory Number Online Rating Balance - Year 1 Balance - Year 2 Balance - Year 3 Balance - Year 4 Balance - Year 5 of Years 3 of 5 11,480 $ 14,350 $ 9,450 $ 6,860 2 of 5 24,108 $ 26,978 $ 25,256 $ 24,682 3 of 5 12,710 $ 11,480 $ 17,220 5 of 5 9,184 $ 17,794 $ 9,758 $ 8,036 2 of 5 4,018 $ 5,166 S 3,444 $ 1,148 $ 1,722 5 of 5 6,314 $ 5,740 $ 5,740 $ 5,740 $ 6,027 5 of 5 6,765 $ 7,462 $ 5,453 2 of 5 2,009 $ 1,148 $ 2,296 2 of 5 35,588 $ 32,718 $ 28,126 $ 30,422 $ 3 of 5 40,754 $ 42,476 $ 43,050 $ 40,754 $ 34,440 3 of 5 28,700 $ 28,700 $ 31,570 $ 30,422 $ 30,135 1 of 5 20,664 $ 21,525 $ 22,386 $ 20,951 $ 19,516 3 of 5 16,072 $ 15,498 $ 14,350 $ 14,924 $ 15,211 5 of 5 26,978$ 74629 14,924 5 of 5 2,009 $ 2,181 $ 5,166 5,453 $ 4,305 3 of 5 6,429 $ 4,879 $ 6,084 $ 5,625 3 of 5 1,722 $ 1,779 37,884 -d d UUS in Paper til Merge & Center - $ % 8 Insert Delete Format Conditional Format as Cell Formatting Table Styles Styles Alignment Number G 1,779 35,588 $ 26,404 $ 13,202 $ 22,960 S 30,422 27,552 14,924 24,100 $ $ $ $ - 17,220 28,126 15,211 24,969 $ $ $ $ $ 1,722 2,870 26,404 12,628 25,256 10,332 20,664 22,386 28,126 3,444 $ $ $ $ $ 26,404 10,906 27,552 $ 22,099 22,099 33,292 $ $ $ $ S S $ 1 Falls 2 Gross Motor Skills 3 Indoor Play Equipment -4 Play Structures 5 Sand & Water 6 Teacher's Resources 27 Trikes & Riding 28 Alphabet Knowledge 29 Games & Puzzles 80 Grammar 31 Interactive Books & Software 82 Journals & Paper 33 Language Easels 34 Reading Comprehension 35 Rhyming 36 Sentence Building 37 Sight Words 38 Addition & Subtraction 39 Counting & Numeracy 40 Curriculum Resources 41 Exploring Manipulatives 42 Geometry 3. Murer Inventory Listing United States Active United States Active Canada Active United States Active New Zealand Active Australia Active Australia Active Ireland Language Australia Language Ireland Language United States Language United States Language Ireland Language Ireland Language Ireland Language Australia Language New Zealand Language New Zealand Mathematics Canada Mathematics New Zealand Mathematics Australia Mathematics Ireland Mathematics Niestand the Construction Paper 3 of 5S 3 of 5 S 2 of 5 S 2 of 5 S 2 of 5 $ 2 of 5 s 2 of 5 S 3 of 5 3 of 5 3 of 5 305 5 of 5 305 3 of 5 5 of 5 2 of 5 2 of 5 1 of 5 of 5 1 of 5 5 of 5 sofs 18,655 S 24,108 S 35,875 $ $ 2,296 $ 37,310 $ 54,530 $ 78,064 $ 49,361 $ 22,960 S 17,220 $ 32,141S 4,82 S 1,837 S 17,220 $ 23,247 $ 35,014 $ $ 2.5835 34.440 S 58,548 5 30,360 47,06RS 15.498 25,256 30,709 20,090 1,722 35,875 35,588 59,983 75,768 44,772 24,969 $ $ $ S $ 36,162 50.512 79,499 $ $ $ 65,780 24.105 $ 27,839S 16,072 $ 29,274 $ 8,553 $ 29,848 $ 6,501 $ 253 $ 30,422 $ 8,151 S 913 S 12,054 28.126 5,510 3.157 34,440 45.920 $ 48,216 S 46,494 $ 7,462 $ Asteve Al inventor-labenia read . Se File Home Insert Page Layout formulas Data Review View Help Share GG Coro - A Wrap Test Mega Center WE Sort Finde BTUA S-99 Conditional Format Ferming Table Cell Styles Clipboard 45,500 $ 55,100 $ 13.212S 45,3465 32,1415 55,104 5.510 S 35.0145 SOS 10,225 10,3325 11,2025 S 6,883 6,601$ 14,921 17,794 $ 5,027 16,072 A - XA 13 CD 42 Geometry Ireland Mathematics of 5 43 Measurement & Classifying New Zealand Mathematics 44 Operations United States Mathematics 3ofs 45 Algebraic Thinking Australia Mathematics 46 Carts & Storage Block Play 2 of 5 47 lumbo Blocks New Zealand Flock Play 45 Novelty Blocks New Zealand Block Play 49 People & Animals Ireland Block Play 505 50 Rus Mats United States Block Play 2015 51 Tabletop Blocks Block Play 52 Unit Blocks Ireland Block Play 2015 53 vehicles New Zealand block Play 5 of 5 54 Chalk & Dry Erase Australia School Supplies 55 Crayons Canada School Supplies 3 of 5 56 Folders & Binders Canada School Supplies 2 of 5 57 Notebooks & Paper Canada School Supplies 305 58 Office Supplies treland School Supplies 59 Paint United States School Supplies 3015 60 Pens & Pencils Australia School Supplies Weland 61 SSORS 2 of School Supplies 62 Storage New Zealand School Supplies Sofs 63 Total: 10,90 20.6515 10, 35 4,305 $ 14. 99 10,6135 5.156 S 15.072 Sof5 20,00 21,3125 3.71 $ 40.1805 1405 3,559S 43,64 $ 3015 52,2345 20,413 $ 50, 512S 21.5 16.0725 MSS 31,283 S 20,126 7.463 41.902 20. 15,75 22,671 Inventory Listing Construction Pro Alignment Number B17 : X G H 2 Inventory Costing Method Comparison Units in Ending Inventory: 571 4 Methods to be used: 5 Last-In, First-Out 6 First-In, First-Out 7 Weighted Average Units 10 Beginning Inventory 11 Purchase #1 12 Purchase #2 13 Purchase #3 14 Purchase #4 15 Cost per Unit Total $ 14 $ 6,860 $ 15 $ 1,905 $ 17. $ 4,080 18 $ 1,530 19 $ 9,595 $ 23,970 16 171 18 19 Inventory Listing Construction Paper AC 121 Apply Your Skills ng and Create a Pivottable for EA4-A1 Edit an Inventory Listing and Create a Educational Endeavors summary data based on an inventory listing for Educational the inventory balance for a product category using three In this exercise, you will compile summary data based on an inventarul Buders. Then you will determine the inventory balance for and methods. Last, you will create a Pivot Table and a PivotChart. 1 One A4-A1-Inventory from your Chapter 04 folder and EA4-A1-Inventory-[YourName) 2. Use a function to display the total number of product categories in cell B2 3. Populate the cells as described for the Construction Paper product category In this cell: Use a function to display the Cells Number of years for which an inventory balance is displayed Cell KS Average inventory balance for the five years shown Cell L5 Maximum inventory balance for the five years shown Cell M5 Minimum inventory balance for the five years shown Hint: Make sure the formula in cell J5 will update properly if any currently blank cells are later filled in 4. Copy the formulas in the range J5:M5 and paste them such that the calculations will be performed for all product categories 5. Enter formulas in the range E63:163 to add the inventory balances for each year. Use Inventory Costing Methods 6. Switch to the Construction Paper tab. 7. Use the CONCATENATE function to enter LIFO Inventory Balance in cell A17. 8. Enter a formula in cell B17 to calculate the ending inventory balance for construction paper under the LIFO method. 9. In cell A18, use the CONCATENATE function to enter FIFO Inventory Balance 10. Enter a formula in cell B18 to calculate the ending inventory balance for construction paper under the FIFO method. 11. In cell A19, use the CONCATENATE function to enter WA Inventory Balance 12. Enter a formula in cell B19 to calculate the ending inventory balance for construction paper under the weighted average method. 13. Enter a formula in the appropriate location on the Inventory Listing tab to display the fifth-year LIFO inventory balance for construction paper 14. Apply the 3 Symbols (Uncircled) icon set and Red - White - Green color scale to the range K5:K62 122 Excel for Accounting Chapter 4: Inventory Costing and Analysis Insert a Pivot Table and a PivotChart 15. Create a Pivot Table on the Inventory Listing tab based on the inventory balances displayed for all five years. 16. Rename the new worksheet tab containing the Pivot Table. 17. Display the Year 5 Inventory Balances within the Pivot Table with: Country of Manufacture in the Rows area Product Category in the Columns area A filter that excludes the Active product department 18. Add a slicer that limits the data to product categories that earned at least a 3 of 5 online rating 19. Create a 3-D Clustered Column PivotChart and display the Online Rating category in the legend. 20. Position the PivotChart and slicer in a logical manner. 21. Save and close the file. e 5 s es Product Department Arts & Crafts Arts & Crafts Arts & Crafts Arts & Crafts Arts & Crafts Arts & Crafts Arts & Crafts Arts & Crafts Arts & Crafts Arts & Crafts Arts & Crafts Arts & Crafts Arts & Crafts Arts & Crafts Active Active Active Ending Inventory Ending Inventory Ending Inventory Ending Inventory Ending Inventory Number Online Rating Balance - Year 1 Balance - Year 2 Balance - Year 3 Balance - Year 4 Balance - Year 5 of Years 3 of 5 11,480 $ 14,350 $ 9,450 $ 6,860 2 of 5 24,108 $ 26,978 $ 25,256 $ 24,682 3 of 5 12,710 $ 11,480 $ 17,220 5 of 5 9,184 $ 17,794 $ 9,758 $ 8,036 2 of 5 4,018 $ 5,166 S 3,444 $ 1,148 $ 1,722 5 of 5 6,314 $ 5,740 $ 5,740 $ 5,740 $ 6,027 5 of 5 6,765 $ 7,462 $ 5,453 2 of 5 2,009 $ 1,148 $ 2,296 2 of 5 35,588 $ 32,718 $ 28,126 $ 30,422 $ 3 of 5 40,754 $ 42,476 $ 43,050 $ 40,754 $ 34,440 3 of 5 28,700 $ 28,700 $ 31,570 $ 30,422 $ 30,135 1 of 5 20,664 $ 21,525 $ 22,386 $ 20,951 $ 19,516 3 of 5 16,072 $ 15,498 $ 14,350 $ 14,924 $ 15,211 5 of 5 26,978$ 74629 14,924 5 of 5 2,009 $ 2,181 $ 5,166 5,453 $ 4,305 3 of 5 6,429 $ 4,879 $ 6,084 $ 5,625 3 of 5 1,722 $ 1,779 37,884 -d d UUS in Paper til Merge & Center - $ % 8 Insert Delete Format Conditional Format as Cell Formatting Table Styles Styles Alignment Number G 1,779 35,588 $ 26,404 $ 13,202 $ 22,960 S 30,422 27,552 14,924 24,100 $ $ $ $ - 17,220 28,126 15,211 24,969 $ $ $ $ $ 1,722 2,870 26,404 12,628 25,256 10,332 20,664 22,386 28,126 3,444 $ $ $ $ $ 26,404 10,906 27,552 $ 22,099 22,099 33,292 $ $ $ $ S S $ 1 Falls 2 Gross Motor Skills 3 Indoor Play Equipment -4 Play Structures 5 Sand & Water 6 Teacher's Resources 27 Trikes & Riding 28 Alphabet Knowledge 29 Games & Puzzles 80 Grammar 31 Interactive Books & Software 82 Journals & Paper 33 Language Easels 34 Reading Comprehension 35 Rhyming 36 Sentence Building 37 Sight Words 38 Addition & Subtraction 39 Counting & Numeracy 40 Curriculum Resources 41 Exploring Manipulatives 42 Geometry 3. Murer Inventory Listing United States Active United States Active Canada Active United States Active New Zealand Active Australia Active Australia Active Ireland Language Australia Language Ireland Language United States Language United States Language Ireland Language Ireland Language Ireland Language Australia Language New Zealand Language New Zealand Mathematics Canada Mathematics New Zealand Mathematics Australia Mathematics Ireland Mathematics Niestand the Construction Paper 3 of 5S 3 of 5 S 2 of 5 S 2 of 5 S 2 of 5 $ 2 of 5 s 2 of 5 S 3 of 5 3 of 5 3 of 5 305 5 of 5 305 3 of 5 5 of 5 2 of 5 2 of 5 1 of 5 of 5 1 of 5 5 of 5 sofs 18,655 S 24,108 S 35,875 $ $ 2,296 $ 37,310 $ 54,530 $ 78,064 $ 49,361 $ 22,960 S 17,220 $ 32,141S 4,82 S 1,837 S 17,220 $ 23,247 $ 35,014 $ $ 2.5835 34.440 S 58,548 5 30,360 47,06RS 15.498 25,256 30,709 20,090 1,722 35,875 35,588 59,983 75,768 44,772 24,969 $ $ $ S $ 36,162 50.512 79,499 $ $ $ 65,780 24.105 $ 27,839S 16,072 $ 29,274 $ 8,553 $ 29,848 $ 6,501 $ 253 $ 30,422 $ 8,151 S 913 S 12,054 28.126 5,510 3.157 34,440 45.920 $ 48,216 S 46,494 $ 7,462 $ Asteve Al inventor-labenia read . Se File Home Insert Page Layout formulas Data Review View Help Share GG Coro - A Wrap Test Mega Center WE Sort Finde BTUA S-99 Conditional Format Ferming Table Cell Styles Clipboard 45,500 $ 55,100 $ 13.212S 45,3465 32,1415 55,104 5.510 S 35.0145 SOS 10,225 10,3325 11,2025 S 6,883 6,601$ 14,921 17,794 $ 5,027 16,072 A - XA 13 CD 42 Geometry Ireland Mathematics of 5 43 Measurement & Classifying New Zealand Mathematics 44 Operations United States Mathematics 3ofs 45 Algebraic Thinking Australia Mathematics 46 Carts & Storage Block Play 2 of 5 47 lumbo Blocks New Zealand Flock Play 45 Novelty Blocks New Zealand Block Play 49 People & Animals Ireland Block Play 505 50 Rus Mats United States Block Play 2015 51 Tabletop Blocks Block Play 52 Unit Blocks Ireland Block Play 2015 53 vehicles New Zealand block Play 5 of 5 54 Chalk & Dry Erase Australia School Supplies 55 Crayons Canada School Supplies 3 of 5 56 Folders & Binders Canada School Supplies 2 of 5 57 Notebooks & Paper Canada School Supplies 305 58 Office Supplies treland School Supplies 59 Paint United States School Supplies 3015 60 Pens & Pencils Australia School Supplies Weland 61 SSORS 2 of School Supplies 62 Storage New Zealand School Supplies Sofs 63 Total: 10,90 20.6515 10, 35 4,305 $ 14. 99 10,6135 5.156 S 15.072 Sof5 20,00 21,3125 3.71 $ 40.1805 1405 3,559S 43,64 $ 3015 52,2345 20,413 $ 50, 512S 21.5 16.0725 MSS 31,283 S 20,126 7.463 41.902 20. 15,75 22,671 Inventory Listing Construction Pro Alignment Number B17 : X G H 2 Inventory Costing Method Comparison Units in Ending Inventory: 571 4 Methods to be used: 5 Last-In, First-Out 6 First-In, First-Out 7 Weighted Average Units 10 Beginning Inventory 11 Purchase #1 12 Purchase #2 13 Purchase #3 14 Purchase #4 15 Cost per Unit Total $ 14 $ 6,860 $ 15 $ 1,905 $ 17. $ 4,080 18 $ 1,530 19 $ 9,595 $ 23,970 16 171 18 19 Inventory Listing Construction Paper