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 practice in supplier selection and 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 2 of a 2 article series that will provide the reader with tips and best practices for the creation of supplier evaluation balanced scorecards.

 

Now that you have selected your supplier(s) using the points outlined in part 1 of this article, titled Creating a Supplier Selection Balanced Scorecard in Excel, your supplier evaluation responsibilities are far from complete. Suppliers must be regularly tracked to ensure that KPIs including price, quality and reliability, or whatever metrics are important to your organization, are available for evaluation. Your Supplier Evaluation Scorecard will be broken down into two separate tables: your Supplier Evaluation Scorecard and your Historical Tracking Scorecard.

 

Supplier Evaluation Scorecard

 

The Supplier evaluation table will include columns possessing the following titles:

  • KPI Groups (optional)
  • Key Performance Indicators (KPIs)
  • Performance Target (optional)
  • Measurement
  • Acceptable Score
  • Score This Month
  • Variance from Acceptable Score


 

KPI Groups (optional) and Key Performance Indicators (KPIs)

KPIs include a description of the metrics used to evaluate the success of the good or service provider. The KPIs can be grouped into a KPI Area (optional) column that proceeds it using KPI groups such as Customer Service, Cost, Quality, etc.

 

Performance Target (optional)

The optional Performance Target column includes the quantifiable goal you wish for the KPI row to achieve. While it is nice to be specific in producing your supplier evaluation balanced scorecard, the point is to spend your time evaluating, as opposed to producing the most detailed document. So use the Performance Target column with caution.

 

Measurement

The Measurement column describes precisely how you will quantify the score associated with each KPI.

 

Acceptable Score

The following column titles are self-explanatory. The Acceptable Score is the score that you would accept. This is the passing score, not necessarily a perfect score.

 

Score This Month

The Score This Month column is where you do the actual scoring. The time interval does not have to be monthly. Use the evaluation time interval that is appropriate for your situation. It is important to remain constant in your scoring for the purpose of incorporating this data with the Historical Tracking Scorecard that we will get to in a minute.

 

Variance from Acceptable Score

A simple =Score This Month cell – Acceptable Score cell formula will deliver your Variance from Acceptable Score for each KPI. The resulting value is what will be used in your Historical Tracking Scorecard.

 

Historical Tracking Scorecard

 

The Historical Tracking Scorecard is where your historical scores are recorded and evaluated. This table can either be placed on the same tab, or on its own separate tab. The table is a stripped-down version of the Supplier Evaluation Scorecard including the optional KPI Area column if you added one, an identical KPI column, and your time interval columns. As this example uses months, we will add 12-time intervals to evaluate. Of course, you can evaluate weekly, quarterly, yearly, etc. Each time interval, copy the score from the Variance from Acceptable Score column in the Supplier Evaluation Balanced Scorecard and paste those figures into the time interval column that corresponds with it in the Historical Tracking Scorecard.




To make the Historical Tracking Scorecard more visual: turn it into a  line chart by selecting the entire table’s containments and clicking on the Insert Tab ==> Chart ==> Line




Right click inside the chart and click Select Data. In the Select Data Source menu, for each Legend entry, highlight the name field and click the KPI cell (start with the first KPI). Then highlight the Y values field and select the entire time interval ROW that are associated with that KPI, all 12 months in our example. Do not include your KPI name. Repeat this step for every KPI. For the Horizontal (Category) Axis Label field, highlight the row cells that indicate your time intervals (month numbers/names, quarter names, etc.) You now have a beautiful chart that will help visualize your historical scores for each KPI. Customize the chart according to your preferences.




Now that you have completed your Supplier Evaluation and Historical Tracking Scorecards, it is vital to spend the time to fill them out within the specified time interval (weekly, monthly, quarterly, etc.). If for whatever reason you miss a time period or neglect to analyze the evaluation data you spent time making clear and easy to interpret, you will have wasted the time you spent producing the scorecards. It is also crucial that you remain constant in your scoring standards. Otherwise your supplier’s performance data will be distorted over time.

 

0

Purchasing

The purchasing department is in a strong position to leverage cost savings into profit. The profit leverage effect dictates that reducing operating expenses is more efficient than increasing sales. Situated at the beginning of the production process of a product or service, the procurement stage is in an excellent position to reduce overall costs, especially in the short term. This is why companies often resort to reducing headcount when they run into financial difficulties. Reducing operating costs is the fastest way to produce a short-term impact on the bottom line.

 

With this in mind, let’s talk about purchasing’s profit leverage effect. The following example will display how every dollar saved in purchasing goes directly to the bottom line, and it does so in a way that is more efficient that it would be through increased sales.

 

Your sales are $120,000 and your cost of goods sold (COGS) are $60,000. Within your COGS is your cost of purchased goods, which is 75% of your COGS ($45,000). Let’s say you reduce your cost of purchased goods through a combination of supplier relations and negotiations by 10%, you would save $4,500. Your cost of purchased goods is now $40,500. Your COGS are now $55,500.


Reducing costs of goods sold decreases your COGS from 50% to 46.25% of sales. Your operating income (net profit) therefor increases by the same amount. Let’s say operating income was $25,000 or 21% of sales, after the 10% of purchasing cost savings, net profit increased by 18% to $$29,736. That’s pretty good!

 

Now let’s look at what the sales department would have to do to achieve a comparable increase in net profit. To calculate how many more sales dollars would have to be generated we divide the needed additional profits ($4,500) by the operating profit margin (21%). The sales department will therefore have to sell an additional $21,428.57 worth of your product or service, which is the equivalent of increasing sales by 15%. And that does not factor in the marketing costs associated with increasing sales.

Which is easier? Decreasing the cost of purchasing by 10% or increasing sales by 15%? For most companies, that large of an increase in sales with no increase in advertising spend would be an incredible challenge, especially in the short run. On the other hand, reducing the cost of purchases by 10% is very attainable for organizations who have not traditionally managed purchasing as a strategic function.

 

Key Takeaway: every dollar saved through purchasing goes straight to the bottom line. By contrast, only 21% of sales goes to the bottom line, the remainder is consumed by the costs associated with doing business.

 

0