How to Build a Spend Cube from Scratch

Archive | Upgrade to Premium | Advertise

🌙 Sunday Night Note

First Off, What Is a Spend Cube?

A spend cube is a multidimensional view of your historical purchasing data (or “spend data”).

It helps you answer questions such as:

• Who bought?

• What did they buy?

• From who?

• With which purchasing channel/process?

• Who oversaw these purchases (e.g. Procurement vs Business unit)?

When to Use a Spend Cube?

In the context of sourcing operations, a Spend Cube is an essential tool for developing effective category strategies. Combined with spend forecasts, full visibility into historical spend will give you the “raw material” needed to determine how to maximize value for your business.

However, a Spend Cube is also crucial to help justify your Procurement function’s digital transformation initiatives (e.g. implementing new systems).

That’s why, unless there’s a very good reason against it, I recommend putting in place a spend cube as the first step of any Procurement digital transformation.

Why?

Without a spend cube, you’re essentially driving at night without the headlights on! You have no way of:

• Picking the best direction to get to your destination

  • Which initiatives do we prioritize in sequence? Why? What is the expected return on investment (tangible or intangible) that justifies this sequencing?

• Getting feedback on what happens when you press on the pedal or turn the steering wheel

  • How did our initiative impact our baseline measures? Why?

  • Does this change our prioritization for the next initiative?

If you want to be a data-driven organization, a spend cube is an essential tool to support decision making related to the digital transformation of the function.

What Are the Attributes of a Spend Cube?

All the spend attributes you have available to you!

Here’s a fairly exhaustive list of what *could* be an attribute, if you’re capturing them:

• Who bought?

  • Organizational view (e.g. your organizational structure)

    • Companies

    • Departments

    • Purchasing Organizations

    • Requesters

    • Etc.

  • Management accounting view

    • Budget objects

    • Cost objects (cost centers, internal orders, projects, etc.)

• What did they buy?

  • Accounting view

    • General Ledger account

  • Procurement view

    • Purchasing commodity code

• From who?

  • Suppliers

  • Contracts

  • Catalogs

  • Currencies

  • Payment terms

  • Fx Rate at time of purchase

• With which purchasing channel/process?

  • Non-PO purchase

  • PO purchase

  • PCard purchase

  • Etc… (here’s a good list of most purchasing channels)

• Who oversaw these purchases?

  • Is this addressable spend?

  • Is it under management (e.g. on contract / part of a strategy)?

  • Who is the Category Manager assigned to this spend?

However, chances are you don’t have all this data available to you… Or if you do, it’s split across disparate systems…

But imagine what you could do if all of this information was centralized in a Spend Cube…

How to Stand Up a Spend Cube

Standing up a spend cube can be a fairly easy process or it can be a nightmare… It really depends on the fragmentation of your data.

If you have a very complex landscape (e.g. 18 ERP systems for different parts of your business), then setting up a spend cube is going to be a nightmare any way you slice it… It’s the same process but multiplied by the number of sources…

Let’s go over the steps and you’ll see for yourself:

1. Data Capture

Most Spend Analysis literature will start this process at step #2, extracting your data from your systems. However, it’s very important to realize that all the other steps become easier if you focus on how the data is captured at the source in the first place (e.g. the requester’s purchase requisition). If you have high quality data to begin with, other steps become less important/relevant.

When you first stand up a Spend Cube, don’t focus on Data Capture just yet but know that I’m coming back to it at the end…

2. Data Extraction, Transformation & Load (ETL)

Once you’ve identified where your available data resides (where all the above attributes are captured, if they exist…), you extract that data, transform it into the required format for import into your tool, whether text/number format or file format (e.g. CSV, Excel, XML, etc.), and load it into your spend tool.

3. Data Normalization

Even if you have a single source of data (e.g. ERP), you may have duplicates that represents the same data entity (example: you have the same vendor with multiple different vendor master records in the same system).

Data normalization is about grouping together these duplicates to get a truly consolidated view of spend (e.g. suppliers “I.B.M.” and “IBM Corporation” are all grouped under a main supplier “IBM”).

4. Data Enrichment

Once all attributes are normalized, you can enrich transactions where data is missing, again based on business rules. For example, you can enrich invoices with commodity codes based on the vendor and key words from the description of the invoice line item.

The difficulty is that your enrichment rules can only be as good as the data available to build them. If your invoice says “see quote” in the line item description, there isn’t much to go on for enrichment…

5. Visualization

Once your data is centralized, normalized and enriched, you can now visualize and analyze it to spot trends and opportunities to develop strategies that will generate value for your business.

Of course, what you are able to accomplish will greatly depend on:

• The quality of your source data

• The functionalities of your toolset for the above steps

• Your organization’s ability and resources to stand everything up

This is why a lot of Spend Cube projects end up dying… They drown in complexity….

The “Quick & Dirty” Method to Stand Up a Spend Cube

So, how can we do away with being “purists” to get the value without all the steps above if we’re starting from scratch?

1. Extract what you have from your systems (Invoices, POs, PCard transactions, etc.)

2. Put everything in Excel

3. Identify the different data elements and which fields represent the same information across sheets (e.g. PO_ID in file #1 and Purchase_Order_Num in file #2 are Purchase Order Numbers from 2 seperate systems. They go together.)

   • Use VLOOKUP, INDEX & MATCH formulas to get data in single tables per data element (vendors, POs, invoices, etc.)

4. Try to normalize suppliers, companies and other organizational units as best as you can.

   • Focus on our old buddy Pareto here. Get the big buckets of spend right. Forget perfection.

5. Forget data enrichment

6. Play with pivot tables to see what you can find as opportunities to generate value.

Doing this exercise has 3 main benefits:

1) You’ll now understand the high level requirements if you want to institutionalize the process with a tool. You just did it manually.

You might also be able to prioritize a subset of data/systems if others aren’t worth pulling (e.g. via PowerBI or any other spend analysis tool).

2) You’ll identify opportunities where data doesn’t exist but could easily exist in the future if requesters started capturing it at the source (I told you it was coming back!).

Your first digitalization initiative could be to implement a commodity code or purchase orders in a given purchasing channel to help you break out big spend numbers into pre-defined categories.

3) If the size of opportunities you identify are significant enough, they may be able to finance the implementation of your Spend Cube, other pieces of technology and have savings left over…

If you’ve never done this exercise, let 2024 be the YEAR OF SPEND ANALYSIS.

If you already have a Spend Cube, this is your reminder to look at your data capture processes (purchasing channels) to see if there are quick wins that would drastically augment the quality of your data. This could remove the need for enrichment going forward.

Whatever your situation, when you work on perfecting your Spend Cube, always ask yourself:

• What potential value can be unlocked with this next improvement to our spend cube?

If you don’t have a good answer, you may be optimizing for optimization’s sake… Your efforts will be better used elswhere

💭 Quote of the Week

📊 How Did I Do This Week?

Best comment from last week:

It’s always great talking with fellow ProcureTech nerds… Even better when other people enjoy listening to it! Thanks!
Vote and leave a comment to get featured next week.

📣 Spread the Word

If you 💗’ed today’s edition, share it with another Procurement professional in your network who would find it useful.

The email writes itself: “Read this. Thought of you. I think you’d enjoy it.”