G/L Imbalances

Version Relevance: All versions (although some functions mentioned are post-V3.1000)

Issue: I cannot close G/L Month-End because the system tells me there is an imbalance. How can I fix this?

Jun 6th, 2011

Feedback: For the Month-End process to proceed the apparent future periods G/L account turnovers and balances must balance. This is easily seen in Ledgers -- G/L -- Review Account List and Future Periods tab pane where the sum of turnovers and balances must add up to zero, otherwise the imbalance will show up on the lower left.

The turnovers and balances are incrementally maintained every time a legitimate transaction is processed. In other words the values for each account are running totals, adjusted as transactions take place. If a transaction is not processed in full these values can get out-of-step and so as a safety procedure at G/L Month-End the G/L is 'Re-Set' (hence the checkbox under General Ledger in Month-End Processing). Re-setting the G/L (which can be done at any time in File -- Advanced -- Re-Set Data Files) re-calculates the turnovers and balances on each G/L Account based on the underlying transactions and so corrects any transaction processing failures to complete. But that is only half the story!

So how can the G/L get out of balance when Caliach Vision forces every activity to balance in the G/L?

  1. Incomplete transaction processing. For example, during a dispatch when the user is disconnected suddenly from the server while the dispatch is being processed. A transaction may have been inserted but the G/L incremented value not updated, or less than the required number of transaction have been inserted.
  2. There is other datafile damage such as index corruption to the transaction file (TRAFILE).
  3. Values in the transaction file (TRAFILE) or G/L Account file (GLAFILE) have been tampered with in peek and poke. We call this "Data Vandalism".
  4. There is a program bug such that for a particular activity G/L balancing transactions have not been created properly.

In the old days of slow computers and networks the reason number 1 was not uncommon. But now it is rare even for very large sites with intense activity. And for the same reason 2 is even more rare. By virtue of Caliach Vision's maturity and constant use by thousands of users, reason 4 has not been seen for many years and is unlikely to unless major changes are made.

So how do we go about investigating and fixing a G/L Imbalance?

Typically when I receive a customer datafile with a report of a G/L imbalance I have no knowledge of the precise activities that led up to the problem being reported. It is fair to say that it is not uncommon for the circumstance to be unknown to the accounts manager that reports them. The damage may well have been caused some time in the past and only became apparent when the month-end needed processing. So we have to start with no assumptions and no clues to exploit. This is my routine approach:

  1. I won't repeat myself to avoid making this document into a tome, but THIS IS VERY IMPORTANT! Once you know you have ledger damage, and an imbalance IS damage, you must first:
    • Stop all processing on the system - get every user off and suspend any activity that may affect the ledgers.
    • Backup the datafiles and make a copy to experiment with. At appropriate stages make further copies because some steps you take may not work and it is useful to be able to step back and try another route or value.
    • Make copious notes of exactly what steps you take and the results so the saliant adjustments can be reproduced on the live datafile, when you reach a conclusion.
    • Identify the last backup before damage was detected, this may be needed if all attempts to repair fail.
  2. File -- Advanced -- Re-Set Data Files and then Reset General Ledger Balances. This ensures this standard tool has not been missed. If there are numerous corrections made, it mat indicate TRAFILE index damage. If this is the case, you need to go to File -- Advanced -- Reindexing, select the TRAFILE and Reindex and after successful completion Re-set the G/L again. If that corrects everything fine, but it can make it worse, and if so recover a backup and perform a DataFix to generate a whole new database.
  3. Assuming the Re-set G/L did little or nothing I then look at the Ledgers -- G/L -- Review Account List and look at the bottom left on the Current Period tab for a current imbalance and on the Future Periods tab for a future imbalance. If there are none then there is no G/L imbalance. However, that is not quite the end of the story even with zero imbalances. A quick look at the Ledgers -- G/L -- Financial Statements, Balance Sheet, 12 periods report may show the balance sheet out-of-balance. If so doing a month-end may create a future G/L imbalance. In other words we could create an G/L imbalance at a month-end by inheriting damage from the past, especially if it is deliberate.
  4. More typically, a current and/or future imbalance will show up. The current period simply looks at the current open G/L period and checks that the closing period balances on asset accounts is equal and opposite to the trading turnovers. In other words if a month-end were performed would the balance sheet remain in balance? If the balance sheet is historically out-of-balance this won't be true of course, but for the current period it will be true. The future imbalance is somewhat more complex as it looks into the indefinite future however far you have posted into future periods. It also looks at turnovers from the start of the year rather than just the current period. However, to do this and balance it needs to exclude any closed period retained earnings that have been recorded at previous month-ends so far this accounting year.
  5. The next thing I do is run a Activity Log report from Period Routines to see whether this indicates any activity that may explain, at least partially, what has happened.
  6. I can then go to Ledgers -- Ledger Manager -- Audit Trail and on the right of the Reports and Analysis tab pane run a number of investigative reports that may point to a cause and where the damage lies. For instance, the Transaction Id check will, for recent periods where archiving has not taken place, identify where transactions have been deleted. Normally a serious crime! The History Integrity analysis looks at monthly turnover and balance history and the associated underlying transactions and looks for a match. An inconsistency could indicate tampering with historic data, a phenomena that is known in the trade as "European Union Accounting" which can seem tempting at first glance but generally is a fool's errand.
  7. One thing we need to get straight is whether the imbalance is in the current or future periods or historic. We can do this easily in the Audit Trail Listing tab pane. Select From Period in the top left dropdown list, enter the current open G/L period Id and Build List. Then to eliminate non-G/L transactions sort by Account and then select all the G/L accounts. WHen you select the sum of selected at the top right will be updated and should be 0.00. If not you need to narrow the list to individual periods to establish where the imbalance lies. If the imbalance is found to be because of incomplete transactions (or corrupt ones) then do a Ledgers -- Ledger Manager -- Non-Balancing Transaction to correct it. but remember that this must be moved to the period of the error in Ledgers -- Ledger Manager -- Change Transaction Period, if the error isn't in the current G/L period.
  8. So what happens if the Audit Trail shows all open period transactions to be in balance?
  9. This is where it gets nasty! If this is the case we can deduce that the imbalance on the current or future G/L is being derived from already closed months. Earlier History Integrity analysis may have pre-warned us of this and may point to the accounts which have been affected. Typically, repair of historic data involves making non-balancing transactions which will post into the current G/L period, then peek and poke the transaction (TRAFILE) and change the TRAPERN period id number back to the appropriate period. You must then run a Re-Set General Ledger to correct balances and turnovers. You may also need to change the historic values in the BUDFILE, but you have to be damn sure what you are doing there!
  10. To help, here are some important facts about how the ledgers normally balance and where the turnover and balance values derive from when the G/L is running normally and when re-set.
    • Trading turnovers are calculated from transactions since the end of the last financial year. It is therefore very important that the Last year end date (MCDLYED) seen in Ledger Manager -- Period Calendar corresponds exactly to a calendar date (BDADATE) - one day out and your year-to-date starts from zero! This does mean that you can hide errors in the past by bringing forward (with peak and poke) your previous year end. This won't change any history but it will put behind you an inglorious past.
    • Asset account balances are calculated differently. These are calculated from all transactions for an account on file. History is only used to populate some reference values but not any current or future balances.
    • You should think of history (BUDFILE) records as merely a snapshot of the month-end transaction states, kept for the purposes of reporting. History values do not affect the state of G/L balancing.
    • Bear in mind that the G/L -- Review Account List is viewing G/L Account turnover and balance record values (with the exception of Y-T-D Retained Earnings transactions in the future listing), which in turn are derived from transactions. The Audit Trail test we did earlier looks at transactions directly and if there is no imbalance in these, then any imbalance must derive from closed periods subsequent to the last year-end. If the G/L has been reset and there is no imbalance in the current and future transactions (see Audit Trail above), if the G/L current and future imbalances are not the same value, this would indicate that the Retained Earnings account transactions are historically in error.
    • In the above circumstances, it is only the contents of this account that differentiates the current and future imbalance calculations. To find out which is the retained earnings account from the Ledger Manager -- Control Settings and then Default Accounts tab pane. You need to work out what adjustment to the value of a closed period transaction in this year is to be made to bring the current and future balances into line. Note, you can't use a non-balancing transaction for this, it MUST be an adjustment in value to an existing historic month-end transaction. This is because it is these and only these that effect the balances. Here is an example: Say the Current Imbalance = 1000.00 = C and the Future Imbalance = -900.00 = F. To make them the same you need to first calculate the difference F - C (taking care to resolve the signs (- - = +)), so -900.00 - 1000.00 = -1900.00. You then need to add that result to the value of the last month-end retained earnings transaction value (both TRAGROSF and TRAGROS). Lets say that value was 100000.00, then you add the difference of -1900.00 to 100000.00 and arrive at 98100.00. Re-setting the G/L will lead to both current and future imbalances being -900.00. The full formula where Tv is the transaction current value is Tv + F - C.
    • Once the current and future imbalances are in line, again assuming all current and future G/L transactions balance, you then need to adjust one or more trading account turnovers by a retrospective non-balancing transaction or a change in value for an existing transaction. Changing an asset account won't get rid of the imbalance, and don't touch the retained earnings account again.
    • After any data manipulation always run the re-set G/L.
  11. Once you have a Review Account List showing no imbalances, you have one more task before going to bed. You must run a G/L Month-End and after that check again. Only with everything balancing after a month-end can you be confident that the imbalances have been fully corrected.

Chris Ross - Caliach Consultant