Small Shop Inventory That Matches the Shelf: How the Inventory Ledger Works
A small shop inventory that matches the shelf: how the Inventory Ledger logs Added, Sold, Used and Counted from a phone form, with a supplier reorder list
A small shop inventory is worth keeping for exactly as long as its numbers match the shelf, and the Inventory Ledger is built around that one idea. It is a Google Sheet with a phone form: each time a batch is finished, an order goes out, supplies are used up or a shelf is counted, the entry travels form to sheet and the counts update from it. Logged in-the-moment, at the packing table or the market booth, the sheet holds a count worth trusting before an order goes in.
The form that writes to the list
The form is called Log Inventory, and a link to it sits on the Dashboard under the heading "In-the-moment entry." It asks for five things. Date, which falls back to today when it is left blank. The Inventory item, chosen from a dropdown of every item on the list, A to Z. What happened: Added, Sold, Used or Counted. Amount, a number in the item's own unit. And a Note.
Everything the form collects lands as one row on the Inventory Log tab, so the whole history of the shelf sits in one place, in order. The log holds up to 10,000 entries, and a shop that reaches the limit is told on the Dashboard from 90 percent full, rather than having entries quietly dropped.
Sold and Used are different things
Four choices cover what happens to stock. Added raises the number: a batch is finished or a supplier box arrived. Sold lowers it: the item went to a customer. Used lowers it too: wax poured into a jar, a jar that cracked, a bar given away as a sample. Counted records what is really on the shelf.
Keeping Sold apart from Used is what makes the Sold figure honest. A candle that went to a customer counts as a sale; a cracked jar does not. The Items tab keeps a separate column for each, and the Dashboard's selling band reads only from Sold.
The Items tab is the list
Every item has one row on the Items tab, with room for 250 of them. The owner's columns are Item, Category, Location, Unit, Reorder at, Reorder amount, Supplier, Cost each and Notes. The sheet fills in the rest: Added, Sold, Used, On Hand, Value, Sold in the last 30 days, Status and Last Counted.
An item is added on the Items tab, not on the form, which keeps the form to one dropdown. As a name goes into the Item column, it appears in the form's Inventory item choices. Each item carries one unit, in the owner's own word: each, bars, packs, oz. The sheet never converts between units, so a number always means what the unit beside it says. Finished goods and supplies share the list, one row each.
How the On Hand number is worked out
On Hand is the most recent Counted amount, plus everything Added since that count, minus everything Sold and Used since it. An item that has never been counted uses the whole log: all Added, minus all Sold and Used. It is a live formula over the log, so correcting a log row corrects the number.
That makes Counted the action that keeps the list honest. A busy market table can leave a sale unlogged. Counting the shelf and logging the result sets On Hand to what is physically there, and the Last Counted column shows the date, so each item shows how recently its number was checked against the shelf. If an item is sold past zero in the log, its Status reads Out and its Value reads zero, never a negative figure.
Reorder at, and the Reorder List
Each item has a Reorder at number: the amount at which it is time to make or buy more, and a Reorder amount for how many at a time. When On Hand reaches the Reorder at number or falls below it, the Status reads Low, and at zero or below it reads Out. Those two statuses feed the Reorder List on the Dashboard, which lists every Low or Out item grouped by supplier and then by name, with its On Hand, its Reorder at, its Reorder amount and its unit. One supplier is one order, so an order can begin with a look at the Dashboard.
The row of counts across the top shows the whole shop at once: items on the list, Low and Out, and the value on hand. Value is the owner's own Cost each times what is on the shelf, added up for every item that has a cost. Items with no cost are left out, and the heading says so.
What is selling
Below the Reorder List sits Selling, last 30 days: the top items by what was logged as Sold, with On Hand and Status beside each. A best seller that is running low appears on both bands, which makes it the obvious first thing to make or reorder. With no sales logged in the window, the band reads "No sales in the last 30 days." Recent Activity sits beneath it and shows the latest entries from the log, newest first.
Locations and categories, in the owner's words
The Customize Forms tab holds two short lists: Locations and Categories. They start with Shelf, Supply closet, Packing table, Storage tote and Booth, and Candles, Soap, Wax melts, Gift sets, Wax, Fragrance oil, Wicks, Jars, Packaging and Shipping. They fill the dropdowns on the Items tab, and any of them can be renamed, added to or removed, so the sheet describes the owner's own shop rather than a template's.
Starting with a sample shop, then making it your own
The Inventory Ledger arrives filled with a sample candle and soap shop: 60 items across five locations and ten categories, with about three months of log entries, so every status and every band on the Dashboard shows something real. That makes the whole system visible before any of the owner's own data goes in. Clear Sample Data, on the custom Ledger & Light menu, empties the log and the items, keeps the locations and categories, and leaves the Items tab ready for the owner's own list. Entering that list is real work, done once. After that, each entry is a short form on a phone, and the sheet does the totalling, the sorting and the dating.
The menu also carries "Show my Log Inventory form link," so the form's address is always on the menu when it needs to be bookmarked on a phone.
Owned, not rented
The Inventory Ledger is a Google Sheet bought once and kept, living in the owner's own Google Drive with no subscription to renew. Because the log is a list of rows, a shop that grows its catalogue simply adds rows to the same sheet, and the history stays in one file.
This article describes how the Inventory Ledger works. It keeps the owner's own counts and the owner's own cost figures, and the sheet's numbers are the owner's own entries.