Inventory planning · NetSuite TD3016323

Inventory Reorder Point Analysis

Demand-based reorder points and safety stock for every item that sold in the last year, and the finding that the account's inventory problem is excess, not shortage.

Prepared 2026-09-23 · Twelve months of demand, all locations · Source: NetSuite via SuiteQL · Prompt: Inventory Reorder Point Calculator, NetSuite AI Prompt Library v1

Executive Summary

The account carries $1,079,446 of inventory across 137 items that sold in the last twelve months. Only 4 of those items have a reorder point set in NetSuite, so replenishment is running on judgment. The reorder points recommended below are computed from twelve months of fulfillment and cash sale demand at a 95% service level, and they lead to one conclusion: the problem in this account is not stockouts. It is excess.

4 items are below their recommended reorder point, all four of them at zero on hand with demand of less than one unit a week. 108 items, 79% of the range, have more than 180 days of supply on hand, and they hold $906,559, or 84% of inventory value. The slow block is leather goods and apparel: a single item, the black leather jacket, has 552 units on hand against demand of about one unit every three days, which is four years of supply and $110,400 of cash. The fast movers, mostly beauty consumables selling one to one and a half units a day, are stocked at 90 to 230 days of cover, two to five times what the recommended reorder point requires.

Methodology Overview

Calculated as: reorder point = average daily demand x lead time + safety stock, and safety stock = Z x daily demand standard deviation x square root of lead time, with Z = 1.65 for a 95% service level. Daily demand is twelve months of fulfilled and cash-sale units divided by 365. Demand variability is the standard deviation of monthly demand, converted to a daily figure. Days of supply is on hand divided by daily demand. Items are classed A, B, or C by annual demand value at average cost: 47 A items carry 80% of the value, 37 B items the next 15%, and 53 C items the last 5%.

Recommendations Table

The twelve highest-demand items. None has a reorder point set today. The recommended reorder point is the level at which a replenishment order should be placed; the current on-hand and days-of-supply columns show how far above that level the stock sits.

Priority is high where on hand is below the recommended reorder point, medium where days of supply is under 60, and low otherwise. For the top twelve, every priority is low: there is no near-term stockout risk in the fast movers. The four high-priority items are two assemblies (AS_SAF001 and AS_MBK001, both at zero on hand with open backorders of 37 and 7 units), a drop-ship end table, and a size of trousers showing negative one on hand, which is a data correction rather than a purchase.

Working Capital Analysis

If the fast movers were held at their recommended reorder point plus one order cycle, roughly 45 days of cover, the consumables alone would release a modest amount, because they are cheap. The money is in the slow block. The ten items below hold $493,965 against combined daily demand of under three units. They are not reorder-point problems; they are disposition problems, and the decision on how to move them is a pricing decision for a person.

Implementation Roadmap

Appendix: Data Lineage

Adaptations from the prompt's templates: reorderpoint, preferredstocklevel, safetystocklevel, and leadtime are not on the item record in this account; they live on inventoryitemlocations and were taken from there. The template's lead-time join uses receipt.createdfrom on the header; the link is on the receipt line. Demand uses fulfillments and cash sales rather than sales orders and invoices, so that an order is not counted twice. STDDEV is not supported in this account's SuiteQL, so variability was computed in code from monthly totals. Stockout history used open backordered quantity rather than a status filter.

SELECT tl.item, BUILTIN.DF(tl.item), i.itemtype, BUILTIN.DF(tl.class), TO_CHAR(t.trandate,'YYYY-MM'), SUM(ABS(tl.quantity)), COUNT(DISTINCT t.id)
FROM transaction t JOIN transactionline tl ON tl.transaction = t.id JOIN item i ON i.id = tl.item
WHERE t.type IN ('ItemShip','CashSale') AND tl.mainline = 'F' AND tl.taxline = 'F' AND i.itemtype IN ('InvtPart','Assembly') AND tl.quantity < 0
  AND t.trandate > ADD_MONTHS(TRUNC(SYSDATE), -12) GROUP BY ...

SELECT i.id, i.itemid, i.displayname, i.itemtype, i.averagecost, i.cost,
  SUM(NVL(iil.quantityonhand,0)), SUM(NVL(iil.quantityonorder,0)), SUM(NVL(iil.quantitybackordered,0)),
  MAX(iil.reorderpoint), MAX(iil.preferredstocklevel), MAX(iil.safetystocklevel), MAX(iil.leadtime)
FROM item i LEFT JOIN inventoryitemlocations iil ON iil.item = i.id
WHERE i.itemtype IN ('InvtPart','Assembly') AND i.isinactive = 'F' GROUP BY ...

SELECT tl.item, po.entity, po.trandate, r.trandate, ABS(tl.quantity)
FROM transaction r JOIN transactionline tl ON tl.transaction = r.id AND tl.mainline = 'F' JOIN transaction po ON po.id = tl.createdfrom
WHERE r.type = 'ItemRcpt' AND po.type = 'PurchOrd' AND r.trandate > ADD_MONTHS(TRUNC(SYSDATE), -12)

Appendix: Assumptions and Formulas

Confidence: high on demand and days of supply; medium on the reorder point levels, because lead time is assumed; high on the excess finding, which does not depend on lead time at all. Service level, the working capital trade-off, and every disposition decision are flagged for human review.