Multi-Source Inventory Reconciliation

Reconciles warehouse inventory, supplier stock, and pending orders using FULL OUTER and LEFT joins. Identifies critical discrepancies including oversold SKUs, stockouts with demand, and missing items across systems.

Input

What you provide to the skill

Combine warehouse.csv, supplier_stock.xlsx, and pending_orders.csv to reconcile inventory levels, identify discrepancies, and flag missing SKUs across sources.

Output

What the skill generates for you

SMART FILE MERGER - INVENTORY RECONCILIATION REPORT

Generated: 2025-12-04T10:47:33Z
Analysis Type: Multi-source inventory reconciliation with discrepancy detection

INPUT FILES ANALYSIS

File 1: warehouse.csv

Rows: 2,847 records
Columns: SKU, warehouse_location, on_hand_qty, reserved_qty, last_counted_date, bin_location
Primary Key: SKU
Coverage: Main warehouse physical inventory

File 2: supplier_stock.xlsx

Rows: 3,102 records
Columns: SKU_Code, supplier_name, available_quantity, lead_time_days, reorder_point, last_updated
Primary Key: SKU_Code
Coverage: Supplier availability and replenishment data

File 3: pending_orders.csv

Rows: 1,534 records
Columns: order_id, SKU, ordered_quantity, order_date, expected_ship_date, customer_priority
Primary Key: Combination of order_id + SKU
Coverage: Outstanding customer commitments

MERGE STRATEGY EXECUTED

Step 1: Base table = warehouse.csv (2,847 SKUs)
Step 2: FULL OUTER JOIN with supplier_stock.xlsx on SKU = SKU_Code
Step 3: LEFT JOIN with pending_orders.csv on SKU, aggregating total ordered_quantity per SKU

Rationale: Full outer join ensures we capture SKUs that exist in supplier system but not warehouse (new items) and vice versa (obsolete items). Left join with orders preserves all inventory items while adding demand.

MERGE OPERATIONS SUMMARY

Operation 1: Warehouse + Supplier Stock

Join Keys: warehouse.SKU = supplier_stock.SKU_Code
Join Type: FULL OUTER
Matched: 2,563 SKUs (90.0% of warehouse inventory)
Warehouse only: 284 SKUs (10.0%) - items in warehouse but not in supplier system
Supplier only: 539 SKUs (17.4% of supplier catalog) - items available from supplier but not stocked

Operation 2: Result + Pending Orders (Aggregated)

Join Keys: SKU
Join Type: LEFT
Aggregation: SUM(ordered_quantity) GROUP BY SKU
SKUs with pending orders: 891 unique SKUs (31.3% of inventory)
Orders matched: 1,534 individual order lines aggregated

FINAL MERGED DATASET

Total Rows: 3,386 unique SKUs
Total Columns: 14

Column Schema:

SKU (primary key)
warehouse_location
on_hand_qty
reserved_qty
last_counted_date
bin_location
supplier_name
available_quantity (supplier)
lead_time_days
reorder_point
last_updated
total_pending_orders
available_to_promise (calculated: on_hand_qty - reserved_qty - total_pending_orders)
reconciliation_status (calculated flag)

INVENTORY RECONCILIATION FINDINGS

Critical Discrepancies (67 SKUs)

Negative Available-to-Promise:

43 SKUs have available_to_promise < 0 (oversold condition)
Example: SKU-A1247 → On-hand: 120, Reserved: 45, Pending: 98 → ATP: -23
Action Required: Expedite supplier orders or contact customers about delays

Stockouts with Pending Orders:

24 SKUs have on_hand_qty = 0 but total_pending_orders > 0
Example: SKU-B3392 → On-hand: 0, Pending: 156 units
Action Required: Emergency supplier contact for lead time acceleration

Missing SKUs Across Sources (823 SKUs)

Warehouse-Only SKUs (284):

Items physically stocked but no active supplier
Potential obsolete inventory or supplier relationship ended
Total value estimate: Based on typical unit costs, ~$47,300 tied up
Action Required: Review for liquidation or supplier reactivation

Supplier-Only SKUs (539):

Items available from supplier but never stocked in warehouse
Could represent new products or intentional non-stock items
Opportunity: Evaluate for new product launches or demand fulfillment

Data Quality Issues

Missing Warehouse Data (539 SKUs):

SKUs from supplier system with no warehouse record
Fields NULL: on_hand_qty, warehouse_location, bin_location, last_counted_date

Missing Supplier Data (284 SKUs):

SKUs in warehouse with no supplier mapping
Fields NULL: supplier_name, available_quantity, lead_time_days, reorder_point

Stale Inventory Counts:

312 SKUs have last_counted_date > 90 days ago
Oldest count: SKU-C8834 last counted 247 days ago
Action Required: Schedule cycle count for aged records

Reorder Point Violations (156 SKUs):

on_hand_qty < reorder_point and no pending orders
Example: SKU-D4521 → On-hand: 15, Reorder: 50, Pending: 0
Action Required: Trigger purchase orders immediately

KEY METRICS SUMMARY

Metric	Value
Total Unique SKUs	3,386
SKUs Fully Reconciled	2,496 (73.7%)
Critical Oversold SKUs	43 (1.3%)
Stockout with Demand	24 (0.7%)
Warehouse-Supplier Mismatch	823 (24.3%)
SKUs Below Reorder Point	156 (4.6%)
Total Pending Order Volume	34,782 units across 891 SKUs
Average Lead Time	12.3 days (for matched SKUs)

DISCREPANCY BREAKDOWN BY CATEGORY

High Priority (Immediate Action - 67 SKUs)

Oversold inventory: 43 SKUs
Zero stock with orders: 24 SKUs

Medium Priority (Review This Week - 440 SKUs)

Below reorder point: 156 SKUs
Warehouse-only (obsolete?): 284 SKUs

Low Priority (Monthly Review - 851 SKUs)

Supplier-only (new items?): 539 SKUs
Stale counts (>90 days): 312 SKUs

RECOMMENDATIONS

Immediate Actions

Contact customers for 43 oversold SKUs to set delivery expectations
Emergency supplier orders for 24 zero-stock items with pending orders
Generate POs for 156 SKUs below reorder point

Process Improvements

Automate reconciliation: Schedule weekly automated runs of this merge
Supplier master data: Review 284 warehouse-only SKUs and update supplier mappings
Cycle counting: Prioritize 312 SKUs with stale counts (>90 days)
ATP monitoring: Implement real-time available-to-promise alerts

Data Governance

Standardize SKU formats between warehouse and supplier systems (currently using SKU vs SKU_Code)
Daily sync of reserved_qty to prevent oversell conditions
Supplier catalog review: Evaluate 539 supplier-only SKUs for stock consideration

OUTPUT FILES

Primary Deliverable:
inventory_reconciliation_merged.csv (3,386 rows × 14 columns)

Supplementary Reports:

critical_discrepancies.csv (67 high-priority SKUs)
missing_skus_analysis.csv (823 unmatched SKUs with categorization)
reorder_recommendations.csv (156 SKUs requiring immediate POs)

TECHNICAL NOTES

Merge execution time: 2.3 seconds
Memory usage: 18.4 MB
Duplicate handling: 0 duplicate SKUs detected in warehouse data
Data type conversions: order_date and expected_ship_date parsed to datetime
NULL handling: Preserved NULLs to distinguish missing data from zero values

Analysis Complete. Dataset ready for inventory planning, procurement decisions, and customer fulfillment prioritization.