GrandTable What You Need in a Restaurant Stock Excel Sheet
If you run a small café, a single-site restaurant or a bar, a restaurant stock management excel workbook is a perfectly reasonable way to start tracking stock. It costs nothing, you already know how to open it, and it makes you learn the numbers that matter before you pay for software. The catch is that a spreadsheet only works when it's built around a routine, not just a list of items.
Before you draw a single column, get to grips with the cycle every stock system follows. Apicbase describes it in four steps: set a par level for each item, count on a fixed schedule in fixed units, log waste and deliveries as they happen, then compare expected usage against what's left. Your sheet needs a home for each step. Miss one, and the whole thing drifts out of date within a fortnight.
In practice, a useful restaurant stock management excel file needs five things:
- An item master list with a unique name, category (meat, fish, dairy, dry goods, spirits), unit of measure, supplier and unit cost in AED.
- Par levels for every item, so the sheet can tell you what to reorder.
- A counting sheet you can print or use on a tablet, with one count column per stock-take date.
- A deliveries log, so purchases are captured with date, quantity and invoice cost.
- A waste log, recording what was thrown away and why.
Settle on units early and never change them. If you buy chicken by the kilo, count it by the kilo. If you buy olive oil in 5-litre tins, decide whether you count tins or litres and stick with it. Mixed units are the most common reason a restaurant stock management excel sheet produces nonsense.
One note: the layout in this article is our own practical guidance, not a standard template taken from a published source. Adapt it to your menu and the way your kitchen actually works.
Building the Sheet: Tabs, Par Levels and Formulas
Keep the restaurant stock management excel workbook simple enough that a shift supervisor can update it without calling you. Five tabs cover most small venues: Items, Deliveries, Counts, Waste and Summary.
On the Items tab, list every stocked ingredient once, with its unit, supplier and current cost per unit. This is your reference list. Every other tab should pull names from it through a drop-down (Data Validation in Excel), so nobody types "tomato", "tomatoes" and "Tomatos" as three different items.
On the Deliveries tab, add one row per delivery line: date, item, quantity received and cost. A SUMIFS formula on the Summary tab then totals purchases per item for any period. On the Counts tab, put the item list down the side and one column per count date. The count at the start of a period is your beginning inventory; the count at the end is your ending inventory.
The most useful formula in the workbook works out usage for each item:
Usage = beginning count + purchases − ending count − recorded waste
If the sheet says you used 14 kg of chicken thighs and your sales say you should have used 11 kg, that gap is worth chasing.
Setting par levels
A par level is the smallest quantity you need on hand to reach the next delivery without running out or over-ordering. Start with average usage per day, multiply by the days between deliveries, then add a buffer for lead time and busy weeks. In a UAE venue, your busy weeks may be driven by school holidays, Ramadan evenings, long weekends and exhibition seasons, so review par levels each season rather than setting them once.
Add a "reorder" column with a simple IF formula: if the latest count is below par, show "Order", otherwise leave it blank. Then add a "suggested quantity" column that subtracts the current count from par. Conditional formatting that turns the row red for items below par makes the sheet scannable in seconds.
For fragile or short-shelf-life items such as herbs, dairy and fresh fish, G2 recommends first-in, first-out: older stock is used before newer stock. Add an "expiry" or "received date" column for those lines so whoever is counting can see what needs to move first.
Calculating Food Cost and Variance in AED
Your Summary tab should turn the counts into a food cost percentage. The standard COGS method, as Orbisk sets it out, is:
Food cost % = (beginning inventory + purchases − ending inventory) ÷ food sales × 100
To value the inventory, multiply each item's count by its unit cost in AED on the Items tab and add up the column. Do this for the opening count and the closing count. Take total purchases from the Deliveries tab and food sales from your POS report for the same dates.
Here's a worked illustration with made-up round numbers in AED:
- Beginning inventory: AED 18,000
- Purchases during the month: AED 62,000
- Ending inventory: AED 16,000
- Food sales (excluding VAT): AED 190,000
Cost of food used is 18,000 + 62,000 − 16,000 = AED 64,000, and 64,000 ÷ 190,000 gives a food cost of 33.7%. That's an illustration of the method, not a benchmark for your venue.
Two details trip people up. First, use sales before VAT, because VAT is collected on behalf of the tax authority and isn't your revenue. Qoyod's Gulf-region worked example makes the same point by using net sales before VAT. Second, keep beverage purchases out of the food percentage. Restaurant Velocity lists skipping the physical count and mixing beverage purchases into food cost as two of the most common errors in this calculation.
Actual versus theoretical
Actual food cost tells you what you spent. Theoretical food cost tells you what you should have spent, based on standardised recipe costs and what you actually sold. The difference is your variance. To build theoretical cost in Excel, add a Recipes tab listing each dish's ingredients and quantities, cost each dish from the Items tab, then multiply by units sold from your sales report.
Sources disagree on what a healthy gap looks like, so treat any figure as a rule of thumb. StockCount says a gap under 2–3% is normal, 3–5% needs attention, and 5% or more points to waste, over-portioning or theft. Tableview suggests the two should match within about 1.5 percentage points. Qoyod's example shows a 34.1% actual against a 31.2% theoretical, a 2.9-point gap. Pick a tolerance that suits your concept, and treat a gap that keeps growing as the signal to investigate.
If you want to go deeper on the percentage itself, this guide to restaurant food cost percentage covers the formula and common mistakes in more detail.
Tracking Waste and Counting on a Schedule
A stock sheet is only as honest as its counting routine. Choose a fixed schedule and keep to it. Apicbase advises counting high-value lines such as meat, fish and spirits more often than dry goods. A sensible pattern for many venues is a quick weekly count of expensive proteins and bar stock, with a full count at month-end for everything else. Count at the same time of day, ideally before service or after close, so deliveries and sales don't move the numbers while you count.
Give every item a storage location on the counting sheet, in the order a person walks the store room, walk-in chiller and freezer. That cuts counting time and reduces the chance of missing a shelf. Missed items quietly throw off the whole picture, and so does a stock-take run by two people at once without a clear split.
What a waste log should capture
Waste is the part of the sheet people skip when service gets busy, which is exactly when it matters. Keep the log short enough that people will actually fill it in. Useful columns are:
- Date and time
- Item and quantity (in the same unit as your count)
- Reason: spoilage, prep trim, dropped, returned dish, staff meal, over-production
- Who logged it
- Cost in AED, calculated automatically from the Items tab
The reason column is what makes the log worth keeping. After a month you can filter by reason and see whether your losses come from over-ordering, poor rotation or over-portioning, then fix the cause instead of guessing. Food waste is a national concern in the UAE too. The ne'ma initiative cites figures of nearly 40% of food wasted and a target of a 50% reduction by 2030, though those are household and national numbers rather than restaurant benchmarks. Your own log is a far better measure of your venue.
When Excel Stops Being Enough and an App Makes Sense
A restaurant stock management excel file is a good teacher and a poor long-term engine. It works while you have one site, a manageable number of items, and one or two people who own the file. It starts to strain when any of these happen:
- Manual sales entry. A spreadsheet doesn't know what you sold. Someone has to copy sales figures in, then work out theoretical usage. A POS-linked system does this step for you: each sale triggers a recipe-based deduction, so expected stock updates continuously. That's our own explanation of the mechanism, and it's the biggest time difference between the two approaches.
- Several people editing. Shared files get overwritten, formulas get broken, and nobody trusts the numbers.
- More than one outlet or kitchen. Consolidating separate workbooks is slow and error-prone.
- Frequent price changes. Updating unit costs across the Items and Recipes tabs by hand means dish costs go stale quickly.
- Counting takes too long. If a full stock-take eats half a day, you'll do it less often, and the data decays.
None of the sources we reviewed quantify how much time or error a switch saves, so we won't claim a figure. The honest test is simpler. If you're updating the sheet more than you're using it to make decisions, it has stopped paying for itself.
When that point arrives, look for a system that deducts ingredients automatically as items sell, tracks waste, flags low stock and ties into your recipes. Features worth having include an auto-86 function that marks a dish unavailable when its components run out, as TheRestaurantHQ notes. You can read more about what to expect in our restaurant stock management system guide for the UAE, and if cost is your worry, our piece on what free POS software for restaurants really includes is worth a look first.
GrandtablePOS builds stock management, recipe costing and live profit reporting into the same system you take orders on, priced at AED 75 per device per month and running on devices you already own. You can start a free 7-day demo, with no card required, and bring your own item list to see how it compares with your spreadsheet. If you'd rather talk it through first, contact the team.