Create Your Invoice Summary Template for R&D Effortlessly

Invoice summary template for R&D

hello everybody welcome back to new video tutorial in microsoft excel and in this video i am going to show you how to analyze account receivable in microsoft excel and if you can see here for example of account receivable data the invoice number is given on the column a customer name is given on the second column or the column b and date of invoice is given on third column and the invoice amount is given on fourth column so this is our raw data and we are going to analyze this account receivable from different angles using fiber table and first of all we need to insert some more columns here to make the analysis easy and here in this after this invoice date insert two more columns insert the entire column ok and insert another column also and here in the first inserted column write due date due date and in next column write number of days so we need to insert these two more columns and here you can see credit side 45 days it means that the company gives the customer credit side of 45 days it means that if the invoice is made and today the maid invoice will be d1 due after 45 days so to calculate the due date here in this column we use a formula which is equal to the invoice date loss then we need to add 45 days here so write 45 and press enter here you can see if the invoice was made on 2nd january 2020 the invoice will be due at 16 february 2020 so we get the due date by adding 45 days in the invoice date now you can simply double click on this to fill the other cells with due date formula now here in this number of days we use a formula which is equal to today this two-day formula gives us the current date for example if i hit enter here the current date is 19th february 2021 and the today formula gives us the current date so we need to deduct this invoice date from the current date to get number of days from the invoice date so here go to the formula and subtract the invoice date after subtracting here you can see the number of days is given on date format so we need to change this date format to uh number format so to change this into number format you can go to home menu and here you can change the date format to number format here the number format or the number of days is given and you can delete the decimal digits after decimal by simply clicking here now after using formula you can simply double click here to autofill other cells down here you can see it is now filled after preparing this data now we can create a pivot table to analyze our account receivable from different angle and to create pivot table first of all click on the data then go to insert menu then here you can see pivot table options so click on pivot table and here you can see create pivot table dialog box from here we can change the location of our table here the new worksheet is automatically selected if you want to create a table in existing worksheet then you can choose existing oxide from here here i am choosing new oxide now click ok now here you can see a pivot table is created in another sheet sheet number four and from here we can make analysis of our account receivable for example here you can see customer name invoice date due date number of days and invoice amount for example if you want to know the total outstanding customer wise in that case you can drag this customer name to the row label here you can see row label drag customer name into row label then you can drag this invoice amount into values field here you can see invoice amount is given after dragging customer name into row level and uh invoice amount in values here you can see the report is generated from here we can know which customer has how many how much outstanding amount here you can see customer b has this total outstanding or the receivable similarly customer a has this amount of receivable and so on similarly if you want to analyze the date wise or day wise outstanding for example if you want to know how much outstanding amount which is from 0 days to 30 days similarly 30 days to 60 days similarly if you want to know outstanding amount from 60 days to 90 days in this case you can do it from this fiber table and to do this you have to drag this number of days into the coulomb level here you can see number of days so drag this to column level here column level and click ok now here you can see the pivot table is generated and to make this 0 to 30 and 30 to 60 we have to go here and right click on our mouse then go to this group option here you can see group option is given and here in this buy field we need to give the days interval so here write 31 and click ok now here you can see it is now grouped here you have to go to group and then write 30 here and click ok now here you can see the invoice overdue from one day to 30 days given here 1 to 30 is given here 31 to 60 is given here 61 to 90 is given here the customer wise and date or day group wise data is given here from these we can know which customer has overdue starting from 1 to 30 similarly starting from 31 to 60 days and so on similarly if you want to view this report as month wise you can deselect this number of days and from here you can select this due date and drag this to the column level and here you can see date wise total outstanding or the datewise account receivable data is given here and you can now group this into month wise so right click on this then go to grouping option group then here you can choose month and year because we have data of 2020 and 2021 as well so select month and years in this grouping option then click ok now here you can see the outstanding or receivable amount is grouped month-wise here in you can see february 2020 march 20 april 20 may 20 and so on this is the month-wise outstanding of these customers from this we can know customer b has 241249 account receivable which is being due on february 2020. this is the outstanding amounts being due on march 2020 similarly here you can see different months are given and if you go here this is for 2021. from this you can know in which month how much outstanding amount is being due so in this way you can analyze similarly from here you can analyze this report by here i am deselecting all the fields and i'm selecting customer name and then invoice amounts and if you want to know how many invoices per customer is overdue or outstanding then you can go to this value field setting from here value field setting then from here you can choose different options to know the number of invoices receivable you can go to this count option and then click ok and here you can see count of the invoices from this we can know customer b has 36 invoices outstanding similarly customer a has 39 invoices outstanding or receivable and so on so in this way we can analyze our account receivable data from different angle using this pivot table now i am showing you how to analyze this account receivable data by filtering option filter option we can do this by filter option also for example here i am selecting this heading and then applying data filter from here and here i am using soft total formula equal to subtotal open bracket and here write 9 then give a comma and select this invoice amount range from here select invoice amount range from the first invoice to last invoice here i have selected all the invoices then close bracket and press enter now the total is given here this is the total of all the customers outstanding and if you want to view a single customers outstanding then you can go to filter option here click here and then select the customer you want to view here i am selecting customer e and clicking ok and here you can see these are the invoices of customer e which is receivable from company the total receivable from customer e is given here which is 490 6810 similarly if you change another customer from here customer a then this value is changing in this way we can analyze our account receivable similarly if you want to know the month wise invoicing total you can select the you can use auto filter here in this invoice date if you want to know in which month in which month what was the outstanding amount or what was the invoice amount then you can go to this invoice date and select the one from here here you can see if you want to know the invoices made on for example may 2020 then you can select may from here click ok here you can see the invoices made on main month is given which is 344.2282 and if you want to view the customer a invoice which is made on may month then you can again select customer a from here and click ok and here you can see these are the invoices made on main month for the customer a and the value is given here so in this way also we can analyze our account receivable now i am clearing filter from here i am selecting all and here also i am clearing the i'm clearing the filter similarly if you want to know in april 2021 how much invoices is being due in that case you can go to this due date and apply auto filter click on this filter option in the due date column and then deselect all then select expand this 2021 and from here you can choose april click ok here you can see these two invoices are being due on april month so in this way you can view in which month how many invoices are being due similarly if i change this to february here you can see the invoices being due on february 2021 this is the invoices being due on february 2021 and from here we can choose customer if you want to view a particular customer record here for example customer e in this case you can view the record here so we can analyze the account receivable by using pivot table and filter option and the best way is pivot table the pivot table is the easiest way to analyze large number of data so you can use the pivot table to analyze your account receivable and if you prefer to analyze by using the filter option then the filter option is also okay but private table is the best choice which is time saving and this much for today thank you very much for watching this video we will again meet on another video and if you have any comments you can write it on comment section below thank you very much for watching the video