Aging methods in NAV – which buckets are you looking for?

When running any type of financial report, it’s important to ask the right question in order to get the right answer. When running your accounts payable or accounts receivable aging reports, it’s especially important since there are three available options that will give you the same total answer but will break your transactions down into different aging bucket categories. Choosing the correct option for the question you actually want to answer is the key.

Question #1:  How overdue is the invoice?

By choosing the Aging Method of Due Date, you are asking NAV to age each bucket on your aging in intervals based on the Due Date of the invoice. Remember the due date is based on a calculation that applies payment terms against the document date of the invoice.

Question #2:  How many days have passed since the invoice was posted?

By choosing the Aging Method of Transaction Date, you are asking NAV to age each bucket on your aging in intervals based on the Posting Date. This is sometimes confusing to new users since on both the purchase and sales invoice forms, the two available dates are posting date and document date. The posting date should always be the date you want to post the invoice to your books and therefore the fiscal period the invoice will report in. For the purposes of your aging reports, posting date is equivalent to transaction date.

Question #3:  How many days have passed since the vendor billed us – or – since we billed the customer?

By choosing the Aging Method of Document Date, you are asking NAV to age each bucket on your aging in intervals based on the Document Date. This one makes a little more sense to folks because at least the terms are the same. But darn us accountant types, we often call the document date the invoice date when we’re referring to it. Basically, this date should be the date your vendor has provided on the invoice, or from the customer side, the date you shipped and therefore the date you provided on the invoice to the customer. Any payment terms defined on the account will use the document date to calculate the due date.

An example of two invoices shown with all three aging methods

In a perfect world, our posting dates and document dates would all be the same. Let’s pick a perfect example with posting date of 11/01/10 and document date of 11/01/10 and payment terms of Net 21. This would calculate a due date of 11/22/10 on this invoice.

Then let’s pick an imperfect example. Let’s pretend someone at our company turned in that 11/01/10 invoice sometime in December, after the November books were closed. We still need to book this late invoice, so we’ll choose a posting date of 12/01/10, but since it was not the vendor’s error, we’ll use their invoice date of 11/01/10 in order to calculate the payment terms correctly.

Now, let’s look at an aging as of December 15th, 2010, all three ways. Take note that for all three methods, the balance due is exactly the same. The differences appear in how the aging buckets are defined and how transactions age into the different buckets.

Aging Method of Due Date

Key difference here is the aging buckets start calculating at the due date, so you’ll see column headers of current, up to 30 days, 31-60 days and over 60 days only when using this aging method.

Aging Method of Transaction Date

This aging is based on the posting date, the date that corresponds to the fiscal period you posted the invoice in. Note the late invoice with a 12/1/10 date shows as current.

Aging Method of Document Date

This aging has the same buckets as the one run with transaction date, but is based on the document date or invoice date.

This posting is one of the Top 20 Most Viewed in the last year! Follow this link to see the entire list.

10 easy tips for payment terms success in Microsoft Dynamics NAV

1.  Know that the Document Date is what controls the calculation of payment terms. This is true for both vendor invoices as well as customer invoices.

2.  The vendor and customer areas share the same Payment Terms setup. Make sure the folks who maintain these tables know they’ll need to share the codes that are set up here.

3.  Basic terms use very basic setup. Payment terms of Net 30 simply require a Due Date Calculation of 30D in order to work.

4.  Immediate payment terms can be accomplished in two ways. The first way is to leave the payment terms field blank on the vendor or customer card. When this occurs, NAV makes the assumption that the payment terms are immediate. The second way is to populate the Due Date Calculation with 0D. By putting in zero days, you can still define a payment term and name it as immediate. I don’t like to see blank fields in any of my data, so I recommend using this method.

5.  Discounted terms follow a specific combination in the payment terms table. For discounted terms like 1/10 Net 30 (one percent discount if paid in ten days, otherwise due at thirty days), use this setup.

6. You may have some things you always pay on the first of the month, like rents. How do you get invoices to show up on your payables aging on the first day of the month regardless of whether the month has 31, 30, or 28 days? In the due date calculation, use this simple formula:  CM+1D.

7.  If you send checks, and you need for that first of the month payment to get in the mail in time to be received on the first, get a little fancier with the formula and use CM-3D. This will calculate the due date as the third to last day of the month, no matter how many days are in the month.

8.  If you need to date something as always due the last day of the month, set up the Due Date Calculation as D31. This will always calculate the due date on the last day of the month, regardless of how many days the month has.

9.  When running the suggest vendor payment process for payables, the last payment date corresponds to the due dates calculated by the payment terms. The date you put in this field will suggest payments to be made with due dates up to and including the date listed.

10.  If any invoice gets posted with incorrect payment terms and you need to correct the due date in order for it to show up properly on your aging, you can go in and correct the due date on the already posted invoice. This can be done from the end-user level and does not require use of the object designer.  Drill down to the correct invoice record from the Balance ($) field found on the general tab of the vendor card.  Use a triple-click with your mouse on the due date field to change the date quickly and easily. The same steps can be accomplished from the customer card.

This posting is one of the Top 20 Most Viewed in the last year! Follow this link to see the entire list.