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.
Before You Begin
- Required permission: Model Management Access > Model.
- See Create Modeled Sheets.
How You Get There
From the nav menu, select Modeling.
Create Personnel Dimensions
It saves time to create the needed personnel-related dimensions before starting on the sheet.
From the Modeling menu, select Dimensions.
Create dimensions for identifying employees, such as Region or Job Code. You do not need to select a dimension type.
Create values for each dimension. For example, a dimension called Region may have values of North America, South America, and Asia.
See Dimensions for more information.
Create a New Personnel Sheet
From the Modeling menus, select Level Assigned Sheets.
Select the New Sheet button.
Enter a Sheet Name.
Select the Create a new sheet radio button, and then choose Modeled and Personnel from the drop-downs.
Enter an Account Code Prefix, and select Next.
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).
- 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
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.
if(month(this) >= month(ROW.StartDate) and (isblank(ROW.EndDate) or month(this) <= month(ROW.EndDate)), 1, 0)
This formula calculates headcount.
This formula calculates hours worked per month by an hourly employee.
ROW.PartialHeadcount* if(ROW.per="Hr",ROW.HrPerWeek*(2080)/(480), 1/12)
This formula calculates an employee’s monthly salary.
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.)
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
Use the breadcrumbs to return to the Sheet Summary, and select Columns and Levels.
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:
Select Benefits (center pane) and scroll down to Value Lookups (right pane).
Expand Value Lookups and select Add new value lookup.
Add a Name for the lookup (for example, BenChoices).
Specify your preferences for Display As and Decimal Places.
Select OK to add the new lookup, then Save to save changes to the sheet.
Select Benefits > Value Lookups and select Edit (next to BenChoices). A page appears with a row for each Benefits choice you created (Yes, No, and so on).
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.
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.
Select Modeled Accounts. When the Accounts page appears, select the Benefits calculated account.
Scroll to Data Type (right pane).
Enter a new formula which multiplies salaries by the new lookup. For example:
- 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.
- From the Sheet Summary, select Assumptions.
- 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.
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:
From the Sheets Summary, select Columns and Levels.
Select Dimensions (left pane) to view a list of available dimensions for the sheet.
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.
For each dimension you want users to modify from the Sheet Viewer, select the dimension and select Edit dimension on sheet.
Set Sheet Properties
Sheet properties control To set the sheet’s properties, do the following:
Select (on the toolbar) to set the sheet properties.
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.
Select Settings and make changes as desired. Refer to Set Sheet Properties.
Select OK to save the property changes.
Save (on the toolbar) to save all changes to the sheet.
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:
From the nav menu, select Formulas.
Select the GL salaries account that should display the results of the Personnel sheet calculations.
Select Set Formula > Formula Assistant.
Use the Formula Assistant to create a formula linking the GL salaries account to the appropriate modeled account.
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