Inventory Management Excel Template: Track Stock, Reorder Points and Turnover
Build an inventory management template in Excel for a retail or online store: product list, stock movements, stock on hand with SUMIFS, reorder points and quantities, ABC analysis, inventory turnover and cycle counts.
Short answer: an inventory management template in Excel works best with three sheets: Products (one row per SKU with supplier, lead time and safety stock), Movements (every receipt, sale, return and adjustment as a row) and Stock, where SUMIFS calculates stock on hand for each SKU, flags items at or below their reorder point and suggests how many to order. Add an ABC analysis and a turnover figure, and the same file tells you what to buy, what to count and what is tying up cash.
This guide is for retailers and online sellers who buy finished products and resell them. If you make your own products from raw materials, see our craft inventory spreadsheet, which adds recipes and production batches. All figures below are illustrative.
The three sheets
| Sheet | One row per | Columns |
|---|---|---|
| Products | SKU | SKU, name, category, supplier, unit cost, lead time (days), safety stock, location |
| Movements | stock change | Date, SKU, type (Receipt, Sale, Return, Adjustment), quantity, unit cost, reference (PO, order number), note |
| Stock | SKU | Received, sold, returned, adjusted, on hand, value, daily sales, reorder point, status, suggested order |
Recording movements instead of overwriting a stock number gives you a full history: you can see why a count is off, calculate sales velocity and value inventory from actual purchase costs. Turn each range into an Excel table (Ctrl+T) and use data validation for SKU and Type so entries are always consistent.
Step 1: Record movements
In Movements (A Date, B SKU, C Type, D Quantity, E Unit cost, F Reference, G Note), follow a few rules:
- Quantities are always positive; the Type decides whether stock goes up or down.
- Adjustments carry a sign: negative for damage, loss or shrinkage, positive when a count finds more than expected — always with a note.
- Customer returns that go back on the shelf are a Return; damaged returns are a Return plus a negative Adjustment.
- Include freight-in in the unit cost of receipts, so inventory value reflects what stock really cost you.
If you sell online, export orders weekly and paste them as Sale rows instead of typing each one.
Step 2: Stock on hand
In Stock, with the SKU in column A:
- Received (B):
=SUMIFS(Movements!D:D,Movements!B:B,A2,Movements!C:C,"Receipt") - Sold (C): same with “Sale”; Returned (D): “Return”; Adjusted (E): “Adjustment”
- On hand (F):
=B2-C2+D2+E2 - Average cost (G):
=IFERROR(SUMPRODUCT((Movements!B2:B5000=A2)*(Movements!C2:C5000="Receipt")*Movements!D2:D5000*Movements!E2:E5000)/B2,0) - Inventory value (H):
=F2*G2
The total of column H is the cash sitting on your shelves — a number worth watching every month.
Step 3: Reorder point and order quantity
The reorder point is the stock level at which you must order so that new stock arrives before you run out:
Reorder point = average daily sales × lead time in days + safety stock
- Daily sales (I), last 60 days:
=SUMIFS(Movements!D:D,Movements!B:B,A2,Movements!C:C,"Sale",Movements!A:A,">="&TODAY()-60)/60 - Reorder point (J):
=ROUNDUP(I2*XLOOKUP(A2,Products!A:A,Products!F:F)+XLOOKUP(A2,Products!A:A,Products!G:G),0) - Status (K):
=IF(F2<=J2,"Reorder","OK") - Suggested order (L), to cover a set number of days after arrival (in cell $N$1):
=IF(K2="Reorder",ROUNDUP(I2*(lead time+$N$1)+safety stock-F2,0),0)
Example: a SKU sells 15 units a day, the supplier needs 10 days and you keep 60 units of safety stock. The reorder point is 15 × 10 + 60 = 210. With 190 on hand, the sheet says Reorder. To cover 30 days after arrival, order 15 × (10 + 30) + 60 − 190 = 470 units. About 150 sell while you wait, so the delivery arrives with 40 on the shelf and brings stock to 510 — 30 days of sales plus safety stock.
Adjust the suggestion for supplier minimums, case packs and price breaks. For seasonal products, use the same period last year instead of the last 60 days.
Step 4: ABC analysis
A small share of SKUs usually drives most of the value. ABC analysis ranks SKUs so you can manage them differently:
- Calculate each SKU’s annual sales value (units sold × price) or consumption value (units × cost).
- Sort from highest to lowest and calculate the cumulative share:
=SUM($M$2:M2)/SUM($M$2:$M$500). - Classify: a common convention is A up to about 80% of cumulative value, B up to about 95%, C the rest.
A items deserve tighter reorder points, more frequent counts and closer supplier relationships. C items can be ordered in larger, less frequent batches — or reviewed for discontinuation.
Step 5: Turnover and days of inventory
- Inventory turnover = cost of goods sold for the year ÷ average inventory value
- Days of inventory = 365 ÷ turnover
With $180,000 of cost of goods sold and an average inventory of $36,000, turnover is 5 and stock sits for about 73 days on average. Calculate it by category: slow categories tie up cash and storage space, and are candidates for promotions or smaller orders. Cost of goods sold also feeds your profit and loss statement.
Step 6: Cycle counts
Instead of one stressful annual count, count a few SKUs every week, rotating through the list so A items are counted monthly and B and C items quarterly. When a count differs from the sheet, record an Adjustment with the reason (damage, theft, receiving error, unrecorded sale). Patterns in the reasons tell you which process to fix.
For tax purposes, how you value inventory and when you must take a physical count depend on your business and accounting method; confirm with your tax professional, and keep year-end count sheets with your records.
Common mistakes
- Overwriting stock numbers instead of recording movements.
- One SKU for several variants (sizes, colors), which hides stockouts.
- Ignoring lead time variability: if a supplier is sometimes two weeks late, your safety stock must cover it.
- Leaving freight out of cost, which overstates margins.
- Never reviewing slow movers, which slowly fill the warehouse.
Frequently asked questions
Can I use this template in Google Sheets?
Yes. SUMIFS, XLOOKUP and SUMPRODUCT work in Google Sheets, and several people can record movements from a phone or tablet in the stockroom.
How do I handle products with sizes and colors?
Give each variant its own SKU and add a Product column for grouping. A pivot table then shows totals per product while reorders happen per variant.
Can I scan barcodes into Excel?
Yes. A standard USB or Bluetooth barcode scanner types the code like a keyboard, so you can use barcodes as SKUs or look them up with XLOOKUP.
When should I move to inventory software?
When stock must sync automatically across several sales channels, when several people pick orders at once, or when you need lots, serial numbers or multiple warehouses. The structure above transfers directly to most systems.
More guides for sellers are collected in Online Selling & Inventory.
