How We Cut Stockouts
by 35% in 90 Days
A detailed walkthrough of the automated inventory forecasting system built for a consumer electronics brand — the data model, the Google Sheets architecture, and the measurable impact on revenue and IPI score.
The Problem
The brand was losing sales in a way that never showed up as a single dramatic failure — it showed up as a slow, recurring drip. A best-seller would run out for four or five days every few weeks, just long enough to lose its search ranking momentum and hand ready buyers to a competitor. Reordering decisions were made from memory and a rough gut sense of "we're probably fine," because the only real data source was a sales report nobody had time to reconcile against incoming shipment schedules.
This is a common failure mode for growing consumer electronics brands specifically: SKU counts had grown past what a spreadsheet-free process could track, but the business wasn't yet large enough to justify enterprise inventory software. The gap between "too big for gut feel" and "not big enough for an ERP" is exactly where this system needed to live.
The Data Model
What actually needed to be trackedBefore building anything, the forecasting logic needed three inputs the brand already had scattered across different places, pulled into one consistent structure:
- Sales velocity, trailing and weighted. A rolling 30/60/90-day average per SKU, weighted toward the most recent 30 days so the model reacted to real demand shifts instead of smoothing them away.
- Lead time per supplier, not per category. Different components and suppliers had genuinely different fulfillment timelines, and the original process had been applying one blanket lead-time assumption across all of them — quietly wrong for at least a third of the catalog.
- In-transit and on-hand inventory, reconciled daily. The biggest single fix here wasn't a formula, it was simply making sure "what we have" and "what's coming" were pulled from source data automatically instead of manually updated whenever someone remembered to.
The reorder point formula that came out of this was intentionally simple: (daily velocity × lead time) + safety stock buffer, with the safety stock buffer itself scaled to each SKU's demand volatility rather than a flat number applied across the board. Volatile, fast-moving SKUs got a larger buffer; stable, slow movers got a smaller one — tying the buffer to actual variance rather than intuition was what most improved forecast accuracy.
The Google Sheets Architecture
Why sheets, not softwareAn off-the-shelf inventory platform was evaluated and shelved — the SKU count and team size didn't justify the cost or the implementation time, and the brand needed something live within weeks, not a quarter. Google Sheets, structured properly with Apps Script automation, turned out to be enough to run the full model.
The architecture split into four connected tabs, each with one clear job:
- Raw_Sales_Feed pulled daily order data in automatically via a scheduled Apps Script trigger connected to the sales report export, removing the manual copy-paste step that used to introduce delays and errors.
- Lead_Time_Master held the per-supplier, per-SKU lead time assumptions in one place, so updating a single supplier's shipping delay updated every downstream calculation instead of requiring a manual hunt through formulas.
- Forecast_Engine ran the weighted velocity and reorder point calculations referenced above, recalculating automatically each time the sales feed refreshed.
- Reorder_Alerts flagged any SKU that had crossed its reorder point and pushed a notification — email and a Slack webhook — directly to the ops team, replacing the "someone remembers to check" step entirely.
The core design principle was that no human step should be required between a SKU crossing its reorder threshold and someone being told about it. Every step before that alert is fully automated; the only manual decision left is approving and placing the purchase order.
The 90-Day Rollout
- Weeks 1–3 — Data audit and cleanup. Reconciling actual on-hand and in-transit inventory against what records claimed, since the model's accuracy was capped by the accuracy of what it was fed. This step alone surfaced several SKUs with quietly wrong stock counts.
- Weeks 4–7 — Model build and validation. Building the four-tab structure, then running it in parallel with the existing manual process for two full weeks to compare its reorder recommendations against what the team would have decided on their own, before trusting it fully.
- Weeks 8–12 — Live rollout and tuning. Switching over fully, then tightening the safety stock buffers SKU by SKU as real alert accuracy data came in — the first version of the model was slightly conservative on fast-moving SKUs and slightly loose on slow movers, both corrected during this window.
The Results
By day 90, stockout incidents were down 35% against the trailing quarter, and — more importantly for long-term health — the brand's Inventory Performance Index score improved measurably, which fed back into better storage limits and search visibility on the platform side. Revenue impact followed directly from keeping best-sellers in stock through the periods that used to see recurring gaps.
The system didn't make better guesses than the ops team — it made sure nobody had to guess in the first place.
What Actually Made the Difference
- Per-supplier lead times, not category averages. This single change corrected the largest source of error in the old reorder logic.
- Volatility-scaled safety stock. A flat safety buffer either overstocks slow movers or leaves fast movers exposed — scaling it to each SKU's own demand variance fixed both problems at once.
- Automation at the alert step, not just the calculation step. The forecasting math was only half the fix. Removing the human "remember to check the sheet" step is what actually stopped the recurring stockouts.
This didn't require enterprise software or a data science team — it required treating the reorder decision as a system with clear inputs and a clear trigger, instead of a judgment call someone had to remember to make. A properly structured spreadsheet with the right automation behind it closed a gap that was costing real revenue every month, and did it inside a single quarter.
