accounts receivable aging report?
309 views
Skip to first unread message
charles
Dec 6, 2015, 7:30:21 PM12/6/15
to Ledger
Hello all,
I began using ledger a few weeks ago to keep track of my small service business (about 10 regular clients, up to 200 invoices per year, two possible currencies/commodities). I've been using a big general ledger spreadsheet for several years, but now I've converted all the data for the current year into ledger transactions to start figuring out what ledger commands and options to use to get the few reports I need.
I keep track of customer invoices using this account structure: Assets:Receivables:<customer>:<invoice>, meaning a new ledger account gets created with every new invoice. One disadvantage of this structure is not being able to predeclare these invoice accounts, so I can't use --strict and/or --pedantic to help prevent entry errors.
I chose this account structure because I could not determine another way to keep track of invoice payment status (unpaid, partially or fully paid) and individual due amounts for each invoice.
Is there a better way to structure Receivables in order to more cleanly accomplish this requirement? (And what ledger commands would I use to report invoice paid status for individual invoices?)
But more to the point of this post:
An aging report for accounts receivable would seem like a common report a business would want. The report would look like this for a certain client (or all clients sorted in a single report would work, too):
Receivables Aging Report for <client>
Invoice Date Invoice No. Amount due Age (days since invoice was sent)
------------ ----------- ---------- ---------------------------------
<date> <invoice> <amt due> <age>
...
Can ledger generate such a report?
If yes:
- What would that ledger command look like?
- How would transactions and/or accounts have to be structured for ledger to be able to do this?
If not, I have built a way to do it using ledger, awk and bash commands in a bash script. But it's inelegant because I need to call ledger several times with different commands and options just to gather the data I need to generate the aging report. It would be neat to have a single ledger command that would generate a receivables again report without a bunch of bash code on the back end. Any ideas?
Thanks much!
I began using ledger a few weeks ago to keep track of my small service business (about 10 regular clients, up to 200 invoices per year, two possible currencies/commodities). I've been using a big general ledger spreadsheet for several years, but now I've converted all the data for the current year into ledger transactions to start figuring out what ledger commands and options to use to get the few reports I need.
I keep track of customer invoices using this account structure: Assets:Receivables:<customer>:<invoice>, meaning a new ledger account gets created with every new invoice. One disadvantage of this structure is not being able to predeclare these invoice accounts, so I can't use --strict and/or --pedantic to help prevent entry errors.
I chose this account structure because I could not determine another way to keep track of invoice payment status (unpaid, partially or fully paid) and individual due amounts for each invoice.
Is there a better way to structure Receivables in order to more cleanly accomplish this requirement? (And what ledger commands would I use to report invoice paid status for individual invoices?)
But more to the point of this post:
An aging report for accounts receivable would seem like a common report a business would want. The report would look like this for a certain client (or all clients sorted in a single report would work, too):
Receivables Aging Report for <client>
Invoice Date Invoice No. Amount due Age (days since invoice was sent)
------------ ----------- ---------- ---------------------------------
<date> <invoice> <amt due> <age>
...
Can ledger generate such a report?
If yes:
- What would that ledger command look like?
- How would transactions and/or accounts have to be structured for ledger to be able to do this?
If not, I have built a way to do it using ledger, awk and bash commands in a bash script. But it's inelegant because I need to call ledger several times with different commands and options just to gather the data I need to generate the aging report. It would be neat to have a single ledger command that would generate a receivables again report without a bunch of bash code on the back end. Any ideas?
Thanks much!
Chris Bennett
Dec 8, 2015, 8:32:25 AM12/8/15
to ledge...@googlegroups.com
Hi Charles,
I can comment on some parts of your request, but not all :)
> I keep track of customer invoices using this account structure:
> Assets:Receivables:<customer>:<invoice>, meaning a new ledger account gets
> created with every new invoice. One disadvantage of this structure is not
> being able to predeclare these invoice accounts, so I can't use --strict
> and/or --pedantic to help prevent entry errors.
This is exactly how I do it. I don't use strict or pedantic (I really
should, and plan to). But as part of my invoice generation, I
generate the ledger entry too, so I would probably append the new
account (invoice id) to the file that contained the list of valid
accounts. Maybe you can do something similar?
> If not, I have built a way to do it using ledger, awk and bash commands in
> a bash script. But it's inelegant because I need to call ledger several
> times with different commands and options just to gather the data I need to
> generate the aging report. It would be neat to have a single ledger command
> that would generate a receivables again report without a bunch of bash code
> on the back end. Any ideas?
Agree - I munge my data with perl to report on what I want (but do not
yet do an ageing report. My small business is large busy enough to
worry).
But I am very interested in any suggested approaches to this.
Hopefully someone else chimes in with something more useful :)
Regards,
Chris
I can comment on some parts of your request, but not all :)
> I keep track of customer invoices using this account structure:
> Assets:Receivables:<customer>:<invoice>, meaning a new ledger account gets
> created with every new invoice. One disadvantage of this structure is not
> being able to predeclare these invoice accounts, so I can't use --strict
> and/or --pedantic to help prevent entry errors.
should, and plan to). But as part of my invoice generation, I
generate the ledger entry too, so I would probably append the new
account (invoice id) to the file that contained the list of valid
accounts. Maybe you can do something similar?
> If not, I have built a way to do it using ledger, awk and bash commands in
> a bash script. But it's inelegant because I need to call ledger several
> times with different commands and options just to gather the data I need to
> generate the aging report. It would be neat to have a single ledger command
> that would generate a receivables again report without a bunch of bash code
> on the back end. Any ideas?
yet do an ageing report. My small business is large busy enough to
worry).
But I am very interested in any suggested approaches to this.
Hopefully someone else chimes in with something more useful :)
Regards,
Chris
Kyle Spaans
Dec 8, 2015, 8:44:45 PM12/8/15
to ledge...@googlegroups.com
Hi Charles,
Have you looked into the "clearing" feature for transactions?
This doesn't help you with the aging report, but it could be useful to keep track of your owed balance vs your paid balance. Possibly you could pair it with "auxiliary dates" (http://ledger-cli.org/3.0/doc/ledger3.html#Auxiliary-dates) in order to get a date when invoices were paid. This may help simplify your ad-hoc scripts?
Personally, I use "cleared" transactions to represent CC charges or bank charges that I've double-checked. So when I buy something with my CC and get a receipt I mark it in my ledger with the uncleared state (no *). A few days later when it appears on my CC statement (or at the end of the month if I'm lazy), I verify the amount in my ledger against the statement and mark the transaction as cleared. This helps me root out manual entry errors and missing transactions.
--
---
You received this message because you are subscribed to the Google Groups "Ledger" group.
To unsubscribe from this group and stop receiving emails from it, send an email to ledger-cli+...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
Reply all
Reply to author
Forward
0 new messages