A shop with a hundred kinds of things on its shelves and no list of them is running on memory, and memory runs out of stock without warning. A spreadsheet is the cheapest list there is. I have built and kept inventory sheets of this kind myself, and the design below is plain on purpose: a handful of columns, one rule that turns a cell red, and a count on a schedule. The tax and bookkeeping points come from the IRS, and the spreadsheet mechanics from Google’s help pages. Both are linked where used.

Google Sheets – Inventory Management System Template, Learn Google Sheets & Excel Spreadsheets on YouTube

What the sheet has to answer

Hands counting boxes on a shelf with a clipboard

What is here, where it is, and when to buy more

Three questions cover most of what a small shop needs from an inventory list. What do I have? Where is it? And when do I need more? Every column should exist to answer one of those, and a column that answers none of them is clutter.

The IRS is more formal on what counts. In Publication 538, Accounting Periods and Methods, inventory is described as merchandise, raw materials, work in process, finished products, and supplies that physically become part of a saleable item. Purchased goods count once title passes to the buyer, even if they are in transit. Land, buildings, equipment, receivables and goods held on consignment are not inventory. The practical reading for a shop is that the sheet lists what is for sale or goes into what is for sale, and leaves out the shelving, the register and the tape gun.

The columns

Eight columns on one tab

Here is a layout that answers the three questions. It fits on one tab.

ColumnWhat goes in it
Item codeA short, unique code for each distinct item, never reused
NameThe plain name of the item, matching what the shop calls it on the shelf
LocationWhere it lives: a shelf, a bin, a room
On handThe count from the last time someone looked
Reorder atThe count at which the shop buys more
Cost eachWhat one unit cost to buy or make
SupplierWho sells it and how to reach them
Last countedThe date of the last physical count

The item code is the column that saves the most grief. At Marcellino NY, my own business since 2010 and one I have a financial interest in, an early lesson was that too many customizations make every piece different, and hard to track and remake. The cure on the inventory side is the same as on the product side: a code for each distinct thing, so that two items that differ in a way that matters get two codes, and two that differ in a way that does not get one. A name can be spelled three ways in three months. A code cannot, if nobody is allowed to retype it.

The cost column matters because the IRS wants the cost of goods on hand to reflect what the shop actually paid. Under the method Publication 538 calls perpetual or book inventory, the inventory account should reflect the actual cost of merchandise bought or produced, and be reduced when goods leave stock through use, transfer or sale. A column of unit costs is how a spreadsheet carries that idea.

The Reorder at column is a judgment call, and no formula supplies it. It depends on how long the supplier takes to deliver and how fast the item sells, and the shop owner knows both better than any template. A reasonable first pass is to set it by feel, run the sheet for a season, and adjust each time the shop runs out of something it thought it had enough of. The column starts as a guess and turns into a record of what the shop has learned about its own stock.

Making low stock visible

Laptop with a blurred grid on a shop counter beside a tape gun

One rule that fills the row

A number in a cell does not announce that it is low. Google’s help page on conditional formatting in Sheets describes the tool that fixes this. You select the cells, open Format and then Conditional formatting, and add a rule. A single-color rule uses a condition such as “Less than” and a formatting style. For anything more involved the page offers “Custom formula is.”

For an inventory sheet, the rule wanted is one that compares the On hand cell with the Reorder at cell in the same row, and fills the row when the first is at or below the second. The page’s own custom-formula example, a COUNTIF test that highlights duplicate values, shows the shape: a formula that returns true or false, applied to a range. Writing the exact comparison is left to the owner, and I have not tried to reproduce menu labels that Google may have reworded since. The page also notes that when several rules apply, the first one found to be true defines the format, so the order of rules matters.

The same trick catches a second kind of mistake. A rule that colors a cell when the count is blank, or when the Last counted date is old, shows at a glance which shelves nobody has looked at in a while. A sheet that is full of stale counts is worse than no sheet, because it looks authoritative.

Keeping the entries clean

Pick from a list instead of typing

Free text is the enemy of a sheet that has to be sorted. If one person types “Back room” and another types “backroom,” the filter treats them as two places. Google’s help page on drop-down lists covers creating and editing in-cell dropdowns, and the Location and Supplier columns are where they earn their keep. The shop defines the list of locations once, and every entry afterward is a pick from that list.

The page covers creating, editing and deleting dropdown lists, including turning dropdown chip suggestions on or off. It is a small feature with a large effect on how reliable the sheet is in month six.

The same logic argues against merged cells, colored cells that carry meaning only to the person who colored them, and notes crammed into the count column. Each of those makes the sheet read well to one person and badly to everyone else.

Counting and reconciling

Row of small boxes on a shelf with blank shelf tags

The sheet says what should be there; the shelf says what is

A spreadsheet that is never checked against the shelf drifts. The IRS says so directly, in the section on physical inventory: a business must count its actual stock at “reasonable intervals” and adjust the book inventory figure so it matches the physical count. The tax rules are about accounting, but the idea carries to any shop. The sheet says what should be there, the shelf says what is, and the difference is information.

Pick an interval the shop can actually keep. A small shop may count a different section each week and the whole stockroom once a quarter, or count everything before a busy season. Publication 538 does not name an interval beyond “reasonable,” and I have not tried to invent one. What matters is that the date goes into the Last counted column every time, so the sheet shows how old each number is.

When the count and the sheet disagree, correct the sheet and write down why, if the reason is known. Breakage, a missed sale, a miscounted delivery, a return that was never logged: each is a different problem, and a shop that tracks only the final number never sees the pattern.

How valuation enters the picture

Two defensible values for the same stock

If the shop sells goods and files taxes, the sheet’s cost column is also the start of a valuation question. Publication 538 describes the FIFO, or first-in first-out, method: the first items bought or made are treated as the first sold, so ending inventory is matched with the most recent costs. It also describes LIFO, which treats the last items acquired as sold first, and says LIFO is complex and carries its own pooling and election rules. When prices rise, LIFO produces higher cost of goods sold and lower closing inventory, and FIFO does the reverse.

For a one-room shop whose costs barely move, none of that changes the spreadsheet. But a shop whose supplier prices rise over the year will find that the same stock has two defensible values, depending on the method. The choice belongs to the owner’s accountant, not to a blog post. The one thing the owner can do in advance is record each purchase’s cost and date, so that whichever method is chosen, the numbers exist.

The publication also describes an exception for smaller businesses, which can skip keeping a formal inventory for tax purposes if they meet its gross receipts test, though they still need a method that clearly reflects income. I have not quoted the test’s amount, because it changes and the version in force on any given day is the accountant’s to confirm.

A short list for the first afternoon

Everything fancier can wait a season

  • Create one tab with the eight columns above.
  • Give every distinct item a code and enter each once.
  • Add one conditional formatting rule that fills the row when On hand is at or below Reorder at.
  • Set dropdowns on Location and Supplier.
  • Count one shelf, enter the date, and fix the entries that were wrong.
  • Put the next count on the calendar.

That is the whole system. Anything fancier can wait until the simple version has run for a season and shown where it hurts.

I covered the matching idea for customers in a customer spreadsheet for a small shop, and the habits are close: one row per thing, unique identifiers, and a list that someone is responsible for keeping.

For informational purposes only. Inventory valuation and tax rules depend on the business and the year; consult a licensed attorney, accountant or financial advisor before choosing a method or changing how inventory is reported.

Sources