tax and reports on capital expenses

Shane Litherland litherland-farm at bigpond.com
Sun Aug 21 04:20:51 EDT 2011


Hi fellow users,

Here's some more experiences from my gnucash forays.
Particularly:

-Using separate Tax accounts for received and owed taxes;

-Using tax accounts to differentiate capital and non-capital expenses
(as opposed to numerous separate sub-accounts in 'expenses');

-Trying to use the tax accounts to filter and report on capital and
non-capital expenses, and the complications for different expense
scenarios.

It's an unabridged 'step-by-step' so readers can see what avenues I
tested and eliminated, before I suggest options/improvements or ask for
guidance ;-)

In Australia, we have Goods and Services Tax (GST), applied to almost
everything, and if you are in business, it's one of those taxes which
you have to track how much you pay on your expenses, how much you
collect on your sales/services, then report it to the government and pay
or get reimbursed the balance over the relevant reporting period.

There's decent answers/examples on how to deal with taxes like this in
Gnucash. For the last few years, I was happy using one account in
'Liabilities' for GST paid/received, and the balance at the end of each
year was what I paid (or rarely, received).

As the Taxation Office reporting asks for certain breakdowns in
expenses/sales, I decided for the latest financial year, to 'upgrade' my
methods and split the GST three ways - still keeping all three
sub-accounts in one 'Liabilities:GST~' account for overall ease of
balancing, but the actual GST from sales goes to '~:GST-received' while
expenses now go to '~GST-capital' or '~GST-non-capital'.

This approach is along the lines of what's already suggested/documented
for 'how to do it' in Gnucash.

I am still completing my re-arrangement of the year's info, but so far,
I looks like the figures are balancing correctly and it will help me
report GST aspects a little smoother... but not seamlessly...

Where I seem to have come to some hurdles, is in generating the right
reports to give me the figures I need. I had in mind, that I could use
the GST sub accounts to 'filter' info in expense accounts to show
capital and non-capital items with GST, and for the occasional items
without GST, I could do a report for them also and manually
differentiate between capital and non-capital. I though by using the GST
subaccounts I could gather this level of info without having to create
'capital' and 'non-capital' subaccounts for every single expense
account.

When I tried to run a transaction report (no other reports seemed to be
suitable for the desired output) on my expenses, filtered by the GST
accts, obviously, nothing happened - because all expense accounts are
transactions to/from the A/P account. No direct connection to the GST
accounts. The relationship to the GST account is only valid from the A/P
account. So I was foiled by how the filter actually works!
I can run same report on A/P, and then it will list stuff based on my
filter settings to include/exclude txns from the GST accounts, so I know
the report is actually working correctly.

I guess what I had in mind was having a filter mechanism that
could..."include/exclude transactions that are a split in a 'parent
transaction' that includes another split to/from the chosen GST account"

So, not just looking directly at where the txn in question came/went
from ( in this case, an expense from A/P), but looking at what that item
was part of (ie a bill which included a split to a GST account) and
determining the filter output by that means.

It seems, for now at least, that is not an option in Gnucash??

So, I went back to trying to get reports from A/P. This was less
preferred as it doesn't give me the info broken down per expense
account. I can get the report to show some info to this effect but it is
cumbersome - I chose multi-lines, and to display 'other account' info -
now, that showed me the account codes for each expense split, but I
don't know if I have given all accounts a code and I have to then wrack
my brain to figure out which account the code relates to. the option to
show full account name just shows 'split transaction'... dunno why the
report can get a match for the code but not for the account name??
something to improve in the reports maybe? And of course, it's not
sorted into expense accounts, even choosing the sort option based on the
'other account' option doesn't give any clear/useable output - Because
the first split in each txn is to the A/P account, when sorted by the
code it doesn't have any benefit.

I can settle for minimal information and get a workaround for some
scenarios. If I just want to report on 'capital expenses' (that had GST
charged) I can run a txn report on A/P and filter by the GST-capital
account, to include txns in the filter account. I can do the same for
non-capital expenses (with GST).
This will give me a report for each, based on the the transaction totals
in A/P. Unfortunately, those totals will include, for some bills,
private amounts e.g. phone bills are partly private and partly business.
(See below for elaboration.) The current 'workaround' would be a pen and
calculator, or copy/paste to spreadsheet, and manually go through
expenses to adjust figures. Not the most time-effective approach, no
different to where I was when using one GST account and txn reports from
individual expense accounts.

Then, what about the occasions where I have an item (capital or
non-capital), without GST? e.g. at a charity auction, second-hand items
with no GST applied, I could by an office chair. Without any GST on the
item, I can't use an 'include' filter to report this.

If I report by an 'exclude' filter to exclude all the previous items
that had GST-capital or GST-noncapital, then it will select such
purchases, but they will be hidden amongst all the payments into A/P...
because when a bill is paid, the txn into A/P is just that, money from
the cheque/cash account, with no connection to the GST account. I have
to also exclude the asset accounts to remove the payment transactions.
Then it starts to make a little more sense.

But yet another complication is then apparent: This report of non-GST
txns in A/P also includes some items which were of a private nature (GST
cannot be itemised from private txns for business accounting purposes,
it simply remains part of the total price). Again, I can exclude the
'private drawing' accounts which they are posted to. But this  also
removes other relevant txns: Some items e.g. rates, are not only
GST-exempt but may be partly business and partly private. By selecting
the 'private drawing' accounts as part of an 'exclude' filter, then any
txn with a split to these is removed from the report, not just the split
amount that went to the private drawings accounts.

This complication of private/business mix exists regardless of whether
GST is applicable or not. Back to the phone bill example: A txn report
from A/P using an 'include' filter on the GST accts, will show the total
of the bill (private+business). There seems no way to filter such splits
out at the moment, though I can get an 'all-or-nothing' report by doing
an 'include' filter on the GST account, and  an 'include' filter on all
the expense accounts except the 'private' ones. That excludes completely
private expenses, without excluding those that are a mix of business and
private (which would be the result of an exclude filter on the private
expenses). A bit of a fiddle to set up but it comes closer to the info I
require.

I think some of the reporting covered above, could be streamlined and/or
condensed, with some 'step-wise' filtering e.g. filter by 'criteria-1',
then by 'criteria-2', in a similar fashion to how it might be done for
spreadsheets or databases. (e.g. include txns to/from a GST account, but
then exclude from that subset any with txns only to/from a private
expense account). This 'stepwise' approach is already an option in the
sorting dialog, where two levels of sorting can be applied. So maybe the
tools/syntax are already in existence to build upon..?

So, that is one suggestion I have for a tool in gnucash that would add
versatility and address some of the scenarios I have given.

Another approach to a solution, and possibly more versatile, would be a
filter tool that had the option to do what I initially hoped it would do
- a 'look-through' method of assessing each transaction, so rather than
just being able to filter the account/s in question based on the
immediate relationship of each txn to other accounts, the ability to
'track' any transaction that is a split of a 'parent transaction' back
to it's originating account and then applying filter criteria on all
splits in the 'parent transaction'.

I think this approach would then allow a report to be run from an
expense account, using suitable criteria to achieve what I am currently
having to generate via several separate reports and some time spent
adjusting them (spreadsheet or calculator).
Having a bit of background in SQL and spreadsheet formula, I reckon this
is logically achievable, but don't know the insides of gnucash to
elaborate on which bits might be best tweaked.

OK, now, open the floor to comment. Useful improvement for others? Waste
of time? Did I bash my head on the brick wall and not notice the door a
few feet away?...

Regards and hope the tome didn't send too many of you to sleep...
-shane



More information about the gnucash-user mailing list