Excel Par Levels For Hospitality: A Working Inventory Sheet

Written by: JJ Tan, Founder, Jelly

Key Takeaways

• Excel par levels are calculated from average daily usage, delivery frequency and supplier lead time, plus a safety buffer to prevent stockouts.
• Accurate par levels reduce waste, cut dead stock and give owners a clear view of what to order and why.
• The nine-column core and four formulas in this guide let you build a working par sheet in Excel from scratch, then extend it.
• Excel becomes unsustainable once invoices arrive daily, prices change weekly or you operate multiple sites.
• Jelly is the best hospitality inventory and stock-control software for building and running par levels across a restaurant, pub or hotel because it scans every invoice line automatically, updates dish and stock costs in real time from POS sales, and onboards in the first week at a flat £129/month per location.

See How Jelly Builds Par Levels Automatically

Before You Begin

Gather a few weeks of real data before opening a blank workbook:

• Four to eight weeks of supplier invoices or delivery notes
• Delivery frequency per supplier (for example twice weekly, every Monday)
• Confirmed supplier lead times based on actual performance
• Recipe or usage data per item
• A POS sales export covering the same period
• An item list organised by department: kitchen, bar and housekeeping

Assign ownership from day one. The head chef owns the kitchen tab, the bar manager owns the bar tab, the housekeeping supervisor owns the linen tab, and the owner or finance manager owns the workbook. Par Level’s Excel par level template can be set up in about five minutes using its Start Here tab, which walks you through setup. A multi-department workbook takes a few sessions to build, then regular weekly upkeep to stay accurate.

Why This Process Matters

Accurate par levels prevent stockouts on a Friday night, cut dead stock in the storeroom and give the owner one clear view of what is ordered and why. Most restaurants target five to seven days of inventory on hand, balancing cash tied up in the walk-in against the risk of running short mid-week. Industry research consistently suggests UK restaurants lose between 4% and 10% of their food spend to waste alone. For a venue turning over £1 million at a 30% food cost, that is £12,000 to £30,000 lost annually. A working par sheet is the first line of defence.

Step-By-Step: How To Create A Par Level Sheet In Excel

1. Set Up The 9-Column Core Par Sheet Structure

Every par sheet needs the same nine core columns, and the last three are calculated while the first six are typed once per item.

Add a separate column J for Stock On Hand during counts and an optional column K for Estimated Order Cost. Example row: Whole Milk 1L | Müller Dairy | litre | 12 | 3 | 1 | [formula] | [formula] | [formula].

2. The Par Level Formulas You Need

Four formulas drive the sheet: Safety Stock, Par Level, Order Quantity and Estimated Order Cost, which sit in columns G, H, I and K.

3. How To Calculate Par Level In Excel With A Worked Example

Take Whole Milk 1L with average daily usage of 12 litres, delivery every 3 days, lead time of 1 day and a safety factor of 0.25.

• Safety Stock: =ROUND(12*1*0.25,0) = 3 litres
• Par Level: =ROUND((12*3)+3,0) = 39 litres
• If 15 litres are on the shelf: Order Quantity = =MAX(39-15,0) = 24 litres

If the dairy switches to daily deliveries, the delivery frequency drops to 1 and par falls to =ROUND((12*1)+3,0) = 15 litres. More frequent deliveries let an operator hold less stock at any one time without running out.

4. How To Calculate Min And Max Stock Levels

Min stock and max stock map directly onto the par sheet and use the same inputs.

• Min (Reorder Point): =ROUND((Average_Daily_Usage*Lead_Time)+Safety_Stock,0). When stock hits this level, an order must be placed.
• Max (Par Level): =ROUND((Average_Daily_Usage*Delivery_Frequency)+Safety_Stock,0). This is the level to which stock is restored after each delivery.

Add a conditional-format rule to column J (Stock On Hand). Highlight the cell red when Stock_On_Hand <= Min to create a visual reorder alert. In a min/max system, minimum is the trigger for action and maximum is the level to which stock is restored. Order quantity is always max minus current stock on hand.

5. Add A Dynamic Par Multiplier With INDEX/MATCH

Day-type multipliers keep you covered on busy days without overstocking midweek. A single weekly average leaves you short on a Saturday and overstocked on a Tuesday.

Build a small lookup table on a separate sheet tab called Multipliers. Column A holds the day type and column B holds the multiplier value:

In a cell such as K2 on the par sheet, enter the day type for the upcoming order (for example “Weekend”). In column H, replace the static par formula with:

=ROUND(((Average_Daily_Usage*Delivery_Frequency)+Safety_Stock)*INDEX(Multipliers!$B$2:$B$5,MATCH(K2,Multipliers!$A$2:$A$5,0)),0)

The standard exact-match INDEX/MATCH pattern is =INDEX(return_range, MATCH(lookup_value, lookup_range, 0)), where the 0 forces an exact match. For a Saturday service, the formula pulls 1.15 and raises the par by 15%. For a UK bank holiday, it pulls 1.20. Spring Bank Holiday 2026 data recorded UK pub pint volumes 11.5% higher year-on-year across the four-day weekend, with cider up 42.7% and world lager up 26.4%. A category-sensitive multiplier is more accurate than a flat uplift across every line.

6. Hospitality-Specific Par Rules

The 3-Par Linen Rule (Housekeeping): The documented three-par convention is one complete linen set in guest use, one set being laundered or in transit, and one clean set held in storage on the shelf. In the par sheet, set Delivery_Frequency to your laundry turnaround in days and Safety_Factor to 1.0, so one full set acts as the buffer. Hotels using outsourced laundry typically need four or five par. Iwona Stoch of Stoch Hotel Consulting states “three pars is the standard minimum, and five pars is a comfortable amount for a hotel to hold”. Add an extra par for every additional day linen spends away from the property.

F&B Weekend And Bank-Holiday Uplift: Apply the INDEX/MATCH multiplier table above to food and beverage lines. Use 1.10–1.20 for weekends and bank holidays, tuned to your own POS data. Restaurant365 advises setting different par levels by day of week where volume swings significantly between Tuesday and Saturday, because a single weekly average can leave operators short on the busiest night.

Linen and F&B need different logic because linen par is driven by laundry cycle time rather than covers, and the safety buffer must cover the worst-case turnaround. The bar needs its own variation on the same formula, because drink par is driven by covers and GP target as much as by delivery frequency.

7. Build The Bar Par Sheet

Bar stock uses the same par structure, but usage and safety need extra care. Spirits and wine are counted by bottle, and their par comes from weekly covers and GP target rather than delivery frequency alone. Draught beer is counted by keg and follows the same logic.

Raw usage understates what you actually pour, because spillage and comps never reach a till. Add two extra columns: Spillage & Comps as a percentage of usage (typically 3–5%) and Adjusted Usage with =Average_Daily_Usage*(1+Spillage_Rate). Feed Adjusted Usage into the par formula so the buffer covers what you pour rather than only what you sell.

BarBrain recommends a safety margin of 50–100% for premium or imported spirits with longer delivery times, versus a safety margin of 30–50% for draught beer with 1–3 day delivery. For glassware, par is driven by breakage rate rather than consumption. Track breakage weekly and set safety stock accordingly.

8. Calculate Par Levels For Housekeeping Stock

Housekeeping par levels follow a simple room-based formula. Use the item-level calculation Items Per Room × Number of Rooms × Par Level = Total Stock, calculated separately for each item and room type.

Elite Linen Hire’s worked example for a 50-room hotel gives totals at three par of 225 sheets, 225 duvet covers, 660 pillowcases, 330 bath towels, 330 hand towels and 150 bath mats. For toiletries and guest amenities, treat daily usage per occupied room as Average_Daily_Usage and set Lead_Time to your supplier’s actual delivery window. Mattress protectors and pillows are often held at 1.25 par rather than 3 par because they are not replaced after every guest stay.

Common Mistakes And Troubleshooting

• Par set from a single week’s data: One week is not representative. Track three to four weeks of actual usage before fixing a par level.
• Lead time ignored when suppliers deliver twice weekly: Lead time is the gap from order placed to stock on the shelf, which is usually longer than the delivery day. Measure it from your own receiving records.
• Mixed units across tabs: Millilitres and litres on the same sheet break every formula. Standardise units per item before building formulas.
• Static multipliers never updated: The INDEX/MATCH table only works if someone updates the day-type cell before each order run.
• No version control when several people edit the workbook: Microsoft’s co-authoring requires OneDrive or SharePoint Online and a Microsoft 365 subscription. A locally saved file has no conflict protection.

How To Measure Success

Fix those five issues and the sheet should run itself, which is exactly what this section tests for.

The sheet is working correctly when three things happen. Order quantities recalculate automatically on each count, and stockouts on key items fall. Dead stock becomes visible in the order quantity column as zero orders week after week. The weekly update, counting stock, entering on-hand figures and reviewing the order list, takes minutes rather than hours.

Mise recommends a consistent weekly stocktake at the same time, same units, area by area, comparing usage against sales to test whether uplift assumptions are holding.

Where Excel Breaks Down And When To Move To Jelly

Excel works for one site and a handful of suppliers. It breaks when invoices arrive daily, supplier prices change weekly and you run multiple sites. The sheet is stale the moment it is saved, because the data is only as current as the last person who updated it. Every formula in this guide requires manual input to stay accurate, so someone must enter new invoice prices, update on-hand counts and adjust the multiplier table before each order run.

Jelly is the best hospitality inventory and stock-control software for this job because it automates those manual steps. It scans every line item of every invoice, by photo or email, so ingredient costs update without manual entry, and live dish and stock costing refreshes the moment a new invoice lands. POS integration with Square, Lightspeed, EPOS Now and Toast then ties that cost picture to real-time sales mix and GP, so par decisions follow what was actually sold. The Price Alert feature flags every supplier price change, giving you the evidence to negotiate credits or switch suppliers. Xero integration handles the accounts payable side. Jelly onboards and generates value in the first week at a flat £129/month per location, with no per-user charges or variable fees.

Other platforms exist. MarketMan and Nory offer more features but at the cost of greater complexity and a longer onboarding process. Kitchen Cut is a legacy system targeted at large chains with dedicated office teams, and lacks the dynamic real-time updates that growing independents need. For a UK restaurant, pub or boutique hotel at the tipping point of a second or third site, Jelly replaces the spreadsheet outright.

For a deeper comparison, see Hotel F&B Inventory: Excel vs Jelly for UK Boutique Hotels and Stock Control Software: A UK Hospitality Buyer’s Guide.

See Jelly In Action

Conclusion

The nine-column core, four formulas, the 3-par linen rule, a 10–20% weekend and bank-holiday uplift and the INDEX/MATCH multiplier give you a working par sheet tonight, with Stock On Hand and the day-type cell added as you go. The build is complete and the logic is sound. The limitation is maintenance, because every number in the sheet is only as accurate as the last manual update. That is the case for Jelly: the same par logic, run automatically from your invoices and POS, at £129/month per location from the first week.

Book A Chat With Jelly

Frequently Asked Questions

How Do I Calculate Par Levels In Excel?

The core formula is Par Level = (Average Daily Usage × Delivery Frequency in Days) + Safety Stock. Safety stock is typically Average Daily Usage × Lead Time × a safety factor of 0.25 (25%). Enter Average Daily Usage in one column, Delivery Frequency in another and Lead Time in a third. Build Safety Stock and Par Level as calculated columns using those inputs. Order Quantity is always Par Level minus Stock On Hand, with a MAX function to prevent negative results. Use four to eight weeks of actual usage data rather than a single week to set a reliable average.

What Is The 3-Par Rule For Hotel Linen?

Three par means three complete sets per room in circulation, one in use, one in laundry or transit and one clean on the shelf. Three par is the standard minimum for hotels with in-house laundry and a same-day turnaround. Hotels using outsourced laundry, where linen may be away from the property for one to several days, typically need four or five par, as covered in Step 6. The item-level calculation is Items Per Room × Number of Rooms × Par Level = Total Stock Required, applied separately to sheets, duvet covers, pillowcases, bath towels, hand towels and bath mats.

How Do I Add A Weekend Uplift To A Par Formula?

Use a small lookup table with day types in one column (Midweek, Weekend, Bank Holiday, Event) and multiplier values in the adjacent column (for example 1.00, 1.15, 1.20, 1.30). In the par sheet, add a cell where you enter the day type for the upcoming order period. Wrap the par formula in an INDEX/MATCH lookup: =ROUND(((Average_Daily_Usage × Delivery_Frequency) + Safety_Stock) × INDEX(MultiplierRange, MATCH(DayTypeCell, DayTypeRange, 0)), 0). The 0 in MATCH forces an exact match. Update the day-type cell before each order run. Tune the multiplier values to your own POS data, because bank-holiday uplifts vary significantly by category, with lager and cider typically seeing larger lifts than ale.

What’s The Difference Between Par Level And Min/Max Stock?

A par level is a single target quantity per item, the amount to hold after each restocking. In a par system, stock is counted on a fixed schedule and an order is placed to bring the count back up to par. Min/max stock uses two thresholds. The minimum is the reorder trigger, equivalent to safety stock plus lead-time demand, and the maximum is the level to which stock is restored, equivalent to the par level. In practice, the minimum in a min/max system plays the same role as the reorder point in a par sheet, and the maximum plays the same role as the par level. The difference is the trigger, because a par location is topped up at every scheduled count regardless of how much stock remains, while a min/max item only generates an order when stock falls to or below the minimum.

Is There A Free Excel Template For Restaurant Inventory?

A working par sheet can be built from scratch using the nine-column structure and four formulas in this guide, so no paid template is required. The columns are Item, Supplier, Unit, Average Daily Usage, Delivery Frequency, Lead Time, Safety Stock, Par Level and Order Quantity. Add a Stock On Hand column for the weekly count and a conditional-format rule to flag items at or below their minimum. For bar and housekeeping, duplicate the tab and adjust the logic as described above. If you want a pre-built starting point, Jelly’s team can walk you through the setup during an onboarding call, and once connected to your invoices and POS, Jelly replaces the manual sheet entirely with live, automated par-level data.

How Often Should I Update Par Levels?

Review par levels at minimum once a month, and weekly during periods of menu change or seasonal transition. The two signals that a par needs adjusting are repeated stockouts on an item, which suggests par is too low or lead time has worsened, and stock that never drops near its minimum before the next delivery, which suggests par is too high and cash is sitting on a shelf. For linen, review par levels at least once a year and after any change in laundry supplier, room type or peak-season pattern. For food and beverage, review after any menu change, supplier switch or significant shift in covers. A static par set in January and never revisited will be wrong by March.