Inventory position from ERP, e-commerce and marketplaces, plus invoiced sales
Net available, single product key, average sales and stockout parameters
Days of coverage and stockout status by product and branch
Replenishment dashboard, availability AI and alerts
What it solves
- Available to sell is what is left after reservations, without doubling inventory or counting unlimited stock.
- Each product has days of coverage and a single status: stockout, critical, attention, healthy, excess or no turnover.
- The time series keeps the days at zero, which is what really measures stockouts and turnover.
Business questions it answers
Ask your agent these questions once the case is live.
What you need
- ERP, e-commerce and marketplaces connected to Nekt, with inventory position and invoiced sales
- The branch register, including which branches share the same physical inventory
- Replenishment lead time by product or group, and target stock in days
What you get
- A current position table by product and branch with total, reserved, available and in transit
- A history with two measurements per day, including zero balances
- Stockout traffic light with ABC curve, coverage and estimated lost sales
- Freshness monitor and a catalog exceptions table
Where to activate
Traffic light by product, branch and channel, daily stockout trend and net requirement by curve.
A salesperson asks by product name and gets available stock by branch, with the time of the latest measurement.
An A-curve item that went into stockout or critical, and an inventory load that passed the limit without updating.
The current position comes out fast. The product key across sources, mirror branches and the stateful history are what take time. The detailed view has the business rules, the pitfalls and the acceptance test. Or send everything straight to your agent with the Nekt MCP.
Each source names inventory its own way (total, reserved, committed, available, sellable, inbound) and identifies the product another way: a code with leading zeros in the ERP, a SKU with color and size embedded in e-commerce, a SKU with a suffix in the marketplace warehouse. Mirror branches double the balance and unlimited stock sentinels inflate the total. The current position has no past: without storing the series, there is no way to measure how many days the item sat at zero.
How it is usually done, and the rule
Each row is a mistake that already happened in practice.
Store them in their own columns: total, reserved (committed, orders already sold and not shipped), available = greater of (total minus reserved) and zero, and in transit (incoming, inbound, being restocked). In Shopify, read the quantities by name (available, committed, incoming). In the Amazon warehouse, available is fulfillableQuantity and in transit is the sum of inbound working, shipped and receiving. Never mix in transit with available.
Standardize the code before joining inventory with sales and the catalog: left-pad with zeros to the ERP length (18 positions in SAP; 6 in Protheus, only for 4 to 6 digit codes), never applied to marketplace SKUs. Extract the code from the product reference field, not from the URL slug. When the SKU embeds color and size, extract product and color with a documented expression and aggregate without size if sales are compared at that level. Kits get their own prefix (P for product, K for kit) so they do not collide with products.
A marketplace SKU resolves to the product in order: alias table, direct code, and a regular expression for warehouse suffixes (for example, the FBA suffix). Store which rule resolved it. An unmapped SKU with stock stays visible as "unmapped"; an unmapped SKU with zero (inactive listing) leaves the dashboard.
Products flagged with unlimited stock (hasunlimitedquantity true) leave the balance. Some marketplaces return a huge fixed quantity as an unlimited sentinel (around 10,000 or more): flag it and exclude it from the sum, with the threshold validated with the client. Mirror branches of the same physical inventory aggregate by stock group, as in the AI assistant.
Average daily sales use line items from non-cancelled invoices whose operation moves inventory: outbound adds and returns (inbound) subtract; operations that do not move inventory stay out. 30-day window for the average; keep 400 days of sales to compare year over year. When the channel provides sales ready-made (unitsShippedT30 in the Amazon warehouse), use the channel's figure.
Days of coverage = (available + in transit) / 30-day average daily sales. In transit goes in the numerator: for a channel that uses the marketplace warehouse, ignoring it underestimates days of coverage. With no sales in 30 days, coverage is null and the item goes to the no-turnover status, never to infinity or zero.
Active assortment = invoiced in the last 90 days, not inactive and sales not suspended. Otherwise: out of assortment. Within it: stockout (zero available), critical (coverage below the product's replenishment lead time, with the group's lead time as fallback), attention (below lead time plus a margin), healthy, excess (above target stock in days) and no turnover. All thresholds live in a parameters table or block, never scattered across the query.
Rank products by revenue over the last 90 days and accumulate: up to 80% of revenue is A, up to 95% is B, the rest is C. Recompute every day on the rolling window. A product with no sales in the window has no curve ("SEM_CURVA"), not C.
A SQL query in Nekt is stateless: to build a series, use a notebook that appends the snapshot. Two measurements per day (opening in the morning and closing at night, Brasília time), classified by the hour before or after noon so it survives schedule changes. Store zero balances. A daily step after the close purges beyond retention and leaves one row per product, branch, day and measurement. Without a snapshot, rebuild the day from the inventory ledger: the day's last movement gives the balance and days without movement inherit the previous one.
Net requirement = open requisition minus (available + open purchase order), by product and curve. Estimated lost sales = average daily sales x days out of stock in the period x average price, only for active assortment items. Inventory value = quantity x book cost. A product with a balance and no catalog record goes to an exceptions table for the catalog team to fix, outside analytical consumption.
Stockout traffic light
Order of evaluation; parameters in a single table.
Definitions
Pitfalls that already happened
A query stripped leading zeros from the code on one side while the ERP inventory kept them. Practically no SKU matched inventory until the key became text with zeros.
Canonical code as text, padding by the ERP rule, never on marketplace SKUs.
Extracting the product code from the friendly URL, instead of the reference field, left out about 80% of one country's inventory.
Code from the product reference field; slug only to extract attributes when there is no field.
A marketplace returned a huge fixed quantity for unlimited stock listings and the channel's coverage shot up.
Sentinel flag with a validated threshold and exclusion from the sum.
The calculation used only available stock. Once in-transit stock to the marketplace warehouse was included, that channel's coverage rose by about five days and some items left critical.
In transit in the coverage numerator, as the source BI did.
The history was built as a SQL query and each run overwrote the previous one. There was no way to say how many days an item sat at zero.
A notebook that appends the measurement, with zero balance, dedup and retention.
The quantity field in the product record came back as zero for many items with movements. The real balance was in the movements ledger.
Balance from the ledger's last movement, with forward fill on days without movement.
Acceptance test
The case is done when every item passes.
- For 10 products and 3 branches, available matches the ERP or platform inventory screen at the same time (zero difference).
- No product with unlimited or sentinel stock enters the sum, and the balance of a group of mirror branches equals that of one branch in the group.
- The history has at most one row per product, branch, day and measurement, and includes the zero balance rows.
- Every active assortment product has exactly one traffic light status, and the sum of statuses equals the total number of products.
- For a sample product, coverage recomputed by hand from available, in transit and 30-day sales matches the table.
Agent package
Paste into Claude, ChatGPT or any client with the Nekt MCP connected.
Inventory and coverage - Current position: one row per product and branch. History: one row per product, branch, day and measurement (opening or closing, Brasília time). - Available = greater of (total minus reserved) and zero. In transit is a separate column. Unlimited and sentinel stock stay out of the sum. - Mirror branches aggregate by stock group, never summed. - The product code is canonical text with the ERP padding rule. A marketplace SKU resolves by alias, direct code or warehouse suffix. - Average daily sales = units from non-cancelled invoices that move inventory over the last 30 days / 30. - Days of coverage = (available + in transit) / average daily sales. No sales in 30 days: no turnover, null coverage. - Statuses: FORA_SORTIMENTO (out of assortment), SEM_GIRO (no turnover), RUPTURA (stockout), CRITICO (critical), ATENCAO (attention), SAUDAVEL (healthy), EXCESSO (excess). Parameters in the parameters table. - ABC curve by cumulative 90-day revenue: A up to 80%, B up to 95%, C the rest. - Stockouts over a period: use the history of closing measurements, not the current position. - Say whether the product does not exist in the catalog or exists and is out of stock. - State the date and time of the latest measurement in every availability answer. Client exceptions: - <client>: branches <A> and <B> in stock group <group>. - <client>: target stock <days> days; attention margin <days> days; default replenishment lead time <days> days. - <client>: sentinel threshold for channel <channel> = <quantity>.
Build the "Inventory and coverage" case in my Nekt workspace following the rules at https://academy.nekt.com/en/industrias/varejo/estoque-cobertura.md. 1) Find the source tables and confirm the columns the rules use. 2) Create a transformation at the stated grain applying every rule. 3) Run the acceptance test and show me the result of each item. 4) Create the case's context document in the workspace, leaving my clients' exceptions open for me to fill in.
Variations
Position by country and product-color, joined with the country's sales on the same key, and a daily FX rate for valuation.
Inventory in each marketplace warehouse by base SKU, with target stock in days per channel.
Parts inventory by company and supplier, with workshop requisitions and purchase orders in the net requirement.