Create Your Perfect Billing Template Excel for Planning with airSlate SignNow

Billing template excel for Planning

hello and welcome in this video I'll be showing us how to prepare a monthly payment tracker using Microsoft Excel like I usually say if you are new to this channel just go to the go to the go to the end of this video you are going to see an icon called subscribe just click on it and subscribe and turn on the notification Bell so that you can get more updates regarding Microsoft Excel and also to you know encourage me so in this video I'm going to show us how we can prepare a monthly payment tracker or you can call it monthly fee collection using Microsoft Excel let us get started all right now in this blank new Excel workbook the first thing you are you're going to write is the name of the organization which is a b c Nigeria ABC Nigeria ABC Nigeria limited ABC Nigeria Limited so then you write ABC Nigeria limited monthly fee collection ABC Nigeria Limited Edition Limited monthly fee collection so the next thing we are going to write is let me just copy here for the year ended 2022 on the year ended 2022 so that's what we are just going to write let me just copy the value so for the year end year 2022 so we have typed it the next thing you are going to type is the next does all so what you're going to do you're going to leave a space here and I'm going to write serial number let me just copy the headings let me just copy this adding here let me just copy the heading here Ctrl C let me just come here see Ctrl alt V so value let me just copy the values so so this is these are the details that you're going to have all right so you're gonna have you're going to have serial number reference number so let me just boot everything so what you do is you come here then you click on boot then you boot everything then you can increase the font size to 12. let me also increase this so that we can't so that we can see all right good one now we are going to increase year to I'm going to come here increase here you can utilize this you can increase it so that guy is 16 or let me leave it this way and then here we can also increase here to 22 so leave it that way now the next thing you're going to do is to you know adjust the cell cell wideness so we are going to you know adjust this and for this one we are going to wrap the text can you see reference number I'm gonna wrap the text and also gonna yeah we're going to wrap the text so we are going to click on this I'm going to wrap all the text I'm going to wrap on the text let us adjust the row Heights so let's click and drag so now we are done now the next thing we are going to do is to centralize everything so you click on the row and come to this place Center can you see Center your text so level Center it so we are done with that so now let's assume that we want to type all these things remember I have taught us uh whenever I told us how to use you know autofill so we'll come here the first thing you need to type is on January which you have type you don't need to type February just left click just come here and go to this place left click and drag it and drag it can you see it says December then you leave it you can easily to fill the rest for you are we together so that's how you do it your serial number the same thing let's see the serial number that you're targeting in this case we have from 1 to 19. so we come here and say one enter two enter then we highlight these two place to Highlights it and then we drag and then we we drag it we drag it you drag it down can you see 18 19 then we leave it easy to fill it for us let me centralize that so now the reference number we have let's say we want to generate any random reference number two three four zero zero one I just generated any random number so two three four zero zero and you don't want to feel for the rest you can also use Flash view just click on here when you click on just click on the cell that you have written two three four zero zero or any number you want to use then come to the edge of that say to change to plus then you drag it down then it will give you this one then you come to this place that you're seeing click on it and say few series but I see to feel it for us so let us centralize the reference also now we are done with that the name of the customers now or the name of it can be name of the student it can be name of individuals and what have you so you just type any name so for both of this illustration I'm just going to come here copy the names that I've prepared here Ctrl C and come here or it's V and value so so I've pasted it here so you can but you can type when you are practicing you can type all these things once again please if you are new to this Channel please consider subscribing just click on this subscribe button it will go a long way to appreciate me thank you now the amount to do um let's say the amounts that we want to collect you know this is a monthly fee collection or a payment tracker so Ctrl C is fifteen thousand your own can be thirty thousand it's just an example you understand so and we see value okay now fifteen thousand now we if you write if you can type in fifteen thousand naira manually but because of I'm just going on so when you type it discover that it is not showing our Command so we'll come here and put command here can we see right now so also you can click on here and insert I want to insert something here so the row height is too big so you left click and push it up a bit yeah so I want to put a neural sign here and I want to put n here representing amount so I can write on that any enter you so let me know just which time let me just drag drag everything to show that all these plays therefore amounts are we getting it so let me also increase this place yeah are we together so it's looking nice now right so we're just typing the amount so all those ones they are nothing now the most important thing I want to show us now is let us adjust this place ABC Nigeria limited highlight it across your data set highlights across your data set and stop in this outstanding balance and click on margin center it's going to centralize it for you the same way I like this one across your data sets and click on margin center it's going to margin Center for you so are we together now the amount that you pay we don't yet know so but before then let us come here and write total c-o-t-a capital letter c o t a l total I reach all together so we click on here and then we bought it all right so let us so let me make this 12. let me make everything 12. 12. yeah all right are we together so let me just zoom out a bit a beat so so that we can we can see it so ABC Nigeria limited monthly fee collection that is we want to collect their monthly deal all right and this is the amount due this amount you missed the amount that they are owing this is the amount that they are expected to pay but they will now split it and pay monthly depending on their capacity so we can decide and see okay our policies that they should pay 1250. if they pay 1250 per month it will result to the total will give you 15 000 now but you know that some people they may know pay that 100 250 is a month so depending on your financial capacity so you want to track their payments now what is the total amount pay total amount speed is all the amounts that the person has paid from January from January to February are we getting it total amount speed is all the amount of personal speed from January to December rather are we together so we are going to type a formula here and say equals to s-u-m open a brackets click on this please and drag left click and drag and drag to December and close your bracket press enter it's going to give you zero why because there's nothing here let us stay strong whether it is correct so I'll just copy some figures here let me just copy these figures that are here you can type these figures because of time so let me just copy of this figure here see I'm just copying the figure so you can type these figures so and say okay so these are the figures here can you see here is 15 000 meaning that can you see if you sum 1250 to December it is 15 15 000 error can you see in this place this person paid one five in January we'll pay six thousand error but did not pay anything for March April and May and June you know it depends on their financial capacity some people we who decide to shift the payment to the next move whichever way your own needs to track everything monthly and the total year to give you the total so once again if I want to Total it again I will say equals to s u m open your brackets click on this place left click and drag and drag I'm bringing it down I don't want it to go and stop at December can you see from January to December you know press enter and I also taught us how to use Auto fix if I want to use autofill I'll just highlight this place if I don't want to use some formula I'll highlight the data sets and I like where I even want the answer to be and come to Auto song to sum it for me so if I click here and I check the formula can you see enter so you can use autism whichever way now instead of me to continue to type everything as our usual way once you do one for Excel you can do the rest so just click on the cell go to the edge of the box and you'll be that to fill in the rest for you are we together so let me let me delete this I don't need this this one is empty are we together so I don't need this now now that we are done let me just highlight this place and um I don't like this and by the edge so I just say ignore error so it doesn't mean so are we together can we see now it's looking nice smoothly collection now the next thing we are going to do is outstanding balance now what's the formula for outstanding balance how do you know outstanding balance our standing balance is the amount due C2 the amount due minus the total amount speed let me write it for us here out standing balance is equals to amount due minus amount feed amounts view minus total amount speed so let me just put it so can we see the outstanding balance is what amount due minus total amounts speed can we see outstanding balance is amount due minus total amount speed so let us interpret this this is the amount due this is the total amounting this is the amount due and this is the total amount speed so that is amount due the money that the person is owing and total amount speed the money that person actually paid so to know your outstanding balance is the money that that the person is owing minus the money that the person actually paid do we understand so it's just normal logic so let's say somebody is owing you 500 this is the amount that's the amount they say somebody's loan you 500 and the person paid 200 naira so their standing balance would be worth 300 should be understand so amongst you is the amount that they are owing an amount paid is the amount that they actually pay so the formula is amount due minus amount speed just like we see it here amount due minus total amount paid so you're going to come here and say equals to foreign it means zero because I have formatted it in a comma can you see because I put comma here supposed to show zero she understand so so this Dash here it means it representing zero if you do your own can be zero but because I formatted it so 15 000 error minus 15 000 gives us zero do we get it now so that is that is that or that now the next thing let me show us again the formula equals to yeah minus look for dash on your keyboard and tap on this total amount speed for the same rule that is Rule seven please don't make mistake rule seven then you press enter do you understand so instead of what's doing for the rest let's just start here and look at that to get for the remaining ones so we don't need this one are we together so with us we have calculated the outstanding balance now so the next thing we are going to do is to put the Border Lines so let us just put the borderline so we are done I like your data sets and click here and go to all border click on all border so we are done with that so the next thing we are going to do is to Total January February March April May how much did we receive you're also going to use the sum function which is equals to sum open a bracket click here and drag it down drag it down to online you you rejoice then press enter can we see it now so let me put everything down here everything just click on this place and see boot can we see it now so let me also give it a comma so can we see I've given it a comma so I've given the comma let me also give it a narrower sign I'm coming I'll give that so the same way if I want to sum this place equals to sum open a bracket click here left click and drag and drag it along you to Rejoice again press enter and if I want to use that shortcut I thought I switched Auto so I like everything including the place that you want to put your answer come to AutoZone and select song can you see it will give you the answer now I cannot be doing that because of time so normally next day when you do for one you have done for all so just and just drag it left click and drag it so we have calculated and I told you whenever you see this thing in Excel it means that the spaces the space is not enough so you just double that you feel it for you so can we see now I hope you can see it right now so this is how to calculate a monthly fee collection or a monthly payment schedule this is these are monthly payments now look at it all these figures here is the amount the personal speed and to the organization ABC Nigeria limited they are collecting it so some some who can call it monthly fee collection some people can decide to call it monthly payments schedule and don't forget the formula for outstanding balance amount to you minus total amount paid let us do some basic formatting so we highlight this place come here and you can make it you can give me this color and give it this color and also you can highlight this place I buy this place I like this place and also give you the same color so you can see it's looking fine now so the same thing you can highlight this place I like this place I like it come to go to your formatting you have General number currency you can come to currency to give you the last time but don't come to currency come to accounting so come to accounting I want to change it to Nigerian naira so click here because I'm from Nigeria living here from USA you see it look at other countries England United States that's dollars euros and you see pounds and you know the right the likes so you come here so once you click on accounting you click on this icon here and then you you begin to search down you need to search downside down side down side down side down side you know I want to see me right now now you see we see hausa Fulani do anyone so far it's nearer anyone you can choose anyone let me just choose hausa this time around okay can you see I told you if you see this thing don't worry so just double tap on it just double tap on it if you go we show for you don't worry don't worry so just double tap here double tap here the w and build it up here so so we can see it right now so this is how to prepare a monthly payment schedule and you can also come here highlight this from January to December I like it and also Marge and Center it and you can call it monthly monthly you can call it monthly payments received so we can't we can also boot it let's put it let's make it 12. so monthly payments received so so that's just it so this reference number makes the number unique so these are names of my friend is so this is how to calculate a monthly payments schedule or a monthly fee collection using Microsoft Excel once again if you are new to this channel kindly please like this video And subscribe just click on this subscribe button that is here click on the Subscribe button below this video and if you need this template I'm going to put a link in the description box of this video I'm going to put a link in the description box of this video so that you can go download this file and probably use it for your business or for your you know monthly contribution you know some of what's your day monthly contribution I want to track the amounts that we are paying so you can also track your the amount they are paying so you can I'll keep this I'll I'll give you this link in the description box below so go check the the link in the description box below to download this file and also if you are new to this Channel please subscribe to this channel so that I can it will encourage me to release more video content like this and also like this video and comment if you find Value in this video I remain on my Google Emmanuel a data analyst an accountant and a Microsoft Excel expert see you in my next video thank you