I am quite sure that you will definitely love to create ageing analysis report using Google Sheet Formula as it can be a great time saver for you. Why? Because once you complete this age analysis spreadsheet, you will see your data automatically moves among columns based on ageing. That means once you create the report you can leave it untouched. It can automatically adjust the values age wise. Therefore no need to manually cut and paste values between age wise columns.
The best way to create an age analysis report in a spreadsheet is using the Pivot Table. But you can make a custom ageing analysis report using Google Sheets’ IF or IFS logical function which is pretty easy.
We can learn how to create an accounts receivable ageing analysis report on Google Sheet and of course using formulas. As told above, no pivot table for this purpose. In between let me tell you one more thing. Similarly you can create accounts payable age analysis also. Let’s begin the steps to create ageing analysis report using Google Sheet formula.
This’s a very simple tutorial. Actually I wish to make few templates but unfortunately there is no option to submit to Google. So you should follow this tutorial in order to create a custom age analysis report in Google Spreadsheets.
I’m straightaway coming to the steps. This ageing analysis report can also prepare in Excel using the same steps.
Ageing Analysis Report Using Google Sheet Formula
Below is the format of ageing analysis report which we are going to prepare in Google Spreadsheet using formulas. I mean our finished accounts receivable age analysis report will look like as below.
As a side note: Don’t take the below data seriously. It’s just a sample data and treat it likewise. You can use your own data in the tutorial.
Prepare the format and fill the data except in the highlighted cells. In the highlighted cells we will apply the IF formula which will automatically adjust the value as per ageing.
Apply the below formulas in respective cells. That’s the only thing required to complete your accounts receivable report.
1. In Cell F6 the formula is =IF(N6<30,E6,””)
2. In Cell G6 the formula is =IF(AND(N6>29,N6<60),E6,””)
3. In Cell H6 the formula is =IF(AND(N6>59,N6<90),E6,””)
4. In Cell I6 the formula is =IF(AND(N6>89,N6<121),E6,””)
5. In Cell J6 the formula is =IF(N6>120,E6,””)
6. In Cell N6 the formula is =TODAY()-D6 [This cell is outside your format. I intentionally put it there. It’s not part of the format]
Now copy paste the formula to below cells in the same columns. That’s it. You have finished completing Ageing Analysis Report .
Change the date in the “Date” column, that is Column D, to see the invoice value skips columns as per age wise.
You can add or insert as many rows as you wish. But when you add new columns to the right or between Columns F and J, be careful. You may end up with undesired results.