Calculate collection date for different customers based on established payment behaviours

[damiduran]

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

 

[ExcelGzh]

This is an extremely convoluted thing to do but it is possible. For each different payment type (You have described 5 and there may be more) assign a number 1, 2, 3 etc. Then using the CHOOSE function, put each of the different formulas in the part of the CHOOSE parameters that matches the payment type. The example formula you gave for Client B would be payment type 2 for example. You then create an extra column, put your payment type for each customer in the column and put the CHOOSE function with all its various formulas in the Realistic Date column. You then put the cell reference to the Type column in the IndexNum parameter of CHOOSE.

 

[StephenCrump]

... or if you're happy to use VBA (?), perhaps a UDF? Using Select Case, in lieu of ExcelGzh's CHOOSE().

Easy to set up. And easy to modify when your customers inevitably come up with new weird and wonderful ways to subvert your due date system.