---
id: varejo/estoque-cobertura
industry: Retail and e-commerce
usage: common
effort: medium
grain: Product x branch (current position) and product x branch x measurement (history)
---

# Inventory and coverage

How much of each product is actually available at each branch, DC or marketplace warehouse, how many days it lasts, and which items are heading for a stockout.

## Executive summary

**For:** Retailers, distributors and sellers with inventory across several branches, DCs or marketplace warehouses.

**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.

**Why it matters:** A stockout on an A-curve item is lost sales that never shows up in any sales report. Inventory sitting on a no-turnover item is tied-up cash. Without net available stock and coverage per branch, purchasing and transfers are decided by spreadsheet, and salespeople promise what is not there.

**Business questions it answers**

- Is product X available? At which branch or DC?
- Which A-curve products are out of stock or have coverage below the replenishment lead time?
- How many days of coverage do we have per channel, counting what is on the way?
- How much did we fail to sell because of stockouts this month?
- Which items are in excess or have no turnover, and what are they worth at cost?
- What needs to be bought now, considering open requisitions and purchase orders?

**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

## Detailed

### The problem

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.

### Business rules

1. **Separate the four quantities.** 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. (Avoid: A single "inventory" column that is sometimes total and sometimes available.)
2. **One canonical product key.** 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. (Avoid: Stripping leading zeros on one side and keeping them on the other, or extracting the code from the slug.)
3. **Channel SKU mapped in cascade.** 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. (Avoid: Discarding every SKU that does not match the catalog directly.)
4. **Unlimited and sentinel stock stay out.** 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. (Avoid: Adding a channel's sentinel and showing years of coverage.)
5. **Average sales come from sales that move inventory.** 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. (Avoid: Average sales from orders, counting cancellations and shipments.)
6. **Coverage counts what is on the way.** 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. (Avoid: Dividing only available by sales and treating zero sales as zero coverage.)
7. **Traffic light with parameters in one place.** 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. (Avoid: Different thresholds in the dashboard, the alert and the AI agent.)
8. **Rolling ABC curve by revenue.** 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. (Avoid: A fixed curve from the start of the year applied forever.)
9. **History requires state, and zero goes in.** 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. (Avoid: A history table with only products that have a positive balance.)
10. **Net requirement and lost sales.** 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. (Avoid: Calculating purchases from the balance alone, ignoring purchase orders already issued.)

### Stockout traffic light

Order of evaluation; parameters in a single table.

- **Out of assortment** → No invoicing in 90 days, inactive or sales suspended (`FORA_SORTIMENTO`)
- **No turnover** → In assortment, with a balance and no sales in 30 days (`SEM_GIRO`)
- **Stockout** → Zero available with active assortment (`RUPTURA`)
- **Critical** → Coverage below the replenishment lead time (`CRITICO`)
- **Attention** → Coverage below lead time plus the margin (`ATENCAO`)
- **Healthy** → Between the margin and target stock (`SAUDAVEL`)
- **Excess** → Coverage above target stock in days (`EXCESSO`)

### Definitions

| Metric | Definition |
|---|---|
| Available inventory | greater of (total minus reserved) and zero, by product and branch (or stock group), at the latest measurement, excluding unlimited and sentinel stock |
| In transit | quantity on its way to the location (incoming, inbound working + shipped + receiving), by product and location |
| Average daily sales | units from non-cancelled invoices that move inventory (outbound minus returns) over the last 30 days / 30, by product and branch |
| Days of coverage | (available + in transit) / average daily sales; null when there are no sales in 30 days |
| Stockout rate | active assortment products with zero available / active assortment products, by curve and day (closing measurement) |
| Days out of stock | closing measurements with zero available in the period, by product and branch |
| Estimated lost sales | average daily sales x days out of stock x average price, by product and month |
| Net requirement | open requisition minus (available + open purchase order), by product and ABC curve |
| Inventory value | quantity in stock x book cost, by product and branch |

### Pitfalls

- **No SKU matched.** 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. How to avoid: Canonical code as text, padding by the ERP rule, never on marketplace SKUs.
- **Inventory lost through the slug.** Extracting the product code from the friendly URL, instead of the reference field, left out about 80% of one country's inventory. How to avoid: Code from the product reference field; slug only to extract attributes when there is no field.
- **Years of coverage.** A marketplace returned a huge fixed quantity for unlimited stock listings and the channel's coverage shot up. How to avoid: Sentinel flag with a validated threshold and exclusion from the sum.
- **Coverage underestimated at the channel warehouse.** 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. How to avoid: In transit in the coverage numerator, as the source BI did.
- **History that did not exist.** 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. How to avoid: A notebook that appends the measurement, with zero balance, dedup and retention.
- **Zeroed balance in the ERP.** The quantity field in the product record came back as zero for many items with movements. The real balance was in the movements ledger. How to avoid: Balance from the ledger's last movement, with forward fill on days without movement.

### Acceptance test

- [ ] 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.

### Context document

```text
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>.
```

### Variations

- **Inventory in several countries:** Position by country and product-color, joined with the country's sales on the same key, and a daily FX rate for valuation.
- **Multichannel seller:** Inventory in each marketplace warehouse by base SKU, with target stock in days per channel.
- **Parts and after-sales:** Parts inventory by company and supplier, with workshop requisitions and purchase orders in the net requirement.