• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Cash flow forecasting

svramanan

New Member
Hello
Need help in mapping the debtors collections to the respective month(s) based on the credit period scenario. Pls find attached sample work sheet for ref.

Rgds
venkat
 

Attachments

  • Drs ageing sample.xlsx
    10.2 KB · Views: 12
The amounts are to be stored in the respective month when the amount is due. For ex if the credit period is 30 days; the sales amount relating to Apr 2021 will be stored in the collections of May 2021. In the xl sheet, sales is mapped to col E and hence the collection to be mapped to col F (if the credit period is 30 days). Thanks for the help in advance
 
" If the collection period is 30 days, then the amount collected should get populated from May 2021 onwards " what happens if we are in June or July? When should it start?
 
If we are in June / Jul then the collection should start in the next calendar period Jul / Aug (sales month + 30 days will be the collection month)
 
Try,

In E5, formula copied across to P5 and all copied down :

=IF($C5/30<COLUMN(A1),INDEX($E$3:$P$3,COLUMN(A1)-$C5/30),"")

74925
 
Last edited:
Back
Top