How to Analyze Carrier Profitability by Agent in Excel

How to Calculate Carrier Profitability by Producer and Carrier in Excel

This tutorial shows how to calculate and visualize Carrier Profitability by Producer (Agent) and by Carrier. You’ll learn how to build a pivot table that summarizes Gross Revenue per agent per carrier, and then use charts, slicers, and heat maps to instantly reveal which carriers and agents contribute most to your revenue.

1. Create a Pivot Table

1. Click anywhere inside your dataset.
   
2. Go to Insert → Pivot Table → From Table/Range.
   
3. Choose Existing Worksheet and set the location to A1.
   
4. In the PivotTable Fields panel:
   
   • Drag Agent Name to the Rows area.
     
   • Drag Carrier to the Columns area.
     
   • Drag Gross Revenue to the Values area.
     

You now have a summary table showing total gross revenue by agent and carrier.

2. Create a Pivot Chart

1. Click inside the pivot table.
   
2. Go to Insert → Pivot Chart and click OK.
   
3. Choose a Clustered Column Chart (not stacked) to show one column per carrier per agent.
   
4. Go to the Chart Design tab to adjust the color scheme or layout. Many prefer a dark background for readability.
   
5. Remove filter buttons from the chart by selecting it and going to:
   PivotChart Analyze → Field Buttons → Hide All.
   

3. Clean Up Chart Formatting

• To remove unnecessary decimals in the data table, highlight the pivot table and adjust number formatting.
  
• If you want decimals to remain in the table but not in the chart, double-click the chart axis → go to Axis Options → Number, and set decimals to 0.
  
• Add clear chart titles (for example, Gross Revenue by Agent and Carrier).
  

4. Add a Slicer for Product Line

1. Click anywhere inside the pivot table.
   
2. Go to Insert → Slicer.
   
3. Select Product Line.
   
4. Resize and position the slicer beside your chart.
   
5. To display multiple columns of buttons, click the slicer → Slicer Tab → Columns → 2 or 3 depending on fit.
   

Now, when you click a product line in the slicer, your chart and pivot table automatically update to show revenue for that product only.

5. Add Conditional Formatting (Heat Map)

1. Highlight the pivot table values (excluding the Grand Total column).
   
2. Go to Home → Conditional Formatting → Color Scales.
   
3. Choose a color gradient (e.g., green for high values, red for low).
   
4. For expense-style data, you can reverse the color order.
   
5. You can also create a custom gradient that matches your brand colors.
   

This instantly turns your pivot table into a heat map, visually showing which agents and carriers generate the highest revenue.

6. Identify Top Performers

• To see which carrier produces the most total revenue, look at the Grand Total column.
  
• To find your highest-performing agent, look at the Grand Total row.
  
• Apply conditional formatting separately to these totals if you want to highlight your top carrier and agent.
  

7. Optional Enhancements

• Add filters for Region or Policy Type for deeper insights.
  
• Convert pivot charts into dashboards by linking multiple slicers.
  
• Use Alt + H + A + C to center-align your pivot values for a cleaner layout.
  
• If you want to simplify comparisons, you can also create a secondary chart that shows average revenue per policy per carrier.
  

8. Interpret the Results

The resulting dashboard shows which carrier–agent combinations produce the most revenue and margin.
For example:

• Allstate might generate the highest overall revenue, but Travelers may perform better for certain agents.
  
• The slicer lets you isolate product lines like Auto or Homeowners to see detailed profitability.
  

This analysis helps you decide which carrier relationships to strengthen, maintain, or renegotiate.

9. Final Touches

• Rename your chart to Carrier Profitability by Agent and Carrier.
  
• Save the workbook as a template so you can refresh it with new data each month.
  
• If you want to share your report, copy the chart into PowerPoint or Power BI for distribution.
  

Result

You now have a professional Excel dashboard showing Gross Revenue by Carrier and Producer, complete with dynamic slicers and heat maps. It provides instant insight into which relationships drive profitability and which need optimization.