Analyze Monthly Cash Collected by Carrier in Excel

How to Calculate Monthly Cash Collected by Carrier in Excel

This tutorial shows how to calculate and visualize monthly cash collected by carrier using a Pivot Table and a Stacked Column Chart.
You’ll also learn how to add slicers for filtering by agent name, region, or any other field, so your chart updates dynamically as you explore the data.

1. Insert a Pivot Table

• Click anywhere inside your dataset.
  
• Go to Insert → Pivot Table → From Table/Range.
  
• Choose Existing Worksheet and select the starting cell where you want the pivot table to appear.
  
• Click OK to create it.
  

Now you’ll define which fields go where in the Pivot Table.

2. Set Up Pivot Table Fields

• Rows: Month (Column D)
  
• Columns: Carrier (Column J)
  
• Values: Sum of Cash Collected (Column AY)
  

Excel automatically sums the values for each carrier by month.
You can center-align the text and adjust formatting for readability.

3. Create a Stacked Column Pivot Chart

• Click anywhere inside the pivot table.
  
• Go to Insert → Pivot Chart.
  
• Choose Stacked Column Chart and click OK.
  
• Move and resize the chart as needed.
  

To match your dashboard’s design, you can switch to a dark theme or customize colors.

4. Clean Up Field Buttons

When you insert a Pivot Chart, Excel adds interactive buttons (field buttons) by default.
You can hide them for a cleaner look:

1. Click the chart.
   
2. Go to PivotChart Analyze → Field Buttons.
   
3. Deselect Legend Field, Axis Field, and Value Field buttons.
   

Now your chart will show only the data, without extra clutter.

5. Add Slicers for Filtering

• Click inside your pivot table.
  
• Go to Insert → Slicer.
  
• Select the fields you want to filter by, for example, Agent Name and Region.
  
• Drag and position your slicers near the chart.
  

Each slicer acts as a filter. When you select an agent or region, your chart instantly updates to show the corresponding data.

To make slicers easier to use:

• Click a slicer → Slicer Tab → Columns → set to 2 (or 3 if you have many options).
  
• Resize the slicers for better layout and readability.
  

6. Analyze Cash Flow by Carrier

With your chart and slicers ready, you can:

• See which carriers generate the most monthly cash collected.
  
• Identify which carrier represents a large share of total cash flow (potential risk concentration).
  
• Spot trends or irregularities in monthly collections.
  

This helps you quickly evaluate cash flow risk and carrier performance at a glance.

7. Optional Customizations

• Add slicers for additional dimensions (e.g., Customer Type, Product Line, Region).
  
• Use conditional formatting in your pivot table for high/low cash values.
  
• Change chart labels, colors, or fonts to match your dashboard’s design.
  
• Rename the chart to something like “Monthly Cash Collected by Carrier” for clarity.
  

Result

You now have a dynamic Pivot Table and Stacked Column Chart that displays monthly cash collected by carrier.
With slicers, you can instantly filter by agent name, region, or any other field, giving you a flexible, interactive view of your company’s cash flow by source.