I’m having a little project here and I hope that some of you have tried something similar before.
I need to make a report where I can print out the credit cost of my customers.
To simplify it then you can say it like this. If your credit cost is 10% pa, your customer owes you 1000 USD for 1 month, then the credit cost is 100/12. That’s easy enough.
The difficult part comes if your invoice was paid over several payments.
How would you do this in Navision?
try based on daily balance… Go by date - calculate balance - multiply by % / 365.
Hi Val,
That’s very a good suggestion.Thank you.
I was not really thinking in that direction as I was seeing a report where you had a customer based list, which then again listed all the invoices, the revenue and cost per invoice, incl. credit cost.
The only issue with your suggestion is that it will not make it possible to specify the costs of credit for invoices paid too late (and only cost of the time paid too late).
Erik,
You don’t need to check all days(dates), Recalc must be done on dates when balance changes (new invoice or new payment). This is the same way as bank calculates the deposit % it owes to clients, and formulae can be found in Excel, parameters are Balance,Daycount, YearRate, and then total the result for all periods - you only need to mimicrize these formulae in Navision syntax.
Thank you Modris, but that will still not give me the cost of credit specific to invoices overdue.
What I want to know is how much the cost is for credit per customer, but separated in total credit costs and credit costs for overdue payments. What I mean here is if you have an invoice and your payment terms is 30D but the customer doesn’t pay until 10 days after the due date, then I need to know the credit cost of the 40 days (30+10) and for the 10 days it was paid to late.
Hmmm… If it was 30 days = Amt1, then 10 days Amt2 it could be done based on CLE, as DueDate sits there, but more complicated to calculate.
As you need 40 days = AmtFull and 10 Days = AmtFull- Amt30Days, I have no idea, especially if overdue days have different (larger?) percentage…
This is why I don’t sometimes like Navision RW - such things are difficult to achieve in it [;)]
Modris, I’m happy that you now understand my problem!
And the problem starts getting even more complex if the invoice was paid in multiple payments. Or even more complex if you credits part of the amount after 40 days, then you basically have to recalc it all…
Erik, I’ll spoil your mood on Sat evening even further - what if several pending invoices (+partially paid) exist?
Then even ClosedWith & Closing Date for each Invoice line in CLE won’t help, as these are cumulative fields and reflect current status, no way to detect 13 partial payments… Sorry of making even more problems to you instead of helping to solve already existing, but as you know, I’ve now MUCH free time to think about all possibilities, even the weirdest ones.
Don’t worry Modris. You’re not spoiling my mood with such an issue! Problems like this is really what makes Navision development fun even after almost 19 years!
And yes, make sure to get enough of stuff you can do on the computer when you’re going to be off-line the next week!
Go backwards… find all invoices open and overdue - calculate cost… Than go by payments and look if it was paid late and calculate cost…
Hi Val,
That number will not be correct!
Example: If you have an invoice USD 1000. Due Date 30.8. - On 20.8 paid 200 USD. On 31.8. Paid 200 USD. On 10.9. 200 USD and on 20.9 credit memo 200 USD. Finally on 30.9 200 USD.