Optimisez L'efficacité Avec Notre Modèle Excel Gratuit De Suivi Des Factures Pour La Banque

Invoice tracker excel template free for Banking

hello and welcome to this video today I'm going to give you a tour of my new Google Sheets Bell tracker template this is the second version that I create it's the extended version and it is a 12 month tracker that you can start on any month that you want so you don't have to start in January you just select your first month from the drop down you set a year and then the other 11 will get updated automatically so this is going to be a quick tour and I'm going to show you a general overview of how the whole thing works how everything is connected what are the things that you are going to input the scenarios that this can handle so let's get into it as you can see down here you have several tabs but you're really only going to input your information into of them and the other three are summaries that are created automatically so you get the monthly transactions Tab and this is where you're going to input your bills that you pay every single month and that includes stuff that you pay more than once a month so if you have a student loan that you pay a every other week for example you add that in this tab and it's divided in two sections so you get your monthly bills that you pay the exact same amount every single month and you input those in the fixed section and then you get your variable section and this is where you're going to input everything you pay a similar amount each month but you don't pay exactly the same you have a budget and you have an estimate but you don't know the actual amount until you get the bill so I'm going to talk about the difference between how those two sections work in a moment and then you get your annual transactions Tab and this is where you're going to input everything else so if you have something that you pay for once a year every other month quarterly this is a place where you're going to add that and this section is really cool because what you're going to do is you're going to input your basic Bill information and then you're gonna select in which months you want to activate that bill so it's going to automatically load your default information and you're going to be able to tell where you have expected payments and then you have a calendar in which you're going to be able to select a month from the drop down and it's going to pull every single transaction from both sections and it's going to show you this list that you can choose to sort in ascending or descending order and then it's going to show you this calendar and you can choose to only view the things that are pending by unchecking this box or you can also choose to show the things that you already paid by checking this box and then you get some other summaries over here and then you get 12 month summaries so throughout the template you're going to be able to assign a category to each of your bills you're going to be able to assign a payment method and then all of your bills are going to be gathered together and grouped by the bill name that you assigned and they're going to be summarized so if you want something to be grouped in the end you just have to name the bill the exact same way and if you want to assign certain amounts to a fixed section A different amount to the variable section A different amount so the regular section if you name them the same way they will be grouped in this final summary so this entire thing is completely optional and it is read only you don't have to do anything here it's just all your information gathered together and shown in different ways and then you also get these all transactions Tab and this is pretty much just Gathering absolutely every single transaction from every single month from every single section both the monthly transactions the annual transactions and it's going to show you this list of absolutely everything so you can sort it by due date and then know what transactions are upcoming you're going to be able to tell in which section those are located you can also click on this link and you're going to jump into that section so if I click here I'm going to jump into the fixed section of April in the monthly transactions tab so that's what happened there so this is just for your own reference in case you want to look at the entire thing as just one single list and so this is for the pending transactions and this one is for the paid transactions so this is all that's included now I'm gonna input some new bills on the monthly transactions Tab and the annual transaction step just so you can get an idea of how the whole thing behaves so as I mentioned when you are in the monthly transactions tab you're going to see two sections you get the fixed section and you get the variable section and for both of those the first thing you have to do is you have to enter a bill so I'm gonna do my new fixed Bell and you're gonna enter a due date so let's do 31 and once I do that you're gonna see that every single month gets updated with the corresponding due date and since I entered 31 and for example April that only has 30 days so if you enter 31 it's going to choose the very last day of each month so it's not going to jump into the next month then if I do that in the variable section so let's do new variable build the same thing is going to happen you're going to get a due date every month and you can totally change that manually everything that I'm going to show you that is added by default you can change that manually and I talk about that in the longer video so if you're interested in that one make sure to check the link in the description and now the budget is where things start to go different so I'm gonna do ten dollars and as you can see immediately I got a budget for ten dollars and I also got an amount due for ten dollars because since this is a fixed Bill I'm gonna be paying the exact same thing everything a month so the budget is going to get assigned on both of these scams and that won't happen down here so let's do twenty dollars for the variable section it's only going to get assigned on the budget column because the amount due won't be exactly the same as the budget so once you actually get the bill that's where you're going to input your amount due so you know that you pay somewhere around twenty dollars a month but once you actually get the bill for January in this case you realize it was actually 24.50 so you add your amount due and it's still not going to be considered as paid you can add the amount due once you actually know what it is but it's only going to be marked as paid when you add a date paid in the cell so if I double click here and I select the date then it's going to turn green and it's going to get added up here so I'm going to delete that and just look at this number and look how it's going to go down once I delete the date paid so it went down because it's not considered a spade until I actually add a date paid so the fixed section behaves differently since this is a fixed bill you already know how much you're going to pay so all you have to do to mark this bill as paid and look at this number once I check this box this number is going to increase so check the box it turns green and this number increased so those are the main differences between both sections now you can also assign a default payment method and these are fully customizable and you customize them in the customize here tab so I can do new payment method and if I come in here and assign it then it's going to get assigned by default to every single one of my months and the same thing happens down here so if I select new payment method then it's going to get assigned every time I actually have an amount due so I assign an amount due and then the payment method is going to show automatically so gray cells are automated and they are showing default information with the help of formulas but you can also replace those formulas with manual information so for example in here if you do not want this due date and just for this month you want to set it manually you can delete it and you can select a different date if you don't like this budget you can't delete it and you can add a new one so as you can see whenever I add manual information the cell is going to turn white and I walk you through a more detailed example in the longer video in the detailed tutorial so if you already own the template and you find yourself in this scenario in which you want to replace the default information with manual information and then you want to learn how to bring the formula back be sure to check out that video now I know some bills won't be running through the entire 12 months so sometimes you cancel a bill or sometimes you start by paying for a bill somewhere in the middle of the year and I also build a feature to handle that scenario so I'm gonna delete my payments right here I'm gonna delete this payment I'm gonna delete this amount too so if you start paying for something let's say my new variable bill I actually am going to start paying for that in March so I can select a start date in March and then you're going to see this x right here and it's no longer expecting a payment if I delete that start date then it's going to be expecting a payment in January if I set it again as March 1st I'm not expecting anything in January I'm not expecting anything in February and then I am expecting payments all the way through the end of the 12 months to cancel it maybe I'm just gonna use it from March to November I'm going to cancel it in November so let's say I cancel it November 30 I won't be expecting a bill in December and as you can see December has this X and it does not have a value here so you can set a start date and an end date or you can just set one of them and the rest of the months will be expecting a payment as well and then as an additional feature you can also assign a category to each of your bills and this is useful for the summary part and if you scroll to the right you're gonna see totals and the payment summary so this is your total budget and then you get this payment summary so every time you pay a bill it's going to show up in this table so you can have an overview of the whole thing every single payment all together in one section and if it's not expecting a payment then you're gonna see the X here as well now let's add something to the annual transaction so you see how that works so let's say I pay this is an annual bill this is a new quarterly Bill six month bill I can assign a due date of the 23rd 15 and then this can be the first and then I'm gonna assign a default budget 10 20 and 30 and as you can see right now there's nothing in these rows on any month information will start appearing as soon as you check these boxes so as you can see as I'm checking the Box the bill is getting activated if I look at January I know that I am expecting payments for these bills if I do the same for February so right now I don't have those three in February but if I activate them then you're going to be expecting a value and everything that's overdue everything that is due today or before today is going to turn blue and then once you actually know the final amount to do you can just input that one and this one works pretty much the same way as the variable section in the monthly tab so you input your amount too and it's still not going to be considered as paid until you double click and select the date paid so as you can see that increased then this is going to increase and then it's going to increase again and it's going to turn green whenever I already bait it it's going to be Jello right here if you are expecting a payment and then you can also manually assign a payment method in here and if you scroll all the way to the right you're also going to find a payment summary so you can tell where payments are expected because it's going to turn yellow this is just view only it's for a visual reference you actually input your information in here but this is just so you can see all of your payments gathered all together in one table and gray cells again they are formulas they are automated they are going to be showing you your default information so if I change this to 300 you're going to see how that changes here and if you want to overwrite that you can do it you just delete the value and add a new value and as you can see it turned this purple color when I added it manually because I have this box checked so if you want to highlight the things that you changed manually where a formula used to be you can check this box and it's going to be very obvious where you made manual changes and you can undo them if you want and I talk about that in the long video so you get your monthly transactions that's where you enter everything that happens every single month either fixed or viable amounts you have your annual transactions tab for everything else so you have your calendar you have your 12 month summaries and I forgot to mention if you want to focus in a specific row you can highlight it using this drop down so if I wanted to just focus on the payment method for example you can highlight it so that's a little helper you can sort and you have your all transactions tab I also have a more detailed video that you can find Linked In the description I start off with a blank template from the beginning step one customize your sheet if you already have the template and you would like to follow that video to start filling your own then you can totally do that so that's it for this video I hope you liked it if you have any questions please feel free to contact me and I will be happy to help you thank you so much for watching