Late Delivery % of Spend (Weighted): Build a Supplier On Time Delivery Heat Map in Excel

Late Delivery % of Spend (Weighted)

1. Confirm the dataset columns you need

• Verify your PO dataset includes:
  • Order date
  • Promised delivery date
  • Receipt date
  • Supplier
  • PO cost or extended cost
  • Canceled flag
• Filter out canceled POs so they do not impact the metric.

2. Add a month-end date field for grouping

• Create a helper column that converts each order date into an “end of month” bucket.
• Use the EOMONTH function to generate the month-end date.
• Fill the formula down for all rows so every PO is assigned to a month bucket.

3. Create an on-time flag

• Add a helper column that labels each PO as on-time or late based on receipt vs promised date.
• Use the IF function to return:
  • “No” when the receipt date is later than the promised delivery date
  • “Yes” otherwise
• Fill the logic down the entire dataset.

4. Convert the dataset into an Excel Table

• Format the data as a Table so you can reference fields by name.
• Confirm the Table name is set (this makes structured references easier and more consistent).
• From this point, you build the heat map without repeatedly selecting the raw data ranges.

5. Generate the heat map axes

• Create a vertical list of unique month-end dates:
  • Use the UNIQUE function on the Table’s month-end date field
  • Format the results as dates
  • Copy and paste as values with Ctrl + Shift + V
• Create a horizontal list of unique suppliers:
  • Use the UNIQUE function on the Table’s supplier field
  • Sort alphabetically if desired
  • Use TRANSPOSE to place suppliers across the top row
  • Copy and paste as values with Ctrl + Shift + V

6. Calculate late dollars by supplier and month

• In the heat map grid, calculate the dollar value of late deliveries:
  • Use SUMIFS on the PO cost field
  • Filter by:
    • On-time flag equals “No”
    • Supplier equals the supplier header for that column
    • Month-end date equals the month value for that row
• Format as currency if you want a companion “Dollars at Risk” view.

7. Convert late dollars into Late Delivery % of Spend (Weighted)

• Use the same SUMIFS setup to calculate total spend for the same supplier and month, but without filtering by the on-time flag.
• Divide late dollars by total spend to get the weighted percent.
• Format as percent using Ctrl + Shift + 5.

8. Lock references so the grid copies correctly

• Before filling the grid, adjust references so:
  • Supplier criteria stays tied to the column header when copying across
  • Date criteria stays tied to the row label when copying down
• Use absolute and mixed references so the heat map fills properly.
• Copy and paste across the grid instead of dragging if the table structure causes shifting.

9. Add supplier totals and month totals

• Create an “Overall by Supplier” section:
  • Reuse the same calculation but remove the date filter so results roll up across all months.
• Create an “Overall by Month” section:
  • Reuse the same calculation but remove the supplier filter so results roll up across all suppliers.
• Format all rollups as percentages for consistency.

10. Calculate the company-wide annual late delivery percent

• Build a single overall value:
  • Sum late PO cost across the entire year using SUMIFS filtered to late deliveries
  • Divide by total PO cost across the entire year using SUMIFS without the late filter
• Format as a percentage and set your preferred decimal precision.

11. Add a dynamic threshold that flags high risk

• Create a “threshold” input cell (example: 15%).
• Apply Conditional Formatting to the heat map:
  • Highlight values greater than the threshold cell
  • This makes the heat map instantly actionable for the team.
• Rename the threshold visually without converting it to text:
  • Use Format Cells with Custom number formatting so the cell displays a label while staying numeric.

12. Add a “Dollars at Risk” companion view

• Duplicate the heat map calculations but keep the output as dollar sums (late PO cost only).
• Add totals using the SUM function:
  • Sum rows and columns
  • Confirm totals reconcile (percent contribution totals should add to 100% when designed that way.

13. Final cleanup for presentation

• Apply consistent number formats:
  • Percent for the KPI grid
  • Currency for the dollars view
• Align and space the table for readability
• Ensure sorting and labels are clear for teammates reviewing the file