Reconciliation Process

Reconciliation is the process of making sure that two records are in agreement. In our case a department will want to make sure that their financial records (posted transactions) are in agreement with the campus central accounting system.

Gus provides two methods to reconcile against the central accounting system:

  1. Edit/Reconcile
  2. Assisted Reconciliation (recommended as best practice)

It should be noted that these two approaches to reconciliation are exclusive and should not be used in parallel. Once you begin reconciling a GL month with one method the reconciliation process should be completed with that same method.

Edit Reconcile

This approach was the standard prior to creation of the Assisted Reconciliation tool.

  1. Using a copy of the campus general ledger, a departmental staff member would pull up the appropriate project (account/fund) in GUS and:

    • Choose a transaction from the GL
    • Find and click on a financial transaction to reveal the details in GUS
  2. Mark it as ‘Reconciled’ if the records match.

  3. Repeat for all records on the GL

Assisted Reconciliation

The assisted reconcile functions are intended to help departments with the monthly process required to reconcile departmental financial information to the General Ledger provided by Accounting.

GUS allows you to import the GL from the Data Warehouse into the GUS database. Once imported you will be able to see comparable data from GUS and from the GL for each account/fund/sub combination in a single window.

Assisted Reconcile provides tools to help you manually find and reconcile matches between the GUS and GL data. It provides tools to correct data mistakes. It also provides an automatic reconciliation function which can reconcile the bulk of the GL without direct human intervention.

In practice, the reconciliation process will consist of a combination of the automatic reconciliation process and the computer-assisted manual reconciliation process. Since the assisted reconcile feature tracks not only allocations, expenses and credits, but also related overhead it allows you to quickly identify specific transactions in which there are overhead errors. It also allows automatic posting of corrections for overhead imbalances caused by rounding errors.

Overview

  1. Get the GL data from the data warehouse and save it on your local computer
  2. Import the GL data into GUS

  3. Remove any existing GL Liens

  4. Remove any previously matched GL entries (at departmental option - some departments may opt to save matched items)

  5. Allow GUS to auto-reconcile

  6. For each account/fund pair:

    Manually identify and reconcile any remaining unreconciled GUS transactions and matching GL entries. Compare final GUS balances for each account/fund pair to those on the ledger. Any remaining unmatched GL items become part of the “Problems List”. These are transactions which will require additional research to resolve. This would typically involve examining or obtaining backup documentation for the transactions in question.

Downloading/Importing GL

After logging into EZ Access, select the ‘GUS export’ under the General Ledger menu. When downloading the GL from Data Warehouse, there is no need to open the file first.

  1. Save the file to your computer.
  • this can be done in one of several ways (depending on your operating system, browser). Here are two generic options:

    1. Click on the ‘Export to Excel’ link.
    • A window may prompt you on what to do with the file
    • Choose to save to a location on your computer
    1. Right click on the ‘Export to Excel’ link and choose to ‘Save link As’
    • Choose to save to a location on your computer
  • It will default to be downloaded as a file named ‘GUS_export.xls’.

  1. In the Assisted-Reconciliation window, choose “import GL” choose-import
  2. Browse to the location of the file that was saved in step 1 and select ‘Open’
  3. A window will open up displaying a preview of the imported data
  • If certain records have already been imported, there will be a message at the top of the preview window already-imported

Form Layout

The Assisted-Reconciliation summary page consists of three major areas:

  1. Data summary area for each account/fund combination in your database
  2. Summary for GL records which includes some ability to delete GL records
  3. Preferences area

Assisted-Reconciliation

In addition, there are:

  1. Buttons which allow you to import GL data, print a list of account/funds

  2. Scan for information about your account/funds and to auto-reconcile

  3. tabbed areas which will allow you to see all of the GL and GUS info in your database

    • These pages are mainly to be used if you need to un-reconcile something which has been accidentally reconciled.

Using Assisted Reconciliation

If you open this form and see zeros in all of the columns (and the GL has already been imported) you’ll need to click on the Scan button near the lower center of the form, to gather information about your unreconciled GUS records and any GL data in your database.

After the scan you will see numbers in the columns and (perhaps) a symbol in the Rec column, next to an account/fund:

  • * (asterisk)

    This indicates that there are GL records for that account-fund, but there is no corresponding account-fund within GUS. This can happen if Accounting accidentally posts transactions to your department which don’t actually belong to your department. It can also happen if the activity is related to Projects for your department which you have marked as Closed or Archived.

  • <- (an arrow)

    For any account-fund pair in which there is the possibility that something might be reconcilable. If there is no arrow, GUS is telling you that there is nothing possible to reconcile for that account-fund.

The “Rec” column may also change as you work.

Reconciliation Process

In order to reconcile GUS transactions you must select them in the upper subform and you must also select the corresponding GL transactions in the lower form. If overhead is charged against the transactions you must also select the appropriate Sub Y transactions.

As you select records GUS will give you feedback to help you determine whether you need to select or deselect records and whether you are ready to mark the transactions as “Reconciled” (e.g. GUS compares the totals of the GUS items selected and the G/L items selected and lets you know if the two are not of the same value).

Auto-Reconciliation

Auto-reconciliation can be a daunting idea. How does it work? The following is a high level view at the way that GUS decides how to auto-reconcile.

First, Account Fund Sub must match.

Then it looks at:

  1. Control number

    • GUS side

      control number no allocations not previously reconciled flagged as an expense

    • GL side

      po number = GUS control number not matched $0 allocation

    • total gus expense per control number:

      expense + sales tax + use tax + shipping

    • number of gus transactions per control number

    • total GL expenses per control number

    • number of GL transactions per control number

    GUS marks as reconciled and matched if both totals and number of transactions match

  2. Department number similar to control number except we look at GUS Dept Number field

FAQs

Should I wait for June’s final ledger to use the Assisted Reconcile?

The June Ledger is different from other months in that it comes in two stages; preliminary and final. Because of this split, you may be thinking of waiting until the June Final is released to import June ledger data. This would require you to wait until August to finish your June reconciliations.

There is another option. GUS will allow you to import two ledgers with the same ledger date (though it will warn you that you are doing so.). This means that you can download and import the June Preliminary ledger as soon as it is released. You can then use this data to perform most of your reconciliation process. You won’t be able to finish until the June Final is released.

When the June Final is released you can use properly formatted queries to extract the data which was not included in the June Preliminary, import the new data into GUS, and then finish your June reconciliation.

If you are using Brio to access the Data Warehouse you can get the preliminary data by setting your GUS export report to select the appropriate fiscal month and set the Closing Cycle field to Equal P. This will select the Preliminary data which you can export to your computer and import into GUS.

To add a new limit on the closing cycle field, users must navigate to the Query section of the document and double-click on the field Closing Cycle in the list to add the new limit. When the final is done they will need to change the operator in the limit box from Equal to Not Equal and then type in the P.

When the June Final is released you will do a similar query except that the Closing Cycle is set to NOT Equal P. This will select the data which was not in the Preliminary. You can export this selection to your computer and then import it into GUS.

Mary Wenzel’s group in the Data Warehouse has modified Easy Access to also allow the selection of ledger data based on the closing cycle.

What is the problems list?

The Problems List is located near the bottom of the Account/Fund Status form. (Budget:Account/Fund Status).

It is a place where financial transactions which appear on the general ledger but which we don’t have entered into GUS can be recorded.

Some common examples of this are interdepartmental recharges for which you have received no backup documentation, invoices which have been posted by accounting but for which you haven’t received a “green block”, intercampus recharges from other UC’s, and appropriations on the ledger which don’t match what you have in GUS.

The two main advantages of entering this information in the problems list are that it helps you keep track of problems which need to be solved and the totals of the GUS balances plus the problems list totals should match the general ledger totals during the reconciliation process.

Adding an item to the Problems List

You can manually add an item to the Problems List three ways: 1. Double-click on a blank line in the problems list 2. Click on the “New PL Item” button near the bottom of the Account/Fund Status form. 3. (In GUS 4.3a or later) Click on the “+” button near the top of the scroll bar for the Problems List subform.

You can fill in whatever information is appropriate. Items outlined in red are mandatory. Everything else is optional. You can’t enter Lien data or the ADA Time Stamp. The GL Date, Account and Fund will be filled in automatically. The Project Code can be selected from a pulldown.

If you use the GUS Assisted reconcile features and import your ledger from the data warehouse, Problems List items will be automatically entered.

Deleting an item from the Problems List

To manually delete an item from the Problems list, double-click on it to open the record and then click on the “Delete” button near the bottom of the form.

If you use the GUS Assisted reconcile features you will see that your ledger data from the Data Warehouse is imported into the Problems List. As you reconcile each Account-Fund combination the reconciled records will be removed from the Problems List until finally you have either no problems remaining or only those for which you’ll have to do some additional research.

After a problem has been researched, the next steps would be to post and reconcile the items in GUS.

If you reconcile the newly posted transactions using the Edit/Reconcile screen you will have to manually go to the problems list for that particular account-fund combination and delete the related problems list entries.

If you reconcile the newly posted transactions using the Assisted reconcile forms, the problems list entries will be automatically taken care of.