Collaborate on Invoice Log Template for R&D with Ease Using airSlate SignNow

Invoice log template for R&D

how you returned leap and goodly once again and in this video I'm gonna speak about a new dashboard and a template that I've created which helps you track the invoices and the receivables that you collect from customers in a very very effective manner now I have the dashboard in front of me but before I explain to you anything about the dashboard I want to explain to you the logic of the dashboard as simple as I can so I'll take you back to a excel file which is where I'll explain you the logic of the dashboard real quick and then we go ahead and take a look at the features of the dashboard what I have here we are in a very simple excel file in this excel file the first sheet that I have here is the invoices sheet so you have very simple columns like invoice number the date of the invoice the customer to whom the invoice was issued and the amount on the invoice really simple nothing too complicated now the problem is the customer doesn't pay up against the invoice the payments that you receive from the customers are lump sum payments and you have to allocate those payments to the invoices that you have created so in this payment received data we have the customer name we have the date of the payment then when the payment was received and we just have the received amount and you can take a look that we do not have the invoice number here that means the hundred dollars that we received from the customer was lump sum now the problem arises let's say for example if I ask you a question that at the end of the month of January what is the status of the paid and unpaid invoices what you have to do is you'll have to take this hundred dollars take a look at all the invoices that were created in the month of January allocate hundred dollars to all those invoices using a FIFO method first and first out method and then find out that how many invoices have been paid for and how many invoices are still pending now this is easily done using the template that I have created I want to show you a very very small demo here now let's just go take a look at a very simple calculation here so I'm gonna change that to the month of Jan and let's just take a look this is customer a selected now since we just had one invoice for customer a and the unvoiced amount was 90 but the amount received from that customer was 100 you can see that the first invoice is settled and we have a tick against it but since the customer overpaid you can see that this is the total of all the invoices which is 90 since we just had one invoice but the amount received is 100 and we still have a negative that means the customer overpaid for the month of January now let's just take a look at the month affair lets you see what happened in the month of web so if I go to the invoices sheet in the month of feb 10 was delivered an invoice of 30 dollars but if you take a look at the receipts data customer a did not pay in the month of feb 16 was cleared obviously the customer paid hundred bucks but the customer paid $10 extra last month and that $10 extra has been settled against the second voice generated in the month of feb 13 voice amount we settled 10 dollars as a balance from the last month and the balance against this invoice is still to be paid by the customer which is $20 simple as that now let's just take a look at the customer number be customer B all invoices are cleared you can see that total Billings of customer B is $200 the receipts in the month of feb 9 were $200 so balance is absolutely null zero now a further complexity could arise when the customer starts to pay lump sum like the way we've been seeing it and also against the invoice once in a while that means that if I just go to my receipts let's say for example the customer paid 90 bucks against invoice number one right so now we have the invoice number and sometimes the customer is paying against the invoice sometimes the customer is not paying against the in way these are lump sum payments but this payment is made against the invoice right now let's just take a look at what happens so I'm just going to go back to the model right click and I'll do a refresh to my pivot table and let's just see what is happening right here of course I have to select customer a here because we were talking about customer a now you can take a look since the customer paid against the invoice and we have a table on the right-hand side which is self populated so the two invoices are separate out these are all the payments and the invoices that were general and this is the table where you have a specific invoice and the amount was specific to that invoice chain raised so invoice number one was on 1st of January the amount was 90 we received 90 on the first of Jan in case the the customers paying up in installments let's say for example I come here and I just only pay up 70 bucks to against the invoice number one come here and then refresh this pivot table let's just take a look now obviously the amount was 90 but I have paid 70 so the invoice is still not clear there's a balance of 20 let's see if that shows up or not amount was 90 received was 70 on the first of Jan and there is still balance pending so the tick mark has gone this invoice is still not clear so everything which is paid against the specific invoice gets collected here all the general invoices and the lump sum payments they just get collected here now that is the dashboard all about now that you have an understanding of what the dashboard is gonna do why don't we actually take a look at the power bi dashboard that I've created it's a template you input the data and the dashboard is going to be ready with your data and analysis let's just go take a look all right so we are taking a look at the dashboard now and this dashboard is really simple has just got two screens the overview screen and the invoices detail screen let's just talk about the overview first so on the left hand side we have a company level analysis this talks about the entire company all the customers as a whole and you can see that on the top we have the month of May selected that means what we are seeing now is the status of all the do's and the receivables as on May 2018 so the total Billings that we have done until this month is a 760 the total receipts that we have received is 792 that means customers have overpaid then what we have built that's a good thing actually the customers are actually keeping up spare balances that they can consume later so we have a negative pending here is a small chart which talks about the balanced spending from top 7 customers that means that from this customer $24,000 is still pending by the end of May 2018 and if you hover the mouse on top of that it talks about the total billing and the balanced spending so you can see that we have built 24 for 20 and the customer has paid nothing of that let's just take a look at the second customer we have billed 22 915 the customer has paid five thousand eight hundred and thirty-five and seventeen thousand 80 is depending from the customer at the bottom we have a small table this table shows a couple of columns here we have a date the cumulative amount that means day on day how did the billing increase this is the running total the running total of the amount received and the balance so you can see that on the first we did a billing of 890 and we received nothing so 890 up till the second that means the running total until the 2nd of June was 7000 550 received was nothing until this time so the balance is complete and so this actually tells you that ok how the health of the company is doing and that means what has been the total billing until the current date what has been the amount received until the current date and what has been the balance pending and by the end of this table you will find that this is going to get negative because the customers have overpaid so you can see that this turns blue so on the right here we have the customer 11 analysis that means we can analyze a specific customer and the details of the how the customer has been billed and paid over a period of time a customer freaked Shack has been selected and you can see that until May 2018 we have billed a total of 39 845 we have received thirty four thousand three hundred and thirty five and five thousand five hundred and ten dollars is still pending from the customer you can take a look the chart here the red line talks about the Billings cumulative Billings blue line talks about the balance as on the current date all right let's just take a look at the table here and the table at the bottom talks about the date the amount that you billed to the customer on the particular date how much money have you received in terms of general receipts remember that we had two receipts we had a general receipts and the invoice receipts the customer can pay a lump sum or the customer can pay against the invoice as well so these are general receipts and the invoice receipts now after which we have three columns this is the cumulative amount the running total of the amount billed so you can take a look at the on the second of Jan we had 2,620 we added hundred to that now the amount has become two thousand seven hundred and twenty so this is nothing but the running total of the amount column the cumulative receipts is nothing but the running total of general and the invoice receipts so this is nothing but the total amount this is nothing but the total received and this is nothing but the balance of that customer as on a particular date and you can see that the same balance tallies up in the table as it tallies up in the card on the top we have 39 845 as a total built we have 34 335 as the total received the balance is nothing but five thousand five hundred and ten and if you take a look at the chart here and the chart gives you a quick summary of how the customer has been billed and how the customer has been paid what you actually should have is that the red line should go up on the top that means that you should build the customer higher but the blue line should go at the bottom because you also want to collect the amount of money from the customer you can see that here by the end of March sorry by the end of May the total balance is just five thousand five hundred and ten now if you change this dashboard to a particular customer any other customer let's say for example Ora navigations right here now you can see that since since the customer did not pay anything so the balance has been going up which is not a very good thing you might want to call this customer and ask for money and that's it and you can also change the month now if I just select December 2018 it's going to show the entire ear strings and other navigation actually started to pay up so the balance has come down drastically remember that this dashboard is an as on date dashboard that means when I select December 2018 all the visuals the charts are going to update and show me the data up till that particular day all right let's move on to screen number two which is invoice details now this screen or this report it's a further drill down of the overview sheet now here in this screen I can take a look at invoice by invoice which invoices have been paid for and which invoices are still due for payment now you can take a look we have selected a customer called sphere cards here and we are in the month of June 2008 in the month of June we raised all these invoices a total of six thousand six hundred and forty we did not receive any money from the customer so the everything is spending we also read one more invoice of two thousand six hundred and twenty five and the customer decided to pay specifically against that particular invoice all right this table also shows you one additional thing which is spending days and you can see that if the invoice was raised on 1st of January the pending days are 31 which is calculated as per the dates in the slicer not as of today now let's just take a look at them in the month of February and you can see all the invoices have been paid for why because the customer paid ten thousand six hundred and seventy dollars in lump sum against which all the invoices have been settled for including the invoices that have been raised in the month of feb 10 2015 season full let's just take a look at the month of February if I click on the month of sorry if I click on the month of March let's just take a look at what happened so more money you see from the customer and you can see the balance still went up by negative by 4105 there is some balance here so the set of balance is $200 which is shown up right here in the month of April we received some money against this invoice we received some money 2625 is saved on the 7th of April and the invoice balance is zero so this invoice has been cleared and you see a check mark here and let's just maybe move on to the month of September let's see ok fine now you can see since we have done more invoices and received less money and you can see that we have an invoice pending although the invoice amount was two thousand nine hundred and seventy we had some money from the customer already with us so we are just at seven hundred and sixty dollars and the balance amount is right here this the invoice is still not clear that's the reason why you still see pending days and you don't see a check mark here in the month of September we had raised invoice and the invoice amount was 1805 but the customer decided to pay in two installments which is 1662 and 143 dollars totaling up to 1805 and you can see both the instances of payments received here and also again once again here this invoice was 1040 we only have received 485 dollars against the invoice and the invoice is still pending for a balance of five hundred and fifty-five dollars and if we go to the month of November you can see that in November the customer pays of five hundred fifty-five dollars and the entire and invoice amount is complete and all the invoices are checked that's all good and once you kind of select any other customer from here you even typed the name of the customer you select any other customer the report will refresh to that particular customer all right now that we've taken a look at the template let's just go take a look at the data behind the template all right so here I am I have a very simple excel file which contains the data that is going in the template and if you take a look at the bottom we just have three sheets we have invoice data we had the receipts data and we have the customers data in the invoices data we have a table the table contains a couple of columns date column or the invoice number the customer the customer ID and the amount now there's nothing too complicated that I have to explain everybody understand what this columns means but it's just one thing that you need to remember is that the invoice number should be unique in this entire column so one row simply means one unique invoice and you enter the details of the noise the date the the moist number the customer the customer ID and the amount and this is good to go I'm also going to talk about that why do I have customer and the customer ID in just about a while let's just go take a look at the receipts data first alright Here I am on the receipt sheet and you can see that we again have a couple of columns nearly the same columns as we had in the invoices now in the receipts you can possibly have two different types means you can receive the money against a particular invoice or you could much receive the money in lump sum if you're receiving the money in against a particular invoice we're gonna write the invoice number right here and there are invoices at the bottom of this data where you have money received against a particular invoice right so there are a lot of them actually the other method is that you just received a lump sum in that case you're just going to leave this column absolutely blank no space nothing of that sort just absolutely blank you will write the date you will write the amount received and the customer name and the customer ID now here you are allowed to have duplicates meaning you can see that on the 5th of January we received two payments one from titanium and the other from from Tiger Shadow Tiger Shadow again we received two payments back-to-back one on 5th January and the other one on 6 January now the thing is the tiger Shadow could have paid us the amount on the same day as well that means both of these amounts could have been on the 5th of January also when you're writing the invoice number again you can have duplicates you can see that we have an invoice number here on the 15th of January Tiger Shadow pay two four six zero as an amount against this invoice number now this invoice could have been possibly paid into installment now the only thing that you have to make sure let me just go take a look at this invoice go find this invoice in the invoices sheet so I'm gonna come here now this invoice value was two six four zero and this entire amount was paid in one shot on the 15th of January now the only thing that I have to remember is that the date of when you receive the payment cannot be before the invoice date the second thing that I have to talk about is that you cannot receive more payment that the invoice amount so the invoice amount was how much it's two four six zero but you cannot receive more money than what you build the customer in the invoice value now I can definitely have two installments of this this two four six zero can come in two three four five as many installments as you wish and against every single installments you will have the annoys number there until the time all of this matches to be two four six zero the invoice is going to be shown as knocked off in that particular month and that's about it this is gonna work absolutely fine whereas when the customer Tiger shadow pays us lump sum amount of money there can be instances where he ends up paying more money than what his actual of all the total bills is so maybe the total of the wheels is five thousand but he ends up paying five and a half thousand that's totally okay it's gonna show up as a negative balance and let's just take a look at the customers data just two columns we have the customer column and we have the customer ID column customer column is the name of the customer and the customer ID is the unique ID of the customer now since the customer name could be duplicated that's the reason why we have a customer ID now these are the customer IDs that I have generated if you haven't generated customer IDs you will have to generate a unique customer ID for every customer write them down here and you are good to go and you will see that's the reason why every where we have a customer ID because we are allocate the money to the customer whose period just make sure that whatever you're writing here in terms of the details of the customer the same customer gets repeated right here different customers should not appear here also that applies to the invoice number so in the same invoice number if it is getting paid should reflect in the receipts data it should not have a space it should not have a lower case upper case it should exactly be this same invoice number same is the case with the customer and the customer I see the case and the text should again match I'll just mention that both in the invoices and the receipts if you want to add a couple of more details in terms of columns you can do that so you can add more columns here it's not going to be a problem the table will automatically expand if you want to add more number of rows at the bottom sure enough you can do that it's not going to be a problem so is the case with the invoice you want to add more columns here you can do that you want to add more columns in the receipts you can do that but what you should not do is you should not change the names of the existing columns so I have called this column as a date you should not call this anything else apart from date let the columns name be the same their columns that I have already used all right one last thing to remember is that all of these data sets have been converted into a table structure and you can take a look that we have the design tab on the top and here I have the name of the table you should also not change the name of the table let this table name be invoices because that's how power bi is pulling the data in by the invoices table and the second sheet we have receipts so if I just take a look here this is receipts and the last sheet which is customers so this data is nothing but customers and it's gonna stay customers so that's about it just be cautious when you enter the data if the data entered is correct the output is gonna be correct once you purchase this template what you will get is a secure login ID but from which you can download the dashboard itself the power bi file and you'll also be able to download the data set which is behind this so you'll be able to write your own data there there's also going to be a slight tutorial and a guide in order to how to set up the dashboard how do you connect the dashboard to the backend data and just in case if you need any customization or if you need me to build a dashboard for you from scratch please feel free to reach out to me I'll probably leave my email somewhere here on the on the video and you can just write to me if you have any questions any comments any suggestions please feel free to put them down in the comments below I'll be more than happy to help you out and thanks so much for sticking around thanks so much for watching this you take care of yourselves I'll see you in the next one bye bye