How to Calculate Break-Even Revenue by Month
(and See Which Months Beat It)

Build the Break Even Revenue Model in Excel

1. Create the Month column from the Date

• Identify the column that contains the job date.
  
• In a new column, use the TEXT function to convert the date into the month name:
  
  • Use =TEXT([date_cell],”mmmm”) to return the full month name.
    
  • If you prefer abbreviations, use =TEXT([date_cell],”mmm”).
    
• Fill the formula down for all rows so every record has a month label.
  

2. Build a Month list for the summary table

• In your summary area, type January and drag down through December.
  
• Center the month list if needed so the table is easy to read.
  

3. Sum monthly Revenue using SUMIFS

• In the Revenue column of the summary table, use SUMIFS to sum revenue by month:
  
  • Sum range: Revenue column
    
  • Criteria range: Month column you created
    
  • Criteria: The month name in your summary table (January, February, etc.)
    
• Copy the formula down for all months.
  
• Format revenue as currency using Ctrl + Shift + 4.
  

4. Sum monthly Variable Cost using the same SUMIFS pattern

• Copy the Revenue SUMIFS formula.
  
• Change only the sum range from Revenue to Variable Cost.
  
• Copy down for all months.
  
• Format as currency with Ctrl + Shift + 4.
  

5. Calculate Contribution Margin dollars

• In the Contribution Margin column, subtract Variable Cost from Revenue:
  
  • =Revenue – VariableCost
    
• Copy down for all months.
  
• Format as currency.
  

6. Sum monthly Fixed Cost

• Use the same SUMIFS approach again:
  
  • Sum range: Fixed Cost column
    
  • Criteria range: Month column
    
  • Criteria: Month in the summary table
    
• Copy down for all months.
  
• Format as currency.
  

7. Calculate Contribution Margin percent

• Divide Contribution Margin dollars by Revenue:
  
  • =ContributionMargin / Revenue
    
• Copy down.
  
• Format as percent using Ctrl + Shift + 5.
  

8. Calculate Break Even Revenue

• Break Even Revenue is Fixed Cost divided by Contribution Margin percent:
  
  • =FixedCost / ContributionMarginPercent
    
• Copy down for all months.
  
• Format as currency.
  

9. Add conditional formatting color scales

• Apply a green-high color scale to Revenue:
  
  • Home → Conditional Formatting → Color Scales → Green high
    
• Apply color scales to other columns as desired:
  
  • Variable cost can be red-high if you want, but interpret it carefully (high cost can happen in high revenue months).
    
• Use Format Painter to quickly apply the same scale style from one column to another where “higher is better.”
  

10. Create the combo chart for Actual Revenue vs Break Even Revenue

• Highlight:
  
  • Month column
    
  • Revenue column
    
  • Break Even Revenue column
    
• Insert → Recommended Charts → All Charts → Combo
  
• Keep both series on the same axis (no secondary axis needed).
  
• Optional: add data labels, but note it can get visually noisy.
  

11. Create the Contribution Margin trend chart

• Highlight Month.
  
• Hold Ctrl and highlight Contribution Margin percent.
  
• Insert → Line Chart.
  
• Optional: add values if you want, but keep it readable.
  

12. Add a Sparkline trend row

• Add a row label like Trend beneath the table.
  
• Click the first trend cell in that row.
  
• Insert → Sparklines → Line.
  
• Select the data range (for example, the row of monthly values you want to trend).
  
• Click OK.
  
• Drag across to fill other sparkline cells if needed.
  
• Optional: enable High Point and Low Point markers in the Sparkline tools.