StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>What you are describing here is basically a decision between open item accounting and balance forward accounting. </p> <p>In open item accounting, each invoice is kept "open" while it has a balance owing and payments are attributed to the individual invoices that they are paying. This makes it easier to work out things like interest - for example if you only charge interest on balances owing older than 30 days then you need to know what invoices have outstanding balances more than 30 days old. </p> <p>Balance forward accounting is similar to credit card payments, when there is one lump sum balance that is carried forwards and payments are made against that total balance rather than individual invoices. </p> <p><strong>Update for clarification</strong></p> <p>Open item accounting is good for complex billing processes, for example when only some of the products are attached to certain invoices. For example, a builder buys a few dozen products but they are to be invoiced separately to three different building projects. </p> <p>Open item accounting is also used when you need to track each individual invoice for payment and dispute resolution purposes. For example, a building supply has a builder for a client. Sometimes the goods supplied are wrong or faulty so the builder pays all the other invoices (including more recent invoices) except that one, which is tracked and dealt with separately - perhaps getting paid when replacement goods are supplied or perhaps getting a credit note against the invoice. </p> <p>In balance forward accounting, you would deal with this situation by simply applying a credit against the account as a whole, then re-adding the transaction when the replacement goods are supplied. Any interest charged on the balance can also be reversed against the account. </p> <p>Simplistically, here's a way you could set these up in your database:</p> <p><em>Open Item Accounting</em></p> <p>You need the following tables:</p> <pre><code>Client [ClientId, Name, AccountBalance] Product [ProductId, Description, Cost] Invoice [InvoiceId, ClientId, Date, TotalAmount, Outstanding] InvoiceItem [InvoiceId, ProductId, Quantity, Amount] Receipt [ReceiptId, Date, TotalAmount] ReceiptItem [ReceiptId, InvoiceId, Amount] </code></pre> <p>Client gets an invoice created when buying products. For each product bought, an invoice item is created for that product showing the quantity bought and the amount. When the invoice is updated, the invoice's outstanding balance is the total of the invoice and client account balance is updated (can be calculated on the fly but easier and quicker if maintained automatically). When the client pays one or more invoices, a receipt is created and receipt items are allocated to each invoice being paid. The invoice's outstanding balance is updated as with the client's account balance. Note that overpayments need to be dealt with separately. Interest charges are raised on the next invoice (or a separate invoice) as an invoice item (this can be a custom product). </p> <p><em>Balance Forward Accounting</em></p> <p>You need the following tables:</p> <pre><code>Client [ClientId, Name, AccountBalance] Product [ProductId, Description, Cost] Invoice [InvoiceId, ClientId, Date, Amount] Transaction [TransactionId, ClientId, InvoiceId, ProductId, Date, Quantity, Amount] </code></pre> <p>When a product is purchased, a transaction is made on the client's account showing the quantity of the product and the amount and the client's account balance is updated. When a receipt is made, again a transaction is made on the client's account and the client's account balance is updated. Overpayments and interest payments are just a transaction as well. At invoicing time, you simply grab all the transactions (purchases, receipts, interest payments, etc) that aren't allocated to an invoice and add them to your new invoice. The invoice doesn't appear in the transaction list, it is just for convenience in tracking transactions that have been invoiced and for giving your clients a reference number when paying. You might also want to track receipts in this model. </p> <p><em>Other considerations</em></p> <ul> <li>This doesn't take into account the general ledger posting which will be an entirely different set of tables. This is just for the management accounting, not the financial accounting.</li> <li>In practice, there may be a projects table between the client and the invoice to track each of the clients individual projects with you.</li> <li>This is really simplistic just to give you an idea. More tables and fields would no doubt be required for a full implementation. </li> </ul> <p>With regards to what happens if they don't pay of their invoice when it comes time for billing, in either system it doesn't matter - you do not go back in time and change previous accounting entries. Anything that happens now goes on a new invoice. Of course, it is entirely appropriate to have a line on the new invoice saying "Previously outstanding balance" which shows the amount they already owe (or are in credit for overpayments). </p> <p>You will also want to be able to produce a Statement of Account, which isn't an invoice, its just like a letter reminding them they have outstanding balances. This is used when there is no new invoice to be generated but the client needs a reminder about their account balance. </p> <p>I prefer open item accounting for my systems but from your description balance forward accounting sounds like a good fit. </p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload