Bibitor LLC is a retail wine and spirits company operating ~80 stores across the fictional state of Lincoln. Annual sales range between $420–450M, with COGS around $300–350M. Despite strong revenue, store-level shrinkage (unexplained inventory loss) was increasing, reducing profit margins and creating stock imbalances: some stores were overstocked, while others frequently ran out of top-selling products.
The Challenge: Identify where inventory is disappearing, what’s tying up capital, and which operational failures are driving both issues.
Despite strong revenues, Bibitor's margins are being quietly eroded from two directions:
65% of all shrinkage is localized in four stores — notably Store 77 and Store 25 — which also carry the highest excess inventory levels. Vendor fulfillment inaccuracies compound the problem, with William Grant & Sons Inc alone accounting for a 35K-unit delivery variance.
Prioritizing targeted audits at high-risk stores and enforcing vendor compliance controls will provide the fastest path to reducing losses, improving inventory turnover, and releasing trapped capital.
View Dashboard here.
Four locations — Stores 77, 25, 63, and 47 — account for approximately $129K, or 65%, of total annual shrinkage ($200K). Combined exposure breaks down into $94K in direct cost and $35K in lost margin, confirming that financial impact extends beyond inventory write-offs. Store 77 is the highest-risk location at $53K in exposure, likely amplified by its large SKU assortment (7,344 SKUs).
Business Implication: Shrinkage is concentrated, not systemic. Because unrealized margin exceeds direct write-offs, the true financial impact is understated in accounting records — and the concentration means leadership can target a small set of stores rather than applying broad, inefficient interventions across the network.
While high-value products show a slightly higher average loss rate, low-value items account for 88% of total shrinkage value ($233K of $264K) — driven by volume, not unit price.
Business Implication: This pattern points to operational failures — receiving errors, miscounts, breakage, and recording gaps — as the primary loss drivers. Interventions focused solely on high-value theft prevention would address less than 12% of total exposure. Broader process controls are required to materially reduce shrinkage.
Across the network, 8,198 SKUs exceed 90 days of supply, tying up approximately $28M in working capital. Exposure is highly concentrated — four stores (77, 26, 49, and 25) alone account for ~$5.5M, with Store 77 as the single largest contributor at nearly $2M in slow-moving stock.
Liquidity risk is most severe in Stores 64, 77, 40, and 49, where inventory-weighted average days of supply exceed 700 days, indicating prolonged stagnation.
Business Implication: Inventory inefficiency poses a larger structural risk than shrinkage. At an 8% cost of capital, excess stock generates ~$2.24M in annual opportunity cost — and unlike shrinkage, this exposure compounds over time as holding periods increase damage and obsolescence risk.
Inventory variance is concentrated among specific vendors rather than occurring randomly. William Grant & Sons Inc shows cumulative under-deliveries exceeding 35,000 units, while Alisa Carr Beverages over-delivered by approximately 65% relative to ordered quantities. These deviations are consistent across multiple products, indicating persistent fulfillment inaccuracies.
Business Implication: Vendor delivery reliability is a direct driver of inventory imbalance. Under-delivery creates stockouts and lost sales, while over-delivery increases capital lock-up. Because variance is concentrated among a small number of suppliers, this represents a manageable counterparty risk rather than a systemic supply-chain failure.
Losses cluster geographically. Mountmend (Store 77) and Paentmarwy (Store 25) together account for over $64K in shrinkage. In contrast, locations such as Eanverness (Store 49) and Pitmerden (Store 40) exhibit significant overstock relative to recorded deliveries.
Business Implication: Operational failures are not uniform across the network. Loss-heavy cities indicate breakdowns in inventory control or security, while overstock-heavy locations suggest receiving and recording failures. This heterogeneity invalidates one-size-fits-all solutions and highlights the need for differentiated operational diagnosis.
| Metric | Value | Business Context |
|---|---|---|
| Total Shrinkage Loss | $199K | Annual revenue impact across 41 stores |
| Capital Trapped | $28M | Total value of inventory exceeding the 90-day supply threshold |
| Worst Performing Store | Store 77 | Accounts for $53K in loss; ranks 1st in shrinkage despite 6th in sales |
| Top Vendor Issue | 35.9K | Grant & Sons Inc — highest under-delivery variance across all vendors |
| Loss Concentration | 65% | $129K of total losses are localized within just 4 key stores |
| Product Pattern | 88% | Loss is dominated by low-value items, suggesting process errors over theft. |
Tools: PostgreSQL • DBeaver • Tableau
Architecture: Raw → Clean → Gold (medallion approach)
Data Flow:
Raw CSV Files
↓
Clean Layer (Views: deduplication, validation, type casting)
↓
Gold Schema (Star schema with materialized aggregations)
Why This Architecture: The Gold schema ensures
consistent business definitions across all downstream analysis—mimicking
the semantic layer approach used by enterprise retail analytics teams.
Detailed technical documentation here.
Downlaod Dataset
bibitor_llc/ ├── docs │ ├── images │ ├── results │ ├── 01_schema_layers.svg │ └── 02_ERD_bibitor_llc.svg ├── sql │ ├── 01_ddl │ ├── 02_load │ ├── 03_test │ ├── 04_analytics │ └── db_init.sql ├── License.txt ├── README.md ├── Retail Shrinkage & Inventory Analysis | Bibitor LLC.twb └── data_catalog.md
This project is licensed under the MIT License. See the License file for details.