# Purchase Price Variance Worksheet

> Source: https://netray.co/tools/purchase-price-variance-worksheet
> Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems.
> Section: Free Tools

## Short answer

Calculate purchase price variance line by line from quantity, standard price and actual price, with totals and percent of spend.

## Category

cost-finance

## Industry

Discrete Manufacturing

## Audience

- CFO
- Controller
- Procurement manager
- Cost accountant

## Tool

**Kind:** worksheet

**Mode:** editable

**Currency:** $

**Columns:**

#### PO number

**Key:** po

**Type:** text

#### Part

**Key:** part

**Type:** text

#### Supplier

**Key:** supplier

**Type:** text

#### Qty received

**Key:** qty

**Type:** number

#### Standard price

**Key:** std_price

**Type:** number

**Format:** currency

#### Actual price

**Key:** act_price

**Type:** number

**Format:** currency

#### Standard value

**Key:** std_ext

**Type:** number

**Expr:** qty * std_price

**Format:** currency

#### PPV (positive = unfavorable)

**Key:** ppv

**Type:** number

**Expr:** qty * (act_price - std_price)

**Format:** currency

#### PPV %

**Key:** ppv_pct

**Type:** number

**Expr:** if(gt(std_price,0), (act_price - std_price) / std_price * 100, 0)

**Format:** percent

**Rows:**

**Po:** PO-45012

**Part:** Steel bar 4140

**Supplier:** Metro Steel

**Qty:** 12000

**Std price:** 2.3

**Act price:** 2.48

**Po:** PO-45027

**Part:** Bearing 6204-2RS

**Supplier:** Apex Bearings

**Qty:** 2000

**Std price:** 3.6

**Act price:** 3.52

**Po:** PO-45031

**Part:** Motor 0.75 kW

**Supplier:** Volt Drives

**Qty:** 120

**Std price:** 112

**Act price:** 118

**Po:** PO-45044

**Part:** Control PCBA rev C

**Supplier:** Circuit Works

**Qty:** 400

**Std price:** 43

**Act price:** 41.2

**Po:** PO-45050

**Part:** Housing casting

**Supplier:** Delta Foundry

**Qty:** 900

**Std price:** 16.5

**Act price:** 17.8

**Totals:**

#### Total standard value

**Key:** std_ext

**Agg:** sum

**Format:** currency

#### Total PPV

**Key:** ppv

**Agg:** sum

**Format:** currency

**Summary:**

#### PPV as percent of standard spend

**Key:** ppv_total_pct

**Expr:** if(gt(sum_std_ext,0), sum_ppv / sum_std_ext * 100, 0)

**Format:** percent

**Primary:** true

#### Largest line variance

**Key:** worst_line

**Expr:** max_ppv

**Format:** currency

#### Best line variance

**Key:** best_line

**Expr:** min_ppv

**Format:** currency

**Notes:** PPV is measured at receipt or at invoice match depending on your system. Use the same basis for the whole sheet. Freight and duty variances are often tracked separately from price variance.

## Content

**Intro:** Purchase price variance compares what you paid with what the standard cost assumed. It is one of the first numbers a controller reviews at month end, and the one buyers are most often asked to explain. This worksheet computes it line by line and in total.

**Sections:**

#### How to use the worksheet

**Body:** Paste or type receipt lines: PO, part, supplier, quantity, standard price and actual price. The sheet calculates the extended standard value, the PPV in currency and the PPV percent for every line. Totals show overall PPV and the share of standard spend. Sort in Excel by PPV to see the five or ten lines that explain most of the movement. Positive numbers are unfavorable, negative numbers are favorable.

**Bullets:**

- Use quantity received, not quantity ordered
- Convert prices to the same unit of measure as the standard
- Convert foreign currency at the rate your policy requires
- Flag lines with a variance above your threshold for buyer comment

#### Formula and accounting

**Body:** PPV = quantity received x (actual unit price - standard unit price). When material is received at standard cost, the difference is posted to a purchase price variance account at the moment of receipt or invoice. Inventory then stays at standard, and the P and L carries the variance. If the part has already been consumed, some systems post part of the variance to cost of goods sold or a capitalization account instead.

#### Reading the result

**Body:** In the example, steel and castings are unfavorable and the PCBA is favorable. Net PPV is the sum, and about 2 to 3 percent of spend is typical for a commodity-exposed basket, though your own history is the better benchmark. Split each large variance into market movement, supplier increase, and wrong standard. Only the second is a negotiation issue. The first and third are solved by indexing or by revising the standard.

#### Common mistakes and ERP notes

**Body:** Do not net unrelated favorable and unfavorable items without looking at both. Do not let standards lag a year behind a moving market. In SAP the variance is posted on goods receipt or invoice receipt under the PRD account when price control is standard (S); in SyteLine, Epicor and NetSuite it is posted at receipt against the item standard cost. If you use moving average price, there is no PPV, and price changes flow through inventory instead.

**Cta:** We can build a PPV exception report on top of your ERP receipts that lists the largest variances with supplier and buyer attached. Ask for an expert review.

**Faq:**

#### Is a negative PPV always good?

**Answer:** Not always. A favorable variance can mean a cheaper supplier with quality issues, a smaller order or a standard set too high. Check that the part is the same and that the savings are real before celebrating.

#### How do I handle invoice price differing from PO price?

**Answer:** Many systems record PPV at receipt using PO price, then post an invoice price variance later. For this sheet, use the price basis your general ledger posts to the variance account for consistency.

#### How often should standards be updated to limit PPV?

**Answer:** At least annually, and more often for commodity-driven parts. A rising persistent PPV on the same parts is the clearest sign that standards need revising.

#### Is PPV the same as savings?

**Answer:** No. PPV is relative to standard, and the standard may itself be out of date. True savings are measured against a validated baseline such as last year paid price or an index-adjusted price.

## Search terms

purchase price variance worksheet, PPV calculation excel, purchase price variance template, purchase price variance formula, PPV report manufacturing, material price variance worksheet, standard cost vs actual price

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