Modeling staff compensation

To dynamically link employee compensation into your income statement based on start dates, you can use a clean and efficient formula in Excel. Here's how you can approach it:

1. Structure Your Employee Compensation Tab

• Include columns for:
  • Employee Name
  • Start Date
  • Monthly Salary
  • End Date (if applicable, or leave blank for active employees)

2. Dynamic Formula for Summing Compensation

• Use a formula that checks if the employee's start date is before or equal to the current month in the income statement and, if applicable, if the end date is after or blank.

• Assuming your income statement months are in row 1 (e.g., B1, C1, etc.), and your employee data starts in row 2, you can use the following formula in the income statement for each month:

  =SUMIFS(EmployeeCompensation!C:C, EmployeeCompensation!B:B, "=" & A1, EmployeeCompensation!D:D, ">=" & A1)

• Explanation:

  • EmployeeCompensation!C:C: The column with monthly salaries.
  • EmployeeCompensation!B:B: The column with start dates.
  • "=" & A1: Ensures the start date is before or equal to the current month.
  • EmployeeCompensation!D:D: The column with end dates.
  • ">=" & A1: Ensures the end date is after or blank.

3. Handling Employees Without End Dates

• If some employees don't have an end date, you can adjust the formula to account for blank cells: excel =SUMIFS(EmployeeCompensation!C:C, EmployeeCompensation!B:B, "=" & A1, EmployeeCompensation!D:D, ">=" & A1) + SUMIFS(EmployeeCompensation!C:C, EmployeeCompensation!B:B, "=" & A1, EmployeeCompensation!D:D, "")

4. Alternative Approach: Helper Column

• Add a helper column in the employee compensation tab to calculate whether the employee is active for a given month: excel =IF(AND(Start_Date = Current_Month, OR(End_Date >= Current_Month, End_Date = "")), Monthly_Salary, 0)
• Then, sum this helper column in your income statement.

5. Projection Without Monthly Breakdown

• If you want to avoid projecting out monthly, you can calculate total compensation for the year and allocate it evenly across months. However, this approach sacrifices granularity and may not reflect actual timing of expenses.

This method ensures your model remains dynamic and scalable, even as you add more employees or adjust start/end dates.

Sources: How much have you saved?, "Leaving IB Soon, Final Rants" - The Follow-Up, "Leaving IB Soon, Final Rants" - The Follow-Up, How do you value the people you work for?, Salary progression for F500