Announcement

Collapse
No announcement yet.

Allocate pre-payments to oldest debt first

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • samaria
    started a topic Allocate pre-payments to oldest debt first

    Allocate pre-payments to oldest debt first

    Hi,
    Am seeking assistance on an Excel macro. The issue is where an entity has debts as well as credits (pre-payments) which have different age profiles. In essence, the pre-payments should go towards clearing the oldest debt first. The screenshot below illustrates this (where pre-payments are the negative amounts). For instance, for Client X below, the prepayments in the 90-180 days and 181-365 days should clear the debt over 365 days. Any ideas will be of great help
    Original data
    Client 0-30 days 31-90 days 91 - 180 days 181 - 365 days >365 Total due
    X $ 30,000.00 $25,000.00 $-20,000.00 $ -5,000.00 $ 34,900.00 $ 64,900.00
    Y $ - $ - $ -4,500.00 $ -6,000.00 $ - $-10,500.00
    Z $ 4,500.00 $ 5,790.00 $ 9,000.00 $ 2,000.00 $ - $ 21,290.00
    A $ - $ - $ -4,550.00 $ 20,000.00 $-23,500.00 $ -8,050.00
    B $ 7,000.00 $ 4,500.00 $ - $ 9,000.00 $ -8,500.00 $ 12,000.00
    Total $ 79,640.00
    Required results
    Client 0-30 days 31-90 days 91 - 180 days 181 - 365 days >365 Total due
    X $ 30,000.00 $25,000.00 $ - $ - $ 9,900.00 $ 64,900.00
    Y $ - $ - $ -4,500.00 $ -6,000.00 $ - $-10,500.00
    Z $ 4,500.00 $ 5,790.00 $ 9,000.00 $ 2,000.00 $ - $ 21,290.00
    A $ - $ - $ -4,550.00 $ -3,500.00 $ - $ -8,050.00
    B $ 7,000.00 $ 4,500.00 $ - $ 500.00 $ - $ 12,000.00
    Total $ 79,640.00
    Regards







  • Carim
    replied
    You are welcome ....

    Thanks for your Thanks ..AND for the Like ...

    Leave a comment:


  • samaria
    replied
    Thanks Carim for this. I really appreciate. I have tried it on various scenarios and it's good.

    Out of curiosity, I wonder why the macro just didn't work on particular random rows while it was worked perfectly for the others.
    Again, thanks

    Leave a comment:


  • Carim
    replied
    Hello,

    Have extracted out of your 990 records the remaining 25 records which do not reconcile in order to deal with their specifics ...

    Attached is your test file Version 3 ...

    Hope this will help

    Attached Files

    Leave a comment:


  • samaria
    replied
    Hi Carim,
    I have changed the variables, but the cells where the negative values still remain after offsetting the positive value still retain the issue. I have also noticed that the issue affects those particular cells, even if you move them within the worksheet. So, for all the other rows, the macro is perfect, but for particular cells, if a pre-payment completely offsets a positive value (receivable), that cell will retain the difference between the pre-payment and the receivable, instead of having the value zero

    Leave a comment:


  • Carim
    replied
    Hello,

    Noticed a mistake in the Variables definition ...

    Could you just replace As Long by As Double ...

    It will correctly adjust for the hidden decimals ...

    Leave a comment:


  • samaria
    replied
    Hi Carim,

    Many thanks for you assistance, I really appreciate. The macro works fantastically. However, upon application of the formula to a much larger database, some cells still retain the negative (pre-payment) values, although those pre-payments have totally cleared the debt in that cell (please refer to the message inboxed)

    Leave a comment:


  • Carim
    replied
    Hello,

    Quickly tweaked the macro for Client C ...

    Further tests might be required ...

    Hope this will help
    Attached Files

    Leave a comment:


  • samaria
    replied
    Thanks Carim. I will really appreciate

    Leave a comment:


  • Carim
    replied
    Hello,

    Will take a look at your macro ... as soon as I am back from my business trip ...

    Leave a comment:


  • samaria
    replied
    I have tried tweaking the formula to no avail. Any assistance will be greatly appreciated Copy of Test Receivables Allocation V2.xlsm

    Regards

    Leave a comment:


  • samaria
    replied
    Hi Carim,

    Am back again . I noticed the macro needs some tweeking. I have explained in the attached file. Hope you will be able to be of assistance

    Regards Copy of Test Receivables Allocation V2.xlsm

    Leave a comment:


  • Carim
    replied
    Originally posted by samaria View Post
    Many thanks Carim. Appreciated. Works like a treat
    Glad you could fix your problem ...

    Thanks for both your Thanks ...AND for the Like ...

    Leave a comment:


  • samaria
    replied
    Many thanks Carim. Appreciated. Works like a treat

    Leave a comment:


  • Carim
    replied
    Hi again,

    Attached is your Test file with a macro to allocate pre-payments against Receivables as requested ...

    Hope this will help
    Attached Files

    Leave a comment:

Working...
X