[stable 6.0.2] Account Balance Sheet parser logic error leads to incorrect totals amounts and developer confusion

Attachment of o4sb custom balance sheet parser - hopefully it can be of some use in resolving this. Of course you would also need to update report, at the very least getting rid of absolute value functions.

Note this is not a drop in replacement as it is designed for Aeroo reports and a custom extension of account.account but at least the logic is similar and it is based off the original balance sheet parser.

On 18. 05. 11 02:43, Graeme Gellatly wrote:
> Public bug reported:
>
> If you look at the section where it calculates sum_dr and sum_cr it is
> incorrect. Well it is technically correct, but in the sense of having a
> balance sheet that balances it is incorrect.
>
> Take the accounting equation property = property rights expanded to
> assets = liabilities + equity
>
> In the case of a net profit we incorrectly add the result to sum_dr, our
> equity has increased afterall, so sum_cr should increase, (this
> behaviour is correct, except the function is named wrong) however in the
> case of a net loss we subtract the amount from sum_cr (which should be
> sum_dr) which is incorrect as our equity has reduced.
>
> However here is the first bug - sum_dr and sum_cr are around the wrong
> way - sum_cr is increased for asset accounts, and sum_dr is increased
> for liabilities - it should be the other way around
>
> Put another way, for a balance sheet, we don't really want the totals to
> be the sum of dr's and cr's, we want it to be the total of the stuff on
> the left (Assets) and the total of the stuff on the right (Property
> Rights).
>
> So in the case of a Net Profit we must increase equity (credit) and in
> the case of a net loss we must reduce equity (credit). At no point
> should we be changing debits. The value of our Assets has not changed
> (in so far that a net loss already reduces our assets), it is our equity
> that has reduced.
>
> Now to make this one step more confusing, and for some unknown reason it
> has been decided to leave liabilities as a negative balance, and then on
> the report use absolute value which is also incorrect. Many assets and
> liabilities should have a balance opposite of the standard, Accumulated
> depreciation for example in Assets, VAT Paid in Liabilities, Bank
> Accounts temporarily in overdraft.
>
> In my own parser, which I have written, to resolve this we declare a
> dictionary called signs and at the point we compute the account.balance
> we multiple it by the sign.
>
> sign = {
> 'fixed_asset': 1,
> 'asset': 1,
> 'liability': -1,
> 'fixed_liability': -1,
> 'equity': -1,
> }
>
> We use more types than standard but you get the idea. Assets are their
> balance, liability is their balance *-1.
>
> Then all we do in the for account in accounts loop is make the balance:
> account.balance * sign[typ]
>
> So in summary
> sum_dr and sum_cr are round the wrong way.
> Adding a net loss to Assets is incorrect - it should be subtracted from Equity.
> values (already reported bug) should not be absolute, but rather their inverse for liability and equity accounts.
>
> ** Affects: openobject-addons
> Importance: Undecided
> Status: New
>
I would like to add one more issue to your bug, as I feel it falls in
the same "logic":

as the sums are calculated with "abs", even on the same side the result
is at least misleading. I noticed this when asset credit (cumulated
depreciation on assets) was presented as a positive balance. I think
that it should be negative..

thank you!
Bogdan

Hello ,

Currently I am setting this issue as an opinion status for more clarification.P&L and Balance Sheet report are already improved.
The above mention two reports are considering only 4 types : Income , Expense, Liability and Assets. So If any new type will suggested then it will consider under this 4 type only.(As we have added one separate field in User defined type form).We have transfered the NP or NL with respect of related reserve and surplus account for matching the balance sheet total.

Again this specification needs more discussion between Accounting Experts.
@Accounting Experts : Would you please share your view regarding this bug specification.

Thanks.

Sorry Vinay this is not opinion it is bug. I will release my improvements
to accounting community tomorrow, with new types and reports to support
equity, however I just look at the latest sources and all the bugs are all
still there. Then I thought maybe you meant trunk - but it is exactly the
same??

If you need to ask accounting community opinion on whether a dr is on the
left or right side of a balance sheet then serious questions need asking.
Again if OpenERP genuinely believe that an asset can never have a negative
balance, more questions need asking.
If OpenERP really need to ask opinion if the left side of a balance sheet
total should equal the sum of the amounts above it - c'mon really?
Or whether a liability/equity account should ever be added or subtracted
from the Assets side? This is the basics.

These were the bugs reported, I never asked you to change types, I just gave
an example from my own module on how we handle the negative balances. It
was never suggested that OpenERP should add these types. In fact
specifically it was written

"We use more types than standard but you get the idea. Assets are their
balance, liability is their balance *-1."

On Thu, May 19, 2011 at 9:15 PM, Launchpad Bug Tracker <
<email address hidden>> wrote:

> You have been subscribed to a public bug by Vinay Rana (openerp)
> (vra-openerp):
>
> If you look at the section where it calculates sum_dr and sum_cr it is
> incorrect. Well it is technically correct, but in the sense of having a
> balance sheet that balances it is incorrect.
>
> Take the accounting equation property = property rights expanded to
> assets = liabilities + equity
>
> In the case of a net profit we incorrectly add the result to sum_dr, our
> equity has increased afterall, so sum_cr should increase, (this
> behaviour is correct, except the function is named wrong) however in the
> case of a net loss we subtract the amount from sum_cr (which should be
> sum_dr) which is incorrect as our equity has reduced.
>
> However here is the first bug - sum_dr and sum_cr are around the wrong
> way - sum_cr is increased for asset accounts, and sum_dr is increased
> for liabilities - it should be the other way around
>
> Put another way, for a balance sheet, we don't really want the totals to
> be the sum of dr's and cr's, we want it to be the total of the stuff on
> the left (Assets) and the total of the stuff on the right (Property
> Rights).
>
> So in the case of a Net Profit we must increase equity (credit) and in
> the case of a net loss we must reduce equity (credit). At no point
> should we be changing debits. The value of our Assets has not changed
> (in so far that a net loss already reduces our assets), it is our equity
> that has reduced.
>
> Now to make this one step more confusing, and for some unknown reason it
> has been decided to leave liabilities as a negative balance, and then on
> the report use absolute value which is also incorrect. Many assets and
> liabilities should have a balance opposite of the standard, Accumulated
> depreciation for example in Assets, VAT Paid in Liabilities, Bank
> Accounts temporarily in overdraft.
>
> In my own parser, which I have writ...

Hello Graeme Gellatly,

I know the balance sheet formula :
Assets = Liabilities + Shareholders' Equity

Current report is supporting equity type.The only thing remain is set the proper configuration in user defined account type form.
The existing equity type is not shareholders equity.If you want this as shareholders equity then you just need to change the selection 'Balance Sheet(Liability side).'
I have set this as a opinion for other thing.

We already assigned one of the following wishlist bug for sign related issue:
https://bugs.launchpad.net/openobject-addons/+bug/740132

Hope this will help you
Thanks.

Vinay,

You don't get it, I couldn't care less what types you have. They don't
matter. You have serious bugs in the report and some more minor ones I
don't report yet, like you cannot filter a balance sheet between 2 periods
or dates, it is a point in time report. And anyway I just check latest
6.0.2 sources again and in the balance sheet parser your types are still
hard coded as 94<http://bazaar.launchpad.net/%7Eopenerp/openobject-addons/6.0/view/head:/account/report/account_balance_sheet.py#L94>

        types = [

  95<http://bazaar.launchpad.net/%7Eopenerp/openobject-addons/6.0/view/head:/account/report/account_balance_sheet.py#L95>

            'liability',

  96<http://bazaar.launchpad.net/%7Eopenerp/openobject-addons/6.0/view/head:/account/report/account_balance_sheet.py#L96>

            'asset'

  97<http://bazaar.launchpad.net/%7Eopenerp/openobject-addons/6.0/view/head:/account/report/account_balance_sheet.py#L97>

        ]

so where is this new equity type? Equity is no longer on the balance sheet?

You have function called sum_cr which actually sums debits and vice versa.
You have a balance sheet where the totals do not add up to the amounts above
it (depending on profit or loss)

If your bank account is $1000 overdrawn, it will show as being $1000 in
credit.
If you generate a loss or a profit, you add or subtract it from different
sides of the ledger

Vinay - this is fundamental errors, not wishlist or opinion.

On Fri, May 20, 2011 at 1:04 AM, Vinay Rana (openerp) <
<email address hidden>> wrote:

> Hello Graeme Gellatly,
>
> I know the balance sheet formula :
> Assets = Liabilities + Shareholders' Equity
>
> Current report is supporting equity type.The only thing remain is set the
> proper configuration in user defined account type form.
> The existing equity type is not shareholders equity.If you want this as
> shareholders equity then you just need to change the selection 'Balance
> Sheet(Liability side).'
> I have set this as a opinion for other thing.
>
> We already assigned one of the following wishlist bug for sign related
> issue:
> https://bugs.launchpad.net/openobject-addons/+bug/740132
>
> Hope this will help you
> Thanks.
>
> --
> You received this bug notification because you are a direct subscriber
> of the bug.
> https://bugs.launchpad.net/bugs/784339
>
> Title:
> [stable 6.0.2] Account Balance Sheet parser logic error leads to
> incorrect totals amounts and developer confusion
>
> Status in OpenERP Modules (addons):
> Opinion
>
> Bug description:
> If you look at the section where it calculates sum_dr and sum_cr it is
> incorrect. Well it is technically correct, but in the sense of having
> a balance sheet that balances it is incorrect.
>
> Take the accounting equation property = property rights expanded to
> assets = liabilities + equity
>
> In the case of a net profit we incorrectly add the result to sum_dr,
> our equity has increased afterall, so sum_cr should increase, (this
> behaviour is correct, except the function is named wrong) however in
> the case of a net loss we subtract the amount from sum_cr (which
> should be sum_dr) which is incorrect as our equity has reduced.
>
> Howev...

+1

2011/5/19 Graeme Gellatly <email address hidden>

> Vinay,
>
> You don't get it, I couldn't care less what types you have. They don't
> matter. You have serious bugs in the report and some more minor ones I
> don't report yet, like you cannot filter a balance sheet between 2 periods
> or dates, it is a point in time report. And anyway I just check latest
> 6.0.2 sources again and in the balance sheet parser your types are still
> hard coded as 94<
> http://bazaar.launchpad.net/%7Eopenerp/openobject-addons/6.0/view/head:/account/report/account_balance_sheet.py#L94
> >
>
> types = [
>
> 95<http://bazaar.launchpad.net/%7Eopenerp/openobject-
> addons/6.0/view/head:/account/report/account_balance_sheet.py#L95>
>
> 'liability',
>
> 96<http://bazaar.launchpad.net/%7Eopenerp/openobject-
> addons/6.0/view/head:/account/report/account_balance_sheet.py#L96>
>
> 'asset'
>
> 97<http://bazaar.launchpad.net/%7Eopenerp/openobject-
> addons/6.0/view/head:/account/report/account_balance_sheet.py#L97>
>
> ]
>
>
> so where is this new equity type? Equity is no longer on the balance
> sheet?
>
> You have function called sum_cr which actually sums debits and vice versa.
> You have a balance sheet where the totals do not add up to the amounts
> above
> it (depending on profit or loss)
>
> If your bank account is $1000 overdrawn, it will show as being $1000 in
> credit.
> If you generate a loss or a profit, you add or subtract it from different
> sides of the ledger
>
> Vinay - this is fundamental errors, not wishlist or opinion.
>
> On Fri, May 20, 2011 at 1:04 AM, Vinay Rana (openerp) <
> <email address hidden>> wrote:
>
> > Hello Graeme Gellatly,
> >
> > I know the balance sheet formula :
> > Assets = Liabilities + Shareholders' Equity
> >
> > Current report is supporting equity type.The only thing remain is set the
> > proper configuration in user defined account type form.
> > The existing equity type is not shareholders equity.If you want this as
> > shareholders equity then you just need to change the selection 'Balance
> > Sheet(Liability side).'
> > I have set this as a opinion for other thing.
> >
> > We already assigned one of the following wishlist bug for sign related
> > issue:
> > https://bugs.launchpad.net/openobject-addons/+bug/740132
> >
> > Hope this will help you
> > Thanks.
> >
> > --
> > You received this bug notification because you are a direct subscriber
> > of the bug.
> > https://bugs.launchpad.net/bugs/784339
> >
> > Title:
> > [stable 6.0.2] Account Balance Sheet parser logic error leads to
> > incorrect totals amounts and developer confusion
> >
> > Status in OpenERP Modules (addons):
> > Opinion
> >
> > Bug description:
> > If you look at the section where it calculates sum_dr and sum_cr it is
> > incorrect. Well it is technically correct, but in the sense of having
> > a balance sheet that balances it is incorrect.
> >
> > Take the accounting equation property = property rights expanded to
> > assets = liabilities + equity
> >
> > In the case of a net profit we incorrectly add the result to sum_dr,
> > our equity has increased afterall, so sum_cr should increase, (this...

Hello,

There are little bit confusion for me for equity user defined type in account, If this is Shareholders' Equity then it should be link with liability side for Balance sheet report.Currently It is wrongly assigned as a assets side.so the report will be display wrong account.

Thanks.

I have the same problem!

Just simple test on default demodata will reveal the problems.

Balance sheet is not calculating correctly at all.

Testing on 6.0.2.

If you don't agree, please post a simple test that proves me wrong.

Hello,

To Fix this bug i am setting all equity kind off account type's category to liabilities in
lp:~openerp-dev/openobject-addons/trunk-bug-784339-psi branch

All other Balance sheet improvements will be merge into addons very soon.

Revision No: 4814
Revision ID: <email address hidden>

Thanks
Purnendu Singh

I am affected by this bug too.
Server version 6.0.3

I have a P/L account Foreign Exchange Loss.
Losses, so it is an expense.
When I receive a foreign payment and the exchange rate happens to be in my advantage, the loss is negative.

So when booking the payment, the difference is Credit.
The balance of the Foreign Exchange loss is negative in the G/L

When I create the P/L sheet, the sign on the Foreign Exchange Loss acct is lost, and
this amount is substracted from my net profit. That is stupid.

What is worse is that my Balance sheet is now out of balance, and shows two different totals.
That is because the Net Profit is calculated incorrectly.

Apparently the Net Profit is calcultated first and then transferred to the Balance sheet.
The other accounts are transferred to the balance sheet as well and calculated independently.
With the wrong figure for Net Profit it is apparent the wrong balance totals will appear.

I can't believe this is a DISCUSSION whether this is a bug or not. In double accounting ALL
accounts can have a negative value. It is preferred that it is not PRESENTED as such,
but it is incredible that a CALCULATION goes wrong on this.

What am I to do? Create 2 accounts, one for Foreign Exchange Losses and one for Foreign Exchange Gains, and book on
each of them (only debit on one, only credit on the other) dependent on the exchange rate?

jlinkels

Hi Jlinkels,

sadly, the milestone on this bug is 6.1. Given the usual policy, no backport of the solution to 6.0.x is to be expected from OpenERP SA unless someone with a support contract starts making a lot of noise.

The solution which I proposed which is relatively easy, is here: https://bugs.launchpad.net/openobject-addons/+bug/740132/comments/3. Mind that you do need to take out the calls to abs() from the balance sheet rml files, apart from applying the patch.

Cheers,
Stefan.

I missed this message somehow, hence my delayed answer.
It would have saved me a lot of frustration if I had seen it before.
I will apply your patch and see how it works.
The removal of abs() in the rml is obvious.

I'll report back after testing.

jlinkels

Stefan,

I think there is some confusion.

I have noticed that
(a) the balance sheet is wrong by not displaying the correct balances (https://bugs.launchpad.net/bugs/930283)
(b) the P/L sheet is wrong by not processing the sign of expense/income accounts (https://bugs.launchpad.net/bugs/930739)

THIS thread we are posting in now describes bug (b) taking abs() values in the P/L sheet.
As it seems, your patch is for solving bug (a)

Applying your patch aborts with an error message (unexpected EOF) but I think it is only the lines with the '-' and '+' character in front, and the rest is just context, right?

Let me have a look in the account_profit_loss.py if this issue is solved. As far as I have seen, the issue is still under discussion.

BTW, just to be sure, I have checked two other accounting programs (SQL-ledger and xTuple Postbooks), and both handle a negative amount on an expense account correctly (and a negative balance on the balance sheet, that is). So I think there is little doubt about what the correct processing and display should be.

jlinkels

I mean:
Let me have a look in the account_profit_loss.py of version 6.1 if this issue is solved. As far as I have seen, the issue is still under discussion.

Hi Jlinkels,

sorry about the patch. It is not a valid patch file but a readable digest of one. You can apply it manually, it is indeed only the one line.

If I am confused about all the issues surrounding the 6.0 financial reports, it is because they were reported so many times and never fixed.

We have ported the reports from 6.0 to 6.1 and fixed the issues so that they seem to work for us. You can find them here: http://apps.openerp.com/addon/6715. I will try your scenario one of these days and see if the module can be further improved (apart from adding the translations)!

Cheers,
Stefan.

Hmm, I have expected that I would find the file in ~openerp/openobject-addons/trunk. Am I mistaken?

jlinkels

The location depends on your setup. Look for your addons directory under 'account'.

Thanks for sending me.

Unfortunately I was not successful in running you r module. I had to make these changes in order to get the module loaded:

-> In openerp/addons/account/report/__init__.py I had to comment out:
#import account_balance_sheet
#import account_profit_loss
because the new modules would conflict with existing modules

-> openerp/addons/account_report_alt/report/account_balance_sheet.py I had to make this change:
#from openerp.addons.account_report_alt.report import account_profit_loss
import account_profit_loss
because the server complained it couldn't find openerp.addons.account_report_alt.report import account_profit_loss

Error #1 is of course related to #2, but I fail to see what is wrong with the path openerp.addons.account_report_alt.report.

Anyway, WHEN I have it up and running, I get this error:
[2012-02-17 14:18:34,408][demo] ERROR:web-services:[11]: File "/usr/share/pyshared/openerp/addons/account_report_alt/report/account_profit_loss.py", line 63, in set_context
[2012-02-17 14:18:34,408][demo] ERROR:web-services:[12]: new_ids = 'chart_account_id' in data['form'] and data['form']['chart_account_id'] and [data['form']['chart_account_id'][0]] or []
[2012-02-17 14:18:34,408][demo] ERROR:web-services:[13]: TypeError: 'int' object is not subscriptable
> /usr/share/pyshared/openerp/netsvc.py(131)abortResponse()
-> raise
Which seems to me either a version problem or a wrong object class, maybe because I changed the wrong things.
I think the errors are because my lack of understanding.

However, when I look into the code of account_profit_loss.py, it seems to me that still the absolute value of the account balance is being used to calculate net profit or net loss.

In the mean time, I got some replies on my own bug report https://bugs.launchpad.net/bugs/930739 and in the OpenERP forum.

Unless the error is solved sooner, I would be gratefule if you could reproduce the bug in the demo version and confirm. I noticed you are from the Netherlands as well, so may it is a Dutch problem that we want to have negative balances on Expense accounts.

Just kidding, the demo database I use is English language with a generic accounting scheme.

jlinkels

Hi Jlinkels,

sorry if I was not clear about this, but these 6.1 modules can not be installed on 6.0.

I think that the modules still need to be fixed with regards to this particular bug, and I will try to have a look soon.

Regards,
Stefan.

> sorry if I was not clear about this, but these 6.1 modules can not be installed on 6.0.
Guessed so. Never mind. This refreshed my knowledge about how to add modules anyway.
I will keep posting

jlinkels

Stefan,

I have subscribed you to the other bug report so you can continue to follow that discussion. To avoid confusion I won't post about the P/L problem here.

In the mean time there was some discussion on the OpenERP forum, which shows that in 6.1 the account reports and settings have been changed. Still not without errors though.

Hans