Restaurant inventory in Excel: a movement ledger with checkable balances

Use a ledger, not an overwritten stock number

A restaurant inventory workbook needs to explain why stock changed. Keep the opening count, individual movements, expected closing balance, and physical closing count separate. If a person changes “cheese: 12” directly to “cheese: 10,” the sheet loses the receipt, recipe consumption, and waste that explain the difference.

This example uses one count period, two storage locations, kilograms of cheese, and units of bottled drinks. It is an illustrative workbook design, not a restaurant's results or a recipe recommendation. Keep one base unit per SKU and one opening count per location. For the broader process behind the sheet, use the restaurant inventory control guide.

Create the Items sheet

Create a workbook with sheets named Items and Moves, exactly as written. Copy the tab-separated block below into cell A1 of Items. Each tab must create a separate column; use Excel's Text to Columns with a tab delimiter if the paste lands in one column. Save your workbook as .xlsx.

Store	SKU	Item	Unit	Opening	Counted	Expected	Variance
MAIN	CHEESE	Cheese	kg	12	10		
PREP	CHEESE	Cheese	kg	5	4		
MAIN	BOTTLE	Bottled drink	unit	24	16		

The counted quantities are observations at the close of the period, not formulas. Leave Expected and Variance empty until you enter the formulas below. MAIN and PREP are location codes; CHEESE and BOTTLE are item identifiers, not names that should change when a label is edited.

Record movements in base units

Paste this block into A1 of Moves. In and Out are always positive quantities; the column determines their direction. Every row has a unique ID, a reason, a reference, and an operator.

ID	Date	Store	SKU	Type	In	Out	Reference	Operator
M01	2026-10-21	MAIN	CHEESE	RECEIPT	8	0	PO01	Ana
M02	2026-10-21	PREP	CHEESE	TRANSFER_OUT	0	2	T01	Ana
M03	2026-10-21	MAIN	CHEESE	TRANSFER_IN	2	0	T01	Ana
M04	2026-10-21	MAIN	CHEESE	TRANSFER_OUT	0	1	T02	Luis
M05	2026-10-21	PREP	CHEESE	TRANSFER_IN	1	0	T02	Luis
M06	2026-10-21	MAIN	CHEESE	RECIPE_USE	0	10	SALES01	Luis
M07	2026-10-21	MAIN	CHEESE	WASTE	0	0.5	WASTE01	Luis
M08	2026-10-21	MAIN	BOTTLE	RECEIPT	12	0	PO02	Ana
M09	2026-10-21	MAIN	BOTTLE	SALE	0	20	SALES02	Luis

A 2 kg transfer has one outgoing and one incoming line with reference T01. The reverse 1 kg transfer uses T02. A supplier receipt is recorded from the quantity accepted, not automatically from the invoice. If an ingredient arrives in cases, convert it to kilograms before entering it here. Do not mix grams, kilograms, and cases in the same quantity column.

Calculate expected stock and variance

In Items!G2, enter:

=E2+SUMIFS(Moves!$F$2:$F$2000,Moves!$C$2:$C$2000,$A2,Moves!$D$2:$D$2000,$B2)-SUMIFS(Moves!$G$2:$G$2000,Moves!$C$2:$C$2000,$A2,Moves!$D$2:$D$2000,$B2)

In Items!H2, enter:

=F2-G2

Fill G2:H2 down through row 4. The SUMIFS criteria match both location and SKU, so cheese at PREP does not get added to MAIN. The Microsoft SUMIFS reference explains the multiple-criteria syntax.

The example expects 10.5 kg, 4 kg, and 16 units in G2:G4. The corresponding variances are −0.5 kg, 0 kg, and 0 units. For MAIN cheese, check the arithmetic independently: 12 + 8 + 2 − 1 − 10 − 0.5 = 10.5. The physical count of 10 leaves an unexplained 0.5 kg shortage.

This version covers rows 2–2000 and one period. Expand every SUMIFS range together before exceeding that limit. If you combine periods, add date criteria and define the opening date; otherwise historical movements will be added again.

Keep the sheet reliable after the first count

Protect formula cells and give editors access to input columns only. Use fixed location and SKU lists, reject negative input quantities, and flag duplicate movement IDs. The date column should use actual dates if you later filter or aggregate by period. Confirm decimal and list separators in your Excel installation; a quantity stored as text can be ignored by a sum.

Count at a defined cutoff. Recount an unusual variance and inspect receiving, transfer pairs, recipe use, and waste before approving an adjustment. Record the correction as a traceable movement with its reason, not by overwriting history to force zero variance.

A refunded prepared meal may still have consumed ingredients. Record the money reversal separately from a physical stock return. Likewise, the 10 kg recipe-use line must already include the recipe quantities you intend to subtract; do not subtract both finished dishes and their ingredients again.

Use Excel for control, and a separate rule for online availability

A workbook helps a small team establish units, movements, and count discipline. It becomes fragile when several locations edit at once, recipes change frequently, or purchasing and waste need approval workflows. At that point, evaluate an inventory system using the same ledger and exception examples, rather than abandoning the rules the spreadsheet made explicit.

Ordering.co's availability controls manage product stock and what customers can order. Ingredient recipes, purchasing, costing, and the formulas in this workbook are a different layer. Do not assume that selling one configured menu item automatically posts every ingredient movement from your Excel file.

Define which system owns each number and how an approved count changes sellable availability. A traceable inventory process is more valuable than a stock total that updates quickly but cannot be explained.