Supplier Master & Vendor Rating (Excel)
Choosing a supplier on price alone is how most delays start. This workbook keeps one clean master list of your suppliers with their contacts, payment terms and promised lead time, logs every delivery against its promised date, and rates each supplier on on-time delivery, delivering in full, quality accepted and your own service score. The weights are yours to set, and the result is a score out of 100 with an A, B or C grade you can share at the review meeting.
Free · no sign-up · opens in Microsoft Excel, Google Sheets, LibreOffice and WPS Office. Files contain no macros.
Preview
Real view of the file with its sample data (yellow cells are where you type).
| Supplier | Category | Deliveries | On time % | In full % | Quality % | Service (1-5) | Actual lead time (avg days) | Standard lead time | Score | Grade | Rank |
| Bharat Steel Traders | Raw material | 4 | 75% | 50% | 99.7% | 4 | 8.75 | 10 | 80.7 | B | 2 |
| Precision Bearings Co. | Bought-out parts | 2 | 50% | 50% | 97.8% | 3 | 23 | 21 | 68.2 | C | 3 |
| Shree Packaging Industries | Packing material | 2 | 50% | 100% | 100.0% | 5 | 7.5 | 7 | 82.5 | B | 1 |
| Kumar Fasteners | Spares & consumables | 1 | 0% | 100% | 0.0% | 2 | 7 | 5 | 21 | C | 4 |
What's inside
- Suppliers sheet: code, name, category, contact person, phone, email, city or country, tax ID, payment terms, standard lead time, approval status, service score and last review date
- Deliveries sheet: supplier, PO number, PO date, promised date, received date, quantity ordered, received and accepted; days late, on time, in full, actual lead time and quality percent calculate themselves
- Rating sheet: deliveries, on-time percent, in-full percent, quality percent, average actual lead time against the promised lead time, weighted score, grade and rank
- Settings: weights for on-time, quality, in-full and service, grace days, and the score needed for grade A and B
How to use it
- On the Settings sheet check the weights (on time 35, quality 35, in full 15, service 15 to start with) and the grace days allowed before a delivery counts as late.
- Fill the Suppliers sheet: one row per supplier with its terms and standard lead time in days. Pick the approval status from the list.
- Give each supplier a service score from 1 to 5 for things the numbers don't show: response to queries, documents on time, help with problems.
- For every delivery, add a row on the Deliveries sheet with the PO date, the date promised, the date received and the quantities. Use the GRN log or your stock records for accepted quantity.
- Open the Rating sheet. Suppliers with deliveries get a score out of 100, a grade and a rank. Sort by score (Data › Sort) for your review meeting.
- Update the Last reviewed date on the Suppliers sheet after you discuss the rating with the supplier.
Tips
- Rate on at least five deliveries before you take a decision about a supplier; one late truck says little.
- A big gap between the actual and the standard lead time means your reorder levels are too low for that supplier: use the actual figure when planning.
- Keep blocked suppliers in the list with status Blocked instead of deleting them, so their history stays.
- Share the rating with the supplier. Most improve quickly when they see their own on-time percent.
Questions and answers
How is the score calculated?
Each measure (on-time percent, quality percent, in-full percent, and the service score divided by 5) is multiplied by its weight, added up and divided by the total of the weights, giving a score out of 100.
What counts as on time?
A delivery received on or before the promised date plus the grace days on the Settings sheet.
What does in full mean?
The quantity received is at least the quantity ordered on that delivery row. Part deliveries lower the in-full percent even when they arrive on time.
Can I rate service suppliers such as transporters?
Yes. Use quantity 1 for each job, and accepted 1 or 0 depending on whether the work was acceptable.