Understanding AR Balancing in GovCollect

Introduction

In the GovCollect system, all financial transactions (Debits / Credits) are stored in a table called the GLTRAN. All balancing actions and financial reports look at the transactions inside that table.

Every transaction in that table has a TYPE and SEGMENT which determine how that transaction behalves in the system and on reports. For example,

when a new charge is applied for $100, in the GLTRAN there are two lines created:

1. TYPE AR – SEGMENT Normal - DEBIT $100 to GL Number usually AR account

2. TYPE AR – SEGMENT Normal – CREDIT $100 to GL Number usually Revenue account

The TYPE of AR, separates these transactions into the BILLING column on financial reports and separates them from PAYMENTS on daily balancing and exports to accounting software.

OTHER TYPES in GovCollect include – PAYMENT, ADJUST, CREDIT REC, CREDIT APP, REVERSAL, REFUND, AR_REV, WRITEOFF.

SEGMENT refers to the portion of the amount like Normal (base amount), penalty, and interest. These are also used to format reports and balancing.

To see an example of this data in your system, click TREE \ AUDIT \ GL Entries all Transactions to browse your GLTRAN data.

 

arbal1.JPG

This will display your GLTRAN Work area. This should only be accessible to employees allowed to edit the gl entries if need arises.

 

arbal2.JPG

Scrolling from left to right, you can learn the type of data recorded for every transaction including glnumbers, amounts, descriptions, pidn, account, receipt numbers, sessions, deposit numbers, users and much more.  Two very important fields are at the far right, Balanced_date and Balanced.  We will discuss those fields in the balancing section.

 

arbal3.JPG

 

How are Initial AR Entries Created with the Recalc

GovCollect has one program that handles all math calculations and we refer to it as RECALC.  When a charge is applied in Govcollect onto a PIDN’s CHARGES SCREEN, the last step is a RECALC.  The Recalc may be done on that single PIDN or for the entire system.  That Recalc does many things. 

First, it matches the TRAN CODE on that charge with your TRAN CODE SETUP area (TREE \ SYSTEM SETUP \ TRANSACTION CODE SETUP). The TRAN CODE SETUP tells the system the rates to use and the AR GL Numbers assigned to that Tran Code.

 

arbal4.JPG

Above is a picture of the Spot in the tran code on the Accounting Tab, where the Recalc would look to pull the gl numbers for the tran code being recalced. If these fields are blank then the gl number field in the GLTRAN will be blank on these entries. (In the picture we use words so it is easy to identify. Normally you would type your glnumber such as 01-000-0101).

Second, the Recalc looks at the dates (due and past due) on the charge to evaluate if the charge should have a discount or is late and needs penalty and interest.

 

arbal4b.JPG

Third, the Recalc does the math for the charge and inserts the value into the proper field on the Charge.  Normal for the base amount, discount (if applicable), penalty and interest.  Then it adds all those segments together and inserts the GROSS balance.

 

arbal5.JPG

Fourth, it updates the Calc_Type status which is used on reports with Pending, Past Due or Paid.

 

arbal6.JPG

Fifth, the Recalc makes the GL entries (debit and credit) in the GLTRAN with the dates, amounts, tran code, AR TYPE and segment.

Last, the Recalc inserts the tran code and date into the AR Start field at the bottom of the charge screen.  This action tells the system that an initial AR entry has been made and do NOT make another AR entry.  That is how we get an initial “Billing” AR entry.

 

arbal7.JPG

 

Ongoing AR / Adjustment entries made by Recalc

Every time the system or single Recalc is run, every charge is evaluated unless the charge line calc type = “Paid”.  When the system recalcs the line, if the amount changes (up or down), the system will make an adjusting entry in the GLTRAN to reflect the change.  The most common example is when penalty or interest is applied.  The system would do the calculation and create new gl entries in the GLTRAN for the new penalty or interest amount.

AR Reversal Entries

If a charge is deleted off the charge screen from a PIDN, the system will create AR_REV entries in the GLTRAN which would reduce the billed amount.

AR Writeoff Adjustment

Since most communities do not have a writeoff liability account, the Govcollect system makes this adjustment easy. When a customer short pays by say 1 penny and the user is allowed to say yes WRITEOFF Balance, the system does an AR adjustment to reduce the expected AR by a penny for that so you stay in balance.

 

Balancing AR transactions

Balancing after applying a Billing

After you apply a bulk billing like utility bills or property tax bills, we recommend running 2 reports to match. Pre Billing vs Fiscal Audit.

Amounts from the Charges screen

REPORTS \ PRODUCT \ BILLING

 

arbal8.JPG

#1 is your charges by each PIDN.  #2 is the same data separated by service.  You can run either to match against our GL Report.  Just type the due date start and end equal to your current billing due date.

Amounts from GLTRAN

 

arbal9.JPG

REPORTS \ ALL \ 2 Daily \ #2 Fiscal Year

This report allows you to insert a due date and as of date to match with the pre billing report.  This report is from your GLTRAN and is what is balanced and exported to accounting software.  The standard presentation of this report is by tran code and by due date.


Daily Balancing of AR Entries

Any time an amount is added or changes in the system new GL Entries will be created in the GLTRAN.  These entries are available for audit and export (report or file) for accounting software updates.  THESE ARE SEPARATE FROM PAYMENT ENTRIES.

Click TREE \ BALANCING \ ACCOUNTING EXPORT

 

arbal10.JPG

*Payments are done in sessions and that is why those are balanced in the “Balance Session” area. AR entries are grouped by Date.

 

arbal11.JPG


1. Click the Gather Sessions and Dates button and the system will return any Payment Deposit files to export as well as every day that has AR entries that have not been resolved.  In the picture above you see 4 AR files and 1 Deposit.

2. Click the AR date you want to work with and click GATHER TRANSACTIONS.  You can highlight more than 1 date if you want to by holding down the Cntrl button. Text will appear when it is complete.

 

arbal12.JPG

3. Click on the Reports Tab. Highlight the EXPORT AR GENERAL LEDGER and push RUN button.

4. Box will appear telling you the data is ready.  Click OK and the report will preview.

5. The report is grouped by GL Number and then tran code to identify the amounts. This report is used if making journal entries to your accounting system.

 

arbal13.JPG

REGARDLESS OF IF YOU EXPORT TO AN ACCOUNTING SYSTEM, YOU NEED TO AT LEAST DO THE NEXT STEP TO KEEP YOUR DATA CLEAN.

1. Click on the EXPORT TAB

 

arbal14.JPG

2. Clicking “Step 1 – Mark Transactions for Export” will insert a TRUE Value into the BALANCED field in the GLTRAN and make this AR session disappear from the “Gather Session” screen (step 1 in the accounting export steps above).  This also prevents you from exporting the same data twice.

 

IF YOU DO NOT EXPORT TO ACCOUNTING SOFTWARE THEN YOU ARE FINISHED AND CAN EXIT.

 

IF YOU EXPORT TO ACCOUNTING SOFTWARE CONTINUE

1. After pushing “Mark Transactions for Export,” then go to step 3 and pick your accounting software from the drop down list.  When you select your software, a default path will appear for our export file and what it will be called.  Normally our files are exported to the Govcollect\AccountingExport folder.

2. In Step 4, it defaults to insert the posting (transaction) date in the file but you can select the date the transactions were Balanced (right now) or the transaction’s effective date if you prefer.  For AR entries posted date is normal.  Push the EXPORT button.  The system will pop up a box saying it is done.  You are finished and can exit Accounting Export area.

File that is exported

Each accounting system has a different format so it is important you select the proper accounting product before producing your export file.

 

Troubleshooting a File

Files are tested prior to going live to insure the layout is matching.  So if you receive and import error from your accounting system the issue is almost always data.  Most common is no GL Number or wrong gl number entered for those tran codes. 

If you experience this type of problem, we recommend using the report to make a manual journal entry to your accounting system for that AR session while you investigate what is wrong in the file so it can be corrected for future files.

Not being able to import a file does not mean you have an error in Govcollect. Like mentioned earlier, leaving out a glnumber does not break GovCollect but it does hamper the integration.

 

Daily System AR Balance Report

If you used the AR Maintenance program to build ar entries and payments on your old history then you can use this report to keep a running total of activity. The report will show you where you started the day, what was added and where you ended up.

Reports \ All \ Daily \ #1

 

arbal15.JPG