A wholesaler doesn't sell what's on the shelf. It sells what's on the shelf and not already promised. Orders come in days before they ship, so at any moment some of the cases in the warehouse belong to a customer who hasn't collected them yet. A wholesale inventory template tracks both numbers for every product: cases on hand, from a count, and cases on open orders, from the order book. The difference is what you can say yes to on the phone. It also counts in cases, with the units in a case beside them, because that's how the stock arrives, sits and leaves. Below is a sheet for a made-up wholesaler of specialty foods, what its columns are for, and the file.
The short version
- One row for each product in each case size.
- Cases available = cases on hand − cases on open orders.
- Promise from available, never from on hand.
- Reorder when available falls to the level, not when the shelf looks empty.
- Value stock at what a case cost you.
- Keep each delivery's lot and best-by date: stores ask for shelf life.
- TaroStack keeps stock, orders and lots together. More below.
An example sheet
| SKU | Product | Units in a case | On hand | On open orders | Available | Reorder at | Status |
|---|---|---|---|---|---|---|---|
| HS-ORG-12 | Hot sauce, original, 5 oz | 12 | 48 | 20 | 28 | 30 | REORDER |
| HS-HAB-12 | Hot sauce, habanero, 5 oz | 12 | 36 | 6 | 30 | 20 | OK |
| JM-STR-6 | Strawberry jam, 9 oz | 6 | 60 | 12 | 48 | 25 | OK |
| GR-MPL-8 | Maple granola, 12 oz | 8 | 22 | 10 | 12 | 15 | REORDER |
| CF-DRK-6 | Coffee, dark roast, 12 oz | 6 | 40 | 0 | 40 | 20 | OK |
| CR-SEA-12 | Crackers, sea salt, 4 oz | 12 | 18 | 18 | 0 | 12 | REORDER |
All quantities are cases. Six products, $6,637.60 at cost, with three to reorder.
The crackers show why the second number matters. Eighteen cases are on the shelf, and a walk through the warehouse would say there's plenty. All eighteen are on open orders. There are none to sell, and the next store that calls should hear a date, not a yes.
The original hot sauce has 48 cases, which looks comfortable against a level of 30. Twenty are promised. The 28 that are free are under the level, so it goes on this week's order to the maker.
Cases and units
Count in the unit you sell in. This wholesaler buys and sells whole cases, so every quantity on the sheet is cases, and the units in a case are there for the day a store asks "how many bottles is that?" The file works out units available: 28 cases of hot sauce are 336 bottles.
If you break cases, give the broken case its own row in single units, or decide you don't sell singles. A sheet that says 22.5 cases is one nobody can count against.
The code on each row should tell the picker the case size: HS-ORG-12 is twelve to a case. How to build codes is in how to create SKU numbers.
Setting the reorder level
| Maple granola | |
|---|---|
| Cases sold in a week | 6 |
| Weeks from order to delivery | 2 |
| Sold while waiting | 12 |
| Cushion | 3 |
| Reorder at | 15 |
The level is what you'll sell before the next delivery can arrive, plus a cushion, compared with cases available. These are this wholesaler's numbers. The formula is in reorder point example, and the cushion in safety stock formula. A maker may have a minimum order, so the amount you order can be more than the amount you need.
Lots and shelf life
| Received | SKU | Supplier lot | Cases | Best by |
|---|---|---|---|---|
| Nov 9 | HS-ORG-12 | L2611 | 40 | May 9, 2028 |
| Nov 12 | GR-MPL-8 | G1109 | 30 | May 12, 2027 |
| Nov 16 | CR-SEA-12 | C320 | 24 | Aug 16, 2027 |
A wholesaler of food is in the middle of every recall: the maker calls you, and you have to call the stores. That takes two records, which lot came in on which delivery and which lot went out on which order. The sheet's second tab holds the first. A store may set a minimum shelf life on what it accepts, so ship the oldest lot first and know each lot's date: see minimum remaining shelf life and what buyers mean by lot tracking.
Get the template
Download the wholesale inventory template. Two sheets and a read-me: inventory that works out cases and units available, REORDER and value at cost, and a deliveries sheet with lots and best-by dates. It's an ordinary Excel file with no macros, and it works in Google Sheets, LibreOffice and Numbers.
Where this stops working
The open-orders column is typed. Every order taken, changed, shipped or cancelled should change it, and on a busy day none of them do. By Thursday the sheet says 28 cases of hot sauce are free when 9 are, and the person on the phone finds out when the picker comes back from the shelf. The lot that went on each order isn't on the sheet at all.
How TaroStack does it
TaroStack keeps stock, orders and lots in one place.
Customers and sales orders. An order is entered once, picked from one picking list, and shipped with a delivery note.
Stock is held by lot, received with or without a purchase order, with the supplier's lot and best-by date.
Oldest first. Picking takes the first-expired lot by default, and a customer can carry a rule for the days of shelf life they require.
What ships is recorded by lot, so one report shows which customers got a lot and what's left.
A reorder list in days of cover, with each supplier's lead time and minimum.
| The job | Spreadsheet | In TaroStack |
|---|---|---|
| What's on open orders | A typed column | The sales orders themselves |
| Which lot to pick | Read the cases | Oldest first, by default |
| Who got lot C320 | Unknown | One report |
| A store's shelf life rule | Remember it | On the customer |
TaroStack doesn't take payments. Customers, sales orders, lots 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.
