openerp-expert-accounting team mailing list archive

[Ferdinand Gassauer <office@xxxxxxxxxx>]

Am Mittwoch 31 März 2010 12:41:48 schrieb Borja López Soilán (Pexego):
> Hi Ferdinand,
> 
> I'm not sure if I understand what are you trying to accomplish: Are you
> trying to have the period sums of the accounts precalculated so when
> doing the fiscal year closing (or printing balance reports) only the
> last period values have to be considered? Or do you mean than in Austria
> you do a sort of period-closing?
> 
> I think I better explain how spanish companies are required to work (by
> the spanish general accounting plan), and how most of the programs I know
>  :)
> 
> 
>       How do we work on Spain
> 
> First of all, in Spain we have a complex minimal tree-like account chart
> (take a look here: http://www.gabilos.com/webcontable/indexn.htm). Small
> and medium business are allowed to use a simpler chart (mostly removes
> the "8" and "9" accounts). This chart of accounts is required (you can't
> delete or modify these accounts, but you may create new sub-accounts) by
> Hacienda (the spanish Treasury Department) as it's used to provide
> several official reports.
> 
> 
>         Period operations
> 
> Every period (quarterly for S&M business, monthly for big ones) we must
> report to Hacienda (Treasury) this official reports (there are more, but
> these are the important ones):
> 
>     * Report of the taxes by tax code (i.e. sum amounts of the taxes and
>       base amount at 16% rate, at 7% rate, etc...).
> 
>       /Also called "303 model". This report
>       (//https://www2.agenciatributaria.gob.es/es13/h/ie93030b.html)//
>       currently has to be filled in by hand on OpenERP >_< with the
>       values taken from the "taxes report" from the account module
>       (though it doesn't seem to work for me: too slow and the RML
>       doesn't seem to be properly parsed, instead of the amounts it
>       spurts things like this:   "[[ [[  [[formatLang(o['debit'])
>       formatLang(o['credit'])]] formatLang(o['tax_amount']...").
>       /
>     * Report of all the invoices received and sent with detailed taxes.
> 
>       /Also called "340 model". A (non-official) report with mostly the
>       same info can currently be printed with the
>       account_financial_report module. And there is a
>       l10n_ES_aeat_mod340 being developed on the spanish localization
>       (though it is not currently usable) to generate the official
>       report file (an ASCII file with fixed length records).
> 
>       /
>     * Report of retained taxes to your employees (part of the employee
>       salary must be retained and delivered to the Treasury every period).
> 
>       /Also called "110 model". It has to be filled by hand on OpenERP//
>  :(/
> 
>     * Report of profits from real state rentings
> 
>       /Also called "115 model". Currently there is no way on OpenERP to
>       record or tell apart 'real state renting' invoices :( so it is
>       obvious that this report must be generated by hand.../ :(
> 
> That means that (after) every period (when we have already received all
> the invoices of the period) we need to calculate the sums of the tax
> accounts on that period ("477" and "472" accounts) and leave them balanced.
> 
> (Notice that we don't calculate the P&L or the result of the fiscal year
> or anything else here: just taxes)
> 
> 
>         End of year (fiscal year closing) operations
> 
> After the end of year (actually in March) some yearly reports must be
> sent to Hacienda (Treasury) or the Registro Mercantil (Commercial
>  Registry):
> 
>     * P&L: Detailed report of the profit and losses by concept (not by
>       account!, a concept is a sum of the values of several accounts) of
>       the closed fiscal year compared to the previous fiscal year.
> 
>       /This report can be created using the account_balance_reporting
>       module (that serves as a report engine) and the
>       l10n_ES_account_balance_report module (the report templates for
>       Spain). It gets the info from the "6" and "7" accounts.
> 
>       /
>     * Net P&L: Detailed report of the profit and losses on the 'net'
>       accounts by concept. Similar to the P&L report, but only for big
>       companies with "8" an "9" accounts.
> 
>       /This report can be created using the account_balance_reporting
>       module (that serves as a report engine) and the
>       l10n_ES_account_balance_report module (the report templates for
>       Spain). It gets the info from the "8" and "9" accounts./
> 
>     * Balance: Detailed report of the balance of all the accounting by
>       concept (for example there is a concept "suppliers" that is the
>       sum of the 400, 401, 403, 404 and 405 accounts minus the 406
>       account) compared to the previous fiscal year. This report must be
>       done after computing the P&L and, as the report name states, all
>       the concepts should be balanced.
> 
>       /Again, this report can be created using the
>       account_balance_reporting module (that serves as a report engine)
>       and the l10n_ES_account_balance_report module (the report
>       templates for Spain). It gets the info from the "1", "2", "3", "4"
>       and "5 " accounts.
> 
>       /
>     * The Memory: A complex report with several subreports that compare
>       concepts with the previous fiscal years, and that details
>       everything worth to notice about how the accounting was performed
>       (for example if you change an account code you will have to
>       explain here why).
> 
>       /This one is currently not supported on OpenERP, but I doubt that
>       small business generate it by themselves (they usually have an
>       external accounting manager/adviser)./
> 
>     * A report of some of the operations (sales, purchases, received
>       payments) with partners. For example we must report all the
>       parners that invoiced us (or that we invoiced them) more than
>       3005.06 euro, all the partners that payed us more than 6000 euro
>       in cash, etc.
> 
>       /Also known as "347 model". This report may be generated using the
>       l10n_ES_aeat_mod347 module from the spanish localization.
>       /
> 
>     * Summaries (complete fiscal year versions) of some of the "by
>       period" reports.
> 
>       /For example the// "390 model" is a //summary// of the "303
>       models", the// "190 model" is summary of the "110 models" of the
>       fiscal year, and the "193 model" is a //summary// of the "115
>       models"//./
> 
> 
> There are more 'models' and reports (I think the accounting in Spain is
> too complex), but those are the important ones, and should be enough to
> give the accounting experts a good idea of why do the things we do.
> 
> 
> So, this is what we do when closing the fiscal year (after 'end of year
> operations' like the inventory, the assets amortization/depreciation...):
> 
> - Generate the P&L report.
> - Do the P&L "regularization": Sum all expenses and purchases accounts
> (those that start with "6") with the profit and sales accounts (those
> starting with "7") and store the 'profit of the fiscal year' into
> another account ("129") that would be used later on one of the concepts
> of the 'Balance' report.
> 
> - Generate the Net P&L report (if needed).
> - Do the Net P&L "regularizations" (if needed): Sum some net expense
> accounts (those that start with "8") and net income accounts (those
> starting with "9") and store that sums on several accounts
> ("800"+"801"+"802"+"803" into "133"; "810" + "811" + "812" into "134";
> "900" into "135").
> 
> - Generate the 'Balance' report.
> - 'Close' all the accounts: Sum all the balances of all the accounts
> (but the "6", "7", "8" and "9" accounts that already have been balanced)
> in a single account move (without using a centralized counterpart). If
> there are no errors in the accounting, the move should be balanced and
> all the accounts should have a balance of 0 after this operation.
> 
> If you think about how the accounting was done by hand (with pen and
> paper) this last move is indispensable if you want to assert that the
> accounting has no errors.
> 
> And this is what we do when opening the next fiscal year:
> 
> - 'Open' all the accounts: Make a move, inverse to the closing one, to
> restore the balance of the accounts.

But doesn't this mean that after closing a fy it's impossible to print the 
years end balance of these accounts ??? - except one defines a method (a 
special journal) not to add up the closing moves ???
For this a closing journal and an opening journal is needed.

> 
> *Notice this:* One account with debit "3000" and credit "1000" (balance
> 2000) will be closed with a debit "0" credit "2000" entry, and will be
> reopened on the next year with debit "2000" credit "0". _Currently
> OpenERP always shows the cumulative debit/credit from the beginning of
> the times, so it will display debit "5000" and credit "3000" instead of
> the expected D "2000" C "0" :(_

it's easy to implement this in my module (and I will do soon)

Thank you for the clarification


As I said in Austria it's not a requirement to create these moves it is 
sufficient that closing balance and opening balance are identical.
You can do it but it's not a legal requirement. 

The problem is in OpenERP - the closing and opening moves are part of a 
"period" which IMHO must be kept clean. 
here all printed output shows at least the following columns
account name * balance carried forward * debit * credit * balance 
whereas debit * credit * balance  are the accumulated values for the requested 
periods of the fy.

IMHO in OpenERP it's sort a bit difficult to calculate the balance carried 
forward. 
 
> 
> 
>       What Spanish programs do to calculate some reports faster
> 
> All the accounting programs I know (ContaPlus, ContaSol, ClassicConta,
> Eurowin, Primavera) do the same: They have a table with the
> precalculated debit, credit, balance and total amounts per month!
> Every time you create or modify an account move, that table would be
> updated (sometimes using SQL triggers to ensure consistency).
Exactly !!
> 
> When you want to print a balance like report (a general ledger for
> example) for a given month, or simply want to see the current
> credit/debit of the account, the program only has to look at the values
> of that summary table (a single and simple query!) instead of
> recalculating the sums of each account from the beginning of time (that
> is what OpenERP does).
Did you look at my demo?
> 
> Even Postgres (which is quicker than I thought when doing this
> aggregates) has a hard time when an OpenERP user tries to browse a chart
> of accounts with thousand of accounts. We have a client that has like
> 3000 clients itself; opening the 'clients' branch in the account tree
> view brings Postgres and OpenERP to his knees, cause it just
> recalculates the debit and credit of 3000 accounts (Postgres would have
> to sum all the account move lines of those 3000 accounts) every time.
good to hear that I am not alone :-(
> 
> I think we could do something similar in OpenERP:
That's exactly what I am proposing (for a long time already)
> 
>     * Have precalculated debit/credit/balance totals per account and
>       period (i.e. an account_account_totals table with account_id,
>       period_id, period_debit, period_credit, period_balance,
>       period_draft_debit, period_draft_credit, period_draft_balance,
>       total_debit, total_credit, total_balance, total_draft_debit,
>       total_draft_credit and total_draft_balance fields).
>     * Use postgres triggers to update this totals (that would ensure
>       ACID) each time a account_move_line is created/updated/deleted.
the module is available at request as I am not sure yet if it is bug free as 
it must correctly handle current OpenERP's financial closing moves. 


>     * Use this precalculated totals on the account.account __compute
>       method (that is used on the debit, credit, balance function fields).

that' what I am proposing

> 
> That would pretty much speed up all the accounting in OpenERP. Just
> think about this:
> 
> Every time we want to get the current balance of some accounts OpenERP
> does a query like this:
> 
>     * "SELECT l.account_id as id, COALESCE(SUM(l.debit),0) -
>       COALESCE(SUM(l.credit), 0) as balance FROM account_move_line l
>       WHERE l.account_id IN (%s)  GROUP BY l.account_id"
> 
> With the precalculated totals table it would be a much (several orders
> of magnitude*) quicker query:
> 
>     * "SELECT t.account_id as id, t.total_balance FROM
>       account_account_totals t WHERE t.account_id IN (%s) AND
>       t.period_id = %s"
> 
> 
> /(*) We turn the O(n*m) complexity of the first query into O(n)./

Exactly 

we have implemented such sums already for very large companies and it works 
perfectly

> 
> 
> 
>       Summary
> 
>     * The way OpenERP calculates the credit/debit/balance is toooooooooo
>       slow, and can be improved a lot by using precalculated sums by
>  period. 
>  * The way OpenERP calculates the current credit/debit (taking into
>  account all the fiscal years instead of the current one) may not be valid
>  (or confortable) on all the countries. We should add a (configuration)
>  option to show only the current fiscal year
>       amounts (using precalculated sums it would be much easier to
>       implement).
You may want to look at the end of this page 
http://www.chricar.at/ChriCar/PartnerDayLayout.html
>     * The "close fiscal year" wizard of OpenERP is not valid on
>       countries that need to regularize, close and reopen the accounting.
>     * OpenERP lacks lots of accounting/financial reports.
> 
> 
> Well, I hope all this rants are useful for the OpenERP accounting experts
>  :) Best Regards.
> 
> Ferdinand Gassauer wrote:
> > Hello !
> >
> > I just would like to ask if Spain and other countries really require
> > account move lines for closing a fiscal year
> >
> > For some (also very big) companies here in Austria we do not create
> > closing moves for each account but on a permanent basis we calculate
> > period sums
> >
> > * for the fiscal year of the move
> >
> > ** P&L account for all P&L moves - showing the result of the fy
> >
> > ** Balance account - for all P&L moves - showing the result of the fy
> >
> > * for all following fiscal years (!) : balance carried forward - which
> > is hold in a special period '00'
> >
> > the big performance advantage is that for all period orientated
> > reports and views no detail moves have to be accessed.
> >
> > We calculate these sums when a move state is set to 'done'
> >
> > I have created a proof of concept but working module for the balance
> > carried forward (with PSQL triggers) which I can provide or can be
> > seen here
> >
> > Demo
> > <https://openerp.chricar.at/login?user=demoe&password=demoe&db=demo_ger_2
> >>
> >
> >
> > look at
> >
> > Finance / Charts / Accounts with postings
> >
> > look at 2010 - period 201000 holds the balance carried forward
> >
> > Please your opinion
> 

-- 
Best Regards

ChriCar Beteiligungs- und Beratungs- GmbH
http://www.chricar.at/ChriCar/index.html
Dr. Ferdinand Gassauer
Official Tiny Partner