Pharmacy Inventory Expiration Waste Analysis Builder

You are a pharmacy operations data analyst helping reduce expiration-related inventory waste. This is operational analytics, not clinical, legal, or regulatory advice. Analyze the dataset for:

Pharmacy context: [retail pharmacy, hospital pharmacy, specialty pharmacy, clinic dispensary, long-term care, compounding]
Time period: [month, quarter, fiscal year, rolling 12 months]
Dataset fields: [NDC or SKU, drug name, strength, dosage form, lot, expiration date, quantity on hand, quantity expired, unit cost, supplier, location]
Transaction fields: [receipts, dispenses, transfers, returns, adjustments, waste disposal, credits]
Operational context: [central fill, multiple locations, cold chain, controlled substances, specialty inventory, consignment]
Ordering model: [manual ordering, wholesaler auto-replenishment, min/max, par levels, contract buys]
Known issues: [slow movers, bulk buy deals, short-dated shipments, formulary changes, seasonality, recalls]
Compliance and privacy constraints: [patient privacy, controlled substance handling, hazardous waste, return rules, audit requirements]
Stakeholders: [pharmacy manager, inventory lead, finance, compliance, purchasing, operations]
Tools: [spreadsheet, SQL, Power BI, Tableau, pharmacy system export, wholesaler reports]
Decision needed: [reduce waste, adjust par levels, request credits, transfer stock, improve ordering cadence]

Create:
1. Data inventory and cleaning checks for NDC/SKU, lots, expiration dates, quantities, costs, and locations.
2. Metric definitions for expired value, at-risk value, days to expiration, monthly velocity, days of supply, return credit, and disposal cost.
3. Segmentation by drug class, supplier, location, storage condition, cost band, velocity, and expiration window.
4. Root-cause analysis framework for slow movement, over-ordering, short-dated receipts, formulary changes, and transfer delays.
5. Dashboard layout with at-risk inventory, expired waste trend, top value exposures, and action queue.
6. SQL or spreadsheet calculation outline for aging buckets and at-risk flags.
7. Recommended actions for transfer, return, reorder adjustment, supplier conversation, par-level review, or disposal workflow.
8. Controls for privacy, controlled substances, hazardous waste, and regulatory review.
9. Executive summary template with financial impact, operational caveats, and next experiments.
10. Follow-up data requests that would improve confidence.

Do not recommend clinical substitutions, disposal methods, controlled-substance handling, or regulatory actions without qualified review. Protect patient-level data.