Announcement

Collapse
No announcement yet.

Allocate pre-payments to oldest debt first

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

  • 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







  • #2
    Hi and Welcome to the Forum

    In order to make things a lot easier ... why don't you attach your test file with your next message ...

    Regards
    If you feel like saying "Thank You" for the help received ...You can click on the "Like" icon ...just underneath ... ... in the bottom right corner ...

    Comment


    • #3
      Thanks Carim for the message. Attached is the test file.

      Regards Test data.xlsx

      Comment


      • #4
        Hi again,

        Based on you sample file ... attached is a proposal to be tested ...

        Hope this will help
        Attached Files
        If you feel like saying "Thank You" for the help received ...You can click on the "Like" icon ...just underneath ... ... in the bottom right corner ...

        Comment


        • #5
          Hi Carim,

          Many thanks for your assistance. It's greatly appreciated.

          However, there is a slight problem. The formula should be dynamic, so that it checks the whole row for any negative numbers. Any negative number (pre-payment) offsets the oldest positive debt. If the pre-payment is greater than the oldest debt, then the remaining pre-payment offsets the next oldest debt, and so on, until all pre-payments are exhausted

          I have attached the file with the comment and an example, hopefully to make it clearer.

          Regards Copy of Test Samaria data.xlsm

          Comment


          • #6
            Hi again,

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

            Hope this will help
            Attached Files
            If you feel like saying "Thank You" for the help received ...You can click on the "Like" icon ...just underneath ... ... in the bottom right corner ...

            Comment


            • #7
              Many thanks Carim. Appreciated. Works like a treat

              Comment


              • #8
                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 ...
                If you feel like saying "Thank You" for the help received ...You can click on the "Like" icon ...just underneath ... ... in the bottom right corner ...

                Comment


                • #9
                  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

                  Comment


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

                    Regards

                    Comment


                    • #11
                      Hello,

                      Will take a look at your macro ... as soon as I am back from my business trip ...
                      If you feel like saying "Thank You" for the help received ...You can click on the "Like" icon ...just underneath ... ... in the bottom right corner ...

                      Comment


                      • #12
                        Thanks Carim. I will really appreciate

                        Comment


                        • #13
                          Hello,

                          Quickly tweaked the macro for Client C ...

                          Further tests might be required ...

                          Hope this will help
                          Attached Files
                          If you feel like saying "Thank You" for the help received ...You can click on the "Like" icon ...just underneath ... ... in the bottom right corner ...

                          Comment


                          • #14
                            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)

                            Comment


                            • #15


                              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 ...
                              If you feel like saying "Thank You" for the help received ...You can click on the "Like" icon ...just underneath ... ... in the bottom right corner ...

                              Comment

                              Working...
                              X