Written by: JJ Tan, Founder, Jelly

Key Takeaways

  • A five-tab Excel architecture (Item Master, Stock In, Stock Out, Stocktake, Executive Summary Dashboard) gives single-site UK operators clear visibility on inventory value, food cost percentage and reorder needs.
  • Core formulas such as XLOOKUP, SUMIFS and conditional formatting keep unit costs live, flag low stock and calculate accurate food cost percentages based on net sales.
  • The 80/20 rule focuses weekly counts on the 20% of SKUs that typically represent 80% of inventory value, which reduces time spent on low-value items.
  • Excel dashboards stop scaling when invoice prices drift, kitchen teams skip daily logging, variances go uninvestigated or a second site appears.
  • Jelly removes these ceilings by scanning every invoice line automatically, keeping dish costs and GP margins live and onboarding UK restaurants in the first week at a flat £129/month per location.

Talk To Jelly About Replacing Your Spreadsheets

What Is a Restaurant Inventory Dashboard?

A restaurant inventory dashboard in Excel is a single summary view that pulls stock levels, usage, waste, stocktake counts and food cost percentage from linked tabs. It lets a UK operator see total inventory value and reorder needs without adding up sheets by hand. The dashboard acts as the control panel that sits on top of your raw data tabs and turns them into decisions.

The Five Tabs Your Restaurant Excel Dashboard Needs

Every working restaurant Excel dashboard inventory build uses the same underlying architecture. Each tab has a defined job and they work together as one system. The diagram below shows how the four data tabs feed the Executive Summary Dashboard.

restaurant Excel dashboard inventory five-tab architecture diagram

  1. Item Master — The single source of truth for every SKU you stock. Columns: SKU, item name, supplier, pack size, unit, unit cost (net of VAT), par level, reorder point. Every other tab looks up from here using XLOOKUP. If a unit cost is wrong here, it is wrong everywhere.
  2. Stock In — A log of every delivery received. Columns: date, supplier, invoice number, item (SKU), quantity received, unit price on invoice, VAT flag. This tab is where invoice price drift becomes visible. If the unit price on the invoice differs from the Item Master, you see it immediately.
  3. Stock Out / Usage / Waste — A daily record of everything that leaves stock. Columns: date, item (SKU), quantity, reason (usage, waste, spoilage, staff food, breakage). Reason codes matter because they separate controllable loss from uncontrollable loss and make variance investigations possible.
  4. Stocktake / Count Sheet — The physical count record. Columns: item (SKU), counted quantity, system quantity (calculated from Stock In minus Stock Out since last count), variance (counted minus system), variance value (£). This tab is where theoretical numbers meet actual counts.
  5. Executive Summary Dashboard — The summary view built from PivotTables and slicers pulling from the four data tabs. This is the tab you open on Monday morning to see total inventory value, food cost percentage, low-stock alerts, top variances and waste cost.

The Excel Formulas That Make The Restaurant Inventory Dashboard Work

These formulas are written for Excel 365. Paste them into the relevant tab and adjust the table and column names to match your own build.

XLOOKUP — Pull Unit Cost From Item Master Into Stock In

In the Stock In tab, in the Unit Cost column:

=XLOOKUP([@SKU], ItemMaster[SKU], ItemMaster[Unit Cost (Net)], "NOT FOUND")

This keeps the cost column live. If you update the Item Master when a supplier price changes, every Stock In row recalculates. If it returns “NOT FOUND”, the SKU is missing from the Item Master. That result is a data-quality flag rather than a formula error.

SUMIFS — Total Usage Per Item Per Period

Once costs are live, the next job is totalling usage over a period. In the Dashboard tab, to sum usage for a given SKU between two dates:

=SUMIFS(StockOut[Quantity], StockOut[SKU], [@SKU], StockOut[Date], ">&="&StartDate, StockOut[Date], "<="&EndDate)

Use the same pattern to sum waste only by adding a reason-code criterion: StockOut[Reason], "Waste".

Conditional Formatting — Low-Stock Reorder Alert

Select the current stock column in the Item Master. Apply a rule that formats cells where the value is less than or equal to the Reorder Point column. Set the fill to red. Any item at or below its reorder point turns red automatically. No formula is required because this is a built-in conditional formatting rule.

Food Cost Percentage

= Cost of Goods Sold ÷ Net Food Sales × 100

Net food sales is the denominator. HMRC VAT Notice 709/1 confirms that restaurant meals are standard-rated, so VAT must be stripped before calculating GP. A £32.00 gross cover is £26.67 of net sales. Counting VAT as revenue flatters your food cost percentage by several points. See the full Excel food cost tracking guide for a worked example.

VAT-Aware Stock Valuation

= SUM(Quantity × Unit Cost) / 1.2

Use this when your supplier invoices prices inclusive of VAT. HMRC’s standard VAT rate is 20%. Note that most food ingredients are zero-rated under HMRC VAT Notice 701/14, while alcohol and soft drinks carry 20% input VAT. Apply the /1.2 adjustment only to standard-rated lines, not to zero-rated food purchases. Gross profit must always be calculated on net figures.

PivotTables and Slicers For The Dashboard Tab

Insert a PivotTable from each of your four data tabs into the Dashboard tab. Use slicers to filter by supplier, category, date range or reason code. With the formulas and PivotTables in place, the dashboard can surface the handful of numbers that actually drive weekly decisions.

Download the free UK stocktake spreadsheet template to get started with a pre-built count sheet.

See How Jelly Keeps Your Food Cost Live

The KPIs To Put On The Dashboard

How To Keep Track Of Inventory In a Restaurant Using Excel: 7 Steps

  1. Build the Item Master first. Every SKU you buy goes in here with its supplier, pack size, unit, net unit cost, par level and reorder point. No other tab works without this foundation.
  2. Set par levels and reorder points. Par level is the minimum quantity you want on hand before ordering. Reorder point is the trigger. Base both on actual weekly usage rather than gut feel. Over-ordering perishables creates waste before it happens.
  3. Log every delivery in Stock In on the day it arrives. Check the invoice unit price against the Item Master. If it has moved, update the Item Master. This step prevents invoice price drift from silently eroding your margins.
  4. Record usage and waste daily in Stock Out. Use reason codes such as usage, waste, spoilage and staff food. A waste log completed daily is evidence. One estimated at month end is a guess.
  5. Count weekly in the Stocktake tab. Count at the same time, in the same unit, walking the same storage route. Consistency matters more than frequency because a count done differently each week produces incomparable numbers.
  6. Link the Dashboard tab via PivotTables. Refresh PivotTables after each count. The Executive Summary Dashboard should update in under a minute once the data tabs are current.
  7. Review food cost percentage and variance every week. Compare this week against the previous four. A variance above roughly 5% of purchasing value warrants investigation. See the four common causes covered in the FAQ below.

What Is the 80/20 Rule For Inventory?

The 80/20 rule, or Pareto principle, applied to restaurant inventory means that roughly 20% of stock items typically account for about 80% of purchasing or consumption value. The exact split should be calculated from the venue’s own data. These A items, typically premium proteins, seafood, spirits and wine, get the tightest controls and most frequent counts, while low-value C items like napkins and dry goods can be counted less often without meaningful risk.

To apply it in Excel, add a column for Total Stock Value in the Item Master that multiplies quantity on hand by unit cost. Sort descending by that column. The top 20% of rows by value are your A items. Apply a conditional format with green fill to flag them. Count these weekly or daily and apply looser controls to the long tail.

Industry research suggests UK restaurants lose between 4% and 10% of food spend to waste alone. Focusing count discipline on the top 20% of SKUs by value is where the recoverable margin sits. That discipline is achievable in Excel up to a point.

Can I Use Excel For Restaurant Inventory Management?

Excel works for a single site with a disciplined weekly stocktake routine. A five-tab Excel dashboard gives real visibility on stock value, food cost percentage and reorder needs. Many single-site UK operators run on exactly this setup for years.

The build stops working reliably at four specific points:

  • Invoice price drift. A supplier raises a unit price and nobody updates the Item Master. Every XLOOKUP, every SUMIFS and every food cost calculation runs on a stale number. The dashboard looks fine and is quietly wrong.
  • Chef compliance. The Stock Out tab depends on a busy chef recording usage and waste daily. When service is full, that tab does not get updated and variance accumulates invisibly.
  • Stock variance nobody investigates. The Stocktake tab shows a variance and there is no time to trace it. It rolls forward. By month end, the gap between theoretical and actual is large enough to matter but too old to diagnose.
  • Multi-site version control. A second site means a second spreadsheet. Prices diverge and formats diverge. Comparing food cost across sites then requires manual consolidation every week.

Excel Inventory Dashboard Vs Stock Control Software

Excel is free, flexible and familiar, and it is the right starting point for an operator building stock control discipline for the first time. It stops functioning as a stock control system once invoice prices are moving weekly, the kitchen team is skipping updates or a second site is on the horizon.

For UK operators who have hit any of those four ceilings, Jelly is the right choice. It scans every invoice line automatically, whether it arrives by email or photo. Unit costs update the moment a new invoice lands, so nobody has to edit the Item Master by hand. Because those costs stay current, dish costs and GP margins update in real time as ingredient prices change. The Price Alert feature then flags every supplier price movement and gives operators the evidence to negotiate credits or switch suppliers. The Flash Report delivers a daily or weekly GP view by integrating directly with POS systems including Square, EPOS Now, Lightspeed and Toast, which automates the sales side of the food cost calculation. Xero integration handles the accounts payable side. The flat rate is £129/month per location, whatever the size of your team.

The impact shows up quickly in practice. Amber, a Mediterranean restaurant in East London, saves £3,000–£4,000 per month with Jelly, according to Murat Kilic, chef-owner at Amber. “Jelly keeps my business alive,” he says. The mechanism is straightforward. Invoice automation catches price changes the same week they happen, real-time recipe costing makes the right action obvious and a single system for invoices, pricing and GP removes the spreadsheet drift that erodes margins invisibly.

Talk To Jelly About Automating Your Stock Control

Frequently Asked Questions

What Is The Best Free Excel Template For Restaurant Inventory?

Free templates exist from Microsoft, Etsy sellers and Supy. The common limitations are that they are generic, not built for UK food cost percentage or VAT-aware stock valuation, and they contain no variance reporting between theoretical and actual usage. Etsy templates in particular are typically designed for e-commerce or retail sellers rather than kitchens, and most carry disclaimers that they are record-keeping tools rather than accounting or food-cost systems. The five-tab build described in this article is a more useful starting point for a UK restaurant because it is structured around the specific data a kitchen generates: deliveries, usage, waste, physical counts and food cost percentage against net sales.

How Often Should I Do a Stocktake For My Excel Dashboard?

Count weekly for high-value items such as meat, fish, spirits and wine, and monthly for a full count across all categories. The practical rule is that consistency of time, unit and shelf-walking order matters more than raw frequency. A count done at the same point every week, in the same units, by the same person, produces comparable period-over-period numbers. A count done differently each time produces noise. Daily spot-checks on two or three of your highest-value A items add meaningful control without significant time cost. Monthly-only counting is too slow for problem categories because by the time a monthly count flags an issue, a full month of unexplained loss has already been absorbed.

How Do I Calculate Food Cost Percentage In Excel?

The formula is COGS ÷ net food sales × 100, where net food sales is the denominator with VAT stripped out. Restaurant meals are standard-rated for VAT at 20%, so a £32.00 gross cover is £26.67 of net sales. Using gross, VAT-inclusive sales as the denominator understates food cost percentage and flatters your margin. COGS is opening stock plus purchases in the period minus closing stock, rather than just purchases, which spike when you build stock and improve artificially when you run shelves down. The two-point drift on weekly net sales mentioned earlier compounds to £5,824 over a 13-week quarter.

Why Does My Excel Inventory Dashboard Keep Drifting From Reality?

The four most common causes are invoice price drift, unrecorded waste, portion variance and stale recipe costs. Invoice price drift happens when a supplier changes a unit price and nobody updates the Item Master. Unrecorded waste appears when the Stock Out tab is not completed daily, so spoilage and staff food disappear from the count. Portion variance occurs when dishes go out heavier than the recipe specifies, which shows up as unexplained stock loss. Stale recipe costs arise when a dish was costed months ago and ingredient prices have moved since. The gap between theoretical food cost, which your recipes and sales data predict, and actual food cost, which your purchases and counts show, is where the money leaks. A consistent gap above roughly 5% of purchasing value is a signal that one of these four causes is structural rather than random.

Conclusion: Build It This Week, Then Know When To Stop

The five-tab build described above gives a single-site UK operator real visibility on stock value, food cost percentage and reorder needs. The XLOOKUP pulls live unit costs. The SUMIFS totals usage and waste by period. The conditional formatting flags reorder alerts. The PivotTables turn raw data into a weekly decision view. The 80/20 discipline focuses count effort on the 20% of SKUs that hold 80% of the value.

Build it and run it for a month. Then look honestly at the four failure modes: invoice prices that nobody updated, a Stock Out tab that the kitchen did not fill in, a variance that nobody had time to investigate and the prospect of a second site making the whole thing unmanageable.

Jelly is the best restaurant inventory and food-cost platform for UK operators who have outgrown the Excel dashboard. It removes the invoice drift, the manual logging and the multi-site consolidation problem described above, at the flat rate covered earlier. The Excel build teaches the discipline. Jelly keeps that discipline running once the spreadsheets stop keeping up.

Get Jelly Working In Your Kitchen