Mastering Reorder Points: Your Spreadsheet Guide to Smarter Shopify Inventory

Hey fellow store owners! Let's talk inventory. Specifically, that sweet spot where you know exactly when to reorder so you're never caught with empty shelves (or a warehouse full of dead stock). With Stocky gone, a lot of us are looking for alternatives, and while there are some fantastic apps out there, a recent discussion in the Shopify community really highlighted something crucial: understanding the underlying math is gold, no matter what tools you use. And guess what? You can absolutely do a lot of this with a good old spreadsheet.

Our community expert, Zecathop, kicked off a fantastic thread showing us the foundational mechanics of manual reorder point modeling. It's the kind of practical advice that resonates because it puts the power back in your hands. But like any good discussion, the community chimed in with even more invaluable insights, refining the process and pointing out common pitfalls. Let's dive into how you can set this up for your store.

The Core Idea: Reorder Points in a Nutshell

At its heart, calculating a reorder point isn't rocket science. Zecathop broke it down beautifully with a simple example:

Imagine you sell a candle that moves about 4 units a day. Your supplier takes 30 days to deliver. The moment you place an order, you're going to sell roughly 4 units/day × 30 days = 120 candles before that new stock even hits your shelves. If you wait until you're down to, say, 50 candles, you'll be out of stock for weeks.

So, you add a little buffer for those unexpected delays or sudden sales spikes – let's say 10 extra days of sales, which is 40 candles in this case. Add that to your 120, and boom: your reorder point is 160 candles. That's it! The formula is straightforward: (units per day × supplier lead time in days) + buffer.

Your Spreadsheet Blueprint: The DIY Method

Ready to build this out? Here's how you can create your own reorder point calculator in a spreadsheet, drawing from Zecathop's initial outline:

Step-by-Step Setup:

1. Export Your Sales Data: Head to your Shopify admin: Analytics → Reports → Sales by product variant. Choose 'last 90 days' (or a longer period if your sales cycles are longer, or a seasonal period if applicable), and export it as a CSV.
2. Open Your Spreadsheet: Import that CSV into Google Sheets, Excel, or your preferred spreadsheet tool.
3. Add These Columns: Set up your sheet with the following columns. The formulas here assume your data starts on row 2.

When column H shouts "YES," it's your cue to place an order. For the quantity, Zecathop suggests ordering enough to cover your usual cycle (e.g., units per day × 60 for a two-month cycle), minus what you currently have.

Community Wisdom: Supercharging Your Spreadsheet

This is where the community insights really shine, turning a good system into a great one. Dharmendra_Ahluwalia and lumine brought up some critical points that can make or break your inventory accuracy:

1. Tackling the "Out-of-Stock" Trap (The Most Expensive Mistake!)

This was a huge point both Zecathop and lumine highlighted. If an item was out of stock for 50 of your 90 days, but sold 20 units during the 40 days it was available, your spreadsheet will wrongly calculate its average daily sales based on 90 days. This makes it look like a slow seller, leading you to under-order continually. The fix? Divide by the days the item was actually in stock and sellable, not just calendar days. Lumine notes that Shopify doesn't easily hand you this history, so you might need to track it manually or through an inventory app.

2. Real Lead Times vs. Promised Ones

Your supplier might promise 15 days, but what's their actual track record? Lumine and Zecathop both stress that you should use the real, historical average lead time (from order placed to stock received) in Column D. That buffer you're adding is mostly there to cover the gap between what's promised and what actually happens. If you have old Stocky data, that historical PO information is gold; grab it before it disappears!

3. Variant-Level Precision is Key

Lumine hit the nail on the head here: average at the variant level, not product level. If you sell a t-shirt in S, M, L, and XL, and the large is your bestseller, averaging sales across the whole product will tell you to reorder sizes that are already piling up, while your large sizes run out. Focus on individual SKUs.

4. Beyond the Static 90-Day Average

Dharmendra pointed out that a fixed 90-day window can be slow to react to sudden velocity spikes or seasonal trends. Zecathop also warned: "August doesn't predict November." If your business is seasonal (hello, Q4!), you need to look at last year's sales for that specific period, or adjust your categories manually. For dynamic calculations, some advanced systems (like the one Dharmendra's team built) can use rolling velocity and real-time data.

5. Handling Oddballs

• Wholesale Orders: If a single large B2B order skews your average for a smaller product, remove it before calculating.
• Slow Sellers: Products that sell less than ~1 unit a week don't average well. For these, Zecathop suggests a simpler approach: "when I'm down to 2, I order 10."
• Case Packs: Lumine wisely suggests adding a column for case pack quantity. A calculated reorder point of 147 might actually mean you order 150 if your supplier only ships in packs of 10.

6. A Smarter Buffer (Optional, but Handy!)

If you want a more sophisticated buffer that adjusts for how volatile a product's sales are, Zecathop shared this gem: =1.65*STDEV(daily sales)*SQRT(D2). This gives more cushion to items with unpredictable sales (swinging between 0 and 40) than to those that sell a steady 4 units a day. The simple 10-day buffer is perfectly fine to start, though!

When the Spreadsheet Stops Being Enough

Zecathop candidly admits that this spreadsheet method works for years for many stores. But it does have its limits. Once you hit a few hundred SKUs, multiple locations, or need weekly (or even daily) updates, keeping it current manually becomes a huge time sink. The math itself doesn't get harder, but keeping the data fresh and accurate does.

This is where automated solutions, like the ones mentioned by Dharmendra and lumine (both of whom build inventory apps), come into play. They can continuously recalculate dynamic reorder points, leverage real-time sales velocity, and even automate purchase order triggers. But here's the kicker: as Zecathop and lumine both emphasized, the math is the math. If you can't reproduce your app's suggestions by hand using these fundamental principles, you should probably be a little suspicious of the app.

So, whether you stick with your trusty spreadsheet for a while or decide to invest in an app, understanding these core principles of reorder point calculation will empower you to make smarter inventory decisions for your Shopify store. It's about being proactive, not reactive, and ensuring you have what your customers want, when they want it.