Stop the Profit Leak: Track Shrinkage % by
Month and Location

Shrinkage % Heat Map Build in Excel

1. Start with the inventory reconciliation table

• Confirm your sheet includes the key inventory fields needed for reconciliation, especially quantities and value-related columns.
• Identify which value column will be used as the denominator for the calculation later.

2. Add three helper columns to the dataset

• Create an End of Month column using EOMONTH based on the inventory count date.
• Create a Shrinkage Quantity column using basic arithmetic logic from the reconciliation fields.
• Create a Shrinkage Value column by multiplying shrinkage quantity by unit cost.
  
  
• Ensure shrinkage value is treated as a positive number using either ABS or by flipping the sign.

3. Build the month list for the heat map rows

• Use UNIQUE to generate a distinct list of months from the End of Month column.
• Format those results as dates so they display as month-ending dates.
• Copy and paste values to lock the list in place.

4. Build the location list for the heat map columns

• Use UNIQUE to generate a distinct list of locations.
• Sort the location list alphabetically for easier scanning.
• Move the list across the top of the heat map using TRANSPOSE.
• Copy and paste values to lock the header row.

5. Calculate shrinkage value totals by month and location

• Use SUMIFS to total Shrinkage Value using two criteria:
  • Month (End of Month)
  • Location
• This returns the shrinkage value bucketed into each month-location intersection.

6. Convert totals into Shrinkage %

• Keep the SUMIFS result as the numerator.
• Divide by the denominator using the same structure, but referencing the average inventory value field instead.
• Confirm the resulting percentages are small and formatted as percentages.

7. Lock references so the matrix copies correctly

• Apply absolute and mixed references so:
  • Month references stay fixed when copying across columns
  • Location references stay fixed when copying down rows
• Because structured table references can behave differently when dragging, use copy-paste to fill the matrix when needed.

8. Add totals by month, totals by location, and the overall KPI

• Add an Overall by Month column by reusing the same logic but removing the location criterion.
• Add an Overall by Location row by reusing the same logic but removing the month criterion.
• Add the single Overall value by using SUM on the full table values:
  • Sum Shrinkage Value divided by Sum Average Inventory Value

9. Apply conditional formatting to create the heat map

• Use Conditional Formatting → Color Scales to visualize high vs low shrinkage.
• Apply formatting by month rows or by the full heat map depending on how you want to compare.
• Use Format Painter (double-click) to quickly apply the same formatting rules across multiple rows or sections.

10. Optional threshold highlighting for exceptions

• Add a single “threshold” cell and use Conditional Formatting → Highlight Cells Rules to flag values above that threshold.
• This makes it easy to spot problem months or locations without scanning the full heat map.