Inventory Par-Level Calculator & Reorder Trigger Worksheet

Inventory Par-Level Calculator & Reorder Trigger Worksheet

Purpose: Help operational leaders keep the right medical supplies on hand—enough to avoid stockouts for patient care, but not so much that items expire or tie up budget. This tool explains the key formulas, gives Excel-ready examples you can paste into a spreadsheet, and supplies a short audit checklist to keep your system healthy.

When to use this worksheet

• Setting initial par levels for consumable supplies at a unit, procedure room, or clinic location.
• Revalidating reorder triggers when demand, lead time, or supplier reliability change.
• Combining vendor SLA and lead-time data with usage patterns to reduce stockouts and expiration risk.

Key terms (plain language)

• Average daily demand: Typical units used per day (use moving average or smoothed demand).
• Lead time: Time (in days) between placing an order and receiving it from vendor.
• Safety stock: Extra units held to cover variability in demand or lead time.
• Reorder point (ROP): Inventory level that should trigger a replenishment order.
• Par level: Target on-hand quantity after a routine replenishment to support expected demand until next delivery.

Core formulas (use in Excel or Google Sheets)

These examples assume you record daily usage for a period (e.g., 30–90 days) and have an estimated lead time in days.

1. Lead time demand (LTD) = Average daily demand × Lead time

   Excel: =AVERAGE(range_daily_usage) * lead_time_days

2. Estimate demand variability (standard deviation)

   Excel (sample): =STDEV.S(range_daily_usage)

3. Safety stock (service-level based)

   Safety stock = z × SD(daily demand) × SQRT(lead time)

   Where z is the z-score matching your desired service level (e.g., z≈1.28 for 90%, 1.65 for 95%).

   Excel: =z_value * STDEV.S(range_daily_usage) * SQRT(lead_time_days)

4. Reorder Point (ROP) = Lead time demand + Safety stock

   Excel: = (AVERAGE(range_daily_usage) * lead_time_days) + (z_value * STDEV.S(range_daily_usage) * SQRT(lead_time_days))

5. Par level (target after replenishment)

   Par = ROP + (expected daily demand × review period), where review period is the time between routine counts/replenishments

   Excel example if you restock weekly: =ROP + (AVERAGE(range_daily_usage) * 7)

Worked example (compact)

Assume: average daily demand = 4 units; lead time = 5 days; SD(daily demand) = 1.5; target service level = 95% (z=1.65)

• Lead time demand = 4 × 5 = 20 units
• Safety stock = 1.65 × 1.5 × SQRT(5) ≈ 1.65 × 1.5 × 2.236 ≈ 5.53 → round up to 6 units
• ROP = 20 + 6 = 26 units (place reorder when on-hand reaches 26)
• If weekly review/restock: Par level = 26 + (4 × 7) = 26 + 28 = 54 units

Spreadsheet layout (columns you can copy)

Use one row per SKU-location combination.

1. Item code
2. Item description
3. Location (e.g., OR1, Pharmacy)
4. Average daily demand (formula cell)
5. SD daily demand (formula cell)
6. Lead time days (vendor)
7. z-value (service level)
8. Safety stock (formula)
9. Lead time demand (formula)
10. Reorder point (formula)
11. Review period days
12. Par level (formula)
13. On-hand quantity (live count)
14. Days of cover (On-hand / Avg daily demand)
15. Suggested reorder quantity (Par level - On-hand, min 0)

Sample Excel formulas (assume row 2, daily usage range on a separate sheet named 'Usage' rows 2:31)

Place these in the row for the SKU and adapt ranges:

• Average daily demand: =AVERAGE(Usage!B2:B31)
• SD daily demand: =STDEV.S(Usage!B2:B31)
• Lead time demand: =C2 * D2 (if C2 = AvgDaily, D2 = LeadTimeDays)
• Safety stock: =E2 * F2 * SQRT(D2) (if E2 = z-value, F2 = SD)
• Reorder point: = (C2 * D2) + (E2 * F2 * SQRT(D2))
• Par level (weekly refill example): =J2 + (C2 * L2) (J2=ROP, L2=ReviewPeriodDays)
• Suggested reorder qty: =MAX(0, M2 - N2) (where M2 = Par level, N2 = On-hand)

Sample Vendor SLA / Lead Time table

Keep a small vendor master for each SKU/vendor combination so you can track real lead-time performance.

Periodic Inventory Audit Checklist (quick)

• Confirm on-hand counts for high-risk and high-cost SKUs weekly.
• Compare actual lead time vs promised lead time for recent deliveries (rolling 3 months).
• Flag SKUs with >30% variance in daily usage and investigate clinical or process causes.
• Check expiration dates; segregate items with <90 days to expiry and consider returns, usage acceleration, or reduced par.
• Review suggested reorder quantities against recent consumption for any manual overrides.
• Confirm critical item contact list and backup vendor options are up to date.

Suggested operational KPIs

• Stockout events per month (by criticality)
• Percent of items within target days-of-cover range
• Vendor on-time delivery % and average lead time
• Inventory turnover (annualized) by category
• % of value at risk due to near-expiry (<90 days)

Common mistakes and how to avoid them

• Using too short a usage history — smooth demand over a representative period and account for seasonality (e.g., flu months).
• Ignoring lead time variability — update safety stock when vendor performance degrades.
• Setting a single service level for all SKUs — tier critical items (OR implants, emergency meds) at higher service levels than common disposables.
• Allowing manual overrides without a short review period — require documented justification and a 30-day follow-up.

How to adapt this tool for your site

1. Identify your critical SKUs (those that would stop procedures or harm patients if out of stock).
2. Decide review cadence by category (daily for OR critical stock, weekly for clinic disposables, monthly for storeroom non-critical items).
3. Choose service levels per category (e.g., 99% for critical implants, 95% for routine disposables).
4. Load usage history and vendor lead-time records into the spreadsheet and validate formula outputs with a small pilot of 25–50 SKUs for 4–8 weeks.

Next steps & optional enhancements

Start with a small pilot: pick a unit, implement the spreadsheet, run weekly audits, and compare stockouts and on-hand variability before and after. Use the pilot learnings to tune z-values and review cadence.

For broader deployments, consider capability enhancements (below) that make this worksheet operational at scale.