How to Track Revenue per Labor Dollar in Excel
for Better Workforce Performance

How to Calculate Revenue per Labor Dollar in Excel

1. Create the fully burdened payroll cost column

• Start with the payroll dataset and identify the pay-related fields that need to be combined.
• Add together gross pay and the additional employer-paid costs such as payroll taxes, workers’ compensation, unemployment, and benefits.
• Use the SUM function to roll those payroll-related values into one monthly labor cost field.
• This creates the payroll cost figure that will be used in the KPI.

2. Create a month field for grouping the data

• Use the payroll period end date to create a month-based grouping field.
• Apply the EOMONTH function so each record is assigned to the end of its month.
• Make sure the function uses the current month rather than pushing the date into the following month.
• This gives you a clean month column that can be used to summarize revenue and payroll by month.

3. Build a unique monthly summary list

• Create a new section for the KPI summary table.
• Pull a distinct list of months from the month field you just created.
• Use the UNIQUE function on the table column, not the entire worksheet column.
• Format the month list and center it so the summary section is easier to read.

4. Return one revenue value for each month

• In this dataset, the monthly revenue value repeats across multiple payroll rows for the same month.
• Because of that, revenue should not be summed from every row in the payroll table.
• Instead, return a single matching revenue value for each month from the repeated records.
• Use XLOOKUP to find the month in the payroll table and return the corresponding revenue amount.
• An exact match is used so each month pulls the correct revenue number.
• Fill the result down for the rest of the months in the summary table.

5. Sum the monthly payroll cost

• Once revenue is returned by month, calculate total payroll cost for each month.
• Use SUMIFS to add the fully burdened payroll cost for all rows that belong to the same month.
• This gives you one monthly payroll total for each month in the summary table.
• Fill the calculation down through the full monthly list.

6. Calculate revenue per labor dollar

• Create the KPI column in the summary table.
• Divide the monthly revenue by the monthly payroll cost.
• Fill the result down for each month so you can see how the KPI changes over time.
• This produces the monthly revenue per labor dollar values used in the chart.

7. Add a benchmark line

• Add a separate benchmark column next to the KPI.
• Enter a target value such as 1.75 and repeat it down the full table.
• Keeping the benchmark in its own column makes it easy to change later and have the chart update automatically.
• This creates a dynamic target line for comparison.

8. Add an overall total section

• Build a total row or small summary block below the monthly table.
• Use SUM to total overall revenue and overall payroll cost across the dataset.
• Then divide those totals to calculate the overall revenue per labor dollar for the full time period.
• This gives you a single top-level KPI in addition to the monthly trend view.

9. Create the combo chart

• Highlight the month column, the revenue column, and the revenue per labor dollar column.
• Insert a chart from Recommended Charts, then switch to All Charts and choose Combo.
• Set revenue as a column series.
• Set revenue per labor dollar as a line series.
• Move the revenue series to the secondary axis so the chart is easier to read.
• This setup lets the monthly revenue bars and KPI line work together without distorting the scale.

10. Add the benchmark line to the chart

• Include the benchmark column in the chart as an additional line series.
• Format it as a subtle reference line so it does not dominate the visual.
• This makes it easy to see which months are above or below target.
• Because the benchmark is linked to cells, changing the target automatically updates the chart.

11. Clean up the chart formatting

• Adjust the chart title to match the KPI.
• Refine the line and bar formatting so the benchmark stays visible without taking up too much visual space.
• Keep labels minimal so the chart does not become cluttered.
• If needed, add labels only to the KPI line or only to a few important points such as highs and lows.

12. Extend the model if needed

• If you want to break the analysis down further, add a year column to the data.
• Then use SUMIFS with an additional criterion to summarize by both month and year.
• This is especially useful when the dataset spans more than one calendar year.
• The same structure can also be adapted for department, job type, or other workforce categories.