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 NIS(JAS)
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 NIS(JAS)
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 JD
Edwards Solutions Explorer via JAS and follow this path:
General Accounting
General Accounting-Agencies
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 JD
Edwards Solutions Explorer via JAS and follow these menu choices:
General Accounting
General Accounting-Agencies
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 OK
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
Click on
“Form” and “Submitted Jobs” on the JAS Toolbar
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 JD
Edwards Solutions Explorer via JAS and follow these menu choices:
HR_PR
Human Resources & Payroll-Agencies
Inquiries & Reports
HR/Payroll Reports
Payroll Reports
Time and Pay History Detail
Click “Data
Selection”
Click Submit
On the “Date-Pay
Check”, in the drop down box for “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”
In the drop
down box for “Right Operand” choose “Literal”
Click the “
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
On the
display box 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
Stay in this
screen for the next step
How to get the information
out of NIS(JAS)
Click on
“Form” and “Submitted Jobs” on the JAS Toolbar
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 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