Cellwise Start free

Excel guide · 4 min read

How to calculate purchase price variance (PPV) in Excel

Quick answerPurchase price variance is (actual unit price - standard unit price) x actual quantity. With the standard price looked up from an item master, the per-line formula is =(F2-H2)*E2. A positive result means you paid more than standard (unfavourable), a negative one means you paid less (favourable). Sum the column for total PPV and divide by spend for PPV %.

Sample data

Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.

RowABCDEFGHIJKL
1PODateSupplierItemQtyUnit priceEach per unitStd pricePPVLabelSpend
2PO-3101Sep 4, 2026FastlineBRK-105002.5512.475Unfavourable1275
3PO-3102Sep 11, 2026NorthboltSCR-501206.516.8-36Favourable780
4PO-3103Sep 18, 2026FastlinePNL-086019.4118.554Unfavourable1164
5PO-3104Sep 25, 2026SealcoGSK-2220000.9210.95-60Favourable1840
6PO-3105Oct 2, 2026NorthboltBRK-108002.3512.4-40Favourable1880
7PO-3106Oct 9, 2026FastlinePNL-084018.9118.516Unfavourable756
8PO-3107Oct 16, 2026SealcoGSK-2215001.0110.9590Unfavourable1515
9PO-3108Oct 23, 2026NorthboltSCR-501078126.8-36Favourable780

Download this sheet as an Excel practice file (.xlsx)

Set up PO lines and an item master

Export your purchase order lines with one row per line: PO number in A, order date in B, supplier in C, item in D, quantity in E and the unit price on the PO in F. Keep the standard costs in a separate item master (M:N in the sample), one row per item, priced per each. Standards change once or twice a year; PO prices change every order, so the two never belong in the same column. If your POs start life in Excel, the free purchase order template gives you the line layout (item, quantity, unit price, discount) to export from.

H2 fetches the standard price for the line: =XLOOKUP(D2,$M$2:$M$5,$N$2:$N$5), filled down. The dollar signs keep the master table fixed while the item reference moves. If an item is missing from the master, XLOOKUP returns #N/A, which is what you want here: a PO line with no standard is a gap in the master, not a zero variance. In Excel 2019 or earlier, use =VLOOKUP(D2,$M$2:$N$5,2,FALSE).

=H3

Result: 6.8

6.8: the standard price per box for SCR-50 screws, pulled from the item master.

The PPV formula, line by line

For a line bought in the same unit as the standard, PPV is =(F2-H2)*E2. PO-3101 bought 500 brackets at 2.55 against a standard of 2.40, so it cost 0.15 more on each of 500 units: 75 unfavourable.

Wrap it in ROUND(...,2) so prices like 0.92 - 0.95 don't leave you with -59.9999999 that refuses to tie out to the ledger. Then label each line in J2 with =IF(I2>0,"Unfavourable",IF(I2<0,"Favourable","At standard")). Most finance teams use this sign: positive is bad. If yours uses the opposite, flip the subtraction once at the top and say so in the header, so nobody reads a saving as a loss.

=SUM(I2:I9)

Result: 63

63: across all eight lines the company paid 63 more than standard.

The case vs each trap

Look at PO-3108. Northbolt quoted 78 per case, and a case holds 12 boxes. The plain formula compares 78 with a standard of 6.80 per box and reports a huge unfavourable variance, when the real price per box was 6.50, below standard.

Add a conversion column (G, "Each per unit": 12 for a case, 1 for everything else) and convert both price and quantity to the standard's unit before comparing: =ROUND((F2/G2-H2)*E2*G2,2). For PO-3108 that's (78/12 - 6.80) x 10 x 12 = -36, favourable. Any line where the PPV is wildly bigger than its spend is worth checking for this before anyone calls the supplier.

=(F9-H9)*E9

Result: 712

712: the wrong answer you get for PO-3108 without converting cases to boxes. The converted formula in I9 gives -36.

PPV by supplier, by month and as a percent of spend

Spend per line in K2 is =E2*F2. With PPV and spend side by side, SUMIFS gives any summary you need. By supplier: =SUMIFS($I$2:$I$9,$C$2:$C$9,"Fastline") returns 145, Northbolt -112 and Sealco 30. Divide PPV by spend for the same supplier to compare suppliers of different sizes.

By month, use a date range so it works across years: =SUMIFS($I$2:$I$9,$B$2:$B$9,">="&DATE(2026,9,1),$B$2:$B$9,"<"&DATE(2026,10,1)) gives 33 for September; October comes to 30. In Excel 365, =SORTBY(UNIQUE(C2:C9),SUMIFS(I2:I9,C2:C9,UNIQUE(C2:C9)),-1) lists the suppliers from worst variance to best: Fastline, Sealco, Northbolt.

=SUMIFS(I2:I9,C2:C9,"Fastline")/SUMIFS(K2:K9,C2:C9,"Fastline")

Result: 0.045383411581 shows as 4.5% with Percent format

Fastline's PPV is 145 on 3,195 of spend: 4.5% above standard. Across all suppliers it's 63 on 9,990, about 0.6%.

Don't let favourable and unfavourable cancel out

Sealco nets to 30, which looks harmless. Underneath, one gasket order was 60 favourable and the next was 90 unfavourable: the price jumped from 0.92 to 1.01 in three weeks. Netting hides exactly the swings a buyer should ask about.

Report the two sides separately: =SUMIFS(I2:I9,I2:I9,">0") gives 235 unfavourable and =SUMIFS(I2:I9,I2:I9,"<0") gives -172 favourable. For the total size of the misses regardless of direction, =SUMPRODUCT(ABS(I2:I9)) returns 407. A standard that's always beaten or always missed by the same supplier usually means the standard is out of date, not that buying is good or bad. How your company books PPV in the accounts is a question for your finance team; this sheet only measures it.

=SUMIFS(I2:I9,C2:C9,"Sealco",I2:I9,">0")

Result: 90

90: Sealco's unfavourable variance on its own, hidden inside a net of 30.

Common mistakes

Comparing a case price with a per-each standard

Add an "each per unit" column and convert price and quantity before subtracting, as in PO-3108.

Typing the standard price onto each PO line

Keep standards in one item master and look them up with XLOOKUP, so a new standard updates every line at once.

Treating a missing standard as zero variance

Leave the #N/A visible, or use XLOOKUP's if_not_found argument with "No standard", and fix the master.

Reporting only the net PPV

Show unfavourable and favourable separately; offsetting lines hide real price swings.

FAQ

What is the formula for purchase price variance?

PPV = (actual unit price - standard unit price) x actual quantity purchased. In Excel, with the PO price in F2, standard in H2 and quantity in E2: =(F2-H2)*E2.

Is a positive purchase price variance favourable or unfavourable?

With actual minus standard, positive means you paid more than standard, which is unfavourable. Negative means you paid less, which is favourable. Some teams flip the sign, so label the column.

How do I calculate PPV percentage in Excel?

Divide PPV by spend (quantity x actual price) for the same lines: =SUM(I2:I9)/SUM(K2:K9). Some teams divide by spend at standard instead; pick one and use it consistently.

How do I show PPV by supplier in Excel?

Use SUMIFS on the PPV column with the supplier as the criteria, or put the PO lines in a PivotTable with Supplier in Rows and PPV in Values.

Functions used

Related guides

Updated