Reckon 2016 R1 home and business actual account register balance does not equal account balance report

  • 1
  • Question
  • Updated 3 years ago

When I run an account balances or net worth report, the balances do not equal the actual account register balance. This happens across all account types eg bank, credit, loan but not necessarily every account in the type is affected.

All accounts have been reconciled without discrepancy. 

I am at aloss on this one. 

Photo of Simon Pinnock

Simon Pinnock

  • 170 Points 100 badge 2x thumb

Posted 3 years ago

  • 1
Photo of Adrian

Adrian

  • 3,630 Points 3k badge 2x thumb
Are your reports run on a cash basis and not accrual basis?

Adrian
Photo of Simon Pinnock

Simon Pinnock

  • 170 Points 100 badge 2x thumb
Yes, cash basis
Photo of Adrian

Adrian

  • 3,630 Points 3k badge 2x thumb
Change report setting to Accrual basis then the figures will be correct.

Adrian
Photo of Robyn Kelly (Partner)

Robyn Kelly (Partner), Accredited Partner

  • 4,930 Points 4k badge 2x thumb

Hi Simon

As you are using the Home & Business product there is no option to run the report on an accrual basis.

Can you please check some of these things:

  • That the reports you are looking at are all the same date
  • Can you please click "Customise" and make sure there are no accounts hidden and all accounts are selected
  • Same with categories, payees and securities
  • Try using "All" dates and see if the same thing happens
  • If there are still issues try each year at a time to see where the problem first started
  • What I find sometimes with H&B is that there could be a transaction in the cheque account and the split to that transaction i sgoing back to the same cheque account

Hopefully one of those is your solution.

Regards, Robyn Kelly

Reckon Accredited Partner

Photo of Simon Pinnock

Simon Pinnock

  • 170 Points 100 badge 2x thumb

I have tried to change report settings through reports centre/preferences/advanced and through edit/preferences/program/reports and graphs but there is nothing related to cash basis or accrual basis. Same if I go to a register and press options it goes to the same page with no cash/accrual option.

Is there a global setting that I have missed

Photo of Simon Pinnock

Simon Pinnock

  • 170 Points 100 badge 2x thumb

Replied to Adrian before I saw Robyn's comment

Hi Robyn

Thanks to answer some of your suggestions

Yes all reports have same date, all accounts including hiddens, all categories, all payees etc . I've tried custom dates, earliest to date, last year all the same result.

I will do some investigation a year at a time and see what I can discover.

Photo of Simon Pinnock

Simon Pinnock

  • 170 Points 100 badge 2x thumb

Hi all

I ran the Account Balance Report over multiple years and compared the 30 June balances between the report and the registers. The discrepancies are over multiple years across multiple accounts and there are hundreds of them at least.

I narrowed down what is happening.

In the register some transaction split categories have been deleted resulting in an uncategorised balancing item which is the same amount as the discrepancy. 

If I enter a category and save the transaction the report is brought into line but we are talking about potentially thousands of transactions across 20 years and 20 + accounts to validate.

So I ran an itemised categories report with all accounts, transactions, payees, etc with only one category checked i.e. no category. This will provide all transactions with no category. 

There are no uncategorised  transactions in any year up to 30 June 2015!

Now I am thoroughly perplexed. The categories report confirms transactions were categorised but the account balances report/net worth/balance sheet simply exclude the uncategorised splits and as a result are inaccurate.

Any thought  would be appreciated. Thank you

Photo of John G

John G, Information Support Analyst

  • 27,510 Points 20k badge 2x thumb
Hi Simon,

Please Validate and then Super Validate your datafile.
File > File Operation > Validate.
to Super-Validate, repeat validate steps but hold down Ctrl+Shift as you click on Validate.

Is there any change to your results?


regards,
John.
Photo of Simon Pinnock

Simon Pinnock

  • 170 Points 100 badge 2x thumb
Hi John have validated and there was no change. Never knew about super validate will try later today and advise. THANKS
Photo of Simon Pinnock

Simon Pinnock

  • 170 Points 100 badge 2x thumb

Hi John

Further update I ran the super validate.

The no categories report shows multiple uncategorised transactions from 1996 to March 2005 and zero uncategorised transactions from March 2005 to 30th June 2015.

FY15/16 is showing uncategorised transactions as I haven't finished the accounts for last year because of this issue.

So I ran an Account Balances Report for one affected account and compared it to the actual account register. As at 30th June 2008, the account balance report was $3858.20 higher than the actual register figure.

Going through every transaction in 2008 from opening balance, I discovered some interesting behaviour which occurs randomly

  1. In the register, expenses are sometimes added to the balance and deposits are subtracted from the balance
  2. Any expense or deposit with a single split may be ignored, added or subtracted partially or in full to the balance
  3. To correct these issues, an expense or deposit must be re-entered using the split window only and even then it may not add up correctly to the balance or be repeated multiple times to effect the change
  4. The corrections must be done sequentially and each calculation verified if not the balances backwards and forwards will be incorrect due to (2) above and you have to start back at transaction one 

After 2 hours, I finally got the register and account balance report for this one account with only 40 transactions to match for the year 30th June 2008.

It is the reconciled register balances that are inaccurate and it seems it is the account balances and categories reports that are accurate. This strikes me as very odd, as I would have thought that the basis of the reports would be the data in the registers.

The dropping of categories still exists but this lack of integrity in the account registers is very concerning.

Any thoughts or suggestions would be most welcome.  

Cheers

Simon

Photo of Robyn Kelly (Partner)

Robyn Kelly (Partner), Accredited Partner

  • 4,930 Points 4k badge 2x thumb

Hi again Simon

I have actually seen this happen before - what you are talking about that the uncategorised transactions don't actually show on the uncategorised report as I call it (what you have explained above).  I am presuming your data has come from an older version?  In an older version (and I can't pin point which one) some of the uncategorised transactions didn't / don't appear on that report.  I thought it was if the transaction had not been split but you are explaining above that the transactions are split with an uncategorised amount in the split.

I've just tried the above in version 2016 and the uncategorised amounts are showing up in the uncategorised report correctly; so I think it is a hang over from a previous version.

Two questions for you:

  1. Are you registered for GST?
  2. If so, do the uncategorised amounts in the split look like the GST amount?

One other check I do every end of financial year is: 

  • Net worth value at 30/06/xx
  • Minus Net worth value at previous 30/06/xx

  • Must equal the profit or loss for that financial year.

I often find errors by doing this simple check each financail year end.

Regards, Robyn Kelly

Photo of Simon Pinnock

Simon Pinnock

  • 170 Points 100 badge 2x thumb

Hi Robyn

Yes I am registered for GST and splits don't equal GST values.

You are correct that the data has passed through multiple versions of Quicken/Reckon since 1996 in fact.

What is weird is that I reconciled everything when I upgraded to 2016, ran uncategorised reports   earliest to date which reported zero uncategorised items except for the current financial year which at the time was 15/16. Checked P&L and balance sheet in old software to 2016 and all equalled.

I was confident that the starting point for all accounts and categories was accurate i.e. 30 June 2014.

"Best laid plans of mice and men go awry" I guess

Thanks Simon




Photo of John G

John G, Information Support Analyst

  • 27,510 Points 20k badge 2x thumb
Hi Simon,

It would seem your file may have suffered a corruption just prior to upgrade and the upgrade has accentuated the problem.  There was a similar issue with upgrades from pre-2008 editions to 2008 and later, described in this KB, that may have impacted on your file, an issue we have not seen in many years.

In addition, does your file have transactions dated in the future?  Have previously reconciled transactions been unreconciled?  These may account for differences in balances but not for the appearance of uncategorised transactions.

It sounds like you are doing the only steps you can take - methodically going through period by period, identifying transactions contributing to the error and making the corrections necessary. 


regards,
John
Photo of Simon Pinnock

Simon Pinnock

  • 170 Points 100 badge 2x thumb

Hi John

Thanks for your help.

Further investigation has identified that 8 accounts are affected unfortunately 6 of those are accounts I have held since 1996.

I started with one that had no discrepancies until 2004. The instant I rectified the transaction the calculation errors advised above cascaded backwards and affected balances 6 years prior. So I am now in a worse position than when I started.

Is there a way to  split my data file at a fixed date eg 30 June 2010.  File one would be the historical data and file 2 would have new and corrected opening balances for each account and I can rectify entries as needed. 

If I can't do that can I export all transactions for all accounts for this year to a new file.

Cheers


Simon


Photo of John G

John G, Information Support Analyst

  • 27,510 Points 20k badge 2x thumb
Hi Simon,

Yes, you can split your file through the File > File Operations > Create a Year End Copy.


regards,
John
Photo of Simon Pinnock

Simon Pinnock

  • 170 Points 100 badge 2x thumb

Thanks John

I've read the create a year end copy material and there are two options archive a file and create a new year file. From the description would creating a new file be your recommendation?

Cheers


Simon

Photo of John G

John G, Information Support Analyst

  • 27,510 Points 20k badge 2x thumb
Hi Simon,

The Year-End process will only affect your Bank Accounts and reconciled transactions.  Cash and Investment accounts will not be affected.

The new file will contain only transactions from the nominated date and any un-reconciled transactions before that date.  

Your existing file will be backed up in full - from the file's start date to now.

When prompted for which file to open, chose the new year one.  


regards,
John.
Photo of Simon Pinnock

Simon Pinnock

  • 170 Points 100 badge 2x thumb

Thank you all who provided the info, in particular to John who provided the means to resolve the issue - very much appreciated.

The data has come across successfully and with only some adjustment to opening balances everything is all squared away.

Have a great Christmas!