FIFO inventory method

FIFO is both a physical handling rule and an accounting model. The physical rule ships older units first to limit obsolescence. The accounting model assumes that the oldest units were consumed first, regardless of the actual flow, because it turns inventory into a solvable bookkeeping problem. The gap between those two views is where FIFO analysis is most informative and also most fragile.

Table of contents

Physical FIFO vs analytical FIFO

Physical FIFO is a warehousing practice: move older units out first, often to avoid expiration or cosmetic degradation. Analytical FIFO is a model: it rebuilds the state of stock by assuming the oldest purchases are the first ones sold. The model is useful because it needs only two inputs: current stock and purchase history. The quality of the results hinges on how close this model is to reality.

What fifo() really returns

fifo() does not output the age of each unit. Instead, it assigns the current stock to purchase order lines, returning how many units from each purchase line are still unsold. Once you have those unsold quantities, valuation, average purchase price, and stock age become direct aggregations.

table Items[id] = with
  [| as id, as StockOnHand |]
  [| "alpha", 7 |]
  [| "beta", 2 |]

table PO = with
  [| as ItemId, as Date, as Qty |]
  [| "alpha", date(2024, 1, 1), 5 |]
  [| "alpha", date(2024, 2, 1), 4 |]
  [| "alpha", date(2024, 3, 1), 6 |]
  [| "beta", date(2024, 1, 5), 2 |]
  [| "beta", date(2024, 2, 10), 3 |]

expect PO.id = PO.ItemId

PO.Unsold = fifo(Items.StockOnHand, PO.Date, PO.Qty)

show table "Unsold by purchase line" with
  PO.ItemId
  PO.Date
  PO.Qty
  PO.Unsold
  order by [PO.ItemId, PO.Date]

Example output:

The remaining stock is assigned to the newest purchases, which is exactly what FIFO implies: the oldest units are considered sold first.

Valuation, average purchase price, and stock age

Once the unsold quantities exist, the financial KPIs are just weighted sums. The example below highlights a subtlety: a small remaining stock can be expensive if it is concentrated in recent, higher-cost purchase lines.

table Items[id] = with
  [| as id, as StockOnHand |]
  [| "alpha", 7 |]
  [| "beta", 2 |]

table PO = with
  [| as ItemId, as Date, as Qty, as NetAmount |]
  [| "alpha", date(2024, 1, 1), 5, 50 |]
  [| "alpha", date(2024, 2, 1), 4, 44 |]
  [| "alpha", date(2024, 3, 1), 6, 78 |]
  [| "beta", date(2024, 1, 5), 2, 6 |]
  [| "beta", date(2024, 2, 10), 3, 12 |]

expect PO.id = PO.ItemId

PO.Unsold = fifo(Items.StockOnHand, PO.Date, PO.Qty)
PO.UnitCost = PO.NetAmount / PO.Qty

today = date(2024, 3, 15)
PO.Age = today - PO.Date

Items.StockValue = sum(PO.Unsold * PO.UnitCost)
Items.AvgPurchase = Items.StockValue /. sum(PO.Unsold)
Items.AvgAge = sum(PO.Unsold * PO.Age) /. sum(PO.Unsold)

show table "FIFO valuation" with
  Items.id
  Items.StockOnHand
  Items.StockValue
  Items.AvgPurchase
  Items.AvgAge
  order by Items.id

Example output:

alpha keeps newer, pricier units, so its average purchase price is high even with modest stock. beta keeps older units, so its average age is high even though the absolute stock is small.

Limits of FIFO analysis

FIFO is only as good as its purchase history. If the history is truncated, old units appear cheaper and younger than they really are. Replaying purchase and sales sequences to infer past stock-outs is often unstable: a single missing or duplicate line derails the reconstruction into small, misleading residuals. Finally, when serial numbers exist and are tracked, FIFO is a rough proxy for a far richer data source.

Matching sales and purchases with assoc.quantity

When you need to link sales to purchase lots (for margin attribution or multi-source analyses), assoc.quantity() performs FIFO matching between two chronological sequences. It is sensitive to identifier quality and to data completeness, so the matching should be interpreted as a model, not an audit.

table Sales = with
  [| as SaleId, as SaleDate, as SoldQty |]
  [| "s1", date(2024, 1, 5), 5 |]
  [| "s2", date(2024, 1, 12), 4 |]

table Purch = with
  [| as PONumber, as PurchaseDate, as PurchasedQty |]
  [| "p1", date(2024, 1, 1), 6 |]
  [| "p2", date(2024, 1, 10), 6 |]

Purch.Rank = rank() scan Purch.PurchaseDate
poCount = count(Purch.*)
Sales.POCount = poCount

table T max 100k = extend.range(Sales.POCount)
T.SaleIdentifier = Sales.SaleId
T.SaleDate = Sales.SaleDate
T.SoldQty = Sales.SoldQty
T.PurchaseIdentifier = same(Purch.PONumber) by Purch.Rank at T.N
T.PurchaseDate = same(Purch.PurchaseDate) by Purch.Rank at T.N
T.PurchasedQty = same(Purch.PurchasedQty) by Purch.Rank at T.N

T.AssociatedQuantity = assoc.quantity(
  T.SaleIdentifier, T.SoldQty,
  T.PurchaseIdentifier, T.PurchasedQty)
  sort [T.SaleDate, T.PurchaseDate]

where T.AssociatedQuantity > 0
  show table "FIFO match" with
    T.SaleIdentifier as "SaleId"
    T.SaleDate
    T.SoldQty
    T.PurchaseIdentifier as "PurchaseId"
    T.PurchaseDate
    T.AssociatedQuantity as "AssociatedQty"
    order by [T.SaleDate, T.PurchaseDate]

Example output:

The second sale consumes the remaining unit from the first purchase and then spills into the next lot, which is the core FIFO behavior.