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 _______________________________________________ Mailing list: https://launchpad.net/~openerp-expert-accounting Post to : [email protected] Unsubscribe : https://launchpad.net/~openerp-expert-accounting More help : https://help.launchpad.net/ListHelp
