Restaurant Inventory Template: A Free Spreadsheet Layout for F&B

Most F&B owners start inventory in Excel or Google Sheets, and that is a sensible place to start. The problem is rarely the software. It is the layout. A sheet that mixes item names, counts, prices and orders on one tab breaks the first time a supplier changes a pack size. This guide gives you a restaurant inventory template you can copy column by column, explains how each tab connects, and shows where a spreadsheet stops being enough.

It is the master list, not the counting process. For how to run a count, value stock and chase variance, read our restaurant stock take guide, which includes a count sheet. For how to work out how much of each item to hold, see par levels for restaurant inventory. All figures below are hypothetical examples.

What a restaurant inventory template should do

A good template answers four questions quickly:

1. What do we stock? One row per item you buy, with a unique code.
2. What is it worth? Unit cost multiplied by quantity on hand.
3. What do we need to order? Quantity on hand compared with par and reorder point.
4. Where did it go? Opening stock plus purchases minus closing stock, compared with what your sales say you should have used.

If your current sheet cannot answer those in a few minutes, the layout is the issue. The fix is to separate reference data (the item list) from transactions (counts, deliveries and orders).

The recommended tab structure

Build the workbook with five tabs. Every tab except the first looks up the item list by item code, so you never retype a name or price.

Tab	Purpose	Updated
Items (master list)	One row per stock item with units, supplier, cost, par and reorder point	When an item, pack size or price changes
Suppliers	Supplier name, contact, order cut-off, delivery days, lead time, payment terms	When terms change
Counts	One column per count date, quantity in count units	Every count (daily, weekly or monthly)
Orders and deliveries	What was ordered, what arrived, invoice price, any shortfall	Every order and delivery
Recipes	Ingredient quantities per menu item, linked to item cost	When a recipe or price changes

The recipes tab can be as simple or detailed as you like. Our recipe costing template covers the columns for yield, sub-recipes and plate cost.

The master inventory sheet: columns to copy

This is the heart of the template. Copy these headers into row 1 of the Items tab.

Column	What goes in it	Example (hypothetical)
Item code	Short unique code. Never reuse a code.	DRY-012
Item name	Name as your team says it, plus brand if it matters	Jasmine rice 25kg
Category	Dry, chilled, frozen, beverage, alcohol, packaging, cleaning	Dry
Storage area	Where it lives, in count order	Dry store shelf B
Purchase unit	How the supplier sells it	Bag
Pack size	Count units per purchase unit	25
Count unit	The unit staff count and recipes use	kg
Supplier	Main supplier (matches the Suppliers tab)	Supplier A
Purchase price	Latest invoice price per purchase unit, before GST	$50.00
Unit cost	Formula: purchase price / pack size	$2.00 per kg
Average weekly usage	From the last four to eight weeks of counts	40 kg
Par	Target quantity after a delivery	50 kg
Reorder point	Quantity that triggers an order	20 kg
On hand	Lookup from the latest count	18 kg
Order flag	Formula: on hand at or below reorder point	ORDER
Suggested order	Formula: (par minus on hand) / pack size, rounded up	2 bags
Stock value	Formula: on hand x unit cost	$36.00
Active	Yes/No, so discontinued items drop off count sheets	Yes

Keep purchase unit and count unit separate. This is the most common reason restaurant stock management in Excel goes wrong: the supplier invoices by the carton, staff count by the bottle, and recipes use millilitres. With pack size in its own column, the sheet converts for you.

Formulas that make the template work

You only need a handful of formulas. Written for Excel or Google Sheets, with the Items tab laid out as above:

• Unit cost: =purchase_price / pack_size. Update the purchase price from each invoice, and the unit cost follows.
• On hand: use XLOOKUP (Excel) or INDEX/MATCH to pull the latest count for the item code from the Counts tab.
• Order flag: =IF(on_hand<=reorder_point,”ORDER”,””). Use conditional formatting to shade flagged rows.
• Suggested order in purchase units: =IF(order_flag=”ORDER”,ROUNDUP((par-on_hand)/pack_size,0),0).
• Stock value: =on_hand*unit_cost, summed by category for your month-end figure.
• Actual usage for a period: =opening_count + deliveries – closing_count, with deliveries summed from the Orders tab using SUMIFS on item code and date range.

A simple starting point for the reorder point is average daily usage multiplied by supplier lead time in days, plus a safety buffer. The par levels guide explains how to set and adjust both numbers for weekends, festivals and perishables.

A worked example: five rows from a café (hypothetical)

Here is how the master list looks once it is filled in. Prices and quantities are invented for illustration only.

Code	Item	Pack	Unit cost	Weekly use	Par	Reorder	On hand	Order
BEV-001	Fresh milk 1L	12 x 1L	$2.50/L	60 L	30 L	15 L	12 L	2 cartons
BEV-004	Espresso beans 1kg	1 kg	$30.00/kg	8 kg	6 kg	3 kg	4 kg	–
CHL-010	Butter 250g	20 x 250g	$3.00/block	25 blocks	20 blocks	10 blocks	9 blocks	1 carton
DRY-020	Plain flour 25kg	25 kg	$1.40/kg	30 kg	40 kg	15 kg	22 kg	–
PKG-003	12oz takeaway cup	1,000 pcs	$0.08/pc	700 pcs	1,000 pcs	400 pcs	350 pcs	1 carton

Notice that packaging sits on the same list as food. Cups, lids, bags and cutlery are real costs, and they run out at the worst moment if nobody counts them.

How the template connects to counts, orders and recipe costing

Counts. Sort the Items tab by storage area, then print or share a count sheet that shows item code, name and count unit only. Staff fill in quantities; you paste them into a new dated column on the Counts tab. The master list picks up the latest figure. The stock take guide covers how often to count and how to handle opened packs.

Orders. Filter the Items tab on the order flag and group by supplier. That gives you a draft order per supplier, in purchase units. Log what you ordered and what arrived on the Orders tab, including short deliveries and price changes. If you want a more formal flow with approvals and goods receiving, see our guide to a purchase order system for restaurants.

Recipe costing. Each recipe line looks up the unit cost from the master list. When the rice price changes on the Items tab, every dish that uses rice updates. That link is what turns a stock list into a margin tool.

Variance. Compare actual usage (from counts and deliveries) with theoretical usage (items sold multiplied by recipe quantities). A gap points to waste, over-portioning, unrecorded staff meals or theft. Theoretical usage needs sales by menu item, which is where your POS reports come in.

Restaurant inventory management in Excel: rules that keep it accurate

• One owner. Only one person edits the Items tab. Everyone else enters counts and deliveries.
• Codes, not names, in formulas. Names get retyped with different spellings; codes do not.
• Prices from invoices. Update purchase prices from the invoice, not the quote, and record the date.
• Protect formula columns. Lock unit cost, order flag and stock value so they are not overwritten.
• Count in the same order every time. Match the sheet order to the shelves.
• Archive, don’t delete. Set Active to No for discontinued items so history stays intact.
• Version control. Save a dated copy at every month-end before you start the next period.

When a spreadsheet stops scaling

A spreadsheet template is fine for one outlet with a stable menu and a disciplined manager. It starts to strain when:

• You run two or more outlets and need to compare usage or move stock between them.
• Supplier prices change often, so recipe costs are always out of date.
• Your menu has many modifiers, sets and add-ons, so theoretical usage is hard to calculate by hand.
• Counts are late because the person who maintains the sheet is on leave.
• You want to know stock levels during service, not after the next count.

At that point, recipe-level stock deduction in a POS takes over the arithmetic. Each sale deducts ingredients according to the recipe, so theoretical stock is always current and variance shows up as soon as you count. ChaChaCha offers recipe-level stock deduction and recipe costing per menu item as part of its inventory management. For other inventory features, such as purchase orders, transfers, unit conversion or wastage logging, ask us to confirm what fits your set-up, and get it in writing from each vendor you compare. Our round-up of the best restaurant inventory software in Singapore covers what to check.

Keeping your spreadsheet layout tidy now makes that move easier later: a clean item list with codes, units and pack sizes is exactly what any system will ask you to import.