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

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

Excel, Inventory Management, Purchasing, Supply Chain Management

Quantitative forecasts are as in-demand as ever. This post provides four solid forecasting options in Microsoft Excel that can be used to predict sales, operating costs, performance and more. The forecasting methods explained include: moving average, the Excel FORECAST function, trendline and regression using the Analysis ToolPak.


Moving Average

A moving average predicts the future value of a time period through averaging past time period values. That forecast can then be used in further forecasting down the line, averaging it with other time period values. The risk in Moving Average forecasting is that it can lag behind a trend.

  1. Select the Data tab, then Data Analysis command button. In the Data Analysis dialog box, select the Moving Average item from the list and then click OK.
  2. Identify the data you want to use to calculate the moving average. Click in the Input Range text box of the Moving Average dialog box. Then identify the input range, either by typing a worksheet range address or by selecting the worksheet range. Your range reference should use absolute cell addresses ($A$1:$A$5 as opposed to A1:A5).
  3. Indicate how many values are to be included in the moving average calculation in the Interval text box. By default, Excel uses the most recent three values to calculate the moving average (i.e 3 month moving average, 3 year moving average, etc.). To specify that another number of values are to be used to calculate the moving average, enter that value into the Interval text box.
  4. Use the Output Range text box to establish the worksheet range into which you want to place the moving average data.

The FORECAST Function

The FORECAST Function predicts a future value using existing values. The predicted value is a y value for a given x value. The known values are existing x values and y values, and the new value is predicted by using linear regression. You can use this function to predict future sales, inventory requirements, or consumer trends.

FORECAST (x, known_y’s, known_x’s)

example =FORECAST(100,A2:A10,B2:B10)

  • X is the data point for which you want to predict a value.

  • Known Ys are the dependent range of data.

  • Known Xs are the independent range of data.


Trendline


A Trendline is simple way to analyse the trend within a collection of data points within a graph, smoothing out the data oscillations in the process. The trend within the data is then used to forecast future performance.
  1. Create a graph with your existing data.
  2. Right click on any of the data oints within the graph and select Add Trendline.
  3. In the Format Trendline window select whether you would like to forecast/analyse using: exponential, linear, logarithmic, polynomial, power or moving average. Linear is most common. If the data appears to trend towards compounding, try exponential
  4. Click on close.

Regression Using the Analysis Toolpak (ATP)


  1. Ensure Analysis Toolpack is installed (Tools tab, select Excel Add-ins, select Analysis Toolpak)
  2. Select the Tools tab
  3. Select Data Analysis
  4. In the Data Analysis window select regression.
  5. The Input Y range represents what you want to estimate (likely sales)
  6. The Input X range represents the data that can explain your Y (likely unit cost or price)
  7. Click OK


What does all this mean?

In Regression Statistics
Multiple R represents to correlation coefficient between Y and X
R Square represents the percentage of Y you can explain from X. A number closer to 1 indicates low variability and a number closer to 0 indicates a random correlation between your X and Y.

In ANOVA
Regression represents the number of independent variables
Risidual represents Total – Regression
Total represents the number of values – 1 (minus 1) (likely the number of active rows – 1)
SS represents Sum of Square
MS represents Mean Sum of Square
Lower % and Upper % means that 95% of the time, your coefficient will be between the lower value and the upper value


P-Value – The lower the P-Value, the less variability you have. The result of 1 – P-Value provides you with the percentage that the intercept will be correct


Cover photo credit: Finance Monthly
0