Everybody needs this looks like. Somebody start a bounty. I’m sure it will get funded enough for it to get built quickly.
Thanks
Jay
1 Like
I will get the bounty started! Revant was beggining some work on it, I think he eill be happy with a Bounty!
Update :
Prerequisite for Consolidation, Ledgers must match,
Added Account No. field to Account and show it in trial balance,
Accountants can download trial balance for each company and compare / process accounts based on their number instead of name.
https://bitbucket.org/mntechnique/ixsystems_erpnext/wiki/Home
4 Likes
Great work revant_one, account number will help in the consolidation efforts for the account matching table for subsidiary companies!
any update on this issue?
Can we have a pull request please?
Account numbering needs more thought if it becomes default feature.
Right now there is just a custom data field for account number, Accountant needs to carefully set number for each account. There are no validation on hierarchy/series of numbers given to account.
Community inputs on numbering?
Numbering will not only solve consolidation of FS but will solve basic accounting presentation problems currently faced in erpnext.This is a much desired feature
Account numbers are assigned by the accountants, and don’t need any validation. They should be used to sort the trial balance and general ledger reports, which are more appropriate than financial statement groups. Financial statement groups should be used for financial statements only, and not conflated together with the general ledger, which are 2 different things. In most accounting systems, financial statements are a separate module altogether and is a reporting function only. If you are not sure, you could make them optional, but they will be required if you are doing consolidations. You can’t rely on description to determine which accounts to add together. They may be described differently in different companies. They may even be in a different language. For example, you are consolidating a french company with an english one.
2 Likes
I have some thoughts on this as I’ve had to tackle it from both an accounting and a data science perspective:
-
The ‘mapping engine’ that has been worked on for the QuickBooks connector can also be used here: the user(s) need to define what accounts map to what, but this is usually a one-time activity that gets tweaked (like a handful of accounts that are added, deleted or merged) annually or per consolidation period. This approach solves @fred concern as well.
-
On my project we had a different take on @Tropicalrambler idea of defining companies through stock ownership, instead just creating an adjustment column that allows for annotation. We were doing it in Excel but I understand there is some off-the-shelf accountant report prep software that does it better. Format wise, I absolutely prefer footnotes to inline or Excel comments, but sometimes that’s the only option. The stock idea doesn’t really hold with smaller companies or a mix of sole proprietors and LLCs as you try to come up with a value for “stock” that’s really just represented as a $ of owner equity.
-
In my project, the consultants defined a common chart of accounts so that the 45+/- companies could be benchmarked to each other, both in financial and production denominators. But that’s essentially the same use case, only with an averaging step added. So having a user-definable chart of accounts for consolidation is key and leveraging one companies default COA is probably not a great idea, though it would work fine in many cases.
-
From my CFO days, having an explicit way to adjust inter-company notes between two+ companies being consolidated was crucial, especially for measures of liquidity (quick ratio, etc) in the monthly lender reporting I had to do.
@revant_one @rmehta @Tropicalrambler or whomever else is contributing to this, I would be happy to help, please DM me with an assignment or if I can contribute to a code review, or bring tea, whatever needs doing.
This is a great feature! Let’s get it done!
- On my project we had a different take on @Tropicalrambler idea of defining companies through stock ownership, instead just creating an adjustment column that allows for annotation. We were doing it in Excel but I understand there is some off-the-shelf accountant report prep software that does it better. Format wise, I absolutely prefer footnotes to inline or Excel comments, but sometimes that’s the only option. The stock idea doesn’t really hold with smaller companies or a mix of sole proprietors and LLCs as you try to come up with a value for “stock” that’s really just represented as a $ of owner equity.
I understand that upfront this would seem like a gargantuan task, thus creating an adjustment column is the easy way. The reason I am exploring, using and contributing to ERPNext is precisely because I dislike hearing: that “it is not feasible” or “it’s too complicated” Now, what I propose is not to track stock value but track quantity and type of shares plus their relationship amongst companies defined within ERPNext. For the basic use ERPNext can calculate a proportion. This is a simple mathematical calculation. The logical endgame for this is for ERPNext is to have the capacity to keerp track of the growth into a multinational which owns other businesses, etc. perhaps for ERPNext V10 (2018)?
I am running ERPNext for one company whose ownership structure is 51-49. Right off the bat, having a way to track ownership for me is apparent. Sure, the project is small, but I am already thinking on starting “company B”. So at some point I might be running about 10 companies / Foundations with all the projects I have in mind. Some are sole propietorships: 100% stake. Others are corporations that involve simple structures: like my 51-49%. I would really like to have ONE place to have all the information, and not have to go through the " combine in excel" thing, which is prone to a lot of errors. Sure, in the short term it is much simpler, but my philosophy is: program it once, use it often with the time savings it involves (not another excel to keep track of the shares outstanding!)
Clearly, when consolidating statements individual adjustments will be necessary each cycle, thus, if those adjustments are made explicit within ERPNext, the confidence in the financial status of the company for stakeholders will increase or remain unscathed. I have already had too many instances of cooked numbers, even with “respectable” auditing firms involved, and having a system implemented within ERPNext, that at the very least keeps track of which adjustments were made, makes for a robust financial accounting solution. The whole idea is for ERPNext to “suggest” a mapping, but the user can "create a consolidation"with modifications, and then Validate it as other DocTypes in ERPNext.
So from the looks of it, we need to have a separate module for Financial Accounting / consolidation. This module shall include:
-
Company relationship definition
-
Stock Types (a Share is a type of stock item which is serialized. It has a face value, not intrinsic.) And it also has characteristics that give ownership rights (or not) depending on the type.
-
Mapping engine á la Quickbooks for consolidation using a suggested common chart of accounts, but allowing the user to map on his own so as to create the inter-company notes necessary for correct accounting of those items.
So here is my plan: I have hired a local developer to help me do the crunch work of programming, and as I move forward with customizations I will be posting code so you guys can comment, adjust, etc. revant_one has already advanced a lot in code, however I will set up a “test” ERPNext server version where we can try all the features and do the necessary debugging as we develop these features, until we can either have a Pull request done for the main branch of ERPNext, or keep it as a separate, but open source module that can be added to ERPNext.
I think there are two things here that are worth pursuing and they are at this time worth separating.
- The owner idea with stock or percentage contributions is a core ERPNext idea and should be incorporated into future versions. It should probably be in its own module until then.
- I think a consolidation module should be a separate app at this time and may be pulled in, but many/most(?) ERPNext users are not working with multiple companies that require consolidation. I could be wrong about this, and I suspect that the Frappe team has some insight into how wrong.
Something that isn’t really in the ERPNext Ecosystem is the idea of recommended apps, which leads to an ever-growing core.
Hacking something together in Excel often a solution, but that doesn’t mean it’s a good one. I am happy to say that ERPNext has raised my expectations in that regard.
-T
Consolidation should be a part of the core. Like we have said, the direction is to build a larger core with parts that can be switched off when not needed.
Apps have 2 big problems
- Duplicity - people will build alternate apps doing the same thing
- Maintenance - not all apps will be maintained by the owner.
Again. I am hijacking, but it seems consolidation is a regular request, and if someone has taken a shot at it, they should send a pull.
Rushabh, you are not hijacking, thanks for attention to this.
I have hired an in house developer (he’s learning the ropes in Frappé) to help me with such tasks. revant_one has already started with some basic things, and here is the Bounty for this particular feature.
Bountysource [Now $100!]
This would empower ERPNext considerably, since it already has multiple company support. We will send a pull request when ready.
To continue the discussion and ensure that the programmed features comply with international standards such as IFSB, I realized today that what’s really necessary is to have at the very least, two new reports for each Financial Statement: Income Statement, Balance Sheet, Cash Flow. Al these should be listed in Accounts module of ERPNext.
Report type 1
Consolidated Financial Statements
This report type would simply aggregate the financial positions of a parent company and its subsidiaries. This shows the user the overall health of the group in a holistic manner, without having to view each individual subsidiary company’s financial statements separately. User can compare years, quarters, months. Just like the existing reports in ERPNext. An added feature would be to select individual periods for comparison, amongst themselves.
Report Type 2
Combined Financial Statements
This report would show the complete financial results from each subsidiary as a separate statement, on the same report page. At the last column, it could show the consolidation of all the accounts as an aid. to compare individual to overall performance. User can select which companies to show or hide.
I referred to this Website
I also referred to this handy and clear video on how to consolidate in Quickbooks, which helps to envision the process for what we are aiming to do.
I am attaching screenshots of a mock up of how the Combined Statement should look…
Closer (part 1)
Closer (part 2) (with optional message…)
Notice that I intentionally altered some account numbering schemes to reflect dissimilar COA, but with the mapping feature where accounts from “Company B” are mapped to “Company A” group accounts, this should not be a problem.
5 Likes
thanks for the work, as most of the world countries are joining IFRS this would be the bbiggest add point for ERP Next as consolidation finacial report for Corporate companies which have multiple sister companies under it is manadatory on IFRS.finally is there any update on this issue?
Is it okay for new version of ERPNext ?
Is there any update for consolidation?
Good day everyone,
Very good feature this is. Do we have an update on this?
Thanks
Consolidation is installed but unable to see from where we can get the report
Hi, is this feature done? I see @Parag_Kapoor installed it somehow. Can you guide me on how to test it as well?