Spend Analysis: How to Build a Clean Spend Cube

Executive summary

Spend analysis is the work of turning accounts payable data into a view of what the company buys, from whom, and at what price. The output is a spend cube: invoice-level data, cleaned and classified, that you can cut by supplier, category, site, cost center, and month. Every sourcing decision, consolidation plan, and savings claim in procurement rests on it, which is why it is the first asset in the Procurement Playbook.

The size of the problem is easy to state. A spend cube is only as good as three numbers: the share of dollars classified to a real category, the share of vendor records merged into true parents, and the share of lines with a usable unit price. If any of the three is low, the totals you present will move every time someone checks them, and category owners will stop trusting the work.

The first move is an extract, not a tool purchase. Pull twelve months of invoice lines from the AP ledger and purchase cards, reconcile the total to the general ledger, and then do three things in order: merge vendors, classify categories, and measure quality. A spreadsheet is enough for a company with a few thousand vendors.

When to use this

Use this guide when:

• You are starting a procurement or cost program and have no consolidated view of spend.
• Category owners and finance disagree about how much the company spends with a supplier or in a category.
• You acquired a business and need to see combined spend across two or more ERPs.
• A sponsor wants a savings estimate and you need to know which spend is addressable.
• You suspect duplicate vendors, duplicate payments, or off-contract buying.

Do not use it when the AP subledger does not reconcile to the general ledger. Fix that first; the cube will inherit every error in the ledger. Also, a spend cube measures what was paid, not what should have been paid. It does not tell you whether a price was contractually correct, which needs the contract data described in Contract Management for Procurement Savings.

Diagnostic questions

Answer these before you build, so you know where the effort will go.

1. How many ERPs, ledgers, or entities hold AP data, and can you extract all of them in the same format?
2. Do invoices carry line-level detail (description, quantity, unit price), or only a header total coded to one GL account?
3. How many vendor records exist, and when was the vendor master last cleaned?
4. Does the vendor master record a tax ID, remit-to address, or parent company that you can use to merge records?
5. What share of spend is coded to catch-all GL accounts such as "other," "miscellaneous," or "general expense"?
6. Is purchase card and expense-report spend visible alongside AP, or is it separate?
7. Which spend is outside the scope you want (payroll, taxes, intercompany, rent, benefits, debt service)?
8. Is there an existing category structure, either a standard tree or the chart of accounts, that people already use?
9. Do POs exist, and what share of invoices carry a PO number?
10. Who will own the cube after the first build, and how often will it be refreshed?

How to read the answers: header-only invoices mean you will classify by vendor and GL account, and your unit-price coverage will be low. A large catch-all share means classification will take more effort than merging. Several ERPs mean currency and vendor ID alignment come first.

The framework: extract, merge, classify, measure, publish

The six stages are sequential, but you will loop. Classification often reveals vendors that should have been merged, and the quality scorecard decides whether another pass is worth the time.

Step-by-step process

Stage 1: Extract and reconcile (week 1)

1. Define the period. Use the most recent twelve complete months, so seasonality is included. Owner: FP&A. Output: a stated start and end date.
2. Extract invoice lines from every AP ledger, plus purchase card and expense data where suppliers appear. Include vendor ID and name, invoice number and date, line amount, currency, GL account, cost center or site, PO number, description, quantity, and unit of measure. Owner: Controller. Output: raw files.
3. Reconcile the sum of the extract to the AP and expense totals in the general ledger for the same period. Explain any difference, such as credit memos, voided checks, or timing. Owner: Controller. Output: a tie-out with the remaining difference stated.
4. Convert currency to one reporting currency using a stated rate method (for example, monthly average), and keep the original amount. Owner: FP&A.

Stage 2: Scope the spend (week 1)

5. Exclude non-addressable spend: payroll and benefits, taxes, intercompany, rent under long fixed leases, debt service, and refunds. Keep a list of what you excluded and why. Owner: FP&A. Output: in-scope spend total and an exclusion list that reconciles to the extract total.
6. Separate direct and indirect spend if the company buys raw materials and components under a different process. Direct and indirect often use different data and owners. Owner: Procurement lead.

Stage 3: Merge vendors (weeks 1-2)

7. Standardize names by removing punctuation, case differences, and legal suffixes (Inc, LLC, Co) for matching purposes only. Keep the original name in a separate column. Owner: Procurement analyst.
8. Match records on tax ID or registration number first, then remit-to address, bank account, and fuzzy name matches. Treat fuzzy matches as proposals. Owner: Procurement analyst.
9. Review merges for every vendor above a spend threshold you set. A wrong merge can hide a real supplier or combine two unrelated companies with similar names. Owner: Category owner or AP lead.
10. Roll up parents where a supplier has divisions or subsidiaries and you want to treat them as one relationship. Keep the child ID so you can still see the entity that invoices you. Owner: Procurement analyst. Output: parent vendor ID on every line.
11. Flag risk records: vendors with the same bank account but different names, same invoice number and amount on two dates, and one-time vendors with large totals. These are inputs for the AP controller, not savings yet. Owner: AP lead.

Stage 4: Classify (weeks 2-3)

12. Choose the tree. Use an established category taxonomy with two or three levels, such as the UNSPSC structure or a published industry tree, and simplify it to what you will actually manage. Avoid designing a custom tree from scratch. Owner: Procurement lead.
13. Classify by vendor first. Most suppliers sell one thing; assign their default category. Owner: Procurement analyst.
14. Override at line level for suppliers that sell multiple categories (distributors, office suppliers, large service firms), using GL account, description keywords, or PO item codes. Owner: Procurement analyst with category owners.
15. Handle the remainder. Rank unclassified lines by dollars, classify the largest first, and stop when the remainder is below the tolerance you set. Owner: Procurement analyst. Output: category on every line, with an "unclassified" label for what remains.

Stage 5: Measure and publish (week 4)

16. Calculate the quality scorecard (below) and reconcile the final cube to the starting extract. Owner: FP&A.
17. Write the data dictionary: field definitions, exclusions, the currency method, the parent rules, and the classification tree. Owner: Procurement analyst.
18. Review with category owners line by line for their top vendors, and correct what they find. Owner: Procurement lead.
19. Publish the cube together with the scorecard. Never share category totals without the quality numbers beside them. Owner: Procurement lead.

Templates

Cube fields (copy into a spreadsheet)

Quality scorecard

Metrics and KPIs

Setting targets without invented benchmarks. Set data-quality targets from your own baseline, and raise them each refresh: classified spend should rise against the figure you measured in the first build, and the top vendors by dollars should reach full review before you ask category owners to commit to a plan. Define "good enough" by decision, not by a universal percentage: if a category owner is about to negotiate, the vendors and lines for that category should be fully reviewed even if the rest of the cube is not. Set the tail threshold from your own distribution, for example the point below which a vendor costs more to manage than it could save.

Common mistakes

1. Ranking suppliers before merging them. Symptom: the largest vendor on the list is a single record, while the actual largest supplier is spread across six. The worked example below shows this.
2. Merging on name similarity alone. Symptom: two unrelated companies with similar names are combined, and a category owner finds a vendor they have never heard of. Merge on tax ID or bank account first, and review fuzzy matches.
3. Classifying by GL account only. Symptom: "repairs and maintenance" contains everything from elevators to software. GL accounts record how finance books the cost, not what was bought.
4. No tie-out to the ledger. Symptom: totals differ from finance's numbers and no one can say why. Reconcile first, and show the remaining difference.
5. One-time project, no refresh. Symptom: a cube built for a presentation and never updated. Schedule a quarterly refresh and name the owner.
6. Presenting totals without quality numbers. Symptom: executives treat a category total as exact when a fifth of its dollars are unclassified.

Worked example

Illustrative. The numbers are made up to show the method, and are not benchmarks.

A company has $18.0M of in-scope indirect spend in the cube. Before merging, the top of the vendor list looked like this, with one supplier appearing as six separate records:

The six records add to $3.10M (1.20 + 0.85 + 0.45 + 0.30 + 0.20 + 0.10). Before the merge, the largest of these records, $1.20M, ranked about fifth on the list. After the merge, the supplier is the largest in the company, at $3.10M, or 17.2% of the $18.0M.

The top ten parent vendors after merging:

Top-ten concentration is $15.05M ÷ $18.0M = 83.6%. The remaining $2.95M (16.4%) sits with a long tail. If that tail is 410 vendors, the average is about $7,200 per vendor ($2.95M ÷ 410).

What this tells the operator:

• A leader that looked like one of many mid-sized suppliers is the largest relationship in the company. Any negotiation or consolidation plan for packaging is now being planned against $3.10M, not $1.20M.
• The top ten account for about five-sixths of spend. Review of those ten is enough to make most category decisions reliable.
• The tail is where processing cost and fragmentation live. The question to ask of it is not "what is the price" but "does each vendor earn its place," which is the subject of the Vendor Consolidation Playbook.

AI prompt assistance

AI is useful for matching and drafting. It will sometimes merge vendors it should not, or classify lines from a description that is too thin. Provide data yourself and check the outputs.

1. Propose vendor merges.

Here is a list of vendor names with total spend and, where available, tax ID and city. Propose groups of records that probably refer to the same company. For each group, give a proposed parent name, the evidence (matching tax ID, matching city, similar name), and a confidence of high, medium, or low. Do not merge records with different tax IDs.

Check: compute the spend before and after the merge for every group and confirm it is identical. Review every group above your spend threshold manually.

2. Classify lines against a tree.

Here is our category tree and 150 invoice lines with vendor, description, GL account, and amount. Assign the lowest-level category you are confident about. If the description does not support a category, return "unclassified." Give one-line reasoning for each assignment and a summary table of spend by category.

Check: review a sample of at least thirty lines, weighted to the largest dollars. Compare the by-category totals to your own recount.

Implementation guide

Keep the merge rules, vendor-to-category defaults, and line overrides. They are the reusable part of the work and make every refresh faster than the first build.

Downloadable assets

The cube field list, quality scorecard, and standard cuts above are designed to be copied into a spreadsheet. The Spend Cube Starter turns a CSV of AP lines into a category rollup and vendor concentration view. It is not published yet.

Related resources

• Procurement Playbook: how the cube and the contract calendar drive the savings program.
• Vendor Consolidation Playbook: what to do with fragmentation the cube reveals.
• AP process design: approval, matching, payment runs: the upstream controls that determine data quality.
• Contract Management for Procurement Savings: adding contract IDs and end dates to the cube.

Frequently asked questions

What is a spend cube?

A spend cube is a cleaned and classified set of invoice-level spend data that you can analyze by supplier, category, site, cost center, and time. It differs from an AP report because vendors are merged into parents and every line carries a category. The cube is the base for sourcing, consolidation, and savings tracking.

How do you do a spend analysis?

Extract twelve months of AP and card lines and reconcile them to the general ledger. Remove non-addressable spend, merge vendor name variants into parents, classify each line to a category tree, and measure data quality. Then review the top vendors with category owners. Publish the totals together with the quality numbers so readers know how far to trust them.

How accurate does spend classification need to be?

It needs to be accurate enough for the decision in front of you. Classify by dollars in descending order, and review fully any category you are about to negotiate. Set a tolerance for the unclassified remainder, publish it, and improve it each refresh. Chasing complete classification of tiny lines costs more than it returns.

Can you build a spend cube in Excel?

Yes, for a mid-market company with thousands of vendors and tens of thousands of lines, a spreadsheet or a simple database is enough. The hard part is not the tool but the rules: vendor merging, the category tree, and the tie-out to the ledger. Keep those rules documented so a refresh does not start from scratch.

How often should a spend cube be refreshed?

Refresh it quarterly at minimum, and monthly for categories with active sourcing events. Reuse your merge rules and vendor-to-category defaults so each refresh takes a fraction of the first build. Reconcile each refresh to the ledger and compare the quality scorecard to the prior period before publishing.

How this works in a System of Performance

A spend cube goes stale when the merge rules, category assignments, and corrections live in a spreadsheet one analyst owns. A Performis routine can take on a recurring job like this, with the scope and fit agreed with you: it works from the records your team supplies, prepares the work for your named reviewer, and keeps each approval on record. To see how clean your own vendor and spend data is, bring it to us as a launch partner.