How to Create and Configure a New Worksheet Item
Worksheet items are used on the Schedule tab to enter budget data in the Salary Budget module. All worksheet items require:
1 – An entry in the ‘Worksheet Items’ code table
2 – An entry in the “Worksheet Items Detail’ code table
If the worksheet item is not a shift line it will also require:
3 – An Earning defined in ‘Earning Benefit Types’ that the item will target
This document assumes that any necessary Earning has already been defined; please refer to the ‘How to Create and Configure a new Earning Code’ for these steps.
Step 1 – Create the Worksheet Item Entry
Start by entering Code Table Maintenance.
- Service Area is ‘Budget – Salary’, Code Table is ‘Worksheet Items’
- Create the new Entry in the ‘Worksheet Items’ grid.
You will be filling in the following columns:
Sequence Group | The sequence group determines where it displays on the worksheet. A – Shift Codes – FTE entry B – Annual Budgeted – manually calculated Hours/Each entries C – Relief Calculations – relief items automatically calculated D – Premiums – auto calculated premiums and other auto items |
Code | The short code for the item. |
Description | The description you want to use for the item. |
Step 2 – Create the Worksheet Item Detail Entry
- Start by entering Code Table Maintenance.
- Service Area is ‘Budget – Salary’, Code Table is ‘Worksheet Item Details’
- Create the new Entry in the ‘Worksheet Items Code Table Detail’ grid
You will be filling in the following columns:
Budget Version | Target Budget Version for the new item. |
Worksheet Item | Pick the item you created in ‘Worksheet Items’. If it doesn’t appear you may need to click ‘Refresh’. |
Earning Benefit Type | If you are creating a shift line leave this as ‘NOTCODED’. Otherwise, pick the appropriate Earning code. |
Value Type | The value type of this item. Shift lines are always Hours. Shift differentials/premiums are typically Each.
In general, if the item you are creating is based on uses the employee’s Hourly Rate to determine pay it will be Hours. Otherwise, it will be Each. |
Transfer Value Type | Shift lines are always ‘FTE’. Otherwise, it will agree with the ‘Value Type’ you have chosen. |
Entry Type | Determines where the item will show up in the Schedule tab. |
Allocation Type | If it is a Shift Line leave this as ‘NOTCODED’. Otherwise, pick the appropriate default allocation. This will determine which employees are chosen by default to receive values from this item code. |
Hours Each/Shift | Only used in Shift Lines. Indicates how much paid time a day represents. |
# Weeks | Only used in Shift Lines. Indicates how many weeks of a year the line represents. |
Include FTE Hours | Determines whether the line will generate FTE values in the Schedule tab. |
Paid Hours | Generally agrees with ‘Include FTE Hours’. |
- Set up the Item Accumulators
- Worksheet items may contribute to accumulators used by other Worksheet Items. These can be configured in the ‘CA: Item Accumulators’ tab.
If necessary, you will be filling in the following columns:
Accumulator Group | The accumulator group the item code will contribute to. This should never be the same accumulator used in the item’s formula. |
Total | Entries to the ‘All Year’ column in the schedule tab will be multiplied by the factor entered here and then added to the Accumulator. |
Day01…14 | Entries to the ‘Day’ columns in the schedule tab will be multiplied by the factor entered here and then added to the Accumulator. |
- Setting up the Item’s Formula
Worksheet items that are auto-calculated are configured in the CB: Formulas tab.
A listing of all Source Parameters are located in the ‘Source Parameters’ tab.
Source Parameters are the formula’s interface with the accumulators and with other various data sources in the system. Double-clicking on any source parameter listed will add it to the ‘Formula Editor’ window.
Source parameters that have a single entry (eg: VB, VE, VS) typically pull from a source table elsewhere in the system. Source parameters that list multiple numeric entries (eg: HH1…7) are pulling information from accumulator groups.
For further information on Source Parameters please see our technical documentation on that Code Table.
The ‘Formula List’ tab lists all formulas available to use.
All formulas are documented in the ‘Function Description’ section. Of special note is the ‘UDF_*’ functions, which are used to pull highly specific information from the worksheet and salary records.
The ‘Formula Editor’ is used to input the formula.
Save Formula will save your work. Check Syntax will check for errors in the formula. There is no need to reference ‘PCReplace’ in formulas; the system will automatically multiply the results of formulas by that field.
‘Param Variables’ lists all variables that have been exposed to the formula editor. This is primarily parameters from User Parameters.
‘Column Variables’ lists all columns that have been exposed to the formula editor. These are primarily used as parameters for the ‘UDF_*’ formulas.
- Setting up the Accumulator Ranges
Occasionally you will need to limit the range an accumulator runs on. The best example of this is vacation relief. The formula is very simple: [VB]; Without restricting the range further it will give you calculated vacation for every attached employee, but realistically only full-time employees are desired. This can be handled in the ‘CC: Ranges’ tab.
To begin with, create a new entry for each Item Source Parameter you want to limit. This will, by default, only list those codes entered into the Formula Editor.
After you have saved your entry, select the line and click ‘Modify Range’.
This will bring up the Range Builder.
Focusing on the Database Filter, add in filter entries until you have set things up to only pull data from the records you want. Hit ‘Save and Close’ when complete.
Step 3 – Add to Worksheet Profiles
- Start by entering Code Table Maintenance.
- Going through all of the listed profiles in ‘Worksheet Profile Master’, add the new worksheet item wherever necessary. If you are not adding them to any profiles you are done.
- Head into ‘Salary Budget Utilities’
- Choose ‘Add Worksheet Items From Profile’
- Make sure you select the correct Salary Budget Versions in the right hand column then hit ‘Add Worksheet Items From Profile’
