Build an Inventory Tracker Spreadsheet with Reorder Points and Low-Stock Alerts

You get exact columns, copy-ready reorder-point formulas, a status column and conditional formatting that highlights items to reorder now or this week.

Prompt

Prompt
Act as an operations analyst who builds inventory spreadsheets for small businesses.

Before building anything, ask me these questions in one message and wait for my answers:
1. Do you use Excel or Google Sheets, and about how many products (SKUs) do you track?
2. What is your typical supplier lead time in days, and does it vary by supplier?
3. How do you know your sales pace: weekly history, monthly totals or a rough guess?
4. How much safety stock do you want, in days of sales?

Then build the tracker.

Rules:
- Calculate the reorder point as average daily sales × (lead time days + safety stock days). Show the exact cell formula for row 2.
- Mark every column as either typed in by me or calculated by a formula.
- Include a Days of Stock Left column and a Status column that returns REORDER NOW, REORDER THIS WEEK or OK.
- Give the conditional formatting steps with the exact custom formula and fill colour for each status.
- No macros. Formulas must still work when I add rows.

Output format:
1. Column table (Column | Header | Typed or formula | Row 2 formula | Purpose)
2. Conditional formatting steps
3. Three sample rows with numbers filled in
4. Weekly update routine (3-5 steps)

End with one line naming your assumptions and what would make the reorder points inaccurate.

Example output

Example for: a small candle shop using Excel, 40 SKUs, lead times of 10-21 days, sales from the last 4 weeks, 5 days of safety stock.

1. Columns

ColHeaderTyped or formulaRow 2 formulaPurpose
ASKUTyped—Unique product code
BItemTyped—Product name
CSupplierTyped—Who you order from
DOn HandTyped—Current count
ESold Last 4 WeeksTyped—Units sold in last 28 days
FAvg Daily SalesFormula=E2/28Sales pace
GLead Time DaysTyped—Order to delivery
HSafety DaysTyped—Buffer, e.g. 5
IReorder PointFormula=ROUNDUP(F2*(G2+H2),0)Order when stock hits this
JDays of Stock LeftFormula=IF(F2=0,999,D2/F2)Runway at current pace
KStatusFormula=IF(D2<=I2,"REORDER NOW",IF(J2-G2-H2<=7,"REORDER THIS WEEK","OK"))Alert
LSuggested Order QtyFormula=IF(K2="OK",0,ROUNDUP(F2*(G2+H2+28),0)-D2)Covers lead time, buffer and 4 more weeks

Format A1:L1 as a Table (Ctrl+T) so formulas fill down automatically when you add rows.

2. Conditional Formatting

  1. Select A2:L500.
  2. Home > Conditional Formatting > New Rule > Use a formula.
  3. Enter =$K2="REORDER NOW" and set a light red fill.
  4. Add a second rule: =$K2="REORDER THIS WEEK" with a light amber fill.
  5. Leave OK rows unformatted.

3. Sample Rows

ItemOn HandSold 4 WksDailyLeadSafetyReorder PtDays LeftStatusOrder Qty
Lavender Candle 8oz46843.01455715.3REORDER NOW95
Cedar Wax Melts1201124.021510430.0REORDER THIS WEEK96
Linen Room Spray90421.51052360.0OK0

4. Weekly Routine

  1. Every Monday, update On Hand from a shelf count or your POS.
  2. Replace Sold Last 4 Weeks with the latest 28-day total.
  3. Filter Status to show red and amber rows.
  4. Place orders using Suggested Order Qty, rounded to supplier pack sizes.
  5. Update Lead Time Days if a supplier delivered late.

Assumptions: sales are steady across the 4 weeks and lead times are reliable. Seasonal peaks, promotions or late suppliers will make reorder points too low.

Written by AI from this prompt. Your results will vary.

Tips and common edits

Why this works

  • Asking about lead times and sales pace first means reorder points match your real suppliers, not generic defaults.
  • A three-level status column separates urgent items from ones to order this week, so you can plan purchases.
  • Exact row 2 formulas and formatting rules can be pasted straight in with no guesswork.

Use this in ChatGPT, Claude or Gemini when you are moving stock tracking out of your head or a notebook. Have your product count, supplier lead times and a rough idea of recent sales ready.

Common edits

  • Ask for Google Sheets versions using ARRAYFORMULA so one formula fills the whole column.
  • Add a cost column and ask for the total value of this week's suggested orders.

Prompt details

By
Prompts-GPT editors
Published

Did this prompt work?

Keep exploring

More in Excel & Spreadsheets