SUPPLIER EVALUATION at B & B, Inc.
Rebecca Smith is the purchasing manager for B & B, Inc., a manufacturer of multi-port PC-based voice processing boards that link PC-based computer applications to the telephone network to provide voice/fax mail, interactive voice response, and other telephone applications. Rebecca has collected the data for the company’s three suppliers (see Table 1). Because supplier selection is a key responsibility, Rebecca would like to develop a model for evaluating supplier conformance in four critical performance elements: price, quantity, quality, and delivery. She has decided to use a weighted-point evaluation scheme that develops a score for each element and then calculates an overall performance index based on the weights assigned to each performance element. In her quantification scheme, a supplier would earn performance points as follows:
Price: the ratio of the lowest price to the supplier’s price multiplied by 100
Quantity: the ratio of units received to units ordered multiplied by 100
Quality: the ratio of accepted units (units not returned, rejected, or reworked) to units received multiplied by 100
Delivery: the ratio of deliveries on time (not late) to total deliveries multiplied by 100
Table 1. Supplier Performance Data | Supplier | Element | A | B | C | Price ($/unit) | $170 | $150 | $160 | Quantity (units) | | | | Received | 14,350 | 12,214 | 13,000 | Ordered | 17,000 | 13,000 | 15,000 | Quality (units) | | | | Received | 14,350 | 12,214 | 13,000 | Rejected | 100 | 100 | 500 | Reworked | 75 | 1,000 | 500 | Returned | 25 | 1,050 | 1,000 | Delivery (shipments) | | | | Received | 31 | 32 | 26 | Late | 1 | 7 | 1 |
Rebecca would like the model to answer the following questions: 1. What is each supplier’s level of performance with respect to each element? 2. Which supplier has the best overall performance index, if the weights are as follows: price (30%), quantity (20%), quality (30%), and delivery (20%)?
Solution
The goal of the model is to develop a formal weighted-point evaluation scheme to track vendor performance on price, quantity, quality, and delivery. To accomplish this goal, we must first determine the points scored for each performance element and then calculate an overall score using the weights assigned to each element.

Design
The completed model appears in Figure 1. Note the layout clearly separates the input cells (shaded) from the supplier analysis (output) by creating two distinct sections (“Input” and “Supplier Analysis”) on the same sheet. Besides shading the cells that require user input, a helpful technique for identifying the cells that require user input is to use a colored font for the cell input (blue is a good choice). By contrast, the cells in the Supplier Analysis section of the sheet contain only formulas, along with the text headings. Additionally, observe that the sections are clearly outlined, the cell contents are clearly labeled and aligned, and the numbers are formatted appropriately. Proceed through the following steps to create the model. 1. Outputs. The “Supplier Analysis” section shows a side-by-side comparison of the results for each supplier with the scores for each element and the overall scores. 2. Inputs. The inputs include the user-assigned weights reflecting the importance of each performance element and the historical performance data of each supplier. The weights are entered as decimal fractions and formatted to appear as percentages and should sum to 100 percent.
Figure 1.B & B Model

3. Formulas. First, for clarity, use the “pointer” technique in the Supplier Analysis section of the spreadsheet to capture the supplier names that were entered in the Input Section automatically. For example, enter “=C14” in cell I5 to repeat the supplier name (“A”) that was entered in the Input Section. By using this technique, you can automatically update the results whenever you change the name of the supplier in the Input Section of the workbook. Second, translate Rebecca’s quantification scheme for each performance element into an Excel formulas and enter them in the appropriate cells. * Tip 1: use the MIN in the price score formula and the SUM function in the quality score formula. * Tip 2: think about how you might use absolute references in the formulas for the price score and the weighted average score, so that you can conveniently copy it to other cells. For example, you could write formula for Supplier A’s price score as follows and then copy it to the cells for Suppliers B and C.
=MIN($C$15:$E$15)/C15*100
Developing the B&B Model 1. Open the file titled “B&BShell.xls,” which is an incomplete version of the model shown in Figure 1. 2. Format the sheet (fonts, shading, outlines, column widths, etc.) 3. Enter the input described in the case in the Input section. 4. Enter the formulas in the Supplier Analysis section.
Validating the B&B Model
Enter the following weights in the Input Section: price 0.25, quantity 0.25, quality 0.25, and delivery 0.25. The weighted average scoresfor suppliers A, B, and C should be 92.0, 88.6, and 90.3, respectively.
Using the B&B Model
Use the B&B model to determine which supplier would have the best overall score, if the weights were as follows: price (30%), quantity (20%), quality (30%), and delivery (20%).
Creative Thinking 1. Given Rebecca’s weights, how much of an improvement in delivery performance is needed for supplier B to earn the highest overall score (hint: use Data Table to find the answer)? 2. If supplier B had perfect scores of 100 for quantity, quality, and delivery, what price would it have to charge to achieve an overall rating of 95 and become the preferred supplier (hint: use Goal Seek to find the answer)? 3. What additional criteria might be useful in the evaluation of suppliers? Explain.