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
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
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.
 
Upvote 0
... 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.
 
Upvote 0

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top