Skip to main content
Adaptive Insights
Knowledge and Support - Adaptive Insights

Formula Examples: Analyzing a Pay Rate Formula - A Detailed Walkthrough

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

This topic provides details on the Pay Rate calculation used in a Personnel modeled sheet. The initial formula makes the assumption 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 statement prevents Adaptive Planning from calculating a raise before this version, which would cause a formula error.
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)

For additional Pay Rate options, refer to Formula Examples - Personnel, Pay Rate Calculations.

  • Was this article helpful?