An inventory spreadsheet in Excel is a short job and needs only three formulas. You make a row for each item with ten columns, then let the sheet work out three things you'd otherwise do in your head: what each row is worth (on hand × unit cost), whether it's time to reorder (on hand at or below a level you set), and, for anything with a date, how many days are left. Add a total at the top and a second sheet for counting, and you have something you can run a small business on. Below are the steps with the exact formulas, using a made-up coffee roaster, and the finished file if you'd rather start from it.
The short version
- Row 1 is headings. One item to a row below it.
- Ten columns: item, category, unit, on hand, reorder at, unit cost, value, status, supplier, where kept.
- Value:
=D2*F2. - Status:
=IF(D2<=E2,"REORDER","OK"). - Days left on a dated lot:
=G2-TODAY(). - Freeze the top row, and make REORDER show in red.
- TaroStack keeps the same list current without retyping. More below.
Step 1: the columns
Open a blank workbook, name the first sheet Inventory, and type these headings in row 1.
| Column | Heading | What goes in it |
|---|---|---|
| A | Item | A name specific enough to reorder from: "Bag, 12 oz, with valve" |
| B | Category | Green coffee, Packaging, Finished |
| C | Unit | The unit you count in: lb, each, bag |
| D | On hand | What you counted |
| E | Reorder at | The level at which you order more |
| F | Unit cost | What one unit cost you, delivered |
| G | Value | A formula |
| H | Status | A formula |
| I | Supplier | Who you buy it from |
| J | Where kept | Rack, shelf or room |
Then make the headings stay put: select row 2, and on the View tab choose Freeze Panes. The headings now stay visible as you scroll.
Step 2: the two formulas
In G2, the value of the row:
=IF(OR(D2="",F2=""),"",D2*F2)
In H2, the reorder flag:
=IF(OR(A2="",D2="",E2=""),"",IF(D2<=E2,"REORDER","OK"))
The plain versions are =D2*F2 and =IF(D2<=E2,"REORDER","OK"). The longer ones do the same sums and leave the
cell blank on an empty row, so you can copy them down a hundred rows without a column of zeros. Select G2 and H2,
and drag the small square at the corner of the selection down as far as you want rows.
Format F and G as currency. For the total, pick an empty cell to the right and type =SUM(G2:G100). To count
what needs ordering: =COUNTIF(H2:H100,"REORDER").
Step 3: fill it in
| Item | Unit | On hand | Reorder at | Unit cost | Value | Status |
|---|---|---|---|---|---|---|
| Green coffee, Colombia | lb | 320 | 300 | $4.85 | $1,552.00 | OK |
| Green coffee, Ethiopia | lb | 180 | 150 | $5.60 | $1,008.00 | OK |
| Green coffee, decaf | lb | 60 | 75 | $6.10 | $366.00 | REORDER |
| Bag, 12 oz, with valve | each | 1400 | 1000 | $0.38 | $532.00 | OK |
| Label, 12 oz bag | each | 900 | 1000 | $0.07 | $63.00 | REORDER |
| Shipping box, 6 bags | each | 220 | 150 | $0.92 | $202.40 | OK |
Eight rows in all, $4,367.80, with three to reorder: decaf green coffee, labels and roasted decaf. The labels are the row to notice. They're $63.00 of stock, the smallest number on the sheet, and without them nothing ships.
To make REORDER stand out, select column H, and on the Home tab choose Conditional Formatting, Highlight Cells Rules, Equal To. Type REORDER and pick red text.
How to choose the reorder level for each row is in reorder point example.
Step 4: a sheet for dates
Anything with a best-by date needs more than one number, because two deliveries of the same item have different dates. Add a second sheet, Lots, with a row for each delivery or batch: Received, Item, Supplier, Lot, Quantity, Unit, Best by. Then two more formulas.
Days left, in H2:
=IF(G2="","",G2-TODAY())
Status, in I2:
=IF(H2="","",IF(H2<0,"EXPIRED",IF(H2<=30,"USE FIRST","OK")))
TODAY() is today's date, so the count changes every day you open the file. The 30 is the warning window: change it to suit what you sell. More of these are in Excel formula to track expiration dates.
Step 5: a count sheet
The On hand column is only as good as the last count. Add a third sheet that pulls the item names and the sheet's quantities across, with empty columns for Counted and Difference:
- Item, in A2:
=Inventory!A2 - On the sheet, in D2:
=Inventory!D2 - Difference, in F2:
=IF(E2="","",E2-D2) - Worth, in G2:
=IF(E2="","",F2*Inventory!F2)
Print it, walk the shelves, and type what you counted. The Worth column says what each difference cost. Then type the counted figures into Inventory, and you start the next period right.
Other layouts, for stock in and out, recipes and batches, are in inventory spreadsheet examples. The same build in Google Sheets is in inventory spreadsheet in Google Sheets, and which columns suit which business in inventory list format.
Get the finished file
Download the inventory spreadsheet. Four sheets: Inventory, Lots, Count sheet and a read-me, built as above, with the roaster's rows in it. Click any Value or Status cell to see its formula. It's an ordinary Excel file with no macros, and it works in Google Sheets, LibreOffice and Numbers.
Where this stops working
Every formula above is correct, and the sheet still depends on someone typing. The On hand column doesn't know that Tuesday's roast used 60 lb of the Colombia or that 200 bags went out on Wednesday. Either you subtract by hand each day, or the sheet is right only on count day. And one roasted bag is really four rows changing at once: coffee down, a bag down, a label down, a finished bag up. Excel will do that sum if you build it. Most people don't, and the labels run out on a Thursday.
How TaroStack does it
TaroStack keeps the same columns and does the subtracting.
A recipe says what a finished item takes: the coffee, the bag, the label. Each batch takes them out of stock and adds what it made.
Receiving adds stock as it arrives, with or without a purchase order, each delivery as a lot with its date.
A reorder list in days of cover, with each supplier's lead time and minimum, and reorder points suggested from how fast things really go.
Counting works on a phone with no signal. A count shows what each difference is worth.
"What could I make right now" says how many bags today's stock allows, labels included.
| The job | Excel | In TaroStack |
|---|---|---|
| Value and reorder flags | Formulas you copy down | Kept for every item |
| Taking materials out when you make something | By hand, or a sheet you build | Each batch does it |
| Days left on each lot | A formula on a second sheet | An alert before the date |
| The count | Print, count, retype | On the phone, valued |
TaroStack isn't a spreadsheet, and you can bring yours in: items, recipes, suppliers and opening stock import from a CSV file, with a preview of every row. Stock, recipes and the reorder list are on every plan, from $9.99 a month (50 items, 5 recipes) or $39 a month (3,000 items, 50 recipes). 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.
