Cash entry question

[GaryPanic]

Hi guys -
I have presented my design to my new company and they were impressed
However they raised the issue of payments


I had designed my system with a cash book - 1 invoice and one payment
(basically its either paid or its not)...

Now I think I can get round this - but other views would be nice

ok my view is
allow duplicate entries on the cash then when i run statments have the cash book aggregate by invoice no and match it against the amount due then a simply report showing outstanding

THe reason for aggregate the cash book is the payment may come in in 2- 3 -4-5 cheques
does this sound the right approach ?

 

[GaryPanic]

outline

amount due £1000
invocie 12345


payment in 250 on invoice 12345 April
payment in 250 on invoice 12345 May

June statment

invoice 12345 due £1000 paid £500 outstanding £500

another invoice due 500.00 paid 0.00 outstanding £500.00

I presume i will have to mess around with NZ fucntion in my report

 

[boblarson]

If you want to keep it in accounting concepts then you would have a debits field and a credits field, you sum them both and then subtract the credits from the debits.

Or you can use a single field for the amounts and then another field to designate what it is. If your interface enters the credits as negative then when you sum everything up for that invoice you have the amount due or amount due back to the customer.

 

[PNGBill]

It may be a good idea to review your fields that accept and calculate amounts and prevent more then 2 decimal places being entered.
Nothing worse then a statement that doesn't add up - is 1 cent out.

An operator can enter 35.333 by error and all you see is 35.33 never realising the error until 6 months later the statement is out due to accumulated .003's

 

[GaryPanic]

First option is the one I kinda had in mind -
i wasn't going to allow part payments as it causes grief further along the line - as I cannot part pay the supplier it has to be full payment (this isn't a problem as I have almost unlimted credit arrangements in time- but its an admin nightmare - I hope that these will be few and far between

as there is primaliary going to be 2-3 users doing this ( moer to be added along the way) the users (myself included) would credit the invoice out and re-invoice the excat amount paid and then another invocie for the amount outstanding - this means that my book of accounts looks cleaner - as my aged debtors is smaller due to there being smaller amounts unsettled


But as usual Bob you input has steered me in the right direction - thanks again

g

 

[GaryPanic]

It may be a good idea to review your fields that accept and calculate amounts and prevent more then 2 decimal places being entered.
Nothing worse then a statement that doesn't add up - is 1 cent out.

An operator can enter 35.333 by error and all you see is 35.33 never realising the error until 6 months later the statement is out due to accumulated .003's

Not to worry on this - the entry field will be fixed to 2 decimal places (currecny format) - and small amounts can be absorbed(?)

the set up is this its paid and settled (two different processes ) or its part paid and not settled (which was giving nightmares)

or it could be settled and not paid (which is weird -) only items that are unsettled will appear on statement - amd would involve use paying the supplier without getting monies in .

 

[boblarson]

I would use the Currency datatype too. It will keep things straight if you have two decimals selected.

 

[boblarson]

Oh, and my first suggestion didn't mean to have it all in one record. You can have as many payments as necessary for when you aggregate it will all come together just like if you did the second method.

 

[PNGBill]

Sounds like you are linking specific items to it's purchase, sale and subsequent payment whereas normally purchases effect Inventory and sale effect Inventory and accounts receivable is linked to sales, not inventory - I think.

Wished we had setup our system to allocate payments to Debits rather then just show all debits and all credits on the statement and if balance is zero, then settled.

 

[gemma-the-husky]

you probably need these

invoice table

payments table

invoice/payment matching table


since a single payment may contain multiple items, and a payment may be paid in multiple instalments.

also worth thinking about the mechanism for allocating payments. in a large business, a single payment may cover dozens/hundreds of transactions. automation of the matching procedure is likely to be very useful.

if you have to deal with settlement discount, it may be slightly more complex also.

 

[Simon_MT]

I only use two tables invoices and payments. I inititally flag the invoice as Unpaid, then Part Paid or Paid depending on the value of the receipt. A dialog box is called upon processing subsequent receipts on invoices that are part paid and it works out if the total receipts result in the invoice being fully paid.

I have query to total all the payments to produce Debtors.

I agree with Bob to use a Currency Flag.

Simon

 

[DCrake]

In some systems I have done I used a reducing balance method, whereby the client made a payment of lets say £1000. The screen would list all outstanding invoices or invoices not fully paid in aged order, oldest first.

I then entered the amount to pay and it would trawl throught the invoices and either fully pay them or part pay the last one in the list if the balance remaining was less then the balance of the payment.

So if they had 4 invoices outstanding

inv005 - £250 - Balance £50
inv004 - £600 - Balance £600
inv002 - £400 - Balance £400

Inv005 would be cleared
inv004 would be cleared
inv002 would be part paid £150

That way your aged debtors report would be correct.

 

[PNGBill]

David,

Your method is fast and works great 99% of the time but when a customer has a reason to either not pay an invoice or part pay one it may not show in the statement because the "broad brush" approach to allocating payment has shown the invoice as paid and in the "history bin".

 

[boblarson]

So basically, the approach taken will be influenced by the business rules. I do advocate for not just going with the business rules because they can change but find the solution which is flexible AND can maintain the business rules.

 

[GaryPanic]

interesting..

well I am taking the approach

Invocies (not settled)
cash in (sepearate table )
run a agg on the cash table (by invoice number)
then match against invocies from my maintable
show on a report x-y amount due
I have to do this per invocie as client x may pay a named item not the oldest

my next problem was(is) un-allocated cash
my solution is to have a field on the account form - unallocated cash (with notes field) and when I run statementss have this show on the report

the aggregation of the cash table is causing a little concern (Not the mechanics ) - but I could end up with a cash table of say 20000 entries. which would be a bit of a bitch to do - so I may end up doing some sort of cashed settled update to remove invocies that are full paid (or just excluded them from the agg function?) I know what I want to do - its just wrapping it around in my head and writing it down on papers to quantify it -- or I may just archive the true settled invoices out into a ledger table as I have the main info already .. I don't know .. I will give this one some serious thought

well any guys as usual your thoughts have been interesting and I am glad to see that the various approaches people have used and that my thought process is not 100% out of sink -

I may have a chat with an old friend of mine who introduced me to access - he's a full qualified accountant - so his take on it should be pretty good

 

[GaryPanic]

In some systems I have done I used a reducing balance method, whereby the client made a payment of lets say £1000. The screen would list all outstanding invoices or invoices not fully paid in aged order, oldest first.

I then entered the amount to pay and it would trawl throught the invoices and either fully pay them or part pay the last one in the list if the balance remaining was less then the balance of the payment.

So if they had 4 invoices outstanding


That way your aged debtors report would be correct.

agree this would be great -however regulation in insurnace won't let me do this a payment in has to be for a "name" invocie/policy it would leave my compnay open to E&O's

 

[gemma-the-husky]

Gary

if you take invoice payments to its logical extreme you end up requiring a sales ledger (accounts receivable)

you may be able to get by by allocating payments against invoices (in an invoice table?) but for truly flexible recording you do meed the entities I outlined above

a ledger transactions tables (the invoice table MAY suffice - but in strict terms it doesnt handle ledger adjustments, bad debts etc etc)

a payments table

an invoice/payment allocation table

you may be able to get workrounds with less than this - but thats what it will be - a workround. you will end up reducing the o/s balance of an invoice as you record payments, and will find it difficult to get a history of all the payments that paid down an invoice. It isnt hard to do it right, and it will give your users a better experience.

If they start asking you to calculate o/s debtor days, and other stuff like that, you will struggle without the proper tables.

 

[GaryPanic]

I am have a rethink -- will come back with my thoughts

 

[GaryPanic]

OK my set is
accNo
currency (sterling/euro/dollar/AUS/Rands /etc
invoice (main table)
settled date (main table)
(other stuff)


Cashtable
accno
currecny
invoice
amount
Date recieved
Date banked
Cashbanktransno-


this extra table
will need
Accno
invoiceno
currency
amountdue
amountpaid
status (outstanding/part/settled)
and I will have to run statments from this table (joined by account number to the accouts table and also by the invoice number to let the client know what for )
does this seem about right ?

 

[gemma-the-husky]

it depends how you maintain the sales ledger (are you intending to have a ledger)

although this is definitely a non-normalised approach, I am sure many accounting systems duplicate the invoices in the SOP system, by transferring them to a SL transactions table.

The SL transactions can then include things that dont come from the invoice table - ie cash postings, journal entries, credit notes (maybe)

By doing this, you end up with a fully standalone sales ledger, independent of the SOP system- which is proably a more traditional accounting approach.

--------------