Aging analysis is helping accountants since ages and is one of those reports that are prepared mostly in Excel to track both receivables and payables. So today we are learning how to conduct aging analysis in Excel. Before we learn how to create aging reports in excel, if you want to learn more about aging or ageing analysis concept do read my explanation: What is age, aged, ageing or aging analysis?
Love reading about Excel? Subscribe my youtube channel dedicated to Excel.
If you are new to this analysis tool and don’t know what it does I strongly recommend reading the above mentioned explanation as only then you will grab the concept of what we are going to do and why we are going to do.
This is what our end result at the end of this tutorial looks like:
In very few words and if I try to define aging analysis in context of receivables or debtors, then it is an analysis that helps me determine when certain sales invoices are falling due. And more importantly since how long a certain receivables are outstanding. First aspect helps me determine expected cash flows. Second helps me determine where recovery department must concentrate its efforts.
A dash at the data and requirements
Open up the workbook you downloaded and its a fairly simple data consisting of four very fragrant debtors. Each has several invoices against its name with different due dates. We want to classify invoices as follows:
- Not due: The invoices which that has not fallen due yet
- 0-30 days: The invoices that are past due for days between 1 to 30 days.
- 31-60 days: The invoices that are past due for days between 31 to 60 days
- 61-90 days: The invoices that are past due for days between 61 to 90 days
- >90 days: the invoices that are past due for more than 90 days.
Understanding the approach – Aging Analysis Reports in Excel
So it seems simple and it is simple if you know how to go about IF function and how to make multiple IF statements using AND function. Its all part of today’s discussion.
But as I always like to raise the bar a little higher than requirement so I will going one step further and will do:
- aging analysis using the slabs give above i.e. not due, 0-30, 31-60 etc.
- compute the number of days since the invoice is outstanding
So lets get started!
Watch a walkthrough OR continue reading for something additional!
You can either watch the following video on aging analysis in Excel to prepare basic aging analysis report OR continue reading to learn additional techniques involving conditional formatting and sparklines!
Adding column headers
We need to add few headings here to accommodate our requirement. So in cell
- E1 type: Days outstanding
- F1 type: Not due
- G1 type: 0-30 days
- H1 type: 31-60 days
- I1 type: 61-90 days
- J1 type: >90 days
Wings Meaning! – Number crunching Time!
Step 1: In cell E5 put the following formula press Enter:
This formula checks that if today’s date is later or greater than the date mentioned in cell C5 the deduct today’s date from the date in cell C2 to calculate the number of days. However, if today’s date is earlier than the date in C5 then put 0 as a result.
Step 2: To apply the same formula in order to calculate the days for all the invoices simple double click the fill handler and it will populate the cells immediately of column E.
Giving Data even more meaning!
Although we have calculated number of days and manager can easily sort and filter the column to find the invoices exceeding certain number of days. But using conditional formatting feature in Excel we can create a sort of heatmap to show values with long outstanding period as red and the ones that are in favourable time range as blue or similar color. By adding colors to the data, its much easier to identify and focus on important aspects of data. Follow these steps to accomplish it
Step 1: Select the values in Column E that you just calculated using formula. You can make selection with mouse or using keyboard shortcuts. To learn how to select using shortcuts read this: 40+ Excel navigational shortcuts to make accounting job super efficient
Step 2: Go to Home tab > Styles group > click the conditional formatting drop down > click new rule. A new dialogue box will appear
Step 3: Make sure the first option is selected. From the format style drop down selection menu select 3-color scale. This will enable three shades possibility.
Step 4: From type drop-down menu select number option under each of the three categories i.e. minimum, midpoint and maximum.
Step 5: Set the values as following:
- minimum: 0
- midpoint: 60
- maximum: 90
Step 6: Select the colors you like for minimum, midpoint and maximum values. I chose blue for minimum, beige for midpoint and red for maximum. You can select any that suits your need and liking. Click OK
Joys!!! now you can instantly see what invoice needs immediate attention. (It seems this company’s recovery department is doing nothing at all!!!)
Aging Analysis Report in Excel! – Finding the lazy ones!
Now the next part which is our actual requirement i.e. to have the ageing or aging or aged analysis of each of the invoices. Follow along:
Step 1: In cell F5 put the following formula:
This formula checks that if value in cell E5 is not equal to zero then fetch the value in cell D5 as this column is “not due” and this way the value of invoice will be inserted here as “zero” in cell E5 means it hasn’t even fallen due.
Step 2: In cell G5 of column 0-30 days put the following formula:
The above formula is an example of nested IF statements in which we basically have IF function within IF function. In words the above formula will be said as follows:
If date in cell C5 is earlier then the date than today then check if the difference between today and date in cell C5 is less than or equal to 30 days. If it is less than or equal to 30 days then fetch the value from cell D5. If not equal or less than 30 days then put 0. And also if today is later than the date in cell C5 then put zero.
Once the formula is in place. Press Enter and double click the fill handle to apply the same formula down the column covering all the invoices. Following animation helps
Step 3: Next column is of 31-60 days. This is a bit challenging as we two conditions to meet check if the invoice is past due for days upto 60 days but more than 30 days. In other words it must exclude the invoices that has fallen due for in last 30 days. If we don’t add second condition then it will include those invoices as well that have fallen in last 30 days as when I ask it for all the invoices that has fallen due in last 60 days, the invoices fallen due in last days also fall in this criteria. So I have to tell Excel to exclude such invoices. To do this put the following formula in cell K5:
The above formula is an example of multiple IF statements or multiple criteria where each criteria is joined using AND function. AND function does literally the same thing as the and does in language. So the above formula will be read like this in plain English:
If difference between today’s date and the date in cell C5 is less than or equal to 60 days AND the difference between today’s date and the date in cell C5 is greater than 30 days than fetch the value from cell D5 otherwise put 0.
This can also be said like this; fetch the value from cell D5 if both of the following conditions are met:
- if difference between today’s date and the date in cell C5 is less than or equal to 60 days; AND
- if the difference between today’s date and the date in cell C5 is greater than 30 days
Once the formula is in place press Enter and double click or drag the fill handle to apply the formula to rest of the invoices.
Step 4: Next is 61-90 days column and on the same concept as used in above step we will adjust the formula to find us the right invoices. Following is the formula that you will put in cell I2. Press Enter and drag the fill handle to populate the column with formula:
Step 5: Under column >90 days put the following formula in cell J5:
This is a simple formula which is checking if the difference between today’s date and the date in cell C5 is greater than 90 days then fetch the value from cell D5 otherwise insert 0. Double click the fill handle to paste the same formula down the range.
Hurrah!!! You have completed the aging analysis and now you can see what invoice falls under what category. Well job done! Now you can sum the values of invoices to calculate the invoices that are falling in each category. To quickly do that select cell F30 to J30 and hit Alt+= and in the blink of an eye you have the totals! Just excelling at excel every moment. Two thumbs up!!! 🙂
Bonus Tip: Adding Spark to the data! – Sparklines!
Yes we do have total numbers of invoices under each category. But it always good to see the data instead of reading it. To add a little spark to the totals lets blend the eye-candies using Excel’s sparklines feature!
Step 1: Select the area within cell F32 and J 34. Go to home tab > Alignment group > Click merge and center button.
Step 2: Select the merged area you created and go to Insert tab > Sparklines group > click column button. A dialogue box will open. Click once inside the data range and then select the totals you did for each category of invoices. Click OK.
You get a nice looking graph made up for you instantly and that is sitting right inside cells! How sparky is that! If you change the data the sparklines graph will update automatically.
To read more about sparklines please check out this page for full listing of articles on sparklines and their use
Lucky me! Common sense knocked the door!
Well yes I must admit that my brain really had tough day today and probably the marriage with common sense is going through bad patch. But it felt pity and did pulled some neurons and made me think about another way to do aging analysis.
Look at the formula I suggested above. Basically in those formulas I used today’s date and the date in cell C5. I completely bypassed the days I calculated!!! I could have used the days I calculated and building formula would have been even more easier. But I leave that for you to do as I am a person who assume that if you understand a thing through difficult route you must be able to do it the easy way easily! 😉
Well yes I do have one other homework for you. Above is the example of receivables aging analysis. Now try to make the creditors aging analysis in which you have determine which invoice is falling due in next specific time lapses. To do this assignment download this file.
Next step – Aging analysis using Excel Pivot tables
In the above tutorial we used formula approach to get the job done. But we can do this and much more using pivot tables. To learn how to do aging analysis using pivot tables check out: Making Aging Analysis Reports using Excel Pivot Tables – How To
Dear Tutor, our aging analysis presentation is so good.
I am working in a company where i am handling collection process for more than 150 clients.
Each client has separate statement of accounts in separete excel and have several invoices.
I need your assistance to do the aging analysis. What’s the simple method of it.
This is very helpful.
But if I have a Customer ledger or customers ledger and there are some invoice and payment too against the invoice. It may be full payment against the invoice or part payment of the invoice.
In that condition or as per ledger of the customer how can i apply aging formula. Guide me please
its working and i am thankful to person who posted this ageing report formulas videos.
great and helpful.thanks
Can You explain when ther term include Credit Period and Credit Limit, How it could be incorporated.
Thanks in advance
how to do if my creditors have different term period? what formula should i apply?
Interesting twist. For this you will either have to provide term period for each creditor separately or categorize your creditors and use the criteria based on category. Will work on it and come up with a tutorial soon! Thanks for asking!
how can i make a formula in a separate column if one those debtor paid.
Thanks Mr Imran, It was very useful and excellently explained. Thank you very much
Hello, every time I type in the formula from your blog post into excel it comes up as an error, could you help at all?
Here to help always! which formula you are trying to copy/paste and not working?
Thank you for your help, I have another question regarding your post. In step 1 you mentioned =IF(TODAY()>C5,TODAY()-C5,0) what if today date is change to 2 years date, how to make the formula
Do i use the same steps to create a creditors report? payables?
Yes as you are after time elapsed and for that calculation is same for both. The only difference is that here we took receivables and in that case we will take payables.
Am humble for the formula. many thanks.
Please what is the full name of the one developed this formula?
I have a question. How can I calculate average day (ratio ) from the DSO.
Thanks a lot, great article. really helped a lot.
Really very helpful … thanks
Thank you so much, It is really help full..
Very useful. Thanks a lot.
Very Useful, I learned it. Thanks a lot.
Just what I was looking for. I also can be helpful here 🙂 If you ever need to fill out a form, here is
https://goo.gl/tCMIE3a really useful tool. Very easy to navigate and use.
Nice work Hasaan! thanks for imparting your knowledge with us. It helps a lot…More power!
Fabulous instructions and I love the embedded recording of each section as you hover over it! Much recommended to friends and co-workers. Thanks for your time and help on this very useful subject.
very Nice formula, it is very useful for me.
how will you do the stock aging analysis in excel
Its done on similar concept but will do a write up for that as well just to be clear.
Thank you, helped a lot. Just note the “C2” error, which should be “C5” in step 2.
Thank you sooooooo much!!!!
Is it possible to do the same analysis in single column, like below:
Days outstanding | Ageing
29 90 Days
Concatenate it! 😉 Either combine the output of two formulas or combine two formulas inside concatenate function.
I haven’t tried this specific situation formulas in concatenate formula but just tested if it works with functions inside CONCATENATE and it does.
Wonderful formula for ageing analysis
Making Aging Analysis Reports Using Excel – How To
Excellent instructions. On this same concept – How would we forecast AGING RECEIVABLES so we can quickly see invoices due today, tomorrow, the next day etc.? Thank you.
I think you meant to ask the reports that changes if we change the intended date. For example how much will be the receivables or in particular slab at a certain date. For that instead of using TODAY() argument in formulas you can refer to a particular cell and that cell hold the date on the basis of which you want analysis done.
Making Aging Analysis Reports Using Excel – How To
Excellent instructions. On this same concept – How would we forecast so we can quickly see invoices due today, tomorrow, the next day etc.? Thank you.
Thanks Fazal , great and very useful
what if we are using old data for our practice. the use of Today( in formula will be wrong isn’t? what we should use the otherway formula ?
Just mention a specific date in any empty cell and reference to that cell will do.
Great article and it support us for our company business in calculating our sub dealers due status.
Thank you very much.
Senior Manager spare parts
M.G.Brothers Automobiles Pvt Ltd
Love your site. Great article
Very good article . It helped a lot.
Thanks for posting.
Thanks a lot for this much really productive article. Stay Blessed. Ali.
Quick question what would be the formula for calculating the number of invoices/debits per customer in a particular aging bucket?
I have created new aging formula for your data. thanks!