Build a self-calculating performance dashboard for supplier evaluation using reliability and refund metrics.
Why Automate Seller Scores?
Manually tracking supplier performance is time-consuming and prone to errors. By creating an auto-calculating spreadsheet, you can:
- Instantly see a seller's current performance score.
- Objectively compare multiple suppliers based on key metrics.
- Save hours of manual calculation and data entry.
- Make faster, data-driven sourcing decisions on CNFANS
The Core Metrics: Reliability & Refund Ratio
Our automated score will be based on two fundamental indicators:
| Metric | Description | Data Source |
|---|---|---|
| On-Time Delivery Rate (Reliability) | Percentage of orders delivered by the promised date. | Your order history/logs. |
| Refund/Issue Ratio | Percentage of orders requiring a refund or major dispute. | Platform dispute records or communication logs. |
Building the Automated Spreadsheet
Follow these steps in Google Sheets, Excel, or similar software.
Step 1: Structure Your Data
Create columns for raw transaction data. Example:
Seller Name | Order ID | Promised Date | Actual Date | Order Value | Refund Amount | Refund Issued?Step 2: Create Summary Calculations
In a separate Dashboard
This counts orders delivered on/before promise and divides by total orders. Or, if tracking by count: Combine the metrics into a single, weighted score. For example, prioritize reliability (70%) over refund ratio (30%). Explanation: A high reliability percentage and a low
Reliability Formula
=COUNTIFS(SellerRange, "SellerA", ActualDateRange, "<=", PromisedDateRange) / COUNTIF(SellerRange, "SellerA")Refund Ratio Formula
=SUMIF(SellerRange, "SellerA", RefundAmountRange) / SUMIF(SellerRange, "SellerA", OrderValueRange)=COUNTIFS(SellerRange, "SellerA", RefundIssuedRange, "YES") / COUNTIF(SellerRange, "SellerA")Step 3: Calculate the Final Performance Score
Overall Score Formula (0-100 Scale)
=(ReliabilityCell * 0.7 + (1 - RefundRatioCell) * 0.3) * 100
Example Dashboard View
| Seller | Reliability | Refund Ratio | Auto Score | Rating |
|---|---|---|---|---|
| Supplier_A | 95% | 2% | 93.9 | ★ ★ ★ ★ ★ |
| Supplier_B | 80% | 10% | 77.0 | ★ ★ ★ ☆ ☆ |
| Supplier_C | 70% | 15% | 67.0 | ★ ★ ☆ ☆ ☆ |
The "Rating" column can use the =IF(ScoreCell >= 90, "★★★★★", IF(ScoreCell >= 75, "★★★☆☆", "★★☆☆☆"))
Pro Tips for CNFANS Users
- Color-Code Scores:green (Score 80), yellow (60-80), or red (Score < 60).
- Dynamic Updates:automatically.
- Add More Metrics:
- Pivot Tables: