Teach the Excel algorithms for FIFO, LIFO, Specific ID, and Weighted Average
Excel Algorithm Anatomy: All Four Inventory Methods
You are not memorizing formulas. You are learning what each method is doing to inventory layers so you can build and defend the logic in Excel under pressure.
Shared Setup (used by all methods)
Build Purchases with LotID, Date, SKU, Qty, UnitCost, LotTotal and Sales with Date, SKU, Qty, LotID. The entire workbook depends on these two tables. Every method block reads the same source tables and only changes how cost is assigned.
Shared totals: UnitsSold = SUM(Sales[Qty]), TotalUnits = SUM(Purchases[Qty]), GAFS = SUM(Purchases[LotTotal]).
Method 1: FIFO Algorithm (oldest lots first)
Add a running cumulative quantity in Purchases. Then compute consumed units by lot with:MAX(0, MIN([@Qty], UnitsSold - ([@[FIFO CumQty]] - [@Qty]))). Then cost used is consumed units × unit cost. This works because each row calculates only the amount still needed after older lots.
Method 2: LIFO Algorithm (newest lots first)
Build a helper block ordered newest to oldest (reverse lot order), then apply the same consume pattern as FIFO on that reversed block. The formula can stay the same because the order has changed.
Method 3: Specific ID Algorithm (exact layers)
Use Sales[LotID] to identify the exact lot sold. Pull each unit cost with XLOOKUP(Sales[@LotID], Purchases[LotID], Purchases[UnitCost]), then compute each sale line cost = Qty × looked-up unit cost and sum for Specific ID COGS. Here, table references are the traceability chain from sale line back to purchase lot.
Method 4: Weighted Average Algorithm (periodic)
Compute one periodic blended rate:WA Rate = GAFS / TotalUnits. Then WA COGS = UnitsSold × WA Rate andWA EI = (TotalUnits - UnitsSold) × WA Rate.
Output Switch (display layer)
Create a method summary table with one row per method (COGS, Ending Inventory). The selector should lookup values from that table instead of hardcoding nested IF chains so results are auditable and easy to explain.
Professional Expectations (Not Scored in This Lesson)
- Use Tables and structured references so logic expands safely.
- Keep clear names for blocks, selector cells, and method summary rows.
- Add input validation and friendly error handling to support audit quality.
If we sell oldest inventory first, what are COGS and ending inventory today?
What Excel Is Doing
Excel walks lots from oldest to newest and consumes units until all sold units are assigned.
Why This Works
The running cumulative quantity tells Excel how much has already been consumed, so each lot contributes only the remaining needed units.
Formula Pattern
MAX(0, MIN([@Qty], UnitsSold - ([@[FIFO CumQty]] - [@Qty])))First Setup Trap to Check
If cumulative quantity is not running oldest-to-newest, FIFO will consume the wrong lots.
Reference Meanings in This Method
[@Qty]: Current row lot quantity in Purchases[@[FIFO CumQty]]: Current row running total quantityUnitsSold: Named total sold units from Sales table
| Reference | Plain Meaning | Example Use |
|---|---|---|
Purchases[Qty] | All Qty values in the Purchases table. | SUM(Purchases[Qty]) gives total purchased units. |
Sales[LotID] | All LotID values in the Sales table. | Used to connect sales rows to exact purchase lots. |
[@Qty] | Qty in this row only. | Inside a row formula, it means the current lot quantity. |
[@[FIFO CumQty]] | FIFO CumQty from this row only. | Used to tell how many units are already consumed before this lot. |
Quick self-check: active method is FIFO. Explain aloud what[@Qty] and one full-table reference mean before moving to Phase 3.
In Lessons 2-4 you calculated these methods manually. In Lesson 05, your assessment is whether you can translate each method into a correct Excel algorithm that remains consistent when data changes.
Quick check before Phase 3: explain in plain language what Purchases[Qty] means and what [@Qty] means. If you can decode both, you are ready to rehearse the workbook logic.