Excel, Purchasing, Supply Chain Management

Creating a Supplier Selection Balanced Scorecard in Excel

In all likelihood, your first encounter with a balanced scorecard would have been your report card, where individual school projects, tests and exams were given a certain weight in accordance to their level of importance. In purchasing, this tool is put to use in supplier selection and in supplier evaluation. It is used to avoid risk, reduce costs, mitigate rogue or maverick purchasing and ultimately aid in the selection of the most qualified good or service provider. Performance metrics are listed in columns and are then scored using a standard numerical value range often being 0% to 100% or 1 through 10. Each individual score is then multiplied by the weight determined by their level of importance and are summed at the bottom, often converted into a percentage format. This is part 1 of a 2 article series that will provide the reader with tips and best practices for the creation of supplier selection and supplier evaluation balanced scorecards.

 

Technology

 

You will want to create your balanced scorecard in Excel. While you could print a basic table in Word or even draw one out by hand and calculate the scores manually, you will be missing out on the analytical opportunities outlined in these two articles,and the digital sharing functionality with your colleagues, while increasing the possibility of human error. Your Excel scorecard will essentially be a dashboard, with colour notifications by way of conditional formatting and historical tracking with automated graphs if you want to get fancy. If creating dashboards in Excel is not for you and this all sounds intimidating, you’re in luck! I have created the following Purchasing Coordinator evaluation scorecard for your use in hiring someone to build one for you. See how useful balanced scorecards can be?

adamkwitko.com

 


Creating a Supplier Selection Balanced Scorecard

 

To build a scorecard to help in the selection of a supplier, you will want to begin by identifying the metrics considered to be of importance to your organization relating to the good or service the potential supplier is to provide. This is often associated with the business problem they are to solve. A more complex product or service will likely require more performance matrices than one that is less complex, but don’t get carried away. You do not want to waste your time creating and evaluating metrics, so 5 to 8 should do. List them vertically, assigning each metric to its designated cell in the performance metric column. You will input your potential suppliers’ names in the top row of your table. This should be a relatively short list as well, as the scorecard is designed to identify the most qualified provider according to the needs of your organization.

 

Each metric now has a designated row. The cells of that row under each candidate name is where the score that particular candidate achieves for that metric will be input. This will generally be in the form of a range or a binary Yes/No input. It is important to remain consistent in the range you use to evaluate each row throughout your scorecard. A score of 1-10 is a good idea. For a Yes/No selection, you have a few options:

  • Input 10 to equal Yes and 0 to equal No
  • Indicate “Yes” to equal Yes and “No” to equal No
  • Insert a check box or radio button.

 

Consider incorporating conditional formatting to bring attention to a particular score range. For example: make the score cell turn green if the cell’s score >= 8, yellow if >=4 and <8, red if <4. For Yes/No or checkbox/radio button cells you can use conditional formatting to make the cell turn green if Yes or if the box is ticked and red if No or if the box is not ticked.

 

The bottom row is where the total score is calculated for each candidate good or service provider. This is where your SUM formula will go. Sum each candidate’s column, while assigning a percentage or decimal point to each performance metric, giving the more important ones more weight. Note that if you ranked your metrics using a scale of 1 to 10 as recommended in this article, you will want to divide it by 10 before multiplying the cell by the percentage weight. For example:

=SUM(B2/10)*whatever weight you would like it to be worth)+your next equation

 

If you opted to use “Yes”/”No” text, checkboxes or radio button for your binary metrics, you will have to incorporate an IF Statement into your formula for each one. For example:

 

=if(E5=”Yes”,whatever weight you would like it to be worth,0) for the “Yes”/”No” text

=if(E5=TRUE,whatever weight you would like it to be worth,0) for a Checkbox or radio button




Each of your column’s SUM formulas should look something like this:

=SUM(B3/10*0.05,B4/10*0.2,B5/10*0.1,IF(B12=TRUE,0.15,0),IF(B13=TRUE,0.125,0),IF(B14=TRUE,0.125,0),IF(B15=TRUE,0.125,0),IF(B16=TRUE,0.125,0))

 

As the supplier selection scorecard is an internal document, the final perfect score does not have to equal a clean 100%, however it will be more comprehensive if it is equal to a common sense rounded number. Now that you have the tools to evaluate your suppliers, the next step in your supplier evaluation adventure is to crete Supplier Performance Evaluation Scorecards.

Author


Avatar

Adam Kwitko

Recent supply chain management graduate certificate recipient, certified SAP Associate, Lean Six Sigma Green Belt and Microsoft Office Specialist (Excel & Access). Currently utilizing 6 years of event operations, media management and production experience to transition into a supply chain analyst position or similar.