Inventory spreadsheet: how to track stock, cost and reorders without losing count
By the BizDocs Studio team · Updated
- •Do not type over quantities. Log every stock move (receive, sale, return, adjust, transfer) on one tab and let on hand calculate from the log.
- •Reorder point = average daily sales x (lead time + safety days). Bully sticks selling 1.58 a day with a 7-day lead time and 7 safety days should reorder at 23, not 10.
- •Moving weighted average cost: 10 bags at $40.00 plus 20 bags at $43.00 = $1,260 / 30 = $42.00 a bag. Sales do not change the average.
- •Count blind (no expected quantity on the sheet), one location at a time, and recount any line whose dollar variance is over your limit before you adjust it.
Most free inventory templates are a list of items with a quantity column. That works until someone types a new count over the old one, a delivery is added twice, and nobody can say why the shelf has 4 bags when the sheet says 11.
A spreadsheet that stays right is built differently: quantities are the sum of every stock move, cost comes from what you paid, and a count checks the log instead of replacing it. Most examples below come from the sample pet supply shop in our template (80 SKUs, three stock locations); the cost example uses round numbers.
What an inventory spreadsheet needs
Six pieces cover a shop, an online store or a parts room. You enter each fact once.
| Tab | What you enter | What it gives you |
|---|---|---|
| Items | SKU, supplier, location, bin, reorder point and qty, case pack, lead time, cost, price | Status per item: OK, On order, Reorder, Out of stock |
| Movements | Date, SKU, type, qty, location, unit cost on receipts | On hand by item and location, average cost, cost of goods sold |
| Reorder | Nothing | Items at or below reorder point, grouped by supplier, with a suggested qty |
| Purchase order | Pick a supplier | A one-page PO from the reorder list |
| Count sheet | Pick a location, then the counted qty | Variance in units and dollars, and the adjustment to log |
| Stock health | Nothing | Days of stock, last sale, dead and slow stock, margin by item |
Step 1: log stock moves, never overwrite quantities
Every change in stock is one row with a date, a SKU, a type and a quantity. On hand for an item is receipts plus returns plus adjustments minus sales. Stock at a location adds transfers in and takes transfers out. If a number looks wrong, you can read the rows and find the move that caused it.
Sales do not need to be logged one by one. Most small shops paste a total per item from the POS sales report, daily, weekly or monthly. The more often you log, the more exact your days of stock and last sale dates are.
| Type | Qty | Example |
|---|---|---|
| Receive | Positive, with unit cost paid | 20 bags of dog food at $43.00 |
| Sale | Positive | 14 cans of wet cat food sold this week |
| Return | Positive | An unopened bag brought back |
| Adjust | Minus for a loss, plus for a find | -1 torn bag, -2 expired treats, +1 found in back stock |
| Transfer | Positive, from one location to another | 5 bags from the back room to the sales floor |
Step 2: cost each item at moving weighted average
Supplier prices move. If you keep only the latest price, a 5% increase on 9/15 makes every bag already on the shelf look 5% more expensive, and your margins look worse than they were. A moving weighted average re-averages the cost each time stock arrives. Sales, returns and adjustments are valued at the current average and do not change it.
Use landed cost on receipts: the invoice price plus freight and fees for that shipment, divided by the units. Leaving freight out flatters your margins.
On hand: 10 bags at an average of $40.00 = $400.00
Receive 20 bags at $43.00 = $860.00
New average = ($400.00 + $860.00) / (10 + 20) = $42.00 a bag
Sell 5 bags at $67.99: sales $339.95, cost of goods sold 5 x $42.00 = $210.00, gross profit $129.95 (38.2%)
On hand now: 25 bags x $42.00 = $1,050.00. The average stays $42.00 until the next receipt.
If stock has hit zero (or gone below) when a receipt arrives, the average restarts at that receipt's cost. Note that weighted average is a practical stock tool; ask your accountant which inventory method you report for taxes.
Stock on hand by location, reorder alerts by supplier, a printable purchase order and a count sheet for small product businesses.
Step 3: set reorder points that match how fast things sell
One reorder point for everything is the classic spreadsheet mistake. A can of cat food that sells 9 a day and a puzzle feeder that sells about 1 a week need very different triggers.
Reorder point = average daily sales x (lead time in days + safety days). Use the last 90 days of sales so the number follows the season. When on hand plus on order is at or below the reorder point, order.
Sold 142 in the last 90 days = 1.58 a day
Lead time 7 days, safety stock 7 days: suggested reorder point = 1.58 x 14 = 22.1, round up to 23
The shop's own reorder point is 10: with 8 on hand (5 days of stock) it is on the reorder list, and 23 shows the setting is too low
Order qty at the current settings: reorder point 10 + reorder qty 36 - on hand 8 - on order 0 = 38, rounded up to the case pack of 12 = 48
Once the PO is sent, record the qty as on order so the item does not show up on the next reorder list and get ordered twice.
Step 4: count blind, one location at a time
A count sheet that prints the expected quantity invites the counter to find that number. Print the bin, SKU, item and unit with a blank box, and compare after. Count one location at a time and keep stock from moving until the counts are typed in.
Then reconcile each line: variance in units, variance in dollars at average cost, and a decision. Small differences get an Adjust row. Anything over your dollar limit gets recounted first, ideally by someone who did not receive the stock.
| Item | System qty | Counted | Variance | Avg cost | Variance $ | Result (limit $25) |
|---|---|---|---|---|---|---|
| Probiotic powder, 30 servings | 24 | 22 | -2 | $12.795 | ($25.59) | Recount |
| Freeze-dried beef patties | 14 | 13 | -1 | $19.72 | ($19.72) | Adjust -1 |
| Chicken pate wet cat food | 378 | 378 | 0 | $0.96 | - | Match |
A practical rhythm: your A items (most of your cost of goods) every month, the rest every quarter, and easy-to-pocket items more often.
Step 5: find the cash sitting on the shelf
Days of stock = on hand / average daily sales. Twenty-five bottles of flea shampoo that sell one every 8 days is 200 days of stock: cash you could have spent on something that sells. Dead stock is anything with no sale in the window that was already in stock before it started.
Sell-through = units sold in the window / (units on hand at the start + units received). Low sell-through on a seasonal line tells you to cut the next order before you place it.
Mistakes to avoid
- Typing a new count over the quantity column. Log an Adjust row for the difference so the history stays readable.
- Using the latest purchase price as the cost of everything on the shelf. Average it, and include freight.
- One reorder point for every item, or reorder points set once and never revisited. Recheck them against recent sales each quarter.
- Posting a large variance without a recount. A miscounted case of 24 cans is a lot of fake shrink.
- Splitting locations into separate files, which hides the total on hand.
Frequently asked questions
What is the best way to track inventory in Excel or Google Sheets?
Keep an Items tab with one row per SKU and a Movements tab with one row per stock move. Calculate on hand with SUMIFS over the Movements tab instead of typing quantities, and build the reorder list, count sheet and reports from those two tabs.
How do I calculate a reorder point?
Average daily sales x (lead time in days + safety days). With 1.2 sales a day, a 7-day lead time and 7 safety days, reorder at 17. Base daily sales on a recent window, such as the last 90 days.
What is a blind inventory count?
A count where the counter does not see the expected quantity. The sheet shows only the bin, SKU, item and a blank box. The system quantity is compared afterwards, so it cannot steer the count.
How often should a small business count inventory?
A full count at least once a year, plus cycle counts: your top items every month and the rest every quarter.
When does a spreadsheet stop being enough?
When you need live sync with several sales channels, barcode scanning or lot and expiry tracking, or you pass a few hundred SKUs. Below that, a well-built spreadsheet does the job.
Stock on hand by location, reorder alerts by supplier, a printable purchase order and a count sheet for small product businesses.