What is Spend Analysis?

Spend analysis is the process of collecting spend data from all purchasing sources and systems, cleansing the data, categorizing the data, and analyzing it for the purpose of understanding spending trends and identifying saving opportunities. The data included in the spend analysis might include purchasing data, payment data, credit card data and P-card spend data. Procurement/ strategic sourcing professionals are generally responsible for conducting spend analysis in the organization. The information is not only useful for sourcing professionals but also for management and budget owners.

If you have ever performed spend analysis, you might agree that this could be a daunting task, if you don’t have the right tools and technologies in place.

Most of the spend analysis tools have basic features that can help with your spend analysis effort. If you don’t have these tools in place, don’t worry we will cover how to conduct spend analysis without those tools too. But first, let’s cover the basics of spend analysis.

1. Benefits of Spend Analysis

Before we get into how to conduct spend analysis, let’s look at some of the benefits of spend analysis and its common use cases.

There are many benefits of spend analysis, As per APQC (American Productivity & Quality Center) – Best-in-class companies that have implemented spend analysis programs have lowered their cost of procurement because of cycle time reduction.

Source: APQC

It is important that you define the use cases upfront within your team and with senior management so that a clear data and information structure can be defined upfront.

Download – Procurement Guide to Strategic Savings

A) Spend Visibility

Whether it is the CFO of the company or whether it is a department budget owner, they are seeking better visibility into the company or their department spend. The goal is, of course, to understand how much they are spending but also to understand where they are spending money.

Spend analysis done right can help answer common questions asked by senior management.

Generally, the financial reporting is focussed on spend by GL (General Ledger) chart of accounts. But it is very difficult to get a better handle on your Spend leveraging GL based grouping of spend. The information is useful but not granular enough to make informed decisions.

I am not saying that it can’t work but it doesn’t work in most cases because the GL chart of accounts is not granular enough to provide commodity level visibility.

B) Savings Opportunity Identification

The next most common use case is identifying opportunities for savings/cost reduction. Procurement professionals are charged with cost reduction and they need data to better understand spend at the line item level to come up with a savings pipeline.

Spend analysis done right can help procurement professionals not only achieve saving goals but also helps in better forecasting the savings for the future quarters, months, or years.

C) Spend Forecasting

The ability to forecast spend is an important benefit of spend analysis. There are multiple teams in the organization who can use the spend data to forecast future spending.
For example, finance and planning can use the spend data to understand recurring vendor spend along with long-term contracts to forecast the next few year’s expenditure budgets.

Similarly, the same information can be used by sourcing teams to work with department owners to help them forecast the Spend on their key vendors and categories.

D) Diversity Reporting

One of the key metrics organization track is spending with diverse vendors. That need is driven is either by being a good corporate citizen or a need driven by their key customers. Many customers require vendors to have a certain amount of spend with diverse vendors.

Having data at one single place helps to better understand spend with diverse vendors but also helps uncover opportunities to engage more with diverse vendors in certain categories.

Moreover, diversity reporting is required by the sales team to respond to RFPs, so having this data handy leads to a faster turnaround of RFP responses by your sales teams.

2. How to Get Started with Spend Analysis

So far we have talked about the benefits of spend analysis, now let’s get into how to conduct spend analysis.
If you are new to spend analysis, don’t worry, by the time you are finished reading this, you would be very familiar with how to conduct spend analysis. So with further ado let’s get going

A) Objective

The first step is to define the objectives of the spend analysis exercise. It is important to define the objectives clearly because that will drive your data gathering and analysis efforts. Following are some common objectives of spend analysis

• Understand spend at a granular level so that the sourcing team can identify saving opportunities.

• Understand key vendors so that procurement can define and execute a strategy for strategic vendors.

• Provide visibility to senior management on key spend areas impacting EBITDA margins. For example COGS(Cost of goods sold) spend, SG&A (Sales, General and Administration) spend.

B) Source Systems

The next step is to create an inventory list of all source systems from where you need to pull the spending data.

The goal is to ensure that the entire spending is captured for analysis. If your company has multiple business units, it is likely that you have multiple systems. So the scope of analysis will determine what systems you need to capture. A simple inventory table should capture all data points

C) Schema For Data Capture

Since you are pulling data from multiple systems, they are bound to have a different set of fields.

The first and foremost task is to identify what data you want to be captured and put it through a common data schema. A data schema is a simple definition of what fields you want to be captured and what they mean.

Since different systems have different nomenclatures for the same fields, it is handy to have a common definition of the information you are trying to gather. It would be much easier to gather data from disparate systems if you have a common data schema/structure.

D) Data Availability

Now you have the data schema, the next step is to reach out to your IT team and have them check the effort required to pull the data from different spend systems. Few things to keep in mind

Define the frequency of data refresh

Spend analysis done right is not a one-time activity. You would get better value for your efforts if you refresh the data frequently. The frequency of data extraction depends upon the purpose of spend analysis.

For example, if you are using spend analysis primarily to identify saving opportunities then a quarterly refresh should be sufficient.

That way you can also track how the negotiated savings are being realized.

Data extraction format

The other thing to keep in mind is how data will be provided.

If possible have the data in the same file format (Excel, CSV) so that it is easy to consolidate the data from multiple systems. If you don’t have multiple systems then this should not be of any concern to you.

You can always ask your IT team if it is possible for them to import this data into an enterprise data warehouse (if you use one) and have the data consolidated for you.

If that is not possible, it is not the end of the world. It should not take more than few minutes to consolidate the data manually.

E) Classification Schema

Most of the ERP systems have some way or fashion to categorize the spend transaction into unique buckets. The most common approach is to use General Ledger chart of accounts to categorize data.

In some cases, you might see homegrown or industry-specific nomenclature for classification. If that serves your purpose, use that, if not, there are multiple options available for classification schema. Data needs to be categorized in unique buckets so that analysis is easy.

Here are some Industry standards in order of their popularity

1.UNSPSC stands for United Nations Standard Product and Services Code. It is an open and global standard for efficient and accurate classification of products and services.

It is free to browse and download in PDF format. If you need an alternate format, you can download for $100 from UNSPSC website.

2.NAICS stands for North American Industry Classification schema. It is used by federal agencies to classify businesses.

It is the primary classification schema used by federal agencies for reporting statistics.

F) Data classification

You might have existing GL based spend classification from your ERP system. But it is highly recommended that you reclassify the data into the new classification schema. There are multiple reasons for that

–  The same spend might be misclassified and hence needs correction.

 –  If the category structure is not the same, you might have the same item identified differently across different systems.

– Most of the credit card companies provide a classification for each transaction. It is generally in grouped into MCC (Merchant category codes).

That is good enough for T&E (Travel and entertainment) spend, however, if your employees are using credit cards for other material purchases, then you need to make sure that spend data is categorized in the same way as the spending from other systems.

Having said that, data classification is the most time-consuming part of the whole exercise. Few things to consider while classification

Granularity – How granular you go with data classification?

I hate to say this but the answer is that it depends. You might need granular categorization for certain categories like MRO and direct materials but for products like office supplies, you don’t need to classify it at each individual commodity level.

So unless you are planning to present different types of pens your company purchases, it is useless to categorize the data at that level.

Accuracy

Even with fully automated classification systems, it is hard to achieve 100% accuracy so focus on high ticket items. If you do a simple Pareto on your spend data, you would realize that 80% of your spending is captured by 20% of your transactions.

So focus on important items and ensure they are correctly classified

When it comes to the classification of spend data, there are primarily two approaches

 – Use a third-party tool for analysis. Just google “Spend Analysis Tools” and you can see different vendors who can offer such services. It is always a good practice to do a quick proof of concept to ensure that vendor has the capability to classify data in your domain.

– If you can’t secure funding for such systems then hire an intern and get started with data classification! We will cover some tricks on how to conduct spend analysis using excel

G) Data analysis

This is probably the most important step in the whole spend analysis exercise – Slice and dices the data to identify saving opportunities. There are two approaches to doing this analysis

–  Use a Business intelligence or reporting tool: If your organization has such a tool, then load the classified data into the system. If you don’t have access to such a tool, check with your marketing department or data analytics group if they have one. Between these two groups, I am sure you can find a tool that can help with analysis.

–  If the data analysis tool is not available, then use your old friend Microsoft Excel to run the analysis. You can download a sample template here

The focus of data analysis is to identify trends based on your objectives. If the objective was enhanced visibility, then this exercise should be focussed on identifying spend trends over time.

If the focus is on identifying saving opportunities, then the analysis should be on understanding price variance, supplier proliferation, etc. We will cover some common spend analysis reports in the later sections.

H) Opportunities presentation

The last step in the spend analysis process is presenting the results to senior management or your stakeholders. Here are some common scenarios on how the output of spend analysis is used

Helping stakeholders understand the spend trends

The scope of this analysis is generally tied to a department. The opportunities presentation would be focused on Spend by the vendor, spend by category, individual category trends, and price trends for high ticket items.

For example, the IT director might be interested in understanding the trends in contingent labor or annual software maintenance trends.

Providing visibility

This is generally requested by senior management. Some of the trends which are helpful are

–  Overall spend trend year or year.

–  Spend trend broken down by direct/ COGS(Cost of goods sold) vs indirect spend /SG&A (Sales and general administration) spend.

–  Cost reduction year over year

3. Spend Analysis Using Excel

If you don’t have automated tools for spend analysis, then this section is for you, otherwise, feel free to skip to the next section. In this section, we will cover two areas

How to categorize data using excel

If you don’t have a third party tool or your purchasing system doesn’t have good categorization, then you are left with the option of categorizing data manually.

It is very time consuming but if the dataset is small, you can classify data in a few hours. Get some coffee and let’s get started

Steps to categorize data manually

1. Sort items by a common unique identified – in terms of priority

1. GL code
2. Item description

2. Then create a pivot with item description in rows and then add the count to it. This will only list unique items.

3. Sort items by count.

4. Then copy that data in a separate tab.

5. Start classifying the data line by line. At this time, you are only classifying unique line items. You can speed up the process by searching by keywords and grouping spend together for classification.

6. Perform a Vlookup using the description as a lookup field and then add category information to the main file.

Once you are done with classification, you are ready to move to the data analysis step.

How to analyze data using excel

When it comes to analyzing data in excel, the pivot table is your best friend. If you are not familiar with pivot tables, this blog post is a good start.

Also, make yourself familiar with creating simple charts using excel. If you are not familiar with that, this article covers the basics of how to create charts using excel.

To get you started, you can download this excel template. It is not exhaustive but good enough for you to run different reports.

Common Spend analysis reports

The focus of these reports is on identifying saving opportunities and provide visibility. This is not an exhaustive list but a good start

Spend by vendor

This report can provide visibility into the following areas

Who are your top suppliers and what is the spending pattern with those suppliers

How consolidated or fragmented your spend is. This can be answered by identifying the number of vendors contributing to 80% of spend.

If you have a long tail of vendors contributing to the remaining 20% of the spend, this might be an opportunity to consolidate tail spend.

How you compare with your vendors in terms of spend trends.

Vendors per category

This report helps you to understand how many vendors you have in a given category.

Having many vendors for a category is not necessarily a bad thing. It all depends on the category and your business.

For example, it might be Ok to have multiple vendors for geographically spread operations or in case your company prefers to do business with local suppliers.

The idea is to analyze whether you can leverage volume discounts by consolidating spend across different suppliers.

Price part variance:

It is not uncommon that different locations/departments are buying the same part from different vendors or from the same vendor but at different rates.

This could be a lack of part standardization or due to a decentralized sourcing strategy.

A report like this will quickly identify saving opportunities by consolidating multiple pricing structures and in some cases part standardization across the organization.

Payment term analysis

This report presents total company spend grouped across different payment terms for vendors. The obvious goal of this report is to identify if there are any opportunities to extend payment terms with your high spend vendors. The way to calculate potential savings is the [total amount * potential days extension* WAC ( Weighted average cost of capital). ]

I am sure the treasurer of your company would be very happy to see any progress on this metric.
Before you present numbers, do a quick benchmark check on standard payment terms in your industry.

Spend by business unit

If you have multiple business units, this report helps in understanding how different business units are spending. Most common things to present here

1. Year on Year spending trend.

2. Categories spend broken down into business units.

3. Monthly spend trend.

Conclusion

Spend analysis done right can lead to higher savings and better visibility into company spend. Procurement professionals can use this tool to engage with stakeholders in a fact, data-based fashion.

Not only it increases procurement professionals’ productivity, but it also helps elevate the value of procurement in the organizations and it is a great step towards transforming procurement into a strategic, value-adding function.