Question

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 three-star rating. Label the new worksheet tab appropriately and use slicers to create the Pivot Table. Create a Pivot Chart displaying the results of the Pivot Table, using appropriate labeling and formatting throughout. Position the PivotChart and slicers in a logical manner.

Answer 1

Sales Activity Variance

$ 1,58,770.00

U

Market share variance

$ 1,99,760.00

U

Industry Volume variance

$    40,990.00

F

Working :

Sales activity variance = (Actual unit sold - Budgeted unit) * Budgeted contribution margin

Sales activity variance = (108960 - 145044) * 4.40

Sales activity variance = 158770 (U)

Market share variance = (Actual unit sold * Standard contribution margin per unit) - (Standard unit sold * Standard contribution margin)

Market share variance = (108960 * 4.40) - (908000 * 0.17 * 4.40)

Market share variance = 479424-679184

Market share variance = 199760 (U)

Industry volume variance = (Standard unit sold * Standard contribution margin per unit) - (Budgeted units sold * Standard contribution margin per unit)

Industry volume variance = (908,000 * 0.17 * 4.4) - (145044 * 4.40)

Industry volume variance = 679184 - 638194

Industry volume variance = 40990 (F)