Most procurement leaders at mid-market companies know they have a spend visibility problem. They have a rough sense of their top ten suppliers, a vague awareness of which categories are growing, and a gnawing suspicion that maverick spend is higher than anyone wants to admit. What they do not have is a clean, queryable picture of where the money actually goes — organized by category, by supplier, by business unit, by time period — that they can walk into a CFO conversation with and defend. The standard answer has always been: hire a BI analyst, build a data warehouse, get a Coupa or Jaggaer implementation funded. In our experience, that answer is wrong for most organizations in the 100–2,000 employee range. A rigorous spend analysis is achievable with existing ERP data, a structured methodology, and Power BI. The challenge is knowing exactly what to do and in what order.

Why most mid-market spend analysis projects stall before they start

The failure mode is almost always the same: someone pulls an AP transaction export from the ERP, opens it in Excel, sees 40,000 rows with inconsistent vendor names, missing GL codes, and descriptions like “INV-2024-00312,” and concludes that the data is too dirty to work with. The project gets deprioritized. Six months later, the same conversation starts again.

The root causes are structural, not technical. ERP systems — whether that is SAP Business One, Microsoft Dynamics 365 Business Central, Oracle NetSuite, or Sage Intacct — are designed to process transactions accurately, not to support category-level analysis. Vendor master records accumulate duplicates over years (“Staples Canada,” “Staples Canada Inc.,” “Staples – Ancaster”). GL account structures reflect accounting logic, not procurement category logic. PO descriptions are written by requisitioners who have no reason to think about downstream analytics.

The correct starting position is to accept imperfect data, apply a triage approach, and extract actionable insight from the 70–80% of spend that can be classified with reasonable confidence — rather than waiting for 100% data quality before starting.

Step one: Extracting usable data from your ERP

Every major mid-market ERP has an AP transaction export capability. The goal is a flat file with the following minimum fields: transaction date, vendor name, vendor ID, invoice number, GL account code, GL account description, line-item description, amount in local currency, and cost centre or department code. If purchase orders exist, adding PO number and PO line description significantly improves classification accuracy.

The extract should cover a rolling 12–24 months. Twelve months gives you a full seasonal cycle and is sufficient for identifying concentration risk and quick-win sourcing opportunities. Twenty-four months lets you see trend lines — which categories are growing, which suppliers are gaining or losing wallet share.

Common extraction mistakes that create downstream problems:

• Pulling header-level rather than line-level data: A single invoice to a facilities management firm may contain janitorial supplies, equipment repair, and pest control. Header-level data classifies all of it under one description. Line-level data gives you accurate category splits.
• Including intercompany transactions: If your ERP books intercompany charges, filter them out before analysis. They inflate spend totals and distort supplier concentration metrics.
• Ignoring credit memos and reversals: Net spend by vendor — not gross invoiced — is the number that matters for sourcing leverage conversations.
• Exporting in functional currency only: For organizations with USD or multi-currency payables, ensure you have both original currency and a consistent converted amount. Mixing the two without normalization produces nonsense totals.

The classification methodology that actually works at mid-market scale

Spend classification is the step where most organizations either over-engineer or under-engineer the solution. Over-engineering looks like attempting to implement a full UNSPSC four-level taxonomy across 40,000 transactions before any analysis begins. Under-engineering looks like dropping everything into five buckets (IT, Facilities, Professional Services, MRO, Other) and calling it done.

The approach that works is a two-level custom taxonomy sized to your organization, applied using a combination of GL account mapping, keyword rules, and manual review of the long tail.

Level one (category family): 8–12 top-level groupings that reflect how your CFO thinks about cost. Common examples for a mid-market B2B firm include: IT and Technology, Professional and Consulting Services, Facilities and Real Estate, Logistics and Freight, Marketing and Media, HR and Benefits Administration, Indirect Materials, Direct Materials (if applicable), and Financial Services.

Level two (category): 3–6 subcategories under each family. Under IT and Technology: Software Subscriptions, Hardware, Cloud Infrastructure, IT Services and Consulting, Telecom. Under Professional and Consulting Services: Legal, Accounting and Audit, Management Consulting, Recruitment.

The classification sequence is:

1. GL-to-category mapping first: Your GL account structure already contains implicit category logic. Map each GL code to a Level 1 and Level 2 category. This typically classifies 50–65% of transaction volume with high accuracy and minimal effort.
2. Keyword rules on vendor name and description: Build a simple lookup table. Vendors containing “Microsoft,” “Adobe,” or “Salesforce” map to Software Subscriptions. Vendors containing “Fedex,” “UPS,” “Purolator” map to Logistics and Freight. Applied systematically, this gets you to 75–85% classification coverage.
3. Manual review of residual spend above threshold: Sort unclassified transactions by dollar amount descending. Manually classify everything above a materiality threshold — typically $5,000–$10,000 per transaction for a mid-market firm. This resolves the majority of remaining spend value without requiring you to touch thousands of low-dollar tail transactions.
4. Bucket the remainder: Everything below your threshold that remains unclassified goes into a “Miscellaneous / Under Review” category. Track it as a percentage of total spend. In our experience, organizations that execute the first three steps well end up with less than 5–8% of spend unclassified by value.

The spend cube: the structure that answers CPO and CFO questions

A spend cube is a three-dimensional view of procurement spend: category on one axis, supplier on a second axis, and business unit or cost centre on the third. It is not a new concept — large enterprises have been building spend cubes in SAP BW and Oracle OBIEE for decades. The insight for mid-market organizations is that the same analytical structure is fully replicable in Power BI with a flat AP transaction file as the source.

The spend cube answers the questions that actually come up in procurement reviews:

Building this in Power BI Desktop requires four tables: the transaction fact table (one row per AP line item), a vendor dimension table with normalized names and any supplier attributes you want to filter by, a category dimension table with your two-level taxonomy, and a date dimension table for time intelligence calculations. Relationships are straightforward: transaction fact table connects to each dimension on its respective key. Total model build time for a competent analyst — or a procurement manager who has spent a day learning Power Query — is typically four to eight hours for a clean initial version.

The 80/20 approach to surfacing actionable insights

Once the spend cube is functional, the temptation is to build every possible visualization and present a comprehensive spend review. Resist this. The analyses that drive procurement decisions are a short list.

Supplier concentration analysis: Rank suppliers by total spend. Identify the threshold at which 80% of spend is accounted for. In most mid-market organizations, this is somewhere between 15 and 40 suppliers. Everything below that threshold is tail spend — important for policy and compliance reasons, but not where sourcing savings come from. Focus strategic procurement activity on the concentration tier.

Category fragmentation analysis: For each category, count distinct suppliers. A company spending $800,000 on IT consulting across 22 different firms has a fragmentation problem — and a consolidation opportunity. Categories with high supplier counts relative to spend volume are candidates for preferred supplier programs or vendor consolidation initiatives.

Spend trend by category: Quarter-over-quarter or year-over-year growth by category identifies where cost is accelerating before it becomes a budget problem. Software subscription spend, in particular, tends to grow 20–40% annually in organizations that have not implemented a SaaS management process — a pattern we see consistently in mid-market technology and professional services firms.

Maverick spend identification: Cross-reference AP transactions against your contract register. Any spend with a supplier where a contract exists but the purchase did not flow through a PO or approved channel is maverick spend. Any significant spend with a supplier where no contract exists is an unmanaged supplier relationship. Both are immediate action items.

Power BI templates: what to build and what to skip

A functional spend analysis dashboard in Power BI needs five pages, not fifteen. Adding pages beyond this threshold increases maintenance burden without proportionally increasing decision-making value.

• Executive summary: Total spend, top 10 suppliers, spend by category family (donut or bar chart), and a trend line showing monthly spend over the analysis period. This is the page a CFO or CPO looks at first.
• Category drill-down: Filter by Level 1 category to see Level 2 breakdown, supplier list within category, and quarter-over-quarter trend. This is where a category manager does their pre-sourcing research.
• Supplier analysis: Supplier-level view with spend over time, categories purchased, and business units purchasing. Essential for supplier relationship management and contract renewal preparation.
• Business unit view: Spend by cost centre or department, filterable by category. This is the page a CFO uses to understand departmental cost drivers.
• Tail spend and compliance: Suppliers below a defined threshold, unclassified spend percentage, and — if contract data is available — maverick spend rate. This is a governance and housekeeping page, reviewed monthly or quarterly.

What to skip: geographic spend maps (irrelevant for most mid-market procurement functions), individual invoice drill-through (that is an AP function, not a procurement analytics function), and real-time refresh (monthly or weekly refresh from an ERP export is sufficient for strategic procurement decisions).

Frequently asked questions

How long does it realistically take to build an initial spend analysis from scratch?

For a mid-market organization with a single ERP system and 12 months of AP data, the realistic timeline from raw data export to a functional Power BI dashboard is three to six weeks. The majority of that time is spent on vendor normalization, GL-to-category mapping, and the manual classification review pass — not on building the Power BI model itself. Organizations that attempt to compress this timeline by skipping the classification methodology step tend to produce dashboards quickly and accurate insights slowly. Doing the data work properly once is faster than rebuilding a flawed model three months later.

Our ERP data is genuinely a mess — multiple systems, inconsistent coding. Where do we start?

Start with the system that holds the most spend. In most mid-market organizations, one ERP or one set of AP books accounts for 70–80% of third-party spend. Build the spend cube for that system first, get it working, and extract the insights from it. A clean analysis of 75% of spend is more actionable than a broken analysis of 100%. The secondary systems can be integrated into a consolidated model in a second phase, once you have proven the methodology and secured stakeholder buy-in from the first output.

Do we need Power BI Pro licenses to share spend dashboards internally?

Power BI Desktop is free and sufficient for building and reviewing spend dashboards in a single-user or small team context. Sharing interactive reports through the Power BI service requires either Power BI Pro licenses (currently priced per user per month) or a Power BI Premium capacity. A practical alternative for organizations that want to avoid licensing cost is to export dashboard pages as PDF snapshots and distribute them through email or SharePoint on a scheduled basis — not as elegant as live interactive reports, but fully functional for monthly spend reviews with leadership teams.

How do we handle spend that crosses multiple categories on a single invoice?

If your ERP captures line-level detail, classify at the line level — this is the correct approach and produces accurate category splits. If you only have header-level data, apply the classification based on the dominant spend purpose of that vendor. A vendor that is 90% IT services and 10% hardware gets classified as IT Services; the rounding error is acceptable at this stage of analysis maturity. Document your classification rules explicitly in a methodology note so that the logic is reproducible when you update the analysis next quarter.

What is the single most common mistake organizations make when starting a spend analysis?

Choosing the tool before defining the questions. We regularly encounter organizations that have purchased analytics platforms, hired BI consultants to build dashboards, and produced visually impressive spend reports — without ever agreeing on what decisions those reports are supposed to support. A spend analysis is only useful if it is connected to a sourcing agenda: which categories are being taken to market in the next 12 months, which supplier relationships need to be restructured, which compliance gaps need to close. Define those questions first. Then build the analysis that answers them. The reverse order produces reports that get reviewed once in a QBR and then never opened again.

Spend Analysis Without a BI Team: A Procurement Leader’s Practical Guide

Most procurement leaders at mid-market organizations know their spend visibility is inadequate but assume fixing it requires resources — a BI team, a data warehouse, an enterprise analytics platform — that are not available to them. This guide offers a structured methodology for extracting, classifying, and analyzing procurement spend using existing ERP data and Power BI, without a dedicated analytics function.