Tracking Slow-Moving Inventory % in Excel

How to Calculate Slow-Moving Inventory % by Month and Part Category

1. Confirm your required columns are in the dataset

• Month-end inventory date (or a date column you can convert to month-end)
• Part category
• Cost and inventory quantities or values needed to compute on-hand value
• Last time the part was sold or issued

2. Standardize the month-end date

• If your date field is not already month-end, convert it using EOMONTH
• Format the output as a date so it is easy to read and group by month

3. Create a slow-moving flag

• Decide your slow-moving threshold (the video uses 90 days)
• Use IF to label each row as slow-moving or not based on the days since the last sold or issued date
• Fill the logic down the entire dataset

4. Create a slow-moving value helper column

• Use IF to return the on-hand value only when the slow-moving flag is “Yes”
• If not slow-moving, return a blank or zero based on your reporting preference
• This helper column becomes the numerator in the final ratio

5. Build a unique list of month-end dates for the heat map rows

• Use UNIQUE to return each month-end date once
• Copy and paste values to remove formulas if you want a static reporting table
• Make sure the dates are formatted consistently

6. Build a unique list of part categories for the heat map columns

• Use UNIQUE on the part category field
• Use SORT to order categories A to Z
• Use TRANSPOSE to place categories across the top row of the matrix
• Copy and paste values to keep the layout clean and stable

7. Calculate the slow-moving percentage for each month and category cell

• Use SUMIFS to aggregate the slow-moving value for a given month and part category
• Use SUMIFS again to aggregate the total on-hand value for the same month and category
• Divide the two results to get the Slow-Moving Inventory % for that cell
• Format the results as a percentage

8. Fix your references so you can copy across and down

• Lock the month reference so it stays aligned when you drag formulas across columns
• Lock the category reference so it stays aligned when you drag formulas down rows
• Use absolute referencing with dollar signs or cycle lock options with F4
• If the structured table references shift unexpectedly when dragging, copy and paste the correct formula into the full range

9. Add “Overall” totals for better rollups

• Add an Overall column that calculates slow-moving percentage by part category across all months
  • Do this by removing the month criteria from your SUMIFS logic
• Add an Overall row that calculates slow-moving percentage by month across all categories
  • Do this by removing the part category criteria from your SUMIFS logic
• Add a single overall KPI for all inventory
  • Use SUM across the table columns for slow-moving value and total on-hand value, then compute the ratio

10. Add a target threshold and highlight problem areas

• Pick a target such as 25%
• Apply Conditional Formatting to highlight cells greater than the target
• This instantly surfaces which part categories and months have the highest slow-moving inventory concentration

11. Format the target label without breaking the math

• Enter the target as a true numeric value
• Use Ctrl + 1 to open formatting
• Apply a Custom number format to display a label like “Target: 25%” while keeping it numeric