The Official Website of the Massachusetts Teachers' Retirement System (MTRS)

Form with pencil
Search feature
Home
Calendar
Download forms
Publications
Legislative news
Calculators
Sign up for MTRS Mail
Useful links
Site map
Help
Site policies
 



 


   

MTRS logo

News Members Employers About the MTRS Contact us  
Home > Employers > Payroll reporting: Basic Electronic Reporting Tool (BERT) >
BERT 2.1 User Guide
>
Part 6: Appendixes
 

BERT 2.1 User Guide

PART 6
Appendixes

6.1 Ten steps to BERT success
6.2 BERT 2.1 error messages, definitions and what to do
6.3 Tips for using
Microsoft Excel to view and analyze your BERT data


6.1 Ten steps to BERT success

Below is a ten-step reference guide that will quickly lead you through testing and correcting a monthly MTRS deduction report and be in compliance with MTRS reporting standards.

  1. Locate the MTRS deduction report file you would normally send to the MTRS.
  2. Make sure it is a text file. Text files are recognized by the “*.txt” file extension in the file name. If it is not a text file, right click on the file, and rename it “reportmonth.txt”. Make sure there is only one “.txt” in the file name.
  3. Open BERT 2.1 and click on the “File Changes” button, then select “Browse” and locate the text file.
  4. Once the file is selected, click “Import MTRS File” and then “OK,” and then “OK” again to confirm that the file has been imported. Then click “Exit to Main Page.” Your new file should appear on the Main screen. Check the Pay date column on the left to be sure that you have imported the correct file. For more details on importing files, see section 4.4.
  5. Check the “File Total” on the bottom right of the Main screen and verify that it matches your report payment total. For more information on the Main screen, see section 4.1.
  6. Test your file by clicking the “Export Error Report” button on the Main screen and saving the error report to your desktop (for example, as “Aug2009BERTerrorReport.xls”). The error report will be in Microsoft Excel format; for tips on how to use Excel to sort or manage your error report, see section 6.3.
  7. Open the error report that you just saved and if there are no errors,
    your original file can be sent to the MTRS. If you have errors, fix them either
    in BERT or in your system (sometimes it’s more efficient to delete the
    BERT file, fix the errors in your system and import them into BERT all
    over again). For an explanation of the error messages, see section 6.2.
  8. If you have changed the file in BERT, repeat step 6 and step 7.
  9. If you have changed anything in BERT, export your file from BERT by clicking “Export MTRS file.” Then save two copies: one to send to the MTRS and a corrected backup copy for your files. Please add either “Fixed in BERT” or a “c” to the name of the file you save for your records so you know it has already been corrected. Note: if you saved your original file to a CD, you probably will need a new CD because you can only save a file on a CD-R once.
  10. Exit BERT from the main screen.

 

6.2 BERT 2.1 error messages, definitions and what to do


Error message: 2% ded. is calculated wrong or record has bad data
What it means:
BERT has calculated the 2% figure according to the data in your record and the calculated amount does not match your deduction.
What to do:
Please make sure all the contract data in the “Edit Record Page” is correct. If all of your contract data is correct, verify that your payroll software is calculating the 2% deduction properly. If it isn’t, get it corrected.
Please note: Once the 2% is being taken properly, please contact your Employer Services Representative and arrange to make an adjustment for the next pay period through your payroll system to correct the error. This correction should be reported as an adjustment record.

Error message: 2% deduction invalid
What it means:
The value in the 2% field is either blank or non-numeric.
What to do:
Check the 2% deduction and change the blank or non-numeric value in BERT to the proper 2%. Please enter 0.00 if no 2% deduction was taken.

Error message: 2% deduction is missing
What it means:

This error will appear for any 8% or 9% member who is listed as full-time with a salary of more than $30,000 but did not have a 2% deduction taken.

What to do:

Check this member’s record to make sure the proper rate is really 8% or 9% and that the member earns enough to require a 2% deduction. If this member is not having 2% deducted in error, please start taking the 2% deduction in the next payroll cycle.
Please note: Once the 2% is being taken properly, please contact your Employer Services Representative and arrange to make an adjustment for the next pay period through your payroll system to correct the error. This correction should be reported as an adjustment record.


Error message: 2% deduction is negative
What it means:

BERT has detected a negative deduction in the 2% field.

What to do:

Look at the record to see why the amount is negative. All corrections should be submitted in adjustment records. If this negative amount is a result of an adjustment, please make sure you are reporting it as an adjustment record. If this record is reported as a normal record but should be an adjustment, change the record type from N to A at the top of the Edit Record Page.


Error message: 2% deduction taken in error
What it means:

BERT has detected a record with a member rate code of 05 or 11 that had a 2% deduction taken.

What to do:

Please verify that the member’s rate code is correct and if this is an actual error, stop taking 2% deductions in your next payroll cycle.
Please note: After the erroneous 2% deduction has been stopped, please contact your Employer Services Representative and arrange to make an adjustment for the next pay period through your payroll system to correct the error. This correction should be reported as an adjustment record.


Error message: 50% full-time record detected, check annual salary
What it means:

BERT has detected a member who is listed as a part-time member at 50%.

What to do:

Please ensure that the full-time equivalent annual salary and the rest of the contract data is correct.


Error message: Adjustment record detected
What it means:

BERT has detected an adjustment record in the file that you have sent to fix a previous error.

What to do:

Make sure this adjustment record is valid and that the MTRS receives an explanation for the adjustment.


Error message: Agency code is invalid
What it means:

The four-digit agency code (example: 0100) is missing.

What to do:

Contact your payroll vendor and ask why the agency code is not showing up on the report. If you do not have a payroll vendor, please update your payroll system or manually change the records in BERT.


Error message: Annual salary is invalid
What it means:

The annual salary cannot be blank, zero, or negative.

What to do:

Please update the annual salary in your payroll system, and make the correction in BERT. Be sure to enter the annual salary as the full-time equivalent salary for members starting mid-year and part-time employees.


Error message: Base earnings do not match annual salary
What it means:

BERT has computed the base earnings using the annual salary and full-time percentage and the calculated figures don’t match your figures.

What to do:

Make sure the annual salary and full-time percentage are reported correctly. If they are not, correct them in your payroll system and either generate a new text file from your payroll system or make the correction in BERT.


Error message: City field is blank
What it means:

The city field cannot be blank for any records.

What to do:

Update the city field in your payroll system. If you are not generating a new text file from your payroll system, also make the correction in BERT.


Error message: Contract term is invalid
What it means:

The value being reported is either blank, zero, or not a valid selection from the dropdown menu.

What to do:

Determine what the correct contract term is (number of months the employee is contractually required to work) and correct it in your payroll system. If you are not generating a new text file from your payroll system, also make the correction in BERT.


Error message: Deduction does not match eligible earnings
What it means:

BERT has calculated your employee’s regular deduction using the reported earnings and rate code in the record and it does not match the deduction you are reporting.

What to do:

Look at the member’s record and make sure all the earnings and deductions match your payroll system. If the earnings or deduction data doesn’t match your system, correct the errors and save your changes. If the amounts all match what is in your payroll system and this error is a real problem, please inform your Employer Services Representative.
Note: Once the cause of this error has been fixed, please contact your Employer Services Representative and arrange to make an adjustment for the next pay period through your payroll system to correct the error. This correction should be reported as an adjustment record.


Error message: Deduction without eligible earnings
What it means:

A deduction has been taken; however, no eligible earnings are listed on the report.

What to do:

Research your payroll system and, if eligible earnings are missing, add them to the record. If there are no eligible earnings because the deduction in the record was not taken from eligible compensation, please inform your Employer Services Representative.
Please note: Once the cause of this error has been fixed, contact your Employer Services Representative and arrange to make an adjustment for the next pay period through your payroll system to correct the error. This correction should be reported as an adjustment record.


Error message: Eligible coach earnings must be in coaching field
What it means:

The coach earnings are being reported in a field other than the coach earnings field (most likely the base earnings field).

What to do:

In BERT, move all coach earnings to the coaching field. Also, make sure to code the earnings correctly, so they appear in the correct field in future reports.


Error message: Eligible earnings without a deduction
What it means:

Earnings are listed in the payroll record; however, it is showing no regular retirement deduction was taken.

What to do:

If this member should have had retirement contributions deducted and did not due to an error, contact your Employer Services Representative and discuss how to handle the correction. If this record pertains to someone who should not be making retirement contributions, such as a retired or substitute teacher, delete the record from the BERT file.


Error message: FTE% of less than 50% may affect Member Eligibility for the MTRS
What it means:

This message is intended as an alert that the employee might not be eligible for MTRS membership.

What to do:

Determine whether the employee is working at another school district at the same time. If the combined FTE% between the school districts adds up to at least 50%, he or she is eligible and therefore leave the report as it is. If the person is found to be not eligible, contact your Employer Services Representative.


Error message: Full-time percentage is not valid
What it means:

The full-time percentage is either blank, zero, or above 100%.

What to do:

Correct the full-time percentage in your payroll system and if you are not generating a new text file from your payroll system, also make the correction in BERT.


Error message: Name field is blank
What it means:

There is no name listed on the deduction report record.

What to do:

Update your payroll system and if you are not generating a new text file from your payroll system, also make the correction in BERT.


Error message: Name field must have one comma
What it means:

The information that you have listed in the name field is not in the proper format or is missing required information. The proper name format can be found in the MTRS Earnings and Contribution Report File Layout.

What to do:

Correct the manner in which you are reporting this name and either add or remove a comma as needed.


Error message: Negative eligible earnings
What it means:

BERT has detected a negative value in an eligible earnings field.

What to do:

Examine the record in BERT and determine why the earnings are negative. All corrections should be submitted as adjustment records. If this negative amount is due to an adjustment, please make sure you are reporting it as an adjustment record. If this record is reported as a normal record but should be an adjustment, change the record type from N to A at the top of the Edit Record Page.


Error message: Pay date and period date do not match
What it means:

The pay date and period being reported have different months. Example: pay date is 3/30/2009, and the period date is 200904 (April).

What to do:

Check your payroll system to see why the dates did not match. If this is a payroll software error, have your software corrected. This can be fixed in BERT on the individual member’s Edit Record page, or globally, by clicking on the File Changes screen and changing the pay date, which will automatically fix the period date. If this error message appears because this record is being reported as a normal record but should be an adjustment or retro record, please change the record type from N to A or R at the top of the Edit Record Page.


Error message: Pay duration and pay frequency are not compatible
What it means:

The pay duration and pay frequency are not a logical combination.
Example of error:
Pay frequency = 21 (bi-weekly over 10 months)
Pay duration = 12 (12 months)

What to do:

Make the necessary changes in your payroll system and if you are not generating a new text file from your payroll system, also make the correction in BERT.


Error message: Pay duration cannot be shorter than the contract term
What it means:

It is not logical for someone to work longer than the period of time in which they are being paid.
Example of error:
Contract term = 12
Pay duration = 10

What to do:

A teacher’s contract term is usually 10 (working September through June) and an administrator’s contract term is usually 12 (working the entire year). Review this employee’s contract data in BERT and make the necessary changes in your payroll system. If you are not generating a new text file from your payroll system, also make the correction in BERT.


Error message: Pay duration is invalid
What it means:

The pay duration is either blank, zero, or not listed under the valid pay duration dropdown menu.

What to do:

Correct the pay duration, both in your payroll system and in BERT. For a list of valid pay durations, click on the dropdown menu on the Edit Record page.


Error message: Pay frequency is invalid
What it means:

The pay frequency is either blank, zero, or not listed under the valid pay frequency dropdown menu.

What to do:

Correct the pay duration, both in your payroll system and in BERT. For a list of valid pay frequencies, click on the dropdown menu on the edit record page.


Error message: Pay frequency is only valid for coaches
What it means:

Pay frequencies of 01, 02, 03, and 06 are only valid for coaches.

What to do:

If the person is a coach, change the position code to COACH in BERT. If not, correct the pay frequency in your payroll system and in BERT.


Error message: Pay period is not valid
What it means:

The pay period is either blank or in the wrong format. The correct format is yyyymm; for example, March 2009 should be 200903.

What to do:

Contact your payroll vendor and find out why the pay period is not being populated correctly. Make the necessary changes in your payroll system and if you are not generating a new text file from your payroll system, also make the correction in BERT.


Error message: Please verify data for hourly employee
What it means:

A record for an hourly employee is on the deduction report.

What to do:

Look at the record on the Edit Record Page and make sure all information is being reported correctly.


Error message: Position code is invalid
What it means:

The position code is either blank or has been entered incorrectly. The position code must be in caps and spelled correctly.

What to do:

Correct the position code in your payroll system as well as BERT. A list of valid position codes can be found by clicking on the position code dropdown menu in BERT or in the MTRS Earnings and Contribution Report File Layout.


Error message: Rate code is invalid
What it means:

The rate code is blank or not a valid code.

What to do:

Determine the member’s correct contribution rate and update both your payroll system and BERT. In BERT, there is a dropdown with the valid entries: 05, 07, 08, 09, and 11.


Error message: Record has no earnings or deduction
What it means:

BERT has detected a record on the payroll report with all earnings and deductions reported as $0.00. This error commonly appears in July and August for employees who received a lump-sum payment.

What to do:

If you have verified that is this record should not be on the MTRS report, click on the “File Changes” button, and then click on the “Remove Blank Records” button. This will remove all the blank records, thus dealing with the condition that caused the error message. If this member should have had deductions, contact your Employer Services Representative.


Error message: Regular deduction is invalid
What it means:

The deduction field is blank and is showing no value.

What to do:

Check your payroll records to determine what exactly was deducted. If nothing was deducted, change the deduction amount to zero in BERT. Contact your Employer Services Representative and discuss how to handle the correction.


Error message: Regular deduction is negative
What it means:

BERT has detected a negative regular deduction.

What to do:

Examine the record and determine why the amount is negative. All corrections should be in adjustment records. If this negative amount is due to an adjustment, please make sure you are reporting it in an adjustment record. If this record is reported as a normal record but should be an adjustment, change the record type from N to A at the top of the Edit Record Page.


Error message: Salary less than $20,000
What it means:

There is a possibility the salary has been entered incorrectly, or the employee might not be eligible for MTRS membership. If this person is a coach, please change the position code to COACH.

What to do:

Check BERT to see if the salary listed is correct. If not, make the necessary changes. If this employee is eligible for MTRS membership and the full-time equivalent salary is really less than $20,000, or if you determine that this employee is not really eligible for membership, contact your Employer Services Representative.


Error message: SSN is invalid
What it means:

The Social Security number is either blank, all zeroes, or is an unlikely number, for example: 123-45-6789, or 111-11-1111.

What to do:

Make the correction in your payroll system, and the necessary change in BERT.


Error message: State field is invalid
What it means:

The state abbreviation listed in the record is not one of the 50 states, or has been entered incorrectly.

What to do:

Please make the correction in your payroll system and the necessary change in BERT.


Error message: Street address is blank
What it means:

The address listed in your payroll record is blank.

What to do:

If needed, add an address to your payroll system and if you are not generating a new text file from your payroll system, also make the correction in BERT.


Error message: Transaction date is invalid
What it means:

The transaction date is either blank or all zeroes.

What to do:

This date should match the pay date of the transaction that you are reporting. Make the necessary changes in your payroll system and if you are not generating a new text file from your payroll system, also make the correction in BERT.


Error message: Transaction type is invalid
What it means:

There can only be three types of transactions: N for Normal records, A for Adjustment records, and R for Retro records.

What to do:

Make the necessary changes in your payroll system and if you are not generating a new text file from your payroll system, open the Edit Record page in BERT and find out what transaction type is listed in the upper left hand corner. Choose the appropriate record type from the dropdown menu.


Error message: Unauthorized installment payment received
What it means:

BERT has detected an installment payment. Installment payments are not currently allowed by the MTRS.

What to do:

If there is money being reported in the installment field, it is most likely due to an adjustment or retro payment. If the amount in the installment field is an adjustment, please report it in a separate adjustment record. If it is a retro payment, please: either create a separate retro record; or, add the amount of deductions you’ve reported in the “installment payment” field to the regular deductions (and 2% deduction if necessary), and list the associated earnings in the retro earnings field.


Error message: Zip code is invalid
What it means:

The zip code is blank, all zeroes, or less than five digits.

What to do:

Make the correction in your payroll system and the necessary change in BERT.


 

6.3 Tips for using Microsoft Excel to view and analyze your BERT data

BERT has three functions that allow you to export data into Excel, where it can be used for various purposes. The most commonly used Excel export is the Export Error Report function so this section of the guide will walk you through a few tips to help you process your error report.

Microsoft Excel offers a variety of features that enable a user to do things like sort, format and find data. Before we cover some of those features, we thought it useful to review a few key terms.

Excel 101
Excel files are called either spreadsheets or workbooks. When you open an Excel workbook you default to looking at a worksheet. Each worksheet has a tab at the bottom of the page. Each square in a worksheet is called a cell and the cells are organized as rows and columns. A row is a set of cells on the same horizontal plane and each row is numbered on the left hand side of the worksheet. Columns are a set of vertically aligned rows. Each column of a worksheet is labeled with a letter. Every cell in a worksheet is named according to the corresponding column and row that it lives in. For example, the first cell in every worksheet is named cell A1 because it is in column A and Row 1.

A note about Excel 2007
A fairly recent release, Microsoft Excel 2007 has all of the features described below, but they may not be located in the same menus and toolbars where they were in previous versions of Excel. If you are using Excel 2007 and have difficulty finding a function, please either check Microsoft’s website or contact your Employer Services Representative for assistance. Below is an illustration of the new “look and feel” of Microsoft Excel.

New look and feel of Excel

This section will offer tips to help you with the following functions in Excel:

Widening your columns
When you first open an error report, you may find the data a little hard to read because the columns are not wide enough. To widen the columns follow these two steps:

  1. Select all of the cells by clicking on the square in the top left-hand corner of the worksheet between the “A” and “1” labels or by hitting control+A on the keyboard.
    Excel – Sample error report window

  2. Once all of the cells are selected, simply click on the line between column label A and column label B (circled below) and Excel will resize all of your columns based on the widest value in each column. Your reformatted worksheet should now look like this:
    Excel – Sample error report window

Sorting your data
Organizing your data can be a key step in analyzing and processing your report. To sort your data in a specific manner, follow these three steps:

  1. Select all of the cells by clicking on the square in the top left-hand corner of the worksheet between the “A” and “1” labels or by hitting control+A on the keyboard.
    Excel – Sample error report window

  2. Next, if you are using a pre-2007 version of Excel, go to Data in the top menu and choose Sort (see below). If you are using Excel 2007, go to Options in the menu and choose Sort.
    Excel – Sample error report window

  3. You can sort data in a worksheet by one column or several columns in the order of your choice. You can also choose to sort any list either in ascending (low to high) or descending (high to low) order.

    Note
    Make sure you select the header row option to keep your column names at the top of the list.


    Sort menu window

Formatting your cells
Information stored in Microsoft Excel can be displayed in a variety of formats, and you can use the formatting of this to make it easier to read and process your data. To set the format for any cell or group of cells in Excel, follow these three steps:

  1. Begin by highlighting the cell or cells that you would like to format.
    Excel – Sample error report window

    Tip: You can highlight a whole column or row in a worksheet by clicking on that column or row’s corresponding letter or number label.

  2. Next, if you are using a pre-2007 version of Excel, go to Format in the menu bar and choose Cells (see below). If you are using Excel 2007, go to Options in menu bar and choose Cells.
    Excel – Sample error report window

  3. Once you are in the Format menu you will have several options to choose from, such as setting the data in your cells to be right justified or formatting a column to hold a specific data type such as dates, text etc.
    Format menu window

Finding specific data or a specific record
If you are dealing with a large spreadsheet, you may want to find a specific cell or value within a particular record (row) without having to scroll back and forth. You can use the Find function to search the entire sheet, or you can narrow the search by highlighting a specific column or row before searching.

To use the Find function to locate data:

  1. Start your search by choosing whether to search the entire sheet or a selected section. To search the entire sheet, select all of the cells by clicking on the square in the top left-hand corner of the worksheet between the “A” and “1” labels or by hitting control+A on the keyboard. Search a column or row by clicking on the column letter or row number. Below is an example of highlighting a column to search through.
    Excel – Sample error report window

  2. To find data in your selected range of cells, access the Find menu by either hitting control+F or by going to Edit from the top menu and selecting “Find.”
    Excel – Sample error report window

  3. When the Find box appears, enter any string of characters that you would like to locate in your selected cells and choose Find Next. You can also replace data with this function by choosing the replace tab and typing the replacement data in the Replace With box.
    Find and Replace window

  4. Excel will either highlight the first positive match and allow you to continue searching or display a message that “your requested data could not be found.”
    Excel – Sample error report window

For complete information on using Microsoft Excel and all of the application’s various options, visit Microsoft’s website.


Go to BERT 2.1 User Guide