Personnel Export & Import Files

When first entering information into Personnel, it is often easier to use the export and import process to load large amounts of data at once.

There are three types of imports within Personnel Budgeting:

• Pay Data File - This is the most comprehensive file and allows for importing of all information within Personnel except for Allocations. The best use of this file is for adding Employees and Positions.
• Pay Item File - This is the best file to import the compensation for the positions positions.
• Allocation File - This is the only way to import Allocations and is usually used when an organization has a lot of allocations.

Note: It is imperative that an organization utilize the export from within their system in order for the import process to work as expected. Do not try to create a file for import from the documentation in this section.

Import notes

• The Excel tab names should not be changed
• The Excel header row names should not be changed
• For multi-tabbed files, you can remove unneeded tabs and import only the tabs desired
• Imports only update or add information, they will never remove positions, employees, allocations or any other data within personnel
• All imports will be queued in the background, utilize the Dashboard > Updater page to ensure they have completed successfully

How to Use the Pay Data File

The Pay Data file allows for importing new data and adjusting existing data throughout the personnel module. The primary use for the Pay Data file is to import and update employees, positions, and dimension assignments for the positions. Attached is an example Pay Data File with instructions on each tab.  

The general process for updating a scenario is:

1. Make sure the scenario you want to work in is set as Selected.
2. Export the Pay Data file. Make changes to the Employees and Positions tabs.
3. Import the Pay Data file back into Martus.
4. Export the Pay Item Grid file. Make changes to the pay types columns.
5. Import the Pay Item Grid back into Martus.

Exporting the Pay Data File

1. Go to Personnel > Scenarios.
2. Ensure the appropriate scenario is selected as the Current Scenario.
3. Click Export Pay Data File.

Understanding the Pay Data File

The Pay Data File contains numerous tabs. Martus recommends ONLY utilizing the following three tabs for adjustment via the export and import process.

Employees tab:

• ID: Martus assigns this ID when Employees are added. Leave blank for new employees, or leave as is for updating existing employees.

• FirstName: The first name of the Employee.
• LastName: The last name of the Employee.
• Inactive: The status of the employee. Update to TRUE for any employee no longer active with the organization. Otherwise, active employees should be labeled here as FALSE.
• External ID: The ID that corresponds with the employee in the organization's payroll system. This allows for easier cross-referencing.
• Anniversary Date: The date the employee began working with the organization. This date can be used to calculate bonuses or salary increases based on the employee's anniversary.
• Coverage 1: The level of election within a pay type; most commonly used for medical insurance.
• Coverage 2: The level of election within a pay type; most commonly used for dental and vision insurance.

Notes for Import:

• The Coverage 1 and Coverage 2 values must match exactly (capitalization and spacing) to a value in the Coverage tab of the file.
• All columns must be included in the import, columns may be left blank, but do not remove the column header.

Positions tab:

• ID: Martus assigns this ID when Positions are added. Leave blank for new Positions, or leave as is for updating existing Positions.
• PositionName: Martus will assign this field using the Title of the Position when adding and adjusting position titles. Leave blank for new positions, or leave as is for updating existing positions. Note: Martus expects this to be a unique value within each Scenario. Martus appends a numeric value to the end based on the title. Example: CFO 1
• Title: The job title of the Position. If more than one position has the same Title, Martus will append a sequential numeric value in the PositionName upon import. Example: Five employees have the same title RN. You will list 'RN' for each one. When the file is imported into Martus, the Position Name (previous bullet point) will be unique: RN 1, RN 2, RN 3, RN 4, RN 5.
• Employee: The employee filling this position. This must be formatted with the exact values from the Employee sheet using [External ID] [FirstName] [LastName].
  • For new imports, this field can be filled in via the values on the Employee page utilizing the following formula. Note the values in this column must be copied and pasted as values (not the formula) before import.
     

    Formula to autofill Employee column from the Employee's sheet
    =CONCAT(Employees!E2," ",Employees!B2," ",Employees!C2)
    
    

• Type: The Position type is a custom identifier to help filter and sort the various data within a scenario. Ensure to copy values exactly as they appear on the Position Type tab.  
• IsTaxable: Is utilized for positions that have opted out of FICA taxes, this normally only applies to clergy. Set to False for those who have opted out.
• IsPool: Determines if this position is pooled or not. All pooled positions will multiply the FTE amount per each Pay Item assigned to this position. 
  • More information on Budgeting for Pooled Positions
• StartDate: The date in which to begin budgeting for this position. This is utilized for seasonal positions or mid-year hires. Leave blank for positions that need to be budgeted for all 12 months in the scenario.
• EndDate: The date in which to stop budgeting for this position. This is utilized for seasonal positions or mid-year position eliminations. Leave blank for positions that need to be budgeted for all 12 months in the scenario. 
• FullTimeEquivalent: This controls whether or not this employee is considered full-time. This number will apply to the Allocation Analysis tab and the FTE Analysis tab. It is also used in conjunction with the IsPool field to calculate pooled positions.
• SheetDim1, SheetDim2, LineDim2, etc: The dimensions associated with this position. These will be unique for each customer. To confirm which dimension is SheetDim1, SheetDim2, etc., navigate in Martus to Setup > Dimensions and note the order of Dimensions on the screen. SheetDim1 is first, SheetDim2 is second, etc. 
  • Note: Only add each position once. If a position will be allocated to multiple dimensions, those will be handled using the Allocation feature in Personnel.

Notes for Import:

• Ensure there are no formulas remaining on the page. A simple way to do this is to copy all of the data and paste it as values only. 
• Ensure that the Employee name is an exact match of the External ID, First, and Last name of the employees either on the Employee tab of the import or from what is in Martus. Example: 4215 Sam Powers
• Ensure that the combination of sheet dimensions used in the Positions tab exist in the Planner Budget as budget worksheets. To determine this, go to Planner > Planner Setup > Worksheet Management tab and search for the appropriate worksheets.

The PayItems tab

The best practice for updating Pay Items is to use the Pay Item Grid (separate file). The Pay Items tab within the Pay Data file should only be used when pay changes are needed mid-year that can not be accomplished via the Scenario's Detail tab. 

The PayItems tab contains all the pay items for each position. There is a separate line for each form of compensation for each position: for example, a line for Hourly pay, a line for Medical Insurance, and a line for Dental Insurance. 

To export the Pay Item tab with Monthly values

1. Go to Personnel Budgeting > Scenarios
2. Ensure the appropriate scenario is selected as the Current cenario
3. Select to 'Include Monthly Detail'
4. Click Export Pay Data File

The PayItems tab

• Id: This will be filled in when exporting, leave as is. 
• PositionName: This will be filled in when exporting, leave as is.
• PositionTitle: The title of the position, exactly as used in the Positions tab. 
• Employee: Must be exact to the way the Employee is displayed in the Positions tab. 
• PayTypeName: The name of the pay type to be added or updated. Must match exactly to what is in Martus
• Hours: For hourly pay types, the number of hours per pay period for this position is required. Otherwise, leave this blank. 
• Amount: The periodic rate or amount for this pay type. For hourly pay types, it is the hourly rate of pay. For regular pay types, it is the rate to be budgeted per the frequency of the pay type. For percentage pay types, this is usually set to zero (so that it will use the applicable default percentage), but it can be set to an employee-specific percentage.
• Months: All months in the fiscal year are listed and are filled in with the applicable monthly rate. 

• Best Practice: Use the Pay Item Grid (separate file) to update pay items for compensation. Exporting the Pay Data File to 'Include Monthly Detail' is useful when you need to assign different amounts by month to pay items on the position. This is not common.
  

Use the links below to navigate the learning path.

Position Types <<  Introduction to Personnel - Learning Path  >> Pay Item Grid

How to Use the Pay Item Grid

The Pay Item Grid is the recommended method to add and update pay items within a scenario. In this file, the data is formatted in one line per position/employee combination, with columns for each pay item. 

Exporting the Pay Item Grid File

1. Go to Personnel > Scenarios
2. Ensure the appropriate scenario is selected as the Current Scenario
3. Choose whether or not to include Starting Rates
4. Click Export Pay Data Grid

Updating the Pay Data Grid

The first three columns in this file serve as reference for the positions in the scenario, Martus only supports changes to the Hours and Pay Type columns, do not adjust the first three columns in this file and attempt to re-import.

• Position Name: This is for reference only, do not update.
• Title: This is for reference only, do not update.
• Employee: This is for reference only, do not update.
• Hours: Used with Hourly pay types only, leave blank for positions without hourly pay types such as salaried individuals.
• Pay Type Columns: The columns after the first four columns above will be dictated by the Pay Items that are set up within your system. Each customer will have a different set of columns. Navigating to Personnel > Pay Items will display the Pay Type frequency, type, and any default values. The following are general rules for utilizing these columns:
  • Hourly Pay Types: Enter the hourly rate for each position.
  • Salary Pay Types: Enter the rate per pay period as defined on the Pay Type in Martus (Ex. Weekly, Bi-weekly, Semi-monthly, etc).
  • Percentage Pay Items: Enter a 0 to utilize the default percent or enter the percentage that should be applied to that position if different from the default. Note: You can only overwrite the default percentage to be lower than the default. If the position does not get the pay item, leave the cell blank. 
  • Regular Pay Items: Enter a 0 to use the default amount set on the Pay Type in Martus or enter the appropriate amount based on the frequency of the Pay Type. If the position does not get the pay item, leave the cell blank. 
  • Tiered Pay Items: Pay types based on tiers use the default amounts by entering a 0 in the cell. If the position does not get the pay item, leave the cell blank. 

Import Guidelines

• Martus will add Pay Items or update Pay Items with the import file, it will not remove Pay Items even if they are removed from the file.
• Do not adjust or remove the Position Name, Title, Employee, or Hours columns from the file.
• Only enter a value or a 0 in cells where that corresponding position should have the Pay Type associated. If the position does not get the pay item, leave the cell blank. 

Importing to Martus

1. Go to Personnel > Scenarios
2. Ensure the appropriate scenario is selected as the Current Scenario
3. Choose the appropriate file
4. Click Import Pay Item Grid

Use the links below to navigate through the learning path.

Use the links below to navigate the learning path.

Pay Data File <<  Introduction to Personnel - Learning Path  >> Allocations

Accrue Budgeted Personnel Costs Based on the Number of Days in a Month

Background

A Martus client wishes to calculate the budgeted amount for compensation costs based on the number of calendar or working days in each month.  

Let’s start by discussing how pay item amounts are tracked in Martus.  

• Each pay item on a position has a "starting rate". This is either the amount that is specifically entered for this pay item on this position, or the default rate for that pay item.  You see this displayed on a monthly basis on the Detail and Summary screens within Personnel Budgeting.  
• When a rate update is applied, it updates a range of months within Personnel Budgeting.  
• For your accrual, we're going to use Excel to calculate monthly rates in a more complicated way, then update each month with the appropriate amounts.  To do this, we’ll import a Pay Data File containing only the Pay Items Page, with the monthly amounts set for each pay item that is to be accrued on a monthly basis.   

Method

The approach is to complete the personnel budget within Martus, then prepare a scenario that contains accrued amounts and post that to the Martus budget. If updates are needed later, it is recommended that changes be made in the scenario that is in the normal format, then another accrued scenario be created for posting to the Martus budget. Remember, it is important to calculate the accrual as a final step, immediately before applying the allocation and importing into the Martus budget.  

Here’s a step-by-step breakdown:  

1. Copy the finished scenario (on the Scenarios page) to a name like "Scenario with Accrual" and set that to Current. Note: every step below relates to this new scenario!
2. Export a Pay Data file. Be sure to use the dropdown to Include Monthly Detail.  From this Pay Data File, delete all the pages except the Pay Items page. From the Pay Items page, delete all the lines that you are not going to accrue.  
3. Use the exported Excel to calculate the correct accrued amount for each month based on the number of working days. Paste that information into the appropriate months.  
4. Import the completed Pay Data File (Pay Items page only) back into the scenario.  
5. Use the Detail page and "recalculate" all of the percentage-based pay items (so they will be calculated on the basis of the updated monthly amounts.  
6. Apply your allocations as usual before posting to the Martus budget.