The cost of chaos calculator: one spreadsheet for dark days, carrying, and dead stock cost

The cost of chaos calculator: one spreadsheet for dark days, carrying cost, and dead stock

Most stores can't answer a simple question: what does messy inventory actually cost me per month? Not abstractly — in dollars, from their own data. This article is a complete build guide for one spreadsheet that totals the three leaks: revenue lost while bestselling SKUs sat dark, the carrying cost of everything on the shelf, and the monthly cash bleed from stock that never sells. Every formula is given, every input is defined, and the whole thing runs off two exports you already have. Budget 45 minutes to build it and one hour a month to feed it. (The three costs each have a deeper owner article — linked as we go — this page is the one sheet that combines them.)

The sheet in one picture

Three calculators, one inputs tab, one summary:

• Tab 1 — Inputs: your numbers, entered once and refreshed monthly.
• Tab 2 — Leak calculator: estimated lost revenue from out-of-stock days, per SKU.
• Tab 3 — Carrying cost: what holding your current stock costs per month.
• Tab 4 — Dead stock burn: monthly cash bleed from stock with no sales.
• Tab 5 — Summary: the three totals, added, per month and per year.

One vocabulary note used throughout: out-of-stock days are a fact (the SKU had no sellable units that day); lost revenue from those days is an estimate (you don't observe sales that didn't happen). The sheet keeps that distinction visible on purpose.

Tab 1 — Inputs

Holding rate, honestly: don't pull a benchmark. Build it: capital cost (what your idle cash would otherwise earn, or what your credit line costs — varies by business) + insurance (from your policy) + storage (B4 ÷ average inventory value) + shrinkage/obsolescence allowance (your own shrink history as a % — start at 1% and correct it against reality). A store with cheap self-storage and paid-off stock might land near 15%; a store on expensive credit in paid warehousing might see 30%+. Your number, your inputs. The full component math lives in the carrying cost article; the sheet needs only the final rate.

Tab 2 — Leak calculator (estimated lost revenue from dark days)

Data source: export from Shopify (Analytics → Reports → Inventory, or your inventory export) with per-day availability, or — simpler — count "days the SKU was unavailable to sell" in your window by comparing sales records against stock levels. If you use any tool that produces a per-SKU out-of-stock-days ledger, paste it here. Set up columns:

The one methodological rule that matters: compute column C using only the days the SKU was in stock — dividing by all window days treats dark days as zero-demand days and understates the leak. This in-stock-only correction is the core of the out-of-stock cost method and the whole analysis inherits it.

Worked row (illustrative numbers): SKU TENT-2P-GRN, a two-person tent at $95, was dark 22 of the last 90 days. In-stock daily velocity: 2.1 units. Estimated lost revenue = 22 × 2.1 × $95 ≈ $4,389 (column E); estimated lost margin = $4,389 × 0.55 ≈ $2,414 (column F). One SKU. Sort column F descending, and the top ten rows are your next buying meeting's agenda.

Label check: those are estimates — demand during dark days isn't fully observable. The ranking is robust; the dollar figure is directional. Say that out loud to anyone you show the sheet to.

Tab 3 — Carrying cost (what the shelf costs per month)

Data source: one inventory value export (quantity × cost per item, summed).

Example with illustrative numbers: $150,000 inventory, 18% capital rate, $400/mo storage, 0.6% insurance, 1.4% shrink. Capital $27,000 + storage $4,800 + insurance $900 + shrink $2,100 = $34,800/yr ≈ $2,900/month to own that shelf. Every dead unit on it consumes that pro rata while contributing nothing — which is the bridge to Tab 4.

Tab 4 — Dead stock burn (the monthly bleed)

Data source: inventory export sorted by last-sell date.

Set the dead threshold in the header: 120 days is a common starting line for fashion-adjacent; parts and slow-category sellers may use 180. Filter B ≥ threshold, sum column F. Example: 38 SKUs totaling $26,400 at cost, holding rate 24% → burn = $26,400 × 0.24 ÷ 12 = $528/month, every month, until you act. That's the honest price of keeping them "in case it sells" — before any markdown, which is a separate decision covered by the dead stock playbook.

Tab 5 — Summary (the number you came for)

For our illustrative store: $2,414 leak from that one tent (one SKU of many — say the full top-20 sums to ~$3,800/mo estimated lost margin) + $2,900 carrying + $528 dead burn ≈ $7,200/month ≈ $87,000/year. Whatever your version of that number is, it's the budget for fixing the causes: better reorder timing, tighter buying, and a pruning pass. It's also, not coincidentally, the size of prize that makes discipline feel cheap.

Keeping it alive

• Refresh cadence: first Monday of the month. Refresh Tabs 2–4 from fresh exports; update the 90-day window; log the summary in a running row so you get a trend, not a snapshot.
• Sensitivity row worth adding: velocity ±20% in Tab 2 (C2 and C2×0.8 side by side). If the leak ranking barely changes, you can trust the priorities even while doubting the dollar precision.
• Where it breaks: missing price or cost per item (Tab 2 column D and Tab 4 column D both lie), uncounted stock (all three tabs inherit phantom units), and manual velocity from memory. The spreadsheet is honest; the exports have to be.

One honest scope note: this sheet is a monthly rear-view mirror. If you'd rather have the out-of-stock-days ledger and estimated lost sales per SKU computed continuously from your Shopify data instead of by hand, that's a small part of what our tool does at $9.99/mo — but the spreadsheet above works fine, and plenty of stores should start there before paying anyone for anything.

Common questions

How do I calculate the cost of out-of-stock days? Days dark × in-stock-only daily velocity × unit price for lost revenue, then × your margin rate for lost margin — the in-stock-only correction and why it matters are in the out-of-stock cost method.

What is a good holding rate for carrying cost? There's no universal number to copy — it's the sum of your own capital cost, storage, insurance, and shrink/obsolescence rates, and it varies widely by business. Build it from your inputs: a credit-financed store in paid warehousing lands far above a self-funded one in a garage. Recompute it whenever any component changes materially.

How do I find my dead stock in Shopify? Export inventory with last-sell dates (or sort an inventory report by units sold in a long window), then list every SKU with no sales beyond your threshold — 120 or 180 days depending on category. Multiply value at cost by your monthly holding rate to get each SKU's burn. The deeper detection and disposal sequence is covered in our dead stock article.

Is lost sales estimation accurate? It's a structured estimate, not a measurement — you can't observe purchases that didn't happen. The method is conservative and honest when velocity uses in-stock days only, and it's most trustworthy as a ranking: which SKUs cost the most. Use the dollars for prioritization and communication, not for bookkeeping.

How often should I run this calculator? Monthly is the working cadence — refresh exports on a fixed day, log the summary total, and watch the trend. A falling cost of chaos over six months means the system is working; a flat one usually means the reports are being read but the buying decisions aren't changing.

Where to go next

Build Tab 2 first — it's the smallest effort and the biggest immediate payoff, because the top of that ranking is your next purchase order. Then read the method behind each number: the out-of-stock cost math, the carrying cost components, and the dead stock detection playbook. The summary total is the score; those three articles are how you move it.