Inventory Spreadsheet for Small Business (Free Build)

Quick Answer: A working inventory spreadsheet for a small business has two tabs: an Items list (SKU, description, unit cost, price, reorder point) and a Movements log where every receipt, sale and adjustment is one row. Stock on hand is a SUMIFS of movements per SKU, inventory value is quantity times cost, and a reorder flag lights up when stock falls to the reorder point.

Most inventory templates fail because they store the current quantity as a number people overwrite. Once two people edit it, nobody knows why the shelf has 14 when the sheet says 20. Logging movements instead, and calculating stock from them, gives you a history you can audit and a free spreadsheet that holds up for hundreds of items.

Tab one: the item list

Each product gets exactly one row. Column by column:

Format the range as an Excel table and name it Items. Setting the reorder point is simple arithmetic: average units sold per week times the weeks your supplier takes to deliver, plus a safety buffer. If you sell 8 lavender candles a week, delivery takes two weeks and you want one week of buffer, the reorder point is 8 × 3 = 24.

Tab two: the movement log

Every change in stock is a row: date, SKU, type, quantity (positive in, negative out), reference, note. Types are a short fixed list: Received, Sold, Returned, Damaged, Count adjustment. Use a drop-down so nobody invents a sixth. Microsoft's steps for data validation: select the cells, choose Data > Data Validation, set Allow to List, and enter the source. Point the SKU column's list at Items[SKU] the same way so typos cannot create phantom products.

Name this table Moves. Then the on-hand formula on the Items tab is a SUMIFS:

=SUMIFS(Moves[Qty],Moves[SKU],[@SKU])

To show the description next to each movement for readability, XLOOKUP pulls it from Items: =XLOOKUP([@SKU],Items[SKU],Items[Description],"unknown SKU"). Microsoft lists XLOOKUP for Microsoft 365, Excel 2021 and 2024, not Excel 2016 or 2019; on those versions use INDEX with MATCH instead.

One SKU through a month

Here is what the log looks like for the lavender candle in March, starting from a count of 30 on March 1:

SUMIFS returns 62 on hand, valued at 62 × $4.80 = $297.60. The reorder flag fired in the first week, as soon as sales pulled stock down to 24. The carton ordered that day landed on March 16, when the shelf was down to 11. That is the flag working: the order went out in time because the point was set from delivery time, not from a feeling that the shelf looked thin.

How do you record sales without typing every one?

Typing each sale is the step that kills most inventory sheets. Options, from simplest:

1. Daily totals. At closing, enter one Sold row per SKU with the day's quantity from your point-of-sale report.
2. Weekly import. Export the POS item sales report as CSV, paste it into a Staging tab, and use a PivotTable or SUMIFS to roll it up to SKU and quantity, then copy those as Sold rows.
3. Online store export. Most store platforms export order lines with SKU and quantity; the same staging approach works.

Whichever you choose, keep the reference column filled (the POS report date, the order number) so a discrepancy can be traced.

Physical counts and shrinkage

At least once a year, and ideally each quarter for fast movers, count what is physically on the shelf. Put the count in a Count tab next to the calculated on-hand figure, and the difference becomes a Count adjustment row in Moves dated the count day. Summing adjustments by SKU over a year tells you where shrinkage lives: breakage in glass items, theft in small high-value ones, or a receiving habit that skips the paperwork.

What is my inventory worth?

Total inventory value at cost is =SUM(Items[Value at cost]). That figure is the inventory line on your balance sheet. Using the current unit cost is the simplest method, and it is fine for planning. If costs change often, a tax preparer may want inventory valued under a specific method (such as first-in, first-out), which a spreadsheet can do but which is far easier in inventory or accounting software.

On the tax side, IRS Publication 334, Tax Guide for Small Business (2025 edition), says that a small business taxpayer can choose not to keep an inventory, provided its method treats inventory as non-incidental materials and supplies or conforms to its financial accounting treatment; it defines a small business taxpayer as one with average annual gross receipts of $31 million or less for the three prior tax years (indexed for inflation) that is not a tax shelter. If you do keep an inventory, the publication says you generally must use an accrual method for purchases and sales and value inventory each year to figure cost of goods sold. That is a decision for your preparer, but the spreadsheet gives either choice the records it needs.

Tie purchases to supplier payments

Every Received row should trace to a supplier invoice, and every invoice to a payment on your bank or card statement. A monthly check catches the two errors that quietly inflate inventory: goods logged as received that were never paid for (or never arrived), and invoices paid for goods nobody logged.

• Add an Invoice column to Moves for Received rows.
• Build a small Payables tab: supplier, invoice number, amount, date paid.
• Fill the date paid from the bank and card statements. If those come as PDFs, QuickBankConvert gives you the statement as rows, so you can filter by supplier name and match amounts to invoices quickly.
• Compare: for each invoice, received quantity × unit cost should be close to the invoice amount less freight and tax.

The general ledger side of this, where those payments are coded to purchases or cost of goods sold, belongs in your bookkeeping spreadsheet template; this sheet only tracks the units.

When should you move to inventory software?

A spreadsheet is the right tool until one of these becomes true:

Sellers on marketplaces hit these limits first; Amazon FBA bookkeeping covers the extra wrinkle of stock held in someone else's warehouse. Until then, two tabs and three formulas will keep the shelves and the numbers in agreement.

Related guides

• Bookkeeping spreadsheet template for the money side of purchases and sales
• Small business sales tracker in Excel to analyse what sells
• Balance sheet template in Excel, where the inventory value lands