Supply Chain & InventoryFree worksheetPDF, Excel, Word export

Purchasing Spend Analysis Worksheet

Spend analysis shows where the money goes so you can decide where sourcing effort pays off. This worksheet lists categories with spend, supplier counts, PO volume and a savings target, then calculates opportunity, average line value and spend per supplier.

Download:

Savings opportunity	Average PO line value	Spend per supplier
$96,000	$5,714	$400,000
$46,800	$1,279	$86,667
$155,000	$2,296	$172,222
$24,800	$141	$22,143
$15,600	$289	$104,000
$31,500	$265	$40,909

Total addressable spend

$7,560,000

Total savings opportunity

$369,700

Total supplier relationships

Total PO lines

8,080

Blended savings target

4.9%

Average PO line value across all spend

$936

Pull annual spend, supplier counts and PO line counts from a purchase order or AP report for the last 12 months. A supplier counted in two categories appears in both rows, so the supplier total is a relationship count, not a unique supplier count.

Automate your spend cube

Optional: a short review of how to refresh spend by category and supplier straight from your ERP.

Optional. The tool and every export work without this. No spam.

How to use the spend analysis worksheet

Export twelve months of purchase order or accounts payable data and group it by category. For each category, enter annual spend, the number of suppliers, the number of PO lines and the name of the largest supplier. Set a savings target percentage and pick the sourcing lever. The worksheet calculates savings opportunity, average PO line value and spend per supplier for each row, and totals them. Sort by savings opportunity in Excel after export to rank projects. Start with the top three categories, since they usually hold most of the value.

What the indicators tell you

Large spend with many suppliers is a consolidation candidate: fewer suppliers means more leverage and less administration. Low average PO line value with high PO count points to process cost, where blanket orders, catalogs or vendor managed inventory can remove effort. A category with one dominant supplier is a risk and a negotiation challenge, so consider qualifying a second source. Spend per supplier highlights fragmentation. Together these show not only where to save but which lever fits: bid, consolidate, renegotiate, change the specification or reduce demand.

High spend, many suppliers: consolidate
Low line value, many POs: simplify the process
One supplier dominates: build a second source
Stable spec, large spend: competitive bid

Setting realistic savings targets

Targets should follow the lever and the market. A competitive bid on a commodity with several qualified suppliers can deliver several percent, while renegotiating a sole-source item may only hold price. Packaging, MRO and indirect categories often have more fragmentation and therefore more room. Avoid applying one blanket target to everything. Record the assumption beside each category and review it with the commodity owner. Treat the blended result as a planning figure, not a promise, and measure actual savings against a baseline price from the previous period.

Common mistakes

Poor data is the main problem. Suppliers appear under several names, items are coded to the wrong category, and AP data includes tax or freight that distorts spend. Clean supplier names before grouping. Do not mix capital spend and operating spend. Do not count price changes caused by volume or specification as savings. Finally, avoid chasing the long tail first. A one percent gain on a large category usually beats a ten percent gain on a small one.

Frequently Asked Questions

What is a spend analysis?

Spend analysis is the process of collecting, cleaning and grouping what an organization buys, by supplier and category, to see where money goes and where savings are possible. It is the base for sourcing strategy, supplier consolidation and compliance work.

What data do I need to start?

Twelve months of purchase order lines or accounts payable invoices with supplier, item or description, quantity, amount and date. Add a category for each line. Even an imperfect export is enough to start if you clean the top suppliers first.

How do I choose which category to source first?

Rank by spend and by savings opportunity, then weigh in risk, complexity and the effort of changing supplier. Large, stable-spec categories with several qualified suppliers usually give the fastest return. Low-risk indirect categories are good for quick wins.

How often should I refresh a spend analysis?

At least annually, with a lighter quarterly check on the top categories. Refresh sooner after acquisitions, large price changes or supplier failures. A rolling twelve-month view avoids distortion from seasonal buying.

If your purchase orders and AP data are in an ERP, Netray can help you build a spend cube that updates itself and answers questions in plain language.

Talk to an Expert Explore More Free Tools

Related Tools

Supply Chain & Inventory

More Supply Chain & Inventory tools

See all

Back to All Tools