Profit per person is a metric that measures the efficiency and performance of a company’s workforce. It is calculated by dividing the company’s profit by the number of full-time employees. It can be used to compare the profitability of different companies or different periods of time.
How to Calculate Profit Per Person in Excel
To calculate profit per person in Excel, you need to have the following data:
- Total revenue of the company
- Operating expenses of the company
- Number of full-time employees (FTEs) of the company
The formula for profit per person is:
You can enter this formula in any cell of your Excel worksheet, and adjust the cell references according to your data. For example, if your revenue is in cell C4, your operating expenses are in cell C5, and your FTEs are in cell C6, you can enter the following formula in cell C7:
= (C4 - C5) / C6
This will give you the profit per person for your company.
Example of Profit Per Person in Excel
To illustrate how to calculate profit per person in Excel, let’s use the following example data:
| Revenue | Operating expenses | FTEs |
|---|---|---|
| 500,000 | 300,000 | 50 |
We can enter this data in an Excel table, and use the formula we learned above to calculate the profit per person. Here is how the table looks like:
| Revenue | Operating expenses | FTEs | Profit per person |
|---|---|---|---|
| 500,000 | 300,000 | 50 | 4,000 |
As you can see, the profit per person for this company is 4,000. This means that each employee contributes 4,000 to the company’s profit.
Other Approaches to Calculate Profit Per Person in Excel
There are other ways to calculate profit per person in Excel, depending on the data you have and the level of detail you want. Here are some alternative approaches:
- If you have the profit data instead of the revenue and operating expenses, you can simply divide the profit by the FTEs to get the profit per person. For example, if your profit is in cell C4 and your FTEs are in cell C5, you can enter the following formula in cell C6:
= C4 / C5
- If you want to calculate the profit per person for different segments of your company, such as departments, regions, or products, you can use the SUMIF or SUMIFS functions to sum up the revenue and operating expenses for each segment, and then divide by the FTEs for each segment. For example, if you have the following data:
| Department | Revenue | Operating expenses | FTEs |
|---|---|---|---|
| Sales | 200,000 | 100,000 | 20 |
| Marketing | 150,000 | 80,000 | 15 |
| R&D | 100,000 | 50,000 | 10 |
| HR | 50,000 | 20,000 | 5 |
You can calculate the profit per person for each department by using the following formula in cell E2, and copying it down to the other cells:
= (SUMIF(A:A,A2,C:C) - SUMIF(A:A,A2,D:D)) / SUMIF(A:A,A2,E:E)
This will give you the following results:
| Department | Revenue | Operating expenses | FTEs | Profit per person |
|---|---|---|---|---|
| Sales | 200,000 | 100,000 | 20 | 5,000 |
| Marketing | 150,000 | 80,000 | 15 | 4,667 |
| R&D | 100,000 | 50,000 | 10 | 5,000 |
| HR | 50,000 | 20,000 | 5 | 6,000 |
As you can see, the profit per person varies by department, and you can use this information to analyze the performance and efficiency of each segment of your company.
- If you want to calculate the profit per person for different periods of time, such as months, quarters, or years, you can use the SUMIFS function to sum up the revenue and operating expenses for each period, and then divide by the average FTEs for each period. For example, if you have the following data:
| Date | Revenue | Operating expenses | FTEs |
|---|---|---|---|
| 01/01/2024 | 50,000 | 30,000 | 10 |
| 02/01/2024 | 40,000 | 25,000 | 11 |
| 03/01/2024 | 60,000 | 35,000 | 12 |
| 04/01/2024 | 55,000 | 32,000 | 13 |
| 05/01/2024 | 45,000 | 28,000 | 14 |
| 06/01/2024 | 65,000 | 40,000 | 15 |
You can calculate the profit per person for each quarter by using the following formula in cell F2, and copying it down to the other cells:
= (SUMIFS(C:C,A:A,">="&E2,A:A,"<"&E2+90) - SUMIFS(D:D,A:A,">="&E2,A:A,"<"&E2+90)) / AVERAGEIFS(E:E,A:A,">="&E2,A:A,"<"&E2+90)
This will give you the following results:
| Date | Revenue | Operating expenses | FTEs | Quarter start | Profit per person |
|---|---|---|---|---|---|
| 01/01/2024 | 50,000 | 30,000 | 10 | 01/01/2024 | 3,333 |
| 02/01/2024 | 40,000 | 25,000 | 11 | 01/01/2024 | 3,333 |
| 03/01/2024 | 60,000 | 35,000 | 12 | 01/01/2024 | 3,333 |
| 04/01/2024 | 55,000 | 32,000 | 13 | 01/04/2024 | 4,167 |
| 05/01/2024 | 45,000 | 28,000 | 14 | 01/04/2024 | 4,167 |
| 06/01/2024 | 65,000 | 40,000 | 15 | 01/04/2024 | 4,167 |
As you can see, the profit per person changes by quarter, and you can use this information to track the trends and seasonality of your company’s profitability.
