Stock Variance & Anomaly Detection — 12 Interactive Demos ตรวจสอบ Stock Variance & ตรวจจับความผิดปกติ — 12 Interactive Demo

Read the attached Report_Adjust_Stock_2026.xlsx (DATA tab). 1. Map Thai columns to English: รหัสสาขา→BranchCode, ชื่อสาขา→BranchName, รหัสสินค้า→ItemCode, ชื่อสินค้า→ItemName, จำนวน→Qty, มูลค่า→Amount, ประเภท→ReasonCode. 2. Add Month (from filename or date), WeekNo, and VarianceDirection ("Short" if Qty<0, "Over" if Qty>0). 3. Drop rows where BranchCode or ItemCode is null. 4. Output standardized CSV with 10 columns.

From the standardized audit CSV, filter VarianceDirection = "Short". Group by BranchCode, BranchName. For each branch compute: ShortSKU = count of distinct ItemCode ShortUnit = sum of Qty (negative values) ShortAmount = sum of Amount (negative values) Rank branches by ShortAmount ascending (most negative first). Add %OfTotal column = branch ShortAmount / total ShortAmount × 100. Highlight the top-2 worst branches.

From the standardized audit data, create 4 drilldown summaries: 1. By Item: group by ItemCode/ItemName → ShortSKU, ShortUnit, ShortAmount, %Total 2. By Category: group by Category → same metrics 3. By Vendor: group by VendorCode/VendorName → same metrics 4. By Division: group by Division → same metrics Sort each by ShortAmount ascending. Flag any dimension where a single value exceeds 10% of total shortfall. Identify cross-dimension patterns (e.g., same vendor appearing in multiple top categories).

Pivot the standardized audit data by ReasonCode (rows) × Month (columns). Values = count of records per cell. ReasonCode values: "นับ stock", "เกิน", "WRITE", "AUDIT". Add a Total column. Highlight any cell where count exceeds previous month by ≥20%. For each ReasonCode, show Jan→current month trend direction (↑/↓/→). Flag WRITE code specifically if MoM increase > 20% for 2 consecutive months.

Join the standardized audit data with sales data (by BranchCode + ItemCode). Calculate: VariancePctOfSales = abs(VarianceAmount) / SaleAmount × 100. Sort by VariancePctOfSales descending. Flag rows where VariancePctOfSales ≥ 5% as "Review Required". Flag rows where VariancePctOfSales ≥ 10% as "Critical". Output columns: Branch, ItemCode, ItemName, SaleAmount, VarianceAmount, VariancePctOfSales, FlagLevel.

Score each BranchCode + ItemCode combination on a 0-100 scale using these weights: AmtScore (max 30): normalize abs(ShortAmount) within the dataset, scale to 0-30 PctScore (max 30): normalize VariancePctOfSales, scale to 0-30 RepeatScore (max 20): count months this item appeared as Short, scale to 0-20 RankScore (max 20): use inverse sales rank (top seller = 20, lowest = 0) Total = AmtScore + PctScore + RepeatScore + RankScore. Level: High ≥ 75, Medium ≥ 45, Low < 45. Sort by Total descending. Output: Branch, ItemCode, ItemName, each sub-score, Total, Level.

Read POS_Void_Refund.csv. For each CashierID within the same BranchCode: 1. Calculate branch mean and std dev for VoidCount, RefundCount, and TotalAmount. 2. Compute Z-Score = (CashierValue - BranchMean) / BranchStdDev for each metric. 3. Flag as ANOMALY if any Z-Score ≥ 3. 4. Flag as WATCH if any Z-Score ≥ 2 but < 3. Sort by max Z-Score descending. Output: Branch, CashierID, Name, VoidCount, RefundCount, TotalAmt, Z-Score, Flag.

Read PO_History.csv. For each BranchCode + ItemCode combination: 1. Calculate historical average PO quantity (last 12 months) and std dev. 2. For the current month's PO, compute Z-Score = (CurrentPOQty - HistAvg) / HistStdDev. 3. Flag ANOMALY if Z-Score ≥ 3. Flag WATCH if Z-Score ≥ 2. 4. Cross-reference with promotion calendar if available — mark "Promo Justified" if promotion exists for this item/period. Sort by Z-Score descending. Output: PONumber, Branch, ItemCode, ItemName, POQty, HistAvg, Z-Score, Flag, PromoCheck.

Read Money_In_Out.csv. For each BranchCode and transaction Type (IN/OUT): 1. Calculate historical mean and std dev of abs(Amount) over the last 90 days. 2. Compute AmountZScore = (abs(CurrentAmount) - Mean) / StdDev. 3. Flag ANOMALY if |AmountZScore| ≥ 3. 4. Flag WATCH if |AmountZScore| ≥ 2 but < 3. 5. Cross-reference timing: flag transactions on non-business days or outside business hours. Sort by |AmountZScore| descending. Output: Date, Branch, Type, Description, Amount, |Z-Score|, Flag, TimingNote.

Consolidate all scored anomalies from these 4 sources into a single prioritized alert queue: 1. Severity Scoring results (Score 0-100) 2. Void/Refund anomalies (convert Z-Score to 0-100 scale: Z×20, cap at 100) 3. PO anomalies (convert Z-Score to 0-100 scale: Z×20, cap at 100) 4. Money In/Out anomalies (convert |Z-Score| to 0-100 scale: |Z|×20, cap at 100) Assign: High if Score ≥ 75 → "Investigate today". Medium if Score ≥ 45 → "Review in weekly queue". Low if Score < 45 → "Monitor". Sort by Score descending. Flag branches appearing in 3+ alerts as "Systemic Risk".

For each HIGH-priority alert in the alert queue, draft an investigation remark using this template: "Investigate [Subject] at [Branch] [BranchName]: [Severity context with score/Z-score]. [Key data points: amount, %, frequency]. [Specific investigation steps: what to check, what documents to request, what to compare]." CRITICAL: Mark every remark with RequiresHumanConfirmation = True. Do NOT finalize any remark. Do NOT send to any party. Output status must be "Draft — Needs Auditor Sign-off". Include the data source and alert ID for traceability.

Monitor these 4 raw audit files every Monday by 08:30: 1. Report_Bill (expected: Mon 08:00) 2. Report_Lost_Over (expected: Mon 08:00) 3. Report_Negative (expected: Mon 08:00) 4. Report_Special_Location (expected: Mon 08:00) For each file check: - Existence: is the file present? If missing → CRITICAL alert. - Timeliness: received time vs expected. Late > 30 min → WARNING. - Row count: compare to last week. If delta > ±10% → WARNING with note. - Schema: verify column count matches expected. If mismatch → CRITICAL. Output: File, Expected, Received, Rows, Status (OK/Late/Missing), Exceptions.