Popular KPIs & Limitations of Excel for Spend Analysis — Chapter 11

Published in

ProcurementIntel

5 min readJun 20, 2020

Chapter 11 — Standard off the shelf KPIs to analyze your procurement spend & Limitation of Excel for Procurement Spend Analysis

Popular Spend Analytics KPIs

In this section, I will list some of the standard KPIs that you can find on most of the spend analytics tools available in the market. However, these standard KPIs can easily be analyzed using excel also.

Spend by Top 10 Suppliers:

By this time you must have developed a rough idea on how to populate these tables. But, let me describe one of the many such tables.

Rows Section:

Put “Legal Name” (i.e.vendor name)

Values Section:

Put “ITM_TOT_AM” (i.e. PO Total Value)
Followed by “ITM_TOT_AM” (i.e PO Total Value) expressed in terms of %

To express the PO Total Value in terms of %, follow the steps as shown below:

Step 1: Click on the dropdown menu in Columns

Step 2: Choose “Show Values As”

Step 3: From the drop-down list, choose “% of Parents Row Total”

At the end of step 3, we will have our table organized as shown in the picture below.

Done! Now, I will lay a general format here, which you can follow to perform various types of slicing & dicing on your Procurement Data. Pivot tables are extremely useful and if used correctly, can help in analyzing most of the Spend Analytics KPIs. The diagram below highlights the components of a pivot table.

Let me explain a few basic concepts of a pivot table. There are 4 sections in any pivot table

“Filters”, “Columns”, “Rows”, “Values”

Rows

In simple terms, you can put your Criteria in this section. For example: Spend BY Supplier, Spend BY Item, Spend BY Category, etc. So, Supplier, Item, and Category are examples of Criteria. To remember this rule, anything comes after “BY” comes in Row.

Values

The data portion comes in this section. Example, PO Value, Quantity, etc.

Filters

Filters are data ranges that you want to include or exclude in your analysis. These are master level filters. For example, you can put PO Date in the filter and choose a PO range (from and to values). Once you choose, the rest of the data in remaining pivot table fields will adjust accordingly.

Columns

Similar to Rows, Columns also are used to put Criteria. You can use either Rows or Columns to put your Criteria. The only difference (as the name suggests), if you want your criteria to appear in rows, put them in Rows otherwise put them in Columns.

So, coming back to our example of Spend by Top 10 Suppliers, once you have completed step 3, your table will look like the picture shown below:

Done. Similar to Spend by Top 10 Suppliers, we have the following standard KPIs:

2.) Spend by Organization / Plant / Business Units

3.) Spend by Category Year on Year

4.) Spend by Supplier Country

5.) Spend by Payment Term

6.) Spend With PO vs Spend Without PO

7.) Spend by Currency Type

8.) Spend by Single Source Vendor vs Multiple Source Vendor

For this, you will first have to count the number of vendors for each item. This has already been covered in Chapter 03. Once you have the number of vendors, you can easily add an IF condition:

=IF(A1=1,” Single Source Vendor”, “Multiple Source Vendor”)

Where

A1 represents the column where Vendor Count is available.

Our logic is simple

If the Number of Vendors count is 1, write “Single Source Vendor”, otherwise write “Multiple Source Vendor”.

9.) No. of POs and Average Value of POs per Supplier

10.) PO Count Share per Category

11.) New Supplier Per Category Development Year on Year

12.) Local Vendor Price vs Global Vendor Price For The Same Item

Limitations of Doing Spend Analysis Using Excel

Although we have seen what we can achieve doing Spend Analysis using Excel, there are few limitations we should be aware of. The idea is to evaluate if these limitations are of considerable impact in your business environment, you should consider alternate tools.

Limitation 1: Taxonomy

Taxonomy is one of the most important pre-requisite of doing spend analysis in Excel. Taxonomy refers to categories of material codes/SKU codes. For example,

If you do not have a well-defined Taxonomy, your spend analysis in Excel will not be effective. That said, we can still attempt to build our Taxonomy in Excel provided the number of SKUs is within a reasonable range. We can use Fuzzy Search functions in Excel to identify & group similar material codes together.

By reasonable range, I mean if the number of material codes are less than 1000 — 2000, we can still attempt to develop a taxonomy in Excel. Beyond that, it is recommended to utilize external services that can develop a taxonomy.

Some of the standard references available for Taxonomy are

UNSPSC: United Nations Standard Products and Services Code
NAICS: North American Industry Classification System
SIC: Standard Industrial Classification
FSC: Federal Supply Classification
NIGP: National Institute of Governmental Purchasing

However, I recommend you to develop your own custom Taxonomy by utilizing the first & second level classification of UNSPSC and then adding your own custom categories.

Limitation 2: Data Cleansing

Excel has limited functionalities for data cleansing. Most of the cleansing of data has to be done manually. Examples of Data Cleansing include:

Removal of Duplicate Transactions (like Vendor Codes)
Identification of Group Company from list of Subsidiaries (such as ABB China and ABB Germany to be grouped under common group ABB)

Limitation 3: Excel Data Handling Capability

This might come as a bit of disappointment. Excel performance dips significantly while handling large file sizes. Especially, if your file size is greater than 80 MB (Yes, I have worked with such files!).

Conclusion

My recommendation is to start your spend analysis using Excel and evaluate your limitations (if any). If these limitations are significant, then the external service provider must clearly demonstrate how they are mitigating those limitations.