An inventory list has one row for each thing you keep and a small number of columns. Six of them are the format: the item, its unit, the quantity on hand, the reorder level, the unit cost, and the value, which is quantity times cost. With those six, a list can answer the three questions a business asks of it: what do we have, what do we need to buy, and what is it worth. Four more columns make it usable by someone other than the person who wrote it: category, supplier, where it's kept, and a status that flags what's low. Everything else belongs on another sheet. Below is each column with what goes in it, a filled-in example for a made-up candle maker, and the file.
The short version
- Six columns: item, unit, on hand, reorder at, unit cost, value.
- Four more: category, supplier, where kept, status.
- One row for each thing in each form: a 50 lb bag and loose flour are one item, in pounds.
- The unit is the one you count in. Write the pack size in the name.
- Value and status are formulas. Don't type them.
- Dates, lots and counts go on their own sheets.
- TaroStack keeps the list current as stock comes in and goes out. More below.
The format
| Column | What goes in it | Example |
|---|---|---|
| Item | A name specific enough that a stranger picks the right thing | Jar, 8 oz, with lid |
| Category | The group it belongs to | Packaging |
| Unit | What one of it is, as you count it | each |
| On hand | How many, at the last count | 240 |
| Reorder at | The quantity at which you order more | 144 |
| Unit cost | What one unit cost you | $1.35 |
| Value | On hand × unit cost. A formula | $324.00 |
| Status | REORDER when on hand is at or below the level. A formula | OK |
| Supplier | Who you buy it from | Glass distributor |
| Where kept | The shelf, room or bin | Shelf B1 |
A filled-in example
| Item | Category | Unit | On hand | Reorder at | Unit cost | Value | Status |
|---|---|---|---|---|---|---|---|
| Soy wax | Raw material | lb | 120 | 50 | $2.10 | $252.00 | OK |
| Fragrance oil, lavender | Raw material | oz | 64 | 32 | $1.45 | $92.80 | OK |
| Wicks, CD 12 | Raw material | each | 800 | 300 | $0.09 | $72.00 | OK |
| Jar, 8 oz, with lid | Packaging | each | 240 | 144 | $1.35 | $324.00 | OK |
| Candle, lavender, 8 oz | Finished goods | each | 86 | 48 | $5.40 | $464.40 | OK |
| Candle, cedar, 8 oz | Finished goods | each | 30 | 48 | $5.55 | $166.50 | REORDER |
Eight rows in all, worth $1,528.50, with one flagged: cedar candles are at 30 against a level of 48.
Getting each column right
Item. "Wax" isn't enough once there are two waxes. "Soy wax" is. Put the size in the name where sizes differ: "Jar, 8 oz, with lid". Decide the pattern once, thing first and then its details, so the list sorts sensibly.
Unit. Choose the unit you count and use, and keep to it. Wax is bought by the 50 pound case and used by the pound, so it's listed in pounds. If the unit is "case" on one count and "lb" on the next, the numbers mean nothing. Converting between the unit you buy in and the unit you use is the commonest source of wrong lists.
On hand. A count, with a date at the top of the sheet. A list with no date is a rumor.
Reorder at. What you'll use while waiting for a delivery, plus a margin. If wax takes a week to arrive and you use 40 pounds a week, 50 is a sensible level. The sum is in reorder point example.
Unit cost. What you paid for one unit, freight included, in the same unit as the count. A $105 case of 50 pounds is $2.10 a pound. For something you make, it's what it cost you to make: a candle here is $5.40.
Value and status. Let the sheet work these out. Typed in by hand, they're wrong the first time a quantity changes.
What to leave off
| Tempting column | Where it belongs |
|---|---|
| Expiry or best-by date | A lots sheet: one item can have several deliveries with different dates |
| Lot number | The same lots sheet |
| Selling price | A price list. Stock is valued at cost |
| Counted quantity and difference | A count sheet, filled in during the count |
| Notes about last month | Nowhere. The list is today |
A list with fifteen columns doesn't get kept up. If a column isn't used to decide something every week, take it off.
Other layouts for particular jobs are in inventory spreadsheet examples, a sheet for the count itself in inventory count sheet example, and one with dates in inventory list with expiration dates.
Get the file
Download the inventory list format. Four sheets: the inventory list in this format, with value and status worked out; a lots sheet; 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
The format is right and the numbers still go stale, because a list only changes when somebody changes it. Every delivery adds to a row. Every batch takes from four rows and adds to one. Every sale takes one off. That's dozens of edits a week that all have to happen, in the right unit, for "on hand" to mean what it says. Most lists are accurate on the day they're counted and drift from there.
How TaroStack does it
TaroStack keeps the same columns and changes them as the work is recorded.
Receiving adds to stock, with or without a purchase order, at what the delivery cost.
Recording a batch takes its materials off and puts what was made on.
Units convert: bought by the case, counted by the each, used by the gram.
A reorder list in days of cover, with reorder points suggested from how stock really moves.
Counting on a phone, with no signal. A count shows what a difference is worth before you post it.
| The column | Spreadsheet | In TaroStack |
|---|---|---|
| On hand | The last count | Changed by every receipt, batch and shipment |
| Unit cost | Typed from an invoice | From deliveries, freight included |
| Reorder at | A level you set once | Suggested from how stock moves |
| Value | A formula on a stale count | Stock value, by site and month |
Stock, counts and reordering are on every plan, from $9.99 a month for up to 50 items, or $39 a month for 3,000. 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.
