A store's inventory is the goods it has bought to sell and hasn't sold yet: what's on the shelves, in the back room and in boxes not yet opened. A store inventory sheet lists it with a row for each thing a customer can buy. A candle in three scents is three rows, because you reorder them separately and one of them will outsell the others. Each row has the quantity on hand, what one cost you, a level to reorder at, the supplier and where it's kept. From those columns come the three numbers a shop owner needs: what the stock is worth, what to reorder this week, and, after a count, what's gone missing. Below is a sheet for a made-up gift shop, how to set the levels, the count, and the file.
The short version
- One row for each item a customer can buy, down to scent, size and color.
- Unit cost is what you paid, not the price on the tag.
- Value at cost for your books. Value at retail to see what the shelves could bring in.
- Reorder at = what sells while you wait for the delivery, plus a cushion.
- Count on a regular day. The difference from the sheet is shrink.
- Food and anything with a date gets a delivery row with its lot.
- TaroStack keeps stock current and counts it on a phone. More below.
An example sheet
Counted on a Sunday after closing.
| Item | Unit | On hand | Reorder at | Unit cost | Value | Status |
|---|---|---|---|---|---|---|
| Scented candle, 8 oz | each | 34 | 24 | $7.50 | $255.00 | OK |
| Greeting card, assorted | each | 210 | 150 | $1.10 | $231.00 | OK |
| Ceramic mug | each | 18 | 20 | $6.25 | $112.50 | REORDER |
| Tote bag, canvas | each | 26 | 15 | $5.40 | $140.40 | OK |
| Hand soap, 12 oz | each | 40 | 30 | $4.20 | $168.00 | OK |
| Lip balm | each | 75 | 60 | $1.35 | $101.25 | OK |
Ten rows in all, $1,376.25 at cost, with two to reorder: mugs and honey.
Cost and retail. The sheet values stock at cost, which is what your books and your insurer want. The 34 candles are $255.00 at cost. At this shop's price of $18.00 they're $612.00 on the shelf. Add a price column if you want both. The method accountants use to go from one to the other is in retail inventory method formula.
One row or several. Greeting cards are one row here because this shop buys them as an assortment and prices them all the same. If you reorder a card by design, it's a row by design. A code for each row makes that workable: see how to create SKU numbers.
Setting a reorder level
| Lip balm | |
|---|---|
| Sold in a day | 5 |
| Days from order to delivery | 10 |
| Sold while waiting | 50 |
| Cushion | 10 |
| Reorder at | 60 |
The level is what you'll sell before the next delivery can arrive, plus something for a good week. It's a number for each item, and it changes with the season: this shop's candles sell three times as fast in December. A worked example with the formula is in reorder point example.
The count, and shrink
Count on a quiet day, shelf by shelf, in the order of the sheet. The file's count sheet takes what you found and works out the difference.
| Item | Sheet says | Counted | Difference | At cost |
|---|---|---|---|---|
| Scented candle, 8 oz | 34 | 31 | −3 | −$22.50 |
| Lip balm | 75 | 71 | −4 | −$5.40 |
| Ceramic mug | 18 | 18 | 0 | $0.00 |
Three candles and four lip balms are gone without a sale: $27.90 at cost. That's shrink, and it has ordinary causes. Something broke and wasn't written down. A delivery was two short and nobody counted it in. Something was stolen. A sale was rung up as the wrong item. Write off breakage when it happens, with the reason, and the count shows only what's unexplained. Counting a section at a time is in cycle count form.
How fast stock turns is the other number worth working out once a year: cost of goods sold divided by the average stock at cost. A worked one is in inventory turnover example.
Things with dates
| Received | Item | Lot or batch | Quantity | Best by | Note |
|---|---|---|---|---|---|
| Nov 3 | Local honey, 12 oz | Lot H-1027 | 24 jars | Oct 1, 2028 | |
| Oct 20 | Chocolate bar, 3 oz | Lot 2610 | 60 | Apr 20, 2027 | |
| Sep 15 | Hand soap, 12 oz | Batch 0915 | 48 | No date on it: note the batch |
A gift shop that sells honey and chocolate is selling food. Write each delivery's lot and date, and put new stock behind old. If the beekeeper ever calls about lot H-1027, you'll know whether it's on your shelf.
Sheets for particular kinds of shop: candy store inventory, pet store inventory, convenience store inventory spreadsheet. Goods you hold for a maker and pay for only when sold are in consignment inventory example.
Get the sheet
Download the store inventory sheet. Four sheets: inventory with value and REORDER, deliveries with lots and best-by dates, a count sheet that works out each difference and what it's worth, and a read-me. It's an ordinary Excel file with no macros, and it works in Google Sheets, LibreOffice and Numbers.
Where this stops working
A sheet is a photograph. It's right on Sunday, and every sale from Monday on makes it a little more wrong. With ten items you can keep it up by hand. With four hundred, nobody subtracts the day's sales, the reorder column stops meaning anything, and the sheet becomes something you fill in once a year for the accountant.
How TaroStack does it
TaroStack keeps the list current between counts.
Receiving adds stock as it arrives, with or without a purchase order, at what you paid.
Counting works on a phone with no signal, in the back room. A count shows what each difference is worth.
Every adjustment carries a reason: broken, stolen, sample, miscount.
A reorder list in days of cover, with each supplier's lead time and minimum, and reorder points suggested from how fast things really sell.
Lots and dates for the food on the shelf, with an alert before a date arrives.
| The job | Spreadsheet | In TaroStack |
|---|---|---|
| What's on hand | As of the last count | Kept current |
| What to reorder, from whom | Rows that look low | A reorder list by supplier |
| What shrink cost this quarter | Unknown | Adjustments by reason |
| Whether a recalled lot is on the shelf | Read every jar | The lot's stock |
TaroStack doesn't run the register. It connects to Square. Stock, counts, receiving and reordering are on every plan, from $9.99 a month (50 items) or $39 a month (3,000 items). The first 14 days are free, with no credit card. If you'd like a hand setting up, ask, and we'll do it with you.
