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.
You will be filling in the following columns:
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
The short code for the item.
The description you want to use for the item.
Step 2 – Create the Worksheet Item Detail Entry
You will be filling in the following columns:
Target Budget Version for the new 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.
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.
Determines where the item will show up in the Schedule tab.
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.
Only used in Shift Lines. Indicates how much paid time a day represents.
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.
Generally agrees with ‘Include FTE Hours’.
If necessary, you will be filling in the following columns:
The accumulator group the item code will contribute to. This should never be the same accumulator used in the item’s formula.
Entries to the ‘All Year’ column in the schedule tab will be multiplied by the factor entered here and then added to the Accumulator.
Entries to the ‘Day’ columns in the schedule tab will be multiplied by the factor entered here and then added to the Accumulator.
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.
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