xTuple.com xTupleU Blog & News Customer Support

Balance Sheet Imbalance After Creating an Account

My balance sheet went out of balance by $3,364.64 after creating an expense account with no sub-account type a couple months ago. It actually throws every balance sheet off going back to the beginning of fiscal year 2015. When I reload backups of databases I can trace the event back to two hour time period, but have scoured the general ledger trying to find anything out of the ordinary (which I can’t). All balance sheets run in the database an hour before the creation of that account balance, and all sheets in the database an hour after it was created do not balance.

The imbalance can be traced to the Retained Earnings Year End account.

Has anyone ever had this happen? I can’t figure it out at all. Any suggestions?

Running Postbooks 4.11.3 on Postgres 9.6.

I have had that same problem which I traced to not closing fiscal periods. Closing fiscal periods and the previous years does something with retained earnings (I’m not an accountant) and if you don’t do it, it doesn’t balance.

You must close the periods and also the fiscal years to let the profit/loss of a year jump t the next year. xTuple do not pass any revenue/expense balance from one year to the next, the only way to pass that is to close the year and then the net of the income/loss will be moved to an equity account and then pass to the next year.

Regards

Alfredo is correct you have to close them. If that does not fix the issue, you can always reopen if needed.

Thank you both for the advice. Unfortunately, this is a different issue that actually involves only close periods/years. I’ll attach balance sheets from a closed year as an example. One is a correct balance sheet before the creation of this expense account and one out of balance from just after the creation of the account. No individual accounts are off balance, it’s simply as if Retained Earnings magically gained $3,364.64 when the account was created, but gained that over five years before the account was created.

Also attached is a screnshot of the bottom line of the Trial Balance report. Every year from 2015 on shows this beginning balance and ending balance of $3,364.64 in a sort of phantom bottom line.

An accountant asked if xTuple has a utility to check for errors, but it doesn’t right? Is there something I could try directly within the database?Balance sheet noon good.pdf (56.8 KB)
Balance sheet 2pm bad.pdf (56.8 KB)

I had that happen once on my own company books (though i did not have the specifics of when it happened like you do), and I just edited the trialbal table in pgAdmin to fix it.

If you check the PDFs you attached the difference is in the “Retained Earnings Year End” tag, is the only number that changed so someone affected that account. You can check in the database which journal changed the balance of that account. If it changed whitout a journal, then someone closed a fiscal year. Your problem is on that account.

Thanks, Phil. Did changing it in the table affect anything in other accounts? I’m afraid to do anything that might look suspect under audit.

Phil,

There is some side effects when you close the year, then the triabal table save on the year end field the value to move into next year and then you reopen the year, change the retained earnings account to a different one and close again; you can create that kind of discrepancies but requires a series of things to happen.

Regards

Do you have a suggested query to run against the journal? I’ve tried everything I can think of.
Edit: I also went back and reopened every fiscal period and year to before 2014 and then closed them all in sequence to see if that would have an affect, and it didn’t.

In my case I noticed the balance sheet was out of balance, and the value in retained earnings was off by an amount that had no matching transaction…

To begin tracing your problem run SELECT * FROM trialbal WHERE trialbal_yearend > 0;

That will tell you if you have closed a fiscal year and generated a balance hiddend between the ending balance of last month of a fiscal year and beginning balance of the next one. That number pass thru in some kind of hidden way.

Exactly same thing here. I back up the databases every two hours, so I was able to find out that it happened between noon and 2:00 on Oct 22, 2019. (Fiscal year ends June 2019) Only thing that happened in the General Ledger during those two hours was the creation of a new expense account (no transactions into it) and we shipped an item our of inventory for about $800.00.

Very weird the same thing happened to both of us.

Thank you malfredo32! Here is a screenshot of the result. Do you see anything unusual?

I was able to solve this retained earnings error within the trialbal table. If anyone has a problem in the future, feel free to contact me for the steps to take.