Gross Margin per Project %: Track Profitability in Excel

How to Build a Gross Margin per Project % Dashboard in Excel

1. Prepare the project data

• Start with a project dataset that includes fields such as:
  • Start Date
  • Project Manager
  • System Type
  • Job Type
  • Total Revenue
  • Planned Cost
  • Planned Profit
  • Planned Gross Margin %
  • Actual material, labor, other, and total costs
• Keep the dataset in an Excel table so it is easier to reference columns by name and build formulas faster.

2. Create the month field from the project start date

• Add a new column that converts each project Start Date into the end of its month.
• Use the EOMONTH function so every project is assigned to a monthly reporting period.
• This month field becomes the basis for the monthly chart and heat map.

3. Add the actual profit and actual margin columns

• Create a column for Actual Gross Profit using the revenue and actual total cost fields.
• Create another column for Actual Gross Margin % by comparing actual gross profit to total revenue.
• Format the margin column as a percentage so it is ready for reporting.

4. Build a unique monthly summary list

• Create a separate reporting area and generate a unique list of the months from the month field you just created.
• Use the UNIQUE function to pull each month once.
• This gives you the row labels for the monthly performance summary.

5. Summarize actual, planned, and revenue values by month

• Add columns for:
  • Actual Gross Margin
  • Planned Gross Margin
  • Revenue
  • Target
• Use SUMIF to total the monthly actual gross profit.
• Copy that logic and switch the referenced field to summarize planned gross profit.
• Repeat again for revenue.
• Convert the actual and planned values into percentages by comparing each monthly total to monthly revenue.
• Format margin values as percentages and revenue as currency.

6. Add a dynamic target line

• Create a target column with a fixed target percentage such as 30%.
• Set the target as a linked input so changing the target automatically updates the report and the chart.
• This becomes the reference line used to judge whether monthly performance is meeting expectations.

7. Create the combo chart

• Highlight the monthly fields for:
  • Actual Gross Margin %
  • Planned Gross Margin %
  • Revenue
  • Target
• Insert a Combo Chart from the recommended chart options.
• Set:
  • Revenue as columns
  • Planned Gross Margin and Target as lines
• Adjust the axis scale if needed so the variation in margin is easier to read.
• Format the target line so it is thinner and easier to distinguish visually.

8. Build the PM heat map layout

• Copy the unique list of months into a new reporting section.
• Create a unique list of Project Managers using the UNIQUE function.
• Sort the PM list alphabetically.
• Use Transpose so the PM names run across the top row.
• This creates the structure for a month-by-PM matrix.

9. Calculate gross margin by month and PM

• In the heat map, calculate actual gross margin % for each PM and each month.
• Use SUMIF logic to total actual gross profit for the selected PM and month.
• Then divide by the corresponding revenue total for that same PM and month.
• Lock the row and column references correctly so the formula copies across the full matrix without shifting the wrong labels.
• Copy and paste the formula across the table.

10. Highlight underperforming cells with conditional formatting

• Select the completed PM matrix.
• Apply Conditional Formatting to highlight cells below a chosen threshold, such as 33%.
• This instantly shows where margin performance is weakest by manager and by month.
• Because the threshold can be tied to a cell, the highlights update dynamically when the target changes.

11. Label the target without breaking the calculation

• If you want the target cell to display text like “Target,” do not type text directly into the numeric cell.
• Open Format Cells with Ctrl + 1.
• Use a Custom Number Format so the cell still behaves like a percentage while displaying the label style you want.
• This keeps the chart and conditional formatting working correctly.

12. Use the report to investigate margin leakage

• Review the combo chart to compare:
  • Actual vs planned margin
  • Revenue trends
  • Whether monthly margin stays above target
• Review the heat map to identify:
  • Which PMs had low-margin months
  • Which months need a deeper review
  • Where quoting issues, discounts, overruns, or poor planning may be affecting profitability
• Once the structure is built, you can repeat the same method for other dimensions such as:
  • Job Type
  • Residential vs Commercial
  • System Type
  • Other project segments