Revenue per Technician:
Monthly Trend + Tech Concentration

How to Identify Revenue Concentration and Growth Bottlenecks in Excel

This tutorial shows how to uncover revenue concentration, capacity risk, and growth bottlenecks by analyzing average revenue by technician and total revenue by individual contributors. By slicing revenue the right way, you can quickly see who is carrying the load and where scaling issues may appear.

1. Create the Month Column

• Type Month in the header cell.
  
• Enter January and drag down through December.
  
• In your dataset, locate the Date column.
  
• Use the TEXT() function to extract the month from each date.
  
  • Example logic: convert the date into a month name.
    

This allows all revenue to be grouped cleanly by month.

2. Calculate Total Net Revenue by Month

• Create a column called Net Revenue.
  
• Use the SUMIFS() function to sum revenue by month.
  
  • Sum Range: Net Revenue column
    
  • Criteria Range: Month column
    
  • Criteria: January, February, etc.
    
• Drag the formula down for all months.
  
• Format as currency using Ctrl + Shift + 4 and remove decimals if desired.
  

This gives you total monthly revenue.

3. Count the Number of Technicians Working Each Month

• Use the FILTER() function to return technician names by month.
  
• Wrap the result in UNIQUE() to remove duplicates
  
• Use COUNTA() to count how many unique technicians worked that month.
  
• Drag the formula down to see month-to-month variation.
  

This ensures you are counting only active technicians per month, not everyone in the dataset.

4. Calculate Average Revenue per Technician

• Divide Total Monthly Revenue by Number of Technicians.
  
• Drag the formula down.
  
• Center the values and adjust decimals if needed.
  

This metric shows productivity and helps identify capacity constraints.

5. Create a Line Chart for Average Revenue per Technician

• Highlight Month and Average Revenue per Technician.
  
• Hold Ctrl while selecting non-adjacent columns if needed.
  
• Go to Insert → Line Chart (or Recommended Charts).
  
• Optionally apply a dark theme or add data labels.
  

This chart highlights trends, spikes, and potential bottlenecks over time.

6. Create a Sorted List of Technicians

• Use UNIQUE() to generate a list of technician or salesperson names.
  
• Wrap it in SORT() to order alphabetically.
  
• Copy and paste values using Ctrl + Shift + V to remove formulas.
  

This prepares the data for individual performance analysis.

7. Calculate Net Revenue by Technician

• Create a column called Net Revenue by Technician.
  
• Use SUMIFS() to sum revenue by technician name.
  
  • Sum Range: Net Revenue
    
  • Criteria Range: Technician Name
    
• Apply formatting using Format Painter for consistency.
  

This shows total revenue contribution per individual.

8. Create a Clustered Bar Chart

• Click anywhere in the revenue-by-technician table.
  
• Go to Insert → Recommended Charts → Clustered Bar.
  
• Sort the values from smallest to largest for readability.
  
• Resize the chart so all names are visible.
  

This visualization immediately reveals revenue concentration.

9. Interpret Revenue Concentration

• A small number of technicians generate a large share of revenue.
  
• High concentration may indicate:
  
  • Capacity risk
    
  • Dependency on a few installers
    
  • Potential scaling constraints
    

If installers dominate revenue, consider breaking analysis into:

• Installation
  
• Maintenance
  
• Service
  

This gives a clearer picture of operational balance.

10. Extend the Analysis

• Apply the same formulas to:
  
  • Salesperson
    
  • Service area
    
  • System type
    
• All insights come from the same core functions:
  
  • SUMIFS()
    
  • UNIQUE()
    
  • FILTER()
    

The power is not in new formulas, but in how you slice the data.

Result

You now have two powerful views:

• Average revenue per technician over time
  

Total revenue contribution by individual