This example works for any type of item that you would want to calculate per employee based on how long they have been with you. The end formula will calculate a percentage of Salary, then dilute that based on average 401k participation.
This example adds some global assumptions to the model and a new account to the modeled sheet you use for personnel information.
- Assum.401k_Match = the percentage of salary we match.
- Assum.401k_Participation = a percentage of people on average who participates, to reduce the matched amounts.
We use the following syntax to get the number of days the employee has worked.
This - Row.StartDate
“This” in the formula refers to what month (assumes month as lowest time stratum) the system is currently evaluating. Row.StartDate is a column in your modeled sheet (note that codes vary from model to model).
For the final formula we will only calculate the match when an employee has been here for 365 days or more.
(This - Row.StartDate) < 365,
Row.Salary * ASSUM.401k_Match * ASSUM.401K_Participation
The user interface might not match what you see in this video.