Reconciling the General Ledger to your Payroll Register 

 

This reconciliation process is to ensure that what is recorded correctly on the payroll register for each object code is what is posted into the accounting system.  If any variances are found, the agency will need to complete a journal entry to correct the object codes involved no matter what the dollar amount.  If you need assistance email state accounting at as.stateaccounting@nebraska.gov with the words G/L Payroll Register in the subject line.

You will need GL & HR_PR access for this process:

• Finding the Batch number of your Payroll in the General Ledger:
• How to run the General Journal by Batch Report:
  • How to get the information out of EnterpriseOne (Payroll and Financial Center)
  • How to retrieve the information for your review
  • How to sort/subtotal the information and make it user-friendly
• Comparing the Payroll Register to the General Journal by Batch Report
• How to run the Time & Pay History Detail Report
  • How to get the information out of EnterpriseOne (Payroll and Financial Center)
  • How to retrieve the information for your review
  • How to sort/subtotal the information and make it user-friendly

Finding the Batch number of your Payroll in the General Ledger:

Access EnterpriseOne (Payroll and Financial Center) and follow this path:

Accounting>> Inquiries & Reports >> Accounting Inquiries >> Account Ledger Inquiries >> Account Ledger Inquiry

• Open up the Account Ledger Inquiry
• Enter a Business Unit that you know had payroll this pay period
• Complete the Account Number by putting in ".511100"
• In the "From Date" put the date the paycheck was distributed
• Use the same date for the "Thru Date"
• Click Find
• This will bring up the payroll entry along with the Batch Number
• Write down this Batch Number on the top of your Payroll Register

How to run the General Journal by Batch Report:

Access EnterpriseOne (Payroll and Financial Center) and follow these menu choices:

Accounting >>Manage Journal Entry >> General Journal by Batch

• Click on "General Journal by Batch"
• Click on Version "NE999005"
• Click Select
• Click "Data Selection"
• Click Submit
• On line one, in the drop down box for "Right Operand" choose "Literal"
• Put in the 6 digit Batch Number for that payroll
• Click OK for the Data Selection
• Click the "Document Setup" tab
• Put a checkmark next to "CSV (Comma Delimited)"
• Click OK
• Stay in this screen for the next step

How to get the information out of EnterpriseOne

• Click on "View Job Status"
• Click "Find"
• Find the Report in your Printer Queue
• Repeat the "Find" as necessary until the job status changes to D (Done)
• Once it is done you will highlight the row
• Then go to "Row" and "View CSV"
• When the dialogue box pops up click "Save"
• Save it to a place you know you can get to it
  • Name the file and use the .txt extension
  • Change the "save as type" drop down box to "All Files"
  • Choose your location and click Save

How to retrieve the information for your review

• Open up a new Excel Spreadsheet
• Go to "File" on the toolbar and then "Open"
• On Dialogue Box change the "Files of Type" drop down box to "Text Files"
• Find and highlight the document you saved
• Click Open
• The “Text Import Wizard” box will open
• Make sure “Delimited” is selected
• Click “Next”
• Select the “Comma” box
• Click “Next”
• Click “Finish” and the Excel Spreadsheet will open
• Save the workbook with the file extension of .xls

How to sort/subtotal the information and make it user-friendly in Excel

• Go to the bottom of the information
• Highlight starting at the last “AA” (not AA Total) going all the way to the left
  • Also including the row that has the column headings
  • Ex. Batch Number, Do Ty and Fund
• Do not include any of the totals at the bottom
• Go to “Data” on the toolbar and then “Sort”
• In the dialogue box make sure the “Header Row” radial button is clicked
• Sort by “Account Description”
• Click OK
• Delete the row that contains Document Total (Row 9)
• Highlight column A to Q from row 8 and down of the information that contains “AA” in the “LT” column
• Go to “Data” on the toolbar and then “Subtotals”
• You may get a message that says it cannot determine which row contains column labels
• Click OK
• In the dialogue box select Account Description for the top drop down box
• In “Use function” choose “sum”
• Uncheck the checked box then make sure there are check marks in both boxes for “Credit Amount” and “Debit Amount”
• Click OK
• On the left side of the excel spreadsheet there will be a gray bar with Columns 1, 2 and 3
• Click the 2(this will summarize the information)
• These are the numbers that should tie out to your Payroll Register
• Save this file for documentation

Comparing the Payroll Register to the Report

• You will check the totals on the report to the totals on the Payroll Register by Object Account and Pay Type
• You can use the Pay Type/Object Account Relationship to help you compare (click on this link and Scroll down to Lesson 11 Step 4, Pay Type Descriptions) to help you compare.
• Please review the example to help you with your reconciliation (link)
• If the Report ties out to the Payroll Register you are finished
• Sign off, date and file
• If the total of an Object Account does not equal the total of the Pay Types that it has a relationship with write down the difference between the Payroll Register and the General Journal Batch Report and move on to the next item
• After you have recorded all the variances, you will need to run the Time & Pay History Detail Report to identify exactly where the variances occurred.

How to run the Time & Pay History Detail Report

Access EnterpriseOne (Payroll and Financial Center) and follow these menu choices:

Payroll>> Reports >> Time and Pay History Detail

• Check on NIS999001 Version
• Click Select
• Click on “Data Selection”
• Click Submit
• Left Operand should be “Date-Pay Check”
• Comparison should be “is equal to”
• Right Operand choose “Literal”
• Put in the actual pay date for that payroll
• Click OK
• On the next line of the Data Selection:
• Left Operand should be “Business Unit - Home (F0618) [BC]”
• Comparison should be “is equal to”
• Right Operand choose “Literal”
• Click the “Range of Values” tab
• In the “Literal Value From” put your agencies first valid Business Unit
• In the “Literal Value Thru” put your agencies last valid Business Unit
• If you have more than one payroll for your agency you will need to separate out either ranges of home business units or security business units that are connected with each payroll area or a list of home or security business units for each specific payroll area
• Click OK
• Click OK to accept the Data Selection
• Click OK on the next display box with a 1 in each box
• Click the “Document Setup” tab
• Put a checkmark next to “CSV (Comma Delimited)”
• Click OK

How to get the information out of EnterpriseOne

• Click on "View Job Status"
• Click “Find”
• Find the Report in your Printer Queue
• Repeat clicking on “Find” as necessary until the job status changes to D (Done)
• Once it is done you will highlight the row
• Then go to “Row” and “View CSV”
• When the dialogue box pops up click “Save”
• Save it to a place you know you can get to it
  •  Name the file and use the .txt extension
  • Change the “save as type” drop down box to “All Files”
  • Choose your location and click Save

How to retrieve the information for your review

• Open up a new Excel Spreadsheet
• Go to “File” on the toolbar and then “Open”
• On Dialogue Box change the “Files of Type” drop down box to “Text Files”
• Find and highlight the document you saved
• Click Open
• The “Text Import Wizard” box will open
• Make sure “Delimited” is selected
• Click “Next”
• Select the “Comma” box
• Click “Next”
• Click “Finish” and the Excel Spreadsheet will open
• Save this spreadsheet with the file extension of .xls

NOTE: When you open the Time and Pay History Detail report the numbers in the "Hours" column are actually the "Pay Types", but the totals at the bottom are the totals of the "Hours"

How to sort/subtotal the information and make it user-friendly in Excel

• Go to the bottom of the information
• Highlight the Account numbers that should be in Column F, and click 4 times on the Increase Decimal Button on the toolbar which looks like this:
  This will allow you to see the whole account number
• You may need to widen the column to see the whole number
• Highlight starting at the last dollar amount for a person going all the way to the left
• Then up to the row that has the column headings
• Ex. Work Date, Employee Number and Employee Name
• Do not include any of the totals at the bottom
• Go to “Data” on the toolbar and then “Sort”
• Make sure the “Header Row” radial button is clicked
• Sort first by “Pay Type”, then by “Account Number” then by “Employee Number”
• Subtotal by Pay Type (follow instructions for subtotaling above)
• If your variance was for Pay Type 1 (Regular Pay, Object code 511100), you will need to reconcile each employee listed with a Pay Type 1 to the Payroll Register Report (or go back to their time sheets) and determine where the mistake is. 
• Once you find out which object accounts are over and short you will need to contact State Accounting through the mail-in database with this email address: State_Accounting@das.ne.gov and the subject heading “GL/Payroll Register”
  • Things to include:
  • The summary page of your final payroll register (last page usually)
  • Export(s) of reports you used to compare to your payroll
    • Or, Job Number in the Printer Queue if not already deleted
  • Summary of what you found with Over/Short Object Accounts