Excel, Purchasing, Supply Chain Management

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.

0

Inventory Management, Purchasing

          Bankruptcy forced the DeLorean Motor Company (DMC) to shut down its Belfast, Northern Ireland production facility in 1982, with an inventory of over 1,700 brand new cars and millions of parts. During liquidation, Consolidated International acquired all of DMC’s remaining inventory. Meanwhile, Steven Wynne, a British mechanic specializing in DeLorean maintenance and restoration since 1982, opened a 40,000 square foot warehouse in Houston, Texas, meant to act as a centralized distribution centre for used DMC parts. In 1997, Wynne acquired all DMC inventory from Consolidated International, in addition to the DeLorean Motor Company name and logo.

          Initially, the acquisition was aimed to support Wayne’s maintenance, restoration and parts sales operations. He and his team have serviced a consistent stock of 35-45 DeLoreans belonging to owners from all around the world since 1987. They also sell parts to DeLorean owners and restorers. As those operations were still not considerably cutting into the new DMC’s parts stock, they began assembling brand new DeLoreans themselves.


         A DeLorean requires roughly 2,700 individual parts of which DMC has over 99%, with no opportunities for traditional inventory replenishment. To fill the holes in their inventory, the remaining less than 1% are rather easily reproduced, rebuilt or procured as used parts. As all original DMC technical specifications and drawings were also acquired, they are often able to reproduce parts using the original specs with CAD/CAM and 3D modeling. This, in combination with their current inventory, allows the modern DeLorean Motor Company to produce a maximum of 500 cars, while continuing its additional pursuit to be the most prominent facility for DeLorean service, parts and restoration.


     

        Since 2016, the new DMC has employed Acctivate as its inventory management software. Acctivate is utilized for inventory adjustments when parts are received in the distribution centre, whether reproduced or acquired as used parts. Those adjustments are then automatically integrated into DMC’s web store. Acctivate supports and is used to create assemblies (one part containing multiple parts) in addition to sales order management including open and closed sales order monitoring, the creation of pick tickets and sales order printing. Some service orders, such as full frame-off restorations, require 200-300 line items for labor codes and part codes.

We’re able to build a service order pretty quickly with Acctivate, especially with some of these big restorations– Sarah Heasty, Service Manager, DeLorean Motor Company.

         DMC uses Acctivate’s Business Activity Service Billing module to create service order quotes, where separate subtotals can be created for a customer’s engine, transmission, suspension, etc., providing increased transparency. The Business Activity Scheduling module is employed to track labour hours and parts used for each service order. Labour hour tracking helps with DMC’s capacity planning as parts are pulled prior to service, increasing efficiency.

0