Analyze Total Gross Revenue and Margin Trends in Excel

Total Gross Revenue, Margin, and Average Margin in Excel

This tutorial shows how to model total gross revenue, margin by month, and average margin, then visualize them in a combo chart so you can quickly see when your margin is above or below your target.

1. Set up the month column

• In your summary sheet, type the first month (for example, “January”) in the first row of the Month column.
• Use the fill handle (small square in the bottom-right corner of the cell) and drag down until you reach December.
• You now have one row per month where you will calculate written premium, gross revenue, and margin.
  

2. Calculate written premium by month with SUMIFS

You want total written premium per month from your data sheet.

1. In the “Written Premium” column on your summary sheet, enter a SUMIFS formula that:
   
   • Sums the Written Premium column in your data (for example, column N).
     
   • Uses the Month column in the data as the criteria range.
     
   • Uses the month in the current row (for example, cell B3) as the criteria.
     
2. Lock the ranges with absolute references so you can copy the formula down (for example, use F4 or Ctrl + Shift + 4 depending on your keyboard setup).
   
3. Double-click the fill handle to copy the formula down through December.
   
   

3. Calculate gross revenue by month with SUMIFS

Now you repeat the same logic for gross revenue.

1. In the “Gross Revenue” column, use another SUMIFS formula that:
   
   • Sums the Gross Revenue column from the data (for example, column S).
     
   • Uses the data’s Month column as the criteria range.
     
   • Uses the month in the current row on your summary sheet as the criteria.
     
2. Lock the ranges with absolute references, then double-click the fill handle to copy down.
   
3. If you want consistent formatting, you can use the Format Painter to copy the numeric format from the Written Premium column to the Gross Revenue column.
   
   

4. Calculate monthly margin

Margin shows how much of the written premium you are actually keeping after commissions or other costs.

1. In the “Margin” column, enter a simple formula:
   
   • Margin = Gross Revenue ÷ Written Premium.
     
2. Copy the formula down for all months.
   
3. Format the Margin column as a percentage (Ctrl + Shift + 5) and adjust decimal places as needed.
   

5. Calculate the average margin

You want a single average margin line that stays constant across all months so you can compare each month to that benchmark.

1. Pick a cell in the Margin column (for example, the first margin row).
   
2. Build the average as a ratio of sums, not as an average of percentages:
   
   • Average margin = (Sum of all monthly Gross Revenue) ÷ (Sum of all monthly Written Premium).
     
3. Use absolute references (F4) on the ranges so you can copy the formula down the entire Average Margin column.
   
4. Format the Average Margin column as a percentage (Ctrl + Shift + 5).
   
5. The values in the Average Margin column should all be identical, forming a horizontal “benchmark” line in the chart.
   

6. Build the combo chart

Now you will create a chart that shows:

• Written Premium by month
  
• Gross Revenue by month
  
• Margin by month (as a line on a secondary axis)
  
• Average Margin (as a line on a secondary axis)
  

1. Select the Month, Written Premium, Gross Revenue, Margin, and Average Margin columns.
   
2. Go to Insert → Recommended Charts, but instead of the default, select a Combo chart.
   
3. Set Written Premium and Gross Revenue as columns on the primary axis.
   
4. Set Margin and Average Margin as lines on the secondary axis.
   
   • This is important because margin values are between 0 and 1 (0 to 100 percent), while premiums and revenue are large dollar amounts.
     
5. Now you will see:
   
   • Orange line (for example) for monthly margin.
     
   • Green or gray line for the average margin.
     
   • If the monthly margin line is above the average line for a given month, that month is performing above average; if it is below, it is underperforming.
     

7. Format the average margin line

To make the average margin easier to see visually:

1. Click carefully on the average margin line until all its data markers are selected.
   
2. Open the formatting panel (Format Data Series).
   
3. Change the line to a solid color that stands out but is not too bold (the example uses a gray color).
   
4. Change the dash type to a dashed line so it looks like a benchmark.
   

Now the chart clearly shows:

• Columns for written premium and gross revenue.
• A solid line for monthly margin.
• A dashed line for average margin that acts as a visual target.
  

You can quickly tell whether each month is above or below your target margin and use this to manage your agency’s predictability and profitability.