Did I Pick The Right Vendor? Show me the results Item Wise! Chapter 09
Chapter 09— Separate the good vendors from bad ones, for each item!
Now that we have done all the hard work. It is time to reap the rewards!
We will make two tables to see the results.
A.) Summary by Item View
B.) Summary by Vendor View
A.) Summary by Item View:
Arrange your pivot table as shown in the picture below:
1. Filters Section:
1.1: Put field “Range Selection”
1.2: Choose its value as “Include”
2. Rows Section:
Put the following fields in the order shown below
Commodity Description
Legal Name
Year
S.No
3. Values Section:
Put the following fields in the order shown below
Average of Minimum Price
Average of Unit Price
Average of Vendor Price Index
Average of PPV%
Count of Range Selection
Sum of Extra Cost
Sum of Item Total Amount (i.e PO Value)
As explained in the previous post, you can choose Average, Count, Sum properties from the drop-down available in the Values section. Refer to the picture below for details (Follow Step 1 and Step 2)
After completing the steps shown above, your table will look like the table shown in the picture below
One last step is to rearrange the layout of this table to improve visibility. Here are the steps to do so:
1: Click on Design
2: Click on Report Layout
3: Select Tabular Form & Repeat All Labels
Done! Our Item View Summary is now ready. It should look like as shown in the picture below:
Analysis: How to read this table?
Let us start with an example. In the picture shown below, we have “Trucks, Cab and Chassis Only” commodity.
A1 & A2: Look at their purchase period “1st Year of Purchase”. It means “Trucks, Cab and Chassis” was bought for the 1st time this year. A1 purchase order was given to vendor “Santex Truck Centers Ltd” and A2 purchase order was given to vendor “Longhorn International Trucks Ltd”.
B: Look at the column “Average of Vendor Price Index” here. The Value if 100%. Also, look at the column “Sum of Extra Cost”, here the value is 0. That means this is the year (2016) when we had the lowest price for “Trucks, Cab, and Chassis”. We will use this lowest price and index prices of every other purchase. We will also give 1 point to the vendor “Silsbee Ford Inc” under column “Good Quote Score”, everyone else will get 0 points.
Let us come back to A1 & A2 and look at corresponding values in the “Average of Vendor Price Index” column.
A1: Santext Truck Centers Ltd: 525%
It means we purchased “Trucks, Cab and Chassis” from “Santext Truck Centers Ltd’ at 5.25 times price (from that of Silsbee Ford) for the same item. One may argue here that we are comparing 1st of purchase prices with year 2016 but 5.25 times price difference from vendor to vendor is abnormal and should be investigated. So, by not purchasing the item from Silsbee Ford, we gave USD 112,001 in “extra costs”. This is recorded in the column “Sum of Extra Cost”.
A2: Longhorn International Trucks Ltd: 814%
Same as above, we purchased “Trucks, Cab and Chassis” at 8.1 times more expensive than from Silsbee Ford. The extra cost paid was USD 362,648.
Hence by registering Santext Truck Centers and Longhorn International Trucks as approved vendors, we paid USD 474k in extra costs (USD 112,001+USD 362,648)
By repeating this step for all the vendors for “Trucks, Cab, and Chassis”, we can easily figure out the vendors with Highest Vendor Price Index values and Extra Cost paid. Total Extra Cost paid for the “Trucks, Cab, and Chassis” category is USD 5.34 Million. This is an excellent opportunity to eliminate expensive vendors, shift the business share to most competitive vendors, and get more cost savings!
Done!
Chapter 10
In this next chapter, we will see how to consolidate the above information from the vendor view perspective.
You can read Chapter 10 here.
I will appreciate your comments so that I can improve these lessons further.
For short summaries on upcoming procurement lessons, you can always
Subscribe to my publication: https://medium.com/procurementintel
Follow me on LinkedIn: https://www.linkedin.com/in/gsrm/
