How to plan purchases and replenishment
1. Prepare reliable operating data
Purchase planning starts with a reliable snapshot of your operation. Check physical stock, identify reserved quantities and record only purchases that have actually been confirmed. An order under negotiation is not available merchandise. Use a stable SKU for every product and retain the same unit throughout related files. A box and a piece cannot be added without a known conversion factor. For a quick estimate, enter a product name, current quantity, average consumption and delivery lead time. The SKU can be generated automatically. Add safety stock or minimum order quantities only when needed. Start with a practical estimate and then investigate the items that require closer attention.
2. Import and validate each worksheet
Download the free template and retain identifiers as text, especially those with leading zeros. Import products first, followed by consolidated daily sales and expected receipts. To replace an existing stock count, choose the stock update type instead of adding the same products again. Assign every column to one field and explicitly choose the file's number format. Dates must use the ISO year, month and day format. The spreadsheet reader rejects formulas and never executes macros. Incompatible records stop the entire import to prevent silent aggregation. Correct the source and import it again before relying on the results. Keep a copy of the original data for later comparison.
3. Understand demand and missing observations
Daily demand uses recorded days within the selected window. An explicitly recorded zero participates in the average; an absent day is never invented. When dates are missing, the result warns that the average represents available observations only. Classification and statistical safety require a complete window to reduce misleading interpretations. The weekly estimate equals seven daily averages. The monthly estimate uses the actual number of days in the analysis month, including leap year February. If you enter a monthly average manually, daily conversion uses that same month. Without positive demand, coverage is displayed as unavailable rather than infinity. Review whether the observation period represents normal trading conditions before selecting a forecasting method.
4. Separate availability from inventory position
Available stock equals physical quantity minus reserved quantity. Inventory position adds firm future receipts and subtracts only additional backorders that are not already represented by reservations. This prevents subtracting the same customer order twice. Coverage divides available stock by positive daily demand. The reorder point adds expected demand during lead time and safety stock. For example, ten units per day, seven delivery days and twenty safety units produce a reorder point of ninety units. Compare this point with inventory position, but also examine the dated projection. A purchase arriving too late can suppress another quantity recommendation while still leaving a temporary stockout before the delivery arrives.
5. Choose an appropriate safety policy
Safety stock can be a manual quantity, a number of demand days or a statistical estimate. The model uses SS = z × square root of (L × daily variance + daily demand squared × lead time variance). A confirmed fixed lead time can use zero deviation. Cycle service factors are approximately 1.28155 for 90%, 1.64485 for 95%, 1.95996 for 97.5% and 2.32635 for 99%. This does not represent fill rate or guarantee fulfilment. The normal approximation assumes independence and suitable operating conditions. Intermittent demand, seasonality and short histories require manual review. When the model cannot be applied, the planner retains manual safety stock and displays an explicit warning.
6. Convert requirements into feasible order lots
Target stock covers demand during lead time plus the greater of desired coverage and review interval, with safety added. Base purchase quantity is the maximum of zero and target minus inventory position. Minimum orders and packaging multiples are then applied, rounding upwards. A minimum of ten with packs of six can require twelve units. If this exceeds maximum stock, the purchase is suspended for conflict review. Blocked products also require intervention. The budget uses a transparent heuristic: critical items first, then lower coverage, with SKU as the tie breaker. It respects lots and minimums but does not promise mathematical optimality. Partial allocations remain flagged because residual stockout risk still exists.
7. Interpret ABC, XYZ and forecasts
ABC ranks annualized consumption value using a complete observation window and known unit costs. Default thresholds of eighty and ninety five percent use the cumulative share before each item, retaining the item crossing a boundary in its current class. XYZ compares sample deviation with daily mean through configurable coefficient of variation thresholds. Zero demand and incomplete data remain unclassified. Compare simple averages, weighted averages, exponential smoothing, linear trend and Croston with the SBA correction. Temporal validation reports MAE and RMSE when enough observations exist, without misleading MAPE calculations over zeros. A trend does not automatically identify seasonality. Examine the history and select the method deliberately rather than treating any forecast as certain.
8. Review, share and preserve the project
Use scenarios to investigate higher demand, costs, supplier delays and financial restrictions. The comparison shows changes against the first reference state. Projections apply receipts at the beginning of each date and consumption on days following the analysis date. The weekday calendar covers Monday to Friday without public holidays; the entered average must represent those operating days. Exports include assumptions, warnings and parameters so another person can review the decision. Excel separates suppliers, while JSON preserves the project for reopening. Optional browser storage does not replace a backup. Check unknown costs, selected items and units before placing an order. Recommendations cannot replace supplier negotiation, physical counts or an assessment of actual delivery conditions.
| Average demand | Lead time (days) | Safety stock | Reorder point |
|---|---|---|---|
| 10 | 7 | 20 | 90 |
Frequently asked questions
Does this replace a stock count?
No. It uses the stock you provide to suggest purchases. Verify physical quantities before important decisions.
How are open orders handled?
Firm future receipts enter inventory position. Past dates are flagged and excluded; late deliveries can still cause temporary stockouts.
Is the service level guaranteed?
No. The normal model approximates cycle service, which differs from fill rate. Incomplete and intermittent demand falls back to manual safety.
Can I recover my project?
Export versioned JSON or optionally save in this browser. The file retains products, history, receipts, settings and scenarios.
How do I import worksheets?
Import products first. Then import daily sales or receipts by mapping SKU, ISO date and quantity. Choose the number format explicitly.
Related tools
Supplier Quote Comparator & Purchasing Analyzer
Product Feed Generator & Validator · Inventory reconciliation · Spreadsheet comparator