Finance Reconciliations

General Ledger

Check Fund Balancing

The report will list each fund with the total debits, credits, and balance for each. Each fund should have a zero net balance. If there are any funds that do not net to zero, some possibilities include:

• Transactions are still being posted to the period being reconciled. Since this report takes some time to produce, it is possible that a set of transactions started posting after the report was started and finished before the report ended. This could result in only part of the transactions being included on the report. Re-run the process when no transactions are being posted to the period or adjust the period end date to not include active posting dates.
• An accumulator record was deleted. Many transaction reports use accumulators to build an index of transactions to look for. If an accumulator has been deleted the report won't know to look for transactions posted to it. If a recompute has not been run recently, run one before proceeding.
• A posting process was cancelled or aborted before completion. The Jobs Monitor allows for any job to be cancelled. If a job is being run and the system goes down or the process is otherwise interrupted (a scheduled job is interrupted by a system backup or restart), the job will probably get aborted. To find these jobs, run an Account Transactions report (GLREFLTR), select the fund(s) that the report showed were out of balance, select the exceptions only format, sort and total by job number. This will provide a report of job numbers that are out of balance. Additional inquiry should be made to determine why, and how to correct the problem.

Pooled Cash

If the district uses Pooled Cash, run the following report:

Across all funds, the total of all Interfunds should be zero (the pooled cash fund also has an interfund which offsets the total of all other Interfund objects).

If the report shows a balance other than zero, run an Account Transactions report (GLREFLTR), sorted and totaled by JOB Number, selecting on the INTERFUND OBJECT CODE and transaction dates of FYB through MOE (entered as: 07012008-033109), using the Exceptions Only Report Format. Each JOB or Set ID should total zero, and the report will show exceptions where this is not the case. Solutions for out of balance situations will vary depending on the original cause of the problem. It is best to use the original date of the out of balance transaction if possible.

Bank Management

Reconciling Bank Reconciliation (BK) to General Ledger (GL)

Run a General Ledger Detailed Trial Balance (GLRESR01) to get the total cash postings for the period.

Run a General Ledger Detailed Trial Balance again, selecting reversals posted during the period. The check register shows a total for reversals, but some of those reversals may have taken place after the period end we are reconciling. We need to obtain the total reversals that posted during the reconciliation period. In this example, reversals are posted with a set ID beginning with "RV." Each district could be different, so selection criteria may need to be adjusted to get this total. If APTRBTDS is only used to post reversals, selecting by Subsystem ID "TR" would be a way to obtain the total.

Compare these reports. The Grand Totals should match. If there are inconsistencies, rerun the reports using check run dates as periods to be reconciled. Then reconcile each check run.

Bank Statement to the General Ledger

While not required, it is easier if each bank account to be reconciled has its own General Ledger Cash Account (Key-Object combination).

• Update Bank Statement Information (BKUPBA) and run the bank reconciliation module (BKUPRC). This will update checks and deposits that match what the bank has cleared during the period.

• Posting Cash Receipts to the General Ledger in the same manner as deposits are made makes the reconciling process easier and will tie to the BK module as well. Obtain the total deposits per general ledger.

This report provides a list of deposits to be compared to the bank statement. If there are inconsistencies, the user should consider the following:

• Are deposits recorded from any other subsystem (i.e., Journal Entries)?
• Have all deposits been entered for the statement period?
• If Cash Receipts are posted in such a way that the total posting to the General Ledger Bank Object does not match up with deposit amounts, it will be necessary to identify the GL field that is unique to each deposit. Run GLREFLTR using the same selection criteria as in 2 above but use the special sorting format and sort and total by that field.

These reports are used to ensure that the organization's cash balance agrees with the bank's balance.

• OC — Outstanding Check List - The outstanding check list should be run to include all outstanding checks as of the ending date of the bank reconciliation period (CDD BK1020/BK1021). Enter the Total from this report on the bank reconciliation schedule.
• OD — Enter any deposits from the report generated in b, above, that have not cleared the bank, on the bank reconciliation schedule as Deposits-In-Transit. CDD BK1004 – Outstanding Deposits – may also be used.
• GL — Run the General Ledger Report (GLRESR02), selecting on bank account object, date range Fiscal Year Beginning (FYB) through Month Ending (MOE). Enter this amount on the bank reconciliation schedule as the Balance Per GL/Books.

GL-OD+OC = Ending Bank Statement Balance (EBSB) or EBSB+OD-OC = GL

Accounts Payable Open Hold

Accounts Payable Open Hold to Bank Reconciliation

Reconciling checks that were written and recorded in the Bank Reconciliation (BK) subsystem to the paid items recorded in the AP Open Hold subsystem ensures that all invoices marked as paid (PD) in Open Hold are supported by a check in BK. For this reconciliation process to take place, all non-payroll checks must be distributed to the Open Hold database in addition to the General Ledger. For example, all Immediate Pay and Hand Written checks must be distributed to the Open Hold database.

A. Obtain totals from Bank Reconciliation (BK).

B. Obtain totals from Open Hold (OH).

C. Obtain a report of reversed amounts from Open Hold.

D. Compare these reports (A minus B minus C). Inconsistencies could be due to one of the following:

• Immediate Pay or Hand Written transactions might not have been posted to Open Hold.
• Check History is often kept on-line longer than Accounts Payable history. If the attempt is to reconcile for past periods, this might be the problem.
• Re-run these reports selecting a smaller check date range until the period with differences can be isolated and the transactions identified.

Accounts Payable Open Hold to the General Ledger Control Account

The purpose of reconciling Unpaid Invoices to the General Ledger is to ensure that all entries to the General Ledger Accounts Payable account are supported by a record within the Open Hold Data Base. PowerSchool recommends reviewing the reports generated during each distribution and check run to avoid potential problems.

First Pass

This report will list outstanding Accounts Payable by fund per the general ledger These totals should also be the same as those if run for the current fiscal year only..
Obtain a report of Unpaid Invoices:

The totals of these reports should match. If they don't, go on to SECOND PASS.

Second Pass

Obtain a report from the general ledger of AP balances by Set ID for funds that didn't reconcile.

Obtain a report from Open Hold of unpaid invoices by Set ID for the funds that didn't reconcile.

Identify which Set IDs do not match between the two systems. It will now be necessary to do additional reporting on these sets to determine what corrective action is required. A Journal Entry may need to be created to correct this and consider posting to the original date.

Accounts Payable Open Hold to the General Ledger Reconciliation Report

The Accounts Payable subsystem has a reconciliation report designed to support the reconciliation process. To access the reconciliation report from the main menu, select APOHRERG. This reconciliation report can be used to point out areas where Accounts Payable and the General Ledger may not be reconciling. It is important to note that this report runs by Job Number and even if an item were fixed, it will look like a problem still exists with the report. Also, credit memos entered in Accounts Payable will distort this report. Again, this report is designed to be a tool in pointing out potential problem areas.

Trouble Shooting Tip: The General Ledger Report GLUTDIVC can be run to ensure that all check numbers which exist in Open Hold exist in the General Ledger. The running of this report will accomplish two objectives. First, it will verify that all check numbers in Open Hold exist in the General Ledger. The corresponding General Ledger transactions will be corrected based on the transactions in the Open Hold transaction detail. Second, this report will tell if transactions exist in Open Hold but not in the General ledger.

Note that this utility only corrects check numbers in GL if transaction level ("T" Level) posting occurs at "P" date (GLUTSPSI) or on a Modified Accrual basis. Transactions posted at "C" date or "R" date will not be corrected. Transactions posted at other than "T" level will not be updated. These might include self-insurance check where payee names in the general ledger would not be appropriate.

Accounts Payable Open Hold to General Ledger Transactions

Obtain a report of GL transactions posted from Accounts Payable.

Obtain a report of OH transactions posted to general ledger.

The amount we will use is the Total + Reversed, because we don't know when the reversals took place. The next report will tell us the adjustment to make.

Obtain a report of transactions posted to GL from Accounts Payable and reversed in a future period.

Compare these reports as follows:

If there are differences, identify the funds that have differences and then re-run the reports for the funds with difference and change the sort/total from Fund to Set ID. This will help identify which sets have problems, so the user can get closer to the issue that is causing the problem.

Accounts Receivable

The methods used to reconcile the Accounts Receivable subsystem to the General Ledger depend greatly on whether there is a relationship between the AR Division Codes and the General Ledger A/R Control Account(s). The following steps assume that there is either a one to one or many to one relationship between Division Codes and the GL A/R Control Account. An overall assumption is made that reconciliation will occur at the end of a given accounting period and therefore the selection criteria will include a date range.

Accounts Receivable to the General Ledger Long Format

First Pass 

Obtain a report of GL transactions posted to the AR control account.

Obtain a report of AR transactions posted to the general ledger.

If these two reports reconcile, then reconciliation is complete. IF NOT, then run the following reports.

Second Pass

Where a many-to-one relationship between the Accounts Receivable Division Codes and a single General Ledger Accounts Receivable Control Account exists, then the user may wish to include the Division Code in all selection criteria for Accounts Receivable reports and request a separate report for each General Ledger Control Account.

Encumbrances

Encumbrances originate from several modules, including from within the encumbrance module itself. The Purchasing and Stores Inventory modules have utilities to help identify potential issues. Payroll encumbrances are created based on remaining payrolls between the last payroll run and the end of the year. Purchasing and Stores Inventory encumbrances are created when orders are entered. Payroll encumbrances are created from payroll utilities and stored at a detail level in payroll tables. Another process is then run to update the Encumbrance module from the payroll table.

Purchase Orders (PO) to Encumbrances (EN)

To ensure that all outstanding Purchase Orders are encumbered correctly, run Reconciliation with EN report (PORERP). This will check open Purchase Orders to ensure their balances are correctly recorded in EN.

Stores Inventory (SI) to Encumbrances (EN)

To ensure that all warehouse orders are recorded correctly, run Reconciliation with EN report (SIRESRRP). This will check open orders in SI to ensure their unfilled balances are correctly recorded in EN.

Accounts Payable to Encumbrances

The main reason to perform this reconciliation is to verify that Purchase Orders are being recorded and relieved correctly.

Obtain a report of payment records in the Encumbrance module that came from Accounts Payable.

Obtain a report of payment records in the Accounts Payable module that were made against purchase orders.

This report does not include invoices that were posted during the reconciliation range but reversed in a future period. To get this list we will need to run this report again but selecting the reversal date range.

The Disencumbered amount on the Encumbrance report should agree with the sum of the two "Net Amt" totals on the Open Hold reports. A comparison, vendor by vendor, may not match. This may be the result of a Purchase Order being issued to one vendor but paid to another. Other inconsistencies might occur if the district does not post all accounts payable transactions to Open Hold.

Stores Inventory

Stores Inventory to General Ledger

Stores Inventory posts expenses to the General Ledger. It can also create encumbrance and disencumbrance records and update an Inventory asset account. These can all create reconciliation needs.

Compare these reports. Where differences are identified, run these reports again, selecting the fund that does not agree and add Job Number or PEID to the sort on each report. This will make it possible to isolate the difference to a smaller group until the report can be run at a transaction level to identify the specific transaction difference(s) and corrective action, if necessary.

Stores Inventory to Encumbrances

This report will show all encumbered orders that are still open and where the amounts don't agree with the Order Amount in Stores. Correct these entries as needed.

Payroll

Reconciling entries between Payroll and General Ledger and reconciling the status of payroll vendor liabilities can be complicated. The easiest way to ensure a minimum of problems is to check the summary totals for each Register at the time payroll is run. These totals should tie to the summary totals on the General Ledger posting report. It is important to remember that some items in Payroll might not be posted to the General Ledger (i.e., taxable benefits - insurance greater than 50,000).

The general purpose for reconciling is to ensure that the activity within the General Ledger can be tied to the activity within a subsystem. When reconciling Payroll activity there are four main areas of concern: Earnings, Deductions, Employer Contributed Benefits, and Cash. The Payroll Subsystem posts wage expense and checks from the Earnings Register, employer contributed benefits and liabilities with the Contribution Register, and employee withholding liabilities and direct deposits with the Deduction Register. Payroll posts credits to liability accounts in the General Ledger for employer paid benefits and employee withholdings. Payroll also assists in relieving liabilities by sending this information to the Accounts Payable Subsystem. All payroll liabilities will normally net to zero at some point, but the timing will be dependent on the type of withholding.

Payroll and General Ledger

The purpose of this reconciliation is to ensure that transactions in Payroll History that impact employee paychecks and/or district liabilities are recorded correctly in the General Ledger. Typically, for each type of pay, there will be a corresponding expense account. The totals for that pay type within Payroll should match the totals within the General Ledger. Employer benefit contributions are reconciled to ensure that all liabilities are being met correctly. Posting strategies are used to interface history records from Payroll to the General Ledger at the time Payroll is distributed. There are only a few flags on the Payroll Contribution, Deduction, and Hour (CDH) codes that can be used to determine whether that code is being distributed to General Ledger. Typically, Hour codes (and some Contribution codes) distribute if the Gross Paybase flag is check and/or the Net Paybase flag is checked. Contributions distribute if the Debit and/or Credit flag does not equal "NOPOST." Deductions post if the Net Paybase flag is checked.

A Special History report can be written to use Misc flags on the CDH to flag CDH codes for reconciliation reporting. A similar summary report can be written from the General Ledger transactions. These reports can then be compared to ensure the transactions from Payroll posted to the General Ledger as expected. See Appendix for the definition of this report.

This report will summarize the GL transactions at a similar level as the Special History report. It is also possible to create a CDD report with columns that would make the information easier to read and similarly formatted to match the Special History report columns.

On the example reports above, the SUMNET report shows an EFT amount of $531,300.42 for Period 1301130. This can be compared to 01130DP and 01130CM on the Transaction Totals Report for Subsystem 1 ($1,355.64 - $532,656.06 = $531,300.42). Similar comparisons can be made to ensure that the Payroll History records have corresponding entries in the General Ledger.

Subsystem IDs for Payroll in the General Ledger are 1 = Deduction Register, 2 = Contribution Register, 3 = Earnings Register, and 4 = Check Register. Subsystem 4 only posts to the General Ledger if "C" date is used in GLUTSPSI.

Payroll Vendor Liabilities

The purpose of this reconciliation is to ensure that vendor liabilities that were sent to the General Ledger are being passed to, and relieved properly, by Accounts Payable. This reconciliation is performed on the General Ledger side by examining the applicable liability accounts and assumes the Payroll and General Ledger reconciliation has already been performed.

The payroll system creates the General Ledger liability entries with a Primary Reference referring to the Payroll Pay Period. On the previous report they are displayed as CCPPPMM, where CC = Cycle, PPP = Period, and MM = Mask. When Accounts Payable posts the entries sent from Payroll, they also include the Payroll Pay Period in the Primary Reference field formatted as DDDD/YYCCPPP where DDDD = CDH code, YY = Year, CC = Cycle, and PPP = Period. Detailed reconciliation is performed by comparing these entries by their Pay Period references. A summary level comparison can also be performed to determine if a detailed comparison is needed.

The first page of the report starts with Object Code 212010 and displays interim totals by SubSystem ID. If everything is working correctly, the SubSystem 1 and 2 amounts should have offsets in the SubSystem OH amounts. Since the Net balance for the Object Code is $-38.68 we know that all the job numbers do not have matching offsets. To start, notice that the first line in the Deduction section ($-13,096.58) and the first line in the Contribution section ($-13,096.58) tie with the second line in the Open Hold section ($26,193.16). Continuing the comparisons, we are left with the two Job Numbers that show up in the Deduction and Contribution sections but do not have offsetting entries in the Open Hold section. These will need to be examined further to see if they were offset by a Journal Entry (not selected on this report), or if the entries were not interfaced to Open Hold. Continue these steps for the remainder of the liabilities.

Fixed Assets

The definition of Fixed Asset classifications may have a direct bearing on the reconciliation methods outlined below. If a one to one or many to one relationship between Fixed Asset classifications and the General Ledger exists, the user should select reports by classification(s).

Fixed Assets and Accumulated Depreciation

Reconciling Fixed Assets to the General Ledger should compare on two values: Total Asset value and total Accumulated Depreciation. These should be run as of the end of the reconciliation period.

• Determine the period to be reconciled.
• Request a Depreciation Report from the Fixed Asset Subsystem (FA1130 – Fixed Asset Depreciation History Report). The report date should be the last day of the period to be reconciled.
• Run a General Ledger Report (GLRESR02). Select on Object Codes = the Fixed Asset and Accumulated Depreciation object codes, Beginning Date = first day of the period to be reconciled period, Ending Date = last day of the period to be reconciled.
• Fixed Assets Total Value should match the total of all Fixed Asset Accounts in the General Ledger. Accumulated Depreciation should match in both systems. If there are inconsistencies, the user should consider the following:
  • An asset may have been disposed of (sold, thrown away, or destroyed); if this is the case it is important to adjust the General Ledger and modify or delete the Fixed Asset record. If this is not done properly, Total Value and Accumulated Depreciation will be off.
  • If inconsistencies still exist, request a General Ledger Detailed Trial Balance (GLRESR01). Select on Object Codes = the Fixed Asset, Accumulated Depreciation, and the Depreciation Expense object codes and Transaction Date = period to be reconciled. Using this report, determine if all depreciation postings completed correctly.

Job Ledger

The relationship of the job ledger to the general ledger as designed in the account structure is important to successful reconciliation. The job ledger is best viewed as a subsidiary ledger to the general ledger much the same as the accounts receivable subsidiary ledger. When the job ledger is designed as a subsidiary to one or more general ledger control accounts, reconciliation is a matter of reporting the balances of the job ledger and comparing to the general ledger control total. Another help to reconciliation is to duplicate a significant background part in the general ledger and job ledger (Fund or Department). Requiring a job ledger, account when setting up a general ledger org key for each general ledger control account, will substantially increase the success of job ledger reconciliation.

1. To reconcile run a general ledger report (GLRESR02) selecting on the job ledger side. Include all job ledger accounts that will tie to the control account in the general ledger. If a significant background part has been duplicated in both account structures select the job ledger report by the background part, otherwise use job key ranges or wild cards. Compare the job ledger total to the general ledger control account.
2. If the totals do not match run a general ledger transaction listing (GLREFLTR), select on the general ledger control account and sort by job ledger number. This report will reveal any activity posted to the control account from incorrect subsidiary accounts.

• • If the totals still do not match, run a job ledger transaction listing (GLREFLTR), select on the job ledger background part designating the tie to the control account and sort by general ledger account number. This report will reveal activity posted to the job ledger and mapped to an incorrect general ledger control.