• 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.

Aging Report in one Excel Sheet

A

Abdul Azeez

New Member
Hi,

I need to prepare Aging Report in an exccel sheet. Like below


Co.Name Total Due 30Days 60Days 90Days 120Days Above150Days

ABC Co. 10,000.00 2000 3000 4000 1000 0

XYZ Co. 2,000.00 0 1000 1000


Kindly advice me how I can prepare..my email id: smartazeez25@yahoo.com
 
Hi Azeez ,


How is your data organized ? Can you copy + paste say 10 rows of data , from which you wish to prepare this report ?


Narayan
 
No. CUSTOMER AMOUNT 30 Days 60 Days 90 Days


1 Beta Information 7,000.00 1,200.00 1,800.00 4,000.00

2 Capital 1,483.10 1,483.10

3 Life Line 30.00 30.00

4 High Star 700.00 700.00

5 IKEA Trading 1,651.00 1,651.00

6 KPMG 1,895.00 895.00 1,000.00

7 Chillies 11,935.00 5,635.00 6,300.00

8 GINCO Cont. 8,834.00 4,894.00 3,940.00

9 Shahid Al Jamil 7,760.00 7,760.00

10 SHAREE AL-MAJAZ 425.00 425.00

11

12

13

14

TOTAL DUE 41,713.10


These entries I inserted mannually, Kindly advice me.
 
Hi Azeez ,


What is it that you would like Excel to do ? As far as I can see , you are entering the individual amounts , which are due for 30 days , 60 days and so on. Using Excel , the sum of all these amounts can be put in the Amount column. Other than this , I am not able to see what Excel can do.


If you enter data , in the form of amounts and their due dates , then Excel can distribute them in the columns <30 days , between 30 and 60 days , and so on. Is this what you wish to do ?


Narayan
 
Hi Azeez ,


Can you then copy + paste some data from your worksheet ; having data always makes it easier to test one's formula.


Narayan
 
Hi Azeez & Narayan!

here is a link to a file that I use.

https://skydrive.live.com/self.aspx/.Public/creditors-debtors-age-wise-report.xlsx?cid=25637f1c5ce5899f


Hope this would be useful. A date field is very important to calculate the number of days.

You can modify it for your use.


Narayan ! I need your help in clubbing all the data according to their codes and give the figures.


Thanks,
 
sorry !

the link is


https://skydrive.live.com/redir.aspx?cid=25637f1c5ce5899f&resid=25637F1C5CE5899F!124&parid=25637F1C5CE5899F!105
 
Hi shajan ,


Can you please give more details ? I've downloaded your worksheet , but I'm not able to understand what your requirement is.


Narayan
 
The current listing is listed bill wise. Each customer has a code in column E and there are repetitions. i.e same customer, many bills with different dates. I would require one customer per row and the total dues, 30, 60, and so on in the next columns.


Hope I was able to explain it well. Thanks Narayan.
 
Hi shajan ,


Can we consider one customer , code 218 ?


Most of the amounts invoiced have been paid , except for the following :


4784 ..... 11-Jan-12 ..... 18,901

4686 ..... 5-Jan-12 ..... 6,665

4685 ..... 5-Jan-12 ..... 5,274

4484 ..... 26-Dec-11 ..... 25,053

3312 ..... 19-Dec-11 ..... 8,754

4138 ..... 7-Dec-11 ..... 15,467


Can you say how the calculations should be done for the above 6 records ?


Narayan
 
That is correct. all paid, and all the above are below 30 days - the days appear in column W.

Assuming that the invoice dates are different, then it should appear as


Ccode: 30 days : 60 days : 90 days : 120 days.

218 18901(1 bill) 11939 (2 bills) 25053 (1 bill) 24221.(2 bills)

219

220

221


i.e one Ccode in one row with totals spread out according to bill dates.

Thanks.
 
Hi Narayan,


Pls check the link, and advice me how I have I have to format this sheet. We need to check all the customers and their aging in a sheet.


https://skydrive.live.com/?id=7B3F91FDEF9B676B!108&cid=7b3f91fdef9b676b&&event_source=CloseButton#cid=7B3F91FDEF9B676B&id=7B3F91FDEF9B676B%21105&sc=documents


Thank you for your co-operation.

Azeez
 
Hi shajan / Azeez ,


Can you tell me whether the invoice amount will always be paid in full , or is part payment possible ?


Narayan
 
Hi, Abdul Azeez!

As far as I can see from your uploaded file, it seems to be the output desired, that's to say the aging report. What we need to perform that solution is the input data (which I hope it's within another Excel file). For example, for Beta Co. that has 3 aging periods (3000 for 1-30, 2000 for 31-60 and 1000 for 91-120) where and how is stored the data of the at least three invoices/documents that sum 6000? That's what we need to try to help you, not only the desired output you uploaded, but the input as well.

Regards!
 
Part payment also possible, but that data would come from another workbook, and should be adjusted against the oldest unpaid bill. Thanks Naryan.
 
Hi Narayan / SirJB7


Partial Payment is also possible. As I mentioned my link was desired output by manually. I need to format the input file.


I will highly appreiate if could favour me in this regard.


Thank u,

Abdul Azeez.
 
Hi Azeez ,


If you want the final report to be generated from your input file , please upload the input file , so that the report can be generated.


Narayan
 
Hi shajan ,


Can you check out the following link ?


https://skydrive.live.com/#!/view.aspx?cid=754467BA13646A3F&resid=754467BA13646A3F%21146


I have copied your Sheet1 to Sheet1 (2) , and inserted the formulae in this copied sheet , in columns P , Q , R , S , T and U. Is this what you are looking for ? Or do you want a further summary customer-wise ?


Narayan
 
Thanks for your efforts Narayan. In column U, all amounts greater than 180 days should appear. I have changed the formula in "U594"., but there could be a better way to do the same.

Also need summary customer-wise. I have done some work, but please check in sheet named "summary"

I have sent the file to your email. Thanks.
 
You must log in or register to reply here.
Back
Top