A reorder point is not “last month’s usage plus a buffer.” It is the inventory position at which a replenishment order must be released so expected demand during supplier and inbound lead time can be covered at an explicitly chosen service level. For branded merchandise, the calculation also has to respect case packs, minimum order quantities, campaign spikes, warehouse transfers, and items approaching retirement.
This guide gives merchandise, marketing operations, procurement, and fulfillment teams a repeatable model for making that decision at the SKU–warehouse level. The companion workbook is designed for planning, not as an automatic purchase order system; every recommendation should still be checked against current supplier commitments, campaign calendars, storage limits, and finance approval.
Download the localized reorder calculator
Download the 2026 merchandise inventory reorder calculator The workbook contains six working sheets: Read Me, Inputs, SKU-Warehouse Data, Demand History, Calculator, and Scenarios. Yellow input cells are editable. Calculated columns are formula-driven and should remain unchanged.
| Input | What it controls | Practical guidance |
| Target service level | Probability used to derive the standard-normal safety factor | Start with a tier by business impact, not one universal target |
| Review period | Days between formal replenishment reviews | Use the real operating cadence |
| Demand lookback | Historical window used for average and variability | Include enough cycles to avoid a one-campaign sample |
| Lead time and variability | Supplier production plus inbound transit uncertainty | Use observed receipt data when available |
| Case pack and MOQ | Ordering constraints | Confirm by SKU and supplier before release |
| On hand, on order, reserved | Net inventory position | Align definitions across warehouses |
| Unit cost | Inventory value and scenario exposure | Use landed cost when it is consistently maintained |
| Retirement date | Run-down logic for items leaving the program | Pair with a successor or disposition plan |
The outputs are lead-time demand, safety stock, reorder point, net available inventory, base order quantity, rounded recommended quantity, days of cover, projected stockout date, inventory value, and an action flag. Sample rows are illustrative; they are not industry benchmarks or supplier commitments.
Use the formulas as a decision model
The calculator uses a combined-variability safety-stock approach: Safety Stock = z × √((Average Lead Time × Daily Demand Standard Deviation²) + (Average Daily Demand² × Lead-Time Standard Deviation²)) Here, z is the safety factor derived from the target service probability. Microsoft documents the inverse standard-normal function used to obtain that factor in NORM.S.INV, while the NIST normal-distribution reference explains the distributional basis. Reorder Point = Average Daily Demand × Average Lead Time + Safety Stock Net Available = On Hand + On Order − Reserved If net available is at or below the reorder point, the base order closes the gap to a target stock position that includes the review period. The final recommendation is rounded up to the supplier’s case pack and must satisfy the MOQ. The workbook follows Microsoft’s ROUNDUP documentation.
The formula supplies consistency; the operating review supplies judgment. A 95% service input does not promise that 95% of every order will ship on time. It is a modeling assumption tied to a normal-distribution approximation and the quality of the demand and lead-time data. Intermittent demand, launch events, conferences, and bulk internal allocations can violate that approximation. Treat the output as a governed recommendation, not a guarantee.
Prepare SKU–warehouse data before calculating
Start with one row for every active SKU at every stocking location. A single global balance hides whether one warehouse is exposed while another is overstocked. Standardize SKU identifiers, warehouse codes, unit of measure, time zone, inventory ownership, and reservation rules before loading data. Reconcile on-hand, on-order, and reserved quantities to the same cutoff. On order should include only quantities backed by an open supplier or transfer commitment. Reserved should reflect demand that operations intends to protect, not every draft campaign request. If systems disagree, log the source and the reconciliation owner. Demand history should capture issue or ship quantities by date, SKU, and warehouse. Exclude test orders, duplicate postings, and known reversals. Keep genuine campaign demand, but label events so planners can decide whether they should influence the forward forecast. The workbook’s multi-criteria logic follows Microsoft’s COUNTIFS documentation. Calculate observed supplier lead time from purchase-order release to usable receipt. Separate production, international transit, customs, and receiving delay when the data is available. A supplier’s quoted lead time is a useful starting point, but observed variability is often the more important safety-stock input.
Run the monthly review in a fixed order
- Freeze a data cutoff and reconcile inventory by location.
- Refresh demand history and flag campaign anomalies.
- Update average lead time and lead-time variability from recent receipts.
- Confirm case packs, MOQs, landed cost, and item retirement dates.
- Review recommendations beginning with projected stockouts and high-value exposure.
- Test demand-surge and lead-time-shock scenarios.
- Check whether a warehouse transfer is faster and cheaper than a supplier reorder.
- Record the final action, owner, approval, and expected receipt date. The Scenarios sheet does not overwrite the base calculation. It compares baseline, demand surge, lead-time shock, combined stress, and retirement run-down assumptions. A scenario is useful when it changes a decision: ordering earlier, splitting a shipment, transferring stock, or accepting a lower service tier. A good review produces an exception list, not a blanket instruction to buy. Focus on stockouts inside lead time, high-value excess, obsolete merchandise, and items whose order constraints create material cash or storage exposure.
Handle multi-warehouse and retirement exceptions
When should inventory be transferred instead of reordered?
Transfer when another location has usable surplus above its own protected demand, the transfer can arrive before the destination’s projected stockout, and total transfer cost is lower than the avoided expedite or new-order cost. Include handling, freight, customs, system movement, and the risk of weakening the source warehouse.
How should a new SKU with little history be planned?
Use an analogous item, confirmed campaign demand, and a deliberately conservative initial buy. Record the analogy and confidence level. Review frequently until enough real demand and lead-time observations exist; do not manufacture precision by filling history with zeros.
What changes when an item is being retired?
Stop replenishment earlier than the standard formula when the projected usable life is shorter than the coverage created by an MOQ. Review outstanding campaigns, replacement timing, employee-store demand, and disposition options. The retirement date is a decision constraint, not merely a note.
Should every SKU have the same service level?
No. Tier service levels by business consequence: core employee-store items, event-critical kits, executive gifts, seasonal merchandise, and long-tail accessories have different stockout costs. Document who may change a tier and how often it is reviewed.
Add governance around the spreadsheet
Merchandise operations owns data quality and recommendations; procurement owns supplier terms and purchase-order release; finance owns cash and write-off thresholds; marketing owns campaign demand; warehouse partners confirm receipts and usable inventory. One person may hold several roles, but decisions should remain visible.
- Inventory cutoff reconciled across all warehouses
- Demand anomalies labeled, not silently removed
- Supplier lead-time data refreshed
- Case pack, MOQ, and unit cost confirmed
- Campaign commitments included
- Retirement and replacement dates reviewed
- Transfers considered before expedites
- Scenario result documented
- Final order or no-order decision approved
- Next review date assigned Keep an audit trail of input snapshots and final decisions. Do not overwrite prior months. Versioned snapshots let the team compare what was known at the time with what later happened, which is essential for improving service targets and supplier assumptions.
Segment service levels instead of using one target
A service level is a business choice about the cost of a stockout, not a decoration on a formula. Begin by separating items into a few operational tiers. A core item that supports an always-on employee store may deserve a higher target than a seasonal accessory. An item committed to a dated executive event may need an earlier decision even when its average volume is low. Conversely, a long-tail size or color can carry a lower target if an approved substitute exists. A practical tier policy should define five things: the eligible items, target service probability, review frequency, escalation point, and approving role. It should also say how the team treats substitutes. If a black medium hoodie and a navy medium hoodie are interchangeable for a particular program, the combined availability may matter more than either SKU in isolation. If branding, event, recipient, or contractual requirements make them non-substitutable, keep them separate. Avoid changing targets merely to force a preferred order quantity. Service tiers should be reviewed on a regular governance cycle or when business consequences change. A campaign launch, supplier exit, material change, or warehouse closure can justify a temporary override, but record its owner, reason, start date, and expiry.
| Suggested tier | Typical use | Review emphasis |
| Mission critical | Dated programs where a stockout breaks a committed experience | Highest visibility, frequent review, explicit contingency |
| Core | Always-on merchandise with consistent demand | Stable service target and routine replenishment |
| Campaign | Items tied to a known launch or event | Event forecast, cutoff date, remaining usable life |
| Long tail | Low-volume variants or optional accessories | MOQ exposure, substitutes, and consolidation |
| Retirement | Items being replaced or removed | Sell-through, transfer, donation, recycling, or write-off |
Interpret the outputs with a worked example
Assume a tumbler at the East warehouse averages 8 units of daily demand with a daily standard deviation of 3. The observed average lead time is 30 days with a standard deviation of 5 days, and the selected service factor is approximately 1.65. Expected lead-time demand is 240 units. The combined-variability formula produces safety stock of roughly 75 units, so the reorder point is about 315 units. If the warehouse has 210 on hand, 80 on order, and 40 reserved, net available inventory is 250 units. That is below the reorder point, so the calculator recommends replenishment to the target position, which also includes the next review period. If the base recommendation is 145 units, the case pack is 24, and the MOQ is 192, the actionable recommendation is 192 units—not 145—because purchasing constraints govern the order that can actually be released. Now challenge the answer. If another warehouse has 120 surplus units and a transfer can arrive in four days, moving inventory may cover the risk with less cash and no new MOQ. If a 500-person event has just been approved, the average-demand model may materially understate need. If the item retires in 45 days, buying 192 units may create obsolete inventory. The calculation identifies the decision point; scenario and exception review determine the decision. Do not use projected stockout date in isolation. A date based on average demand can create false comfort when demand is lumpy. Pair it with the service tier, reserved quantity, campaign calendar, and lead-time risk. Likewise, days of cover is useful for comparison, but it does not account for order constraints or the timing of inbound stock unless those elements are explicitly included.
Test the model and improve it over time
Run simple back-testing each month. Take a prior cutoff, rebuild the recommendation using only information that was available then, and compare it with actual demand, receipt timing, stockouts, expedites, and ending inventory. Track whether misses were caused by demand, lead time, data quality, supplier constraints, or an unrecorded business event. This prevents the team from blaming every miss on the service factor. Useful operating measures include stockout days by tier, forecast bias, average absolute demand error, planned versus actual lead time, expedite cost, transfer cost, obsolete inventory value, and the percentage of recommendations reviewed on time. Measures should diagnose the process; they should not reward over-ordering. A perfect in-stock result achieved through excessive obsolete inventory is not a successful program. When results repeatedly miss in the same direction, change the relevant assumption deliberately. Persistent late receipts call for a lead-time update or supplier action. Consistently understated campaign demand calls for better event intake. Excess inventory concentrated in case-pack rounding calls for supplier negotiation or assortment rationalization. Unexplained inventory differences call for transaction and warehouse controls before any statistical refinement. Before production use, test the workbook with at least one normal-demand SKU, one intermittent SKU, one high-MOQ SKU, one multi-warehouse item, and one retiring item. Confirm that formula outputs update when inputs change, that blank or invalid inputs are visible, and that no external workbook links or macros exist. Protect the master version, distribute a controlled copy for each review period, and record the workbook version beside the final decision.
Avoid the failure modes that create false precision
The most common failure is mixing units. A case, each, carton, kit, and pallet are not interchangeable. Store inventory, demand, MOQ, and case pack in the same base unit, then show the purchasing unit separately. A recommendation of 96 means nothing if one system treats it as eaches and another as cases. The second failure is counting inbound stock too early. Include an open order only when its quantity, destination, and expected usable date are credible. A purchase order that has not entered production, a shipment held in customs, or inventory awaiting quality inspection should not automatically offset an imminent shortage. Add a risk flag when the receipt date falls after the projected stockout date. The third failure is averaging away a real event. Weekly or monthly averages can hide a concentrated shipment requirement. Record known campaigns and reservations explicitly, with request owner, approval status, required-by date, warehouse, and cancellation rule. When the event is not approved, keep it in a separate scenario instead of silently mixing it into committed demand. The fourth failure is allowing one warehouse’s excess to conceal another warehouse’s shortage. Keep calculations location-specific, then run a network review that tests transfers. This sequencing preserves accountability: first show the local exposure, then decide whether network inventory can resolve it. The fifth failure is treating an old formula as current policy. Review service tiers, lead-time windows, supplier constraints, and retirement rules at least quarterly and whenever the program changes materially. Record every override. A transparent conservative assumption is safer than a sophisticated number whose source and owner cannot be explained.
Define approval thresholds before the review
The team should know which recommendations can be approved routinely and which require escalation. Set thresholds for order value, excess days of cover, expected write-off exposure, expedite cost, and exceptions to the normal service tier. Require finance review when the order exceeds the agreed cash threshold, procurement review when terms or MOQ change, and marketing confirmation when the order depends on an unapproved campaign. A no-order decision also needs ownership when the calculator shows risk. Record whether the team will accept the stockout, use a substitute, transfer inventory, split the launch, or change the recipient promise. This closes the gap between a spreadsheet alert and an accountable operating decision.
Publish one concise decision record
After every review, publish a short internal record containing the highest-risk SKUs, the decision for each exception, unresolved dependencies, and the next check date. Link it to the exact input snapshot and workbook version. If an order is cancelled, resized, split, transferred, or delayed, update the outcome while preserving the original approval so later back-testing can reconstruct the sequence. This record should also state the source of on-hand inventory, the supplier commitment date, any campaign reservation, and the reason a planner overrode the recommendation. A concise, consistent summary helps new team members, finance, procurement, and warehouse partners understand the decision without reopening the full model. It also prevents the same exception from being debated repeatedly in separate meetings.
Connect reorder planning to the full merchandise program
A reorder calculator works best inside a broader operating model. The corporate merchandise program governance guide explains how sourcing, inventory, and fulfillment responsibilities fit together. Compare network trade-offs in global swag fulfillment models. Teams changing platforms or warehouses should coordinate the controls in the company-store migration cutover guide, while finance can align cash exposure with the 2026 corporate gifting budget calculator. The practical conclusion is simple: order when the location-specific inventory position crosses a documented threshold, then round the recommendation through real supplier constraints and challenge it against campaigns, transfers, cash, and retirement. That process is more defensible than intuition and more adaptable than a fixed months-of-stock rule. If your team wants to connect those decisions to a branded merchandise program, Giftpack Swags can support the execution layer across merchandise selection, program coordination, and fulfillment. Keep policy, budget, service-level, and purchasing approval with your organization; use Giftpack to help operationalize the approved program.