PPV CalculatorPurchase price variance template
Fill in one row per purchase line, then drop the file into the calculator. It works out PPV for every line, totals favourable and unfavourable variance, ranks the worst lines and suppliers, and splits out exchange rates if you add rate columns.
Columns
| Column | What to put in it |
|---|---|
| Item | What you bought: SKU, part number or description. Optional. |
| Supplier | Who you bought it from. Optional; adds a by-supplier view. |
| Currency | The supplier's invoice currency. Optional, for your reference. |
| Quantity | Quantity actually purchased (received or invoiced). Required. |
| Standard price | Standard price per unit in your reporting currency. Required. |
| Actual price | Actual price per unit. In the supplier's currency if you give rates, otherwise in your reporting currency. Required. |
| Standard rate | Planned rate: one unit of the supplier's currency in your reporting currency. Optional; needs Actual rate too. |
| Actual rate | Rate actually applied to the invoice. Optional; needs Standard rate too. |
Common header names from ERP exports are recognised too: Unit price, Net price, Invoice price, PO price, Standard cost, Budget rate, Exchange rate, Qty, Material, Vendor, and SAP's MENGE, NETPR, STPRS and WAERS.
What the calculated example shows
The example has 11 made-up purchase lines. Total PPV is 2,229.90: 43.00 from price and 2,186.90 from exchange rates. 2 lines are unresolved because a price or a rate is missing, and are left out of the totals instead of being guessed.
Know the number. Now fix the cause.
If the variance comes from what you agreed to pay, the fix is in buying and contracts. If it comes from explaining the number at month end, the fix is in finance. Five questions tell you which.