メインコンテンツまでスキップ
Adaptive Insights
Knowledge @ Adaptive Insights LLC, a Workday Company

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

 

 

 

 

 

 

  • この記事は役に立ちましたか?