Order Cycle Time (Days): Track Supplier Lead Time
and Prevent Stockouts

Order Cycle Time (Days)

1. Confirm the required columns in the dataset

• Review the purchase order timeline fields (request date, approvals, PO created, supplier acknowledged, shipped, received).
• Identify the two dates you will use to calculate cycle time:
  • Request date
  • Received date
• Confirm you also have a Supplier field so results can be grouped by supplier.

2. Add two calculated columns to your table

• Create a column for Order Cycle Time (Days) using date subtraction (received vs requested).
• Create a column for End of Month based on the request date using the EOMONTH function (with zero offset so it returns the end of the same month).

3. Convert your dataset into an Excel Table for cleaner references

• Format the dataset as a Table so structured references are available.
• Use table column selection in functions so you do not have to reference raw sheet ranges.
• Confirm you can see all column names when you type a bracket in a structured reference.

4. Build the month list and supplier list for the analysis grid

• Generate a unique list of month values from the End of Month column using UNIQUE.
• Generate a unique list of suppliers using UNIQUE.
• Wrap the supplier list with SORT to keep it alphabetical.
• Place suppliers across the top of the grid using TRANSPOSE.
• Paste as values where needed using Ctrl + Shift + V so the layout stays stable.

5. Populate the grid using median cycle time by month and supplier

• Use FILTER to return only the cycle-time values that match:
  • The month for the row
  • The supplier for the column
• Feed that filtered list into MEDIAN to return the median cycle time for that cell.
• Correct the FILTER logic by placing criteria in the proper grouping so it returns results instead of an error.

6. Lock references so the grid can be copied correctly

• Apply absolute and mixed references so:
  • Month references stay fixed to the correct row when copying across
  • Supplier references stay fixed to the correct column when copying down
• Avoid dragging if it causes the table references to shift to the wrong column.
• Copy and paste the formula across and down after locking references properly.

7. Add supplier-level and month-level rollups

• Create an overall median by supplier (across all months) by reusing the same approach and removing the month condition.
• Create an overall median by month (across all suppliers) by removing the supplier condition.
• Fill down and across to complete the summary edges of the heat map.

8. Create a dynamic heat map threshold

• Enter a threshold value (example: 25 days) in a single cell.
• Select the grid and apply Conditional Formatting to highlight cells greater than the threshold.
• Ensure the rule references the threshold cell so changing the threshold updates the heat map automatically.

9. Label the threshold cell without breaking the number

• Keep the threshold as a numeric value.
• Use Format Cells (Ctrl + 1) and a Custom number format to display a label like “Target” while preserving the underlying numeric value.

10. Add two quick insight checks

• Calculate the overall median cycle time for the entire dataset using MEDIAN on the cycle-time column.
• Calculate what percentage of orders exceed the threshold:
  • Use COUNTIF to count orders above the threshold
  • Divide by total row count
  • Show the result as a percentage
• Test it by changing the threshold value and confirming the percentage updates.