5 min read

What is Spend Cube Analysis? Including Power BI Example

Philip Ideson : July 29, 2024

Blog Post Learning Article

As a procurement professional, you need to be able to analyze and understand your organization’s spending patterns in fine detail. You’re likely to come across spend cube analysis – a powerful way to slice and dice spend data in order to understand spending patterns and uncover new opportunities.

Let’s go through what a spend cube is and how it can be used by today’s data-driven procurement teams. At the end of the article I’ll also share a spend cube example you can access via Microsoft Power BI.

 

What is spend cube analysis?

Spend cube analysis is a popular method for visualizing and analyzing procurement data across multiple dimensions. The spend cube consists of three primary dimensions: suppliers, categories, and cost centers or departments. These dimensions form the axes of the cube, creating a matrix of data points that represent your organization’s spending.

Spend cube analysis in procurement

How a spend cube is used in procurement

At its core, spend cube analysis serves as a diagnostic tool for procurement teams and strategic sourcing professionals. It helps identify spending patterns and trends, uncover opportunities for cost savings, possibly detect maverick spending, and even contract leakage. In addition, a spend cube can prioritize areas for strategic sourcing initiatives and effective category management. 

Spend cube analysis provides the foundation for data-informed decision-making in procurement. It can support various strategic initiatives, including supplier relationship management, category strategy development, budgeting, cost control, and performance tracking.

 

Examples of opportunities uncovered by spend cube analysis

One of the most significant benefits of spend cube analysis is its ability to reveal hidden patterns and relationships in your procurement data. A few examples include:

  • Price standardization. You might discover that certain departments are purchasing similar items from different suppliers at varying prices. This insight could lead to standardization efforts and more favorable contract negotiations. 
  • Supplier consolidation. You might identify categories where spending is fragmented across numerous suppliers, presenting an opportunity for supplier consolidation and better pricing by leveraging an increased volume.
  • Contract (re-)negotiation. You might discover that a particular supplier accounts for a significant portion of spending in multiple categories across different departments, indicating an opportunity for consolidation or negotiation.
  • Maverick spend reduction. You could uncover and reduce off-contract spend, or identify which business units need training and alignment on procurement processes.

 

Common challenges for spend cube analysis

While a spend cube can give you insights, it isn’t always easy to find data you can trust. Some of the most common challenges for spend cube analysis involve data quality issues, such as incomplete, inconsistent, or inaccurate data, which can compromise the reliability of the analysis. Integrating data from multiple disparate sources, often stored in different formats and systems, adds complexity to data consolidation and harmonization efforts. You need to have well-defined data models to establish and maintain accurate relationships between various dimensions (e.g., suppliers, cost centers, commodities). 

A second challenge for spend cube analysis is that the insights uncovered can become outdated in today’s fast-moving market. In the past, it was not uncommon to conduct spend analysis on an annual, or even less frequent, basis. With so much inflationary pressure and globally integrated supply markets, relying on year-old data for optimizing procurement strategies will not help you stay ahead of the competition.

A final common challenge is that advanced spend cube analysis requires specialized data analytics skills to create and maintain integrations between different data sources. Today, the analysis is mostly done by dedicated software or by procurement analytics professionals. To maintain up-to-date and accurate spend data requires an investment of time and resources.

 

Automated spend cubes for procurement performance tracking

Spend cube analysis can help you keep track of your procurement team’s performance over time and prove that your strategies are working. A spend cube that is developed in partnership with corporate finance can provide a baseline of procurement performance metrics that both parties can trust.

Increasingly, businesses are choosing to automate the process of creating spend cubes. By regularly updating your spend cube using spend analysis software, you can track long-term changes in spending patterns, assess the impact of sourcing initiatives, and identify emerging market trends. If you’re not ready to invest in spend analytics software, you can regularly update a spend cube using commonly used business intelligence tools like Power BI or Tableau.

 

How to create a spend cube in Power BI

Business intelligence tools like Microsoft’s Power BI are a good option for creating spend cube reports because of their data integration and visualization capabilities. Power BI allows you to model relationships between spending dimensions like suppliers, cost centers, and commodities with drag-and-drop functionality. In addition, its DAX functions support detailed and customized calculations you may need for spend analysis.

You can create your own spend cube reports using Power BI and Excel. Below is a detailed guide on how to accomplish this in several steps, including data preparation, data modeling, and visualization:

 

Step 1: Data preparation in Excel

  1. Collect Data:
    • Gather data related to spending, including details about suppliers, cost centers, commodities, and amounts.
    • Ensure your data is in a structured format, typically in tables.
  2. Organize Data:
    • Open Excel and create separate sheets for each dimension (Suppliers, Cost Centers, Commodities) and the fact table (transaction data).
    • Each sheet should have a clear header row with columns such as:
      • Suppliers: SupplierID, SupplierName
      • Cost Centers: CostCenterID, CostCenterName
      • Commodities: CommodityID, CommodityName
      • Transactions: TransactionID, SupplierID, CostCenterID, CommodityID, Amount, Date
  3. Clean Data:
    • Remove any duplicate entries.
    • Ensure there are no missing values or anomalies in the data.
    • Format the data as tables (Ctrl + T) to make it easier to work with.
  4. Save Data:
    • Save the Excel file with a meaningful name, such as SpendData.xlsx.

 

Step 2: Import data into Power BI

  1. Open Power BI Desktop:
    • Launch Power BI Desktop.
  2. Load Data:
    • Click on Get Data and select Excel.
    • Browse to your SpendData.xlsx file and load the necessary tables (Sheets).
  3. Data Relationships:
    • Go to the Model view in Power BI.
    • Ensure relationships between tables are correctly established based on the IDs (e.g., SupplierID in Transactions table linked to SupplierID in Suppliers table).

 

Step 3: Create spend cube in Power BI

1. Create Measures:

    • In the Data view, create measures for your spending analysis. For example:

Total Spend = SUM(Transactions[Amount])

2. Create Visualizations:

    • Go to the Report view and start creating your visualizations:
    • Matrix Visualization:
      • Add a Matrix visual.
      • Drag CommodityName to Rows, CostCenterName to Columns, and Total Spend to Values.
    • Slicer for Supplier:
      • Add a Slicer visual.
      • Drag SupplierName to the slicer to filter the data by suppliers.

3. Customize Visuals:

    • Format the visuals to match your reporting standards.
    • Add titles, labels, and tooltips for better clarity.

4. Create Filtered Views:

    • Create additional visuals or pages to show filtered views, such as:
      • Spend by Supplier
      • Spend by Cost Center
      • Combined filters (Supplier and Cost Center, etc.)

 

Step 4: Publish and share reports

  1. Publish to Power BI Service:
    • Save your Power BI file (e.g., SpendCubeReport.pbix).
    • Click on Publish and select your workspace in Power BI service.
  2. Create Dashboards:
    • In Power BI service, pin your report visuals to a dashboard for a consolidated view.
  3. Share with Stakeholders:
    • Share the dashboard and reports with relevant stakeholders.
    • Set up data refresh schedules to ensure the reports stay up-to-date with the latest data from Excel.

 

Download IT spend analysis Power BI report

The above example is an extremely simplified example of how you can use Excel and Power BI to create a spend cube.

To get a more detailed view of what you can include in your spend cube report you can download a sample IT spend analysis Power BI report provided by Microsoft to help you familiarize yourself with using the tool.

 

Bottom line on spend cube analysis

Spend cube analysis is a powerful method for gaining insights about what you spend on, who you spend with, and who does the spending. When executed well, it can help procurement teams on the path to data-driven decision-making and strategic sourcing. 

While the process to create spend cubes is not always easy, there are an increasing number of solutions to streamline and automate your analysis from self-built spend analysis dashboards to spend analytics software. By effectively leveraging these tools, organizations can achieve greater transparency, efficiency, and cost savings, ultimately driving more informed business decisions.

 

Related articles and resources