Inventory on hand is what you started with, plus everything that came in, minus everything that went out: on hand = opening stock + received + made − used − sold ± adjustments. That's the quantity physically on your shelves, if every movement was recorded. Two more numbers matter as much. Available is on hand minus anything on hold, quarantined or damaged, because you can't sell or use it. Free to use is available minus what's already promised to an order or a planned batch. In the example below, a kitchen has 20 cans of coconut milk on hand, 16 available and none free, which is why the order placed tomorrow is already late. Below is the formula with an example, the three numbers, weeks on hand, the spreadsheet formula, and why on hand drifts from a count.
The short version
- On hand = opening stock + received + made − used − sold, plus or minus adjustments.
- Available = on hand − held, quarantined or damaged stock.
- Free to use = available − what's promised to orders or planned batches.
- Weeks on hand = on hand ÷ what you use in a week.
- In a spreadsheet, calculate on hand from a log of movements; never type it.
- On hand drifts from the shelf when a movement isn't recorded; a count resets it.
- TaroStack keeps on hand by site and lot as every movement happens, with held stock kept apart. More below.
The formula, worked through
Windward Roots, the made-up taro business I use in these examples, works out its coconut milk at the end of a week:
| Movement | Cans |
|---|---|
| Opening stock, Monday | 14 |
| Received, two deliveries | +48 |
| Used in batches of kūlolo | −40 |
| Sold as cans | 0 |
| Adjusted: two dented cans written off | −2 |
| On hand, Sunday | 20 |
For something you make, the "made" line adds the good units from each batch, and "used" becomes what was sold or went into another product. The formula is the same either way.
On hand, available and free to use
| Number | Formula | Coconut milk |
|---|---|---|
| On hand | Everything on the shelves | 20 |
| Held | One delivery's lot on hold while a dent is checked | −4 |
| Available | On hand − held, quarantined or damaged | 16 |
| Promised | Tomorrow's planned batch of kūlolo | −16 |
| Free to use | Available − promised | 0 |
On hand says 20, which sounds fine. Free to use says nothing is left for anything else this week. Reordering from on hand, instead of from what's free, is how a kitchen runs out with stock on the shelf. How holds work is in hold and release procedure.
Weeks on hand
Weeks on hand turns the quantity into time: on hand ÷ what you use in a week. The kitchen uses about 31.5 cans a week, 4.5 a day, so 20 cans is 0.63 weeks, about four and a half days. Against a 7-day delivery time, that's already too late to order and wait. The same idea in days, with what to do about it, is in reorder point example, and the money version, days of stock on hand across everything, is in inventory KPIs.
The spreadsheet formula
Keep a log, one row per movement, with the date, the item, the quantity in and the quantity out. On a summary sheet, with the item in column A and the opening stock in column B:
=B2+SUMIFS(Log!C:C,Log!B:B,A2)-SUMIFS(Log!D:D,Log!B:B,A2)
That adds every quantity in for the item and takes away every quantity out. Never type an on-hand figure over the formula; if the shelf disagrees, record the difference as an adjustment with its reason, so the log stays the truth. Seven layouts built this way are in inventory spreadsheet examples.
Why on hand drifts from the shelf
The formula is only as good as the movements recorded. On hand drifts when:
| What happened | Why on hand is wrong |
|---|---|
| A batch used two lots of an ingredient, and only one was written down | The other lot still shows on the books |
| A damaged case went in the bin | It's still on hand until it's adjusted |
| A delivery came short, and the order quantity was entered | On hand is higher than the shelf |
| The same unit was counted in cases and in eaches | A conversion error, often by 12 or 24 |
| Stock moved between sites without a record | One site is high, the other low |
A count resets on hand to the shelf; the difference is a loss, a gain or a mistake, and recording its reason is how the next count gets closer. How to count, and what the differences are worth, is in how to count inventory, and how to chase the differences is in why inventory does not match.
Where this stops working
On hand in a spreadsheet is the sum of what someone typed. In a busy week, three batches, two deliveries and a market day, the typing happens on Sunday night from memory, and the number that matters, what's free to use, isn't in the sheet at all. Nobody knows the four held cans are held, or that tomorrow's batch has already claimed the rest.
How TaroStack does it
TaroStack keeps on hand current as the work happens, and keeps held stock out of what's available.
Every movement, recorded where it happens. Receiving adds stock by lot, recording a batch takes ingredients off from the lots used and adds the good units, sales and shipments take stock out, and every adjustment carries its reason. Every movement is a permanent record of who, when and why, so on hand is always the sum of what happened, by site and by lot. Units convert, so a case of 24 and 24 single cans are the same stock.
Held stock, kept apart. Lots on hold, in quarantine or damaged are kept out of what's available to sell, pick or use in a batch. Shipping, selling, making and moving can't take stock below zero: the app says what's there and asks you to count it first.
And time, not only quantity. The reorder list shows days of cover at the rate stock is really going out, with a red line where an order placed today would arrive too late. Counting on a phone, with no signal, shows what each difference is worth before you post it.
| The question | Spreadsheet | In TaroStack |
|---|---|---|
| How many on hand? | Sunday night's sum | Current, by site and lot |
| How many can I use? | Not in the sheet | Held and quarantined kept apart |
| Will it last? | Divide by hand | Days of cover, against lead time |
| Why doesn't it match? | A mystery | Every movement, with its reason |
Stock by site and lot, holds, counts, adjustments with reasons and the reorder list are on every plan, from $49 a month. The first 30 days are free, with no credit card. If you'd like a hand setting up, ask, and we'll do it with you.