Cash trapped in stock: reconciling inventory between the warehouse, the ERP and the balance sheet.

Why three systems show three stock values

Ask a distributor or manufacturer how much inventory it holds at month-end and you will usually hear three answers:

• The Warehouse Management System (WMS): counts physical units in bins, loading bays and quarantine zones as soon as a barcode is scanned.
• The ERP inventory subledger: values each SKU’s on-hand balance using a cost formula—under IAS 2 Paragraph 25, either First-In, First-Out (FIFO) or weighted-average cost—based on posted goods receipts and goods issues.
• The statutory balance sheet (General Ledger): records the inventory control account plus manual journal entries for Goods Received Not Invoiced (GRNI), landed freight capitalisations, and IAS 2 Paragraph 9 write-downs to Net Realisable Value (NRV).

When those three layers are only reconciled once a year for the external audit, two bad things happen: finance faces a surprise year-end write-down in the P&L (required immediately under IAS 2 Paragraph 34), and buyers keep tying up cash in slow-moving stock because headline Days Inventory Outstanding (DIO) hides the tail.

Worked example: WMS to IAS 2 carrying value

Consider a synthetic industrial distributor holding 30,000 units across three velocity cohorts at quarter-end. Under IAS 2 Paragraph 9, each SKU must be carried at the lower of its weighted-average cost (c) and its Net Realisable Value (NRV = estimated selling price in the ordinary course of business less estimated direct costs to complete and sell, per IAS 2 Paragraph 6):

Synthetic IAS 2 valuation schedule. Write-down per unit = max(0, c − NRV): 8,000 × €4.00 = €32,000 on slow-moving; 4,000 × €27.50 = €110,000 on obsolete.

Bridging from the physical warehouse scan to the statutory balance sheet requires two explicit steps—the cut-off bridge and the valuation bridge:

Reconciliation check: €2,438,000 (WMS) − €38,000 (cut-off timing) = €2,400,000 (ERP subledger) − €142,000 (IAS 2.9 NRV reserve) = €2,258,000 (Balance Sheet).

Four operational causes of stock drift

When we build an inventory reconciliation model in the warehouse, the discrepancies between physical stock, the subledger and the general ledger trace back to four operational habits:

1. Goods received on the dock but not booked in the ERP. Warehouse staff scan a supplier pallet into the WMS so orders can be picked, while the ERP Goods Receipt Note waits on paperwork. If a shipment is picked before the receipt is posted, ERP stock goes negative and weighted-average cost calculation breaks.
2. Manual journals posted directly to the GL inventory control account. When finance books freight capitalisations or stock adjustments directly to the general ledger without routing them through the ERP item ledger, the balance sheet and the SKU subledger permanently diverge.
3. Unscrapped returns and quarantine stock. Damaged customer returns sit in a virtual “quarantine” warehouse location at full weighted-average cost, inflating reported stock availability and delaying reorder triggers.
4. Using a blanket percentage reserve instead of SKU-level NRV. Applying an arbitrary 5% provision across total inventory violates the spirit of IAS 2 Paragraph 9 (which requires item-by-item or homogeneous-group assessment) and hides which product families are actually underwater.

SQL model: SKU ageing & NRV write-down

Instead of assembling an obsolescence spreadsheet once a year, compute Days Inventory Outstanding (DIO), days since last dispatch, and the IAS 2.9 lower-of-cost-and-NRV test every night in SQL from the ERP movement ledger:

WITH sku_activity AS (
  SELECT
    s.sku_id,
    s.on_hand_qty,
    s.wav_unit_cost_eur,
    s.est_selling_price_eur - s.est_direct_selling_cost_eur          AS nrv_per_unit_eur,
    COALESCE(SUM(m.shipped_qty_90d), 0)                              AS shipped_qty_90d,
    DATE_DIFF('day', MAX(m.last_sale_date), CURRENT_DATE)            AS days_since_last_sale
  FROM erp_stock_balances s
  LEFT JOIN mart_sku_dispatch_summary m USING (sku_id)
  WHERE s.on_hand_qty > 0
  GROUP BY 1, 2, 3, 4
)
SELECT
  sku_id,
  on_hand_qty,
  ROUND(on_hand_qty * wav_unit_cost_eur, 2)                          AS gross_cost_eur,
  ROUND(on_hand_qty * GREATEST(0, wav_unit_cost_eur - nrv_per_unit_eur), 2)
                                                                     AS ias2_writedown_eur,
  ROUND(on_hand_qty * LEAST(wav_unit_cost_eur, nrv_per_unit_eur), 2) AS carrying_value_eur,
  ROUND(90.0 * on_hand_qty / NULLIF(shipped_qty_90d, 0), 1)          AS dio_days,
  CASE
    WHEN days_since_last_sale > 180 OR days_since_last_sale IS NULL THEN 'OBSOLETE_GT_180D'
    WHEN days_since_last_sale > 90                                  THEN 'SLOW_91_180D'
    ELSE 'ACTIVE_0_90D'
  END AS velocity_cohort
FROM sku_activity;

Fixing reorder rules with clean DIO

Once physical cut-offs and quarantine stock are separated from saleable inventory, commercial and purchasing teams can act on the numbers. Fast-moving SKUs with high margin and volatile supplier lead times get protected safety stock; SKUs crossing 90 days without a dispatch automatically trigger a purchasing freeze and a commercial clearance price list before their Net Realisable Value drops below cost.

Sources & accounting references

• IFRS Foundation — IAS 2 Inventories: Official Standard Summary & Text — Paragraph 6 (definition of Net Realisable Value), Paragraph 9 (measurement at the lower of cost and NRV), Paragraph 25 (permitted cost formulas: FIFO and weighted average cost), and Paragraph 34 (recognition of write-downs as an expense in the period they occur).