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

Formula Examples: Personnel, Compensation Calculations

Provides detailed examples of commonly-used compensation formulas including: salary, overtime, bonus, and commission.

Example 1: Allocated Salary

Pay Calculation - 26 Pay Periods

  • Create the following global assumption accounts:
Assumption  Name Assumption Code Account Type Display As
Weeks In Month WeeksInMonth Assumption-periodic1 Number
Weeks in Year WeeksInYear Assumption-periodic2 Number
  1. Make sure this account has the following Account Settings:

    1. Time Rollup = Average of Roll-up Values
    2. Actuals Overlay = No Actuals for account (Show plan data)
  2. Make sure this account has the following Account Settings:
    1. Time Rollup = Sum of Roll-up Values
    2. Actuals Overlay = No Actuals for account (Show plan data)
  • Columns needed include:

Account Name Account Code  Element Type Display As
Per Per

Text selector. Values are:

  • Hr
  • Yr
N/A
Hr/Week HrPerWeek Number Number
Pay Rate PayRate Initial Balance Currency
  • Modeled accounts needed: 

Account Name Account Code Account Type
Partial Headcount PartialHeadcount Modeled - cumulative

 

Account Name Account Code Account Type 
Salary Salary Modeled - periodic - currency

Formula

ROW.PartialHeadcount*IF(ROW.Per="hr",(ROW.HrPerWeek*ASSUM.WeeksInMonth), DIV(ASSUM.WeeksInMonth,ASSUM.WeeksInYear)) * ROW.PayRate

Pay Calculation - 24 Pay Periods

  • Create the following global assumption account:
Assumption  Name Assumption Code Account Type Display As
Weeks in Year WeeksInYear Assumption-periodic Number
  • Make sure this account has the following Account Settings:

    1. Time Rollup = Average of Roll-up Values
    2. Actuals Overlay = No Actuals for account (Show plan data)
  • Columns needed include:

Account Name Account Code  Element Type Display As
Per Per

Text selector. Values are:

  • Hr 
  • Yr
N/A
Hr/Week HrPerWeek Number Number
Pay Rate PayRate Initial Balance Currency
  • Modeled accounts needed: 

Account Name Account Code Account Type
Partial Headcount PartialHeadcount Modeled - cumulative

 

Account Name Account Code Account Type 
Salary Salary Modeled - periodic - Currency

Formula

ROW.PartialHeadcount*IF(ROW.Per="hr",DIV(ROW.HrPerWeek*ASSUM.WeeksInYear,12),DIV(1,12)) * ROW.PayRate

Pay Calculation - Work Days

  • Create the following global assumption accounts:
Assumption  Name Assumption Code Account Type Display As
Pay Days In Month PayDaysInMonth Assumption-periodic1 Number
Pay Days in Year PayDaysInYear Assumption-periodic2 Number
  1. Make sure this account has the following Account Settings:

    1. Time Rollup = Average of Roll-up Values
    2. Actuals Overlay = No Actuals for account (Show plan data)
  2. Make sure this account has the following Account Settings:
    1. Time Rollup = Sum of Roll-up Values
    2. Actuals Overlay = No Actuals for account (Show plan data)
  • Columns needed include:

Account Name Account Code  Element Type Display As
Per Per

Text selector. Values are:

  • Hr 
  • Yr
N/A
Hr/Week HrPerWeek Number Number
Pay Rate PayRate Initial Balance Currency
  • Modeled accounts needed: 

Account Name Account Code Account Type
Partial Headcount PartialHeadcount Modeled - cumulative

 

Account Name Account Code Account Type 
Salary Salary Modeled - periodic - Currency

Formula

ROW.PartialHeadcount*IF(ROW.Per="hr",ROW.HrPerWeek*DIV(ASSUM.PayDaysInMonth,5), DIV(ASSUM.PayDaysInMonth,ASSUM.PayDaysInYear)) * ROW.PayRate

Pay Calculation - Only Salaried Employees

  • Columns needed include:

Account Name Account Code  Element Type Display As
Pay Rate PayRate Initial Balance Currency
  • Modeled accounts needed: 

Account Name Account Code Account Type
Partial Headcount PartialHeadcount Modeled - cumulative

 

Account Name Account Code Account Type 
Salary Salary Modeled - periodic - Currency

Formula

ROW.PartialHeadcount*DIV(1,12) * ROW.PayRate

Example 2: Unallocated Salary - For Transfers Only

Pay Calculation - 26 Pay Periods

  • Create the following global assumption accounts:
Assumption  Name Assumption Code Account Type Display As
Weeks In Month WeeksInMonth Assumption-periodic1 Number
Weeks in Year WeeksInYear Assumption-periodic2 Number
  1. Make sure this account has the following Account Settings:

    1. Time Rollup = Average of Roll-up Values
    2. Actuals Overlay = No Actuals for account (Show plan data)
  2. Make sure this account has the following Account Settings:
    1. Time Rollup = Sum of Roll-up Values
    2. Actuals Overlay = No Actuals for account (Show plan data)
  • Columns needed include:

Account Name Account Code  Element Type Display As
Per Per

Text selector. Values are:

  • Hr 
  • Yr
N/A
Hr/Week HrPerWeek Number Number
Pay Rate PayRate Initial Balance Currency
  • Modeled accounts needed: 

Account Name Account Code Account Type
Unallocated Partial Headcount Unallocated_PartialHeadcount Modeled - cumulative

 

Account Name Account Code Account Type 
Salary Salary Modeled - periodic - currency

Formula

ROW.Unallocated_PartialHeadcount*IF(ROW.Per="hr",(ROW.HrPerWeek*ASSUM.WeeksInMonth), DIV(ASSUM.WeeksInMonth,ASSUM.WeeksInYear)) * ROW.PayRate

Pay Calculation - 24 Pay Periods

  • Create the following global assumption account:
Assumption  Name Assumption Code Account Type Display As
Weeks in Year WeeksInYear Assumption-periodic Number
  • Make sure this account has the following Account Settings:

    1. Time Rollup = Average of Roll-up Values
    2. Actuals Overlay = No Actuals for account (Show plan data)
  • Columns needed include:

Account Name Account Code  Element Type Display As
Per Per

Text selector. Values are:

  • Hr 
  • Yr
N/A
Hr/Week HrPerWeek Number Number
Pay Rate PayRate Initial Balance Currency
  • Modeled accounts needed: 

Account Name Account Code Account Type
Unallocated Partial Headcount Unallocated_PartialHeadcount Modeled - cumulative

 

Account Name Account Code Account Type 
Unallocated Salary Unallocated_Salary Modeled - periodic - Currency

Formula

ROW.Unallocated_PartialHeadcount*IF(ROW.Per="hr",DIV(ROW.HrPerWeek*ASSUM.WeeksInYear,12), DIV(1,12)) * ROW.PayRate

Pay Calculation - Work Days

  • Create the following global assumption accounts:
Assumption  Name Assumption Code Account Type Display As
Pay Days In Month PayDaysInMonth Assumption-periodic1 Number
Pay Days in Year PayDaysInYear Assumption-periodic2 Number
  1. Make sure this account has the following Account Settings:

    1. Time Rollup = Average of Roll-up Values
    2. Actuals Overlay = No Actuals for account (Show plan data)
  2. Make sure this account has the following Account Settings:
    1. Time Rollup = Sum of Roll-up Values
    2. Actuals Overlay = No Actuals for account (Show plan data)
  • Columns needed include:

Account Name Account Code  Element Type Display As
Per Per

Text selector. Values are:

  • Hr 
  • Yr
N/A
Hr/Week HrPerWeek Number Number
Pay Rate PayRate Initial Balance Currency
  • Modeled accounts needed: 

Account Name Account Code Account Type
Unallocated Partial Headcount Unallocated_PartialHeadcount Modeled - cumulative

 

Account Name Account Code Account Type 
Unallocated Salary Unallocated_Salary Modeled - periodic - Currency

Formula

ROW.Unallocated_PartialHeadcount*IF(ROW.Per="hr",ROW.HrPerWeekDIV(ASSUM.PayDaysInMonth,5), DIV(ASSUM.PayDaysInMonth,ASSUM.PayDaysInYear)) * ROW.PayRate

Pay Calculation - Only Salaried Employees

  • Columns needed include:

Account Name Account Code  Element Type Display As
Pay Rate PayRate Initial Balance Currency
  • Modeled accounts needed: 

Account Name Account Code Account Type
Unallocated Partial Headcount Unallocated_PartialHeadcount Modeled - cumulative

 

Account Name Account Code Account Type 
Unallocated Salary Unallocated_Salary Modeled - periodic - Currency

Formula

ROW.Unallocated_PartialHeadcount*DIV(1,12) * ROW.PayRate

Example 3: Allocated Overtime

Overtime Calculation - Individual Employee

  • Create the following global assumption accounts:
Assumption  Name Assumption Code Account Type Display As
Pay Days In Month PayDaysInMonth Assumption-periodic1 Number
Pay Days in Year PayDaysInYear Assumption-periodic2 Number
  1. Make sure this account has the following Account Settings:

    1. Time Rollup = Average of Roll-up Values
    2. Actuals Overlay = No Actuals for account (Show plan data)
  2. Make sure this account has the following Account Settings:
    • Time Rollup = Sum of Roll-up Values
    • Actuals Overlay = No Actuals for account (Show plan data)
  • Columns needed include:

Account Name Account Code  Element Type Display As
Per Per

Text selector. Values are:

  • Hr 
  • Yr
N/A
Hr/Week HrPerWeek Number Number
Pay Rate PayRate Initial Balance Currency
  • Modeled accounts needed: 

Account Name Account Code Account Type
Salary Salary Modeled - cumulative

 

Account Name Account Code Account Type 
OT OT Modeled - periodic - Currency

Formula

IF(ROW.Per="hr",ROW.OT_Pct*ROW.Salary,0)

Overtime Calculation - Global 

  • Create the following global assumption accounts:
Assumption  Name Assumption Code Account Type Display As
Pay Days In Month PayDaysInMonth Assumption-periodic1 Number
Pay Days in Year PayDaysInYear Assumption-periodic2 Number
  1. Make sure this account has the following Account Settings:

    1. Time Rollup = Average of Roll-up Values
    2. Actuals Overlay = No Actuals for account (Show plan data)
  2. Make sure this account has the following Account Settings:
    • Time Rollup = Sum of Roll-up Values
    • Actuals Overlay = No Actuals for account (Show plan data)
  • Columns needed include:

Account Name Account Code  Element Type Display As
Per Per

Text selector. Values are:

  • Hr 
  • Yr
N/A
Hr/Week HrPerWeek Number Number
Pay Rate PayRate Initial Balance Currency
  • Modeled accounts needed: 

Account Name Account Code Account Type
Partial Headcount PartialHeadcount Modeled - cumulative

 

Account Name Account Code Account Type 
OT OT Modeled - periodic - Currency

Formula

IF(ROW.Per="hr",ASSUM.Global_OT_Pct_Salary*ROW.Salary,0)

Example 4: Unallocated Overtime - For Transfers Only

Overtime Calculation - Individual Employee

  • Columns needed include:

Account Name Account Code  Element Type Display As
Per Per

Text selector. Values are:

  • Hr 
  • Yr
N/A
OT % OT_Pct Number Percent
  • Modeled accounts needed: 

Account Name Account Code Account Type
Unallocated Salary Unallocated_Salary Modeled - periodic

 

Account Name Account Code Account Type 
Unallocated OT Unallocated_OT Modeled - periodic - Currency

Formula

IF(ROW.Per="hr",ROW.OT_Pct*ROW.Unallocated_Salary,0)

Overtime Calculation - Global 

  • Create the following global assumption account:
Assumption  Name Assumption Code Account Type Display As
Global OT % Salary Global_OT_Pct_Salary Assumption-periodic Number
  • Make sure this account has the following Account Settings:

    1. Time Rollup = Average of Roll-up Values
    2. Actuals Overlay = No Actuals for account (Show plan data)
  • Columns needed include:

Account Name Account Code  Element Type Display As
Per Per

Text selector. Values are:

  • Hr 
  • Yr
N/A
  • Modeled accounts needed: 

Account Name Account Code Account Type
Unallocated Salary Unallocated_Salary Modeled - periodic

 

Account Name Account Code Account Type 
Unallocated OT Unallocated_OT Modeled - periodic - Currency

Formula

IF(ROW.Per="hr",ASSUM.Global_OT_Pct_Salary*ROW.Unallocated_Salary,0)

Example 5: Allocated Bonus

Bonus Calculation - Annual $ Amount

  • Columns needed include:

Account Name Account Code  Element Type Display As
Annual Bonus ($) Bonus_Dol Number Currency
Bonus Attainment (%) Bonus_Attainment Number Percent
  • Modeled accounts needed: 

Account Name Account Code Account Type
Partial Headcount PartialHeadcount Modeled - cumulative

 

Account Name Account Code Account Type 
Bonus Bonus Modeled - periodic - Currency

Formula

DIV(ROW.Bonus_Dol*ROW.Bonus_Attainment,12)*ROW.PartialHeadcount

Bonus Calculation - % of Salary

  • Columns needed include:

Account Name Account Code  Element Type Display As
Annual Bonus (%) Bonus_Pct Number Percent
  • Modeled accounts needed: 

Account Name Account Code Account Type
Salary Salary Modeled - periodic

 

Account Name Account Code Account Type 
Bonus Bonus Modeled - periodic - Currency

Formula

ROW.Salary*ROW.Bonus_Pct

Bonus Calculation - Global

  • Create the following global assumption account:
Assumption  Name Assumption Code Account Type Display As
Global Bonus % Global_Bonus_Pct Assumption-periodic Percent
  • Make sure this account has the following Account Settings:

    1. Time Rollup = Average of Roll-up Values
    2. Actuals Overlay = No Actuals for account (Show plan data)
  • Modeled accounts needed: 

Account Name Account Code Account Type
Salary Salary Modeled - periodic

 

Account Name Account Code Account Type 
Bonus Bonus Modeled - periodic - Currency

Formula

ASSUM.Global_Bonus_Pct*ROW.Salary

 

Example 6: Unallocated Bonus - For Transfers Only

Bonus Calculation - Annual $ Amount

  • Columns needed include:

Account Name Account Code  Element Type Display As
Annual Bonus ($) Bonus_Dol Number Currency
Bonus Attainment (%) Bonus_Attainment Number Percent
  • Modeled accounts needed: 

Account Name Account Code Account Type
Unallocated Partial Headcount Unallocated_PartialHeadcount Modeled - cumalative

 

Account Name Account Code Account Type 
Unallocated Bonus Unallocated_Bonus Modeled - periodic - Currency

Formula

DIV(ROW.Bonus_Dol*ROW.Bonus_Attainment,12)*ROW.Unallocated_PartialHeadcount

Bonus Calculation - % of Salary

  • Columns needed include:

Account Name Account Code  Element Type Display As
Annual Bonus (%) Bonus_Pct Number Percent
  • Modeled accounts needed: 

Account Name Account Code Account Type
Unallocated Salary Unallocated_Salary Modeled - periodic

 

Account Name Account Code Account Type 
Unallocated Bonus Unallocated_Bonus Modeled - periodic - Currency

Formula

ROW.Unallocated_Salary*ROW.Bonus_Pct

Bonus Calculation - Global

  • Create the following global assumption account:
Assumption  Name Assumption Code Account Type Display As
Global Bonus % Global_Bonus_Pct Assumption-periodic Percent
  • Make sure this account has the following Account Settings:

    1. Time Rollup = Average of Roll-up Values
    2. Actuals Overlay = No Actuals for account (Show plan data)
  • Modeled accounts needed: 

Account Name Account Code Account Type
Unallocated Salary Unallocated_Salary Modeled - periodic

 

Account Name Account Code Account Type 
Unallocated Bonus Unallocated_Bonus Modeled - periodic - Currency

Formula

ASSUM.Global_Bonus_Pct*ROW.Unallocated_Salary

Example 7: Allocated Commission

Commission Calculation - Annual $ Amount

  • Create the following global assumption account:
Assumption  Name Assumption Code Account Type Display As
Pay Days in Year PayDaysInYear Assumption-periodic Number
  • Make sure this account has the following Account Settings:

    1. Time Rollup = Average of Roll-up Values
    2. Actuals Overlay = No Actuals for account (Show plan data)
  • Columns needed include:

Account Name Account Code  Element Type Display As
Annual Commission ($) Comm_Dol Number Currency
Comission Attainment (%) Comm_Attainment Number Percent
  • Modeled accounts needed: 

Account Name Account Code Account Type
Partial Headcount PartialHeadcount Modeled - periodic

 

Account Name Account Code Account Type 
Commission Comission Modeled - periodic - Currency

Formula

DIV(ROW.Comm_Dol*ROW.Comm_Attainment,12)*ROW.PartialHeadcount

Commission Calculation - % of Salary

  • Columns needed include:
Account Name Account Code  Element Type Display As
Annual Commission (%) Comm_Pct Number Percent
  • Modeled accounts needed: 

Account Name Account Code Account Type
Salary Salary Modeled - periodic

 

Account Name Account Code Account Type 
Commission Comission Modeled - periodic - Currency

Formula

ROW.Salary*ROW.Comm_Pct

Example 8: Unallocated Commission - For Transfers Only

Commission Calculation - Annual $ Amount

  • Columns needed include:
Account Name Account Code  Element Type Display As
Annual Commission ($) Comm_Dol Number Currency
Comission Attainment (%) Comm_Attainment Number Percent
  • Modeled accounts needed: 

Account Name Account Code Account Type
Allocated Partial Headcount Allocated_PartialHeadcount Modeled - periodic

 

Account Name Account Code Account Type 
Unallocated Commission Unallocated_Comission Modeled - periodic - Currency

Formula

DIV(ROW.Comm_Dol*ROW.Comm_Attainment,12)*ROW.Unallocated_PartialHeadcount

Commission Calculation - % of Salary

  • Columns needed include:
Account Name Account Code  Element Type Display As
Annual Commission (%) Comm_Pct Number Percent
  • Modeled accounts needed: 

Account Name Account Code Account Type
Unallocated Salary Unallocated_Salary Modeled - periodic

 

Account Name Account Code Account Type 
Unallocated Commission Unallocated_Comission Modeled - periodic - Currency

Formula

ROW.Unallocated_Salary*ROW.Comm_Pct

Example 9: Taxable Compensation

Taxable Compensation

  • Accounts needed:

Account Name Account Code  Element Type Display As
Bonus Bonus Modeled - periodic Currency
Commission Commission Modeled - periodic Currency
OT OT Modeled - periodic Currency
Salary Salary Modeled - periodic Currency

 

 

Account Name Account Code Account Type 
Taxable Comp TaxableComp Modeled - periodic - Currency

Formula

ROW.Salary+ROW.Allocated_Bonus+ROW.Commission+ROW.OT

Unallocated Taxable Compensation - For Transfers Only

  • Accounts needed:

Account Name Account Code  Element Type Display As
Unallocated Bonus Unallocated_Bonus Modeled - periodic Currency
Unallocated Commission Unallocated_Commission Modeled - periodic Currency
Unallocated OT Unallocated_OT Modeled - periodic Currency
Unallocated Salary Unallocated_Salary Modeled - periodic Currency

 

 

Account Name Account Code Account Type 
Unallocated Taxable Comp Unallocated_TaxableComp Modeled - periodic - Currency

Formula

ROW.Unallocated_Salary+ROW.Unallocated_Bonus+ROW.Unallocated_Commission+ROW.Unallocated_OT

Example 10: Cumulative Compensation

Without Transfers

  • Accounts needed:

Account Name Account Code  Element Type Display As
Taxable Comp TaxableComp Modeled - periodic Currency

 

 

Account Name Account Code Account Type 
CumulativeComp CumulativeComp Modeled - periodic - Currency

Formula

IFF(month(this)>1,ROW.TaxableComp+ROW.CumulativeComp[time=this-1],ROW.TaxableComp)

With Transfers

  • Accounts needed:

Account Name Account Code  Element Type Display As
Unallocated Taxable Comp Unallocated_TaxableComp Modeled - periodic Currency

 

 

Account Name Account Code Account Type 
CumulativeComp CumulativeComp Modeled - periodic - Currency

Formula

IFF(month(this)>1,ROW.Unallocated_TaxableComp+ROW.CumulativeComp[time=this-1], ROW.Unallocated_TaxableComp)