Apply Your Skills

If your classroom is using eLab with Project Grader (PG), download and use the eStart file on the Assignments page and submit as directed. If you dont use the eStart file, your work cannot be automatically graded.

If you are not using Project Grader, open the indicated file from your chapter folder.

EA4-A1 EDIT AN INVENTORY LISTING AND CREATE A PIVOTTABLE FOR EDUCATIONAL ENDEAVORS PG

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

Access the proper file.

• Using Project Grader: Download EA4-A1-Inventory-eStart.
• Not using Project Grader: Open EA4-A1-Inventory.

Prepare the Summary Data

Use the Inventory Listing tab for this section.

Cell B2: Use the COUNTA function to display the total number of product categories. Do not select individual cells in your formula; instead, include one range that encompasses all necessary entries. (PG-1)

Populate the range J5:M5 (Construction Paper category) using the indicated function:

• Cell J5: Number of years for which an inventory balance is displayed (COUNT function covering all years) (PG-2a)
• Cell K5: Average inventory balance for the five years shown (AVERAGE) (PG-2b)
• Cell L5: Maximum inventory balance for the five years shown (MAX) (PG-2c)
• Cell M5: Minimum inventory balance for the five years shown (MIN) (PG-2d)

Copy and paste those formulas such that the calculations are performed on all product categories. (PG-2e)

Populate the totals in the range E63:I63 by entering formulas using AUTOSUM. (PG-3)

Determine Inventory Balances

Remember, throughout these projects, to follow the approaches discussed and formulas used in the chapter. You wont always be reminded of this, but it is always the requirement.

Use the Construction Paper tab for the first part of this section.

Row 17:

• Cell A17: Use CONCAT to enter the text LIFO Inventory Balance: (with colon). (PG-4a)
• Cell B17: Enter a formula that calculates the ending inventory balance under LIFO. (PG-4b)

Row 18:

• Cell A18: Use CONCAT to enter the text FIFO Inventory Balance: (with colon). (PG-4c)
• Cell B18: Enter a formula that calculates the ending inventory balance under FIFO. (PG-4d)

Row 19:

• Cell A19: Use CONCAT to enter the text WA Inventory Balance: (with colon). (PG-4e)
• Cell B19: Enter a formula that calculates the ending inventory balance under the weighted average method. (PG-4f)

Switch to the Inventory Listing tab and, in the appropriate location, enter a formula to display the fifth-year LIFO inventory balance for construction paper. (PG-5)

Range K5:K62 (conditional formatting): (PG-6)

• 3 Symbols (Uncircled) icon set
• Red White Green color scale

Visualize the Data

Hint: You should still be on the Inventory Listing tab.

Create a PivotTable based on all inventory balances displayed for all five years:

• New worksheet tab name: Inventory PivotTable (PG-7a)
• Display the Year 5 Inventory Balances: (PG-7b)
• Rows area: Country of Manufacture
• Columns area: Product Category
• Add a filter: Exclude all but the Active product department. (PG-7c)
• Add a slicer: Show only data for product categories with at least a 3 of 5 online rating (PG-7d)

Create a 3-D Clustered Column PivotChart that displays the Online Rating category in the legend (below any already existing categories in the legend field). Position the PivotChart with the slicer in a logical manner. (PG-8)

Save and close the file.

• Using Project Grader: Save the file as EA4-A1-Inventory-eSubmission and attach it to the assignment you submit for grading.
• Not using Project Grader: Submit the file per your instructors guidelines.