How to Use the Pay Data File

Modified on Tue, Sep 17 at 9:18 AM

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. 
  • 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. 
  • FullTimeEquivalentThis 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

Was this article helpful?

That’s Great!

Thank you for your feedback

Sorry! We couldn't be helpful

Thank you for your feedback

Let us know how can we improve this article!

Select at least one of the reasons
CAPTCHA verification is required.

Feedback sent

We appreciate your effort and will try to fix the article