This weekend — through Sunday at 11:59 PM: BUNDLE-NOV21 takes 10% off the Practice in a Box + Everything Vault bundle. VAULT-NOV21 takes 15% off the Everything Vault. WEEKEND15 takes 15% off everything else, and the new Close the Month + Close the Year bundle is $149.
Browse templates →

How to Track Inventory in a Spreadsheet

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.

$4,000cost of goods sold in the worked example
60 unitsclosing count for the example item
Monthlya physical count keeps the sheet honest

Welcome to the numbers workshop

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.

The two jobs an inventory sheet has to do

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.

Set up inventory tracking in six steps

One tab for the item list, one log for movements. Build it once; upkeep is a few minutes per delivery or sale batch.

  1. Give every item one row and one identifier. Item name or SKU, unit cost, and opening quantity. One row per distinct item — sizes or variants that sell separately get separate rows, because they run out separately.
  2. Record the opening count honestly. Count what is physically there at your start date and value it at what it cost you, not what it will sell for. This opening figure anchors every COGS calculation that follows.
  3. Log every purchase in, at its real unit cost. Date, item, quantity, and the unit cost on that invoice. If the same item arrives at a different cost later, note the new cost — the sections below explain a simple way to handle it without special software.
  4. Log every unit out: sales, damage, and shrinkage. Sales reduce stock, but so do damaged, expired, and missing items. Record all three, in separate columns if you can — the damage and shrinkage columns are where quiet losses become visible.
  5. Count physically every month and reconcile. Count the shelf, compare it to the sheet, and investigate differences while the month is still memorable. The count — not the sheet — is the closing inventory figure your COGS calculation uses.
  6. Calculate COGS for the period. Opening inventory value + purchases at cost − closing inventory value = COGS. Put that figure next to the period's sales revenue and you have gross profit, the number that tells you whether the products are priced to cover their costs.

When the same item costs two different prices

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.

A worked month: from counts to COGS

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:

LineMathAmount
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 soldSales for the month$7,000
Gross profit$7,000 − $4,000$3,000

Spreadsheet inventory habits that prevent surprises

The sheet is only as good as the routine behind it. Five habits do the heavy lifting:

  • Enter purchases the day the delivery arrives, from the invoice — not at month-end from memory.
  • Set a reorder point per item (units left when a new order must be placed) and let the sheet flag it; running out of a best seller costs more than any overstock.
  • Never edit a past month's counts to make a difference disappear — record the adjustment in the current period with a note, so the trail survives.
  • Keep the unit-cost column protected or clearly marked; a cost accidentally overwritten with a selling price corrupts every COGS figure downstream.
  • Count your top sellers weekly even if the full count is monthly — fast-moving items drift fastest.

Start from a tracker with COGS built in

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.

Frequently asked questions

What is the COGS formula, in plain terms?

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).

How often should I physically count inventory?

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 does a spreadsheet stop being enough for inventory?

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.

Before you go

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

Gear we use

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.