Hi @lubos,
since the new reporting tool is opening a complete new word of possible tools, why not considering adding custom fields also to the chart of accounts elements so that we can do automatic reclassification of the numbers? I think that this will also permit creating a cash flow statement.
PS: I know I’ve requested it so many times but now I think that the times are ripe.
Yeah, there is solid use for it. When I see custom reports where 10+ lines are where general ledger account is not... it begs to be solved by custom fields.
However, because chart of accounts is a bit more complex, I need to improve foundation of custom fields. This will give me chance to address other feature requests such ability to do Sum operation on custom fields too.
Dear @lubos, can you please consider adding this feature quickly? It would ease a lot our job.
Would also help generating reports for the tax authority.
Their numbering, breakdown, grouping and ordering of income and expense accounts is different to the account grouping and ordering I want to clearly display business operation.
Use case: ATO Annual tax return submission
Manager functional requirements:
2 account custom fields
Custom report which sorts & groups on both custom fields, display account name and collapsing below that level (not showing actual general ledger transaction but allowing drill down).
The end results should be a custom report similar to a reorganisation of the COA to reflect the local tax authorities specific reporting requirements
Define two custom fields (type list), a skeleton for which is shown below. Note both have a “Not this business” option for income or expenses which are no deductible, or pertain to a different ATO reporting entity.
Both custom fields are then set for each profit and loss account in the Manager business
100 Income – ordinary
200 Expense – ordinary
300 Income – Supplementary
400 Expenses – Supplementary
900 Not this Business
101 Gross payments subject to foreign resident withholding (excluding capital gains) B
102 Gross payments where ABN not quoted A
103 Other sales of goods and services C
104 Gross distribution from partnerships D
105 Gross distribution from trusts E
106 Forestry managed investment scheme income X
107 Gross interest F
108 Gross rent and other leasing and hiring income G
109 Total dividends H
110 Fringe benefit employee contributions I
111 Assessable government industry payments Q
112 Unrealised gains on revaluation of assets to fair value J
113 Other gross income R
201 Foreign resident withholding expenses (excluding capital gains) B
202 Cost of sales A
203 Contractor, sub-contractor and commission expenses C
204 Superannuation expenses D
204 Bad debts E
205 Lease expenses within Australia F
206 Lease expenses overseas I
207 Rent expenses H
208 Interest expenses within Australia V
209 Interest expenses overseas J
210 Royalty expenses overseas U
211 Royalty expenses within Australia W
212 Depreciation expenses X
213 Motor vehicle expenses Y
214 Repairs and maintenance Z
215 Unrealised losses on revaluation of assets to fair value G
216 All other expenses S
412 Personal superannuation contributions
415 Other deductions (eg income protection)
910 Income not this business
920 Expenses no this business
Define a custom report
Time period: financial year for current tax return
Select… General Ledger Account – Code & Name. Amount
Where… General Ledger Account isProfitAndLossAccount isChecked
Order by… General Ledger Account Custom Fields ATO1 & ATO2, General Ledger Account Code & Name
Group by… General Ledger Account Custom Fields ATO1 & ATO2, General Ledger Account Name
This should display information with totals ordered and unlabelled ready to copy to your tax return. Account level breakdown should be summarised with drill down into accounts supported.
Next year, clone the report and update the time period.
Or better yet Manager’s custom reports could be enhanced to support “Add comparative column” time periods. Doing so would require splitting the “Select…” section into “For all time periods” vs “Column for each time period. Meaningful reports are also likely to all have “Group by…” and “Groups to collapse” both checked
I have achieved this already for a sole trader Australian tax return using 3 Custom fields (CF) in Non-inventory items. The 3 CF’ s are Tax Main, Tax Supplementary, and Tax Business, and a Custom Report (CR) is produced for these three categories.
There are some issues that I have had to overcome, partly because of restrictions to COA account access in transactions and partly because there are no global CF’s.
Problem 1 - No global CF’s
With Non-inventory items (you can select almost all accounts from the COA and with having 2 (or more) NII’s pointing to the same account it can be more granular than CF’s on COA. The problem is Inventory Items. The CF’s on Inventory Items is a different variable to CF’s on NII’s, so stymies you when creating a CR as it is not possible to filter all the required transactions.
The workaround remedy for Inventory Items is to create a nett zero Purchase invoice, at year end, using NII on one line and no NII on second line to go in and out of the same account. (see screen shot - lines 3 to 6). I would use a Journal entry (JE) for this, but JE’s do not allow NII’s.
Problem 2 - Restriction to COA account access in transactions.
It is not possible to select the COA account of Accumulated Depreciation in any transaction form. I wanted to use NII’s for depreciation entries so that I could use CF’s in these transactions, but because of this restriction I am forced to use the inbuilt Depreciation Entries, where an NII cannot be applied. I use the same workaround remedy mentioned above for this also. (see screen shot - lines 1 and 2).
The other functionality that I have built into the CF’s on NII’s is to to have a Part Tax Related (PTR) designation in the Tax Main CF and create a separate CR to see part tax related transactions on some expenses. I then include this in the nett zero Purchase Invoice as well (see screen shot - lines 7 and 8) based on percentage.
To overcome this issue I suggest that we need global Custom fields to allocate to every line in transactions (including the header line and other hidden lines)
To do this what is required is, when creating or editing a transaction, a “journal of entries” (all line items that will be generated by the transaction in brief journal format) be shown below the line items in the transaction form.
All the fields will be locked from editing except for a series (maximum could be set) of editable couplet “menu dropdown” fields (CF name and CF content) to select CF’s for each line. Users could have the option in the transaction to show or not show this functionality.
This would also help with tracking BS accounts that is not possible with Tracking Codes.
There would be no need to change the existing non-global CF’s and these could remain for other purposes.
Tagging individual transaction to identify there ATO tax classification is clearly a possible solution. It is an approach taken by some tax processing packages used by accountants but requires a suite of tools for efficient use.
I’m not convinced it is the easiest approach to use or develop for the multiple jurisdiction Manager supports.
The alternative is to map transactions to the ATO classification at the account rather than the individual transaction level. The advantage is there are relatively few COA accounts and they are relatively constant. The disadvantage is the user must construct their COA with fine enough granularity to enable assignment to the required ATO classification groups. In my opinion a reasonable requirement as having visibility of what the ATO sees is of value in it’s self.
Don’t underestimate Tax Offices propensity to complicate things down the line and look for more and more details
I think that there is a broader focus for changes in this area other than just tax items classification. Other users have different ideas for uses of Custom Fields on COA, and I am sure that if there was this ability users are going to devise other innovative uses for it.
I have done some more analyzing on this issue and have changed my thinking a bit, and this is what I’ve looked at:
Firstly, what is it that is needed or wanted?
Secondly, what is already in place?
Thirdly, what I propose is needed.
Take a Sales invoice for example for sale of 2 separate inventory items, with sale prices $10.00 and $8.00, cost prices $5.00 and $4.00 :
When you are in the create (or edit) mode for that Sales invoice you can see the 2 lines you have entered for each inventory item for which you can select a tracking code or a line item custom field. There are also other lines that are generated by this transaction that are not visible or partly visible.
DR AP Customer $18.00 (partly visible - Header part of transaction)
CR Inventory Sales (Item 1) $10.00 (visible - one of the lines in the Sales invoice)
CR Inventory Sales (Item 2) $8.00 (visible - one of the lines in the Sales invoice)
DR Inventory on Hand (Item 1) $5.00 - (not visible - hidden line)
CR Inventory Cost (Item 1) $5.00 - (not visible - hidden line)
DR Inventory on Hand (Item 2) $4.00 - (not visible - hidden line)
CR Inventory Cost (Item 2) $4.00 - (not visible - hidden line)
It is not possible to make selection of Tracking Code or Line Item Custom Field for the hidden or partly hidden lines.
Custom fields for the header/hidden lines can be populated from Custom Field definitions for Customers, Sales invoices and Inventory items [and Non-Inventory items if there was a line in the transaction for a Non-inventory item] ).
That makes sense.
Basically what you’re asking for is global custom fields + the ability to choose wether it applies to individual lines or the whole transaction + the ability for the lines to inherit the whole transaction custom field value, or something to that effect.
Thanks, @AJD. This discussion is a little bit ironic. What you advocate hints at the idea of doing everything by journal entry, with total control. I doubt things will move in that direction.
I suppose an alternative solution to my use case for custom fields on accounts is supporting Multiple COA structures.
A different COA structure could be defined for say
While that would work, it is debatable if user implementation in the long term would be easier or harder. For example the ATO annual tax report generation could become a “Country specific localisation” which includes
My first post in this thread was heading in that direction, for allocating Custom Fields, but I have since gone back on that a bit.
I came to the conclusion that showing the hidden lines when creating (editing) the transaction would be a bit cumbersome, so that is now no longer a part of the suggestion.
I am looking at it like this:
This way the existing transaction formats do not have to change.
That strikes me as impossibly complex, not to program, but for ordinary people to understand and use. I can barely follow you. Imagine trying to explain it to someone’s receivables clerk who was hired to collect on sales invoices.
You are right @tut it is complex, but I think that understanding the concept is the difficult part, and that has to be done here to explain it.
If it was to be put in place then there is no need for users to think about all the algorithms in the background.
I think it would be less complex for users (but not necessarily for the developers) than the present Custom Field set up and also more intuitive once it is in operation.
Also, some Chart Accounts could be blocked from setting Custom Fields, such as those associated with inventory as these would always draw from inventory items. Also, control accounts as Custom Fields for these would be defined in sub-accounts, (Fixed Assets, Supplier and Customer accounts, etc).
I think the discussion here on the forum is refining and evolving the concept into something that will be very beneficial for reporting, both for Custom Reports and Standard reports.
I don’t agree on this. Custom fields for CoA must be on every account or it will not be useful.
I hope this is still on the radar. It’s absence does really complicate annual tax return creation / auditing.
Basically what I want is a profit & loss report grouped / ordered by the tax office’s classification (which is different to what is required for routine business management).
Custom classification of accounts from the COA would readily achieve over 95% of the requirement (100% if the COA has adequate granularity to cover both requirements).
This could be implemented by any of the following:
An alternate COA structure (same accounts but different groups & sub totals)
An alternate COA account “Group” (i.e. add “Custom” the the current “Profit and Loss Statement”, “Cash Flow Statement”).
Supporting COA account custom fields (in detail Custom fields for chart of accounts - #5 by Patch)
Edit
My personal preference is option 3. because
I actually want 2 different custom COA groupings, one to match the tax office’s classification and grouping, but also one to match my accountant pre grouping which that firm uses to later match to the tax office requirements.
It is readily adaptable now by users to produce the Tax offices total, and could be extended later by adding a report transformation to more closely produce the tax office report
It is a general capability which other users are likely to adapt to other requirements
