Personnel, Benefits Calculations Examples

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
401(k) Company Match 100% - Cumulative	Formula `IFF(month(this)=1,ROW.TaxableCompASSUM.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
401(k) Company Match 50% - Cumulative	Formula `IFF(month(this)=1,ROW.TaxableCompDIVF(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
401(k) Company Match Total - Cumulative	Formula `ROW.Comp_Match_100+ROW.Comp_Match_50`
401k Expense	401k_Expense	Modeled - Periodic - Currency
401k Expense	Formula `IFF(ROW.Comp_Match_Total<=ASSUM.401k_Max, (ROW.TaxableCompASSUM.Match_100Perc)+(ROW.TaxableCompdivf(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.

Within the dimension set up the following Dimension Values:

Employee Only
Employee + Spouse
Employee + Child
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.HeadcountRow.Benefits_Eligible`
Dental Expense	Dental_Expense	Modeled - Periodic - Currency	`ACCT.Beneft_Rates.Dental[level=Top Level(-), Benefit=this]Row.HeadcountRow.Benefits_Eligible`
Vision Expense	Vision_Expense	Modeled - Periodic - Currency	`ACCT.Beneft_Rates.Vision[level=Top Level(-), Benefit=this]Row.HeadcountRow.Benefits_Eligible`

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

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.HeadcountROW.SalaryASSUM.Life_Multiplier)),ASSUM.Life_per_Dollar)* ASSUM.Life_Rate,DIV(((ROW.HeadcountROW.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`

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.

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`