Average Days to Pay Calculation (SQL Code)


Average Days To Pay (ADTP) is a very crucial information for us to assess a customer or group of customers and their nature of paying the invoices.

In GP, we have this calculated and displayed when we select a customer in Customer Credit Summary window (shown below):

The following is the definition & formula taken from GP Receivables Management manual, which clearly explains how this is calculated:

After a customer has paid his or her first invoice, the average days to pay (ADTP) is calculated based on the number of invoices a customer has, the time taken to pay the first invoice, and the time taken to pay the most recent invoice.

The formula for calculating the average days to pay is:

ADTP = (Current ADTP) x (Number of Invoices) + (Number of Days Taken to Pay Most Recent Invoice) / (Number of Invoices + 1)

The time it took to pay the first invoice would provide the initial value for the Current ADTP. Any later invoices paid by this customer will provide the values for the number of invoices and the number of days taken to pay the most recent invoice. The ADTP calculated on the customer’s initial invoices then becomes the “Current ADTP.” You can use this value when you recalculate the ADTP for later invoices.

With this as basis, I have written a SQL script (link for which is provided at the end of this post) which will calculate ADTP group by a Customer ID.

Parameters of this SQL Procedure:

1. @FROMDATE – Self-Explanatory

2. @TODATE – Self-Explanatory

So pass two dates and the results are shown based on invoices between those two dates.

I have heard from several GP users that the result what GP shows on Customer Credit Summary window is not correct at times. This SQL script will give you a chance to compare between two and decide which one’s correct.

As always, your feedbacks are welcome.

RM_AverageDaysToPay.sql

VAIDY

Advertisements

Customer Credit Summary: Average Days To Pay


I received an email today with a query on GP’s “Average Days To Pay” on a Customer’s Credit Summary.

Query is: How GP calculates Average Days To Pay for a customer?

According to GP’s Receivables Management user manual:

After a customer has paid his or her first invoice, the average days to pay (ADTP) is calculated based on the number of invoices a customer has, the time taken to pay the first invoice, and the time taken to pay the most recent invoice.

The formula for calculating the average days to pay is: 
ADTP = (Current ADTP) x (Number of Invoices) + (Number of Days Taken to Pay Most Recent Invoice) / (Number of Invoices + 1)

The time it took to pay the first invoice would provide the initial value for the Current ADTP. Any later invoices paid by this customer will provide the values for the number of invoices and the number of days taken to pay the most recent invoice. The ADTP calculated on the customer’s initial invoices then becomes the “Current ADTP.” You can use this value when you recalculate the ADTP for later invoices.

There are two ADTPs; LTD (Life To Date) and YTD (Year To Date).

The important point that you may have to remember is that Average Days To Pay YTD will be calculated only based on Amounts Since Last Close.

VAIDY