ERP Inventory & Stock Accuracy Analysis, Anita Dania

Overview

From reactive stock correction to structured discrepancy tracking.

Investigated large scale inventory discrepancies across ERP records, physical stock counts, warehouse locations and transaction histories, identifying root causes, quantifying financial exposure and supporting corrective action across warehouse operations.

The work improved visibility into stock accuracy issues, location errors, process gaps and system-update failures, helping operational teams move from reactive stock correction to structured, evidence based decision making.

The Challenge

ERP records, locations and physical counts that didn't agree.

The core problem was that ERP records, warehouse locations and physical stock counts did not consistently align, creating uncertainty around inventory accuracy, financial exposure and operational accountability. High volumes of unplanned adjustment transactions were occurring, but existing reporting lacked the depth to surface patterns, risks or root causes.

I analysed transaction level ERP data, validated stock locations, reviewed physical count evidence, and grouped recurring issues by transaction type, location and root cause, building a clear evidence base that let teams prioritise the areas creating the highest operational risk.

My Role

Across inventory control, ERP data and process investigation.

I worked at the intersection of inventory control, ERP data analysis and business process investigation, reviewing system transactions against physical stock movements, warehouse locations and operational records.

I built Excel-based dashboards and trackers to monitor stock variance, aged stock, excess inventory, location accuracy, transaction patterns and recurring discrepancy issues, then translated findings into practical corrective actions for warehouse, supply chain and management stakeholders.

Approach

A four-stage analytics pipeline.

01

Data Extraction & Cleansing

Standardised raw transactional extracts from QAD ERP, resolving inconsistencies, duplicates and incomplete fields before analysis.

02

KPI Framework Design

Developed structured KPI models for inventory loss, gain, variance and adjustment frequency monitoring.

03

Dashboard & Reporting Build

Created operational dashboards, drill down screens and supplier, category and location trend views.

04

Investigation Support

Introduced lookup and exception reporting to cut manual investigation effort and surface high risk items automatically.

Key Features & Highlights

What the solution delivered.

✓ERP transaction investigation

✓Stock variance & discrepancy analysis

✓Warehouse location validation

✓Root cause analysis of process & system issues

✓Excel dashboards & operational trackers

✓Corrective action reporting

✓Cycle count & audit documentation

✓Management visibility into stock accuracy risks

The Numbers

Evidence at scale.

£935K

Discrepancy exposure identified through ERP analysis

37,425+

Unplanned ERP transactions analysed

90%

Reduction in misplaced stock

25.9M

Absolute units of stock movement reviewed

409,372

Net reduction in stock (units)

50.01%

Share of value in negative adjustments

£24.98

Average adjustment value

13.16M

Units in negative adjustments

T01515

The real thing, built in Excel.

Screenshots from the live ISS-UNP workbook. The figures and formulas are untouched, only sensitive fields (employee IDs and supplier names) are redacted.

ISS-UNP Performance and Insights Dashboard in Excel — **Performance & Insights Dashboard.** KPI tiles, category and supplier breakdowns, material type and net financial impact charts, one Excel view, nine analytical tabs behind it.

Overall KPI sheet with SUMIFS formula in the formula bar — **Overall KPI sheet.** Volume and value metrics driven by live `SUMIFS` logic, visible in the formula bar.

Transaction lookup screen driven by a FILTER CHOOSE array formula — **Transaction lookup.** A single `FILTER(CHOOSE(...))` array formula returns every record for an item, here, T01515.

🔒 Real workbook, sensitive fields redacted

Transaction-Level Drill-Down

From 37,425 transactions to a single accountable record.

Item T01515High Frequency

DescriptionNEOLUBE NO2 1 Gallon Can

Transaction No.353376527

Loc. Qty Change1.379414 (reduction)

Value£302.28

CategoryConsumables

Repeat No.19

User IDJHOUGHTO

Why this matters

Record level lookup without trawling raw data extracts
The Repeat No. field surfaces high frequency adjustment items automatically
Supplier and User ID fields support accountability tracking
Category and reason codes enable root cause classification
A High Frequency flag pushes the riskiest items to the top for investigation

Technical Approach

Built entirely in advanced Excel.

A production grade reporting environment delivered without additional tooling or infrastructure, proof that strong analytical structure matters more than enterprise software.

ƒx

Advanced Formula Modelling

Nested XLOOKUP, INDEX MATCH, SUMIFS and conditional logic underpinning every KPI calculation and transformation.

⌕

Dynamic Filtering & Lookup

Interactive lookup screens for record-level search and cross referenced drill down across supplier, category and item.

∿

Trend Visualisation

Operational trend charts and comparative period analysis embedded in the dashboards to surface patterns over time.

⛁

Data Cleansing Pipelines

Power Query transformation layers standardising raw extracts before they ever reached the reporting views.

Impact & Outcomes

Visibility, speed and governance.

◷

Improved Inventory Visibility

Clear view of loss and gain patterns across suppliers, categories and locations, replacing fragmented manual processes with structured reporting.

↘

Reduced Investigation Effort

Dashboard automation and lookup screens cut the time to investigate individual adjustments, freeing analysts for higher value work.

⚖

Enhanced Governance

KPI monitoring, exception reporting and audit ready documentation supported data driven management reviews and stronger accountability.

Deliverables

What was handed over.

01 Root cause analysis findings report

02 Inventory discrepancy tracker

03 Excel dashboards & variance reports

04 Location validation evidence

05 Corrective action documentation

06 Cycle count audit documentation

07 Management recommendations

QAD ERP investigationVariance analysisWarehouse location validationPower QueryPivot TablesRoot cause analysisKPI framework designAudit documentation