Inventory: excess, slow rotation and stock-out risk · Ferretería El Faro
Example business: Ferretería El Faro — a hardware store with a sales floor and one warehouse, 3,120 active references.
The ten sections organize this illustrative example. Each product report has its own scope and format.
Analysis overview
Business, locations and exact stock date.
- Business
- Ferretería El Faro (fictitious data)
- Locations
- Sales floor and main warehouse.
- Stock date
- June 30, 2025
- Sales used for rotation
- July 2024 – June 2025 (12 months)
Executive summary
Inventory value and main alerts.
Valued inventory is USD 268,400. 23% of that capital (USD 61,300) sits in 412 references with more than 180 days without a sale, while 57 high-rotation references have coverage below their replenishment lead time and concentrate the stock-out risk.
- Overall rotation is 3.4 times per year; median coverage is 64 days.
- Five product families concentrate 71% of the excess capital.
- The replenishment proposal covers only references with a known lead time.
Quality and reconciliation
Match between references, movements and stock.
Stock reconciled against the movements file with a 0.8% net difference. 96 references with negative stock or missing cost were excluded from the valued analysis and are listed in the annex. Lead times come from the last three purchase orders per supplier.
- 3,120 active references; 3,024 included in the valued analysis.
- No pending orders are recorded in the file: coverage does not net them out.
- Internal consumption (use in services) is not separated from sales.
Key indicators
Valued inventory, rotation and coverage, depending on available data.
Valued inventory
USD 268,400
Annual rotation
3.4
Median coverage
64
Days of sales covered
Excess capital
USD 61,300
References 180+ days without a sale
Excess and slow rotation
References and associated capital.
Any reference with more than 180 days without a sale or more than 360 days of coverage was flagged as excess. The five families with the most idle capital are shown below; the per-reference detail is in the annex.
Composition of excess capital
Each family’s share of the USD 61,300 excess.
- Paints and solventsUSD 18,20030%
- Faucets and sanitary wareUSD 11,40019%
- Power toolsUSD 6,80011%
- Locks and hardwareUSD 4,3007%
- LightingUSD 2,8005%
- Other familiesUSD 17,80029%
Explore with the pointer or keyboard. Tap a value to keep its detail visible.
Insufficient coverage
Products that require attention and assumptions used.
57 references have coverage below their supplier’s replenishment lead time. The demand assumption is the average daily sale of the last 12 weeks; seasonal references are flagged in the annex.
Replenishment or redistribution proposal
Only when the necessary data is available.
The proposal covers the 41 at-risk references with confirmed lead time and cost. For the remaining 16, supplier data is missing, and they are listed as pending validation instead of proposing quantities.
AI analysis
Interpretation of the imbalances and management alternatives per product group.
Key findings
Excess is concentrated and old
71% of the excess capital sits in five families, and their average age exceeds 200 days without a sale. This is not a general purchasing problem but an accumulation of specific decisions.
Evidence: Excess by family table (element 5).
Stock-out risk sits in the best sellers
The four references with the lowest relative coverage are among the year’s 40 best sellers. A stock-out there hits daily sales, not marginal ones.
Evidence: Insufficient coverage table (element 6).
Calculated coverage ignores in-transit orders
The file does not record pending orders, so part of the listed risk may already be covered by purchases in transit.
Evidence: Quality and reconciliation section (element 3).
Business interpretation
Idle capital could finance the replenishment of what does rotate: liquidating part of the excess frees more than the at-risk replenishment costs (USD 4,400 in the excerpt). The decision this report supports is a targeted clearance campaign plus a coverage-based purchasing rule for high-rotation references.
Hypotheses and limitations
- The paint excess may come from a 2024 volume purchase; reviewing that year’s orders would confirm whether it was a price-driven decision.
- Part of the “slow rotation” in locks may be internal consumption not recorded as sales; separating that use is needed to validate it.
Prioritized recommendations
Confirm in-transit orders before buying
Validate open orders with suppliers for the 57 at-risk references. This prior check prevents buying the same thing twice.
Targeted clearance of the two most excessive families
Paints and faucets hold USD 29,600 idle. A controlled-discount clearance turns capital that loses value in the warehouse into cash.
Coverage-based purchasing rule for the top 100
Set reorder point = daily demand × (lead time + safety margin) for the 100 best-selling references, and review it quarterly.
Proposed follow-up
Action plan
Priorities and validations needed before buying or moving merchandise.
Methodology and annexes
Calculations, assumptions and detail per reference.
Rotation = 12-month cost of sales ÷ average valued inventory. Coverage = stock ÷ 12-week average daily sales. Excess = more than 180 days without a sale or more than 360 days of coverage. Thresholds are parameters and can be adjusted.
- Annex A: per-reference detail (3,024 rows) with rotation and coverage.
- Annex B: excluded references and reconciliation differences.
- Annex C: full replenishment proposal with per-supplier assumptions.