Example: Create Pay Rate Formulas
Describes how a Pay Rate calculation is used in a Personnel modeled sheet. The initial formula assumes that an employee will receive an annual increase in either a specific Raise Month, or on their anniversary date. Alternatives to this formula are also included in this topic.
In this example, the Pay Rate formula references values entered in assumptions Raise Month and Raise Pct (percent) 1.
Some things to understand about the Pay Rate calculation:
- The Pay Rate column is an Initial Balance (cumulative) element, Planned by monthly delta, which means that the data input from the planning side is then carried forward for all periods in the version. The calculations you make on the element will add to the previous month’s balance in the account.
- Syntax for IF statements in Adaptive Planning differ from Excel:
- Adaptive Planning example:
if(ACCT.A6=1 and ACCT.B6=0,X,0)
- Excel example:
if(and(A6=1, B6=0),X,0)
- Adaptive Planning example:
- There are three basic time references in the Pay Rate calculation. Each reference assigns a number to each month in the version and can be used to compare a date column against an assigned month number in the version.
- Month: Each month is assigned a number (1-12) following the calendar year that repeats throughout the version.
- Fiscalmonth: Each month is assigned a number 1-12 following the company's fiscal year that repeats throughout the version.
- Versionmonth: Each month is assigned a number. The first month of the version is 0. The numbers continue across time (without repeating). Positive numbers indicate upcoming months. Negative numbers (-) indicate past months.
The Pay Rate calculation is:
IF((VersionMonth(this) > 0) AND ROW.Headcount>0 AND (((VersionMonth(this) - VersionMonth(ROW.StartDate) > 1) AND ASSUM.RaiseMonth = 0 AND FiscalMonth(this) = FiscalMonth(ROW.StartDate)) OR (ASSUM.RaiseMonth = Month(this))), ROW.PayRate[time=THIS-1]*ASSUM.RaisePct, 0)
The following table provides a detailed breakdown of each statement in the calculation:
Statement | Description |
---|---|
VersionMonth(this)>0 |
This statement checks that the number of the month assigned in any given period throughout your version is actually after the first month of the version. This prevents granting a raise in the first month of the version. It is assumed you entered an employee's salary in the first month as their starting salary. |
ROW.Headcount>0 |
Verifies that the employee is employed in order to apply the pay rate increase. Headcount is based on the employee's start and end date column. It is a binary trigger with a result of 0 or 1 to indicate that the employee is employed (or not) in each period. |
VersionMonth(this)-VersionMonth(ROW.StartDate)>1 |
Checks each month the formula is applied to make sure a pay increase is not applied until after the employee's start date. The VersionMonth assigned to each period is checked against the unique VersionMonth assigned to the employee's start date. If the difference is greater than one (the period occurs after the start date of the employee), the employee can get an increase, provided all other and statements are met. |
ASSUM.RaiseMonth = 0 |
Checks each month to see if the RaiseMonth assumption is 0 . For anniversary raises, the raise month must be zero. |
FiscalMonth(this)=FiscalMonth(ROW.StartDate) |
Checks each FiscalMonth and compares it against the assigned FiscalMonth for the employee's start date. For example: if the employee start date is 8/12/2016 and the organization’s fiscal year is the same as the calendar year, the assigned month for FiscalMonth(ROW.StartDate) would be 8. In January of each year, FiscalMonth(this) returns a 1 . February returns a 2 , and so on. This phrase is true only when the FiscalMonth is the same as the FiscalMonth of the employee start date. This is how, year over year, it is possible to calculate something on an anniversary date. |
ASSUM.RaiseMonth = Month(this) |
Checks to make sure the number you entered for the |
ROW.PayRate[time=THIS-1]*ASSUM.RaisePct |
Defines the True clause. If the previous statements are true (all the The
|
0 |
Defines the False clause. When one (or more) of the IF conditions are not met, the raise is 0 and nothing is added to the balance. |
Read as a sentence, this formula would be:
"If this is a month within this budget version, and the employee is employed, and the period is after the employee has started, and the assumption has a value of zero, and the current month is the same as the employee's start date, or the current month matches the value in the current month of the assumption, then give a raise. Otherwise, don't give the raise."
Alternative Raise Options
Typically organizations do not give a raise on the raise month or a raise on an anniversary date. Most organizations would use simpler version of this increase calculation.
Option 1: Apply raises on the raise month. The formula is:
if(VersionMonth(this)>0 and ROW.Headcount>0 and VersionMonth(this)-VersionMonth(ROW.StartDate)>1 and ASSUM.RaiseMonth=fiscalmonth(this), ROW.PayRate[time=THIS-1]*ASSUM.RaisePct, 0
Changes to the calculation include:
Statement | Description |
---|---|
ROW.Headcount>0 |
As before, the Headcount needs to be greater than zero instead of equal to 1 . This allows the calculation to work for all rows of a split employee that may have less than one headcount in a particular row. |
ASSUM.RaiseMonth=FiscalMonth(this) |
The RaiseMonth is compared to the fiscal month of the company. This allows the employee filling out the assumption to think in terms of the company’s fiscal year rather than a forced calendar year. |
|
There is no or statement for the anniversary date. The raise is only applied on the raise month. |
Option 2: Apply raises on an anniversary date. The formula is:
if(VersionMonth(this)> 0 AND ROW.Headcount>0 AND VersionMonth(this)-VersionMonth(ROW.StartDate)> 1 AND FiscalMonth(this)=FiscalMonth(ROW.StartDate), ROW.PayRate[time=THIS-1]*ASSUM.RaisePct, 0)
See Formula Examples - Personnel, Pay Rate Calculations for additional pay rate options.