How to Setup Par Levels and Reordering Points Using Excel

Setting up par levels and reordering points using Excel can help you efficiently manage inventory, minimize stockouts, and reduce overstocking. This guide provides a detailed step-by-step approach to achieve this in Excel.

Key Concepts

1. Par Level: The optimal amount of stock you should have on hand for each inventory item. It acts as a benchmark for ordering.
   • Formula: Par Level = Average Usage per Day × Replenishment Time
2. Reorder Point (ROP): The inventory level at which you should reorder stock.
   • Formula: ROP = (Daily Usage × Lead Time) + Safety Stock
3. Safety Stock: Extra inventory kept on hand to prevent stockouts due to unexpected demand or supply chain delays.

Steps to Set Up Par Levels and Reordering Points Using Excel

1. Set Up Your Excel Spreadsheet

Create a table with the following columns:

Item Name	Average Daily Usage	Replenishment Time (Days)	Safety Stock	Par Level	Lead Time (Days)	Reorder Point	Current Inventory	Reorder Needed?

2. Enter Static Data

Populate the columns with static data specific to your inventory:

• Item Name: List of inventory items.
• Average Daily Usage: Average amount of stock used per day (e.g., 20 units).
• Replenishment Time (Days): The number of days it takes for stock to be delivered after ordering (e.g., 5 days).
• Safety Stock: Buffer stock for emergencies (e.g., 10 units).
• Lead Time (Days): Enter the lead time for each item.

3. Calculate Par Levels

In the Par Level column, use the formula:

= [Average Daily Usage] * [Replenishment Time (Days)]

For example:

• If Average Daily Usage is 20 units and Replenishment Time is 5 days: = 20 * 5 Result: 100 units

4. Calculate Reorder Points

In the Reorder Point column, use the formula:

= ([Average Daily Usage] * [Lead Time]) + [Safety Stock]

For example:

• If Average Daily Usage is 20, Lead Time is 3 days, and Safety Stock is 10: = (20 * 3) + 10 Result: 70 units

5. Input Current Inventory

In the Current Inventory column, manually enter the current stock levels for each item.

6. Determine If Reordering Is Needed

In the Reorder Needed? column, use the formula:

=IF([Current Inventory] <= [Reorder Point], "Yes", "No")

For example:

• If Current Inventory is 65 and Reorder Point is 70: =IF(65 <= 70, "Yes", "No") Result: Yes

Step-by-Step Example

Input Table Example:

Item Name	Average Daily Usage	Replenishment Time (Days)	Safety Stock	Par Level	Lead Time (Days)	Reorder Point	Current Inventory	Reorder Needed?
Item A	20	5	10	100	3	70	65	Yes
Item B	15	7	20	105	4	80	90	No
Item C	10	3	5	30	2	25	20	Yes

Adding Conditional Formatting

To visually highlight items that need reordering:

1. Select the Reorder Needed? column.
2. Go to the Home tab → Conditional Formatting → New Rule.
3. Choose Format only cells that contain.
4. Set the rule to apply formatting to cells equal to “Yes.”
5. Choose a formatting style (e.g., red fill with white text).
6. Click OK.

Automating Reordering Quantities

Add a column for Reorder Quantity if you want to calculate how much to order when reordering:

= [Par Level] - [Current Inventory]

For example:

• If Par Level is 100 and Current Inventory is 65: = 100 - 65 Result: 35 units

Tips for Effective Use

1. Dynamic Updates: Regularly update the Current Inventory column to reflect real-time stock levels.
2. Daily Usage Accuracy: Use historical sales or usage data to calculate Average Daily Usage accurately.
3. Automate Data Collection: Link the spreadsheet to inventory tracking software (if possible) for automatic updates.
4. Scenario Planning: Create separate sheets for different demand scenarios (e.g., peak vs. off-season).

Export to a Dashboard

For better visualization:

1. Create pivot tables to summarize data (e.g., total reorder quantities by category).
2. Use charts (e.g., bar or line charts) to monitor stock trends and reorder statuses.
3. Add slicers for filtering items by reorder status.

Downloadable Template

If you need an Excel template, I can help create a customizable version with built-in formulas for par levels and reorder points. Let me know if you’d like me to provide it.