Stock Control Spreadsheet: UK Template and Formulas
By mise · Updated 7 October 2026
Quick answer: a stock control spreadsheet lists each line with unit, cost (ex VAT), par, stock count, value (=count×cost) and reorder quantity. Pubs add sales and GP% per line to spot wet-stock losses.
Every stock controller starts with a spreadsheet. It works until the count takes a whole morning.
| Column | Formula / note |
|---|---|
| Line, supplier, unit | Text |
| Cost ex VAT | Latest invoice |
| Par | Target stock |
| Stock count | At the stock take |
| Stock value | =Count × Cost |
| Reorder | =MAX(0, Par − Count) |
| GP% | =(Net price − Cost) ÷ Net price |
Build it step by step
- Headers in row 1: A Line, B Supplier, C Unit, D Cost ex VAT, E Par, F Stock count, G Stock value, H Reorder.
- One row per line, grouped by area (cellar, back bar, kitchen, dry store) in shelf order.
- G2:
=F2*D2; H2:=MAX(0,E2-F2). Copy both down. - Total stock value at the bottom of column G.
- Copy the tab for each stock take, so you can work out usage between two dates.
Adding usage and GP
| Line | Opening | Deliveries | Closing | Usage | Cost ex VAT | Usage at cost |
|---|---|---|---|---|---|---|
| Gin 70 cl | 4.2 | 6 | 5.1 | 5.1 | £16.00 | £81.60 |
| House white 75 cl | 14 | 24 | 19 | 19 | £6.20 | £117.80 |
| Beef mince (kg) | 8 | 20 | 6.5 | 21.5 | £7.40 | £159.10 |
Usage = opening + deliveries − closing. Add up usage at cost for the period to get cost of sales. GP% = (net sales ex VAT − cost of sales) ÷ net sales ex VAT. For the GP of a single serve, use the GP calculator.
Keeping the spreadsheet honest
- Costs ex VAT from the latest invoice, updated when prices change.
- Same units every stock take (bottles and tenths, kg, cases).
- Deliveries entered before the stock take, not after.
- Formula columns protected so nobody types over them.
Ready-made: stock take template (Excel and printable). Doing it on a phone: stock control app and AI stock control.
Spreadsheet or app for a UK pub or restaurant?
| Spreadsheet | Stock control app | |
|---|---|---|
| Cost | Free | Monthly subscription |
| Counting | Read labels, find the row, type | Scan and type the quantity |
| Open bottles and kegs | Typed tenths | Tenths or a Bluetooth scale |
| Several people counting | Awkward | Same count on several phones |
| Deliveries | Typed from the invoice | From a photo or file of the invoice |
| Reports | Your own formulas | Usage, variance and GP built in |
For a few dozen lines a spreadsheet does the job. Once the stock take takes a morning, or the figures are needed every week, scanning saves the time, and the counts can still be exported to Excel for the stocktaker or the accountant.
Scan the barcode or snap a photo: mise's AI finds the product and categorises it. Free trial.
Start free
Frequently asked questions
Should costs include VAT?
No: use cost ex VAT for stock value and GP.
How often should I stock take?
Weekly for wet stock, monthly for food.
Can I export from mise to my spreadsheet?
Yes, to Excel.
Should the spreadsheet include VAT?
Keep costs ex VAT and calculate GP on net sales ex VAT; mixing VAT-inclusive and exclusive figures overstates GP.
How many lines can a spreadsheet handle?
Hundreds of lines are fine for Excel; the limit is the time it takes to count and type them, not the software.
What is the difference between stock control and a stock take?
The stock take is the count itself; stock control is the routine around it: ordering to par, recording deliveries and wastage, and comparing usage with sales.
Can I try mise for free?
Yes. You get a free trial, with no card needed.