Calculate collection date for different customers based on established payment behaviours

damiduran

New Member
Joined
Aug 22, 2018
Messages
2
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?

Capture.JPG


Thank you!
Damian
 

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

ExcelGzh

Board Regular
Joined
Mar 29, 2020
Messages
130
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP
Joined
Sep 18, 2013
Messages
3,960
Office Version
  1. 365
Platform
  1. Windows
... 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.
 

Watch MrExcel Video

Forum statistics

Threads
1,119,178
Messages
5,576,552
Members
412,731
Latest member
yaseen381
Top