Skip to main content
Documentation
Documentation
Adaptive Planning
Last Updated: 2023-06-23
Create a Personnel Sheet

Create a Personnel Sheet

This topic walks you through setting up a typical Personnel sheet. You start with a modeled sheet template for
Personnel
and then customize it. There are several tasks required to create and customize the sheet.

Prerequisites

Navigation

Compass.png From the nav menu, select
Modeling
.

Create Personnel Dimensions

It saves time to create the needed personnel-related dimensions before starting on the sheet.
Compass.png From the Modeling menu, select
Dimensions
.
  1. Create dimensions for identifying employees, such as
    Region
    or
    Job Code
    . You do not need to select a dimension type.
  2. Create values for each dimension. For example, a dimension called
    Region
    may have values of
    North America
    ,
    South America
    , and
    Asia
    .

Create a New Personnel Sheet

Compass.png From the Modeling menus, select
Level Assigned Sheets.
  1. Select the
    New Sheet
    button.
  2. Enter a Sheet Name.
  3. Select the
    Create a new sheet
    radio button, and then choose
    Modeled
    and
    Personnel
    from the drop-downs.
  4. Enter an
    Account Code Prefix, and select
    Next
    .
  5. Select
    Columns and Levels
     to view the columns that are already included on the sheet. You can edit, reorder, or delete them as desired. The columns are Last Name (Text), First Name (Text), ID (Text), Level (Required column), Benefits (Text Selector), Start Date (Date), End Date (Date), Hr/Week (Number), Pay Rate (Initial Balance), and Per (Text Selector).
  6. The personnel sheet template includes calculated accounts. From the left side panel, select
    Display Columns
    .
You can add these accounts to the sheet and modify them. The best practice is to leave these accounts as is, except you may want to modify the Benefits calculation. You can modify Benefits by using the breadcrumbs to return to the Sheet Summary. Then select
Modeled Accounts
.
The automatically-created accounts are:
  • Partial Headcount
    monthfraction(ROW.StartDate,ROW.EndDate,this,30)
     This formula calculates the portion of the month an employee should be paid for. In the month of hire (month of start date), this is likely to be a partial month.
  • Headcount
    if(month(this) >= month(ROW.StartDate) and (isblank(ROW.EndDate) or month(this) <= month(ROW.EndDate)), 1, 0)
     This formula calculates headcount.
  • Hours
    ROW.HrPerWeek*ROW.PartialHeadcount
     This formula calculates hours worked per month by an hourly employee.
  • Salary
    ROW.PartialHeadcount* if(ROW.per="Hr",ROW.HrPerWeek*(2080)/(480), 1/12) * ROW.PayRate
     This formula calculates an employee's monthly salary.
  • Benefits
    ROW.Salary * if(ROW.Benefits="Yes", (10)/(100), 0)
     This formula calculates benefits as a percentage of salaries.
Pay Rate is another calculated account. This account is associated with the initial value element, which is included with the personnel sheet by default.
  • Pay Rate
    if((month(this) > 0) AND ROW.Headcount=1 AND (((month(this) - month(ROW.StartDate) > 1) AND ASSUM.RaiseMonth = 0 AND fiscalmonth(this) = fiscalmonth(ROW.StartDate)) OR (ASSUM.RaiseMonth = Month(this))),ROW.PayRate[time=THIS-1]*ASSUM.RaisePct, 0)
     This formula calculates an employee's pay rate, including any planned raises. Users can to enter each employee's initial pay rate. Subsequent months' pay rates are calculated based on the previous month's rate and a raise assumption.

Review and Update Benefits Expense

On the template personnel sheet, benefits expense is calculated as 10 percent of salaries for all employees who have benefits enabled. The benefits choices are:
  • No
    (default for new employees.)
  • Yes
You can modify or delete this benefits calculation.
Select
Benefits
(in the center pane) and review the choices (in the right pane). You can rename the default choices of
Yes
and
No
, add new choices (for example,
Full-time
,
Part-time
), or use the arrow keys to reorganize the choices. The first choice in the list is the default when users add a new row to the Personnel sheet.
These benefits choices are used in a
calculated account
 called Benefits, with this formula:
ROW.Salary * IF(ROW.BenefitsSelector="Yes", (10)/(100), 0)
If you modify the Benefits choices, this formula may need to be modified as well. For example, if additional choices are added beyond the simple
Yes
and
No
, you may need to add a value lookup to this column and update the calculated account to reference the lookup.

Modify the Benefits

  1. Use the breadcrumbs to return to the Sheet Summary, and select
    Columns and Levels
    .
  2. Select the
    Benefits
    column, update the Text selector items.
Add any new items (for example,
Part-Time
 or
Canada
), and edit or replace the existing
Yes
and
No
items.
If any of the text selector items are in use in the sheet, they cannot be deleted or renamed.

Add a Value Lookup

You can use value lookups to translate information users enter on a sheet into calculated account values. Monthly lookups can be added for a custom dimension or text selector. For example, the
Yes
/
No
 text for
Benefits
choices.
If you edited the sheet to offer more Benefits choices (other than
Yes
or
No
), you need to add a lookup.
To add a value lookup for Benefits:
  1. Select
    Benefits
     (center pane) and scroll down to
    Value Lookups
     (right pane).
  2. Expand Value Lookups and select
    Add new value lookup
    .
  3. Add a Name for the lookup (for example, BenChoices).
  4. Specify your preferences for Display As and
    Decimal Places.
  5. Select
    OK
     to add the new lookup, then
    Save
     to save changes to the sheet.
  6. Select
    Benefits
     >
    Value Lookups
     and select the pencil icon
    (next to
    BenChoices
    ). A page appears with a row for each Benefits choice you created (
    Yes
    ,
    No
    , and so on).
  7. Enter the desired values (formula or number) for each Benefits choice, then Save.

Update the Calculated Account

After you create the lookup, the next step is to modify the Benefits calculated account so that it includes the BenChoices lookup in its formula.
  1. Return to the Sheet Summary for your Personnel sheet. Notice there's a new
    Value Lookups
     link on this page. This link appears because you added a value lookup.
  2. Select
    Modeled Accounts
    . When the Accounts page appears, select the
    Benefits
     calculated account.
  3. Scroll to Data Type (right pane).
  4. Enter a new formula which multiplies salaries by the new lookup. For example:
    ROW.Salary*ROW.BenChoices
  5. Select
    Save
     to save your changes to the sheet.
You can modify the choices in the
Per
 column the same way as described for the
Benefits
 column. The default choices are per
Yr
 and per
Hr
. These
Per
 choices are used in a calculated account called
Salary
. If the
Per
 choices are modified, the
Salary
 account formula may need to be modified as well.

Review Salary Raise Assumptions

The Personnel template already contains two sheet assumptions designed to determine how employees' salaries and wages are automatically increased over time. You can modify or delete these assumptions.
The assumptions do not contain any values, so you need to update them for salaries to be automatically increased.
  1. From the Sheet Summary, select
    Assumptions
    .
  2. You can enter raise assumptions to fit the following two scenarios:
    • Scenario #1: All employees receive raises in the same month, for example 5% every March. To set this up, enter the month number (in this case, 3) in all cells in the assumption titled
      Raise Month
      . Enter the raise percentage in all cells of the assumption titled
      Raise Percentage
      .
    • Scenario #2: All employees are to receive raises in their anniversary months. In this case, enter the month number as zero in all cells in assumption row titled
      Raise Month
      . Enter the raise percentage in all cells of the assumption titled
      Raise Percentage
      .
  3. Save.
You can also create calculated account with the logic to have employees receive raises on their anniversary dates.

Adding Custom Dimensions (Optional)

Depending on your model, it may be useful to include custom dimensions on the personnel sheet. You can use dimensions for sorting and reporting headcount data.
To add custom dimensions to the sheet, do the following:
  1. From the Sheets Summary, select
    Columns and Levels
    .
  2. Select
    Dimensions
     (left pane) to view a list of available dimensions for the sheet.
  3. One-by-one, select the dimensions you want to add (right pane) and drag them to the sheet canvas (center pane). In Dimension Properties, specify the Dimension Values
    to include for each dimension.
  4. For each dimension you want users to modify from the Sheet Viewer, select the dimension and select
    Edit dimension on sheet
    .

Set Sheet Properties

To set the sheet's properties, do the following:
  1. Click the gear icon from the toolbar to set the sheet properties.
  2. Select
    Security
    . If required, select
    Viewing this sheet or any of its rows requires Salary Level Detail permission.
    This prevents sheet users from seeing salary-related information, unless they have permission.
  3. Select
    Settings
     and make changes as desired. Refer to Set Sheet Properties.
  4. Select
    OK
     to save the property changes.
  5. Save.

Linking GL Accounts to Modeled Accounts

The values in the calculated accounts do not roll up to the Profit and Loss (P&L) until a link is made to the appropriate general ledger (GL) accounts. The GL salaries account (for example, 7000_01 Salaries & Wages) in every department can be populated with a formula, linking it to the calculated account(s) for Salaries.
To create a formula linking GL accounts to the calculated accounts for your Personnel sheet:
  1. From the nav menu, select
    Formulas
    .
  2. Select the GL salaries account that should display the results of the Personnel sheet calculations.
  3. Select
    Set Formula > Formula Assistant
    .
  4. Use the Formula Assistant to create a formula linking the GL salaries account to the appropriate modeled account.
  5. Select
    OK
    .

Enter Headcount by Job Position

The personnel sheet created from the template does not have a timespan element. Users are not required to enter values in all the plan months that headcount will be present. The user specifies plans by individual employee, and specifies each employee's start date, annual salary or hourly rate. The personnel sheet then calculates all of the resulting values in the appropriate time periods.
Sometimes budget managers want to plan groups of employees hired in certain time periods. For example, a user may want to plan for four new sales representatives to be hired in January, three more to be hired in March, and so on.
A solution to this is to have separate personnel sheets for different kinds of headcount planning. You can plan existing headcount on a personnel sheet, by person, as described above. Then, you can create a separate personnel sheet to accommodate future new hires by job position