Inventory Control & Reorder Automation
A Google Sheets inventory system for an 850-SKU catalog: ABC classification, automated reorder flags with lead-time-aware safety stock, and a stockout dashboard that replaced guesswork purchasing.
View code & data on GitHub ↗The Problem
A retail operation was ordering stock on intuition: no reorder points, no visibility into which SKUs actually mattered, and a stockout rate quietly costing sales. The purchasing spreadsheet was a flat list updated by hand once a week.
The build goal: a Sheets-native system where daily sales and receipt logs flow into one movements tab, and everything else (current stock, velocity, ABC class, reorder flags, and a purchasing queue) computes itself. No scripts required, so the team can maintain it.
Approach
Movement ledger design
Single append-only Movements tab (date, SKU, location, type, qty) with data-validation dropdowns. Current stock per SKU is a SUMIFS over the ledger, never a hand-edited number.
Velocity & variability layer
ARRAYFORMULA-computed 30 and 90-day sales velocity and demand variability per SKU, the inputs for lead-time-aware safety stock.
ABC classification
Ranked SKUs by 12-month revenue contribution with a running cumulative share. A is the top 80% of value, B the next 15%, C the final 5%, computed live as sales data lands.
Reorder point engine
Reorder point equals average daily demand times supplier lead time plus safety stock (z times demand std dev times the square root of lead time). Flags fire automatically when projected stock crosses the line.
Purchasing queue & stockout dashboard
A FILTER-driven purchasing tab lists only flagged SKUs with suggested order quantities. A dashboard tracks stockout rate, weeks of cover, and dead stock value.
The Work
Representative excerpts from the analysis: the queries and transformations doing the heavy lifting. The full runnable project lives in the GitHub repo.
// Current stock: receipts minus sales, per SKU+location
=SUMIFS(Movements!E:E, Movements!B:B, $A2,
Movements!D:D, "IN")
- SUMIFS(Movements!E:E, Movements!B:B, $A2,
Movements!D:D, "OUT")
// 30-day daily velocity
=ROUND(SUMIFS(Movements!E:E, Movements!B:B, $A2,
Movements!D:D, "OUT",
Movements!A:A, ">=" & TODAY()-30) / 30, 2)
// Reorder point: demand over lead time + safety stock
// (1.65 z-score for a 95% service level)
=ROUND($D2 * XLOOKUP($A2, Suppliers!A:A, Suppliers!D:D)
+ 1.65 * $E2 *
SQRT(XLOOKUP($A2, Suppliers!A:A, Suppliers!D:D)), 0)
// Flag
=IF($C2 <= $F2, "REORDER", "")// Revenue per SKU (12 mo), then rank and cumulative share
=ARRAYFORMULA(
IF(LEN(A2:A)=0, "",
SUMIFS(Sales!D:D, Sales!B:B, A2:A,
Sales!A:A, ">=" & EDATE(TODAY(), -12))))
// Cumulative revenue share of ranked SKUs
=ARRAYFORMULA(
IF(LEN(A2:A)=0, "",
SUMIF(RankCol, "<=" & RankCol, RevCol)
/ SUM(RevCol)))
// ABC class
=ARRAYFORMULA(
IF(LEN(A2:A)=0, "",
IFS(CumShare <= 0.8, "A",
CumShare <= 0.95, "B",
TRUE, "C")))
// Purchasing queue tab: only what needs ordering
=FILTER({SKUs!A:A, SKUs!C:C, SKUs!F:F, SKUs!G:G},
SKUs!H:H = "REORDER")What the Data Shows
Revenue share by ABC class
% of 12-month revenue
18% of SKUs carry 78% of revenue. Purchasing attention should follow the value, not the SKU count.
Key Findings
18% of SKUs (A-class) drive 78% of revenue, but the old manual process gave every SKU equal purchasing attention.
Backtesting the reorder engine against 12 months of history: stockout rate would have dropped from 6.2% to an estimated 1.8% of SKU-weeks at a 95% service level.
C-class dead stock (no sales in 90+ days) tied up about 11% of inventory value, capital sitting on shelves that ABC visibility makes impossible to ignore.
Three suppliers accounted for 70% of late deliveries. Their real lead times ran 4 to 6 days beyond quoted, which the safety-stock formula now absorbs explicitly.
Weeks-of-cover conditional formatting exposed 23 SKUs simultaneously overstocked in one warehouse and stocked out in another: a transfer problem, not a purchasing problem.
Recommendations
Order A-class weekly, C-class monthly
Purchasing cadence should follow value concentration. The 18/78 split justifies dedicating most attention to 150 SKUs, not 850.
Clear the dead-stock 11% with a one-off event
Discounting 90-day-dormant C-class stock converts shelf capital into cash and warehouse space with minimal margin damage.
Use measured, not quoted, lead times
Safety stock built on suppliers' quoted lead times understates risk. The ledger now measures actual delivery gaps per supplier automatically.
Check inter-warehouse transfers before ordering
The overstock and stockout mismatch list should be step one of the purchasing routine. Transfers are faster and cheaper than new POs.