Code
from src.data_loader import get_awards_summary
get_awards_summary(conn)| total_awards | unique_vendors | unique_naics | total_spend_billions | earliest_date | latest_date | avg_lead_time_days | |
|---|---|---|---|---|---|---|---|
| 0 | 100000 | 11257 | 214 | 42.539936 | 2023-08-21 | 2023-09-30 | 170.917537 |
Supply Chain Cost Intelligence
SQL-driven supplier analytics on US federal procurement data
1 Executive Summary
100,000 federal procurement records from a capped FY2023 USAspending.gov manufacturing NAICS-33 development slice: $42.5B in obligations across 11,257 unique vendors and 214 NAICS categories. The retained rows span August 21 through September 30, 2023, not the complete fiscal year.
Using SQL window functions and K-means clustering, I identified four descriptive vendor tiers and produced a $182.5M heuristic opportunity screen under a within-10x benchmark rule and an assumed 30% re-sourcing rate. Award amount is not unit price, and the data do not establish equivalent scope or switching feasibility. The figure is a scenario for procurement review, not validated cost reduction or realized savings.
The business parallel: I’ve run a structurally identical analysis manually: a BOM cost analysis for a major HVAC manufacturer, tracing supplier re-sourcing opportunities and lead-time-driven safety stock across weeks of spreadsheet work. This project shows that analysis as a reproducible, scalable data system.
Key findings:
- Top 1.2% of vendors account for 80% of total spend, far steeper than the classic 80/20 rule
- 1,456 awards (1.5%) are statistical review flags (|z-score| > 2 within NAICS)
- 64 vendors classified as Underperforming (high cost + slow delivery)
- Heuristic opportunity screen: $182.5M (within-10x rule, 30% assumed re-source rate, 507 vendor-category pairs)
2 Data
Source: USAspending.gov Award Data Archive: FY2023 federal contracts, filtered to manufacturing (NAICS 33), awards ≥ $1,000, capped at 100K rows (scripts/fetch_data.py reproduces the slice). The row cap lands on the FY-end window (action dates Aug 21 to Sep 30, 2023), the period when federal obligation volume peaks.
Why this dataset: Federal procurement data is public, real, at scale (millions of records), and structurally identical to private-sector supplier intelligence: the same cost / vendor / category schema as a corporate BOM.
3 Spend Distribution and Vendor Concentration
3.1 Pareto Analysis
The slice’s spend distribution is highly right-skewed, consistent with federal procurement patterns where a small number of large contractors dominate total spend. The Pareto analysis below shows concentration far beyond the classic 80/20 rule: the top 1.2% of vendors account for 80% of total spend, driven by defense primes in aircraft, shipbuilding, and missiles.
Code
from IPython.display import Image
Image('../figures/01_pareto_concentration.png')
Implication: Review effort should concentrate on the top-spending vendors, but with a caveat this analysis carries through to the opportunity screen: the very top of the distribution is dominated by sole-source platform contracts that cannot be re-sourced on price. The next tier down is a better screening pool, but shared NAICS alone does not prove that vendors or awards are substitutable.
4 SQL Analysis
4.1 Vendor Performance Ranking
The following query ranks vendors within their NAICS category by cost efficiency × lead time, using CTEs and SQL window functions, the same patterns procurement analysts use for benchmark-and-rank work in any BI stack.
Code
vendor_sql = open('../sql/01_vendor_performance.sql').read().split('-- ── Save')[0]
vendor_df = conn.execute(vendor_sql).df()
vendor_df.head(20)| recipient_name | naics_code | naics_description | award_count | lifetime_spend | avg_award | median_award | avg_lead_time_days | std_lead_time_days | last_award_date | ... | naics_spend_share | naics_total_spend | cost_rank_asc | speed_rank_asc | volume_rank | naics_vendor_count | composite_efficiency_score | is_low_cost | is_high_cost | is_fast_delivery | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | STAINLESS SHAPES INC | 331110 | IRON AND STEEL MILLS AND FERROALLOY MANUFACTURING | 7 | 27230.67 | 3890.10 | 3267.05 | 77.7 | 15.6 | 2023-09-18 | ... | 0.0017 | 15599237.17 | 1 | 5 | 4 | 40 | 0.0750 | 1 | 0 | 0 |
| 1 | HURLEN CORPORATION | 331110 | IRON AND STEEL MILLS AND FERROALLOY MANUFACTURING | 9 | 37455.87 | 4161.76 | 2762.50 | 71.9 | 23.3 | 2023-09-27 | ... | 0.0024 | 15599237.17 | 2 | 4 | 3 | 40 | 0.0750 | 1 | 0 | 1 |
| 2 | PIERCE ALUMINUM COMPANY, INC. | 331110 | IRON AND STEEL MILLS AND FERROALLOY MANUFACTURING | 3 | 18270.34 | 6090.11 | 5418.98 | 29.3 | 27.6 | 2023-09-27 | ... | 0.0012 | 15599237.17 | 3 | 1 | 8 | 40 | 0.0500 | 1 | 0 | 1 |
| 3 | BAYFRONT METAL PRODUCTS LLC | 331110 | IRON AND STEEL MILLS AND FERROALLOY MANUFACTURING | 5 | 31615.00 | 6323.00 | 6345.00 | 90.2 | 0.4 | 2023-09-21 | ... | 0.0020 | 15599237.17 | 4 | 7 | 6 | 40 | 0.1375 | 1 | 0 | 0 |
| 4 | MGB ASSOCIATED SERVICES INC | 331110 | IRON AND STEEL MILLS AND FERROALLOY MANUFACTURING | 3 | 26059.31 | 8686.44 | 9308.60 | 31.0 | 13.5 | 2023-09-28 | ... | 0.0017 | 15599237.17 | 5 | 2 | 8 | 40 | 0.0875 | 0 | 0 | 1 |
| 5 | RUDY III, ERNEST | 331110 | IRON AND STEEL MILLS AND FERROALLOY MANUFACTURING | 11 | 101327.90 | 9211.63 | 6138.00 | 123.7 | 63.6 | 2023-09-26 | ... | 0.0065 | 15599237.17 | 6 | 8 | 2 | 40 | 0.1750 | 0 | 0 | 0 |
| 6 | ORION-METCO LLC | 331110 | IRON AND STEEL MILLS AND FERROALLOY MANUFACTURING | 5 | 60297.30 | 12059.46 | 12770.00 | 240.6 | 126.9 | 2023-09-21 | ... | 0.0039 | 15599237.17 | 7 | 9 | 6 | 40 | 0.2000 | 0 | 0 | 0 |
| 7 | SUPPLYCORE LLC | 331110 | IRON AND STEEL MILLS AND FERROALLOY MANUFACTURING | 43 | 1262405.43 | 29358.27 | 13861.06 | 44.0 | 41.7 | 2023-09-26 | ... | 0.0809 | 15599237.17 | 8 | 3 | 1 | 40 | 0.1375 | 0 | 0 | 1 |
| 8 | SUPER ROCO STEEL & TUBE, LTD. II | 331110 | IRON AND STEEL MILLS AND FERROALLOY MANUFACTURING | 3 | 267993.60 | 89331.20 | 90049.32 | 81.7 | 24.1 | 2023-09-07 | ... | 0.0172 | 15599237.17 | 9 | 6 | 8 | 40 | 0.1875 | 0 | 1 | 0 |
| 9 | BPD ENGINEERING, LLC | 331110 | IRON AND STEEL MILLS AND FERROALLOY MANUFACTURING | 6 | 1415611.07 | 235935.18 | 219156.52 | 923.7 | 5.1 | 2023-09-28 | ... | 0.0907 | 15599237.17 | 10 | 10 | 5 | 40 | 0.2500 | 0 | 1 | 0 |
| 10 | AIRCRAFT TUBULAR COMPONENTS INC | 331210 | IRON AND STEEL PIPE AND TUBE MANUFACTURING FRO... | 4 | 22659.59 | 5664.90 | 4579.83 | 150.5 | 61.0 | 2023-09-28 | ... | 0.0043 | 5292172.79 | 1 | 3 | 4 | 61 | 0.0328 | 0 | 0 | 0 |
| 11 | AL-TECH ASSOCIATES, INC. | 331210 | IRON AND STEEL PIPE AND TUBE MANUFACTURING FRO... | 5 | 38800.74 | 7760.15 | 3186.54 | 45.8 | 0.4 | 2023-09-28 | ... | 0.0073 | 5292172.79 | 2 | 1 | 3 | 61 | 0.0246 | 0 | 0 | 1 |
| 12 | BB&G ENTERPRISES INC | 331210 | IRON AND STEEL PIPE AND TUBE MANUFACTURING FRO... | 24 | 207644.84 | 8651.87 | 5036.46 | 174.2 | 116.9 | 2023-09-27 | ... | 0.0392 | 5292172.79 | 3 | 4 | 1 | 61 | 0.0574 | 0 | 0 | 0 |
| 13 | RUDY III, ERNEST | 331210 | IRON AND STEEL PIPE AND TUBE MANUFACTURING FRO... | 10 | 90598.86 | 9059.89 | 3846.16 | 142.7 | 108.3 | 2023-09-20 | ... | 0.0171 | 5292172.79 | 4 | 2 | 2 | 61 | 0.0492 | 0 | 0 | 0 |
| 14 | BAYFRONT METAL PRODUCTS LLC | 331221 | ROLLED STEEL SHAPE MANUFACTURING | 3 | 9950.00 | 3316.67 | 1700.00 | 90.0 | 0.0 | 2023-09-13 | ... | 0.0046 | 2185068.48 | 1 | 7 | 7 | 36 | 0.1111 | 1 | 0 | 0 |
| 15 | STAINLESS SHAPES INC | 331221 | ROLLED STEEL SHAPE MANUFACTURING | 3 | 22686.00 | 7562.00 | 3582.00 | 75.3 | 15.5 | 2023-09-26 | ... | 0.0104 | 2185068.48 | 2 | 5 | 7 | 36 | 0.0972 | 0 | 0 | 1 |
| 16 | BB&G ENTERPRISES INC | 331221 | ROLLED STEEL SHAPE MANUFACTURING | 12 | 110535.10 | 9211.26 | 7434.55 | 106.7 | 50.6 | 2023-09-25 | ... | 0.0506 | 2185068.48 | 3 | 9 | 2 | 36 | 0.1667 | 0 | 0 | 0 |
| 17 | MASTERSOURCE CO INC | 331221 | ROLLED STEEL SHAPE MANUFACTURING | 5 | 53759.00 | 10751.80 | 9550.00 | 34.0 | 7.4 | 2023-09-12 | ... | 0.0246 | 2185068.48 | 4 | 2 | 5 | 36 | 0.0833 | 0 | 0 | 1 |
| 18 | HURLEN CORPORATION | 331221 | ROLLED STEEL SHAPE MANUFACTURING | 10 | 141118.79 | 14111.88 | 7666.00 | 69.7 | 19.9 | 2023-09-29 | ... | 0.0646 | 2185068.48 | 5 | 4 | 3 | 36 | 0.1250 | 0 | 0 | 1 |
| 19 | BRIGHT HORIZON METALS, INC. | 331221 | ROLLED STEEL SHAPE MANUFACTURING | 3 | 42650.00 | 14216.67 | 13625.00 | 11.7 | 4.0 | 2023-09-11 | ... | 0.0195 | 2185068.48 | 6 | 1 | 7 | 36 | 0.0972 | 0 | 0 | 1 |
20 rows × 21 columns
4.2 Statistical Award-Amount Screening
Z-score analysis flags award amounts that are unusual within their NAICS category. These are review candidates, not verified overpayments or unit-price anomalies.
Code
anomaly_sql = open('../sql/02_price_anomalies.sql').read().split('-- ── Output 2')[0]
anomalies = conn.execute(anomaly_sql).df()
award_count = conn.execute("SELECT COUNT(*) FROM federal_awards WHERE action_amount > 0").fetchone()[0]
print(f"Anomalous awards: {len(anomalies):,} ({len(anomalies)/award_count*100:.1f}% of awards)")
anomalies.head(15)Anomalous awards: 1,456 (1.5% of awards)| recipient_name | naics_code | naics_description | action_date | action_amount | category_mean | category_median | z_score | iqr_ratio | anomaly_type | agency_name | |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | NOBLE SUPPLY & LOGISTICS, LLC | 332722 | BOLT, NUT, SCREW, RIVET, AND WASHER MANUFACTURING | 2023-08-30 | 1.100286e+07 | 12072.17 | 3031.82 | 67.684 | 2283.728 | overpriced | Department of Defense |
| 1 | IGOV TECHNOLOGIES, INC. | 339999 | ALL OTHER MISCELLANEOUS MANUFACTURING | 2023-09-15 | 1.252539e+08 | 217912.68 | 34512.76 | 61.366 | 1034.554 | overpriced | Department of Defense |
| 2 | LOCKHEED MARTIN CORPORATION | 336413 | OTHER AIRCRAFT PARTS AND AUXILIARY EQUIPMENT M... | 2023-09-12 | 2.977303e+08 | 618655.28 | 14196.19 | 50.054 | 5105.186 | overpriced | Department of Defense |
| 3 | NEW YORK EMBROIDERY STUDIO INC. | 339113 | SURGICAL APPLIANCE AND SUPPLIES MANUFACTURING | 2023-09-21 | 1.488960e+08 | 142134.67 | 17130.08 | 49.960 | 5882.680 | overpriced | Department of Health and Human Services |
| 4 | GENERAL ELECTRIC COMPANY | 332510 | HARDWARE MANUFACTURING | 2023-09-30 | 2.829695e+07 | 48283.31 | 3917.98 | 46.290 | 2452.252 | overpriced | Department of Defense |
| 5 | IRON BOW TECHNOLOGIES, LLC | 334111 | ELECTRONIC COMPUTER MANUFACTURING | 2023-08-30 | 7.084946e+07 | 295455.10 | 42900.00 | 41.935 | 564.715 | overpriced | Department of Veterans Affairs |
| 6 | EXECUTIVE PROTECTION SYSTEMS LLC | 336111 | AUTOMOBILE MANUFACTURING | 2023-09-08 | 9.206174e+06 | 68330.80 | 42061.00 | 38.803 | 535.381 | overpriced | Department of State |
| 7 | DETROIT DEFENSE, INC. | 339999 | ALL OTHER MISCELLANEOUS MANUFACTURING | 2023-08-29 | 7.775438e+07 | 217912.68 | 34512.76 | 38.054 | 642.117 | overpriced | Department of Defense |
| 8 | NEW YORK EMBROIDERY STUDIO INC. | 339113 | SURGICAL APPLIANCE AND SUPPLIES MANUFACTURING | 2023-09-25 | 1.077240e+08 | 142134.67 | 17130.08 | 36.132 | 4255.843 | overpriced | Department of Health and Human Services |
| 9 | ELECTRIC BOAT CORPORATION | 336611 | SHIP BUILDING AND REPAIRING | 2023-09-15 | 5.172488e+08 | 1396142.90 | 80263.00 | 33.792 | 1481.022 | overpriced | Department of Defense |
| 10 | THE BOEING COMPANY | 336411 | AIRCRAFT MANUFACTURING | 2023-09-28 | 1.713444e+09 | 4041338.19 | 8007.68 | 32.510 | 14695.341 | overpriced | Department of Defense |
| 11 | LOCKHEED MARTIN CORPORATION | 334519 | OTHER MEASURING AND CONTROLLING DEVICE MANUFAC... | 2023-09-26 | 9.526186e+07 | 277510.29 | 19616.48 | 31.687 | 1636.975 | overpriced | Department of Defense |
| 12 | RTX CORPORATION | 332510 | HARDWARE MANUFACTURING | 2023-09-27 | 1.834581e+07 | 48283.31 | 3917.98 | 29.983 | 1589.753 | overpriced | Department of Defense |
| 13 | NORTHROP GRUMMAN SYSTEMS CORPORATION | 334511 | SEARCH, DETECTION, NAVIGATION, GUIDANCE, AERON... | 2023-09-28 | 3.459215e+08 | 2100466.66 | 92627.50 | 29.430 | 664.332 | overpriced | Department of Defense |
| 14 | XEROX CORPORATION | 333316 | PHOTOGRAPHIC AND PHOTOCOPYING EQUIPMENT MANUFA... | 2023-09-15 | 2.433729e+06 | 11940.52 | 2243.30 | 28.659 | 834.152 | overpriced | Department of Defense |
5 Supplier Segmentation
5.1 Clustering Methodology
K-means clustering groups vendors into performance tiers based on: cost percentile · lead time percentile · award frequency · spend concentration.
Code
from IPython.display import Image
Image('../figures/02_elbow_silhouette.png')
Optimal k = 4, silhouette score 0.271. The four tiers match the natural business segmentation: Premium Reliable (566 vendors), Cost-Efficient (1,475), Risky / Volatile (1,243), and Underperforming (64).
5.2 Segment Profiles
Code
vendor_segmented = pd.read_parquet('../data/processed/vendor_segmented.parquet')
vendor_segmented.groupby('segment_name').agg(
vendor_count=('recipient_name', 'nunique'),
total_spend=('lifetime_spend', 'sum'),
avg_award=('avg_award', 'mean'),
avg_lead_time=('avg_lead_time_days', 'mean'),
).round(0)| vendor_count | total_spend | avg_award | avg_lead_time | |
|---|---|---|---|---|
| segment_name | ||||
| Cost-Efficient | 1475 | 4.776472e+08 | 42710.0 | 120.0 |
| Premium Reliable | 566 | 1.867790e+10 | 475752.0 | 179.0 |
| Risky / Volatile | 1243 | 8.458073e+09 | 764523.0 | 280.0 |
| Underperforming | 64 | 7.877465e+09 | 5882265.0 | 370.0 |
5.3 Cluster Visualization
Code
from IPython.display import HTML
HTML(open('../figures/02_cluster_scatter.html').read())6 Modeled Review Opportunities
Code
opp_sql = open('../sql/03_cost_opportunities.sql').read().split('-- ── Opportunity 2')[0]
opps = conn.execute(opp_sql).df()
screened = opps['conservative_savings_30pct'].sum()
# Scope-comparability filter: the p25 benchmark vendor is only a credible substitute
# if its price is within 10x of the incumbent's. Without this filter the "savings"
# are dominated by defense platforms where no substitute exists.
actionable = opps[opps['target_avg_award'] >= 0.1 * opps['avg_award_current']]
print(f"Raw price-gap screen: ${screened/1e9:,.2f}B ({len(opps)} vendor-category pairs)")
print(f"Heuristic review screen: ${actionable['conservative_savings_30pct'].sum()/1e6:,.1f}M ({len(actionable)} pairs)")
actionable.head(20)Raw price-gap screen: $8.63B (1284 vendor-category pairs)
Heuristic review screen: $182.5M (507 pairs)| naics_code | naics_description | high_cost_vendor | current_spend | avg_award_current | target_avg_award | price_premium_per_award | awards_to_re_source | gross_savings_full_switch | conservative_savings_30pct | cost_tier | naics_alternatives_available | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 43 | 336414 | GUIDED MISSILE AND SPACE VEHICLE MANUFACTURING | THE BOEING COMPANY | 1.446868e+08 | 10334774.84 | 2172577.87 | 8162196.97 | 14 | 1.142708e+08 | 34281227.28 | High-Cost | 11 |
| 97 | 334111 | ELECTRONIC COMPUTER MANUFACTURING | CDW GOVERNMENT LLC | 4.379124e+07 | 521324.33 | 52137.85 | 469186.48 | 84 | 3.941166e+07 | 11823499.35 | High-Cost | 124 |
| 134 | 339113 | SURGICAL APPLIANCE AND SUPPLIES MANUFACTURING | ATLANTIC DIVING SUPPLY, INC. | 2.555800e+07 | 131742.28 | 15365.24 | 116377.04 | 194 | 2.257715e+07 | 6773143.84 | High-Cost | 233 |
| 145 | 332722 | BOLT, NUT, SCREW, RIVET, AND WASHER MANUFACTURING | NOBLE SUPPLY & LOGISTICS, LLC | 2.741172e+07 | 11769.74 | 3124.84 | 8644.90 | 2329 | 2.013397e+07 | 6040191.34 | High-Cost | 178 |
| 165 | 333120 | CONSTRUCTION MACHINERY MANUFACTURING | CATERPILLAR INC | 2.223350e+07 | 404245.38 | 103228.00 | 301017.38 | 55 | 1.655596e+07 | 4966786.70 | High-Cost | 29 |
| 198 | 337215 | SHOWCASE, PARTITION, SHELVING, AND LOCKER MANU... | JPL & ASSOCIATES, LLC | 1.353041e+07 | 193291.56 | 21374.62 | 171916.94 | 70 | 1.203419e+07 | 3610255.69 | High-Cost | 22 |
| 207 | 334111 | ELECTRONIC COMPUTER MANUFACTURING | EMERGENT, LLC | 1.245912e+07 | 498364.96 | 52137.85 | 446227.10 | 25 | 1.115568e+07 | 3346703.28 | High-Cost | 124 |
| 209 | 337214 | OFFICE FURNITURE (EXCEPT WOOD) MANUFACTURING | STEELCASE INC. | 1.330300e+07 | 154686.10 | 27039.73 | 127646.37 | 86 | 1.097759e+07 | 3293276.22 | High-Cost | 116 |
| 220 | 334516 | ANALYTICAL LABORATORY INSTRUMENT MANUFACTURING | ROCHE DIAGNOSTICS CORPORATION | 1.156327e+07 | 312520.73 | 42044.78 | 270475.95 | 37 | 1.000761e+07 | 3002283.03 | High-Cost | 159 |
| 227 | 334516 | ANALYTICAL LABORATORY INSTRUMENT MANUFACTURING | AGILENT TECHNOLOGIES INC | 1.333096e+07 | 156834.85 | 42044.78 | 114790.07 | 85 | 9.757156e+06 | 2927146.74 | High-Cost | 159 |
| 235 | 337214 | OFFICE FURNITURE (EXCEPT WOOD) MANUFACTURING | PRICE MODERN LLC | 1.096872e+07 | 192433.60 | 27039.73 | 165393.87 | 57 | 9.427451e+06 | 2828235.18 | High-Cost | 116 |
| 252 | 334516 | ANALYTICAL LABORATORY INSTRUMENT MANUFACTURING | THERMO ELECTRON NORTH AMERICA LLC | 1.035538e+07 | 184917.58 | 42044.78 | 142872.81 | 56 | 8.000877e+06 | 2400263.17 | High-Cost | 159 |
| 254 | 334516 | ANALYTICAL LABORATORY INSTRUMENT MANUFACTURING | ILLUMINA, INC. | 1.066415e+07 | 161578.03 | 42044.78 | 119533.26 | 66 | 7.889195e+06 | 2366758.49 | High-Cost | 159 |
| 258 | 334516 | ANALYTICAL LABORATORY INSTRUMENT MANUFACTURING | LIFE TECHNOLOGIES CORPORATION | 9.442584e+06 | 214604.19 | 42044.78 | 172559.42 | 44 | 7.592614e+06 | 2277784.29 | High-Cost | 159 |
| 290 | 337214 | OFFICE FURNITURE (EXCEPT WOOD) MANUFACTURING | ALLSTEEL LLC | 7.708304e+06 | 167571.83 | 27039.73 | 140532.10 | 46 | 6.464477e+06 | 1939342.97 | High-Cost | 116 |
| 312 | 332992 | SMALL ARMS AMMUNITION MANUFACTURING | THE KINETIC GROUP SALES LLC | 1.098159e+07 | 156879.88 | 74354.40 | 82525.49 | 70 | 5.776784e+06 | 1733035.25 | High-Cost | 9 |
| 330 | 333120 | CONSTRUCTION MACHINERY MANUFACTURING | CERTIFIED STAINLESS SERVICE INC. | 6.093860e+06 | 553987.25 | 103228.00 | 450759.25 | 11 | 4.958352e+06 | 1487505.53 | High-Cost | 29 |
| 331 | 334516 | ANALYTICAL LABORATORY INSTRUMENT MANUFACTURING | ABBOTT LABORATORIES INC. | 6.057199e+06 | 208868.91 | 42044.78 | 166824.14 | 29 | 4.837900e+06 | 1451370.00 | High-Cost | 159 |
| 337 | 333120 | CONSTRUCTION MACHINERY MANUFACTURING | GROVE U.S. LLC | 5.519380e+06 | 689922.55 | 103228.00 | 586694.55 | 8 | 4.693556e+06 | 1408066.92 | High-Cost | 29 |
| 346 | 334510 | ELECTROMEDICAL AND ELECTROTHERAPEUTIC APPARATU... | ALLIANT ENTERPRISES, LLC | 5.111718e+06 | 189322.88 | 25604.38 | 163718.50 | 27 | 4.420400e+06 | 1326119.87 | High-Cost | 41 |
Methodology: The scenario is (current_avg_award - benchmark_award) × award_count × 0.30. The 30% factor and within-10x rule are assumptions. They remove obvious mismatches but do not observe unit quantity, equivalent scope, qualified-source status, contractual obligations, transition costs, or actual recoverability. Without the filter, the screen inflates to $8.6B. Neither value is a savings claim.
7 Limitations and Caveats
Lead time proxy: Lead time derived from
period_of_performance_end - action_date, not actual delivery date. This may overestimate lead time for contracts with options.No quality signal: Federal procurement data does not include defect rates or performance ratings. The clustering is cost × speed only.
10x and 30% assumptions: The opportunity screen assumes a benchmark within 10x is comparable and that 30% of Underperforming volume can be re-sourced. Actual recoverability depends on contract type (FFP vs. T&M), NAICS specialization, and competitive landscape.
Scope: Analysis covers manufacturing (NAICS 33) federal contracts ≥ $1,000, capped at 100K rows which lands on the FY2023 year-end window (Aug 21 to Sep 30, 2023), the highest-volume period of the federal fiscal year. Micro-purchases excluded. International procurement not covered.
Award heterogeneity: Average award value is not unit price; awards within a NAICS category differ in scope and contract structure. The scope-comparability filter mitigates the worst distortions, but a production system would benchmark at the product/PSC level.
8 Conclusions
Supply chain cost intelligence is mostly a data organization and SQL problem, with clustering on top to make the output actionable.
The SQL-first approach here deliberately mirrors how procurement analysts actually work: aggregate by vendor and category, rank by KPIs, flag anomalies, then hand off to Python for the segmentation that SQL can’t do cleanly.
The output is a triage queue for procurement review. A procurement team would still need to validate scope, unit economics, supplier qualification, and switching feasibility before acting.
Report generated with Quarto. Source: github.com/aalias01/supply-chain-cost-intelligence