Bank Account no longer reconciles
A frustrated user of AR2015 recently sent us a .myox file for repair. The user first noticed a problem with their Bank Reconciliation. A week after reconciling their main cheque account to 29 Sep 2015 (calculated Statement Balance $59,868.47), she noticed the reconciliation was now out by $8250.00. Not having resolved the discrepancy, a few weeks later she noticed the same bank reconciliation as at 29 Sep 2015 was now out by $9330.70.

The normal response to claims the bank reconciliation is out, is that someone has changed a previously reconciled transaction. In some forum posts, MYOB staff claim this is the only way a previously reconciled bank account no longer reconciles. Most users are sent back through possibly hundred of transactions trying to work out what happened.

This user was adamant no reconciled transactions were disturbed - and she was right. And while you may look through transactions before the reconciled date, it turns out the problems were caused by new transactions dated in Oct and Nov 2015. So how do transactions in Oct and Nov affect a reconciliation to 29 Sep?

To work out the Calculated Statement Balance for reconciliation purposes, AR2015 starts with the current Bank Account balance (AR stores a running balance for each account in a table called Account) and then deducts every transaction affecting that account this is not marked as reconciled. AR2015 tracks whether a transaction is reconciled or not in a table called JournalLine. Now you would think (and certainly expect) that every time a transaction is recorded, both the JournalLine and Account tables would be updated. Apparently this is not always the case, as our user found out.

Our astute user recognised that the 2 amounts that caused the reconciliation discrepancy ($8250.00 and $1080.70 = $9330.70) both related to automatically recorded monthly recurring transactions. It turns out that on 2 Oct MYOB recorded a $8250.00 Rent transaction twice and on 9 Nov recorded a $1080.70 Loan Repayment twice. In both cases, the duplicate transactions were recorded within 1-3 minutes of the original transaction - by the system. Whatever occurred within MYOB that triggered this duplication also caused several other more serious problems. One of the problems was that MYOB updated the JournalLine table but NOT the Bank Account's balance in the Account table.

As you can see from the Find Transactions window for a/c 1-1000 for the month of Nov 2015 the Ending Balance based on the actual transactions (note duplicated transactions) is $14632.85.

Yet if you do a Balance Sheet to 30 Nov 2015, this account (1-1000) shows a Balance of $13552.15 (a difference of $1080.70). This confirms that although the transaction was entered twice, the Bank Account balance was not updated twice.

At present the bank account balance as per the Account table is -$25698.16, and the JournalLine table shows un-reconciled transactions for this account totalling a net of $94897.33. This causes MYOB to calculate the Statement balance as at 29 Sep as $69199.17.

If the user enters $59868.47 as Closing Statement Balance (being the actual bank statement balance as at the last reconciliation), the account is out of balance by $9330.70 as you can see in the image below:

The net $94897.33 in unreconciled transactions includes 2 lots of $8250.00 on 2 Oct 2015 and 2 lots of $1080.70 on 9 Nov 2015, causing the 29 Sep 2015 bank reconciliation to appear 'Out of Balance' - even though nothing has changed before 29 Sep 2015. This explains how in AR2015, transactions in Oct and Nov can affect the Sep reconciliation.

So if your bank account reconciliation is out of balance, and you haven't disturbed any previously reconciled transactions, look at the period AFTER the reconciliation, not BEFORE.

DATAInvalid
This is a Find Transaction Screen for account 6-2000 showing the duplicate $8250.00 transaction on 2 Oct 2015:

Below we have the duplicate $1080.70 transaction dated 9 Nov 2015:

The obvious approach would be to delete the duplicate transactions - however every attempt to delete either transaction is met with an unfriendly 'Error - DataInvalid' message, as you can see below:

If you do a MYOB Community Forum search on this error, you will find hundreds of hits. The explanation is that there is a duplicate record in the file and it needs to be send in to MYOB for them to fix. You might think the duplicate record MYOB refers to is the duplicate transaction for $8250.00 or $1080.70 - but it doesn't.

In this case, the duplicate records relates to duplicate records in a table called AccountRegister. This table holds Activity Totals for each account for each month. So in addition to updating the account's running balance, MYOB also tracks each account's activity per month. MYOB using this information for generating reports such as financial statements.

A close look inside this table revealed that when MYOB hiccupped and duplicated the above transactions, it didn't see the AccountRegister record it created for this account for this month when the original transaction was recorded, and so it created another one - we suspect this has something to do with online syncing, caching of files or something like that.

Either way MYOB's right hand didn't know what it's left had just done, and so a duplicate record was created. The image below shows an extract from the AccountRegister table - the AccountId 135 = a/c 6-2000 Rent:



Notice for AccountID 135 there are 2 records for the month 10 of Year 2015, the Activity shows $7500.00 - the ex-GST amount of the transaction.

Below you can see the same thing for account 2-6160 - which is AccountID 216 - this time with 2 records for Month 11 of 2015.

Now while you can add transactions to these accounts for these months - and MYOB will allocate the activity to the first encountered record, when you try to delete the transaction, MYOB checks if the AccountRegister record for the month is unique, and finding 2, it spits the dummy with the DataInvalid error.

Net Assets does not equal Equity
A fundamental rule of double entry bookkeeping is that Net Assets should equal Equity.

When we generated a Dec 2015 Balance Sheet, we found the following (shown as Level 2 to keep image smaller):

The discrepancy between Net Assets and Equity was $9531.00. This was baffling, not just that MYOB didn't even seem to check for such a fundamental thing and alert the user, but that the discrepancy did not correlate directly to our 2 rogue transactions.

Further investigation revealed a 3rd rogue transactions, another duplicated automatically recorded, recurring transaction. What was more troubling about this was that it occurred in Aug 2015 - and did not throw a reconciliation error. This is because, although it created a duplicate AccountRegister record, unlike the Oct and Nov duplicates, the second transaction did update the Bank account running balance.

See below a duplicated transaction for $950.30 dated 20 Aug 2015:

Interestingly both of these transactions were marked as reconciled. Trying to delete either lead to the DataInvalid Error.

Fortunately this was a Balance Sheet only transaction and did not affect the P&L or the BAS statements. We cringe a little as we contemplate how many AR2015 users are out there who have duplicated transactions which they have not picked up. We stress that all 3 instances here were system generated recurring transactions - these were not caused by user error!

Take a break and open your AR2015 file (or AR2011-AR2014 file) and generate a Balance Sheet dated end of next financial year - and check that Net Assets = Equity. If not, this is a sure sign your accounts are not as they should be. We expect it will be years before AR2015 is a reliable stable product - in the mean time we feel for all those users who are unwitting guinea pigs in this great experiment which is AR2015. (This comment is not based on this file alone, but rather on hundreds and hundreds of MYOB Community Forum posts from frustrated and perplexed AR2015 users.)

FYI - the discrepancy in this file was calculated as follows: $8250.00 less the GST component of $750.00, plus $1080.70 plus $950.30 gives a net discrepancy of $9531.00.

Fixing the Mess
Removing the duplicate AccountRegister records clears the DataInvalid error and allows the duplicated transactions to be deleted. However we certainly hope that the MYOB fix goes beyond that - all discrepancies need to be corrected. We have a tool to check if Activity Totals are correctly recorded for each Month - we certainly hope MYOB Australia checks these before declaring a file 'repaired'. For example in this file 4 Monthly Activity totals were incorrect (meaning Financial Statements are also incorrect), as well as 3 Account Balances.

What About Next Month?
There are numerous users on the MYOB Community Forum who update their original post saying they had previously send their file in for repair, the file was fixed, but now the same issue has re-emerged. Properly fixing a file (including this user's file) will correct the accounts etc - but it doesn't solve the underlying problem - AR2015.

Clearly AR2015 has a problem with automatically recorded recurring transactions. There are no doubt other bugs causing duplicate records and the Data Invalid error. So, unfortunately the same thing may well happen again - until MYOB work out what is causing each of these problems and addresses them. Past experience has shown MYOB are slow to address known bugs - just check out the forum where users re-post after several years reminding MYOB that a particular bug is not yet fixed.

So you may ask - do we recommend users upgrade from v19 to AR2015? - answer 'No'.