Financial
FIFO vs. Weighted-Average Cost inventory COGS & Ending Inventory formulas
Allocate units sold across sequential purchase cost layers with MIN/MAX clipping to compute exact FIFO COGS, Ending Inventory, and WAC comparison.
Deplete purchase cost layers top-to-bottom for FIFO without manual edits!
Suppose a distributor buys 3 inventory lots during rising inflation: Lot 1 (100 units @ $10 = $1,000), Lot 2 (150 units @ $12 = $1,800), and Lot 3 (50 units @ $16 = $800)—300 total units costing $3,600—and sells 180 units (leaving 120 in Ending Inventory).
Under Weighted-Average Cost (WAC), unit cost is $3,600 / 300 = $12.00, so COGS = 180 * $12 = $2,160 and Ending Inventory = $1,440. Under First-In, First-Out (FIFO), each layer's units consumed equals MAX(0, MIN(TotalSold - PriorCumulativeQty, LayerQty)): 100 units from Lot 1 ($1,000) + 80 units from Lot 2 ($960) + 0 from Lot 3 = $1,960 FIFO COGS and $1,640 FIFO Ending Inventory!
This is a periodic weighted-average comparison with three chronological purchase layers available before the modeled sale, not a perpetual moving-average ledger with interleaved transactions. It omits opening stock, returns, write-downs, freight allocations and tax treatment. Unit quantities and sold quantity must be nonnegative, with sales no greater than available units; total available units must be positive.
| Purchase Lot | Qty (B) & Unit Cost (C) | FIFO Units Sold (180 Total) | FIFO COGS vs. Ending Inv |
|---|---|---|---|
| Lot 1 (Oldest) | 100 @ $10.00 ($1,000) | 100 sold, 0 left | COGS = $1,000 | End = $0 |
| Lot 2 | 150 @ $12.00 ($1,800) | 80 sold, 70 left | COGS = $960 | End = $840 |
| Lot 3 (Newest) | 50 @ $16.00 ($800) | 0 sold, 50 left | COGS = $0 | End = $800 |
| Totals (FIFO vs. WAC) | 300 units ($3,600) | FIFO COGS = $1,960 (End $1,640) | WAC COGS = $2,160 (End $1,440) |
FIFO units depleted from each purchase layer (Row 3 formula)
G2 holds Total Units Sold (180); SUM($B$2:B2) gives cumulative units in all earlier layers.
=IF(OR(COUNT(B2:C4)<>6,NOT(ISNUMBER(G2)),MIN(B2:C4)<0,G2<0,SUM(B2:B4)<=0,G2>SUM(B2:B4)),"Invalid inputs",MAX(0, MIN($G$2 - SUM($B$2:B2), B3)))Weighted-Average Cost (WAC) COGS vs. FIFO COGS & Ending Inventory
Computes WAC COGS as G2 * (SUMPRODUCT(B2:B4, C2:C4) / SUM(B2:B4)), while SUMPRODUCT(D2:D4, C2:C4) gives exact FIFO COGS.
=IF(OR(COUNT(B2:C4)<>6,NOT(ISNUMBER(G2)),MIN(B2:C4)<0,G2<0,SUM(B2:B4)<=0,G2>SUM(B2:B4)),"Invalid inputs",ROUND(G2 * (SUMPRODUCT(B2:B4, C2:C4) / SUM(B2:B4)), 2))Try the example
Use the original values or change them to test your understanding.