Using AI to build a simple inventory reorder alert system from your spreadsheet so you stop running out of stock at the worst time

Small businesses lose an estimated 4–8% of potential annual sales to stockouts — for a $300K/year operation, that's $12,000–$24,000 in missed revenue sitting on the table because a spreadsheet didn't flag a low-stock item in time. This post walks you through building an inventory reorder alert system in Google Sheets to track reorder points for your small business, using AI to handle the math and the code so you don't have to. The setup takes under two hours and eliminates the manual stock-checking that most small business owners either do inconsistently or skip entirely.

What you need before you start

Google Sheets{:target="_blank"} — free with any Google account. Every feature used in this post (conditional formatting, IF() formulas, Apps Script email alerts) is available at no cost. No paid tier required.

ChatGPT{:target="_blank"} or Claude{:target="_blank"} — either works for this task. ChatGPT's free tier (GPT-4o as of mid-2026) handles formula writing and Apps Script generation. Claude's free tier (claude-3.7-sonnet as of mid-2026) performs comparably. Paid plans ($20/month for either) are not required unless your SKU list is very large and you're pasting a lot of data at once.

Time required: 30–45 minutes for basic setup with visual alerts. Add another 30–45 minutes if you want automated email notifications via Apps Script.

Skill level: No coding experience needed. Basic spreadsheet familiarity — you know how to enter data and copy a formula — is sufficient. If you export inventory from Shopify{:target="_blank"} (Products > Export in the admin panel), you already have your current stock data in a CSV ready to paste in.

Build the Spreadsheet Structure for Inventory Reorder Tracking

1. Open a new Google Sheet and name it something you'll actually find: "Inventory Reorder Tracker" with the current year works fine.

2. Create these six column headers in row 1: SKU / Item Name, Current Stock, Avg Daily Sales, Lead Time (Days), Reorder Point, Alert Status. These are the minimum columns the system needs to function. Skipping any of them breaks the formula logic downstream.

3. Enter your inventory data in rows 2 onward — one row per SKU. Current Stock and Lead Time are the two figures you need to look up. For Lead Time, use realistic numbers: domestic suppliers typically run 2–7 days; overseas suppliers (Alibaba and similar) typically run 21–45 days. Using the wrong lead time is the most common setup error, and it makes alerts fire too late.

4. Leave columns E and F (Reorder Point and Alert Status) blank for now. You'll populate those with formulas in the next section.

Analytical note: The six-column structure matters because it forces you to encode your actual business logic — your real lead times, your real daily sales rates — rather than guessing at safe stock levels by feel. Businesses that track this formally typically catch reorder needs 5–10 days earlier than those relying on visual shelf checks.

Use AI to Set Reorder Points in Google Sheets From Your Sales Data

The reorder point formula is: Reorder Point = (Average Daily Sales × Lead Time in Days) + Safety Stock, where Safety Stock = (Maximum Daily Sales − Average Daily Sales) × Lead Time. You don't need to memorize this. You need to give your AI assistant your sales data and let it do the arithmetic.

1. Copy a table of your recent weekly or monthly sales by SKU — even a rough one from your order history, a Shopify export, or a handwritten log.

2. Paste the data into ChatGPT or Claude with this prompt:

I run a small product business. Here is my sales data by SKU for the past 8 weeks. For each SKU, please calculate: (1) average daily sales, (2) maximum daily sales, (3) recommended safety stock using the formula (Max Daily Sales − Avg Daily Sales) × Lead Time, and (4) the reorder point using (Avg Daily Sales × Lead Time) + Safety Stock. My lead times are [X days for domestic suppliers / Y days for overseas suppliers]. Return the results as a table I can paste into a spreadsheet.

[Paste your sales data here]

3. Review the output table before pasting it in. Check that the average daily sales figures look right for your business — if you sell roughly 3 units/day of a SKU and the AI returns 0.4, your input data probably had weekly totals that it treated as daily. Correct the prompt and re-run.

4. Paste the AI-generated reorder point values into column E of your spreadsheet.

5. Enter this formula in cell F2 (Alert Status) and drag it down through all your SKU rows:

=IF(B2<=E2, "⚠️ REORDER NOW", IF(B2<=E2*1.25, "Order Soon", "OK"))

This gives you three states: a hard alert when you're at or below the reorder point, an early warning at 125% of the reorder point, and a clean "OK" when you're well-stocked. Adjust the 1.25 multiplier up if you want earlier warnings or down if the "Order Soon" alerts are firing too frequently for your operation.

Set Up Visual Alerts So Low Stock Is Impossible to Miss

Conditional formatting turns the Alert Status column — and the entire row — red without any code. This is the fastest win in the setup.

1. Select all your data rows (e.g., A2:F50, or however far your SKU list runs).

2. Open Format > Conditional Formatting in the Google Sheets menu.

3. Set the format rules as follows. Click "Add another rule" to create each one separately:

Rule 1 — Red (critical): Custom formula: =$F2="⚠️ REORDER NOW" Fill color: red. Text color: white.

Rule 2 — Yellow (warning): Custom formula: =$F2="Order Soon" Fill color: yellow. Text color: black.

4. Verify by temporarily entering a Current Stock value lower than the Reorder Point for one SKU. The row should turn red immediately. If it doesn't, check that your formula range in the conditional formatting rule includes the full row, not just column F. Here is Google's reference on conditional formatting{:target="_blank"} if you need to troubleshoot the rule syntax.

Analytical note: Visual alerts alone work well for businesses that open their spreadsheet daily. If you check it less frequently — or if you have staff who should be notified but don't have spreadsheet access — skip ahead to the email alert section.

Get Email Alerts Automatically with AI-Written Code

Google Apps Script can send you an email whenever a SKU drops to or below its reorder point. You don't write this code yourself — you ask your AI assistant to write it.

1. Open your Google Sheet, then go to Extensions > Apps Script. A code editor opens in a new tab.

2. Delete any placeholder code in the editor window.

3. Go to ChatGPT or Claude and paste this prompt:

Write a Google Apps Script function for a Google Sheet named "Inventory Reorder Tracker." The sheet has columns in this order: A = SKU Name, B = Current Stock, E = Reorder Point, F = Alert Status. The function should: (1) loop through all rows with data starting at row 2, (2) check if the value in column B is less than or equal to the value in column E, (3) if true, collect those SKU names and stock levels, and (4) send a single summary email to [your email address] with a list of all items needing reorder. Include a trigger setup so this runs every morning at 8am.

4. Paste the returned code into the Apps Script editor. Replace [your email address] in the code with your actual address.

5. Click Save (the floppy disk icon), then click Run to test it manually. Google will ask for permission to send email on your behalf — grant it. Check your inbox. You should receive a summary email listing any SKUs currently at or below their reorder point.

6. Set the daily trigger: In Apps Script, click the clock icon (Triggers), then "+ Add Trigger." Set the function to run on a time-driven, day-timer basis at whatever morning hour you prefer. Full documentation on triggers is available at Google's Apps Script developer guide{:target="_blank"}.

Here's the catch: Apps Script email alerts only work if your Current Stock column is up to date. An automated email based on stale data is worse than no alert, because it creates false confidence. The next section addresses this directly.

Keeping the System Working: A 5-Minute Daily Update Routine

The biggest failure mode for DIY inventory tracking is stale data. The formula and alert infrastructure mean nothing if Current Stock numbers aren't current.

The simplest routine that actually holds: update stock counts at the same time you process orders or restock shelves — not as a separate task. If you use Shopify, export a fresh inventory CSV weekly (Products > Export) and paste the Current Stock column over your existing data. If you manage stock manually, update column B as units leave or arrive. The 5-minute daily discipline is: open the sheet, scan the red rows, and update any SKUs you touched that day.

For businesses with fewer than 50 SKUs, this manual-update approach with AI-assisted formula setup is typically more practical than inventory management software running $30–$200/month. The trade-off is the discipline cost — software like Shopify's inventory tools{:target="_blank"} updates automatically if your sales flow through it, while this spreadsheet system requires a human to feed it. If you consistently update it, the spreadsheet wins on cost. If you won't update it, pay for the integration.

For Excel users: Microsoft 365 Copilot (as of early 2026) can analyze inventory data and flag items approaching reorder thresholds using natural language queries — a viable path if your business already runs on Excel rather than Google Sheets. The logic and column structure are identical; only the alert mechanism differs.

When Something Goes Wrong

The Alert Status column shows "OK" for items you know are low. Root cause: The Reorder Point values in column E are too low, usually because the AI calculated daily sales from totals that were actually weekly or monthly figures. Fix: go back to the AI prompt, explicitly label your input data (e.g., "these are weekly sales totals, not daily"), re-run, and paste the corrected reorder point values.

The Apps Script runs but you receive no email. Root cause: Either no SKUs are currently at or below their reorder point (check a row manually), or the trigger didn't save correctly. Fix: run the function manually from the Apps Script editor and check the execution log (View > Logs) for error messages. The most common error is a permissions issue — revoke and re-grant email permissions under your Google account settings.

Conditional formatting highlights the wrong rows. Root cause: The custom formula range in the formatting rule doesn't use an absolute column reference. $F2 locks the column; without the $, the formula drifts when applied across multiple columns. Fix: edit the conditional formatting rule and confirm the custom formula reads =$F2="⚠️ REORDER NOW" with the dollar sign before F.

What to Do Next

Once the basic system is running, the natural extension is adding a "Supplier" column and a "Last Ordered" date column so the reorder email tells you not just what to order but who to contact. Ask your AI assistant to update the Apps Script to include supplier name in the email output — it's a one-sentence change to the prompt.

If you're also wrestling with how AI can help streamline other repetitive operations tasks, how to use AI to automate your weekly business reporting is worth reading next.

FAQ

How do I track inventory reorder points when my sales are seasonal and vary a lot week to week? Use the highest-demand period as your baseline for safety stock, not the annual average. Paste your weekly sales data into ChatGPT or Claude and ask it to identify your peak 8-week average and your off-season average, then build two reorder point values per SKU. Switch between them seasonally by updating column E before peak season starts.

Does this small business inventory tracking system work if I sell through multiple channels — Shopify plus in-person sales, for example? The system works, but you have to consolidate stock deductions manually. The spreadsheet tracks one Current Stock number per SKU. You need to subtract sales from both channels in that one column. The failure point is forgetting to log in-person sales. A simple fix: keep a running tally of in-person sales in a notes column and do one combined deduction at end of day.

What does a spreadsheet reorder alert system cost compared to inventory management software? The spreadsheet system costs nothing if you already have a Google account. Dedicated inventory management software runs roughly $30–$200/month depending on the platform and SKU count — call it $360–$2,400/year. For a business with fewer than 50 SKUs, the honest answer is that the spreadsheet system delivers the core functionality (reorder alerts, safety stock buffers, email notifications) at zero marginal cost. The software earns its price when you need automatic stock deduction across sales channels, purchase order generation, or multi-location tracking — features this spreadsheet doesn't replicate.

How often should I update the Average Daily Sales figures in my reorder point spreadsheet? Quarterly is a reasonable minimum; monthly is better if your sales volumes shift. Outdated average daily sales figures cause the reorder point to drift — too low means you run out before the alert fires; too high means you're holding excess inventory and tying up cash. Set a calendar reminder to paste fresh sales data into your AI assistant and recalculate column E every 90 days. Pricing checked mid-2026 — AI tool free tiers and Google Workspace pricing have been stable, but verify at the links above before making budget decisions.