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

Formula Examples: Personnel, Benefits Calculations

Provides detailed examples of commonly-used benefits formulas.

Example 1: 401k Calculation

For 401k calculations, most companies either do a flat percentage match assumption for planning personnel expense while other might have a percentage match with an annual maximum.

401k Flat Percent

  • Create the following global assumption accounts:
Assumption Name Assumption Code Account Type
401(k) Flat Match 401K_FLAT_Match Percent

Formula

Account Name Account Code Account Type Formula
401(k) Employee Election 401kEmployeeElection Modeled - periodic - Currency

ASSUM.401K_Flat_Match*
ROW.TaxableComp

401k Match with Maximum

  • 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
  • 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)
  • Modeled accounts needed include:
Account Name Account Code Account Type Display As
Taxable Comp TaxableComp Modeled - periodic Currency

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))

Example 2: 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.

Within the dimension set up the following Dimension Values:

  1. Employee Only      
  2. Employee + Spouse
  3. Employee + Child
  4. Employee + Family

Create the following global assumption accounts:

  • 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
  • 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

Once the cube accounts are set up, add the Benefits dimension to the cube sheet.

Formulas

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

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

Example 3: Life Insurance

Most companies will have a Life Insurance plan at a fixed rate per X dollars of benefit.

  • Create the following global assumption accounts:
Assumption Name Assumption Code Account Type
Life_Rate Life Insurance Rate per $X of Benefit Number
Life_per_Dollar $ (per) to apply to Rate Number
Life_Multiplier Life Multiplier (Annual Earnings) Number
Life_Max_Benefit Life Max Benefit Number
  • 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)

Formula

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

Example 4: Long Term/Short Term Disability (LTD/STD)

Most companies pay for disability coverage for their employees based on a rate per $ of coverage. 

  • 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
  • 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)

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