Three prices for the same part, and nobody could see it
Multi-division durable goods manufacturer | SQL Server + Power BI
All supplier names, part numbers, and figures below are illustrative. Client data stays private.
The Problem
Most of the electronics in this product line were built by contract manufacturers. A few internal plants built boards, but the volume sat outside the company. Every contract manufacturer sent its bill of material as a spreadsheet, and every one of them used its own part numbers, its own descriptions, and its own units of measure. The same capacitor showed up under three codes. The same display module was described four different ways.
That made a basic question unanswerable. Ask what the company paid for a part and the honest answer was "depends which spreadsheet you open." Contract prices existed, negotiated part by part, but nothing compared them to what was actually being paid on purchase orders. Buyers negotiated from the last quote they remembered instead of from the full history.
The Solution
The work started with the data, not the report. We matched parts across every incoming spreadsheet, resolved the duplicate codes and units into one part master, and loaded purchase history, contract prices, and supplier records into SQL Server. Then we built the lookup model on top of it: pick a supplier or pick a part, and see contract price, every price actually paid, the quantity on each order, and which suppliers could quote the same part.
Three things sit on that same record, because purchasing decisions are never only about price:
- Commercial terms. Contract price, price paid, minimum order quantity, and the spend that sits above contract.
- Delivery. Lead time by supplier and on-time delivery against promised dates, which is what turns a cheap part into an expensive one.
- Quality. Defect rate in parts per million, carried next to the price so the low bid and the reject rate are read together.
The model also tracks the front end of sourcing. Every candidate vendor moves through named stages, from first contact to NDA to on-site audit to first article to approved to buy, with days in stage recorded at each step. Approval stops being a folder somebody owns and becomes a pipeline anyone can see.
Try the model
This is a small working version of the same model, built on invented suppliers and invented purchase records. Filter by commodity, supplier type, or quarter, sort the scorecard, and open the price ladder to see four suppliers quoting the same part.
Explore a sanitized version of the model
Invented suppliers, part numbers, and figures. 1,005 purchase lines in view.
Spend in view
$71.3M
illustrative
Bought off contract
47.4%
share of spend above contract price
Price leakage
$2.2M
paid minus contract
Negotiation headroom
$6.9M
vs. best contract for the same part
On-time delivery
87.4%
spend weighted
Quality
1,213 PPM
avg lead time 46 days
| Supplier | Type | Paid vs. contract | Avg MOQ | |||||
|---|---|---|---|---|---|---|---|---|
Verdant Assembly China · 18 parts | Contract manufacturer | $26.7M | ▲ 4.1% | 59.8% | 5,000 | 53 d | 87.4% | 1,753 PPM |
Northpoint Circuits Malaysia · 18 parts | Contract manufacturer | $19.7M | ▲ 2.8% | 53.5% | 2,500 | 46 d | 84.5% | 1,021 PPM |
Aurelia Components Taiwan · 18 parts | Component supplier | $5.5M | ▼ 1.1% | 15.0% | 2,000 | 37 d | 93.8% | 512 PPM |
Sable Power China · 6 parts | Component supplier | $5.5M | ▲ 4.2% | 63.4% | 3,000 | 43 d | 81.7% | 1,367 PPM |
Tessera Displays Korea · 8 parts | Component supplier | $5.2M | ▲ 0.1% | 36.9% | 1,500 | 48 d | 90.9% | 884 PPM |
Kestrel EMS Mexico · 18 parts | Contract manufacturer | $4.5M | ▼ 1.3% | 14.2% | 500 | 28 d | 92.6% | 471 PPM |
Brightwater Harness Vietnam · 13 parts | Contract manufacturer | $3.3M | ▼ 1.3% | 11.9% | 1,000 | 34 d | 87.3% | 660 PPM |
Plant 12 United States · 16 parts | Internal plant | $392K | ▼ 1.5% | 2.8% | 100 | 15 d | 97.8% | 330 PPM |
Plant 07 United States · 10 parts | Internal plant | $239K | ▼ 1.6% | 3.8% | 100 | 12 d | 97.6% | 256 PPM |
Harlow Interconnect United States · 15 parts | Component supplier | $176K | ▼ 1.4% | 1.8% | 250 | 21 d | 95.4% | 386 PPM |
Sort by spend and read across. The largest suppliers are not the best on compliance, delivery, or defect rate.
Synthetic demonstration data. Supplier names, part numbers, prices, and performance figures on this page are invented.
The Results
The price ladder was the part people reacted to. Once every supplier's contract price and every purchase sat on one scale, the spread on a single part was obvious, and so was the share of orders that ignored the contract entirely. Buyers walked into renewals with the full history in front of them instead of one remembered quote. Sourcing meetings stopped arguing about whose spreadsheet was right and started arguing about which supplier to move volume to.
In the demo above, open the Supplier scorecard and sort by spend. The two largest suppliers are also the two worst on contract compliance, delivery, and defect rate, which is the pattern that matters. Then open the Price ladder and look at how far apart the suppliers quote the same part.