Skip to main content
Documentation
Documentation
Adaptive Planning
Last Updated: 2023-06-23
Example: Personnel, Benefits Calculations

Example: Personnel, Benefits Calculations

401k Flat Percent Calculation

For simplicity, the examples use comma separators for terms. Some browser settings require you to use semicolon separators.
  1. Create the following global assumption accounts:
    Assumption Name
    Assumption Code
    Account Type
    401(k) Flat Match
    401K_FLAT_Match
    Percent
  2. Use the following formulas:
    Account Name
    Account Code
    Account Type
    Formula
    401(k) Employee Election
    401kEmployeeElection
    Modeled - periodic - Currency
    ASSUM.401K_Flat_Match*
    ROW.TaxableComp

401k Calculation Match with Maximum

  1. Create the following global assumption accounts:
    Assumption Name
    Assumption Code
    Account Type
    Display As
    401(k) Max
    401K_Max
    Periodic - Average
    Number
    401(k) 100% of the first x%
    Match_100Perc
    Periodic - Average
    Percent
    401(k) 50% of the next x%
    Match_50Perc
    Periodic - Average
    Percent
  2. Make sure these accounts have the following
    Account Settings
    :
    • Time Rollup = Average of Roll-up Values
    • Actuals Overlay = No Actuals for account (Show plan data)
  3. Modeled accounts needed include:
    Account Name
    Account Code
    Account Type
    Display As
    Taxable Comp
    TaxableComp
    Modeled - periodic
    Currency
  4. Formulas:
    Account Name
    Account Code
    Account Type
    401(k) Company Match 100% - Cumulative
    Comp_Match_100
    Modeled - Periodic - Currency
    Formula
    IFF(month(this)=1,ROW.TaxableComp*ASSUM.Match_100Perc, ROW.Comp_Match_100[time=this-1] + ROW.TaxableComp *ASSUM.Match_100Perc)
    401(k) Company Match 50% - Cumulative
    Comp_Match_50
    Modeled - Periodic - Currency
    Formula
    IFF(month(this)=1,ROW.TaxableComp*DIVF(ASSUM.Match_50Perc,2), ROW.Comp_Match_50[time=this-1] + ROW.TaxableComp *DIVF(ASSUM.Match_50Perc,2))
    401(k) Company Match Total - Cumulative
    Comp_Match_Total
    Modeled - Periodic - Currency
    Formula
    ROW.Comp_Match_100+ROW.Comp_Match_50
    401k Expense
    401k_Expense
    Modeled - Periodic - Currency
    Formula
    IFF(ROW.Comp_Match_Total<=ASSUM.401k_Max, (ROW.TaxableComp*ASSUM.Match_100Perc)+(ROW.TaxableComp*divf(ASSUM.Match_50Perc,2)), IFF(ROW.Comp_Match_Total>ASSUM.401k_Max and ROW.Comp_Match_Total[time=this-1]<=ASSUM.401k_Max, ASSUM.401k_Max-ROW.Comp_Match_Total[time=this-1],0))

Medical, Dental, and Vision Benefits

Most companies will want to plan Medical, Dental, and Vision Benefits by an employee's election with the ability to manage/maintain separate rates for each.
To begin set up a
Benefit
 dimension. Keep the standard settings. Make sure
List dimension
is selected, but make sure
Use on levels
,
Data import automatically creates dimension values
, and
Keep sorted
 are not selected.
  1. Within the dimension set up the following Dimension Values:
    • Employee Only
    • Employee + Spouse
    • Employee + Child
    • Employee + Family
  2. Set up a user-assigned cube sheet available a the Top Level (only) and create three standard cube accounts:
    Account Name
    Account Code
    Account Type
    Display As
    Medical Rate
    Medical
    Periodic
    Currency
    Dental Rate
    Dental
    Periodic
    Currency
    Vision Rate
    Vision
    Periodic
    Currency
  3. Make sure these accounts have the following
    Account Settings
    :
    • Time Rollup = Average of Roll-up Values
    • Level Rollup = Non-Blank Average of Rollup Values
    • Actuals Overlay = No Actuals for account (Show plan data)
    • Display as = Currency
    • Data Privacy + Value of Account is public at all levels
  4. Once the cube accounts are set up, add the
    Benefits
    dimension to the cube sheet.
  5. The monthly lookup tables needed are:
    Account Name
    Account Code
    Column
    Value Entered
    Benefits Eligible
    Benefits_Eligible
    Employee Type - Text Selector
    Populate 1 in all relevant combinations
  6. Now, set up the following calculated accounts within the Personnel modeled sheet:
    Account Name
    Account Code
    Account Type
    Formula
    Medical Expense
    Medical_Expense
    Modeled - Periodic - Currency
    ACCT.Beneft_Rates.Medical[level=Top Level(-), Benefit=this]*Row.Headcount*Row.Benefits_Eligible
    Dental Expense
    Dental_Expense
    Modeled - Periodic - Currency
    ACCT.Beneft_Rates.Dental[level=Top Level(-), Benefit=this]*Row.Headcount*Row.Benefits_Eligible
    Vision Expense
    Vision_Expense
    Modeled - Periodic - Currency
    ACCT.Beneft_Rates.Vision[level=Top Level(-), Benefit=this]*Row.Headcount*Row.Benefits_Eligible

Life Insurance

Most companies will have a Life Insurance plan at a fixed rate per
X
 dollars of benefit.
  1. Create the following global assumption accounts:
    Assumption Name
    Assumption Code
    Account Type
    Life Insurance Rate per $X of Benefit
    Life_Rate
    Number
    $ (per) to apply to Rate
    Life_per_Dollar
    Number
    Life Multiplier (Annual Earnings)
    Life_Multiplier
    Number
    Life Max Benefit
    Life_Max_Benefit
    Number
  2. Make sure these accounts have the following
    Account Settings
    :
    • Time Rollup = Average of Roll-up Values
    • Actuals Overlay = No Actuals for account (Show plan data)
  3. Formulas:
    Assumption Name
    Assumption Code
    Account Type
    Life Insurance
    Life_Insurance
    Modeled - Periodic - Currency
    Formula
    IFF(DIV(ASSUM.Life_Max_Benefit,ASSUM.Life_per_Dollar)* ASSUM.Life_Rate> DIV(((ROW.Headcount*ROW.Salary*ASSUM.Life_Multiplier)),ASSUM.Life_per_Dollar)* ASSUM.Life_Rate,DIV(((ROW.Headcount*ROW.Salary)* ASSUM.Life_Multiplier),ASSUM.Life_per_Dollar)* ASSUM.Life_Rate,div(ASSUM.Life_Max_Benefit,ASSUM.Life_per_Dollar)*ASSUM.Life_Rate)* Row.Benefits_Eligible

Long Term/Short Term Disability (LTD/STD)

Most companies pay for disability coverage for their employees based on a rate per $ of coverage.
  1. Create the following global assumption accounts:
    Assumption Name
    Assumption Code
    Account Type
    Display As
    Rate per $100
    LTD_RatePer100
    Periodic - Average
    Number
    Rate per $10
    STD_RatePer10
    Periodic - Average
    Number
  2. Make sure these accounts have the following
    Account Settings
    :
    • Time Rollup = Average of Roll-up Values
    • Actuals Overlay = No Actuals for account (Show plan data)
  3. Formulas:
    Account Name
    Account Code
    Account Type
    Formulas
    LTD Expense
    LTD_Expense
    Modeled - Periodic - Currency
    DIVF(ROW.Salary,100) * ASSUM.LTD_RatePer100 * Row.Benefits_Eligible
    STD Expense
    STD_Expense
    Modeled - Periodic - Currency
    DIVF(ROW.Salary,10) * ASSUM.STD_RatePer10 * Row.Benefits_Eligible