# Purchasing Spend Analysis Worksheet > Source: https://netray.co/tools/purchasing-spend-analysis-worksheet > Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems. > Section: Free Tools ## Short answer Summarize spend by category with supplier counts, PO volume and savings targets to prioritize sourcing projects. ## Category supply-chain ## Audience - Procurement manager - Buyer - Category manager ## Tool **Kind:** worksheet **Mode:** editable **Currency:** $ **Columns:** #### Category **Key:** category **Type:** text #### Largest supplier **Key:** top_supplier **Type:** text #### Number of suppliers **Key:** suppliers **Type:** number #### PO lines per year **Key:** po_count **Type:** number #### Annual spend **Key:** spend **Type:** number **Format:** currency #### Savings target % **Key:** target_pct **Type:** number #### Lever **Key:** approach **Type:** select **Options:** - Consolidate suppliers - Competitive bid - Renegotiate - Spec change - Demand reduction - Hold #### Savings opportunity **Key:** savings **Type:** number **Expr:** spend * target_pct / 100 **Format:** currency #### Average PO line value **Key:** avg_po **Type:** number **Expr:** spend / po_count **Format:** currency #### Spend per supplier **Key:** per_supplier **Type:** number **Expr:** spend / suppliers **Format:** currency **Rows:** #### Competitive bid **Category:** Raw material - aluminum and steel **Top supplier:** Metals distributor **Suppliers:** 6 **Po count:** 420 **Spend:** 2400000 **Target pct:** 4 **Savings:** 0 **Avg po:** 0 **Per supplier:** 0 #### Consolidate suppliers **Category:** Outside processing - plating and heat treat **Top supplier:** Plating vendor **Suppliers:** 9 **Po count:** 610 **Spend:** 780000 **Target pct:** 6 **Savings:** 0 **Avg po:** 0 **Per supplier:** 0 #### Competitive bid **Category:** Machined components **Top supplier:** Machine shop **Suppliers:** 18 **Po count:** 1350 **Spend:** 3100000 **Target pct:** 5 **Savings:** 0 **Avg po:** 0 **Per supplier:** 0 #### Consolidate suppliers **Category:** Packaging and consumables **Top supplier:** Industrial supply **Suppliers:** 14 **Po count:** 2200 **Spend:** 310000 **Target pct:** 8 **Savings:** 0 **Avg po:** 0 **Per supplier:** 0 #### Renegotiate **Category:** Freight and logistics **Top supplier:** Regional carrier **Suppliers:** 5 **Po count:** 1800 **Spend:** 520000 **Target pct:** 3 **Savings:** 0 **Avg po:** 0 **Per supplier:** 0 #### Consolidate suppliers **Category:** MRO and tooling **Top supplier:** Tooling distributor **Suppliers:** 11 **Po count:** 1700 **Spend:** 450000 **Target pct:** 7 **Savings:** 0 **Avg po:** 0 **Per supplier:** 0 **Totals:** #### Total addressable spend **Key:** spend **Agg:** sum **Format:** currency #### Total savings opportunity **Key:** savings **Agg:** sum **Format:** currency #### Total supplier relationships **Key:** suppliers **Agg:** sum **Format:** number #### Total PO lines **Key:** po_count **Agg:** sum **Format:** number **Summary:** #### Blended savings target **Key:** blended **Expr:** sum_savings / sum_spend * 100 **Format:** percent **Primary:** true #### Average PO line value across all spend **Key:** avg_line **Expr:** sum_spend / sum_po_count **Format:** currency **Notes:** 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. ## Content **Intro:** 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. **Sections:** #### How to use the spend analysis worksheet **Body:** 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 **Body:** 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. **Bullets:** - 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 **Body:** 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 **Body:** 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. **Cta:** 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. **Faq:** #### What is a spend analysis? **Answer:** 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? **Answer:** 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? **Answer:** 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? **Answer:** 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. ## Search terms spend analysis template excel, procurement spend analysis worksheet, spend cube template, category spend analysis, purchasing spend report template, savings opportunity spend analysis, supplier consolidation worksheet, spend analysis for small manufacturers --- Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json