The Shopify inventory spreadsheet that survives a real catalog

Most Shopify inventory spreadsheets are stock lists: quantities copied from an export, sorted by title, occasionally colour-coded. They are accurate on the day they're built and useless by Thursday, because the decision you make every week is not "what do I have" — it's "what do I reorder, how much, and by when." A sheet can answer that, but only if it holds five things most templates leave out: available stock rather than on-hand, velocity measured on in-stock days, supplier lead time, a reorder point, and an order quantity that respects case packs.

Here is the column set that does it, the three formulas that carry the weight, the ritual that keeps it alive — and, more useful than the template, the specific ways it breaks once your catalog gets real.

The division of labour, in one table

The single rule that keeps a sheet honest: Shopify is the ledger, the spreadsheet is the calculator. Every time the sheet starts storing quantities as its own truth, you have two ledgers and no authority.

The column set

One row per SKU per location. Twelve columns; resist adding a thirteenth unless it changes a decision.

Columns 5 and 7 are the ones templates skip, and they are the two that decide whether the output is trustworthy. Inbound stops you ordering what's already on the water; days in stock stops velocity from being diluted by weeks the SKU was unavailable.

Three formulas, one worked row

Take an olive hoodie, HOOD-OLV-M, illustrative numbers:

• Available 82 units (96 on hand, 14 committed to unfulfilled orders), no inbound.
• Sold 72 units in the last 30 days, in stock the whole time → velocity = 72 ÷ 30 = 2.4/day.
• Supplier lead time 34 days measured from your last six POs; safety buffer 12 days.
• Reorder point = 2.4 × (34 + 12) = 2.4 × 46 = 110.4 → 111 units.
• Days of cover = 82 ÷ 2.4 = 34.2 days — under the 46 days a reorder cycle needs, so status flips to ORDER.
• Order quantity = 111 − 82 = 29, case pack 10 → order 30.

One row, one decision, five numbers. If your sheet cannot produce those five per SKU, it's an inventory record, not a buying tool.

Two refinements that matter more than they look:

Safety days belong in the formula, not in your head. A fixed 12 days is a starting point; a supplier who misses dates needs 20, and a domestic supplier with a 9-day lead time needs 3. Store it per supplier so it updates in one place.

Case packs round up, always. Rounding to the nearest case under-orders by up to half a case every cycle, which compounds quietly into permanent thinness on your best sellers. The supplier's packaging is a constraint on the answer, not a detail to fix later.

Getting data in without corrupting it

Two exports feed the sheet, and both should be pasted as values, never as links:

1. Inventory. Products → Inventory → Export, All states format, all locations. The columns you need are available, committed, incoming, and on hand; the Shopify inventory CSV guide documents which fields are read-only and which write back.
2. Sales. Analytics → Reports → sales by product or variant, last 30 days. Availability varies by plan, so check your own Analytics section before planning a ritual around a report you don't have.

Then timestamp the paste in a header cell (Snapshot: 2026-11-12 08:40) and leave the raw export in its own tab. The timestamp is not decoration — it's the only thing that later explains why the sheet and the shelf disagreed.

The weekly ritual

A sheet that isn't refreshed weekly is worse than no sheet, because it still looks authoritative. Five steps, roughly 25 minutes at 150 SKUs:

1. Paste a fresh inventory export over columns 4 and 5 (values only).
2. Refresh the 30-day sales numbers and recalculate velocity.
3. Sort by days of cover ascending and work the ORDER rows.
4. Place the orders, and update the inbound column the same day.
5. Update any lead time that has drifted — this is the step everyone skips and the one that quietly invalidates every reorder point downstream.

Put it on a recurring calendar block. The failure mode isn't a bad formula; it's a Monday that becomes a Tuesday that becomes "next week."

Where it breaks

Five failure modes, each with an early warning sign, because the sheet never announces its own obsolescence:

The second one deserves emphasis because it is invisible by construction: once a SKU returns from zero, the sheet has no memory that it was ever dark, so the average looks like a demand decline. The correction is to divide by in-stock days, which requires knowing which days those were — the argument for keeping a dated snapshot archive alongside the decision sheet.

The scale test

Three sizes, three honest answers:

• Under ~150 SKUs: the ritual takes 20 minutes and the sheet is genuinely the best tool available. Keep it.
• ~150 to ~500 SKUs: still workable, but the copying starts to dominate and the lead-time column gets stale. This is where most stores add historical snapshots and a stricter ritual rather than a tool.
• Above ~500 SKUs, or more than two suppliers changing dates: the maintenance cost is now measured in hours per week, and the failure modes above start appearing in pairs. That's the point to read when spreadsheets stop working and decide deliberately rather than after a bad quarter.

One adjacent file worth building at the same time: the inventory list template, a separate static record of what you own at cost, dated, for insurance and year-end. Same export, different job — and merging them is how a claim document ends up cluttered with velocity math.

Common questions

What columns should a Shopify inventory spreadsheet have? At minimum: SKU, variant, location, available quantity, inbound quantity, units sold over 30 days, days actually in stock, velocity, supplier lead time, days of cover, reorder point, and a status/order-quantity column. The last five are what turn a stock list into a buying tool.

How do I calculate a reorder point in a spreadsheet? Velocity × (lead time + safety days). A hoodie selling 2.4 units a day with a 34-day lead time and a 12-day buffer: 2.4 × 46 = 110.4, rounded to 111. Order when available stock — not on hand — reaches that number.

Why does my spreadsheet disagree with Shopify? Because it's a snapshot: shrinkage, unlogged damage, orders placed since your export, and till sales all happen after the paste. The sheet is only as true as its timestamp. If the two disagree by more than a rounding error, the shelf is the arbiter and a count is the fix.

Should velocity be average daily sales? Not plain average — divide by days the SKU was actually in stock. Calendar-day averages treat stockout weeks as zero demand, so a bestseller that was dark for a fortnight reads as slowing exactly when it's selling fastest when available.

When should I move off the spreadsheet? When the ritual slips two weeks in a row, when the computed columns stop being maintained, or when a stockout costs money after the sheet already flagged it. Below ~500 SKUs with stable lead times, the sheet is usually the right tool; the failure is almost always the ritual, not the tool.

Next step

Build the twelve columns for your twenty fastest sellers this week — not the whole catalog — and run the ritual once, end to end, at close of day. The SKUs whose days of cover sit below lead time plus safety days are your first orders; the formulas behind them are unpacked in the reorder point formula.