How to Identify Profit Loss with Gross Margin Slippage %

How to Build a Gross Margin Slippage % Heat Map in Excel

1. Calculate the Planned and Forecast Gross Profit Columns

Start by preparing the two supporting columns used throughout the analysis:

• Create a column for Planned Gross Profit
• Create a column for Forecast Gross Profit
• Use simple subtraction for each one based on the revenue and cost fields already in the dataset
• Once those values are in place, the rest of the analysis can be built from them

These two columns are the foundation for the slippage calculation.

2. Create a Unique List of Project Managers

To build the matrix rows:

• Add a header for PM
• Use the UNIQUE function on the PM column in the dataset
• If your data is stored in an Excel table, reference the table column directly
• Sort the resulting list alphabetically
• Copy the list and paste it as values using Ctrl + Shift + V

This gives you a clean vertical list of project managers.

3. Create a Unique List of Job Types Across the Top

To build the matrix columns:

• Add a header for Job Type
• Use the UNIQUE function on the Job Type column
• Wrap it with SORT to organize the list alphabetically
• Use TRANSPOSE so the list spills horizontally across the row
• Copy the result and paste it as values using Ctrl + Shift + V

Now you have the column structure needed for the heat map.

4. Build the Slippage Calculation for One PM and One Job Type

Start with a single intersection in the matrix, such as one PM and one Job Type.

Use SUMIFS to calculate:

• Total Planned Gross Profit for that PM and Job Type
• Total Planned Revenue for that PM and Job Type
• Total Forecast Gross Profit for that PM and Job Type
• Total Forecast Revenue for that PM and Job Type

Then structure the result so the planned margin and forecast margin can be compared inside one cell.

The key point is that the formula repeats the same logic several times. You are mostly changing which field is being summed.

5. Format the Result as a Percentage

Once the first calculation is working:

• Format the result as a percentage using Ctrl + Shift + 5
• Add one decimal place if needed

This makes the slippage values easier to read before you copy the formula across the matrix.

6. Fix the Cell References Before Copying

Before filling the formula across the grid, lock the references correctly:

• Lock the row for the Job Type reference so it stays on the same header when dragging down
• Lock the column for the PM reference so it stays on the same name when dragging across
• Leave the other direction flexible so the matrix updates correctly as it fills

This step is important because the matrix needs to respond differently when dragged across columns versus down rows.

7. Fill the Matrix Across and Down

Because table references do not always behave well when dragged horizontally:

• Drag the formula down where possible
• For moving across columns, copy and paste instead of dragging if needed
• Check that each cell is still pointing to the correct job type and PM combination

Once complete, the matrix will show slippage percentages for every PM by Job Type combination.

8. Add an Overall Total by Job Type

To create an overall result for each Job Type:

• Copy the existing formula from the matrix
• Remove the PM criteria from each SUMIFS section
• Keep only the Job Type filter
• Paste the formula into the total row or summary row
• Format as a percentage with one decimal place

This gives you a rolled-up slippage view by Job Type only.

9. Add an Overall Total by PM

To create an overall result for each PM:

• Copy the same base formula again
• Remove the Job Type criteria from each SUMIFS section
• Keep only the PM filter
• Paste it into the summary column
• Format as a percentage with one decimal place

This gives you a rolled-up slippage view by PM only.

10. Add Conditional Formatting for the Heat Map

Once the matrix is complete, turn it into a visual alert system:

• Highlight the matrix cells
• Hold Ctrl if you also want to include summary cells
• Go to Home > Conditional Formatting
• Use a rule based on values greater than a target cell
• Choose a red fill or another alert color for cells above the threshold

This makes it easy to spot the highest slippage areas immediately.

11. Create a Dynamic Target Cell

To make the heat map adjustable:

• Add a target cell beside the matrix
• Enter a percentage threshold such as 1.5% or 2.5%
• Use that target cell in the conditional formatting rule
• Change the threshold anytime to make the highlighting more or less strict

This lets you control the heat map sensitivity without rebuilding the rule.

12. Format the Target Cell So It Still Works Numerically

If you want the target cell to display text like “Target: 2.5%” without breaking the formatting rule:

• Keep the cell as a number
• Open Format Cells with Ctrl + 1
• Go to Custom
• Add text inside the custom number format so the label displays while the underlying value remains numeric

This keeps the conditional formatting dynamic while making the target easier to understand for other users.