Inventory Audit in Excel

1. System Overview & Purpose

Purpose: The Enterprise Inventory Audit & Discrepancy Tracking Framework is an operational control mechanism designed to reconcile physical stock counts with enterprise resource planning (ERP) ledger balances. It surfaces valuation variances, quantifies shrinkage, theft, and administrative errors, and drives automated root-cause auditing workflows.

Scope: Covers all raw materials, Work-in-Progress (WIP), and finished goods stored across primary distribution centers, bonded warehouses, and retail fulfillment nodes.

Data Update Cadence:

• Perpetual Ledger Inputs: Real-time via API sync or daily batch import at 00:00 UTC.
• Physical Cycle Counts: Daily rolling counts for A-class items; monthly/quarterly physical audits for B and C-class items.
• Variance Calculation & Dashboard Refresh: Automated upon physical count data entry.

2. Column Definitions & Validation Rules Table

3. Complete Master Data Table

4. Formulas & Calculation Logic

Assuming data resides in rows 2 through 13 of an Excel sheet named Inventory_Audit:

• Variance Quantity (Column H): =G2-F2
• Variance Value (Column I): =H2*E2
• Variance Status (Column J): =IF(H2=0, "Match", IF(AND(H2>0, ABS(I2)<=500), "Surplus", IF(AND(H2<0, ABS(I2)<=500), "Shrinkage", "Critical Deficit")))
• Total Inventory Audited Value (Dashboard KPI): =SUMPRODUCT(F2:F13, E2:E13)
• Net Variance Financial Impact (Dashboard KPI): =SUM(I2:I13)
• Total Shrinkage / Deficit Value (Dashboard KPI): =SUMIF(J2:J13, "Critical Deficit", I2:I13) + SUMIF(J2:J13, "Shrinkage", I2:I13)
• Inventory Accuracy Rate (Dashboard KPI): =COUNTIF(J2:J13, "Match") / COUNTA(A2:A13)
• Root Cause Frequency Analysis (Summary Table): =COUNTIF(M2:M13, "Misplacement") (Repeated for distinct root cause criteria)

5. Summary KPI Dashboard

+-----------------------------------------------------------------------------------------+
|                       ENTERPRISE INVENTORY AUDIT & CONTROL DASHBOARD                    |
+------------------------------------+------------------------------------+---------------+
| TOTAL SYSTEM VALUATION             | NET VARIANCE FINANCIAL IMPACT      | ACCURACY RATE |
| $2,987,150.00                      | -$3,212.50                         | 41.67%        |
+------------------------------------+------------------------------------+---------------+
| ABSOLUTE SHRINKAGE VALUE           | CRITICAL DEFICIT COUNT (> $500)    | STATUS        |
| -$3,814.00                         | 3 SKUs                             | [ REVIEW REQ ]|
+------------------------------------+------------------------------------+---------------+

KPI Calculation Blocks & Health Status Rules:

• Inventory Accuracy Rate Target: >= 98.0%.
  • Health Status Flag: < 90.0% triggers [ CRITICAL ] (Red); 90.0% - 97.9% triggers [ WARNING ] (Yellow); >= 98.0% triggers [ HEALTHY ] (Green).
• Net Variance Financial Threshold: Absolute net variance value must not exceed 0.5% of Total System Valuation.
  • Health Status Flag: Exceeding threshold triggers [ AUDIT REVIEW REQUIRED ].

6. Standard Operating Workflow

1. Preparation & Extraction:
   • Export current ERP system inventory balances (SKU, Item_Description, Category, Location_ID, Unit_Cost, System_Qty) into the master data template. Lock these columns to prevent manual tampering.
2. Physical Count Execution:
   • Deploy audit teams equipped with terminal scanners to execute blind cycle counts. Input findings into the Physical_Qty and Auditor_ID columns. Enter the exact date of completion in Audit_Date.
3. Automated Variance Analysis:
   • Let the sheet calculate Variance_Qty, Variance_Value, and Variance_Status automatically via deployed formulas.
   • Filter the table for Variance_Status equal to Critical Deficit or Shrinkage.
4. Root-Cause Investigation:
   • For every non-zero variance, dispatch operations leads to physically re-check bins and verify shipping/receiving logs for unposted transactions.
   • Select the empirical reason from the drop-down list in the Root_Cause column (e.g., Misplacement, Theft/Shrinkage).
5. Ledger Adjustment & Sign-Off:
   • Submit rows flagged as Critical Deficit to the Inventory Controller and Finance Director for write-off approval.
   • Post inventory adjustments inside the ERP ledger to synchronize physical truth with system records, clear the audit cycle, and archive the sheet with a date-stamped filename.