Setting up par levels and reordering points in Excel is one of the most effective, low-cost ways to manage inventory for a restaurant, café, bar, retail shop, or really any inventory-based business. Par levels and reorder points help you maintain enough stock to meet demand while avoiding costly overstocking or frustrating stockouts.
This will be a thorough, structured guide to help you not just build a spreadsheet, but also understand the thinking behind it, so you can adapt the logic to your business later.
1️⃣ Understanding Par Levels and Reordering Points
Let’s define these clearly:
✅ Par Level
A “par” is the ideal quantity of an item you always want to have on hand to operate smoothly. It represents the target stock level, factoring in daily usage and supplier lead times.
✅ Reorder Point (ROP)
This is the minimum quantity at which you should trigger a new order. When inventory drops to the reorder point, you place a new order.
In restaurants, par levels help you ensure you can meet menu demands, while reorder points tell you exactly when to order to avoid running out.
For example:
- You run a coffee shop that goes through 5 kg of coffee beans per day.
- The supplier takes 3 days to deliver beans.
- You want to always have 2 days of buffer.
- Daily usage: 5 kg
- Lead time: 3 days
- Safety stock buffer: 2 days
Par level calculation:
Par Level = (Daily usage × lead time) +( Daily usage × safety days)
Par Level = (5 × 3) + (5 × 2) = 15 + 10 = 25 kg
Reorder point is usually the same as the consumption during lead time plus safety stock:
Reorder Point = (Daily usage × lead time) + safety stock
(which in this case may be the same as par).
2️⃣ How to Structure Your Excel Sheet
Let’s get practical. Here is a simple but powerful way to build a worksheet to manage par levels and reordering points.
Worksheet columns you’ll need
| Column | Purpose |
|---|---|
| Item Name | Identify the inventory item |
| SKU / Code | Unique identifier |
| Unit | e.g., kg, L, pieces |
| Daily Usage | Estimated daily consumption |
| Lead Time (days) | Days for supplier to deliver |
| Safety Days | Extra buffer |
| Par Level | Target inventory to keep |
| Reorder Point | When to reorder |
| Current Stock | What you currently have |
| Quantity to Order | Formula-driven to suggest an order quantity |
By arranging your spreadsheet this way, you will clearly see:
✅ how much you typically use
✅ how long it takes to get more
✅ how much safety you want
✅ and how to automatically calculate the recommended reorder quantity.
3️⃣ Building the Excel Formulas
Let’s break down the formulas to put in Excel.
Assuming the following columns:
- A: Item Name
- B: SKU
- C: Unit
- D: Daily Usage
- E: Lead Time (days)
- F: Safety Days
- G: Par Level
- H: Reorder Point
- I: Current Stock
- J: Quantity to Order
Formula for Par Level (Column G)
In G2:
= (D2 * E2) + (D2 * F2)
This multiplies daily usage by lead time, then adds daily usage by safety days.
Formula for Reorder Point (Column H)
You can keep it simple and have the same logic:
= (D2 * E2) + (D2 * F2)
or separate out the safety stock if you want to adjust:
= (D2 * E2) + (D2 * F2)
Formula for Quantity to Order (Column J)
You want to order enough to top back up to par, whenever current stock is below reorder point.
In J2:
=IF(I2<=H2, G2 - I2, 0)
Which means:
- if your current stock is less than or equal to reorder point, order enough to get back up to par
- otherwise, order nothing
This simple rule will keep your shelves stocked without huge overages.
4️⃣ Practical Example
Let’s fill in an example for clarity.
| A | B | C | D | E | F | G | H | I | J |
|---|---|---|---|---|---|---|---|---|---|
| Coffee Beans | COF001 | kg | 5 | 3 | 2 | =35 | =35 | 10 | 25 |
| Milk | MLK001 | L | 20 | 2 | 1 | =60 | =60 | 30 | 30 |
- For Coffee Beans, daily usage 5 kg, lead time 3 days, safety days 2 days
- Par: (5×3) + (5×2) = 35 kg
- Reorder point: same
- If current stock is 10 kg, you need to order 25 kg to return to par.
5️⃣ Adding Conditional Formatting
You can go a step further and add conditional formatting so Excel highlights anything that needs reordering.
✅ Select your “Quantity to Order” column (J)
✅ Go to Home → Conditional Formatting → Highlight Cells Rules → Greater than 0 → Choose a color (e.g., red)
Now whenever there is an order suggestion, you’ll see it instantly highlighted.
6️⃣ Building Dynamic Par Levels with Seasonality
One weakness with static par levels is they don’t adapt to seasonality. For example, if you sell more ice cream in summer, you’ll want to raise your par levels then.
One way to manage this is by adding a seasonal multiplier:
| K |
|---|
| Season Multiplier |
Then adjust:
= ((D2 * E2) + (D2 * F2)) * K2
Where K2 might be:
- 1.2 in summer
- 0.8 in winter
This is a simple but powerful way to adapt your reorder plan to seasonal swings.
7️⃣ Building a Dashboard
If you want a next-level approach, you can even build a simple dashboard in a new sheet to summarize:
✅ How many items are below reorder point
✅ How much total to spend on the next PO (purchase order)
✅ What supplier you’ll need to contact
For that, add extra columns like Supplier, Unit Cost, and Total Order Cost:
= J2 * Unit Cost
And then sum across for total budget planning.
8️⃣ Step-by-Step Recap
Let’s recap the entire methodology:
- List all your inventory items
- coffee beans, milk, flour, napkins, whatever
- Record your daily usage
- how much you realistically go through in a day
- Determine lead time
- how long your supplier takes
- Choose a safety stock
- a buffer for mistakes or demand spikes
- Calculate par
- so you always have enough
- Set reorder point
- to know exactly when to reorder
- Monitor current stock
- regularly count or use your POS reports
- Use the quantity-to-order formula
- to know how much to buy
- Highlight needs with conditional formatting
- so you see urgent items quickly
- Review regularly
- weekly or biweekly updates are best
- Adapt for seasons or promos
- multiply par levels to handle peaks
- Consider a dashboard
- track total costs, supplier orders, next delivery dates
9️⃣ Advanced Features You Can Add Later
As your business grows, you can gradually make your Excel sheet more advanced. Here are add-ons you might consider:
✅ Inventory Turnover Tracking
- add a column to calculate how many times per month you turn your stock
- e.g., Monthly Usage / Average Stock
✅ Expiry Dates
- for perishables, track expiry to prevent waste
✅ ABC Prioritization
- rank products:
- A: high-value items
- B: medium-value
- C: low-value
- focus more on tight controls for A items
✅ Supplier Contact Info
- save emails/phones in the sheet for quick reordering
✅ Charts
- graph current stock vs reorder point
- helps visualize stock risks
✅ Data Validation
- restrict data entry (no negative numbers)
- reduce input mistakes
✅ Dynamic reorder points
- link to your actual sales data so the reorder point updates each week
Excel is powerful enough to handle all of these if you structure it carefully.
🔟 Best Practices for Inventory Par Levels
Setting par levels and reorder points is as much a business skill as a spreadsheet skill. Here are best-practice tips:
✅ Base your daily usage on a rolling average, not a one-off guess
- e.g., average last 4 weeks sales divided by days open
✅ Keep your lead time current
- sometimes suppliers change their schedules
✅ Audit your safety stock quarterly
- too high = waste of cash
- too low = risk of stockouts
✅ Be realistic about buffer days
- your buffer is your insurance; too small means risk
✅ Count your inventory physically
- a spreadsheet is only as good as the counts you feed it
✅ Train staff
- staff should know how to do a proper count
- how to fill in Excel correctly
- how to avoid typos
✅ Consider barcoding or scanning
- if Excel becomes unmanageable, you could integrate barcode scanning for stock takes
✅ Backup your Excel sheet
- always keep a backup on cloud or USB
1️⃣1️⃣ Example Template to Copy
Here’s a simple layout you could copy/paste directly into your Excel:
| Item Name | SKU | Unit | Daily Usage | Lead Time (days) | Safety Days | Par Level | Reorder Point | Current Stock | Quantity to Order |
|---|---|---|---|---|---|---|---|---|---|
| Coffee Beans | COF001 | kg | 5 | 3 | 2 | 35 | 35 | 10 | 25 |
| Milk | MLK001 | L | 20 | 2 | 1 | 60 | 60 | 30 | 30 |
| Tea | TEA001 | kg | 2 | 4 | 2 | 12 | 12 | 5 | 7 |
| Sugar | SUG001 | kg | 4 | 3 | 1 | 20 | 20 | 22 | 0 |
Then copy the formulas described above into G, H, and J columns.
1️⃣2️⃣ Why Excel is Actually Great for Par Levels
Some business owners think they need fancy software to do this, but Excel is still king for smaller businesses because:
✅ You own it, no subscription
✅ Flexible
✅ Transparent formulas (you see exactly what’s going on)
✅ Portable
✅ Works even offline
✅ Easy to train staff
Of course, as you grow, you might eventually switch to an inventory management system or restaurant POS. But for many small cafés or retail shops, Excel is more than enough.
1️⃣3️⃣ Integrating POS Systems with Excel
One advanced path is integrating your POS to feed into the Excel automatically. Many modern POS systems can export daily sales data. You can:
✅ import that data weekly
✅ update your daily usage automatically
✅ then let Excel recalculate par and reorder points
This semi-automated workflow saves hours and reduces manual data-entry mistakes.
1️⃣4️⃣ Final Words: Think of Par as a Living Number
Don’t treat par levels as “set once and forget forever.” Businesses evolve:
- new menu items
- new suppliers
- staff turnover
- marketing promos
- seasonal rushes
All of these mean you should review your par levels and reorder points at least once per quarter if not monthly.
If you really want to take Excel even further, you can build a second sheet to do weekly forecasting using rolling averages of sales, which is essentially a mini demand-planning module. That can make your inventory even more bulletproof.
🚀 Summary
✅ Par levels = your target stock
✅ Reorder point = minimum to trigger reordering
✅ Excel is perfect to set this up
✅ Use simple columns + formulas
✅ Add conditional formatting
✅ Consider a seasonal multiplier
✅ Use a dashboard to summarize
✅ Integrate with POS exports if possible
✅ Review your numbers quarterly
✅ Train staff to keep it consistent
