# Supplier Spend Pareto Analysis Worksheet

> Source: https://netray.co/tools/supplier-spend-pareto-analysis-worksheet
> Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems.
> Section: Free Tools

## Short answer

An editable spend table that totals supplier spend and orders, calculates average PO value, and gives you the base data for a Pareto or ABC analysis.

## Category

supply-chain

## Audience

- Buyer
- Purchasing manager
- Controller

## Tool

**Kind:** worksheet

**Mode:** editable

**Currency:** $

**Columns:**

#### Supplier

**Key:** supplier

**Type:** text

#### Category

**Key:** category

**Type:** select

**Options:**

- Raw material
- Machined parts
- Castings and forgings
- Electronics
- Packaging
- Outside processing
- MRO
- Freight
- Services

#### Annual spend

**Key:** spend

**Type:** number

**Format:** currency

#### PO count

**Key:** orders

**Type:** number

**Format:** integer

#### Average PO value

**Key:** avg_po

**Type:** number

**Expr:** spend / orders

**Format:** currency

#### Criticality

**Key:** rating

**Type:** select

**Options:**

- Strategic
- Preferred
- Standard
- Tail

**Rows:**

**Supplier:** Northline Castings

**Category:** Castings and forgings

**Spend:** 612000

**Orders:** 48

**Rating:** Strategic

**Supplier:** Pacific Metalworks

**Category:** Raw material

**Spend:** 455000

**Orders:** 26

**Rating:** Strategic

**Supplier:** Delta Forge

**Category:** Castings and forgings

**Spend:** 238000

**Orders:** 31

**Rating:** Preferred

**Supplier:** Summit Machining

**Category:** Machined parts

**Spend:** 164000

**Orders:** 62

**Rating:** Preferred

**Supplier:** Greenline Packaging

**Category:** Packaging

**Spend:** 58000

**Orders:** 40

**Rating:** Standard

**Supplier:** QuickShip Freight

**Category:** Freight

**Spend:** 41000

**Orders:** 120

**Rating:** Standard

**Supplier:** City Industrial Supply

**Category:** MRO

**Spend:** 9500

**Orders:** 85

**Rating:** Tail

**Totals:**

#### Total spend

**Key:** spend

**Agg:** sum

**Format:** currency

#### Total PO count

**Key:** orders

**Agg:** sum

**Format:** integer

#### Largest single supplier spend

**Key:** spend

**Agg:** max

**Format:** currency

**Summary:**

#### Average spend per supplier

**Key:** avg_supplier

**Expr:** sum_spend / count

**Format:** currency

#### Average PO value overall

**Key:** avg_order

**Expr:** sum_spend / sum_orders

**Format:** currency

#### Largest supplier share of spend

**Key:** top_share

**Expr:** max_spend / sum_spend * 100

**Format:** percent

**Notes:** Sort rows from highest to lowest spend, then work down the list adding each supplier's share of total spend until you reach about 80 percent. Those suppliers are your vital few. Pull spend from AP or PO history for a full 12 months and keep currency consistent.

## Content

**Intro:** A spend Pareto shows that a small number of suppliers usually account for most of your purchasing dollars. This worksheet collects spend, order counts and criticality so you can focus negotiation, scorecards and audits where they matter.

**Sections:**

#### How to use the worksheet

**Body:** Export 12 months of purchase or AP data and enter one row per supplier, with annual spend and the number of POs. Choose a category and a criticality level. The sheet totals spend, calculates average PO value for each supplier and shows the largest supplier's share. Sort descending by spend and keep a running total in Excel after export. The point at which cumulative spend reaches roughly 80 percent marks your vital few.

#### The Pareto and ABC method

**Body:** The Pareto principle observes that a minority of causes drive most of an outcome. In purchasing, a minority of suppliers tends to drive most of the spend. ABC analysis formalizes it: A suppliers are the top spend group, B the middle, C the long tail. Thresholds vary by company, and 70 to 80 percent for A is common. Spend is only one axis, so also rate criticality, because a low-spend sole-source part can still stop a line.

**Bullets:**

- A group: manage with scorecards, reviews and contracts
- B group: periodic review and consolidation checks
- C group: simplify with catalogs, cards or blanket orders
- Criticality can override spend rank

#### Reading average PO value

**Body:** Average PO value reveals process cost. A supplier with 85 orders averaging about 110 dollars costs more to administer than the goods are worth. Consider blanket orders, consolidated ordering, vendor-managed inventory or purchasing cards for those lines. Conversely, a high average PO value with few orders points to big commitments that justify contract negotiation and risk review.

#### Common mistakes

**Body:** Mixing currencies, using a partial year, leaving out indirect spend, and splitting one supplier across several vendor IDs all distort the picture. Clean duplicate vendor records first. Also avoid cutting tail suppliers only by size: a small supplier of a regulated or single-source item may be critical.

**Cta:** Netray can pull supplier spend and PO counts directly from your ERP for this analysis, with duplicate vendor detection, on your own infrastructure.

**Faq:**

#### Does the 80/20 rule always hold?

**Answer:** It is a rule of thumb, not a law. Some companies find 90 percent of spend in 10 percent of suppliers. Use your own data to set the cutoff.

#### Which period should I analyze?

**Answer:** Use the last 12 months for a full cycle, and compare with the prior year to spot growth. Seasonal businesses should avoid a short window.

#### Should I include freight and services?

**Answer:** Yes, include indirect spend as separate categories so the picture is complete, but you may analyze direct materials separately for sourcing strategy.

#### How do I deal with duplicate vendors?

**Answer:** Merge or group duplicates before analysis. The same supplier under several IDs hides its true share of spend and weakens negotiating leverage.

## Search terms

supplier spend analysis template, spend pareto excel, abc analysis suppliers template, purchasing spend analysis worksheet, supplier spend by category, vendor spend analysis excel, 80 20 rule purchasing

---
Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json