Vendor Performance Scorecard

Project Overview

Dataset Description

Five tables tracking vendors, purchase orders, delivery records, quality inspections, and invoices.

SQL Analysis

Query 1: On-Time Delivery Rate by Vendor

Calculate the percentage of deliveries arriving on or before the expected date for each vendor. Late deliveries disrupt production schedules and carry hidden costs beyond the purchase price.

SELECT
    v.vendor_name,
    v.category,
    COUNT(d.delivery_id) AS total_deliveries,
    SUM(CASE WHEN d.actual_date <= po.expected_date THEN 1 ELSE 0 END) AS on_time,
    SUM(CASE WHEN d.actual_date > po.expected_date THEN 1 ELSE 0 END) AS late,
    ROUND(
        100.0 * SUM(CASE WHEN d.actual_date <= po.expected_date THEN 1 ELSE 0 END)
        / NULLIF(COUNT(d.delivery_id), 0), 1
    ) AS on_time_pct,
    ROUND(AVG(d.actual_date - po.expected_date), 1) AS avg_days_variance
FROM vendors v
JOIN purchase_orders po ON v.vendor_id = po.vendor_id
JOIN deliveries d ON po.po_id = d.po_id
GROUP BY v.vendor_name, v.category
ORDER BY on_time_pct DESC;

Query 2: Defect Rate and Quality Scoring

Calculate the defect rate per vendor based on quality inspections. Break down by defect type to understand whether issues are cosmetic (minor) or functional (critical).

SELECT
    v.vendor_name,
    COUNT(qi.inspection_id) AS total_inspections,
    SUM(qi.sample_size) AS total_inspected,
    SUM(qi.defects_found) AS total_defects,
    ROUND(100.0 * SUM(qi.defects_found)::NUMERIC / NULLIF(SUM(qi.sample_size), 0), 3) AS defect_rate_pct,
    ROUND(
        100.0 * SUM(CASE WHEN qi.pass_fail = 'PASS' THEN 1 ELSE 0 END)
        / NULLIF(COUNT(qi.inspection_id), 0), 1
    ) AS pass_rate_pct,
    SUM(CASE WHEN qi.defect_type = 'Functional' THEN qi.defects_found ELSE 0 END) AS functional_defects,
    SUM(CASE WHEN qi.defect_type = 'Cosmetic' THEN qi.defects_found ELSE 0 END) AS cosmetic_defects,
    SUM(CASE WHEN qi.defect_type = 'Dimensional' THEN qi.defects_found ELSE 0 END) AS dimensional_defects,
    SUM(CASE WHEN qi.defect_type = 'Material' THEN qi.defects_found ELSE 0 END) AS material_defects
FROM vendors v
JOIN purchase_orders po ON v.vendor_id = po.vendor_id
JOIN deliveries d ON po.po_id = d.po_id
JOIN quality_inspections qi ON d.delivery_id = qi.delivery_id
GROUP BY v.vendor_name
ORDER BY defect_rate_pct ASC;

Query 3: Price Variance Analysis

Compare invoiced amounts against expected amounts (PO price x quantity) to detect overcharging, pricing errors, or unapproved surcharges. High variance vendors require procurement attention.

SELECT
    v.vendor_name,
    COUNT(inv.invoice_id) AS total_invoices,
    SUM(inv.expected_amount) AS total_expected,
    SUM(inv.invoiced_amount) AS total_invoiced,
    ROUND(SUM(inv.invoiced_amount) - SUM(inv.expected_amount), 2) AS total_variance,
    ROUND(
        100.0 * (SUM(inv.invoiced_amount) - SUM(inv.expected_amount))
        / NULLIF(SUM(inv.expected_amount), 0), 2
    ) AS variance_pct,
    SUM(CASE WHEN inv.invoiced_amount > inv.expected_amount THEN 1 ELSE 0 END) AS overcharge_count,
    ROUND(
        100.0 * SUM(CASE WHEN ABS(inv.invoiced_amount - inv.expected_amount) < 0.01 * inv.expected_amount THEN 1 ELSE 0 END)
        / NULLIF(COUNT(inv.invoice_id), 0), 1
    ) AS accuracy_rate_pct
FROM vendors v
JOIN purchase_orders po ON v.vendor_id = po.vendor_id
JOIN invoices inv ON po.po_id = inv.po_id
GROUP BY v.vendor_name
ORDER BY ABS(SUM(inv.invoiced_amount) - SUM(inv.expected_amount)) DESC;

Query 4: Comprehensive Vendor Ranking with Weighted Score

Combine all metrics into a single weighted score: delivery (30%), quality (30%), price competitiveness (25%), and invoice accuracy (15%). This provides the final vendor ranking for the procurement committee.

WITH delivery_score AS (
    SELECT
        v.vendor_id,
        ROUND(100.0 * SUM(CASE WHEN d.actual_date <= po.expected_date THEN 1 ELSE 0 END)
            / NULLIF(COUNT(d.delivery_id), 0), 1) AS on_time_score
    FROM vendors v
    JOIN purchase_orders po ON v.vendor_id = po.vendor_id
    JOIN deliveries d ON po.po_id = d.po_id
    GROUP BY v.vendor_id
),
quality_score AS (
    SELECT
        v.vendor_id,
        ROUND(100.0 - (100.0 * SUM(qi.defects_found)::NUMERIC / NULLIF(SUM(qi.sample_size), 0)), 1) AS quality_score
    FROM vendors v
    JOIN purchase_orders po ON v.vendor_id = po.vendor_id
    JOIN deliveries d ON po.po_id = d.po_id
    JOIN quality_inspections qi ON d.delivery_id = qi.delivery_id
    GROUP BY v.vendor_id
),
price_score AS (
    SELECT
        v.vendor_id,
        ROUND(100.0 - ABS(
            100.0 * (SUM(inv.invoiced_amount) - SUM(inv.expected_amount))
            / NULLIF(SUM(inv.expected_amount), 0)
        ), 1) AS price_score
    FROM vendors v
    JOIN purchase_orders po ON v.vendor_id = po.vendor_id
    JOIN invoices inv ON po.po_id = inv.po_id
    GROUP BY v.vendor_id
),
accuracy_score AS (
    SELECT
        v.vendor_id,
        ROUND(100.0 * SUM(CASE WHEN ABS(inv.invoiced_amount - inv.expected_amount) < 0.01 * inv.expected_amount THEN 1 ELSE 0 END)
            / NULLIF(COUNT(inv.invoice_id), 0), 1) AS invoice_accuracy
    FROM vendors v
    JOIN purchase_orders po ON v.vendor_id = po.vendor_id
    JOIN invoices inv ON po.po_id = inv.po_id
    GROUP BY v.vendor_id
)
SELECT
    v.vendor_name,
    v.category,
    ds.on_time_score,
    qs.quality_score,
    ps.price_score,
    acs.invoice_accuracy,
    ROUND(
        0.30 * ds.on_time_score
        + 0.30 * qs.quality_score
        + 0.25 * ps.price_score
        + 0.15 * acs.invoice_accuracy, 1
    ) AS weighted_total,
    RANK() OVER (ORDER BY
        0.30 * ds.on_time_score + 0.30 * qs.quality_score
        + 0.25 * ps.price_score + 0.15 * acs.invoice_accuracy DESC
    ) AS overall_rank
FROM vendors v
JOIN delivery_score ds ON v.vendor_id = ds.vendor_id
JOIN quality_score qs ON v.vendor_id = qs.vendor_id
JOIN price_score ps ON v.vendor_id = ps.vendor_id
JOIN accuracy_score acs ON v.vendor_id = acs.vendor_id
ORDER BY overall_rank;

Python Analysis

Weighted Scoring Model

Implement the vendor scoring model in Python with configurable weights. This allows the procurement team to run what-if analyses by adjusting the weight of each dimension.

import pandas as pd
import numpy as np
import matplotlib.pyplot as plt

# Load vendor scorecards (from SQL output or calculated in Python)
vendors = pd.read_csv('vendors.csv')
pos = pd.read_csv('purchase_orders.csv', parse_dates=['order_date', 'expected_date'])
deliveries = pd.read_csv('deliveries.csv', parse_dates=['actual_date'])
quality = pd.read_csv('quality_inspections.csv')
invoices = pd.read_csv('invoices.csv')

# Calculate metrics per vendor
merged = pos.merge(deliveries, on='po_id').merge(vendors[['vendor_id', 'vendor_name']], on='vendor_id')

# On-time delivery score
merged['is_on_time'] = merged['actual_date'] <= merged['expected_date']
delivery_scores = merged.groupby('vendor_name')['is_on_time'].mean() * 100

# Quality score: (1 - defect_rate) * 100
qual_merged = deliveries.merge(quality, on='delivery_id').merge(pos[['po_id', 'vendor_id']], on='po_id')
qual_merged = qual_merged.merge(vendors[['vendor_id', 'vendor_name']], on='vendor_id')
quality_scores = qual_merged.groupby('vendor_name').apply(
    lambda x: 100 - (x['defects_found'].sum() / x['sample_size'].sum() * 100)
)

# Price score: 100 - abs(variance %)
inv_merged = invoices.merge(pos[['po_id', 'vendor_id']], on='po_id')
inv_merged = inv_merged.merge(vendors[['vendor_id', 'vendor_name']], on='vendor_id')
price_scores = inv_merged.groupby('vendor_name').apply(
    lambda x: 100 - abs((x['invoiced_amount'].sum() - x['expected_amount'].sum())
                         / x['expected_amount'].sum() * 100)
)

# Invoice accuracy: % within 1% tolerance
accuracy_scores = inv_merged.groupby('vendor_name').apply(
    lambda x: (abs(x['invoiced_amount'] - x['expected_amount']) < 0.01 * x['expected_amount']).mean() * 100
)

# Combine into scorecard
scorecard = pd.DataFrame({
    'Delivery': delivery_scores,
    'Quality': quality_scores,
    'Price': price_scores,
    'Accuracy': accuracy_scores
}).round(1)

# Weighted total (configurable weights)
weights = {'Delivery': 0.30, 'Quality': 0.30, 'Price': 0.25, 'Accuracy': 0.15}
scorecard['Weighted Total'] = sum(scorecard[col] * w for col, w in weights.items())
scorecard = scorecard.sort_values('Weighted Total', ascending=False)
scorecard['Rank'] = range(1, len(scorecard) + 1)

print(scorecard.to_string())

Vendor Comparison Radar Chart

Create a radar (spider) chart comparing the top 5 vendors across all four scoring dimensions. This visualization makes it immediately clear where each vendor excels and where they fall short relative to competitors.

# Radar chart for top 5 vendors
top5 = scorecard.head(5)
categories = ['Delivery', 'Quality', 'Price', 'Accuracy']
N = len(categories)

# Compute angle for each axis
angles = [n / float(N) * 2 * np.pi for n in range(N)]
angles += angles[:1]  # Close the polygon

fig, ax = plt.subplots(figsize=(10, 10), subplot_kw=dict(polar=True))
colors = ['#3b82f6', '#10b981', '#f59e0b', '#ef4444', '#8b5cf6']

for idx, (vendor, row) in enumerate(top5.iterrows()):
    values = [row[cat] for cat in categories]
    values += values[:1]
    ax.plot(angles, values, 'o-', linewidth=2, label=vendor, color=colors[idx])
    ax.fill(angles, values, alpha=0.1, color=colors[idx])

ax.set_xticks(angles[:-1])
ax.set_xticklabels(categories, fontsize=12, fontweight='bold')
ax.set_ylim(0, 105)
ax.set_yticks([20, 40, 60, 80, 100])
ax.set_yticklabels(['20', '40', '60', '80', '100'], fontsize=9)
ax.set_title('Top 5 Vendor Performance Comparison', fontsize=14,
             fontweight='bold', pad=30)
ax.legend(loc='upper right', bbox_to_anchor=(1.3, 1.1), fontsize=10)
plt.tight_layout()
plt.savefig('vendor_radar.png', dpi=150)
plt.show()

Vendor Performance Trend Analysis

Track quarterly trends in each vendor's weighted score to identify improving or declining vendors. A vendor trending downward may need a corrective action plan even if their current score is acceptable.

# Calculate quarterly scores for trend analysis
merged['quarter'] = merged['actual_date'].dt.to_period('Q')

quarterly_delivery = merged.groupby(['vendor_name', 'quarter'])['is_on_time'].mean() * 100
quarterly_delivery = quarterly_delivery.reset_index()
quarterly_delivery.columns = ['vendor_name', 'quarter', 'delivery_score']

# Plot trend for top 5 vendors
top5_names = scorecard.head(5).index.tolist()
trend_data = quarterly_delivery[quarterly_delivery['vendor_name'].isin(top5_names)]

fig, ax = plt.subplots(figsize=(12, 6))
for name, group in trend_data.groupby('vendor_name'):
    color_idx = top5_names.index(name)
    ax.plot(group['quarter'].astype(str), group['delivery_score'],
            'o-', linewidth=2, label=name, color=colors[color_idx], markersize=6)

ax.set_xlabel('Quarter', fontsize=12)
ax.set_ylabel('On-Time Delivery Score', fontsize=12)
ax.set_title('Quarterly Delivery Performance Trend (Top 5 Vendors)',
             fontsize=14, fontweight='bold')
ax.legend(loc='lower left')
ax.grid(True, alpha=0.3)
ax.axhline(y=90, color='red', linestyle='--', alpha=0.5, label='Target (90%)')
plt.xticks(rotation=45, ha='right')
plt.tight_layout()
plt.savefig('vendor_trend.png', dpi=150)
plt.show()

Excel Instructions

Build a vendor scorecard template, weighted ranking system, and performance dashboard in Excel.

1. Scorecard Template: Create a sheet called "Scorecard" with columns: Vendor Name, Delivery Score, Quality Score, Price Score, Accuracy Score, Weighted Total, Rank. Enter vendor data from the SQL query output. Format as a table with Insert > Table for automatic filtering and formatting.
2. Weighted Formula: In the Weighted Total column, enter: =0.30*B2 + 0.30*C2 + 0.25*D2 + 0.15*E2. For the Rank column, use =RANK.EQ(F2, F$2:F$25, 0). Apply conditional formatting with color scales (green = high score, red = low score) to all score columns.
3. Weight Sensitivity Sheet: Create a "Weight Sensitivity" sheet with input cells for each weight (B1:B4). Link the Scorecard formulas to these cells so the procurement team can adjust weights and instantly see how rankings change. Add data validation to ensure weights sum to 1.00: =IF(SUM(B1:B4)=1, "Valid", "Weights must sum to 1.00").
4. Vendor Comparison Chart: Select the top 5 vendors' scores and create a Radar Chart (Insert > Chart > Radar). Each axis represents a scoring dimension. This provides the same visual as the Python radar chart but in an interactive Excel format that stakeholders can explore.
5. Trend Dashboard: Create a "Trends" sheet with quarterly scores per vendor. Build a line chart showing the weighted score over time for each vendor. Add sparklines in the scorecard next to each vendor to show their trend direction at a glance. Use =SPARKLINE or insert sparklines via Insert > Sparklines.
6. Contract Renewal Highlight: Add conditional formatting to the Scorecard table: if the contract_end date is within 90 days, highlight the row in yellow. This flags vendors that need immediate attention for renewal decisions. Use =contract_end - TODAY() < 90 as the conditional rule.
7. Executive Summary: Create a one-page summary sheet with: top 3 vendors by weighted score, bottom 3 vendors (candidates for replacement), vendors with expiring contracts, and a recommendation table (Renew, Renegotiate, Replace) based on score thresholds: above 85 = Renew, 70-85 = Renegotiate, below 70 = Replace.

Key Insights & Recommendations

Knowledge Check