1) Table Design and Basic Rules

• ItemsTbl: Item, UOM, LeadDays, Z (service level), AvgDailyDemand, SdDailyDemand, D (annual demand), S (fixed order cost), H (annual holding cost rate)
• MovTbl: Date, Item, Warehouse, Type (IN/OUT/ADJ), Qty, UnitCost
• Use structured references for all calculations to keep formulas safe when columns move.

2) Calculate Stock on Hand (SOH) — SUMIFS vs. SCAN

Method A) Total by Item and Warehouse (SUMIFS)

=SUMIFS(MovTbl[Qty], MovTbl[Item], [@Item], MovTbl[Warehouse], E2, MovTbl[Type], "IN")
 -SUMIFS(MovTbl[Qty], MovTbl[Item], [@Item], MovTbl[Warehouse], E2, MovTbl[Type], "OUT")
 +SUMIFS(MovTbl[Qty], MovTbl[Item], [@Item], MovTbl[Warehouse], E2, MovTbl[Type], "ADJ")

Method B) Running Total by Date (SCAN) — Daily Balance Table

/* Running total by date for item i and warehouse w */
=LET(
  t, SORT(FILTER(MovTbl, (MovTbl[Item]=i)*(MovTbl[Warehouse]=w)), 1, 1),
  q, MAP(t[Type], t[Qty], LAMBDA(tp,qty, IF(tp="IN", qty, IF(tp="OUT",-qty, qty)))),
  SCAN(0, q, LAMBDA(a,v, a+v)) )

3) Reorder Point (ROP), Safety Stock, and EOQ — Formulas and Excel Functions

Safety Stock (Using Daily Demand Variability)

=ROUND( [@Z] * [@SdDailyDemand] * SQRT([@LeadDays]) , 0 )

Reorder Point (ROP)

=ROUND( [@AvgDailyDemand] * [@LeadDays] + [@SafetyStock] , 0 )

Economic Order Quantity (EOQ)

=ROUND( SQRT( 2*[@D]*[@S] / ([@H]*[@UnitCost]) ) , 0 )

Reorder Alert (When Current SOH Is at or Below ROP)

=IF( SOH <= [@ROP], "Reorder: "&TEXT([@EOQ],"#,##0"), "" )

4) Turnover, Days on Hand (DOH), and Inventory Value

/* If cost of goods sold (COGS) for the period is unavailable, approximate it as issues × unit cost */
Inventory value (current) = SOH * latest average unit cost
Turnover = total quantity issued / average inventory quantity
Days on hand (DOH) = IFERROR( average inventory quantity / average daily quantity issued , 0 )

/* Latest average unit cost — moving weighted average example */
=LET(t, FILTER(MovTbl, MovTbl[Item]=[@Item]),
 inQty, FILTER(t[Qty], t[Type]="IN"),
 inCost, FILTER(t[UnitCost], t[Type]="IN"),
 SUM(inQty*inCost)/SUM(inQty))

5) Create an Inventory Dashboard Without PivotTables

/* Item-level key metrics table (sorted Top N) */
=LET(
  u, UNIQUE(MovTbl[Item]),
  soh, MAP(u, LAMBDA(x, SUMIFS(SOH, Item, x))),
  rop, XLOOKUP(u, ItemsTbl[Item], ItemsTbl[ROP]),
  eoq, XLOOKUP(u, ItemsTbl[Item], ItemsTbl[EOQ]),
  t, HSTACK(u, soh, rop, eoq),
  SORT(t, 2, -1))     // Descending by SOH

/* Low-inventory list */
=FILTER( dashboard_table, dashboard_table[SOH] <= dashboard_table[ROP] )

Adding warehouse/category filters with slicers or data validation drop-down lists makes operations much easier.

6) Error, Performance, and Operations Checklist

• Use Excel Tables and structured references as the foundation — better formula stability
• Standardize date formats (convert text dates with DATEVALUE)
• Keep receipt and issue signs consistent (use Type to determine +/−)
• Manage monthly logs on separate sheets and combine them with VSTACK
• For large datasets, use LET to cache intermediate spill ranges → improved speed
• Conditional formatting: red background when SOH ≤ ROP; bold border below 0