Hi Everyone, I'm working on a cashflow projection and for that I need to determine a realistic date for collections.
I have a list of invoices issued to customers, each with corresponding due date.
However, many customers disregard the due date and pay according to their established payment policy .
For example:
Client A always pays on the 15th or the 30th (when this date falls on the weekend it's defaulted to the following Monday).
Client B always pays on Thursdays
Client C always pays on Mondays and Wednesdays
Client D always pays every other Tuesday.
Client E always pays according to due date.
I can work out individual formulas for each client according to their establish payment pattern/behaviour.
For Client B I would use ='DueDate' + 7 - WEEKDAY('DueDate'+7-'PaymentDay',2))
Where 'DueDate' is a reference to a cell which contains the date the invoice is due and 'PaymentDay' is the day of the week the customer typically pays - in this case Thursdays, so 4.
With a little bit of time I can work out the logic for each one.
Question: is there a way of storing that logic/formula in a cell for each client [maybe in a separate tab which contains a list of clients] so that I can leverage it to calculate the realistic collection date next to each invoice?
Thank you!
Damian
I have a list of invoices issued to customers, each with corresponding due date.
However, many customers disregard the due date and pay according to their established payment policy .
For example:
Client A always pays on the 15th or the 30th (when this date falls on the weekend it's defaulted to the following Monday).
Client B always pays on Thursdays
Client C always pays on Mondays and Wednesdays
Client D always pays every other Tuesday.
Client E always pays according to due date.
I can work out individual formulas for each client according to their establish payment pattern/behaviour.
For Client B I would use ='DueDate' + 7 - WEEKDAY('DueDate'+7-'PaymentDay',2))
Where 'DueDate' is a reference to a cell which contains the date the invoice is due and 'PaymentDay' is the day of the week the customer typically pays - in this case Thursdays, so 4.
With a little bit of time I can work out the logic for each one.
Question: is there a way of storing that logic/formula in a cell for each client [maybe in a separate tab which contains a list of clients] so that I can leverage it to calculate the realistic collection date next to each invoice?
Thank you!
Damian