ABC/XYZ, inventory, market share, TAM/SAM/SOM, CAGR, LFL and business forecasting

ABC analysis

WITH r AS (
  SELECT p.product_name, SUM(oi.quantity * oi.unit_price * (1 - oi.discount_pct)) AS revenue
  FROM orders o JOIN order_items oi ON oi.order_id = o.order_id JOIN products p ON p.product_id = oi.product_id
  WHERE o.status = 'completed' AND o.order_date >= '2025-01-01' GROUP BY p.product_name),
c AS (SELECT *, SUM(revenue) OVER (ORDER BY revenue DESC ROWS UNBOUNDED PRECEDING) / SUM(revenue) OVER () AS cum_share FROM r)
SELECT CASE WHEN cum_share <= 0.80 THEN 'A' WHEN cum_share <= 0.95 THEN 'B' ELSE 'C' END AS class,
       COUNT(*) AS products, ROUND(100 * SUM(revenue) / (SELECT SUM(revenue) FROM r), 1) AS revenue_share_pct
FROM c GROUP BY class ORDER BY class;

Interpretation. About 30% of products (31 of 108) earn 80% of revenue — a Pareto pattern. Decisions: A items get tight stock control and frequent reviews; C items simplified (or delisted if also low-margin).

XYZ analysis

Worked example. Monthly units 10, 12, 8, 10 → mean 10, SD (sample) ≈ 1.63, CV ≈ 0.16 → X. Monthly units 0, 30, 2, 8 → mean 10, SD ≈ 13.6, CV ≈ 1.36 → Z.

ABC/XYZ matrix

Common mistakes. Omitting zero-demand months (understates CV); mixing periods with stock-outs (true demand unobserved); never revisiting classes.

Stock and inventory analysis

SELECT COUNT(DISTINCT product_id) AS products_with_stockouts, COUNT(*) AS stockout_months
FROM inventory_monthly WHERE closing_stock = 0;

Stock-out months hide demand: sales recorded as zero (or low) when nothing was on the shelf. Forecasts trained on them under-predict.

Market share

Formula. Value share = company sales ÷ total market sales (same scope, period, currency); volume share uses units. Relative share = your share ÷ largest competitor's share. Worked example. Orion Baku electronics revenue 0.9M; Baku electronics market 52M ± 10% → share ≈ 1.7% (range 1.6–1.9%). A "market leader" claim would need share data on competitors. Mistake. Mismatched scope — comparing your online + store sales with an offline-only market estimate.

TAM / SAM / SOM

• TAM (total addressable market): total demand for the category if you had 100% share.
• SAM (serviceable available market): the part your offer can reach (regions, segments, channels).
• SOM (serviceable obtainable market): what you can realistically capture in a time frame given competition and capacity.

Worked example. 1.2M households × 60% broadband-relevant × 25 USD/month × 12 = TAM 216M/yr. Serviceable in 3 regions with coverage = 40% → SAM 86.4M. Achievable share in 3 years 8% → SOM ≈ 6.9M. Cross-check bottom-up: sales capacity 1,500 activations/month × 36 months × retention × ARPU — and reconcile.

CAGR

Formula. CAGR = (End ÷ Start)^(1 ÷ n) − 1, with n = number of periods between start and end. Worked example (Orion revenue). 2023: 917,710 → 2025: 1,144,527; n = 2 → (1,144,527 ÷ 917,710)^0.5 − 1 = 1.2472^0.5 − 1 ≈ 11.7% per year. (Actual years: +10.9% then +12.5%.) Mistakes. Using n = number of years listed (3) instead of intervals (2); presenting CAGR as if growth was smooth; endpoint sensitivity (choosing a weak base year inflates CAGR).

LFL (like-for-like)

Definition used here (common retail convention). LFL growth compares only stores that traded for the full comparison periods, removing the effect of openings and closures. (The founder's organisation-specific LFL rule is awaiting confirmation; when supplied, it replaces this convention.)

WITH rev AS (
  SELECT o.store_id, strftime('%Y', o.order_date) AS y, SUM(oi.quantity * oi.unit_price * (1 - oi.discount_pct)) AS r
  FROM orders o JOIN order_items oi ON oi.order_id = o.order_id
  WHERE o.status = 'completed' AND o.order_date >= '2024-01-01' GROUP BY 1, 2),
lfl AS (SELECT store_id FROM stores WHERE channel = 'store' AND open_date <= '2024-01-01' AND close_date IS NULL)
SELECT 'LFL stores' AS scope,
       ROUND(SUM(CASE WHEN y = '2024' THEN r END)) AS rev_2024, ROUND(SUM(CASE WHEN y = '2025' THEN r END)) AS rev_2025,
       ROUND(100 * (SUM(CASE WHEN y = '2025' THEN r END) / SUM(CASE WHEN y = '2024' THEN r END) - 1), 1) AS growth_pct
FROM rev WHERE store_id IN (SELECT store_id FROM lfl)
UNION ALL
SELECT 'All stores + online', ROUND(SUM(CASE WHEN y = '2024' THEN r END)), ROUND(SUM(CASE WHEN y = '2025' THEN r END)),
       ROUND(100 * (SUM(CASE WHEN y = '2025' THEN r END) / SUM(CASE WHEN y = '2024' THEN r END) - 1), 1)
FROM rev;

Interpretation. LFL growth strips out Ankara (opened April 2024) and Baku Airport (closed June 2024) and online; comparing it with total growth shows how much growth is "real" growth of the existing estate.

Prediction and forecasting

Linear prediction worked example. Annual revenue 2023–2025: 917.7k, 1,017.6k, 1,144.5k. Least-squares slope with x = 0, 1, 2: x̄ = 1, ȳ = 1,026.6k; Σ(x−x̄)(y−ȳ) = (−1)(−108.9) + 0 + (1)(117.9) = 226.8; Σ(x−x̄)² = 2 → slope = 113.4k per year; intercept = 1,026.6 − 113.4 = 913.2k. Prediction 2026 (x = 3): 913.2 + 340.2 = ≈1,253k. With three points, express it as a rough range and compare with the CAGR-based projection 1,144.5 × 1.117 ≈ 1,278k — two simple methods, ~2% apart.

Stock prediction = forecasting future unit demand per product (Project 25); tenure prediction = predicting how long a customer/employee stays (Project 14, target definition awaiting founder confirmation). Both need leakage-free features and time-based evaluation (Module 11).

(TTC methodology and "coefficient prediction" are founder topics awaiting definition; they are tracked in the coverage matrix and not taught until defined.)

Practice

Summary

• ABC ranks by contribution, XYZ by demand variability (CV, including zero periods); thresholds are documented assumptions.
• Inventory KPIs: cover, turnover, stock-outs — stock-outs hide true demand.
• Market share needs matched scope; TAM → SAM → SOM narrows from total to obtainable; triangulate.
• CAGR uses the number of intervals; LFL removes openings/closures under a stated rule.
• Always benchmark forecasts against naive and seasonal-naive methods.