Using AI to create a simple inventory reorder system that tells you what to order before you run out

43% of small businesses track inventory manually or not at all, according to Wasp Barcode Technologies' 2023 State of Small Business report — and stockouts cost the average retailer roughly 4% of annual revenue every year. This post walks you through building a working AI inventory reorder system for small business use, using Google Sheets and ChatGPT, with automated email alerts that fire before you run out of stock. Total software cost: $0 beyond a Google account, with an optional $20/month ChatGPT Plus subscription that speeds up the formula-writing significantly.

What You Need Before You Start

Google Sheets{:target="_blank"} — free spreadsheet platform where your inventory tracker lives. A standard Google account covers everything in this guide at no cost.

ChatGPT{:target="_blank"} — used to generate formulas, write Apps Script code, and calculate your reorder points. The free tier can handle basic formula requests. ChatGPT Plus{:target="_blank"} at $20/month as of early 2026 gives you GPT-4o, which handles multi-step scripting requests with significantly fewer errors — worth it if you're building the full alert system.

Time required: 30–45 minutes for basic setup with visual reorder flags. Add another 30–60 minutes to configure the automated Gmail alert system via Google Apps Script.

Skill level: No coding required. You need to be comfortable copying and pasting formulas and following step-by-step instructions in Google Sheets. No prior Apps Script experience needed — ChatGPT writes the code for you.

Why Most Inventory Systems Fail Before You Run Out of Stock

The core problem is not laziness — it's that most small businesses set a static reorder number once and never update it. A single reorder point that works in October is wrong by February. The fix is a formula-driven system where your reorder threshold recalculates based on your actual usage and your supplier's lead time.

The foundational formula, sourced from Investopedia's reorder point definition{:target="_blank"}, is:

ROP = (Average Daily Usage × Lead Time in Days) + Safety Stock

Safety stock is the buffer that absorbs supplier delays and demand spikes. The formula for it:

Safety Stock = (Maximum Daily Usage − Average Daily Usage) × Maximum Lead Time

These two formulas are all you need per SKU. The rest of this guide is about getting them into a spreadsheet that checks itself automatically.

How to Build Your AI Inventory Reorder System in Google Sheets

1. Open a new Google Sheet and create column headers in Row 1: SKU, Product Name, Current Stock, Avg Daily Usage, Max Daily Usage, Lead Time (Days), Max Lead Time (Days), Safety Stock, Reorder Point, Reorder Needed?

2. Enter your product data in rows 2 onward. If you don't have precise usage numbers, estimate from your last 30–90 days of orders and divide by the number of days in the period — ChatGPT can do this calculation if you paste in your order history.

3. Paste the following formula in the Safety Stock column (Column H, starting at H2):

   =(E2-D2)*G2 This multiplies the difference between max and average daily usage by your maximum lead time. The result is your buffer stock per SKU.

4. Paste the following formula in the Reorder Point column (Column I, starting at I2):

   =(D2*F2)+H2 This calculates the stock level at which you need to place an order. Every cell in this column will now update automatically if you change your usage or lead time figures.

5. Set the Reorder Needed? column (Column J) with a simple IF formula:

   =IF(C2<=I2,"ORDER NOW","OK") Cells showing "ORDER NOW" mean your current stock is at or below the calculated reorder point.

6. Apply conditional formatting to Column J: highlight cells containing "ORDER NOW" in red. In Google Sheets, go to Format → Conditional Formatting, set the rule to "Text contains ORDER NOW," and choose a red fill. This gives you a visual dashboard that updates in real time.

This basic setup takes roughly 30 minutes. The analytical payoff: every time you update Column C with your current stock count, the system tells you immediately what needs ordering. No manual comparison against a static list.

Prompt to use with ChatGPT if you want it to build this structure for you:

"I'm building a Google Sheets inventory reorder system. My sheet has columns for: SKU, Product Name, Current Stock, Average Daily Usage, Maximum Daily Usage, Lead Time (Days), Maximum Lead Time (Days), Safety Stock, Reorder Point, and a Reorder Needed flag. Write me the Google Sheets formulas for Safety Stock (row 2), Reorder Point (row 2), and the Reorder Needed conditional — where the flag shows ORDER NOW if current stock is at or below the reorder point, and OK otherwise. Use column letters A through J in order."

ChatGPT will return exact formulas for your column layout. Verify them by manually checking one row: plug a product's numbers into the ROP formula by hand and confirm the spreadsheet matches.

Using ChatGPT to Write Your Reorder Formulas and Alert Scripts

ChatGPT's real value in a small business inventory reorder system is not replacing the formulas — it's handling the logic that changes per supplier and per season. Paste your last 90 days of sales data directly into the chat and ask it to calculate average daily usage per SKU. It functions as a lightweight data analyst without any analytics subscription.

For businesses with seasonal demand patterns — a landscaping supply company, a bakery, a gift shop — a single annual average daily usage figure is wrong for at least six months of the year. Ask ChatGPT to segment your historical data by month, identify peak usage months, and suggest adjusted reorder points for high-season versus low-season periods. It can output these as a table you paste directly into your spreadsheet.

Prompt for seasonal reorder adjustment:

"I'm going to paste in 12 months of monthly sales data for [Product Name]. For each month, calculate the average daily usage. Then identify the three highest-usage months and calculate a separate reorder point for those months versus the remaining nine months. My lead time is [X] days, max lead time is [Y] days. Show me the two reorder points and the safety stock for each scenario."

The honest answer is that static reorder points are the single most common reason these systems get abandoned — they become inaccurate and owners stop trusting them. Seasonal adjustment doubles your setup time but roughly halves your margin for error.

Setting Up Automatic Low-Stock Email Alerts for Free with Google Apps Script

This is where the system moves from passive to active. Google Apps Script{:target="_blank"} lets you attach a script to your spreadsheet that runs every morning, scans Column J, and emails you a list of SKUs flagged as "ORDER NOW." Cost: $0.

1. Open your Google Sheet and click Extensions → Apps Script.

2. Delete the default placeholder code in the editor.

3. Paste the following prompt into ChatGPT to generate the alert script:

   "Write a Google Apps Script function for a Google Sheet with the following structure: Column A = SKU, Column B = Product Name, Column C = Current Stock, Column J = Reorder Needed flag (values are either 'ORDER NOW' or 'OK'). The script should scan all rows with data starting from Row 2, collect any row where Column J says 'ORDER NOW', and send me a Gmail alert listing the SKU and Product Name for each flagged item. The email subject should be 'Inventory Reorder Alert — [today's date]'. If no items need reordering, the script should not send an email."

4. Copy the script output from ChatGPT and paste it into the Apps Script editor. Replace the placeholder email address in the script with your own.

5. Save the script, then click the clock icon (Triggers) in the left sidebar. Add a new trigger: choose your function name, set event source to "Time-driven," type to "Day timer," and set it to run between 7–8am daily.

6. Authorize the script when prompted — Google will ask for permission to send email on your behalf. This is a one-time step.

From this point, the spreadsheet checks itself every morning. You update Column C with current stock counts (daily, weekly, or however often you do a stock count), and the script handles the rest.

Here's the catch: the alert is only as accurate as Column C. If you're not updating current stock counts regularly, the system sends alerts based on stale data. Build a habit of updating stock counts on a fixed schedule — weekly is enough for most businesses with slow-moving SKUs, daily for high-velocity items.

When Something Goes Wrong

Symptom: The script runs but sends no email, even though items show "ORDER NOW" in the sheet. Cause: The script is reading column positions numerically, and the index in the generated code may not match your actual layout if your sheet differs from what you described in the prompt. Apps Script references columns by number (Column A = 1, Column J = 10), so any mismatch between your prompt description and your actual sheet will cause the script to read the wrong column. Fix: In the Apps Script editor, find the line referencing Column J and confirm the index number matches your actual column position. If Column J is the 10th column, the script should reference index 10. Adjust the number and re-run.

Symptom: Safety Stock or Reorder Point formulas return errors or zeros for some rows. Cause: Missing values in the usage or lead time columns. The formulas multiply across cells — a blank cell returns 0, which produces a falsely low reorder point. Fix: Fill in every cell in Columns D, E, F, and G before running the system. Use best estimates for products with incomplete history — even a rough number is better than a zero that masks a real reorder need.

Symptom: The reorder point looks obviously wrong for a product (e.g., flags a reorder for an item you have 500 units of). Cause: Average daily usage or lead time entered in the wrong unit — for example, weekly usage entered as daily usage. Fix: Recheck Column D for the affected SKU. Average daily usage should be units per day. If your records show monthly sales, divide by 30 before entering the figure. ChatGPT can help you convert historical sales records to daily averages if you paste in the raw data.

How to Maintain and Improve Your System Over Time

The most common reason these spreadsheets get abandoned: owners stop updating stock counts because it feels like busywork, the alerts become inaccurate, and trust in the system erodes. The numbers say otherwise — a system you update once a week for 10 minutes prevents a stockout that costs you 4% of revenue. That trade-off is straightforward on paper; the discipline is the hard part.

Practical maintenance steps:

• Review your lead times quarterly. Suppliers change. A vendor who delivered in 5 days last year may now take 10. Update Column F when you notice delays.
• Recalculate average daily usage every 90 days. Paste your recent sales data into ChatGPT and ask it to recalculate per-SKU averages. This takes about 15 minutes and keeps your reorder points accurate.
• Add new SKUs as you add products. The system only covers what's in it. A new product with no reorder point is a blind spot.

When the Spreadsheet Approach Is Enough — and When to Graduate to Paid Software

For businesses with fewer than 50 SKUs, this spreadsheet system covers the core reorder problem at zero ongoing cost. The effort-to-value ratio is strong.

For businesses with 50–500 SKUs, manual stock count updates become the bottleneck. At that scale, you'll want a lightweight inventory app that syncs stock counts automatically from your point-of-sale or e-commerce platform. Shopify{:target="_blank"} handles this natively for e-commerce. For brick-and-mortar retailers or product businesses not on Shopify, tools like Inventory Planner{:target="_blank"} (starting around $99/month as of early 2026 — check their pricing page, these change) add automated reorder suggestions. The honest answer is that you should build the spreadsheet system first: it forces you to understand your own reorder logic before you pay someone else to automate it.

For Microsoft 365 users, Microsoft Copilot{:target="_blank"} (starting at $30/user/month as of early 2026) can perform equivalent spreadsheet automation tasks inside Excel. The approach is the same; the scripting language is VBA or Office Scripts instead of Apps Script. If your team already runs on Microsoft 365, it may be worth asking Copilot to build the equivalent system in Excel rather than switching platforms. For teams working entirely in Google Workspace, Google Gemini Advanced (~$20/month as of early 2026) is a capable alternative to ChatGPT for generating Sheets formulas and Apps Script code — particularly convenient if you prefer to stay within the Google ecosystem.

For related approaches on automating routine business tasks with AI, see our guide on AI task automation for small businesses without writing code.

FAQ

Can I use ChatGPT's free tier for this, or do I need Plus? The free tier can generate basic Google Sheets formulas and simple IF logic. For the Apps Script email alert, GPT-4o (ChatGPT Plus, $20/month as of early 2026) handles multi-step scripting requests with fewer errors and better error handling in the output. If you're hitting a wall with the free tier on the Apps Script section, the $20 is the right call — you'll spend less time debugging.

How do I calculate average daily usage if I don't have detailed sales records? Start with your last purchase order for each SKU and how long that stock lasted. Divide units purchased by days of supply. It's imprecise, but it gives you a starting number. Paste whatever records you do have into ChatGPT and ask it to estimate daily usage — it will flag where the data is thin and tell you what additional information would improve the estimate.

What's the ROI on setting this up, realistically? Stockouts cost the average retailer 4% of annual revenue. For a business doing $500,000 in annual revenue, that's $20,000 in lost sales annually. Even recovering 25% of that figure — $5,000 — against a setup cost of 2–3 hours and $0 in software puts the ROI in the range of thousands of dollars per hour of effort. The caveat: the system only prevents stockouts it can see. Infrequent stock count updates reduce the benefit proportionally.

Does this work for service businesses with physical supplies — not just retailers? Yes. A plumbing company tracking pipe fittings and fixtures, a salon tracking product inventory, a small manufacturer tracking raw materials — the reorder point formula is the same regardless of industry. The difference is that your "average daily usage" may translate to average weekly or per-job usage, which you adjust for in the lead time column. The math is identical.

What if I need to track inventory across multiple locations? The single-sheet approach in this guide covers one location. For multiple locations, you can duplicate the sheet per location and build a summary tab that aggregates flags across sheets using IMPORTRANGE formulas — ChatGPT can write those too. At three or more locations with meaningful stock volume, the manual update burden typically justifies the move to a paid inventory platform.