Modeling staff compensation
Trying to build a financial model for a startups operations at a granular level. I have a revenue build of new customers, churn to get to total customers and with average revenue to get to monthly revenue that folds into the income statement. I have expenses as a % of revenue with drivers at the bottom of income statement.
I have employee compensation broken out into a separate tab with all employees listed out and want to fold that into the income statement line item. Is there a clean way to link with a dynamic formula summing only if start date is later than month date on income statement or something? Could use some help here on thinking through the cleanest formula as there's about 30 employees on the spreadsheet.
Currently have something super basic - see below. Do I have to project out monthly or can I work with something like this?
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
2. Dynamic Formula for Summing Compensation
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:
3. Handling Employees Without End Dates
4. Alternative Approach: Helper Column
5. Projection Without Monthly Breakdown
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
I'd say you're pretty much there. One set up is:
Employee Level tab (shown above):
-Monthly Cost: To easily track, break out into separate columns for (I) current, (II) future employees, using IF Statement based on Start Date vs. Income Statement month (i.e. Sum Monthly Cost column if (I) Column K - Current Employee: If Income Statement date > Start Date; (II) Column L - Future Employee: If Income Statement date Start Date)
Income Statement tab:
-Under your line item (i.e. SG&A), pull in above columns into separate lines for employee compensation of (I) current + (II) future
--(I) Pull from Column K in Employee Level tab
--(II) Pull from Column L in Employee Level tab
Note: When calculating compensation based on dates, use EOMONTH on Start Dates for employees when comparing to Income Statement date for simplicity, assumes 1 month has passed, or pro-rate difference between Start Date vs. Income Statement date for partial periods & compensation accrued for more granular)
Rerum consequatur quam doloribus esse ullam repellendus aut error. Ab expedita blanditiis nihil id sequi optio. Quo asperiores similique facere quas laborum recusandae vel.
Saepe corporis sit ea occaecati odit iure accusantium. Ut dolorem et quia dolorem deserunt voluptates et.
See All Comments - 100% Free
WSO depends on everyone being able to pitch in when they know something. Unlock with your email and get bonus: 6 financial modeling lessons free ($199 value)
or Unlock with your social account...