How to analyze inventory and decide what to reorder

Where the difficulty begins

A low on-hand number may be safe when new stock arrives tomorrow, while a higher number can be risky for a fast-selling item with a long lead time. Returns and unreceived purchase orders often make spreadsheet stock stale.

From the field · Local shop owner

Prepare a weekly reorder list for a neighborhood shop

The shop tracks 450 SKUs. Supplier lead times range from two to 21 days, weekend demand is higher, and some purchase orders are late. A promotion starts next week for one category.

Columns used in the example

sku · location · on_hand · reserved · on_order · expected_date · daily_sales · lead_time_days · unit_margin

Follow it with your own file

1. Reconcile current stock and exclude damaged or reserved units from availability.
2. Calculate expected demand through the realistic receipt date.
3. Apply approved promotion and safety-stock adjustments.
4. Create order, watch, and no-action groups with the evidence for each SKU.

Check: SKU totals must reconcile to stock and purchase-order sources, receipt dates must not be assumed for overdue orders, and the reorder quantity must respect case packs and storage constraints.

Check the source before you publish

• Inventory position reconciled
• Lead times current
• Demand window aligned
• Buffer rule approved
• Case packs and constraints applied

What may be distorting the answer

• On-hand stock disagrees with a physical or system count.
• Open purchase orders are treated as available inventory.
• Reorder decisions use one average for seasonal or intermittent demand.

Build the result step by step

1. Reconcile inventory position: Separate on-hand, reserved, damaged, in-transit, and available units by SKU and location.
2. Estimate lead-time demand: Use recent demand, seasonality, promotions, and supplier lead-time variation.
3. Set an explicit buffer: Choose safety stock or service level based on stockout and holding costs.
4. Prioritize orders: Rank shortages by expected timing, margin, substitutability, and operational importance.

Reorder risk is quantity plus time

Inventory decisions depend on how fast stock will be consumed and when reliable replenishment can arrive. A reproducible rule makes the trade-off between stockout and holding cost visible rather than embedding it in a manager's memory.

Where this usually goes wrong

A new or intermittent item may have too little history for a statistical buffer. Use comparable products, supplier information, and a cautious manual decision instead of false precision.

Choose the approach that matches the evidence and repetition

The decisions that matter

• Separate on-hand, reserved, damaged, in-transit, and available units by SKU and location.
• Use recent demand, seasonality, promotions, and supplier lead-time variation.
• Choose safety stock or service level based on stockout and holding costs.
• Rank shortages by expected timing, margin, substitutability, and operational importance.

Doing these steps in Prepyr

Prepyr accepts CSV files only. Export the worksheet or table you need as a UTF-8 CSV first; workbook formulas, formatting, macros, charts, and other sheets will not be included. Describe the result you need: Analyze inventory and calculate practical reorder priorities in Excel. Check the columns selected, preview any proposed changes, and compare the output with the source before applying or exporting it.

Prepyr steps used here: Data profiling · Natural Language analysis · Charts · Reusable workflows

Questions readers usually ask next

What is a reorder point?

It is the inventory position at which replenishment should be triggered, commonly based on expected lead-time demand plus a buffer.

Should on-order stock reduce a reorder quantity?

Only when the order is valid and expected to arrive in time. Keep late or uncertain orders visible as risk.