Create Your Billing Statement in Excel for Purchasing Effortlessly

Billing statement in excel for Purchasing

welcome to new video tutorial in Microsoft Excel and in this video we are going to learn how to do account receivable analysis in Microsoft Excel based on this data of invoice number invoice date customers name and invoice amount and based on this data we will calculate here in the F column the days from the invoice date means the Aging dates and then we will also try to calculate the group wise account receivables like the receivable which is in the group of 0 to 30 days 30 to 45 days 45 to 60 days and 60 days plus above and we will do this by two different methods the one first one is the data sort and filter method and another one is the formula method by using these two methods we will try to find out the group wise invoice aging and we will also learn how to find out the group Y is using by using the pivot table we can do this by using pivot table also so let's begin analyzing the account receivable first of all we will calculate the Aging days here in this F column so to find out the Aging dates we will use this formula equal to Here I Am first typing the paging dates and here in this cell the formula will be equal to today then Open Bracket close bracket this today function gives the current date for example today is 20 October 2022 and this formula gives us the current date if I hit enter here you can see this is the current date this is in the number format we need to change this to the date formatting to display the date by going here in the sort date option and here you can see the current date is given so when we deduct this invoice date from the today's date we will get the Aging dates so go in the formula here then press minus sign then click on the invoice date so this formula will give you the days from the invoice date the day account will be given here here this is given on the date formatting so we need to now change this to the number formatting to see the days now here you can see 103 days is the Aging days the invoice was made on 9th of July 2022 today is 22nd 20 uh October 2022 and the days is now 103 days so in this way we calculate the Aging days in Microsoft Excel now you can simply go here in the plus button double click and get the Aging days for other invoices here you can see these are the aging days now based on the Aging days we will analyze the account receivable for example if you want to see what is the total outstanding or total receivable that falls under the group of for example 60 days and above in that case you can use this data filtering technique select the heading then go to data then click on the data filter now here you can see the filter is applied to The Heading and if you want to see how many receivable is in the group of 60 days plus above in that case you can display the value here by using soft total function and you can use this filter option by going to the number filters and then here you can see the greater than or equal to option greater than equal to then here you can type 60 if you want to see the receivable which is from 60 days and above then you can type here 60 then click OK which will give you the total outstanding amount which is in the group of 60 days plus so before doing the filter here I am using the soft total formula which will give you the total so press equal to then types of total bracket open the function number for the sum is 9 so type 9 then give a comma and in the reference 1 reference 2 you can select the invoice amount from the beginning to the end in this way now close bracket press enter and here you can see this is the total receivable amount and if you want to view the receivable amount that is from 60 days and above simply go here then go to the number filter option then choose this greater than or equal to then here you have to type 60 now click ok and here you can see this is the invoices that has crossed 60 days and the total receivable is this amount 170 65788 similarly if you want to see the receivable which is from 30 days and below then you can go here in the number filters then here you can see option list then click on less than option and then here type 31 it means the Aging days 0 to 30. less than 31 means 0 to 30 days now click OK and here you can see the invoice having the Aging days of 30 days and the list is given here which is one two eight eight nine four zero similarly you can do different kind of analysis from here if you go again here in the number filter and if you want to see the Aging days or account receivable which falls under the group of 45 to 60 days then you can go here in this between option go to between then here in these type uh 45 greater than or equal to 45 and here you can type less than or equal to Value which is 60 so you can click now okay and here you can see this is the receivable which is in the group of 45 to 60 days so in this way you can do account receivable analysis by using this filter option now I am here selecting all values so this is the way of analysis using the filter option now you can do the same thing by using the formula technique as well for example here I am showing you if you want to find out the total receivable under the group of 0 to 30 days in this case you can use formula here to find out the value so the formula here will be you can use some if formula in this case and I am showing you how to use semi formula equal to then type Summit Open Bracket and here in this range we have to choose the criteria range so select this aging days range from the first to the last in this way then give a comma and here in this criteria we need to Define our logic which is we are going to calculate the total a receivable amount in the group of 0 to 30 days so here in this criteria we have to type double quotes then less than 31 this is our criteria to find out the total outstanding or receivable amount in the group of 0 to 30 days in this criteria we have to we have to select or we have to type less than 31 it means 0 to 30. now give inverted comma give a comma and here in this sum range we have to select this invoice amount rents from the beginning to the end in this way now close bracket and press enter now here you can see the total receivable amount in this group is calculated which is one two eight eight nine four zero if you go here in the data filter also you will get the same result if you go to the number filter from here go to list then then here if you type 31 and click ok here you can see the result is 1288940 and the formula also calculated the same result so we can do this by these two methods one is data filter and another one is the formula method similarly if you want to get here another group of value for example 45 to 60 days values then again you can use here formula and in this case we have to use the sum IPS formula because we will have two criteria here in this case now let's use sumeeps function here to calculate the total receivable amount which falls under the group of 45 to 60 days so this formula can be used here equals then type sumits sum oops then Open Bracket and here in this sumeeps formula you have to first select the sum range this invoice amount range is our sum range so select this from the beginning to the end then give a comma and here in this criteria range 1 we need to select this uh aging days range because we are going to uh calculate the invoice receivable based on the days that's why our criteria is the days and answers so select this from beginning to the end in this way then give a comma and here in this criteria one we need to now Define our first condition so our first condition will be type inverted comma then give greater than equal to 45 this is our first condition Now inverted Perma close then give a comma and here in this criteria range 2 we need to again select by aging these range in this way then give a comma and in the criteria 2 we need to Define our second criteria which is inverted comma and this is less than equal to 60 this is our second condition now inverted comma close then bracket close and press enter and here you can see the receivable which comes under this group is displayed which is one seven one three nine eight five if you verify this from the data filter you will get the same result you can go here in the Aging days go to number filters then go to the between option and then here in this is greater than or equal to type 45 and here in this less than or equal to type 60 then click OK and here you can see your result is one seven one three nine eight five and why the formula method also the same result is displayed so in this way we can analyze the account receivable by using the data filter and by using the formula now let's go to the another technique of analysis which is pivot table analysis technique and to create a piper table analysis from this data first of all click somewhere in the data then go to insert menu and after going to insert menu go to pivot table then click on this from table range now here you can see whether you want to display the analysis in the same worksheet or existing new oxid or existing oxid here I am choosing the new worksheet option now click ok after clicking OK the private table filled list are shown here and here we need these uh aging days and invoice amount so click on these two values then here in this uh fields we need to drag this aging test to the row filter so drag this to the rows option we need to drag this to the columns so drag this cool edging this to the columns filter in this way after doing this then you can right click on this report go to the group option and you can group these values by 30 days in interval so here in the starting eight type one and here in the ending it it shows the last using this which is 172 so by typing 1 here then go to this buy option and here you can put the 30. after you do this then the outstanding or receivable amount will be grouped into 0 to 30 days like 1 to 30 days then 31 to 60 days 61 to 90 days and so on now you can click OK and here you can see the receivable amount is grouped here you can see this is the amount which is in 1 to 30 days aging similarly this is the amount which is in 31 to 60 days similarly this is the amount which is in 61 to 90 days group so in this way we can do account receivable analysis in Microsoft Excel by different methods we learned about analyzing the account receivable by using the data filter by using the formula and by using the pivot table analysis and if you want to learn more about this sumif and some ifs function then you can go to my channel video section where you can find a different video tutorial in someif and sumifs similarly if you want to learn more in pivot table then you can go to my channels video section where you can find a different video tutorial on private table data analysis and this much for today's video thank you very much for watching if you are new in my channel I would like to request you to subscribe my channel so that you will never miss similar kind of videos in future once again thank you very much for watching we will again meet on next video