Procurement & Margin Intelligence Dashboard

The problem

A multi-location manufacturer had no unified view of product margin. Cost data was scattered across ERP tables with no consistent reconciliation methodology — production costs, stock ledger costs, and intercompany transfers all diverged, making it impossible to compare margins across product lines or fiscal years. The business ran on ERP exports and spreadsheets with no IT department to fix it.

What I built

A full-stack BI platform built solo — nightly ETL from an on-premises ERP (via pyodbc) into SQLite, a FastAPI backend with 12 route groups, and a React SPA with 7 role-gated views covering executive summary, product profitability, margin simulation, commodity tracking, supplier intelligence, customer intelligence, and quote benchmarking. The core technical challenge was a four-tier cost reconciliation hierarchy that dynamically selects the best available cost source per product per period, with a single source-of-truth function ensuring UI labels and query logic never drift apart. Live commodity price tracking via metals.dev feeds into Z-score timing signals for purchasing decisions. Deployed on Windows Server via a service wrapper; ingests nightly at 3am unattended.

Tech stack

Architecture

The hard part

Multi-source cost reconciliation across incomplete data. The ERP holds four distinct cost sources that vary in availability and precision by product, location, and fiscal period. The solution was a single cost_method(fy, quarter) function that returns the best available tier and generates the corresponding SQL — so every endpoint, UI label, and coverage note derives from the same logic and can never drift out of sync. A second significant challenge was filtering intercompany transactions: sales have a database flag; purchases do not, requiring supplier name pattern matching — an asymmetry baked into the ERP's data model, not a bug to fix but a constraint to encode cleanly.

Screenshots

Outcomes

• Deployed in production; nightly ingest runs unattended across multiple fiscal years of data
• Sub-second API responses across 500k+ transaction rows
• Commodity timing signals (Z-score vs rolling 252-day window) for copper and aluminium purchasing decisions
• Role-based access for executive, marketing, and procurement teams with page-level guards
• Annual team KPIs are now formally set and tracked through the dashboard