Free stocktake template for cafes and restaurants
An Excel stocktake sheet that works out expected stock, shows the variance in dollars and highlights the items that do not add up. Works in Google Sheets too.
.xlsx, free, no email needed
What is in the sheet
One row per item. You type in the white columns, and the sheet works out the rest.
- Opening
- What you counted last time
- Received
- Deliveries since then
- Used or sold
- From your POS or recipes
- Expected
- Opening + received - used
- Counted
- What is on the shelf now
- Variance $
- The gap, in dollars
It also totals your stock value excluding GST and your total variance, so you have the two numbers your accountant and your ordering both need.
How to do a stocktake in five steps
A stocktake only helps if the count is compared with what should be there. Counting alone tells you what you have. Comparing it with expected stock tells you what you lost.
- 1
List every item you buy
Write down each ingredient and consumable with its unit (L, kg, each) and what you pay per unit, excluding GST.
Tip: Group items by where they sit, so the count follows your walk through the kitchen.
- 2
Work out what should be there
Expected stock is what you counted last time, plus deliveries, minus what you used or sold. The template does the sum.
Tip: Pull "used or sold" from your POS sales report, not from memory.
- 3
Count the same way every time
Count the fridge, freezer, dry store and bar in the same order, and count opened packs as part units.
Tip: Two people count faster and catch more mistakes: one counts, one writes.
- 4
Check the variance
Variance is counted minus expected. Rows turn amber when the gap is above your threshold (15% by default).
Tip: Look at the biggest dollar gaps first, not the biggest percentages.
- 5
Find the cause, then fix it
Missing stock usually means unrecorded waste, over-portioning, a missed delivery or a wrong recipe. Write the cause in the notes column.
Tip: Repeat gaps on the same item point to a process problem, not bad luck.
What to count, and how often
- Weekly: coffee beans, milk and alternatives, proteins, produce with a short life, and spirits. These are the items where a gap costs the most.
- Monthly: dry store, packaging, cleaning supplies and everything else on the sheet.
- At 30 June: a full count, so you have a closing stock value for the year. Ask your accountant what they need.
Stop working out expected stock by hand
In a spreadsheet you type in what you sold. In Previo it comes from Square. When your team counts, Previo compares the number with sales, deliveries and logged waste, and flags the items that do not add up, for example 5 bottles unaccounted for.
Stocktake questions
How often should a cafe or restaurant do a stocktake?
Count high-cost and perishable items (coffee, dairy, proteins, spirits) weekly, and do a full count monthly. Weekly counts of the expensive items catch losses while you can still trace them.
Should stock be valued including or excluding GST?
If your business is registered for GST, you generally claim GST credits on purchases, so stock is recorded at cost excluding GST. The template uses ex-GST costs. Confirm the treatment for your business with your accountant.
What is stock variance?
Variance is the difference between what you counted and what you should have, based on opening stock, deliveries and sales. A negative variance means stock has gone somewhere it was not recorded.
Does the template work in Google Sheets?
Yes. Open it in Excel, or upload it to Google Drive and open it with Google Sheets. The formulas and the amber highlighting carry across.
What is a good variance percentage for a cafe?
There is no single number. A small gap on a bulk item like milk can be a few percent from spillage and measuring. A large or repeated gap on one item deserves a closer look. The template flags anything above 15% so you can start there, and you can change the threshold.
Can Previo do the stocktake for me?
Previo compares each count with the stock your Square sales, deliveries and logged waste say should be there, and flags losses above 15% of normal daily use. You still count the shelf, but the expected figure and the maths are done for you.
More guides: stock rotation and food waste · kitchen prep lists · savings calculator