Quick answer
Count everything at the same point each period, ideally after closing and before the next delivery, in the same order every time. Record full units and part units separately, value stock at what you paid before VAT, then work out usage: opening stock plus deliveries minus closing stock. Compare that with what your sales say you should have used.
Free editable template · CSV
Columns for storage area, item, unit, full and part units, cost price, opening stock and deliveries, with a labelled coffee bean example and blank rows for your own products.
Download stocktake sheetOpen in Excel, Google Sheets or Numbers, then adapt the sample rows to your shop. No sign-up needed. Template instructions · Browse all free templates.
Use Unpickle instead of separate templates for your daily routines. Keep daily checks, stock counts, delivery records and staff procedures together in one app your whole team can use. Try Unpickle free.
A stocktake answers two questions: how much stock is sitting in the building, and how much you used since the last one. The first matters for your accounts. The second is the one that saves money, because the gap between what you used and what you sold is waste, mistakes and things walking out of the door.
Stocktake or stock count?
A full stocktake covers everything and is usually done monthly, or at least at your financial year end. A short count of the ten or fifteen items that run out most often is a different job, done weekly before ordering. If that’s what you need, read how to stop running out of stock first. This guide is about the full count.
1. Pick a fixed time
Count at the same point every period, so the numbers are comparable. After closing on the last day of the month works well: nothing is being used and nothing is arriving. If a delivery does arrive during the count, put it to one side and record it separately so it isn’t counted twice.
2. Set up the sheet in the order of the shelves
List items in the order you walk round: dry store left to right, then the walk-in, then behind the bar. A sheet in alphabetical order means walking back and forth and missing things. Give every item one counting unit (bottle, kilogram, case) and stick to it. If you buy oat milk by the case but count it in bottles, write the case size on the sheet so the maths is easy later.
3. Count in pairs
One person counts and calls out, the other writes. It is faster and far more accurate than one person juggling a clipboard. Count what is actually there, not what the label on the shelf says should be there.
4. Agree how to count part-used items
Opened bags of beans, half bottles of syrup and part-used blocks of cheese are where stocktakes drift. Agree a simple rule and use it every time: weigh open bags of coffee, and count open bottles to the nearest tenth or quarter. Keep full and part units in separate columns so you can see both.
5. Leave out what can’t be sold
Damaged, out-of-date or spoiled stock isn’t stock any more. Take it off the shelf, record it as waste with a reason, and don’t include it in the count. Your waste record is useful on its own: if the same item keeps going out of date, you’re ordering too much of it.
6. Value it and work out what you used
Value each line at the price you paid, before VAT, from the most recent invoice. Then for each item:
Usage = opening stock + deliveries − closing stock
The same sum in pounds across every item gives your cost of sales for the period, which is what your accountant needs to work out gross profit.
Worked example: coffee beans for one month
This is an illustrative calculation, not a customer result.
| Step | Calculation | Result |
|---|---|---|
| Used, by stocktake | 12 kg opening + 30 kg delivered − 9 kg closing | 33 kg |
| Expected, from sales | 1,700 coffees × 18 g per double shot | 30.6 kg |
| Unexplained | 33 kg − 30.6 kg | 2.4 kg (about 7%) |
Some gap is normal: dialling in the grinder each morning, the odd remade drink, staff coffees. What matters is the trend. If the gap is 3% one month and 10% the next, something has changed, and you now know where to look. Use your own dose and your till’s drink counts for the expected figure.
7. Look for the surprises
Don’t just file the sheet. Spend ten minutes on the three or four lines with the biggest gap, the items with lots of waste, and anything you’re holding far more of than you use in a month. Those are the conversations that pay for the hour the stocktake took.
How Unpickle helps
In Unpickle, a count list is a set of products with a line for each. It can repeat daily, weekly or monthly, or be kept for whenever you need it. Staff count on a phone or the shop tablet, with the option of separate unopened and open quantities and a photo for each line. Every line needs a number before the count can be submitted, anything at or under its minimum goes onto the shopping list, and managers can correct a figure with a reason. The product list builds itself from your deliveries.
Unpickle doesn’t value stock or calculate cost of sales, so use the free sheet above for the monthly valuation and Unpickle for the counting routine your team does every week.
See how stock fits into Unpickle’s café management software, and check deliveries properly with the delivery checking guide so the “deliveries” figure in your stocktake is right.
Try it free for 30 days, no card required.