How to Track Internal Parts Shortfill Rate % in Excel
(Truck Replenishment)

Internal Parts Shortfill Rate % (Truck Replenishment)

1. Confirm the Required Columns in Your Dataset

Make sure your dataset includes:

• Request Date
• Parts Category
• Requested Quantity
• Filled Quantity

Once your data is structured correctly, the rest of the model builds from these four fields.

2. Create an End-of-Month Helper Column

• Add a helper column next to Request Date.
• Use the EOMONTH function to convert each request date into a month bucket.
• Format the helper column as a date.

This groups all requests into clean monthly reporting periods.

3. Generate a Unique List of Months

• Use the UNIQUE function on the End-of-Month column.
• Copy and paste as values.
• Format as date and center-align if desired.

This becomes the vertical axis of your heat map.

4. Generate a Sorted Horizontal List of Parts Categories

• Use UNIQUE on the Parts Category column.
• Wrap it with SORT to alphabetize the list.
• Wrap it with TRANSPOSE so categories run across columns instead of down rows.
• Copy and paste as values.

Now your matrix layout is structured:
Months down the left, Parts Categories across the top.

5. Calculate the Fill Rate by Month and Part

• Use SUMIFS to total Filled Quantity for each Month and Parts Category.
• Use SUMIFS again to total Requested Quantity for the same Month and Parts Category.
• Divide the totals to calculate the fill rate.

This shows how much demand was fulfilled for each combination.

6. Convert Fill Rate to Shortfill Rate

• Subtract the fill rate from 1.
• Format the result as a percentage.

This produces the Internal Parts Shortfill Rate % for each month and part category.

7. Lock Cell References for Proper Copying

• Apply absolute and relative references correctly.
• Lock the Month reference where needed.
• Lock the Parts Category reference where needed.
• Copy and paste the formula across the matrix instead of dragging.

This ensures calculations stay aligned as you expand the grid.

8. Add Overall Columns and Rows

Create three rollups using the same logic:

Overall by Part

• Use SUMIFS and remove the Month criteria.
• This shows shortfill performance by category across all months.

Overall by Month

• Use SUMIFS and remove the Parts Category criteria.
• This shows shortfill performance by month across all parts.

Overall for Entire Dataset

• Use the SUM function to total Filled Quantity.
• Use SUM again to total Requested Quantity.
• Subtract the fill rate from 1.

Format all summary cells as percentages.

9. Apply Percentage Formatting

• Format the full matrix and summary cells as percentages.
• Adjust decimal precision as needed.
• Keep formatting consistent across the entire heat map.

10. Create a Dynamic Target Cell

• Add a target percentage cell (for example, 6.0%).
• Select the full heat map range.
• Use Conditional Formatting with a “Greater Than” rule.
• Reference the target cell.

Any value above the target will automatically highlight.

11. Display the Target Label Without Breaking the Rule

• Open Format Cells on the target cell.
• Choose Custom Number Format.
• Add the word “Target” in quotation marks within the format.

This allows the cell to display something like “Target: 6.0%” while still functioning as a numeric value for conditional formatting.