Purchase Cost Variance %:
Track and Control Inventory Margin Leaks

How to Build Purchase Cost Variance % in Excel

1. Set Up the Supporting Cost Columns

Start by adding the three supporting columns used throughout the analysis:

• Standard Cost
• Actual Cost
• Purchase Cost Variance

Use simple row-level calculations first so the KPI table is easier to build and audit later.
The video specifically breaks these out into helper columns instead of combining everything into one large formula.

This makes the final KPI setup:

• easier to read
• easier to troubleshoot
• easier to reuse in other reports

2. Create a Unique List of Parts

To build the matrix rows:

• Go to the Part field in the table
• Use the UNIQUE function to pull a distinct list of part categories
• Wrap it with SORT so the list appears in alphabetical order
• Copy the results
• Paste them as values using Ctrl + Shift + V

This gives you a clean vertical list of parts for the left side of the matrix.

3. Create a Unique List of Suppliers

Next, build the supplier list:

• Use the UNIQUE function on the Supplier field
• Wrap it with SORT again to alphabetize the list
• Then use TRANSPOSE so the supplier names run across the top row instead of down a column
• Copy the results
• Paste them as values

Now you have the matrix structure:

• Parts down the rows
• Suppliers across the columns

4. Build the Main Variance Percentage Calculation

Once the layout is ready, calculate the KPI inside the matrix.

The video builds this in two parts:

• Numerator: total purchase cost variance by supplier and part
• Denominator: total standard cost by supplier and part

Use the SUMIFS function for both parts of the calculation so you can filter by:

• supplier
• part

Then format the result as a percentage using Ctrl + Shift + 5.

This creates the Purchase Cost Variance % for each supplier-part combination in the matrix.

5. Lock the References So the Formula Can Be Reused

Before filling the calculation across the matrix, adjust the cell references so they behave correctly when copied:

• lock the supplier row reference so it stays tied to the header row
• lock the part column reference so it stays tied to the part labels

The video explains this using absolute and relative references so the same formula can work across the full table.

Because of the table behavior shown in the video, the formula is copied across the full selected range instead of dragged cell by cell.

6. Add the Supplier Overall Column

After the matrix is complete, create an Overall column for each supplier.

To do that:

• reuse the same SUMIFS structure
• remove the part condition
• keep only the supplier condition

This gives one overall Purchase Cost Variance % by supplier, regardless of part.

Then copy that formula across the supplier totals section.

7. Add the Part Overall Row

Next, create an Overall row for each part.

To do that:

• reuse the same SUMIFS structure again
• remove the supplier condition
• keep only the part condition

This gives one overall Purchase Cost Variance % by part, regardless of supplier.

Then fill that result down the row.

8. Add the Grand Total Cell

For the final overall metric:

• use SUM on the full Purchase Cost Variance column
• divide it by SUM of the full Standard Cost column

This creates one total Purchase Cost Variance % for the entire dataset.

The video uses this as the top-level summary number for the whole purchasing operation.

9. Add a Threshold Cell for the Heat Map

Create a separate input cell to act as the threshold for highlighting problem areas.

In the example, the threshold starts at 7%, but the point is to make it editable so users can change it whenever they want.

Then format the cell so it can display more precision if needed, such as:

• 7.0%
• 5.5%
• 1.5%

This becomes the control cell for the heat map.

10. Apply Conditional Formatting to Build the Heat Map

To highlight high-variance areas:

• select the matrix and summary cells
• go to Conditional Formatting
• use Highlight Cells Rules
• choose Greater Than
• point the rule to the threshold value

Now any cell above the target percentage turns red.

As the threshold changes:

• lowering it highlights more cells
• raising it highlights fewer cells

This makes it easy to spot which suppliers or parts are creating the biggest purchasing issues.

11. Label the Threshold Cell Clearly

To make the control cell easier for other users to understand, the video adds a label directly inside the same cell.

Instead of converting it to plain text, it uses:

• Ctrl + 1
• Custom number formatting

This allows the cell to stay numeric while displaying a label such as:

Target: 5%

That way, the spreadsheet still works for conditional formatting, but the purpose of the cell is obvious to anyone reviewing the file.

12. Finalize the Matrix for Review

At the end of the process, you have a complete purchase cost variance dashboard that includes:

• a part-by-supplier matrix
• supplier overall results
• part overall results
• a grand total variance %
• a dynamic threshold cell
• a heat map that updates automatically

This layout makes it much easier to review purchasing discipline and quickly identify where actual costs are drifting above standard cost.