Analyze Cash Collected and Collection Rate in Excel

How to Track Cash Collected vs. Written Premium in Excel

In this tutorial, you’ll learn how to create a simple but powerful Cash Collection Dashboard in Excel. We’ll use a Pivot Table, calculate a Collection Percentage, and add interactive slicers and conditional formatting to instantly highlight underperforming carriers.

1. Build the Pivot Table

• Click anywhere in your dataset.
  
• Go to Insert → PivotTable → From Table/Range.
  
• Choose Existing Worksheet and set your starting point (e.g., Cell A1).
  
• If your dataset has many columns, sort them alphabetically to locate fields more easily.
  

2. Add Written Premium and Cash Collected

• Add Written Premium to the Values area of the PivotTable.
  
• Add Carrier to the Rows area.
  
• Then, add Cash Collected to the Values area as well.
  
• Both metrics will now be displayed by Carrier.
  

3. Calculate Collection Percentage

• In your PivotTable field list, find Cash Collection Percent (if already in your dataset).
  
• If not, calculate it manually by dividing Cash Collected by Written Premium.
  Example:
  =CashCollected ÷ WrittenPremium
• Format the result as a percentage with Ctrl + Shift + 5.
  
• Adjust decimals for cleaner display.
  

This metric instantly shows how much of the written premium has actually been collected.

4. Add Slicers for Filtering

• Click inside your PivotTable.
  
• Go to Insert → Slicer.
  
• Select Lead Source and Product Line.
  
• Arrange slicers side by side.
  
• Right-click each slicer → Slicer Settings → Hide items with no data → Uncheck “Display Header.”
  
• Adjust slicer columns to fit all items (e.g., two columns wide).
  

Slicers allow you to filter performance by product or lead source interactively.

5. Apply Conditional Formatting

• Highlight the Collection % column in your PivotTable.
  
• Go to Home → Conditional Formatting → Highlight Cells Rules → Less Than.
  
• Enter 0.95 or 95% as your threshold.
  
• Choose a red or light-red fill to highlight poor collection performance.
  

Now, any carrier or product with a collection rate below 95% will immediately stand out.

6. Make Thresholds Dynamic (Optional)

• Instead of a fixed 95%, you can tie the rule to a cell reference.
  
• Example:
  
  1. Type your desired threshold (like 95%) in Cell A1.
     
  2. In the Conditional Formatting rule, use =$A$1 as the reference.
     
  3. Changing that value will automatically update the highlight range.
     

This lets you adjust your sensitivity (e.g., see results below 90% or 99%) without recreating rules.

7. Interpret and Analyze Results

• A 100% rate means full collection, but always check amounts, large premiums at 95% may still mean thousands outstanding.
  
• Use slicers to identify underperforming segments (like Email Campaigns or Referrals).
  
• Adjust collection strategies based on lead source performance.
  

8. Optional Enhancements

• Add a “Difference” column to calculate Written Premium minus Cash Collected.
  
• Include total outstanding balance in dollars for quick insights.
  
• Turn your table into a dashboard by formatting slicers and charts for presentation.
  

Result

You now have a fully interactive Cash Collection Dashboard in Excel that tracks Written Premium, Cash Collected, and Collection Percentage, complete with slicers and color-coded insights. This setup instantly shows which carriers or products are underperforming, so you can focus on improving cash flow.