Expert guide
Restaurant Inventory Par Levels in Excel
Order what you will use before the next delivery, not what the chef remembers running out of
Call us at +1 (385) 386-3860
Short answer: list every stocked item with unit, supplier, pack size and delivery days; record weekly counts and purchases; calculate usage as opening + purchases − closing; set par = average daily usage × (days until next delivery + lead time) + safety stock; and order =MAX(0, par − on hand) rounded up to pack size. Group the order sheet by supplier. Link it to your recipe costing spreadsheet to compare actual and theoretical food cost.
In most kitchens, ordering is done from memory and fear. The chef remembers the Saturday the salmon ran out and orders extra every week; the dairy over-order goes in the bin on Tuesday. Par levels replace memory with a number: how much of each item you need on the shelf to reach the next delivery. They only work if they are based on actual usage, which means counting.
Step 1: The Item Master
| Column | Example | Why it matters |
|---|---|---|
| Item | Salmon fillet, skin-on | One name, used everywhere |
| Category / Storage area | Protein / Walk-in | Count sheets follow the shelf order |
| CountUnit | kg | The unit staff count in |
| Supplier | Harbour Seafood | Groups the order sheet |
| PackSize | 5 (kg per case) | Orders round to what you can buy |
| UnitCost | $24.80 per kg | Inventory value and food cost |
| DeliveryDays | Tue, Fri | Days between deliveries |
| LeadDays | 1 | Order-to-delivery time |
| Perishable | Yes | Lower safety stock, more frequent orders |
Step 2: Count Sheet and Purchases
Print or tablet-view a count sheet sorted by storage area and shelf order, so counting is a walk, not a hunt. Record counts in a Counts table (Date, Item, Qty) and deliveries in a Purchases table (Date, Item, Qty, Cost) from invoices.
Usage for the week ending in B1 (previous count date in B2):
=XLOOKUP(1, (tblCounts[Item]=[@Item])*(tblCounts[Date]=$B$2), tblCounts[Qty], 0)
+ SUMIFS(tblPurchases[Qty], tblPurchases[Item], [@Item],
tblPurchases[Date], ">" & $B$2, tblPurchases[Date], "<=" & $B$1)
- XLOOKUP(1, (tblCounts[Item]=[@Item])*(tblCounts[Date]=$B$1), tblCounts[Qty], 0)
Average daily usage (last 4 weeks of weekly usage in a history table):
=AVERAGEIFS(tblUsage[Usage], tblUsage[Item], [@Item],
tblUsage[WeekEnding], ">=" & TODAY()-28) / 7Step 3: Par Level and Order Quantity
Days of cover needed until the next delivery arrives:
=[@DaysBetweenDeliveries] + [@LeadDays]
Safety stock (simple version: a share of cover, lower for perishables):
=[@AvgDailyUsage] * IF([@Perishable]="Yes", 0.5, 1.5)
Par level:
=ROUNDUP([@AvgDailyUsage] * [@DaysCover] + [@SafetyStock], 1)
Order quantity, rounded up to full packs:
=IF([@OnHand] >= [@Par], 0,
CEILING.MATH([@Par] - [@OnHand], [@PackSize]))With deliveries on Tuesday and Friday, the gap is three or four days depending on which delivery you are ordering for. Many kitchens keep two pars per item, one for each delivery, which is easy to add as a second column.
Adjusting for busy days
If Friday and Saturday use twice the weekday quantity, average daily usage understates what you need for the weekend delivery. Track usage by day of week for key items, or apply a weekend multiplier on the Friday order.
Step 4: The Supplier Order Sheet
Order lines for one supplier (supplier in B1):
=FILTER(tblItems[[Item]:[OrderQty]],
(tblItems[Supplier] = B1) * (tblItems[OrderQty] > 0),
"Nothing to order")
Order value:
=SUMPRODUCT((tblItems[Supplier]=B1) * tblItems[OrderQty] * tblItems[UnitCost])A dropdown in B1 lets the manager flip between suppliers. Copy the result into an email, or have a macro create one Outlook email per supplier with the lines in the body.
Step 5: Food Cost, Actual vs Theoretical
| Measure | Formula | What it tells you |
|---|---|---|
| Actual food cost | (Opening value + purchases − closing value) ÷ food sales | What you really spent |
| Theoretical food cost | Σ(items sold × recipe cost) ÷ food sales | What you should have spent |
| Variance | Actual − theoretical | Waste, portioning, theft, errors |
| Variance by item | Actual usage − (sold × recipe qty) | Where to look first |
Theoretical usage needs recipe quantities and POS item sales. Our recipe spreadsheet in Excel provides the recipe side; most POS systems export item sales as CSV, which Power Query can import weekly.
Count Day Routine That Keeps the Numbers Honest
Count at the same time each week, before the delivery arrives.
Count in the same units the item master uses; convert cases and partials consistently.
Two people for high-value items such as proteins and alcohol.
Record waste in a simple waste log during the week; it explains variance.
Review the top ten variance items every week, not the whole list.
Where AI Helps a Kitchen Office
Extract quantities and prices from supplier invoices into the Purchases table.
Flag price increases by comparing each invoice line to the last price paid.
Summarise the weekly variance into three points for the chef and owner.
Suggest seasonal par adjustments by comparing usage to the same weeks last year.
Invoice capture follows the method in extracting PDF invoice data with AI. The same reorder thinking for retail and wholesale stock is in our reorder point formula guide.
Multi-Site and Growth
Groups with several locations need one item master, counts per site, and central reporting. That is where a shared database is easier than a set of workbooks; see Microsoft Access inventory management. For a single site that wants its par sheet, order sheets and food cost built properly, a custom Excel workbook is usually enough.
FAQ
Restaurant par levels FAQ
Usage, par, ordering and food cost