PROJECT 04Intermediate

ABC/XYZ Inventory Classification

Business scenario

Orion Retail's buyers treat all 108 products the same: same review frequency, same safety stock rule. Stock-outs and overstock happen at the same time.

Problem statement

Classify products by revenue contribution (ABC) and demand variability (XYZ) and recommend inventory policies per class.

Learning objectives

Compute cumulative revenue share and assign ABC classes
Compute coefficient of variation of monthly demand and assign XYZ classes
Justify thresholds and translate the 9-cell matrix into policy

Instructions

ABC on 2025 net revenue (default A ≤ 80% cumulative, B ≤ 95%, C rest). XYZ on coefficient of variation (CV = std ÷ mean) of monthly units 2024–2025 (default X ≤ 0.5, Y ≤ 1.0, Z > 1.0). Thresholds are assumptions: show how many products move class if thresholds change.

Tasks

Revenue per product and cumulative share (window function).
Monthly units per product including zero months (join to a month list).
CV per product and XYZ class.
Cross-tab ABC × XYZ with counts and revenue share.
Recommended review frequency/safety-stock approach per cell.
Threshold sensitivity table.

Deliverables

SQL/Python
Excel matrix
Policy table

Evaluation rubric

Criterion	Weight	Excellent	Proficient	Developing	Insufficient
Data preparation & validation	20%	Correct filters and grain; validation checks documented and totals reconciled.	Correct and complete with minor gaps in rigour or presentation.	Partly correct; important steps missing, unvalidated or unclear.	Missing, incorrect, or not supported by the data.
Classification correctness	25%	Cumulative shares and CVs correct, zero months included.	Correct and complete with minor gaps in rigour or presentation.	Partly correct; important steps missing, unvalidated or unclear.	Missing, incorrect, or not supported by the data.
Policy recommendations	20%	Policies differ meaningfully by cell and are justified.	Correct and complete with minor gaps in rigour or presentation.	Partly correct; important steps missing, unvalidated or unclear.	Missing, incorrect, or not supported by the data.
Threshold justification	10%	Sensitivity shown, choice argued.	Correct and complete with minor gaps in rigour or presentation.	Partly correct; important steps missing, unvalidated or unclear.	Missing, incorrect, or not supported by the data.
Presentation	15%	Concise, well-labelled outputs a manager can act on without explanation.	Correct and complete with minor gaps in rigour or presentation.	Partly correct; important steps missing, unvalidated or unclear.	Missing, incorrect, or not supported by the data.
Communication	10%	Clear explanation for buyers.	Correct and complete with minor gaps in rigour or presentation.	Partly correct; important steps missing, unvalidated or unclear.	Missing, incorrect, or not supported by the data.