Which is the largest yet often ignored part of Tail Spend? Hidden Tail! Chapter 04
Chapter 04 — Hidden Tail. Finding and Fixing it!
The hidden tail is probably one of the most under-reported tail spend as it is easy to be missed. As explained in Chapter 02, the Hidden tail is the spend where we have a contract present but do not have a purchase order. In simpler words, we are looking for commodity item descriptions where we have both types of transactions — with a contract and without a contract.
Before jumping straight to the Hidden Tail part, we will look at how to properly arrange the pivot table. Let us start with the table we made at the end of Chapter 03. This will be our starting point.
Let us go back to our Pivot table and modify the data source to include our newly inserted columns. To do so, select the pivot table and locate the PivotTable Analyze field. Under that, you will notice the Change Data Source option (as shown in the picture below). Now you will be able to modify the data source range to include our newly inserted columns till column AB. Time to dig in, follow the steps carefully!
Step 1: Major Spend vs Tail Spend
Segregate Major Spend and Tail Spend by putting our “Spend Class” column in Rows. After that pull our “Contract” column under “Spend Class” in the Rows. In the last step, put “ITM_TOT_AM”, i.e our PO Amount column in Values. I have renamed it to “Total Value” for easier reference. Your pivot table should look like the one in the following picture :
Now, we will first focus on Major Spend. Under Major Spend the subcategories are
A.) Contract Available Spend of USD 343.9 Million
B.) Contract Not Available Spend of USD 69 Million
Let us now investigate “Contract Available” spend further to find the “Hidden Tail”.
HIDDEN TAIL
Recall the framework we introduced in Chapter 01, we will now focus on A, the Hidden Tail where we will try to find Spend Outside of the negotiated contracts (as highlighted above)
Arrange your pivot table as shown in picture shown below. If you have noticed, we have added another field “PO” in our Rows of Pivot table just after the “Contract” field. In normal circumstances, there should not be any reason for doing a purchase outside of the contract. It is worth the investigation. According to our preliminary analysis, as shown in the picture shown below, the potential opportunity could be worth $5.16 Million.
However, we need to dive into further details, specifically arrive at individual commodity description level detail to have some actionable insight. We will do so in upcoming steps. Are you able to recall what we are looking for?
We are looking for items (or commodity descriptions in this dataset) where we have both the type of purchases (With contract and without contract).
To arrive at Hidden Tail detail at the commodity description level, carefully follow Steps 1.1, 1.2, 2, 3, and 4 and arrange your pivot table accordingly.
Step 1.1: Setup Filters
Add the “Spend Class” column and “Contract” column under “Filters” in pivotable fields.
Step 1.2: Choose Values for the Filters
Choose the “Major Spend” value for “Spend Class”. Choose “Contract Available” value for “Contract”
Step 2: Choose “Commodity Description” field in Rows for pivot table
Step 3: Choose “PO” field in Columns for pivot table
Step 4: Choose the “ITM_TOT_AM” field (i.e. PO Total Value) in Value for the pivot table. Ensure you have the “sum” (and not count) in the Value. I renamed this field to “Total Value” for better readability. If the field is not set to display the sum, you can always click on the downward arrow beside the Value field in the pivot table, go to Value field settings, and then select summarized by sum.
If you have followed all the above-mentioned steps correctly, your pivot table must look like as shown in the picture below.
In the above picture, I have highlighted an example of the Hidden Tail. If you look at a commodity description “332 Traffic Signal Cabinets”, we have both the cases: “PO available” ($ 642k Spend) and “PO not available” ($172k spend). Ideally, this should not be the case as we already have a contract for this item. Hence, the spend under “PO Not Available” should be 0 or blank. We must note down this as an opportunity and take up with the vendors. We should try to renegotiate the contract by including this additional volume.
There could be many such cases in our dataset. To find them all together we must do the following:
Step 5: We must copy the data (as shown in the picture above) into a new worksheet titled “Hidden Tail”.
Step 6: Once done, apply the filter on containing “PO Not Available” (column C in the picture shown below) and deselect the “Blank” field. This is to select only the relevant information (nonblank).
Step 7: If we need further information such as vendor name in the table shown above, it can easily be obtained by adding the relevant column (such as vendor name) into our pivot file and then copying that information the “Hidden Tail” worksheet. Follow the example shown below. I personally follow this and then speak with the vendors to consolidate and negotiate.
Step 8: Repeat the same instructions for the “Tail Spend” category which have “Contracts Available”. Follow the picture shown below:
Congratulations if you have made it to this far! You have successfully identified Hidden Tail in your company spend as highlighted under “A”. Now, in the next chapter, we will move to the section of Contract Not Available, (in both Major Spend and Tail Spend, as highlighted under “B”) classes and methods to slice and dice to find even more cost-saving opportunities!
Chapter 05
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/
