Did I Pick The Right Vendor? Show me using Numbers! Chapter 08

Published in

ProcurementIntel

9 min readJun 1, 2020

Chapter 08 — Data Preparation to Identify vendors who have been giving bad deals consistently

New vendor registrations are always interesting. A vendor can be cost-effective in one trade but may end up on a totally different side on an overall basis. But what if are to look back and measure the price index of the first trade of each item with each vendor. In this chapter, we will learn how to measure this metric using Excel.

New Framework: Starting Off On The Right Foot

Since we are dealing with Spend Analysis & specifically Tail Spend, our expectation (or hypothesis) should be that the

First trade of every item from every new vendor should be most competitive as compared to existing vendors.

Let us start.

Step A: Data Preparation

Arrange your pivot table as shown in the picture below by following these steps:

Step 1.1: Put Contract, PO Class and Vendor Class fields in the Filter section

Step 1.2: Choose “Contract Not Available” for Contract field in the Filter section

Step 2: Put “Commodity Description”, “Award_Date” and “Legal Name” (i.e. the Vendor name) in Rows section

Step 3: Put Average of “Unit_Price” and Sum of “ITM_TOT_AM” (i.e. Total value of items) in the Values section

Your table should look like this now

Step 4: Change the layout of the pivot table to the “Tabular Form” and allow the “Repeat All Item Labels” option. You can do so by following steps a,c and c as shown in the picture below

Step 5: Group “Award Date” column into “Years”. To do this, right-click on the “Award Date” column and select “Years”. Follow steps 5.1 and 5.2 to do the same.

At the end of step 5, our table should look as shown in the picture below

Now, we are ready to move to Step B.

Step B: Data Manipulation & Enrichment

Copy the data from the table shown above and make a new worksheet. I have named my worksheet as “Final Data”. We are now going to enrich our data by inserting 11 additional columns in our “Final Data” worksheet. The columns to be inserted have been highlighted in yellow. Each column will serve a specific function as explained below:

1.) S.No: This column is needed to identify each transaction uniquely. In case we need additional details of any transaction, this unique number will help us to locate the transaction.

2.) Disc Factor: Stands for Discount Factor. There are 3 challenges when we compare purchase transactions with each other:

a.) Macroeconomic factors such as currency movement, market price movement, etc

b.) Time Value Depreciation due to Inflation, Time value of money factors. Learn about the “Time Value of Money” concept here.

c.) Commercial Terms can differ between two transactions and hence we may not be able to compare them apple to apple basis. Freight conditions (FOB, CIF, EXW, etc), Insurance, Packaging, Origin and Destination, Payment Terms can all contribute to making the comparison difficult.

So, we need to “Normalize” or account for a factor that can bring each transaction at the same level. I am calling this field as Discount Factor.

To keep things simple, I have assumed Discount Factor as 1 for this chapter (I may publish a separate chapter on Normalization of Prices later).

3.) Adjusted Price: Ideally, we should be using the time value of money formula here. We need to recalculate or normalize the average unit price. This will enable apple to apple comparison between two transactions for us. To keep things simple, we will just multiply our Unit Price by Discount Factor.

Here is the formula:

=E2*G2

Where

Column E refers to Average of Unit Price

Column G refers to Discount Factor

4.) Min Price: Minimum price at which a particular item has been purchased. For example, refer to the picture shown below. This particular item “Trucks, Cab and Chassis” is being purchased from 2011 to 2016. The minimum price was achieved in the year 2016 as “26379.50”.

To calculate the minimum price of each item considering their history of purchase (like 2011 to 2016 as shown above), we will use a “MINIFS” function. Here is the formula:

=MINIFS($H$2:$H$6842,$B$2:$B$6842,B2)

Where

Column H refers to Adjusted Price

Column B refers to Commodity_Description

“MINIFS” function has 3 parts. In the first part, we need to specify the numerical value range where the Minimum condition will be applied. In the second part, we need to specify the criteria range, that is, our place of search and in the third part, the exact text that needs to be searched (our search text is commodity description line item)

5.) Max Price: As explained for the Minimum Price, the Max Price field refers to the Maximum price at which a particular item has been purchased.

Similar to Min Price, we will find out Max Price also. I will skip the explanation as it is similar to Min Price. We will use “MAXIFS “function.

Here is the formula

=MAXIFS($H$2:$H$6842,$B$2:$B$6842,B2)

Where

Column H refers to Adjusted Price

Column B refers to Commodity_Description

“MAXIFS” function also has 3 parts, which are similar to “MINIFS” formula.

6.) PPV%: PPV stands for Purchasing Price Variance. It refers to the range of Min Price and Max Price. The idea is to filter items with high price variation (or range) from items with low/medium variation. This concept we have already covered in chapter 07 (if you missed it, you can find the link to chapter 07 at the end of this article). You will see here that we have used the same PPV% concept but have calculated using a shortcut!

Here is the formula:

=J2/I2

Where

Column J refers to Max Price

Column I refers to Min Price

7.) Vendor Price Index: Vendor’s unit price index when compared to Min Price. This will enable us to see who is the expensive vendor for each item, expressed in terms of %. Straight and Simple. Here is the formula:

=H2/I2

Where

Column H refers to Adjust Unit Price

Column I refers to Minimum Price

8.) Purchase Period: Recall our hypothesis stated at the beginning of this chapter

First trade of every item from every new vendor should be most competitive as compared to existing vendors.

Hence, In simple terms, we want to specifically identify the “1st Year of Purchase” of each item. 1st year of purchase refers to the year when the first PO was released for the item. Let us see an example in the picture shown below

A: Refer to the Item. Notice, S.No 1, 2,3 refers to the same item

B: Highlights the column with 1st Year of Purchase for the item. You must look at column C. The oldest period purchase is 2011 (out of 2011, 2013, and 2014).

C: The Formula to calculate the 1st Year of Purchase. Here is the formula

=IF(C2=MINIFS($C$2:$C$6842,$B$2:$B$6842,B2),(“1st Year of Purchase for the item”),C2)

Where

Column C refers to Years (that is Year of PO Date)

Column B refers to Commodity_Description

The logic is

i.) we will use MINIF formula to calculate the lowest year (or oldest year) of purchase of the item. This is done using:

MINIFS($C$2:$C$6842,$B$2:$B$6842,B2

ii.) If the PO Date Year (that is the current value in Column C) equals to lowest year (or oldest year), then we will write “1st Year of Purchase”, otherwise we will maintain the current year value in the column M. This is done using:

(“1st Year of Purchase for the item”),C2)

9.) Best Quote Score: We will check the cost competitiveness of each supplier using a scoring system. So, whenever a supplier is the cheapest supplier for an item, we will award 1 point to that supplier. In the end, when we will do the consolidation, we can filter our supplier with the most number of points as our top price competitive supplier.

It is easy to calculate that. Here is the formula to do so

=IF(L2=100%,1,0)

Where

Column L refers to Vendor Price Index

The logic is simple. If the vendor price index is 100%, then it is the best price and hence we will award 1 point for the best quote. Otherwise, no points will be awarded.

10.) Range Selection: This is an optional column but I prefer you to keep it. When you have a large purchasing dataset, your unit price range will vary significantly. This is natural because each item can have its own unit price range. Especially, for items with low unit prices, the vendor prices can vary significantly. Let me give you an example. In the picture shown below, the vendor price index is 8488%, which is 84 times expensive than the minimum price (I won't comment if this can happen in the real case scenario because I found this dataset online). I decided to keep all cases where the Vendor price index is 50 times. Anything above that, I will exclude from my analysis. You can fine-tune this parameter according to your requirement.

The formula to do this is very simple.

=IF(L25>5000%,”> 50 times — Exclude”,”Include”)

Where

Column L is Vendor Price Index.

Our logic is if Vendor Price Index is more than 5000% (i.e 50 times), I have decided to exclude it from our analysis by writing “ >50 times — Exclude’. If Vendor Price Index is less than 5000%, I have decided to “include” it

11.) Extra Cost: This is our last column. This column measures the difference between actual PO value versus if the PO was purchased at the minimum price. Hence, the name “Extra Cost”. Here is the formula to calculate the same: