Aging Analysis Reports using Excel - How To

[@asoahmedkarim]

that is just great.. not only creates the formula, he explains the logics as well. that is how it should be done

[@DeoKhelawan]

That was a super job. my friend. Continue your great work

[@saquibsher]

Really learnt the firmula.... Excellent explanation of formula.... thanks... keep uploding videos plz

[@kumarsoorier1968]

Hi Hasaan, you are one of the best. God bless you for your kind free presentation to the mankind.

[@HasaanFazal]

Happy it helped.

[@ravi-wf1jz]

Thanks for the explanation!

[@leninpv]

Thank you Sir. I am in search and I got it.Thank you for simplified and clear presentation.More useful for accounts people

[@_AS_Birds_Hiba]

thank you ,, very useful just would like to know about the paid invoices.?? one column where i can add the invoice paid than it can be auto less.

[@GoodLuck-vl1gv]

Its wonderful AR Report for presenting to the Company 👍👍👍👍

[@monissyed09]

Hi I have the ageing report ,I need to put conditional formatting on the data that I have basis ageing like 0-7 days should give me green and 8 and above should give me red.

[@staryeyedify]

Very well done!! Thank you for the crisp and clear explanation.

[@aniruddhamehta7727]

Very nice presentation, easy to understand. May you release more and more such work to help young students.

[@HasaanFazal]

Doing and sure will! Thanks for visiting :)

[@adhamrashad7585]

that was great explaining i love it so much , keep going ... thanks ♥

[@tubespirit7]

Thanks for a great and educational explanation!

[@HasaanFazal]

Glad it helped

[@deepakshweta4348]

In which window we can make this report

[@HasaanFazal]

By window if you mean worksheet then you can have data and the report on two separate worksheets if needed.

[@livvaldez8929]

Thank u sir! This is excellent and clear!!!!

[@khalidmajeed2886]

mashallah.Alllah aap kaay ilm maay izafa kkaray aaameen. sir this can do in power query ..how

[@thargyi85]

thank you so much for the video and worksheet to practice. should the formulas be: =IF(AND(H6=1),D6,0), =IF(AND(H6=31),D6,0), =IF(AND(H6=61),D6,0), Thanks again.

[@goodman-it2qy]

Thank a lot, I have to buy Ms excel accounting software, please how is it possible.

[@rjbanavar]

Thank You good learning, its more excel technical, when compared to easy Pivot Table.

[@ankush-m2k]

can't download the practice file.please solve the problem

[@tammyj123]

This is a great training video! The best I've seen! I need to do something similar for my manager, but she is looking for aging as of month-end as part of month-end analysis. So, instead of (TODAY) I need to do from a specific date. I just can't figure out. Any help would be appreciated!

[@HasaanFazal]

Most probably you have the specific date in a certain cell. So simply replace the today function with that cell address in the formula and you are good

[@hussainbilagi8891]

that is creativity thank u hassan bhai

[@SujathaS-sx3kg]

Nice explaination brother! It's really helpful

[@pradnyawakle284]

thank u, i was looking for the same template. it help me lot

[@Anarchyhimself_]

Why is it not working on just my pc.Could you tell me if there are any prior requirements for this formula?

[@HasaanFazal]

No prior requirements. It should work for you as well. Let me know what error are you getting

[@Anarchyhimself_]

@@HasaanFazal #Value! error. I took some dates as samples and put the formula =today()- the desired cell no.

[@poojaaggarwal5365]

Please help what formula use in days outstanding

[@sanniashah4877]

Very very help full. Thank you sir

[@appawagyei8140]

Hi, I want to understand how you set the due date. I don't seem to get it yet..

[@SarelvdWalt]

This is great, I really like the explanation. The problem I have here though, these videos don't take into account the payments the client made. As an example, if a client gets invoiced 500 each month, and they pay 300 each month - how do we calculate what the age amount is in each section?

[@drizzle786]

perfect guidance thanks

[@neetz_insta]

Should we add $ before H6?

[@indracanti75]

In my company, receipts not adjustued against debtors invoices. Please let me know any solution to get only exact Invoices outstanding

[@neildelapena3515]

Hi sir your presentation was awsome.can i ask? What if i sell motor vehicle in installment sales.then terms is 36month.. how can i do it in aging?

[@VikasSingh-mt7xp]

Thank you Sir!! Are you from India?

[@nagarajumogili6372]

Post a video of Balance sheet analysis... Please

[@legacygk2811]

Thanks a lot Sir!

[@ravindrasarawate4052]

Thanks for explaining how to create Debtors Ageing Schedul. However, we also need to summarize it Customerwise. How can we do so?

[@HasaanFazal]

Please check my latest tutorial on aging analysis using pivot tables that let you do reports on customers basis: kzbin.info/www/bejne/oaiyaICgrMulitk

[@shakicommissariat]

Hasaan, this was most informative, and easy to understand. Thank you. Can you please indicate a way to put zeros all across the aging schedule if I see (from another column) that the invoice has been paid? This will save me another step. Thanks so much.

[@HasaanFazal]

That step is taken even before aging schedule is made. We filter out all the invoices that has already been paid. Therefore any invoice appearing in schedule is basically an unpaid one. Now an invoice is unpaid either it's not due yet or it has been due and still unpaid. Hope this helps. Let me know if you have more questions.

[@shakicommissariat3698]

@@HasaanFazal So after a payment is made, I assume you would move that entry back to your other spreadsheet that contains the details of all payments. If there was a way to do everything within the same (massive) spreadsheet, a lot of time can be saved. There has to be a way to make Excel look at another cell that shows if the invoice is 'PAID' and put a zero all across if that condition is true. You have been most helpful. Thank you.

[@HasaanFazal]

Well anything is possible and this situation is no exception. I simply stated the usual practice. But if you want to show/analyze all the invoices... Paid or unpaid then yes we will have to put another condition in the formula to first check if we have received any payment. If full amount has been received then 0 otherwise remaining amount. That does require change in formula.

[@HasaanFazal]

Now the formula for this course of action is dependent on the format and design of cash book where payment received are recorded.

[@HasaanFazal]

So it's hard for me to give a formula fitting every situation and format of cash book.

[@vinaysrikanth6664]

#So, how about interm receipts/payments received. How do we deduct those amounts?

[@HasaanFazal]

Aging analysis is done after all the adjustments. So interim payments are accounted for first. And aging is done on balances.

[@42Laj]

Thank you for this. I have to present aging of customer complaints for medical device product. I have only beginning skills with Excel. How did you do the formulas? I will be doing aging progressions from 0-120 days. Appreciate any other tips you can provide.

[@HasaanFazal]

42Laj you will have to add one more column for 120 days on same basis just like 90 days but with formula adjusted for 120 instead of 90.

[@42Laj]

Hasaan Fazal Thank you so much!😊

[@thomasblankson4425]

how do you compute ageing with tax component?

[@simphiweartwellmalaza4273]

Please help, when i use the if formula for age analysis on the H6. its populate an automated message that says" The formula you typed contains an error. i am not sure if it is my computer setting. please help and your help will be highly appreciated

[@HasaanFazal]

Are you practicing with same sample data? And what formula are you putting?

[@vinaynagaonkar8592]

I wish to put 2 conditions in outstanding column so that if my dues are nil then it should show 0 else it will calculate no of ostg days

[@anayatchaudharyvlogs1729]

Very useful information thanks

[@kishorprajapati4681]

👍nice👌, thank you.

[@ErikPT]

I wish I used (TODAY) function in my assignment... will definitely use this formula for my quiz!

[@cosmasasinde]

Nice lecture sir

[@ميرانوفا21]

Thank you so much

[@saragriffiin5799]

Please answer my question. Suppose my year end is 31st December 2018. Then is 0-30 considered December, 31-60 as November, 61-90 as October etc. Please answer.

[@HasaanFazal]

Aging analysis is conducted from a particular day onwards. Not particularly year-end. Say you want aging analysis from year-end date which is December 31 then: 0-30 is month of Jan 31-60 is month of Feb 61-90 is month of Mar

[@moonaabood5331]

Hello cn u help me with my project..it's on accounts receivable and I have to present annual age analysis and interpret it..

[@cmamallik674]

What if recipts are not against invoices?

[@mrvipanjum]

All things explained good but if we want to add service charge for late payment on principal amount then how can we do kindly explain

[@PMWorkshop]

Very clear thankyou

[@vangalashivashankar]

super useful

[@santoshjha3405]

Great sir,thanks for guidance

[@emmanuelhakizimana1998]

thank you very much. I have understood.

[@HasaanFazal]

Glad it helped!!

[@johnsonovere1111]

Great!

[@atishdaa]

thank u

[@syedraza2365]

very helpful

[@shrikantbadge3978]

Perfect

[@satyavanivankayala9619]

Nice video. Thank you :-)

[@alisodai]

i need the formula that is the formula should bring the customer names and the total amount of 30 days , the customer names with their total due amount of 60 days, name of the customer with their total due amount of 90 days. pls

[@HasaanFazal]

You can "concatenate" the two cells in required cell i.e. one containing amount and the one containing name. Currently the formula for 31-60 days column is following: =IF(AND(H1030),D6,0) To have named added at the end simply change the formula to this: =IF(AND(H1030),D6,0)&", "&A6 And drag it down to populate the whole column. Hope this helps!

[@vishalmulik]

I think this would be more easy if find out first aging and use pivot table..

[@musicinnature6620]

nice!

[@aijukareagantall4676]

Good

[@sunilhalder971]

i want to know aging concept clearly , can you help me?

[@HasaanFazal]

Sunil Halder sure you can ask your questions here

[@dammyb2247]

Let me use ur word "Perfect"