Weeks of cover spreadsheet template for Shopify (with formulas)
By Andrew Simpson, founder of Canopy Inventory. Last updated .
Quick answer
Put one row per SKU with its available stock and units sold in the last 30 days. Then add formulas for daily sales (units ÷ 30), weeks of cover (stock ÷ weekly sales), stockout date (today + stock ÷ daily sales), reorder-by date (stockout date − lead time − safety stock days) and suggested order (days to cover × daily sales − stock − units on order). The full column layout is below, ready to copy into Google Sheets or Excel, with a free CSV template to download.
The column layout
Put these headings in row 1, columns A to U. Columns A to E and J, K, M, N, P, R and S are inputs you type or paste. The rest are formulas. Weeks of cover is how long your stock lasts at your current sales rate.
Prefer a file? Download the CSV template with these headings and two example rows, formulas included.
- A SKU: from your Shopify export
- B Product and variant: the product title and option, for reading only
- C Available stock: available units across the locations you sell from
- D Units sold: units sold in the sales window, for example the last 30 days
- E Days in window: 30 (or whatever window you used in D)
- F Daily sales: formula
- G Weekly sales: formula
- H Weeks of cover: formula
- I Stockout date: formula
- J Lead time (days): order placed to stock on sale
- K Safety stock (days)
- L Reorder-by date: formula
- M Target cover (days): how many days of stock you want after the delivery lands
- N Units on order: open purchase orders not yet received
- O Suggested order: formula
- P Pack size: the carton or pack quantity (1 if none)
- Q Rounded order: formula
- R Minimum order (MOQ): 0 if none
- S Cost per item: from Shopify
- T Cash to buy: formula
- U Status: formula (Order now, OK, No sales or Out of stock)
The formulas (row 2, then copy down)
- F2 Daily sales: =IF(E2>0, D2/E2, 0)
- G2 Weekly sales: =F2*7
- H2 Weeks of cover: =IF(G2>0, ROUND(C2/G2, 1), IF(C2>0, "no sales", 0))
- I2 Stockout date: =IF(F2>0, TODAY()+ROUNDDOWN(C2/F2,0), "")
- L2 Reorder-by date: =IF(I2="", "", I2-(J2+K2))
- O2 Suggested order: =IF(F2>0, MAX(0, ROUNDUP((J2+M2+K2)*F2-(C2+N2), 0)), 0)
- Q2 Rounded order: =IF(O2>0, MAX(CEILING(O2, MAX(1,P2)), R2), 0)
- T2 Cash to buy: =IF(S2>0, Q2*S2, "add cost")
- U2 Status: =IF(C2=0, "Out of stock", IF(L2="", "No sales", IF(L2<=TODAY(), "Order now", "OK")))
Format columns I and L as dates. Sort by column L, earliest first, to see what to order first. These formulas work the same in Google Sheets and Excel. The CSV template has the headings and two example rows with the formulas in place: open it in Google Sheets (File > Import) or Excel, then replace the example rows with your own SKUs.
Worked example: two rows
Example (round made-up numbers).
Row 2: 300 available, 600 sold in 30 days. Daily sales 20, weekly 140, weeks of cover 2.1. Stockout date is 15 days from today. With a 30-day lead time and 7 safety days, the reorder-by date was 22 days ago, so the status is Order now. With 60 days of target cover and nothing on order, the suggested order is (30 + 60 + 7) × 20 − 300 = 1,640 units. With packs of 24, the rounded order is 1,656. At a cost per item of £4, cash to buy is £6,624.
Row 3: 500 available, 30 sold in 30 days. Daily sales 1, weekly 7, weeks of cover 71.4. Stockout date is 500 days away and the reorder-by date is 463 days away, so the status is OK. The suggested order is 0, because (30 + 60 + 7) × 1 = 97 units needed is less than the 500 in stock. This one is a candidate for overstock.
How to fill the template from Shopify
- Available stock (C): in the Shopify admin go to Products > Inventory, click Export, and choose available for the locations you sell from. Add up the location columns if you have more than one.
- Units sold (D): the Inventory sold daily by product report gives the average units sold per day for each variant over the period you choose. You can paste it straight into column F instead, and skip D and E.
- Cost per item (S): export your products; the product CSV includes a "Cost per item" column.
- Units on order (N): from your open purchase orders. Stock on a purchase order or transfer shows as incoming in Shopify until it is received.
- Match rows by SKU, with VLOOKUP or XLOOKUP, so you can paste new exports each week without retyping.
Why inventory spreadsheets fail (and how to avoid it)
- They go stale. A sheet is only as current as the last paste. Set a fixed day each week to refresh it.
- Formulas break when copied. Keep inputs and formulas in separate columns, as above, and protect the formula columns.
- Stock on order is forgotten. Without column N, you reorder stock that is already on its way.
- Bundles are missed. If you sell bundles, units sold inside a bundle use up component stock but may not show against the component in your sales export. Add them in by hand.
- One person understands it. Write the rules on a notes tab so someone else can run it.
- It does one window. A 30-day rate can lag a product that has just taken off. Compare it with a 7 or 14-day rate on best sellers.
How Canopy Inventory does it
Canopy Inventory, a read-only Shopify app coming soon to the Shopify App Store, does the same sums as this template for every SKU, from your Shopify data. It shows weeks of cover over a 7, 14, 30, 45, 60 or 90-day window, the stockout date, reorder-by date, suggested buy quantity rounded up to your pack size and raised to your minimum order, and cash to buy at your Shopify cost per item, with a CSV export.
A few differences from a spreadsheet. It counts units sold inside bundles towards each component, net of cancellations and refunds. It leaves cash to buy blank, rather than zero, when a product has no cost. It only suggests a buy when stock would run out within your lead time plus safety stock days plus 70 days. Incoming purchase orders are entered by hand on the Growth and Pro plans. The Free plan covers your 50 most recently sold SKUs. See how Canopy calculates.
Skip the weekly paste: weeks of cover for every SKU, from Shopify
Canopy Inventory shows how many weeks of stock each SKU has left, when it runs out and what to reorder. Read-only. Free for your 50 most recently sold SKUs.
Sources
- Shopify Help Center, exporting inventory with a CSV file: help.shopify.com/en/manual/products/inventory/setup/inventory-csv
- Shopify Help Center, inventory reports (Inventory remaining per product, Inventory sold daily by product, Products by sell-through rate, Month-end inventory value): help.shopify.com/en/manual/reports-and-analytics/shopify-reports/report-types/default-reports/inventory-reports
- Shopify Help Center, product CSV columns including Cost per item: help.shopify.com/en/manual/products/import-export/using-csv
- Shopify Help Center, inventory states (available, committed, unavailable, on hand, incoming): help.shopify.com/en/manual/products/inventory/managing-inventory-quantities/inventory-states
- How Canopy Inventory calculates each figure: How Canopy calculates
Last updated 6th November 2026: rewritten as a weeks of cover spreadsheet template with the column layout, formulas, a CSV download and a worked example.
Frequently Asked Questions
Yes. This page gives the full column layout and formulas to copy into Google Sheets or Excel, and a CSV file with the headings and two example rows.
Weeks of cover = available stock ÷ (units sold in the window ÷ days in the window × 7). With stock in C2 and weekly sales in G2: =IF(G2>0, C2/G2, "no sales").
Yes. The formulas use functions that work the same way in Google Sheets and Excel: IF, MAX, ROUND, ROUNDUP, CEILING and TODAY.
At least weekly, on a fixed day, and more often for best sellers close to their reorder-by date.
They go out of date between pastes, formulas break when rows are copied, stock on order and bundle sales are easy to miss, and often only one person knows how the sheet works.
Related pages
Related Articles
Reorder Point Formula: How to Calculate When to Reorder Stock
Reorder point = daily sales x lead time + safety stock. Worked examples in units and days, how to know when to reorder, and how to set it up from Shopify.
Safety Stock Formula: Days, Units and Statistical Methods
Four ways to calculate safety stock: days, fixed units, max minus average and the statistical formula, with one worked example and Shopify steps.
Why Shopify Brands Run Out of Stock (and What It Costs)
Why best sellers run out, how to work out the stockout date and what a stockout costs, and how to spot stockout risk in Shopify. With a worked example.
Get the Canopy Inventory install link
Canopy Inventory is coming soon to the Shopify App Store. One email the day it's live. Free for your 50 most recently sold SKUs.