A warehouse inventory spreadsheet answers two questions for every item: how many, and where. In a stockroom the second is obvious. In a warehouse with racks it isn't, and a pallet nobody can find is the same as a pallet you don't have. So every item on the sheet has a location: aisle, bay and level. One item can sit in two locations, and each is counted on its own. Behind the item rows are the lots: each production run or delivery with its date, so the picker takes the oldest first and a recalled lot can be found on the rack. And the count is done a few bins at a time, every week, not once a year with the doors shut. Below is a sheet for a made-up granola maker's warehouse, how to name locations, and the file.
The short version
- Every item has a home: aisle, bay, level. Label the racks.
- One item in two places is two lines to count.
- Each run or delivery is a lot. Pick the oldest first.
- Count a few bins every week. Count the valuable ones most often.
- Raw materials, packaging and finished goods on one sheet, in separate aisles.
- TaroStack keeps stock by place and by lot, counted on a phone. More below.
An example sheet
| Item | Unit | On hand | Reorder at | Unit cost | Value | Status |
|---|---|---|---|---|---|---|
| Granola, original, case of 12 | case | 184 | 120 | $31.20 | $5,740.80 | OK |
| Granola, maple pecan, case of 12 | case | 76 | 120 | $34.80 | $2,644.80 | REORDER |
| Granola bars, box of 24 | box | 240 | 150 | $19.50 | $4,680.00 | OK |
| Rolled oats, 50 lb sack | sack | 62 | 40 | $27.00 | $1,674.00 | OK |
| Honey, 60 lb pail | pail | 9 | 12 | $162.00 | $1,458.00 | REORDER |
| Pecan pieces, 30 lb case | case | 14 | 10 | $148.00 | $2,072.00 | OK |
Ten rows in all, $21,296.60 on the racks, with three to reorder: maple pecan granola, honey and shipping cartons. For a finished product, "reorder" means schedule a production run.
Naming locations
| Part | Means | Example |
|---|---|---|
| Aisle | A letter for each row of racking | A |
| Bay | A number for each section along the aisle | 01 |
| Level | A number up from the floor | 1 |
| Location | The three together | A-01-1 |
Print the code on a label and put it on the rack beam, at eye level, for every level of every bay. Then put things where the labels say. This warehouse keeps finished goods in aisle A, raw materials in B, and packaging in C, with tree nuts in their own bay at the end of B.
One item, two places. Original granola is in A-01-1 and A-01-2: 64 cases of the older lot at floor level and 120 of the newer one above it. The sheet shows 184 in all, and the lots tab says which is where. An order ships short when the picker sees an empty-looking A-01-1 and stops. The second location is what the sheet is for.
Floor level is for picking. Keep what's being picked at floor level and the reserve above it. When the pick level empties, bring the reserve down and write the move on the sheet.
Lots on the rack
| Made or received | Item | Lot | Quantity | Left | Best by | Where |
|---|---|---|---|---|---|---|
| Nov 3 | Granola, original | G-1103 | 120 cases | 64 | May 3 | A-01-1 |
| Nov 17 | Granola, original | G-1117 | 120 cases | 120 | May 17 | A-01-2 |
| Oct 20 | Pecan pieces | PN-44817 | 20 cases | 14 | Apr 20 | B-03-1, allergen bay |
| Sep 8 | Honey | H-26-09 | 24 pails | 9 | B-02-1 |
Picking the older lot first is easier when the older lot is in the easier place. That's why G-1103 is on the floor and G-1117 is above it. Write the lot on the pallet tag in letters you can read from the aisle. When a customer or a supplier asks about a lot, the tab says how much you have and exactly where it's standing. The difference between the two rules is in FEFO vs FIFO.
Counting by bin
A full count once a year stops the warehouse for a day and finds errors that are eleven months old. Counting a few locations every week finds them while someone still remembers what happened.
| What | How often | Why |
|---|---|---|
| Finished goods and anything expensive (honey, pecans) | Every week or two | Most of the money, most of the movement |
| Other raw materials and packaging | Every month | |
| Pallets, wrap, supplies | Every quarter | Cheap and slow |
Count the location, not the item: go to A-01-1, count what's there, and compare. Count before the day's picking starts. When a count is off, look for the reason that day: a pick from the wrong lot, a pallet put away in the wrong bay, a case damaged and binned without a note. A form is in cycle count form, and the method in how to count inventory. Food safety for a storage building is in HACCP plan for a warehouse.
Get the spreadsheet
Download the warehouse inventory spreadsheet. Four sheets: inventory with locations, value and REORDER, lots with days left, 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 spreadsheet has one "where kept" cell for each item. Real stock moves: a pallet comes down from reserve, a production run goes into whichever bay was empty, forty cases leave on three orders from two lots. Each of those is an edit somebody has to make, on a forklift or after the truck has gone. After a busy week the sheet's locations are where things were on Monday.
How TaroStack does it
TaroStack keeps stock by place and by lot, and changes both as things move.
Stock is held by location. One item can be in several places, and each place has its own count.
Each run and each delivery is a lot with its date. Lot labels print with the code for the pallet tag.
Oldest first. Picking takes the first-expired lot by default, and one picking list covers the day's orders.
Count on a phone, at the rack, with no signal. TaroStack suggests what to count, and a count shows what each difference is worth.
Starting a recall holds a lot wherever it's standing, and one report lists which customers already have it.
| The job | Spreadsheet | In TaroStack |
|---|---|---|
| Where the other pallet is | One cell, as of Monday | Stock by location |
| Which lot to pick | Read the pallet tags | Oldest lot first |
| This week's bins to count | A list you keep | Suggested |
| Finding a recalled lot | Walk the aisles | On hold, by location |
Stock, lots, counts and recalls are on every plan, from $9.99 a month. More than one site, with transfers between them, is on Business at $89 a month. 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.
