Solved

by excel How can I distribute the payment  based on the due date of  invoices " Oldest to newest "

Posted on 2016-10-14
10
57 Views
Last Modified: 2016-10-16
Dear Sir ,

I have a collection of payments from several clients,as well many of  invoices for them , I have summed the payments for each client to be as one payment  in order to cover several Invoices .

My Question now , by excel How can I distribute the payment  based on the due date of  invoices " Oldest to newest " to settle it ,Knowing that maybe the payment closed  full or part of the bill.



My Case

1-Payment
Customer Name    Payment
A                                  5,000$
B                                  3,000$

2-Invoices

Customer Name    Invoice No     Invoice Amount           Bill Date                 Solution /  I looking for it  
A                                          5                   3,000$                      Jan 2016                                  3,000
A                                          6                   2,300$                     Feb 2016                                  2,000

So, please note regarding invoice 5 ,300$ not taken  because the payment from customer A was only 5,000$ so there is remaining balance from invoice 5 =300$ will settle in next payment

Your cooperation is highly appreciated

Best Regards
Ala'a Khalil
0
Comment
Question by:Satech Jubara
  • 6
  • 4
10 Comments
 
LVL 39

Expert Comment

by:als315
ID: 41844987
Can it be done in VBA Macro?
0
 

Author Comment

by:Satech Jubara
ID: 41845224
Dear Als315,

Frankly I don't understand how i can d , i have enclosed my case in excel , could you please help me how i can do it

Thanks
0
 

Author Comment

by:Satech Jubara
ID: 41845226
Dear Als315,

Frankly I don't understand how i can d , i have enclosed my case in excel , could you please help me how i can do it

Thanks
0
 
LVL 39

Expert Comment

by:als315
ID: 41845409
We can do it in macro, but in this case it will be xlsm file and you should allow macros. Can you upload excel file with some sample data?
1
 

Author Comment

by:Satech Jubara
ID: 41845536
Dear ,

Please see the attached
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:Satech Jubara
ID: 41845635
could you please send me the code ???
0
 
LVL 39

Expert Comment

by:als315
ID: 41845767
Sorry, nothing is attached. You should select file ("Attach file") and then press "Upload File"
0
 

Author Comment

by:Satech Jubara
ID: 41845784
Dear Alexey ,

please see my attached file .

Your cooperation is highly appreciated :)

Thanks , once again

Ala'a Khalil
My-case.xlsx
0
 
LVL 39

Accepted Solution

by:
als315 earned 500 total points
ID: 41845843
Look at sample. Allow macros and press Calculate button
My-case.xlsm
1
 

Author Closing Comment

by:Satech Jubara
ID: 41845884
Dear als315

Yes great , Thank you so much my friend, this sheet will help me to solve many cases related with account receivable balances

Thanks , once again  

Thanks
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now