Inventory tracking in a spreadsheet works when it answers three questions at any moment: what do I have, what is it worth at cost, and what did the goods I sold actually cost me. One row per item, disciplined ins and outs, and a monthly count deliver all three.
Hi, I'm Max — I run Vickery Finance, and this page is one room in the workshop. Everything here walks the same path: from shoebox-and-guesswork to a clean close you can defend, one honest number at a time. Work through it with your own figures in hand — that's when a guide turns into a result. And when you're ready to do the close instead of just reading about it, start with the Free Month-End Close Checklist: it's page one of the 18-step close my templates run, printable and yours to keep. Know your numbers.
An inventory spreadsheet does an operational job and an accounting job at the same time. Operationally, it tells you what is on the shelf so you can reorder before you run out and stop reordering what is not moving. For accounting, it produces cost of goods sold (COGS) — the cost of the items you actually sold in a period — which is the number your gross profit, your pricing, and your tax records all depend on.
Most home-grown trackers fail at the same place: they record purchases but not the count. Purchases tell you what you spent; only a count tells you what you still have, and COGS lives in the difference between the two. The system below is built around that fact — every number in the worked example ties to the formula opening inventory + purchases − closing inventory = COGS.
One tab for the item list, one log for movements. Build it once; upkeep is a few minutes per delivery or sale batch.
Sooner or later an item you bought at $4 comes in again at $5, and the sheet needs a rule. The simplest spreadsheet-friendly approach is a weighted average: divide the total cost of all units available by the number of units, and use that average cost until the next purchase changes it. First-in-first-out (FIFO) — assuming the oldest units sell first — is the other common method and can also be tracked in a sheet with a small amount of extra care.
Which method fits your situation is an accounting-policy question with tax implications, so this page teaches the tracking mechanics, not the choice — it is educational information, not tax advice, and your accountant can confirm the method your books should use. What matters for the tracker is consistency: pick a method, apply it the same way every period, and write the rule at the top of the sheet so future-you follows it too. Sellers who also need per-channel profit can pair this system with the e-commerce seller tracker linked below.
One item first: a candle starts the month with 100 units on hand. The business buys 50 more and sells 90, so the count at month-end is 100 + 50 − 90 = 60 units. The whole shop's month, valued at cost, then runs through the COGS formula — opening $2,000 plus purchases of $3,500 minus the counted closing inventory of $1,500:
| Line | Math | Amount |
|---|---|---|
| Opening inventory (at cost) | Counted at month start | $2,000 |
| Add: purchases (at cost) | All invoices for the month | + $3,500 |
| Less: closing inventory (at cost) | Counted at month end | − $1,500 |
| Cost of goods sold | $2,000 + $3,500 − $1,500 | $4,000 |
| Revenue from goods sold | Sales for the month | $7,000 |
| Gross profit | $7,000 − $4,000 | $3,000 |
The sheet is only as good as the routine behind it. Five habits do the heavy lifting:
The E-Commerce Inventory and COGS Tracker is built around exactly this system — items, ins and outs, counts, and the COGS calculation done for you — in Excel and Google Sheets. Browse the full Template Directory for the matching seller tools, including the E-Commerce Seller Profit Tracker for Amazon, Etsy, and Shopify, which pairs per-channel sales with the inventory costs from this page.
Cost of goods sold = opening inventory + purchases − closing inventory, all valued at cost. In the worked example: $2,000 + $3,500 − $1,500 = $4,000. The formula works because everything that was available and is no longer on the shelf must have been sold (or otherwise left stock, which is why damage and shrinkage are logged separately).
A full count monthly is the standard rhythm for a small seller, aligned with the month-end close so the COGS figure uses a fresh closing number. Very small inventories can count quarterly, but any item that sells fast or goes missing often deserves a weekly spot count.
When multiple people sell from the same stock at the same time, when you sell across several channels that must share one live count, or when item counts run into the many hundreds with daily movement. Until then, a disciplined spreadsheet — ins, outs, and a monthly count — produces the same COGS number dedicated software does.
1 — Your first win (10 minutes): the Free Month-End Close Checklist — page one of the 18-step close, printable, free.
2 — Do it with the workbook: find your trade's template — every workbook runs this same clean-close ritual, with example data and a guided first step inside.
3 — Keep the rhythm: one practical numbers email a week — Know Your Numbers, free to subscribe.
The way back: start any visit at the Start Here hub, or email a human — [email protected]. I answer.
Know your numbers. — Max
Some links below are affiliate links. If you buy through them, Vickery Finance may earn a small commission at no extra cost to you.
DYMO LabelWriter 450 Turbo — for labeling the shelves the spreadsheet counts — the 450-series printer standard label rolls fit. Avery Easy Peel Address Labels 5160, 3,000 Labels — for bin and shelf labels from any standard printer, 3,000 to a box.
DYMO LabelWriter 5XL Label Printer — DYMO's wide-format 5XL for 4x6 shipping labels (US model 2112554).
More books and gear live on the Resources page.