Does Adding Trucks Increase Revenue?
Analyzing Profit Per Truck in Excel

How to Measure Profit per Truck and Decide If Adding Trucks Grows Revenue

This tutorial shows how to determine whether adding trucks actually increases profitability or quietly drains cash. You’ll learn how to calculate net profit per truck, analyze results by month and truck type, and visualize how a single truck decision can swing revenue by six figures per year.

1. Set Up the Month Structure

• Create a column labeled Month.
  
• Type January and drag down through December.
  
• This month column will drive all calculations and charts.
  

2. Bring in Revenue and Cost Categories

Copy the following fields into your model:

• Final Invoice Amount
  
• Labor Cost
  
• Fuel Cost
  
• Maintenance
  
• Insurance
  
• Depreciation
  
• Overhead Allocation
  

These values represent your revenue and cost structure per job.

3. Calculate Monthly Invoice Revenue

• Use SUMIFS() to total Final Invoice Amount for each month.
  
• Criteria: Month equals the value in your Month column.
  
• Format as currency using Ctrl + Shift + 4.
  
• Drag the formula down for all months.
  

This gives total monthly revenue.

4. Calculate Monthly Costs

Repeat the same SUMIFS() logic for each cost category:

• Labor
  
• Fuel
  
• Maintenance
  
• Insurance
  
• Depreciation
  
• Overhead
  

Key setup details:

• Lock the month reference correctly using F4 so formulas copy cleanly across rows and columns.
  
• Format all dollar values consistently and center-align for readability.
  

5. Calculate Total Cost per Month

• Add a Total Cost column.
  
• Sum all cost categories for each month.
  
• Copy the formula down the column.
  

This represents the full monthly cost of operating your trucks.

6. Calculate Gross Profit

Gross profit includes only job-specific costs, not overhead-related expenses.

• Formula:
  
  • Final Invoice Amount
    
  • minus direct job costs (labor, fuel, maintenance, etc.)
    
• Use Ctrl + D to quickly copy formulas down.
  

This shows operational profitability before overhead.

7. Calculate Net Profit

• Net Profit = Final Invoice Amount minus Total Cost
  
• Copy the formula down the column.
  

This is your true bottom-line profit per month.

8. Count the Number of Active Trucks per Month

• Use the FILTER() function to return Truck IDs where Month equals the selected month.
  
• Wrap the result with UNIQUE() to remove duplicates.
  
• Use COUNTA() to return the number of trucks.
  
• Copy the formula down for all months.
  

This tells you how many trucks were actually operating each month.

9. Calculate Profit per Truck

• Profit per Truck = Net Profit ÷ Number of Trucks
  
• Copy the formula down.
  

This metric shows how much profit each truck contributes per month.

10. Create a Monthly Profit per Truck Line Chart

• Select the Month column and Profit per Truck column (including headers).
  
• Insert a Line Chart using Recommended Charts.
  
• Add data labels for clarity.
  
• Adjust label formatting (size, color) as needed.
  

This chart reveals trends and volatility in truck profitability over time.

11. Build Annual Profit by Truck Type

Create a separate summary by Truck Type:

• Installation
  
• Service
  
• Maintenance
  
• Other types in your dataset
  

For each truck type, calculate:

• Total Invoice Revenue using SUMIFS()
  
• Total Cost using SUMIFS()
  
• Gross Profit
  
• Net Profit
  

12. Count Trucks by Truck Type

• Use FILTER() to return Truck IDs by Truck Type.
  
• Apply UNIQUE() and COUNTA() to get the number of trucks per type.
  

13. Calculate Profit per Truck by Truck Type

• Profit per Truck = Net Profit ÷ Truck Count
  
• Copy the formula down for all truck types.
  

This highlights which truck types generate the most value.

14. Create Profit per Truck by Truck Type Chart

• Select Truck Type and Profit per Truck.
  
• Insert a Clustered Bar Chart or Column Chart.
  
• Add data labels and adjust styling.
  

This visualization quickly shows which trucks are worth scaling — and which are not.

15. Optional Enhancements

• Add conditional formatting:
  
  • Green for high profit
    
  • Red for high costs
    
• Compare before-and-after scenarios when adding trucks.
  
• Use this model to simulate future truck purchases.
  

Result

You now have a clear, data-driven answer to the question:
Does adding trucks actually grow profit — or just revenue?

This model shows:

• Profit per truck by month
  
• Profit per truck by truck type
  
• How one truck decision can swing annual results by six figures