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

Formula Examples: Personnel, Headcount Calculations

Provides detailed examples of commonly-used headcount formulas.

Example 1: Without Transfer

  • Create the following global assumption account (full-time equivalent, hours per week).
Assumption Name Assumption Code Account Type Display As
FTE Hr/Week FTE_HrPerWeek Assumption-periodic Number
  • 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)
  • Columns needed include:
Account Name Account Code Element Type Display As
Hire Date HireDate Date N/A
End Date EndDate Date N/A
Hr/Week HrPerWeek Number Number
  • Modeled accounts needed include:
Account Name Account Code Account Type
Beginning Headcount Headcount Modeled - cumulative
New Hire NewHire Modeled - periodic
Termination Termination Modeled - periodic
Ending Headcount EndingHeadcount Modeled - periodic
Partial Headcount PartialHeadcount Modeled - cumulative
FTE FTE Modeled - cumulative

Formulas

  • Beginning Headcount

IF (VersionMonth(this) > VersionMonth(ROW.HireDate) AND (IsBlank(ROW.EndDate) OR VersionMonth(this) <= VersionMonth(ROW.EndDate)), 1,0)

  • New Hire

IF (VersionMonth(this) = VersionMonth(ROW.HireDate), 1, 0)

  • Termination

IF (VersionMonth(this) = VersionMonth(ROW.EndDate), 1, 0)

  • Ending Headcount

ROW.Headcount+ROW.NewHire-ROW.Termination

  • Partial Headcount

MonthFraction(ROW.HireDate,ROW.EndDate, this)

  • FTE

DIVF (ROW.HrPerWeek, ASSUM.FTE_HrPerWeek)*ROW.PartialHeadcount

Example 2: With Transfers

  • Create the following global assumption account (full-time employee, hours per week).
Assumption Name Assumption Code Account Type Display As
FTE Hr/Week FTE_HrPerWeek Assumption-periodic Number
  • 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)
  • Columns needed include:
Account Name Account Code Element Type Display As
Hire Date HireDate Date N/A
End Date EndDate Date N/A
Transfer In Date TransferIn Date N/A
Transfer Out Date TransferOut Date N/A
Hr/Week HrPerWeek Number Number
  • Accounts needed include:
Account Name Account Code Account Type
Beginning Headcount Headcount Modeled - cumulative
New Hire NewHire Modeled - periodic
Termination Termination Modeled - periodic
Ending Headcount EndingHeadcount Modeled - periodic
Partial Headcount PartialHeadcount Modeled - cumulative
Unallocated Partial Headcount Unallocated_PartialHeadcount Modeled - cumulative
FTE FTE Modeled - cumulative

Formulas

  • Beginning Headcount

IFF ((VersionMonth(this)=0 AND (VersionMonth(this) > VersionMonth(ROW.HireDate) AND IsBlank (ROW.TransferIn) OR VersionMonth(this)>=VersionMonth(ROW.TransferIn)))  OR VersionMonth(this)=VersionMonth(ROW.TransferIn) AND (IsBlank(ROW.EndDate) AND IsBlank(ROW.TransferOut) OR ((VersionMonth(this) <= VersionMonth(ROW.EndDate) AND IsBlank (ROW.TransferOut)) OR VersionMonth(this)<=VersionMonth(ROW.TransferOut))), 1, 0)

  • New Hire

IF (VersionMonth(this) = VersionMonth(ROW.HireDate) AND IsBlank(ROW.TransferIn), 1, 0)

  • Termination

IF (VersionMonth(this) = VersionMonth(ROW.EndDate) and isBlank(ROW.TransferIn), 1, 0) 

  • Ending Headcount

IFF (IsBlank(ROW.EndDate) AND IsBlank(ROW.TransferOut) OR (VersionMonth(this)<VersionMonth(ROW.EndDate) AND IsBlank(ROW.TransferOut)) OR VersionMonth(this)<VersionMonth(ROW.TransferOut), ROW.Allocated_BeginningHeadcount +ROW.NewHire-ROW.Termination, 0)

  • Partial Headcount

MonthFraction(IF(IsBlank(ROW.TransferIn),ROW.HireDate,ROW.TransferIn),IF(IsBlank(ROW.TransferOut),ROW.EndDate,ROW.TransferOut),this)

  • FTE

DIVF (ROW.HrPerWeek, ASSUM.FTE_HrPerWeek)*ROW.PartialHeadcount