Effortlessly Manage Your Payment Template Excel for it

Payment template excel for IT

hi everyone thanks for clicking on the video this video is just going to go over the accounts payable tracker that's available at accountingexcel.com and i'll leave a link to the description for the template if you want to download it it's also just going to go over the kind of how to keep track of your accounts payable using microsoft excel so let's get started here with the template so it's got four different tabs it's got an ap aging tab it's got a monthly summary so you can see the amounts that you've invoiced each month in your year to date what you've invoiced as well as the amounts that have been paid and the year to date that's been paid just just a good tab to have for analysis then it's got an ap detail tab where we're going to put in all of your accounts payable invoices and also show when we've paid those invoices and then it's going to have a vendor master list that has a list of all of your vendors and this is going to be good because it's going to have all the contact information for each of your vendors the terms of payments for them whether or not you have a w-9 their eins what type of entity they are and whether or not you need to send them a 1099 and also what you've paid them year-to-date so that when it comes time for 1099's you'll have the amount that you need for to prepare your 10.99 so the way this file works it starts with the ap aging summary and everything is driven on the date on the ap aging summary so if we want to see what our uh balances are as of 9 30 2021 we're going to enter that date here and that's going to populate the remaining tabs of the correct dates so it's going to show our detail as of this date so to get started here what we need to do is we need to need to in the way that i need to show you is to enter a couple of vendors so that we can see how that's going to work okay so i entered a couple different vendors in just so we have some examples and what i'm going to do is come over here and show you a couple different drop downs that we have so the terms we can enter in terms if we'd like there's a couple different ones to choose from so we're going to go ahead and select net 30 for this first one and then whether or not we have their w9 and the w9 is something you need to collect from your vendors so we're going to go ahead and select yes and then we're going to put their ein in so we have their ein and then the type of entity and this is going to kind of drive whether or not we need to send them a 1099. there's different rules for who you need to send a 1099 to so we're going to go ahead and select uh c-corp for this and so since they're c-corp we don't need to send them a 1099. so we'll select no on that so i'm just going to go ahead and enter the rest of these in so that we have them all populated okay so now that we have it everything populated we can go ahead and enter in a couple of invoices so to enter an invoice all we need to do is select from the drop down our vendor so the first one is going to be azar and then we're just going to enter in a project name okay so appliance repair and then we're just going to enter in an invoice number invoice date and it's going to automatically populate the terms for this vendor and it's going to populate their due date so then we're going to put the invoice amount in okay and then you can see that it populates the invoice amount and it's going to show the amount outstanding in the days that it's outstanding and the status of that invoice as of whatever date we select so right now it's 31 to 61 days outstanding and then that's going to show up on our ap aging here's our appliances 31 to 60 days we have five thousand eight hundred forty four dollars outstanding as of 9 30. let's say we want to change the state to 12 31. it's going to put it in the correct bucket over 90 days outstanding so i'm just going to enter in a couple more ap invoices so we can see how it looks okay so i've entered in a couple more i'm just going to expand that so we can see so as you can see it calculates everything puts it in the correct bucket as of 12 31 2021 and then coming over to our ap aging puts it in the correct bucket we want to change our aging report to september it's going to show those amounts in the correct buckets as of september another thing it's going to do is it's going to show you on the monthly summary what we have been invoiced for the month and it's also going to show the year-to-date invoice for that year the next thing we need to do is apply a couple payments um just just to clear those out so say that we want to pay uh azar and we're going to pay them on october 15th so say 10 21 and the amount paid is 544. it's going to show that there's nothing outstanding and it's going to remove that from our ap aging and we can also select the payment type that we want to select so let's say that we want to pay them with a check we can select check ach wire cache so in this case i'm just going to put ach and that's going to remove azar from our ap aging so now there's nothing outstanding for azar then if i go back to the vendor master it's going to show what we paid year to date for azar so whenever it comes time to do our 1099s anything that has a yes next to it we're going to send this vendor at 1099 for the amount that we paid them for the year and that's a general overview of just how you can use microsoft excel to keep track of your accounts payable and like i said this template is available at the accounting excel store and i will leave a link in the description so that you can download the template and make sure you like the video subscribe the channel and we'll see you next time