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 |
|
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 |
Comp_Match_100 | Modeled - Periodic - Currency | |
Formula
|
|||
401(k) Company Match |
Comp_Match_50 | Modeled - Periodic - Currency |
|
Formula
|
|||
401(k) Company Match Total - Cumulative |
Comp_Match_Total | Modeled - Periodic - Currency |
|
Formula
|
|||
401k Expense | 401k_Expense | Modeled - Periodic - Currency | |
Formula
|
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 |
|
Dental Expense | Dental_Expense | Modeled - Periodic - Currency |
|
Vision Expense | Vision_Expense | Modeled - Periodic - Currency |
|
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
|
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 |
|
STD Expense | STD_Expense | Modeled - Periodic - Currency |
|