Inventory Carrying Cost Analysis by Month and Part

How to Build the Inventory Carrying Cost Heat Map

1. Confirm the total carrying cost column

• Identify the five cost columns in the dataset:
  • Capital cost
  • Storage cost
  • Insurance cost
  • Obsolescence cost
  • Handling cost
• Create the Total Carrying Cost field by summing those five columns:
  • Use =SUM( … ) across the five cost columns in the row.
• If the dataset already includes Total Carrying Cost, skip adding the column and use the existing one.

2. Create a unique list of months

• Go to the Month/Date column in the dataset.
• Generate a unique month list using UNIQUE() so you only have one entry per month.
• Use Ctrl + Shift + Down Arrow to quickly select the full range.
• Copy, then paste values with Ctrl + Shift + V.
• Label the column as Month and format it as a date.

3. Create a unique list of part categories, then sort and transpose

• Use UNIQUE() on the Part Category column, selecting the table values, not the entire column header letter.
• Sort the categories A to Z using SORT() so the row is easy to scan.
• Transpose the sorted list so categories run across the top:
  • Use TRANSPOSE() to spill the values horizontally.
• Copy and paste values with Ctrl + Shift + V to remove formulas.

4. Build the carrying cost matrix with SUMIFS

• In the first matrix cell (top-left intersection of Month and Part Category), use SUMIFS():
  • Sum range: Total Carrying Cost column
  • Criteria 1: Month equals the month in the left-side row
  • Criteria 2: Part Category equals the header at the top of the column
• Confirm the first result returns the total carrying cost for that category in that month.

5. Lock references so the formula fills correctly

• Fix the month reference so:
  • The month column stays locked when dragging left to right
  • The month row changes when dragging down
• Fix the category reference so:
  • The category row stays locked when dragging down
  • The category column changes when dragging left to right
• Use dollar signs to control relative referencing:
  • Lock the column for the month reference
  • Lock the row for the category reference

6. Copy and paste the corrected formula across the full grid

• Instead of dragging (which can cause Excel to shift table references incorrectly), do:
  • Copy the corrected cell formula
  • Paste across the full matrix area
• Then fill down to populate all months and categories.

7. Add totals for rows, columns, and the grand total

• Add an Overall column at the right:
  • Sum across each month’s row to get total carrying cost per month.
• Add an Overall row at the bottom:
  • Sum down each category column to get total carrying cost per category.
• Add the final grand total:
  • Sum the Overall column (or sum the Overall row). Both should match.

8. Create the heat map using conditional formatting

• Highlight the values area of the matrix.
• Go to Home → Conditional Formatting → Color Scales.
• Choose a scale where red represents high cost.

9. Apply conditional formatting at the column level for better insights

• If formatting the full matrix at once is not useful, switch to column-level formatting:
  • Highlight a single month column in the matrix
  • Apply Color Scales so red is high within that column
• This makes month-to-month intensity easier to interpret.

10. Use Format Painter to apply the same heat map rules quickly

• After formatting one column correctly:
  • Double-click Format Painter
  • Click the first value cell in each other column to apply the same formatting pattern
• Repeat the same process for the totals row or totals column if needed.

11. Repeat conditional formatting for the category totals

• Highlight the category totals row (or category totals column).
• Apply Color Scales with red as high.
• This reveals which part categories are most expensive to carry.

12. Final check

• Verify the matrix totals reconcile:
  • Monthly totals, category totals, and grand total should align.
• Your finished view shows:
  • Carrying cost by month and part category
  • Total carrying cost by month
  • Total carrying cost by part category
  • Annual total carrying cost