Free stock take template for kitchens (Excel and Google Sheets)

A kitchen stock take template records what you had at the start of a period, what you bought and what is left at the end, then values the difference at unit cost to show what you actually used. This free one does that for up to 40 items in Excel or Google Sheets, and turns it into your actual food cost % and GP once you add food sales for the same days, ex-VAT.

Enter each item in the unit you buy it in (kilos, litres, each or cases) with the cost of one unit. Usage and usage value fill in as you type, and the totals are in pounds, so a sheet with kilos, litres and cases on it still adds up. Ten UK kitchen lines are filled in as a worked example to type over.

Worked example: one week, ten lines

The example in the file is a single week. Usage is opening + purchases − closing, and usage value is usage × unit cost:

In pounds: opening stock £310.45 + purchases £563.65 − closing stock £235.58 = £638.52 of food used. With food sales of £1,980 ex-VAT for the same week:

• Actual food cost: £638.52 ÷ £1,980 = 32.2%, so GP is 67.8%.
• Against a 30% target: 2.2 percentage points over, which is £44.52 for the week.

32.2% sits inside the 30–35% band most UK kitchens work to, so on its own it looks fine. But this kitchen set itself 30%, and if the same gap repeats every week it is roughly £2,300 a year. That is the point of counting: a percentage that looks fine can still be money you meant to keep.

How to fill it in

1. Match the stock period to the sales period. Count opening stock on the day your sales period starts and closing stock on the day it ends. Stock and sales must cover exactly the same days, or the percentage means nothing.
2. List items in the unit you buy. Kilos, litres, each or cases, with the ex-VAT cost of one unit from your latest invoice. Mixed units are fine because the totals are in pounds.
3. Enter opening stock. Last period's closing count is this period's opening count, so after the first week you only count once.
4. Add purchases from invoices and delivery notes for the same days, net of any credits for returned or short-delivered goods.
5. Count closing stock shelf by shelf. Use the same route round the stores every time, count opened packs as fractions (half a 1 kg bag is 0.5), and ideally have one person count while another writes.
6. Enter food sales ex-VAT. UK gross profit is measured on ex-VAT sales, so divide a VAT-inclusive figure by 1.2 first. If you did not count drinks stock, leave drinks sales out too.

When the number is higher than it should be

A stock take tells you what you used; it does not tell you why. When actual food cost runs above target, the usual causes are waste (out-of-date stock, over-prep, dropped plates), portions creeping above spec, staff food that is not recorded, supplier price rises that never reached the menu price, and counting mistakes. Comparing what you used against what your sales say you should have used is how you narrow it down: see actual vs theoretical food cost and how to calculate ideal vs actual food cost variance. If waste is the leak, how to reduce food waste in a restaurant kitchen covers tracking it.

Counting habits that keep it honest

Value every item at the same kind of price every time, ideally the latest invoice price ex-VAT. Decide once how you treat prepped items (count them at their ingredient cost, or leave them out) and stick to it, because switching between periods moves the percentage without anything real changing. Do not count goods that have been invoiced but not delivered. And count at the same point in the day, before deliveries arrive, so a morning drop is not counted twice. The full walkthrough is in how to do a food stock take and calculate your actual food cost.

Questions people ask

What is the stock take formula?

Usage = opening stock + purchases − closing stock, valued at unit cost. Actual food cost % = usage value ÷ food sales ex-VAT, and GP % = 100 − food cost %.

How often should a kitchen do a stock take?

Weekly gives you the tightest control and the fastest warning. Monthly is the minimum and lines up with most accounts. Whatever you choose, keep the period the same so one result can be compared with the next.

Should food sales include VAT?

No. UK GP is measured on ex-VAT sales. Divide a VAT-inclusive takings figure by 1.2 for standard-rated food before you enter it.

What unit cost should I use?

The ex-VAT price of one unit as you buy it, usually from the latest invoice. Consistency matters more than method.

Does it work in Google Sheets?

Yes. Upload the .xlsx to Google Drive and open it with Google Sheets; the formulas carry over. It also opens in Numbers and LibreOffice.

How is it different from the paid stock-take tracker?

This free sheet counts item by item. The Food Stock-Take & Actual Food Cost Tracker (£11.99) counts by category in pounds, which is faster, and adds the actual vs theoretical gap and a how-to sheet.

When you outgrow it

The free food cost calculator checks a single dish when you don't want to count the whole store. When you would rather count by category in pounds than item by item, the Food Stock-Take & Actual Food Cost Tracker (£11.99) runs the same maths on a shorter list, with a how-to sheet.