Supplier Cost Comparison
Evaluates suppliers on total cost of ownership (TCO) including unit price, shipping, quality costs, and payment terms. Produces a weighted ranking that balances cost against quality, reliability, and lead time scores.
Operations - Supplier Cost Comparison.xlsx
Excel (.xlsx) — No macros — Works in Excel, Google Sheets, LibreOffice
What This Spreadsheet Solves
- Selecting suppliers based on unit price alone without accounting for total cost
- No standardized framework for comparing suppliers across multiple criteria
- Hidden costs (defects, late deliveries, long lead times) not quantified
- Supplier reviews based on subjective opinions rather than data
- Inability to justify supplier switches to stakeholders with concrete analysis
Who This Is For
- Procurement managers evaluating supplier bids
- Supply chain directors conducting annual supplier reviews
- Operations managers reducing total procurement costs
- Purchasing agents comparing quotes from multiple vendors
Inputs
- textSupplier name
- $Unit price
- $Shipping cost per unit
- %Defect rate
- #Average lead time (days)
- %On-time delivery rate
Outputs
- Total cost of ownership per unit
- Weighted supplier score
- Supplier ranking
- Quality cost per unit (defect-adjusted)
- Cost savings from switching to top-ranked supplier
How Calculations Work
TCO per unit sums the unit price, shipping cost, and quality cost (defect rate times replacement cost). Suppliers are scored on a weighted matrix covering cost (e.g., 40%), quality (25%), delivery reliability (20%), and lead time (15%). Weights are configurable. The final score determines the ranking. A comparison chart shows TCO side by side and highlights potential savings from switching.
Example Use Case
Scenario: Three suppliers quote a component: Supplier A ($12/unit, $1.50 shipping, 2% defect rate, 14-day lead, 95% on-time), Supplier B ($10.50/unit, $3 shipping, 5% defect rate, 21-day lead, 88% on-time), Supplier C ($11/unit, $2 shipping, 1% defect rate, 10-day lead, 98% on-time).
Result: TCO: A = $13.74, B = $14.03, C = $13.11. Weighted score: C = 87, A = 79, B = 62. Supplier C ranks first. Switching from B to C saves $0.92/unit and improves quality and delivery.
What You Get — 5 Sheets
Technical Details
Frequently Asked Questions
How do I set the scoring weights?
Weights should reflect business priorities. Cost-sensitive operations weight cost higher (50-60%). Quality-critical operations weight quality higher (30-40%). Default is cost 40%, quality 25%, delivery 20%, lead time 15%.
What is quality cost per unit?
Defect rate multiplied by the cost to replace or rework a defective unit. If 5% of units are defective and replacement costs $20, quality cost is $1/unit.
Can I add custom evaluation criteria?
Yes. The CONFIG sheet allows adding up to 3 custom criteria with custom weights. Total weights must sum to 100%.
Should I include payment terms in the analysis?
Yes. Favorable payment terms (net-60 vs. net-30) have a cash flow value. The CONFIG sheet has a formula to convert payment term differences to a per-unit cost equivalent.
How often should I re-evaluate suppliers?
Annually at minimum. Quarterly for critical components or during periods of supply chain disruption.
Download Supplier Cost Comparison
Ready to use immediately. Enter your data in the INPUT sheet, see results in OUTPUT.