# Purchase Price Variance Analysis Worksheet > Source: https://netray.co/tools/purchase-price-variance-analysis-worksheet > Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems. > Section: Free Tools ## Short answer List received parts with standard and purchase price to see PPV in currency and percent, and which items drive it. ## Category cost-finance ## Industry Discrete Manufacturing ## Audience - Controller - Buyer - Cost accountant ## Tool **Kind:** worksheet **Mode:** editable **Currency:** $ **Columns:** #### Part number **Key:** part **Type:** text #### Vendor **Key:** vendor **Type:** text #### Qty received **Key:** qty **Type:** number #### Standard cost **Key:** std_cost **Type:** number **Format:** currency #### PO price **Key:** po_price **Type:** number **Format:** currency #### Standard value **Key:** std_ext **Type:** number **Expr:** qty * std_cost **Format:** currency #### PPV (+ unfavorable) **Key:** ppv **Type:** number **Expr:** (po_price - std_cost) * qty **Format:** currency #### PPV percent **Key:** ppv_pct **Type:** number **Expr:** (po_price - std_cost) / std_cost * 100 **Format:** percent **Rows:** **Part:** AL-6061-PL-0.5 **Vendor:** Metro Metals **Qty:** 120 **Std cost:** 42.5 **Po price:** 46.1 **Part:** FST-M6-SHCS **Vendor:** Fastline **Qty:** 5000 **Std cost:** 0.11 **Po price:** 0.1 **Part:** BRG-6204-2RS **Vendor:** Precision Bearings **Qty:** 400 **Std cost:** 3.2 **Po price:** 3.55 **Part:** CBL-HRN-220 **Vendor:** Wiretech **Qty:** 250 **Std cost:** 18.75 **Po price:** 18.75 **Part:** SS-304-BAR-25 **Vendor:** Metro Metals **Qty:** 60 **Std cost:** 31 **Po price:** 28.4 **Totals:** #### Total standard value **Key:** std_ext **Agg:** sum **Format:** currency #### Net PPV **Key:** ppv **Agg:** sum **Format:** currency #### Largest percent variance **Key:** ppv_pct **Agg:** max **Format:** percent **Summary:** #### Net PPV as percent of standard value **Key:** net_ppv_pct **Expr:** sum_ppv / sum_std_ext * 100 **Format:** percent **Primary:** true **Notes:** Positive PPV means you paid more than standard (unfavorable). Negative means you paid less (favorable). Keep standards and PO prices in the same unit of measure and currency. ## Content **Intro:** Purchase price variance shows how far what you paid drifted from what you planned. This worksheet lists receipts with standard and actual price, so you can see the total and which parts cause it. Edit rows and export to Excel for the buyer review. **Sections:** #### How to use the worksheet **Body:** Paste or type one row per received part: quantity, standard cost and the price on the purchase order. The sheet computes standard value, PPV in currency and PPV as a percent of standard. Sort or review the largest variances first. Compare vendors for the same part by adding separate rows. Use the summary to see whether net variance is a rounding issue or a pattern worth acting on. **Bullets:** - Match units of measure before comparing prices - Convert foreign currency at the rate your system uses - Separate freight and duty from item price #### The formula **Body:** PPV equals actual price minus standard price, multiplied by quantity received. Positive means unfavorable under the sign convention used here. Many ERPs post this variance to a price variance account at receipt or invoice match, so the same number should appear in your ledger. In the example, aluminum plate cost 3.60 more per sheet across 120 sheets, adding 432 of unfavorable variance, partly offset by favorable stainless bar. #### Reading the result **Body:** A small net figure can hide large offsetting variances, so look at the gross movement too. Persistent unfavorable PPV on a commodity suggests the standard is stale and should be revised, not that buyers are failing. Persistent favorable PPV may mean standards were set too high, which overstates inventory and product cost. Review standards at least annually and after known price events, and link variance to supplier and contract terms. #### Common mistakes **Body:** Comparing prices in different units, ignoring quantity breaks, treating all variance as buyer performance, and not separating timing from real price change. Also watch for receipts valued at standard but invoiced later at a different price, which creates invoice price variance in some systems. In SAP, for example, standard price materials post differences to price difference accounts, whereas moving average materials follow a different path. **Cta:** If you want PPV by part and vendor pulled directly from your ERP each week, we can help build a read-only report with the underlying query shown. **Faq:** #### Is positive PPV good or bad? **Answer:** Under the convention in this worksheet, positive means you paid more than standard, so it is unfavorable. Some systems flip the sign. Check your ledger convention before comparing numbers across reports. #### How often should standard costs be updated? **Answer:** At least annually, and sooner after large commodity or supplier changes. Constantly large variances on a part usually mean the standard no longer reflects market reality. #### Does PPV include freight and duty? **Answer:** Not by default. Item price variance is separate from landed cost variance. Track freight and duty in their own cost elements or allocations so buyer price performance is not mixed with logistics. #### Can PPV be used to judge buyers? **Answer:** Only with care. Variance depends on how standards were set, market moves and engineering changes. Use it as a signal for review, together with savings against quoted prices and supplier performance. ## Search terms purchase price variance worksheet, PPV analysis excel, standard cost vs PO price template, ppv calculation by part, purchasing variance report template, material price variance spreadsheet, favorable unfavorable price variance --- Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json