frappe / erpnext

Free and Open Source Enterprise Resource Planning (ERP)
https://erpnext.com
GNU General Public License v3.0
21.55k stars 7.29k forks source link

Cost-Center (Dimensional) Trial Balance and Other Financial Reports #12596

Closed jk2081 closed 6 years ago

jk2081 commented 6 years ago

This is in continuation to related issue of improved financial reporting and has been discussed extensively as part of the Accounting module group discussions. Essentially, we would like to be able generate cost-center/geography/customer (all being part of a set of so-called dimensions) wise trial balance and other financial reports.

Currently Sage Intacct does this through dimensional reporting. A video explaining the use case is here: https://www.youtube.com/watch?v=6JwlGdysQBE&feature=youtu.be

I think we are well on our way on there with the cost-center field being part of the GL. Perhaps a good first step would be to make cost-center tagging a must for all GL entries in a system-wide setting and use that to generate the reports. This can be then extended to other popular dimensions.

jk2081 commented 6 years ago

Some information on how Quickbooks does this based on work from Liyakat from the accounting team.

Definition of what information we would like to track.

I Suggest start with cost-center on line-item level.

screen shot 2018-01-23 at 7 47 55 pm

On the Doctype, how it would show up

screen shot 2018-01-23 at 7 48 36 pm

screen shot 2018-01-23 at 7 48 47 pm

On How it would enable different Reports

screen shot 2018-01-23 at 7 48 56 pm

jk2081 commented 6 years ago

On further discussion, it seems like the major gating factor is the current lack of tagging of cost center/warehouse on Balance Sheet items such as Debtors, Creditors, Employee Loans, Advances, Stock etc

One suggested approach to remedy this is to have a cost-center master wherein cost-center specific sub-ledgers can be selected as the default accounts for that cost center and users can be restricted by cost center or select a cost center at login time so that all transactions are tagged accordingly.

That way these reports would be possible.

Please do note that inventory related balance sheet items such as stock details can be queried from the warehouses/stock ledger.

PawanMeh commented 6 years ago

I think from an accounting standpoint, balance sheet transactions generally do not have attributes like cost center etc. they are more needed on P&L side for operational purposes.

I am not sure if this requirement is only about adding cost center filters on certain reports where it is not present or it is to add these to balance sheet account transactions.

vladucoju commented 6 years ago

Erpnext is a unique model. We should use some features from other erp solutions as a example, not to copy everything. I think we must talk about generic features. To fix main problems. I will mention some:

ushashmi commented 6 years ago

Instead of restricting only to cost centers can we look in to incorporating Analysis Codes (T codes) like Infor Sun Financials? Without any restriction of being applied to any document type and General Ledger Account Codes including Balance Sheet and P&L.

sanjay-hercules commented 6 years ago

We are planning to implement Business Unit (Branch) functionality which will facilitate branch-wise accounting by using single chart of account. User would be able to select default bank, warehouse accounts and letterhead at BU level.

BU can be created under Division and Territory (Location/Region). All financial reports will have filter of BU/Division/Location.

We will share design document shortly for review.

kossanah commented 6 years ago

This is a great news. It will be helpful to make easy to track branch-wise expenses, allocate a branch petty cash.

PawanMeh commented 6 years ago

Company tree maybe a better way to go in this case as business units are equivalent to company in ERPNext. You have cost center and department too that you can use as other dimensions.

sanjay-hercules commented 6 years ago

Yes Pawan, BU will be having tree structure like Company >> Region >> Division >> BU. We are planning to keep Cost Center and Department independent of BU.

pm-at commented 6 years ago

Would love to see this extend to HR/Payroll where for scenarios where BUs represent Company structure better than Departments

sanjay-hercules commented 6 years ago

Dear All, please find attached and review draft version of BU implementation document. business_unit_draft_v1.0.docx

Please let us know your feedback on this.

rmehta commented 6 years ago

@sanjay-hercules lets use "Department", now that it is a tree.

We will need to add department wherever cost center is present.

Lets open a new issue for this. Also best to avoid attaching word docs in issues as they are not searchable 😉

sanjay-hercules commented 6 years ago

1 Objective The main objective of implementation of Business Unit in ERPNext is to facilitate branch accounting using single chart of account.

This will help user to :

Currently in ERPNext branch accounting functionality is not available, however user can use Cost Center as a branch to record income and expenses at Cost Center level. But if user want to maintain branch-wise bank, warehouse, receivable and payable then it is not possible by using Cost Center. In this case, he/she has to create company as a branch which makes branch-wise data presentation and consolidation difficult.

2 Assumptions

3 Tagging A business unit can be tagged under a region or a division or both. This tagging will help user to see region and divison wise summary of transactions and financial reports.

4 Hierarchy image

image

5 Setup User is required to setup Region and Division before setting up of a Business Unit.

5.1 Region (Territory)

image

Note : Territory Target should be defined at company level.

5.2 Division

image

5.3 Business Unit

A new doctype will to be created with below details: image

sanjay-hercules commented 6 years ago

@rmehta do you mean to say use Department instead of Division?

rmehta commented 6 years ago

I think we can use department in the GL, now that we are converting it into a tree structure.

See: https://github.com/frappe/erpnext/pull/13567

sanjay-hercules commented 6 years ago

below is draft version of BU doctype where BU is tagged to territory and department (division): business_unit_v_1 0

sanjay-hercules commented 6 years ago

Please review above and let me know if it is okay.

rmehta commented 6 years ago

@sanjay-hercules how is this linked to the GL? Can you start a PR?

sanjay-hercules commented 6 years ago

@rmehta I have not yet done GL linking part. I don't think department can be used at level 3 because in our case it should be at lowest level. Also branch is currently being used in various HR documents so instead of creating Business Unit we can enhance Branch for this purpose.

bu_structure

sanjay-hercules commented 6 years ago

I think it would be better if we go with above approach. In this case, GL Entry will have reference of :

rmehta commented 6 years ago

@sanjay-hercules but the sub depts could be departments too, right like

Branch X - HR Branch Y - HR

etc?

ushashmi commented 6 years ago

great job @sanjay-hercules

Instead of restricting to one standard structure which is applicable to few companies can we look in to incorporating Analysis Codes (T codes) like Infor Sun Financials? Without any restriction of being applied to any document type and General Ledger Account Codes including Balance Sheet and P&L.

A company can define their own analysis codes depending upon their business and operational requirements. The analysis codes can be ranged from T1-T10 where in T1 can be Business Units, T2 Can be Division, T3 can be Cost Center, T4 can be Project, T5 can be Employees etc. etc.

The good thing about T codes (Analysis codes) is that they can be molded to any company’s operational and business requirements. Additionally, the analytical reports can be easily generated which can be used for strategic decision-making purposes.

For reference you can check https://docs.infor.com/sunsystems/6.3.x/en-us/ssolh/lsm1457708075704.html

sanjay-hercules commented 6 years ago

No @rmehta , department couldn't be considered as branch because branch is a revenue generating unit which can have its own payable/receivable/stock/bank accounts.

rmehta commented 6 years ago

Why not just give those properties to department too? its just a way of naming things.

sanjay-hercules commented 6 years ago

@rmehta department is a functional unit where is branch is an operational unit of a company. Also if we use those properties in department then how can an employee be tagged in HR Dept of Branch X?

sanjay-hercules commented 6 years ago

@ushashmi , that link is not working.

ushashmi commented 6 years ago

Here is a link

https://docs.infor.com/sunsystems/6.3.x/en-us/ssolh/lsm1457708075704.html

pm-at commented 6 years ago

@sanjay-hercules: Works if you copy-paste the url in browser.

sanjay-hercules commented 6 years ago

demo of above changes as below cost_center_changes

jk2081 commented 6 years ago

@sanjay-hercules Would a user be able to select or set a cost center at login so that a default cost center could be set in the transactions. I know that this would eliminate a lot of clicks and potential incorrect entries.

sanjay-hercules commented 6 years ago

@jk2081 No, this change will not make cost center available in login page. You can setup default cost center at user level through User Permission.

jk2081 commented 6 years ago

Closing this as @sanjay-hercules has sent a PR and it has been merged for release in v11.

nabinhait commented 5 years ago

17769