How to Analyze Average Gross Revenue per Policy in Excel

Discover how to analyze your agency’s average gross revenue per policy type to understand which lines of business bring in the most revenue. You’ll learn how to organize data by product line, visualize results, and build an insightful chart for your performance dashboard.

Download the Excel file used in this tutorial:

Step By Step Instructions

How to Calculate Average Gross Revenue per Policy in Excel

1. Create a Unique List of Product Lines

Start by removing duplicates from the Product Line column:

Use the UNIQUE function to extract distinct product lines from your dataset.
If you see zeros, it’s because you highlighted the entire column — limit the range to your actual data or add a period (.) inside the formula to prevent zeros while keeping it dynamic.
Once you have the clean list, remove formulas by copying (Ctrl + C) and pasting values (Ctrl + Shift + V).

2. Use AVERAGEIF or AVERAGEIFS for Gross Revenue

To calculate the average gross revenue per policy type:

Use AVERAGEIF if you’re applying a single condition.
Use AVERAGEIFS if you’ll apply multiple conditions (e.g., policy type by location or agent).

Example setup:

Average range: Gross Revenue column
Criteria range: Product Line column
Criteria: Product type cell

Drag the formula down or double-click the small square at the bottom-right corner of the cell to autofill.

3. Remove Formulas and Format Values

Once the averages are calculated:

Copy the cells and paste as values using Ctrl + Shift + V.
Convert results into currency with Ctrl + Shift + 4 (or click the $ symbol in the toolbar).
- Note: Ctrl + Shift + 4 applies currency formatting, while the ribbon button uses accounting format.

4. Build a Chart to Visualize the Data

Highlight your product line list and the new Average Gross Revenue column.
Go to Insert → Recommended Charts.
Choose a Clustered Bar Chart (or another chart type such as a column or pie chart).

Optional improvements:

Add Data Labels to display revenue values.
Adjust label size for readability.
If zeros appear in the chart, remove them from your data or filter them out.

5. Final Adjustments and Dashboard Integration

Remove unnecessary zeros or empty entries.
Position charts side-by-side if you’re building a dashboard.
You can replicate the same process for different metrics (e.g., average commission, loss ratio, or claim cost per policy).

6. Key Takeaways

This process helps you:

Identify which policy types generate the highest gross revenue.
Understand profitability patterns before factoring in acquisition costs.
Create visuals for faster decision-making across product lines.

FAQ

Measuring Average Gross Revenue per Policy

Q1. What is average gross revenue per policy?
It’s the average amount of revenue your agency earns from each policy type before deducting costs like marketing or acquisition. This KPI helps you identify which product lines contribute the most to your top-line revenue.

Q2. Why should I analyze gross revenue per policy in Excel?
Excel makes it simple to group and compare revenue by policy type, highlight high-value products, and create visual dashboards that make trends easy to interpret for management or strategy meetings.

Q3. How do I calculate average gross revenue by policy type in Excel?
You can organize your dataset by product line, calculate the average revenue per policy, and then visualize the results in a chart to compare performance across different policy types. This gives you a quick view of which products drive the highest returns.

Q4. What insights can this KPI provide for an insurance agency?
Tracking average gross revenue per policy helps you identify profitable lines of business, adjust pricing or marketing focus, and make smarter decisions about which products deserve more attention and resources.

Q5. What’s the best chart for comparing policy-level revenue?
A clustered column chart works great for showing which product lines generate higher revenue. You can also experiment with pie or bar charts depending on how you want to visualize your distribution.

Q6. Can I apply this analysis to other performance metrics?
Yes. You can use the same approach to analyze metrics like average commission per policy, average claim amount, or customer lifetime value, giving you a broader view of agency performance.

Get more lessons like this
by joining our newsletter

Receive weekly Excel tutorials, tips, and KPIs straight to your inbox

Databoards

Analysis & Development