Skip to main content
Documentation
Documentation
Adaptive Planning
Last Updated: 2023-06-23
Example: Calculate Personnel and Compensation

Example: Calculate Personnel and Compensation

Allocated Salary

For simplicity, the examples use comma separators for terms. Some browser settings require you to use semicolon separators.
Pay Calculation - 26 Pay Periods
  1. Create the following global assumption accounts:
    Assumption Name
    Assumption Code
    Account Type
    Display As
    Weeks In Month
    WeeksInMonth
    Assumption-periodic
    1
    Number
    Weeks in Year
    WeeksInYear
    Assumption-periodic
    2
    Number
  2. Make sure this account has the following
    Account Settings
    :
    • Time Rollup = Average of Roll-up Values
    • Actuals Overlay = No Actuals for account (Show plan data)
  3. 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)
  4. 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
  5. 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
  1. Create the following global assumption account:
    Assumption Name
    Assumption Code
    Account Type
    Display As
    Weeks in Year
    WeeksInYear
    Assumption-periodic
    Number
  2. Make sure this account has the following
    Account Settings
    :
    • Time Rollup = Average of Roll-up Values
    • Actuals Overlay = No Actuals for account (Show plan data)
  3. 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
  4. 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
  1. Create the following global assumption accounts:
    Assumption Name
    Assumption Code
    Account Type
    Display As
    Pay Days In Month
    PayDaysInMonth
    Assumption-periodic
    1
    Number
    Pay Days in Year
    PayDaysInYear
    Assumption-periodic
    2
    Number
  2. Make sure this account has the following
    Account Settings
    :
    • Time Rollup = Average of Roll-up Values
    • Actuals Overlay = No Actuals for account (Show plan data)
  3. 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)
  4. 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
  5. 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
  1. Columns needed include:
    Account Name
    Account Code
    Element Type
    Display As
    Pay Rate
    PayRate
    Initial Balance
    Currency
  2. 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

Unallocated Salary - For Transfers Only

Pay Calculation - 26 Pay Periods
  1. Create the following global assumption accounts:
    Assumption Name
    Assumption Code
    Account Type
    Display As
    Weeks In Month
    WeeksInMonth
    Assumption-periodic
    1
    Number
    Weeks in Year
    WeeksInYear
    Assumption-periodic
    2
    Number
  2. Make sure this account has the following
    Account Settings
    :
    • Time Rollup = Average of Roll-up Values
    • Actuals Overlay = No Actuals for account (Show plan data)
  3. 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)
  4. 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
  5. 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
  1. Create the following global assumption account:
    Assumption Name
    Assumption Code
    Account Type
    Display As
    Weeks in Year
    WeeksInYear
    Assumption-periodic
    Number
  2. Make sure this account has the following
    Account Settings
    :
    • Time Rollup = Average of Roll-up Values
    • Actuals Overlay = No Actuals for account (Show plan data)
  3. 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
  4. 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
  1. Create the following global assumption accounts:
    Assumption Name
    Assumption Code
    Account Type
    Display As
    Pay Days In Month
    PayDaysInMonth
    Assumption-periodic
    1
    Number
    Pay Days in Year
    PayDaysInYear
    Assumption-periodic
    2
    Number
  2. Make sure this account has the following
    Account Settings
    :
    • Time Rollup = Average of Roll-up Values
    • Actuals Overlay = No Actuals for account (Show plan data)
  3. 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)
  4. 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
  5. 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
  1. Columns needed include:
    Account Name
    Account Code
    Element Type
    Display As
    Pay Rate
    PayRate
    Initial Balance
    Currency
  2. 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

Allocated Overtime

Overtime Calculation - Individual Employee
  1. Create the following global assumption accounts:
    Assumption Name
    Assumption Code
    Account Type
    Display As
    Pay Days In Month
    PayDaysInMonth
    Assumption-periodic
    1
    Number
    Pay Days in Year
    PayDaysInYear
    Assumption-periodic
    2
    Number
  2. Make sure this account has the following
    Account Settings
    :
    • Time Rollup = Average of Roll-up Values
    • Actuals Overlay = No Actuals for account (Show plan data)
  3. 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)
  4. 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
  5. 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
  1. Create the following global assumption accounts:
    Assumption Name
    Assumption Code
    Account Type
    Display As
    Pay Days In Month
    PayDaysInMonth
    Assumption-periodic
    1
    Number
    Pay Days in Year
    PayDaysInYear
    Assumption-periodic
    2
    Number
  2. Make sure this account has the following
    Account Settings
    :
    • Time Rollup = Average of Roll-up Values
    • Actuals Overlay = No Actuals for account (Show plan data)
  3. 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)
  4. 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
  5. 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
  1. 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
  2. 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
  1. Create the following global assumption account:
    Assumption Name
    Assumption Code
    Account Type
    Display As
    Global OT % Salary
    Global_OT_Pct_Salary
    Assumption-periodic
    Number
  2. Make sure this account has the following
    Account Settings
    :
    • Time Rollup = Average of Roll-up Values
    • Actuals Overlay = No Actuals for account (Show plan data)
  3. Columns needed include:
    Account Name
    Account Code
    Element Type
    Display As
    Per
    Per
    Text selector. Values are:
    • Hr
    • Yr
    N/A
  4. 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
  1. Columns needed include:
    Account Name
    Account Code
    Element Type
    Display As
    Annual Bonus ($)
    Bonus_Dol
    Number
    Currency
    Bonus Attainment (%)
    Bonus_Attainment
    Number
    Percent
  2. 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
  1. Columns needed include:
    Account Name
    Account Code
    Element Type
    Display As
    Annual Bonus (%)
    Bonus_Pct
    Number
    Percent
  2. 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
  1. Create the following global assumption account:
    Assumption Name
    Assumption Code
    Account Type
    Display As
    Global Bonus %
    Global_Bonus_Pct
    Assumption-periodic
    Percent
  2. Make sure this account has the following
    Account Settings
    :
    • Time Rollup = Average of Roll-up Values
    • Actuals Overlay = No Actuals for account (Show plan data)
  3. 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
  1. Columns needed include:
    Account Name
    Account Code
    Element Type
    Display As
    Annual Bonus ($)
    Bonus_Dol
    Number
    Currency
    Bonus Attainment (%)
    Bonus_Attainment
    Number
    Percent
  2. 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
  1. Columns needed include:
    Account Name
    Account Code
    Element Type
    Display As
    Annual Bonus (%)
    Bonus_Pct
    Number
    Percent
  2. 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
  1. Create the following global assumption account:
    Assumption Name
    Assumption Code
    Account Type
    Display As
    Global Bonus %
    Global_Bonus_Pct
    Assumption-periodic
    Percent
  2. Make sure this account has the following
    Account Settings
    :
    • Time Rollup = Average of Roll-up Values
    • Actuals Overlay = No Actuals for account (Show plan data)
  3. 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
  1. Create the following global assumption account:
    Assumption Name
    Assumption Code
    Account Type
    Display As
    Pay Days in Year
    PayDaysInYear
    Assumption-periodic
    Number
  2. Make sure this account has the following
    Account Settings
    :
    • Time Rollup = Average of Roll-up Values
    • Actuals Overlay = No Actuals for account (Show plan data)
  3. Columns needed include:
    Account Name
    Account Code
    Element Type
    Display As
    Annual Commission ($)
    Comm_Dol
    Number
    Currency
    Comission Attainment (%)
    Comm_Attainment
    Number
    Percent
  4. 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
  1. Columns needed include:
    Account Name
    Account Code
    Element Type
    Display As
    Annual Commission (%)
    Comm_Pct
    Number
    Percent
  2. 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
  1. Columns needed include:
    Account Name
    Account Code
    Element Type
    Display As
    Annual Commission ($)
    Comm_Dol
    Number
    Currency
    Comission Attainment (%)
    Comm_Attainment
    Number
    Percent
  2. 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
  1. Columns needed include:
    Account Name
    Account Code
    Element Type
    Display As
    Annual Commission (%)
    Comm_Pct
    Number
    Percent
  2. 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)