Gross Margin Breakdown by Month and Job Type

How to Analyze Gross Profit Margin by Job Type

This tutorial shows how to take a raw HVAC job dataset and build a margin reporting view that breaks gross profit margin down by month and by job type. You will create a monthly summary table using SUMIFS, add a heat map to spot trends in revenue and cost drivers, and build charts that highlight gross profit margin performance over time and across job types.

1. Create the Month List

• In your Month column, type January.
  
• Drag the fill handle down to auto-fill through December.
  

2. Set Up the Summary Table Headers

• In the header row, include:
  
  • Invoice amount or revenue field (example: After Discounted Price or Full Invoice Amount)
    
  • Individual cost columns such as:
    
    • Parts and material cost
      
    • Labor cost
      
    • Subcontract and permits
      
    • Truck cost
      
    • Warranty
      
  • Total COGS
    
  • Gross profit margin percent
    

Tip: Breaking out individual costs lets you spot which cost category is driving margin changes month to month.

3. Use SUMIFS to Calculate Monthly Totals

• Use SUMIFS to total the invoice amount for each month.
  
• The structure is:
  
  • Sum range: the metric you are summing (invoice or a cost column)
    
  • Criteria range: month column in the dataset
    
  • Criteria: the month label from your month list
    

Key detail: Lock references correctly so the formula copies cleanly across columns and down rows.

• Lock the dataset month column so it does not shift when copying across
  
• Lock the month label column so January stays January when copying across, but changes to February, March, etc. when copying down
  

4. Copy the Formula Across All Cost Columns

• Once the invoice SUMIFS formula works, copy it across to each cost header.
  
• Because the cost fields sit in adjacent dataset columns, Excel will naturally shift the sum range from invoice to material to labor, etc.
  
• Make sure the criteria range for month stays fixed.
  

5. Calculate Total COGS

• Total COGS can be calculated in two ways:
  
  • Sum the individual cost columns in your summary table
    
  • Or SUMIFS the dataset’s total cost column if available
    

Both approaches work. Summing the cost columns makes it easier to validate category totals.

6. Calculate Gross Profit Margin Percent

• Use this formula:
  
  • (Invoice Amount minus Total COGS) divided by Invoice Amount
    
• Format as percent.
  
• Copy down through all months.
  

7. Build the Heat Map

Apply conditional formatting column by column because each metric has different meaning.

• Invoice amount
  
  • Higher is better
    
  • Use a green-high color scale
    
• Cost columns
  
  • Lower is better
    
  • Use a red-high color scale
    
• Margin percent
  
  • Higher is better
    
  • Use a green-high color scale
    

Speed tip: Use Format Painter to apply the same conditional formatting to multiple cost columns quickly.

8. Create the Gross Margin by Month Line Chart

• Select the Month column and the Margin Percent column.
  
• Insert a Line Chart.
  
• Optional improvements:
  
  • Format the y-axis to show percent cleanly
    
  • Remove decimals on axis labels
    
  • Turn on smooth line if preferred
    

This chart helps you see seasonality and month-to-month variation in margin.

9. Create the Job Type Summary

Now repeat the same process, but group by Job Type instead of Month.

• Use UNIQUE to generate the job types (example: Install, Maintenance, Service).
  
• Paste values to remove formulas.
  
• Sort A to Z if desired.
  

10. Use SUMIFS to Calculate Totals by Job Type

• Use SUMIFS for invoice amount by job type.
  
• Copy across for each cost category.
  
• Calculate total COGS and margin percent the same way as the monthly table.
  

11. Create the Gross Margin by Job Type Column Chart

• Select Job Type and Margin Percent.
  
• Insert a Column Chart.
  
• Optional: Adjust the chart y-axis minimum to better show variation between job types.
  

This view typically reveals that installs have different margins than service or maintenance, helping you prioritize work types strategically.

12. Extend the Model with Multiple Criteria

Once the model works, you can add additional criteria to SUMIFS:

• Month and job type together
  
• Customer type
  
• System type
  
• Discount analysis
  

SUMIFS supports many criteria ranges, so you can slice profitability in several ways without rebuilding your model.