Example: Create Pay Rate Formulas

Last updated
Save as PDF

Shows an example Pay Raise formula and includes a step-by-step description of each statement that makes up the formula.

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)
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 `RaiseMonth` assumption matches the number of the current month. For example: if you want the raise to be in April of 2017, place a `4` in the assumption `RaiseMonth` in April of 2017.
`ROW.PayRate[time=THIS-1]*ASSUM.RaisePct`	Defines the True clause. If the previous statements are true (all the `and` statements and one of the `or` statements), then all the `IF` conditions are met, and the raise (`ASSUM.PaisePct`) is applied. The `PayRate` formula a references the previous period `[time=THIS-1]`, rather than the current period. Reasons for this are: The `PayRate` calculation is an initial balance element. It will carry the balance that is entered in the column from the planning side forward (previous month plus). If there is a formula in the initial balance element like the one described here, it will calculate an addition to the previous month. Because the initial balance element automatically carries the balance forward and adds any new calculations to it, it would also include whatever was being evaluated in the current month. Therefore, to avoid any circular references, the increase is based on the previous month’s pay rate balance.
`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.
`fiscalmonth(this) = fiscalmonth(ROW.StartDate)) or (ASSUM. RaiseMonth = Month(this)`	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.