How is purchase price variance calculated?

PPV is the quantity purchased times the difference between the actual unit price and the standard unit price. Buying 1,000 units at 5.20 against a standard of 5.00 gives a PPV of 1,000 x 0.20 = 200, unfavourable, on an actual cost of 5,200 and a standard cost of 5,000. That is the same answer as the Purchase Price Variance calculator.

Is a positive PPV good or bad?

In this workbook a positive PPV is unfavourable: you paid more than the standard price. A negative PPV is favourable. The Direction column spells it out on every row, and the Dashboard shows unfavourable and favourable variance separately as well as the net figure, so a 4,000 surcharge is not hidden by a 3,000 saving elsewhere.

Should freight and duty be in the actual price?

Only if your standard price includes them. Actual and standard prices must be on the same basis, or the variance is just the freight. Setup has a field where you record the basis, for example delivered before duty. If your standard is ex-works, type the ex-works invoice price and track freight and duty in a landed cost sheet instead.

Why is PPV different from material usage variance?

PPV is measured when material is bought or invoiced and only looks at price. Usage variance looks at how much material production consumed against the standard quantity. A cheaper casting that scraps more can show a favourable PPV and an unfavourable usage variance at the same time, so review both before you call a price change a saving.

How do I handle a price quoted per 100 pieces or per kilogram?

Give the item that unit of measure on Setup, for example 100 pcs or kg, with its standard price per that unit, and type the quantity in the same unit. Thirty boxes of 100 bolts at 13.55 against a 14.50 standard is 30 x (13.55 - 14.50) = -28.50, favourable. The Check column flags a price more than 50% off standard, which usually means the unit is wrong.

What do the tolerance and line limit do?

The PPV tolerance is your limit for net PPV as a percentage of standard spend for the period in view; the KPI tile shows On target, Near target or Above target against it. The line limit flags individual lines priced more than that percentage above standard, for example 3%, so the buyer can check them. Both are your own settings, not industry benchmarks.

Finance and Investment

Purchase Price Variance Tracker Excel Template

Log every receipt or invoice line against its standard price and see net, unfavourable and favourable purchase price variance by month, supplier, category and item on a live dashboard.

Overview

This purchase price variance tracker is a working Excel workbook for buyers, purchasing managers and cost accountants who have to explain why material cost differs from standard. It ships with six months of example receipts for a fictional valve maker, with a foundry surcharge, an electronics shortage and a fastener re-quote in the data, so the dashboard and charts are populated the moment you open it. Replace the example rows and lists with your own and everything recalculates.

Each log row holds the quantity received and the actual price paid per unit of measure. The standard price comes from the Items list on Setup, so it is typed once per item. The workbook then calculates actual cost, standard cost and PPV, which is quantity times actual price minus standard price, the same formula as the Purchase Price Variance calculator on this site. Positive PPV is unfavourable (you paid more than standard), negative is favourable. Every percentage on the Dashboard is total PPV divided by total standard cost, so a large order weighs more than a small one.

In practice, add receipts as invoices are matched and review the Dashboard at month end. Filter to one month to see net PPV against your tolerance, read the supplier chart to see who drove it, and use the item Pareto to pick the items to renegotiate or to re-standardise. Because favourable and unfavourable variance are shown separately, a surcharge on one supplier cannot hide behind a saving on another.

What this template includes

Setup with your currency label, a PPV tolerance for the period, a per-line flag limit, and supplier, category and item lists with the standard price of every item per unit of measure
PPV Log table: date, PO number, supplier, item, quantity and actual price per row; standard price, unit of measure and category looked up from Setup
Per-row actual cost, standard cost, PPV, PPV % of standard, a Favourable or Unfavourable word and a line status against your flag limit
Check column that names problems in words: item or supplier not on the Setup list, missing quantity or price, duplicate receipt line, a price 50% off standard that is probably the wrong unit
Dashboard with category and date filters, KPI tiles for net, unfavourable and favourable PPV and PPV % of standard spend against your tolerance
Three native charts: PPV by month with favourable variance below the axis, net PPV by supplier, and a Pareto of unfavourable PPV by item
Summary tables by month, supplier and category with spend at standard, actual spend, unfavourable, favourable and net PPV; the supplier and category tables also count the lines above your flag limit
Method sheet with every formula, and a Verify sheet that re-checks the formulas against an independent reference, including the PPV calculator's worked example
A CSV with live formulas of the PPV Log for any spreadsheet app; the dashboard, charts, dropdowns and self-check are in the .xlsx

Suggested use case

Use it for the monthly purchase price variance review, to explain material cost against budget or standard, to find the suppliers and items driving unfavourable variance before the next negotiation, and to show the effect of a re-quote or a surcharge.

How to use it

On Setup, type your company, currency label, PPV tolerance and line flag limit, and say what your standard prices include (ex-works, delivered, duty paid).
Replace the example suppliers, categories and items with your own, giving each item its standard price per unit of measure.
On PPV Log, add one row per receipt or invoice line: date, PO number, supplier, item, quantity and the actual price per unit of measure.
Fix anything the Check column flags; Line status shows the lines priced beyond your limit.
Open the Dashboard and filter by category or date range, for example one month, to review net, unfavourable and favourable PPV.

Frequently Asked Questions

How is purchase price variance calculated?: PPV is the quantity purchased times the difference between the actual unit price and the standard unit price. Buying 1,000 units at 5.20 against a standard of 5.00 gives a PPV of 1,000 x 0.20 = 200, unfavourable, on an actual cost of 5,200 and a standard cost of 5,000. That is the same answer as the Purchase Price Variance calculator.
Is a positive PPV good or bad?: In this workbook a positive PPV is unfavourable: you paid more than the standard price. A negative PPV is favourable. The Direction column spells it out on every row, and the Dashboard shows unfavourable and favourable variance separately as well as the net figure, so a 4,000 surcharge is not hidden by a 3,000 saving elsewhere.
Should freight and duty be in the actual price?: Only if your standard price includes them. Actual and standard prices must be on the same basis, or the variance is just the freight. Setup has a field where you record the basis, for example delivered before duty. If your standard is ex-works, type the ex-works invoice price and track freight and duty in a landed cost sheet instead.
Why is PPV different from material usage variance?: PPV is measured when material is bought or invoiced and only looks at price. Usage variance looks at how much material production consumed against the standard quantity. A cheaper casting that scraps more can show a favourable PPV and an unfavourable usage variance at the same time, so review both before you call a price change a saving.
How do I handle a price quoted per 100 pieces or per kilogram?: Give the item that unit of measure on Setup, for example 100 pcs or kg, with its standard price per that unit, and type the quantity in the same unit. Thirty boxes of 100 bolts at 13.55 against a 14.50 standard is 30 x (13.55 - 14.50) = -28.50, favourable. The Check column flags a price more than 50% off standard, which usually means the unit is wrong.
What do the tolerance and line limit do?: The PPV tolerance is your limit for net PPV as a percentage of standard spend for the period in view; the KPI tile shows On target, Near target or Above target against it. The line limit flags individual lines priced more than that percentage above standard, for example 3%, so the buyer can check them. Both are your own settings, not industry benchmarks.