SRM Scorecard Excel Template

What is a scorecard in the context of SRM?

I have written a more general post on the Excel template for SRM here, and with context and definitions. The purpose of this specific post is to focus less on context and more on the excel template file itself.

As I already mentioned in several articles, I use indifferently vendor and supplier most of the time. In this post, I will use mostly the term supplier for better understanding.

What is SRM – Supplier Relationship Management?

We have a dedicated post on it. Long stories short: Supplier Relationship Management (SRM) is the systematic method to evaluating suppliers. It defines their contribution to company’s strategy and implementing actions plan to improve their performance.

• Systematic: this is a never-ending process
• Comprehensive: all suppliers, all categories, long period of time
• Engaging: commitment of interactions with suppliers
• Coordinated: involving a large part of your organization

So this supplier scoring template is part of Supplier Relationship Management tools. But it can as well be used stand alone. Let’s have a look at this Excel tool.

What is a balanced scorecard in general?

A balance score card (BSC) is a document – usually a one pager – summarizing all aspects of a topic in a mix of KPIs and graphics. The share of graphics is important. The purposes are:

• 

  360 degree analysis: support for fact-based and rational decisions

• Objective presentation of a topic: accepted by main actors and stakeholder involved
• Decision support for Management: KPIs follow-up and built-in presentation

But above all, this is your own insurance: you are in control of your activities. Thanks to a scorecard, you prevent being caught off foot. You always have a full understanding of money and risks at stake. And you can act accordingly, in a repeatable and foreseeable way.

What is a supplier performance scorecard?

A Supplier Scorecard is a structured overview of indicators of supplier performance. Those indicators are both qualitative and quantitative. Additionally they are displayed as an overview mixing graphics and figures. They offer a 360 degree view of the supplier performance over a period of time. Supplier Scorecard purpose is to manage the supplier, mitigate risks and optimize spend and profit.

Supplier Performance is a judgment on how well a supplier is supporting your organization reaching its targets. The vendor objective performance assessment is done along: revenue, profit, risks mitigation and business support. On the other side of the coin, the vendor subjective performance is measured per comparison with preset targets, fulfillment of improvement plans and often compared to peers.

A supplier scorecard in Excel is a graphical view of supplier performance along different criteria. You give score for all criteria defined. Then you report them onto a dashboard: the performance scorecard.

Well, this is nice and clear, but what does this mean in real life? How does it look like?

What are the main elements of a supplier performance excel scorecard?

Framework in building a vendor performance scorecard:

Well, there a thousands way of doing it. As a consequence, there is no right or wrong. But they all obey to a common framework to create a supplier rating in excel format. The questions you shall ask yourself upon building a supplier assessment scorecard are:

• Functionality: does it fit its purpose? Is there all i need to take a decision?
• Readability: can a third party read it without having a Phd? Is this read from left to right and top to bottom?
• Updatability: will I be able to updated the scorecard next month without spending my weekend in datamess? Are my source of information updated regularly? How much manual work?
• Conciseness: this is a card. Not a novel. More is not better.

Basic structure of a vendor performance one-pager

A drawing is far better than a long description to draft the vendor performance management excel template:

In this vendor Powerpoint scorecard, we identify the core elements:

• Header: gathering general information
• Body: details performance of the supplier along 4 segments, Business, quality, logistics and finance
• Footer: to ensure traceability and confidentiality

A ready-to-use supplier scoring scorecard excel template

The key is to keep a lean structure for KPIs, and use more metrics than text. It can look like this graphic below. Although there is not limit to how you can adapt it.

Note: If you do not have a clean supplier database, this Excel Template is maybe too early for you. You better have a look at this one: How to clean your supplier base in 5 steps?

The different tabs of the Excel template for SRM scorecard

There are 2 mirroring scorecards. One for you with internal insights, and one for the suppliers with less information. Don’t mix up!

The internal supplier performance scorecard tab is internal and internal only:  do not share with anybody from outside your organization. This is the view you use to prepare your supplier meetings.

Upon printing (or .pdf), they will fit perfectly on a Letter or A4 printout. The idea behind it: use it directly as reporting or as minutes of meetings.

The top part of the supplier performance scorecard

The header of supplier performance tool in Excel:

We are now located in the tab Score_card_share and/or Score_card_internal. The only difference between those 2 tabs is the presence/absence of internal comments, to prevent sharing unwillingly information with your supplier.

The header of vendor performance management scorecard is managed as follows:

• a dropdown list with all supplier name of your database (below in yellow)
• Dynamic fields for all other information, directly updated when chosen a supplier from the dropdown list

General supplier information (Supplier name, identification number,…) are obviously the identification key of each vendor.

Note the difference of the above scorecard for external use with the one for internal use (two different tabs). the header has much more information:

The true purpose is scoring, giving a note from 1 to 5. Five is the best note and one the weakest. We created 4 categories:

• Business score: the one you usually keep in mind. A common mistake is to reduce scorecard to those aspects.
• Quality score: can be the product itself, but as well services around such as data,…
• Logistics: all about the OTIF (on time in full) and similar KPIs
  
• Financial: cash impact or any other relevant topics

The spiderweb criteria principles:

In our Excel template, you can list as many criteria as you want. They will be automatically displayed as spider web KPIs. Have a look at the parameters tab, you will find the criteria for all spiderwebs graphs. Those can be changed any time to better fit your business.

There are organized along 4 supplier assessment categories:

• Business
• Logistics
• Quality
• Logistics

Those categories (and parameters) can be changed any time, and it will reflect automatically in all other tabs.

Each parameter has a weight: This is used to calculate the overall score of the category.

Making changes on your own: If you are a seasoned excel user, you will easily extend the functions, adapt the categories and change the layout. There are no hidden functions, just plain Excel Formulas. No big deal.

A supplier scorecard spreadsheet is defined by categories of criteria (Logistics, Financial,…), criteria (OTIF, Payment terms,…) and weights (Payment terms weight = 1, Currency USD weight = 2,…). This is an objective way of measuring performance of a supplier based on commonly accepted and transparent indicators. And this is valid across supplier categories.

Flexible: Adapt your own scoring criteria and weighting factors as many times as you want. Change colors and font or whatever: no restrictions in Excel, no password, no macro

The database as backbone of the body of the vendor performance scorecard:

To get the automatic scorecard creation, you need to maintain the information on a separate tab. This is the tab called “Supplier Summary”. This tab is feeding the scorecards tabs.

You shall maintain and update the supplier scoring at least once a year. Ideally, you do it quarterly with your colleagues. Below, a single row in Excel correspond to a supplier:

You can filter, sort: all usual methods you know from Excel.

 

Where to download the template:

Go directly at the bottom section of this article.

Customizing:

• Product Category
• Vendor segmentation and typology
• Scoring criteria and their weights. The supplier scorecard criteria are the architecture of the graphs.
• Weighting factors

You can as well add and suppress criteria. You need to be fit in Excel, but this is no rocket science. Done in 3-5 minutes. Obviously, the file is not protected.

What is the main outcome of the supplier report card template:

• Prepare meetings with vendors: in a couple of clicks, a complete overview of your supplier, shows weaknesses. A valuable asset for your negotiations and focusing on what matters. You steer your supplier in the right direction, they have no way out..
• Enable fact-based decisions: forget your gut feeling and start acting fact based. All your colleagues have shared their insights in this scorecard. You sit on top of a huge amount of knowledge. Taking decision is now a matter of tactics, not of luck.
• 360°-degree analysis of your spend: always wondered what you really spend your money on? Full picture of each and every supplier’s performance. All areas are covered:  business, financial, supply chain and quality.

Making changes on your own: If you are a seasoned excel user, you will easily extend the functions, adapt the categories and change the layout. There are no hidden functions, just plain Excel Formulas. No big deal.

Top 4 benefits you instantly achieve by building a supplier evaluation system

The truth is: negotiation is fine. But the powerful negotiation is only possible with powerful insights. Hence a supplier evaluation system.

1st Benefit: Opening negotiation without blindspot

Negotiations based on only a couple of hard facts (prices,…) are not enough to leverage your full potential. Many other soft facts will be revealed by the SRM Excel template. You get a kind of 360 supplier score. Luckily, the vendor scorecard template gives you ammo to shoot where it makes sense.

2nd Benefit: Continuous improvement

Imagine that you tell your baker that his bread would be better if he changed his packaging from plastic to paper. And that from the baker point of view, this is cost neutral. He will probably change on his own account.

Well this is the same in business life. If you point out some fields of improvement, that are fully reachable for the supplier, he might as well give them to you without even negotiating. And this happens only if you know his fields of improvement. That is exactly what generates the vendor evaluation template excel…

3rd Benefit: Commitment and backing-up of colleagues

Since you do not only work with suppliers and probably have a boss, the next question is: what’s in it for me internally?

Honestly this business tool is so powerful that it gives you commitment of your colleagues. There were involved, they gave judgment. They built the supplier relationship management template with you. So when your boss ask any of them: “if what I see on this chart the truth?” You get a massive supporting yes.

4th Benefit: Internal Save-my-day

Last but not least, the save my day benefit of this vendor performance scorecard template in Excel. On a single click, you can answer the Friday 5:00 p.m. question of your CEO. “Oh by the way, I may meet tonight supplier ABC for dinner: can you send me an overview in the next hour please?”

Enjoy your week-end…

Making changes on request:

You feel like you can’t do it on your own? Just contact me, I will let you know what is possible.

Download the SRM Scorecard Excel Template:

Here is the supplier relationship management template with integrated scorecard you will download for free. See our product page for a global overview of all products we offer.

First Name *

Email *

Newsletter *

• Yes, sign me up to the newsletter

GPDR - General Data Protection Regulation *

• I consent that my data (First Name, Email) is stored and processed to receive newsletter and information from this website.

Get the SRM Template with Scorecard