USFANS: Building a Seller Performance Dashboard with the USFANS Spreadsheet
A practical guide to tracking vendor quality control using color-coded metrics for accuracy, speed, and reliability.
Why Track Seller Performance?
In the fast-paced world of e-commerce and sourcing, managing multiple vendors requires clear, actionable data. A well-structured performance dashboard is not just a tracking tool—it's your strategic compass for making informed decisions, fostering accountability, and driving continuous improvement across your supply chain.
The USFANS Spreadsheet
The Three Pillars of Vendor Performance
Your dashboard should measure three critical dimensions. We'll use color-coded metrics
Measures the quality and correctness of delivered goods against specifications. Tracks timeliness and responsiveness throughout the order cycle. Assesses consistency and proactivity in vendor interactions.1. QC Accuracy (Blue Metrics)
2. Speed & On-Time Performance (Green Metrics)
3. Reliability & Communication (Amber/Brown Metrics)
Building Your Dashboard in the USFANS Spreadsheet
Step 1: Structure Your Data Sheet
Create a master sheet named Vendor_Performance_Data. Columns should include: Vendor ID, Vendor Name, Month, and all the raw data for the metrics above (e.g., "Defects Found", "Total Units", "Promise Date", "Actual Date").
Step 2: Create the Calculation & Scorecard Sheet
This is your core dashboard view. For each vendor, use formulas to calculate the performance metrics from the raw data.
Example Formula for Defect Rate (Excel/Sheets):=IFERROR((Vendor_Performance_Data!B2 / Vendor_Performance_Data!C2)*100, "N/A")
Step 3: Apply Conditional Formatting for Color-Coding
This brings your dashboard to life. Select the cell with a metric score (e.g., the Defect Rate %).
- Go to Format > Conditional Formatting.
- Create rules based on your thresholds.
Example for Defect Rate:
- Format cells if...less than- Format cells if...between- Format cells if...greater than- Repeat for every metric column.
Step 4: Create a Summary Overview
Add a separate sheet or section for an Executive Summary. Use functions like AVERAGE, COUNTIF
Best Practices for USFANS Dashboard Management
- Regular Updates:
- Review with Vendors:
- Action-Oriented:
- Iterate:
- Review with Vendors: