How to Track Payroll as a Percentage of Revenue
(and Protect Your Profit)

Payroll % of Revenue

1. Prepare the payroll data

• Start in the payroll tab and identify the fields needed for the analysis.
• Create a month-end date from each pay date using the EOMONTH function.
• Add a total payroll cost field by combining the payroll-related expense columns, such as gross pay, employer taxes, and benefits.
• This creates the monthly payroll amount that will later be grouped by month.

2. Create a unique list of months

• In the reporting area, build a clean month list using the UNIQUE function.
• This removes duplicate month-end dates from the payroll data.
• The result becomes the foundation for the monthly KPI table.

3. Set up the summary table

• Add the reporting columns for:
  • Revenue
  • Payroll
  • Percent
• This creates the structure for the monthly output used in the chart.

4. Pull monthly revenue into the summary

• Go to the revenue tab and match each month in the summary table to the correct revenue amount.
• Use a lookup method such as XLOOKUP.
• The video also mentions that INDEX and MATCH can be used as an alternative.
• Format the revenue values as currency and fill the results down the table.

5. Sum payroll by month

• Return to the payroll data and aggregate the payroll totals by each month in the summary table.
• Use SUMIFS to sum the payroll cost field based on the matching month-end date.
• Make sure the table references point to the correct payroll table columns.
• Fill the calculation down so each month shows its total payroll expense.

6. Calculate the percentage

• Create the percent column by dividing payroll by revenue.
• Format the result as a percentage.
• Fill the calculation down the full month list so each period has its payroll percentage of revenue.

7. Build the chart

• Highlight the month labels, revenue values, and percentage values.
• Select the revenue and percentage series for the chart.
• Go to Insert and then Recommended Charts.
• Since the default chart will not display the KPI clearly, switch to a Combo chart.

8. Assign the axes correctly

• Keep revenue as the column series.
• Set the percentage series as the line.
• Move revenue to the secondary axis so the percentage line remains the primary KPI and is easier to read.
• This makes the chart visually balanced because the revenue values are much larger than the percentage values.

9. Clean up the chart formatting

• Update the chart title to match the KPI name.
• Add data labels only if needed.
• If labels feel too crowded, keep them off or apply them only to the line series.
• Adjust the label color or make labels bold to improve readability.
• Reposition any overlapping labels manually if necessary.

10. Finalize the dashboard view

• Review the table and chart together to make sure each month shows:
  • Revenue
  • Total payroll
  • Payroll percentage of revenue
• Once formatted, the output gives you a clear monthly visual of how payroll is trending relative to revenue.