I published dashboards on Power BI services so anyone can open and interact with PROCUREMENT ANALYSIS.
Alternatively, you can interact with the dashboard directly below.
This dataset captures comprehensive procurement operations of a company. It includes 777 individual Purchase Orders (POs) recorded from January 2022 to January 2024. The data reflects real-world challenges such as managing significant supplier spend, addressing a substantial Overall Defect Rate, and navigating compliance gaps.
The analysis is ideal for driving strategic decisions related to supply chain efficiency, cost optimization, and vendor performance management.
Project Title: Procurement Performance Dashboard.
My Role: Data Analyst / Procurement Analyst.
Project Goal: The project goal is to provide visual insights into procurement spend, negotiation performance, and supplier reliability to support strategic decision-making on cost and quality.
The Problem: The company was struggling with optimizing supplier relationships and controlling costs due to inconsistent reporting. Key procurement risks—namely high defect rates, long lead times, and lack of compliance—were often identified too late or aggregated inefficiently, leading to reactive decision-making rather than strategic sourcing.
Key Question: We needed to answer key questions like: "Which suppliers pose the highest risk due to high defect rates combined with high spend?" and "Are our negotiated savings consistent across all item categories over time?"
Source Data: Data was sourced from flat CSV files containing Purchase Order details, Supplier profiles, Item Categorization, and historical transaction prices.
Modeling Strategy: The model structure involved normalized tables where primary entities (Supplier, Item Category) were separated from the detailed transaction records. This design ensures data normalization (reducing storage inefficiency) and utilizes one-to-many relationships to facilitate highly efficient and reliable data interaction for all dashboard metrics.
Interactive Dashboard: The final solution is a centralized, interactive Power BI Dashboard titled "Procurement Performance Overview". It consolidates key financial and operational metrics into a single source of truth for strategic sourcing decisions.
Visual Strategy: The design prioritizes immediate performance context, displaying 5 core KPI cards—including Total Spend (45.37M $), Total Savings (3.93M $), and Overall Defect Rate (5.64%)—at the top. The layout then cascades to detailed performance scorecards and trend analysis.
The analysis leveraged the integrated visuals to uncover specific areas of risk and opportunity:
Savings vs. Spend Mismatch (Supplier Risk):
Delta_Logistics and Epsilon_Group are among the highest spend suppliers. However, Delta_Logistics shows the highest average defect rate among the peer group, and Gamma_Co's defect rate is also concerning, signaling that high spend does not always correlate with high quality.
Category Focus (Quality Control):
The MRO category, while having the highest overall spend, shows a higher Defect Rate compared to Electronics and Packaging, suggesting this category needs immediate quality review and supplier consolidation efforts.
Conversely, the Packaging category, despite having comparable spend to Raw Materials, maintains a significantly lower Defect Rate, indicating a strong sourcing strategy in place.
Operational Weakness:
The overall Order Status Distribution shows a strong delivery rate (72.07% Delivered), but the 8.1% Cancelled and 9.4% Pending rates signal ongoing operational friction and warrant investigation into the root causes of cancellations and delays.
Spend Volatility:
The Overall Monthly Spend Trend shows significant volatility, particularly the sharp drop in early 2024, requiring management to investigate whether this was planned (e.g., budget cuts) or an unplanned supply chain disruption. The high month-to-month variability in Electronics and Raw Materials further suggests a need for long-term contract pricing to stabilize costs.
Financial Visibility: The dashboard provides real-time tracking of Total Savings (3.93M $) against Total Spend (45.37M $), eliminating manual aggregation and reducing reporting time by an estimated 80%.
Targeted Risk Management: The Supplier Performance Analysis (combining Spend, Savings, and Defect Rate) immediately identified high-risk suppliers like Delta_Logistics (high spend and high defect rate) and Gamma_Co (high defect rate), enabling immediate focus for quality intervention and contract review.
Operational Benchmark: Established a baseline for delivery performance, identifying Epsilon_Group as having the longest average Lead Time in both Electronics and Office Supplies (11.89 to 11.23 days, respectively), setting a clear target for process improvement.
The most immediate step is to implement a robust, automated alert system within the Power BI Service. This moves the Sourcing Manager from checking the dashboard daily to being notified instantly when critical thresholds are breached, ensuring rapid response.
Action: Implement automated email alerts triggered by Power BI, targeted specifically at Sourcing Managers responsible for the non-compliant supplier/category.
Thresholds & Rationale:
Defect Rate Exceeds 7.0%: This threshold is crucial because it provides a 1.36% buffer above the current Overall Defect Rate of 5.64%. Setting this boundary ensures that only quality issues significantly worse than the organizational average demand immediate intervention, prioritizing the most critical quality failures.
Compliance Rate Falls Below 80.0%: Given that the overall order completion rate is 72.07%, setting the compliance target at 80.0% pushes suppliers to improve their adherence to contract terms. This helps flag partners with high non-compliance like Delta_Logistics and Gamma_Co for contract review before performance degrades further.
The next strategic phase requires linking procurement performance to downstream logistics. The current analysis only captures the price; we need to model the true cost of poor quality and slow delivery.
Action: Integrate the Procurement model with the Inventory/Logistics dataset.
Modeling Focus: Develop new measures to quantify the financial impact in two areas:
Cost of Excess Inventory: Model the cost of capital tied up in inventory due to excessively long Lead Times (e.g., the $13.33$ days observed in the MRO category).
Cost of Quality Failure: Quantify the financial loss from defective parts, including the cost of returns, rework, inspection time, and potential stock-outs, providing a stronger justification for demanding supplier quality improvements.
To safeguard the achieved financial metrics, proactive cost control is essential.
Action: Develop a new dedicated visual (Line Chart) to track the historical Price Per Unit (PPU) by Item Category and by Supplier over time.
Strategic Objective (KPI Enforcement): The PVA will be used as a contract enforcement tool. It enables the Sourcing Manager to instantly identify any cost creep—small, incremental price increases that erode the negotiated savings. This is crucial for protecting the current 7.97% Average Savings Rate achieved.
Performance Impact: The analysis provides objective evidence when a supplier breaches agreed-upon pricing, allowing the procurement team to proactively initiate commercial discussions and impose contractual penalties, rather than reacting to end-of-quarter invoice discrepancies. This maintains the integrity of the Supplier Performance Scorecard by holding suppliers accountable for their pricing commitments.