Math for Business Operations

Applied Accounting with Excel

UNIT07 - Lesson 4

Specific Identification and Weighted Average in Simple Excel

45 minutes
Lesson Overview

Lesson Focus

Build Excel Tables and formulas for Specific ID and Weighted Average

Key Unit Objectives

Enduring Understandings:

  • Inventory valuation methods directly impact reported profits, tax obligations, and business decision-making
  • FIFO, LIFO, Weighted Average, and Specific Identification produce different COGS and ending inventory values
  • Accurate inventory tracking is essential for business sustainability and investor confidence
  • The inventory equation (Beginning Inventory + Purchases − COGS = Ending Inventory) must always reconcile

Lesson Activities

Activity 1: Specific Identification Method
12 minutes

Track actual cost of each individual item sold

Details:

  • When to use: High-value or unique products (jewelry, cars, custom orders)
  • Demonstration: Track individual item costs from purchase to sale
  • Practice: Calculate COGS and ending inventory using Specific ID
  • Compare with FIFO/LIFO results
Activity 2: Weighted Average Cost Method
12 minutes

Assign same average cost to every unit

Details:

  • Formula: Total Cost of Goods Available ÷ Total Units Available
  • Demonstration: Calculate weighted average cost per unit
  • Practice: Apply average cost to COGS and ending inventory
  • Compare all four methods side by side
Weighted Average Best Practices

Weighted average smooths cost fluctuations across all purchases

  • Recalculate average cost after each new purchase (perpetual system)
  • Or calculate once at period end (periodic system)
  • Best for homogeneous inventory items (fuel, grain, chemicals)
  • Produces results between FIFO and LIFO in inflationary periods
Activity 3: Excel Table Implementation
15 minutes

Build Excel Tables with structured references for all four methods

Details:

  • Convert inventory schedules to Excel Tables
  • Implement structured references for automated COGS and Ending Inventory
  • Add error checks and edge case handling
  • Validate calculations across all four methods
Activity 4: Peer Accuracy Check
6 minutes

Teams exchange files and verify calculations

Details:

  • Use provided answer key to check all four method results
  • Identify and correct common formula errors
  • Preview Day 5: Method comparison workbook sprint
Required Materials
  • Advanced template with all four methods
  • Peer rubric for validation
  • /resources/unit07-inventory-valuation-practice.csv
  • Excel Table setup guide
Differentiation Strategies

For Struggling Students

  • • Formula templates: Pre-built Excel structures with guided parameter entry
  • • Step-by-step calculation guides: Visual walkthroughs for all four inventory methods
  • • Simplified datasets: Reduced complexity while maintaining authentic business context
  • • Peer tutoring: Pairing with students strong in Excel or business concepts
  • • Alternative assessment: Oral presentation option with reduced Excel complexity requirements

For Advanced Students

  • • Advanced Excel features: Explore LIFO reserve and tax implications as extension activity
  • • Complex scenarios: Multiple inventory categories and sensitivity analysis on purchase timing
  • • Leadership roles: Facilitate team strategic discussions and mentor struggling peers
  • • Extension analysis: Research industry-specific inventory practices and regulatory requirements
  • • Presentation enhancement: Develop sophisticated visual aids and executive summary documents

For English Language Learners

  • • Business vocabulary support: Glossary of inventory terms with visual aids and translations
  • • Calculation emphasis: Focus on quantitative skills while building language gradually
  • • Collaborative support: Mixed-language team structures with peer translation assistance
  • • Visual learning aids: Charts, diagrams, and flowcharts to support conceptual understanding
  • • Presentation alternatives: Option to focus on Excel demonstration with reduced verbal component