How do I apply a 5-4-4 spread to employee salaries in my Modeled sheet?
- From within the Model Builder, add a Sheet Assumption that details your monthly spread pattern. (i.e.5,4,4)
- Create a new Calculated account that pulls values for the part of the salary that would hit the '0' headcount months. (i.e. 0headspread) The formula might look something like,
- Create a new Calculated account that pulls the value of the assumption for only months when headcount is not '0'. (i.e. 0headspreadassum) The formula might look something like,
- Create a new Calculated account for the spread account to pull values only for the periods between the start and end dates. You will also want to add the yearly value from the '0' headcount months and apply that value to headcount>0 months based on the ratio of the week to the total number of weeks the employee is employed. (i.e. 544) The formula might look something like:
if(versionmonth(this) >= versionmonth(ROW.StartDate) and (isblank (ROW.EndDate) or versionmonth(this) <= versionmonth(ROW.EndDate)),spread544 (ROW.Salary[time=this.year])+(ROW.0headspread[time=this.year] * div( ROW.0headspreadassum, ROW.0headspreadassum[time=this.year])),0)
I have bolded areas of the formula logic where specific accounts are referenced. You will want to ensure that you are referencing the correct accounts for your model within your formula logic.
The resulting spread would look something like this when viewed from the Modeled sheet using ‘Row Details’.