Discover the Ultimate Excel Bill Tracker for Procurement

Excel bill tracker for Procurement

hello in this video I'll take you through our procurement template now this template is powered at Microsoft Excel so you need to have Excel pre-installed on your computer preferably a version from 2013 now when you open up the documents you arrive at the dashboard that's where you currently see on the screen and this holds three buttons to navigate to the three major sections of the template we have the supplier section where you add all of your supplier information the procurement section where you create new procurements and the vendor summary which is a report that shows you your purchase history now in this video I'm going to go to all the sections let's just start with the first section and to open it up you simply click so notice as I click on the button it takes me to the supplier section of the template so now we're in the supplier information section now all sections in this template have a similar formatting in the upper left hand corner we have the section title which tells the user at any point inside the section your own so notice it says supplier information so you know this is where you list all your supplier data pretty much serving as a supplier database so the right of this we have four buttons to add a new supplier to delete an existing supplier to edit the drop-down list in this section and to sort your suppliers in ascending order above this we have the navigation pane and this has several labels to navigate to a particular section of the template notice as I click on the dashboard label it opens up the dashboard section where we initially started recall I'd n clicked on supplier so each label represents a button to navigate to a particular section of the template and below the four buttons here we have our data table where all supplier information will be listed now at the top you notice we have column headers these are attributes you expect that to supply for each supplier so to add a new supplier click on the new supplier button at the top and this pops up an Excel form now Excel forms provide an easier way to store data to this table so you notice the column headers I referenced earlier are equally listed on the form as labels the user simply has to supply values to the white portion of the form the first attribute you expected to supply as a supplier is name so let's assume is our first supplier and he supplies us with Dell computers and accessories and once you're done entering the supplier name to move to the next field which is a product and service type you can either click on the white portion with your form or I mean with your mouse excuse me so you can either click with your mouse or you can make use of the tab key or your keyboard so the tab key move you to the next field for details entry now the product and service type this is a drop-down list and it has three default options so we have furniture computers and electrical equipments now little on I'll show you how you can add additional categories using the edit list section at the top here so you can either add additional categories remove existing categories or modify any of the three values here so I'm going to select the second option move to the next field and I'm going to enter the points of contacts the office address phone number email address and any other details pertaining to the supplier so I can enter things like maybe two weeks delivery so let's say they take two weeks for anything I order and they require upfront payments I can just answer any additional details pertaining to this supplier now once I'm done supplying all the values I simply click on the answer button and it stores that supplier to my database so now you notice that the first row is listed with the data we just applied and if I scroll to the right so we can see the additional columns so we see other information and then the outstanding column now the outstanding balance column as a formula generated field to excel will automatically calculate the total outstanding if any that's you owe this supplier so at any point in time at a glance you can know the total outstanding using information from the procurement section so that's a summary of how you add suppliers I'm just going to demonstrate that again you click on new suppliers it pops up a form you enter the suppliers name you move to the next field you enter the product type here you can start typing so if I type F it's a lottle fill with the first category with F the point of contact the address now the address point of contact email other details are all optional fields the only two mandatory fields or the supplier name and a phone number so notice if I don't supply any value and I click enter it's going to pop up a Red Alert so you need to supply a suppliers name and the phone number I'm just gonna go ahead and add the phone number and I'm going to leave all the other fields blank so once I supply it I click on answer and it stores that supplier to my database so now we have two suppliers so if peradventure you make a mistake so notice I answer this number wrongly you can double click on the cell and modify it so the cells are editable so that way you don't have to delete the entire entry you can double click modify or update sets and then click away so now successfully added two suppliers the first row represents the first suppliers information Mike random and the second role represents Anderson's furnitures information so that's how you add suppliers to your database the next functionality is to delete the supplier so to delete any supplier you simply need to click on any cell in that row so recall I said each row represents a supplier so the first row the first supplier second row second supplier and each row is made up of multiple cells so we have the suppliers name style the category type cell the points of contact phone number and so on and as I click you notice as a border around the cell that I'm on so to delete any supply simply if I click on any cell in that row click on delete the template is going to ask are you sure you want to delete the supplier it's equally going to tell you the suppliers name if you do you click yes if you don't you click no so that's how you add new suppliers and delete existing suppliers thirdly this is the section you use whenever you want to modify the contents of the drop-down list here so once you click it opens up a section and these are the three default values so notice at the bottom I can add a new category for instance office supplies so now we have four categories so if I go back to all suppliers click the new supplier you should see the new option at the bottom office supplies so that's how you add additional categories and modify existing categories you simply make use of the third button which takes you to the Edit list section and the last button here enables you sorts this data in ascending order now later on when you want to select suppliers in the procurement section is going to be listed as it is here so you notice we have Mike first we have because this is the manner in which we enter the information but it makes it easier if all the suppliers on ascending order so that way a comes then B C so you know the chronological order now to do that all you need to do is to click on this button once you do the temple also automatically sorted in ascending order so the able now comes first then the M so even if you have a hundred contacts if you press this it automatically sort it all in ascending order so that would just make it easier when you have multiple contacts excuse me to scroll across so that covers the first section so adding deleting editing and sorts and supplies so we move on to the next section of the template which is a procurement section where you create your procurements to open it up and click click and it takes you to the procurement section of the template so you notice it has a similar formatting at the top we have the section title you notice is where you document procurement information we have four buttons to the right to add a new credit purchase to pay a vendor to create a purchase order and to delete an existing procurement above this we have the navigation pane and below this we have the data table where all procurement information will be listed now to add a new procurement you simply click on the credit purchase button because all procurements are done on credit so once you click it pops up the form now this form works exactly like the form we just saw the only difference is that it has more fields so first of all you indicate the dates that you're making the procurements and the date should be entered in this formats the dates first the months and then the year you can either modify any of the contents I'm going to back date so let's assume the first procurement is on the 1st of May so I've changed this to old one and the kid in the first off I've indicated May 2018 and the kid in the year so move to the next field I simply press the tab key and this will list all suppliers I have in my database so now you notice the advantage of having it in ascending order that's alphabetically so that the a comes before the M so if you have multiple contacts you know it's going to be in ascending order so here you simply click on the supplier for instance Anderson furniture you click on the tab key you enter the order number so depending on how your company does order numbers maybe you proceeded with the year then the months really this is subject to how you do your order numbers some are and figures you know how every day just simply enter the order number next to indicate the grand next you indicate the materials the quantity the unit price and the total price the template will automatically calculate the grand total at the top so let's assume the first material is a wooden you know medium in sable let's assume you are buying two units and each goes for 25,000 so you notice as I entered 2 and 25 to Temple will automatically calculate the total price of 50 and the grand total at the top the same way that says you were buying office chairs quickly buying zoo and each goes for 15,000 once I tab away you notice automatically calculates 30 as a total multiplying the two together and the grand total on the top is 80 which is 50 plus 30 so the form supports entering up to 10 items at a go so we notice we have 10 fields have entered to and we'll just enter one more and then the other fields are there so let's assume another thing wants to buy from this particular so one small office tables the seminar or buying for did you go for a 10,000 giving us a total of 14,000 okay so here we have the individual line total excuse me and a grand total at the top and once you're done supplying all the values you simply click on the enter key and example to automatically store this procurement order so it tells us has been added you should now see three rows one row for each item so they're all the same purchase dates the same order number the same supplier but different product names so these are the three products their respective prices total prices and you're all credit purchases so that's how you add credits purchases if I scroll to the right you notice we have one additional column mode of payments but this is used with the payment button okay so this is how easy you ad procurements and now if we go back to our supplier section we should see that we're owning this supplier the sum of the three which is 120 thousand because we haven't made any payments but we've added a procurement so if I go back to my supplier section we should see an outstanding if I scroll to the right of 120,000 so that's how all the sections are connected so if I scroll so you can see that properly you see 120 thousand so going back to the procurement section so that's pretty much how you add procurements you create common credit purchase you the dates I'm going to make this the fifth here eunuch it the supplier the order number depending on how the format of your order numbers and then enter the material name so the first material can be a laptop let's assume you're buying sent from one for each employee and they go for three fifty each so template automatically calculate the total and the grants order next let's assume that you're buying extensions and you're clear buying ten so each office is going to have one you're buying each for five thousand and then it tells you the total as I said you can add up to ten procurements once you're done you click on enter to automatically store that and we should see two additional roles one for the laptop and the other for the extension cables and so now if we go to the supplier section we should be going the second supplier three point five five million so if I go to my suppliers you should see the outstanding balance there that's at three million five hundred and fifty thousand now okay so it's automatically updates the outstanding balance column so that's how you add procurements then when you're ready to pay you make use of the add payments button so next we add payment such as you'd like click on the add payments button and this pops up or form so at the top you enter the payment States I'm going to change this to the second so let's assume we're pain Anderson the next day after we place the purchase order next indicates our suppliers name so this is equally a drop-down list so you can start typing for instance a it's will also fill with a sub first supplier that starts with a now once you move away from this field temple you automatically list the total amount your own debt supplier so that way you don't have to quickly go here to glance it will automatically calculate that for you in the same way if I select Mike and I tab notice the balance updates so that at any point in time you can easily know the total amount you are in a particular supplier when you're about to make payments so let's assume we're going to pay one hundred thousand from this so when we receive the delivery we pay the balance of twenty so here we indicate the mode of payments which can be cash once we're done we click on enter and I tell those opinions has been added so we should see another row indicating their dates the supplier we paid the amount we paid telling us of the vendor payments and we paid in cash so now we should be owing the supplier only twenty because a total of all we purchased is 120 we paid a hundred so go into the supplier section we should see a balance of just 20,000 so that's how all sections work together okay so you can add procurements and then you can add payments and the same way I'm going to add opinions for the second that's Mike Dell computers this is going to be today's dates once I type em it will also fill one site a bit to tell me the total amount on point let's assume I can pay only 550 from this there we go five hundred fifty thousand and now I'm paying true bank transfer if I can type there we go so once I click on enter it's going to store that payments so now we have another row which lists that transaction so now we should be owing Mike a total of three million naira so going to the supplier section notice it's now three million okay so that's how you add or you create new procurements now the next is to create a purchase order so after listing all these values here you might want to create a purchase order for this transaction so to do that all you need to do is to click the purchase order button so the first step as I said to create the purchase order is to select the order number in which you want to put creates the purchase order for so if I wants for this under sense transaction on the first of May I simply click on this field now the first three items have the same order number so I can either click on the first second third it doesn't matter I'm just going to use the first then once I click on the purchase order button it will automatically create a purchase order for that order number so you notice this is the sample we have your logo at the top we have your company address the type of form which is an order form the invoice dates and here is the order number that these items belong to at the top we only have the suppliers name so the template automatically extracts the phone email and residential address if you supply this to the supplier section but recall we only supply the phone number that's why the other fields are blank so quickly going back to the supplier section you notice that for Anderson the email and address fields are blank I'm quickly going to add one just for demonstration purposes and also an office address okay so now I've added an email address and office address so if I regenerates that purchase order so I we select the cell click on purchase order we should now see the email address and residential address listed okay so the symbol automatically extracts the contact data from the supplier section using the supplier you specified on the right hand side we have the other fields as specified in the sample you gave the order number and the three line items at the bottom we have the subtotal the invoice total and the field for the Operations Manager to sign and the managing director so they approve all section okay so that's a summary of how the order or purchase order functionality works the symbol was automatically generated using the order number sample you selected so I'm going to do the same for Mike Dell computers all I need to do selects the new order number so notice the field is selected click on purchase order and the template also matically replace the values so now you notice it's Mike Dell the office and abuja so it does replace the contact data as equally changed it invoice dates to the fifth the order number the two items being ordered the subtotal which is four points one the invoice total and the signal trees at the bottom okay so you notice how fast it automatically creates that now the purchase order has been set in such a way that it prints on an a4 page so the margins have been set on the left and right so it doesn't caught up so you notice it fits on a page and at the bottom we have page 1 of 1 ok so that's a sample of the purchase order this can be printed it can be saved as a PDF and email to the supplier ok so that's the summary of the purchase order functionality and last two deletes any purchase you simply click on you need to be on that particular row so you need to be on a cell and that row click on delete it swings to ask are you sure you want to delete this if you do yes it's going to delete no it's not ok so that's a summary over put Kim in Section then quickly go over it again the credit purchase this is how you create a procurement you enter the date you select the supplier enter the order number and list all the items it simple to automatically calculate the grand total based on the total prices of each line item next to add payment you simply click on the button you indicate the payment dates the moment you select the supplier the sample to automatically return the balance outstanding you indicate the amount pain and the mode of payments this is to create a purchase order he simply needs to click on the order number click on purchase order and to automatically take you to the purchase order section and populate it with the items being ordered and lastly to delete you click on a cell you click on the lease and the confirmation message comes up so that's a summary of the procurement section next we move to the creditors report now it's open it up you simply click on the label now the creditors reports excuse me just list all suppliers that's you owe money so if you have 50 supplier in your supplier section it's going to list the number of suppliers out of the 50 they are currently owing money so right now we owe only to suppliers money and we have only two suppliers so if I click on this button we should see only those two suppliers listed so it takes a while to load because it's refreshing a report or once the process is complete there we go at the top it tells you the total number of creditors which is 2 and it's going to list them with the outstanding amounts now this becomes useful when you have multiple suppliers because you're not always going to be on all of your suppliers so if you quickly want to get a summary of all the suppliers you owe and the outstanding total as I've said day so this is automatically generated point in time that's a day it's to tell you at the top the total number which is 2 and at the bottom it's going to list the suppliers name with the respective amount you owe each so you know this is a total you owe all of your suppliers so that's what the creditors actually does it just generates the reports less than all your creditors the next report is the cat and fold reports and as the name says this enables you quickly get information for a particular supplier so later on when you start using the templates let me go to the procurement section you have multiple procurements across multiple suppliers so right now we have only two procurement orders for Anderson and for Mike but later on you can have up to 2030 or they just multiple procurements across multiple suppliers so you might quickly want to see all transactions you've made with a particular supplier so to do that you simply make use of the get info section now let me point out that the remaining sections I'm going to cover they're all reports so the creditor section this was a report once you click it will automatically refresh listen all creditors in the same way to get info section as a report so once you click it will automatically refresh X in the most recent data here we go here is a report so the report has two sections at the top it's going to list information that's personal information with regards to the supplier things like the contact person the email the phone and so on and at the bottom is going to list transaction details so the actual items you purchased or the payments you've made to that vendor and the first step is you need to select the supplier that you want to generate this report for so once you click on this drop down list here in front of supplier name it's going to list all the suppliers in your database so if you have a hundred suppliers in the supplier section it's going to list the 100 suppliers here but the advantage of this report is that you can start typing the suppliers name so if you can remember that there's the word Dell in the suppliers name because they supply Dell once you type Dell to filter all the 50 and show only suppliers that have this four letters de ll together in your name in the same way for furniture once I start typing furniture it will filter all the contacts and list only suppliers that have the word furniture in their name okay so that makes it easier when you have to select true multiple suppliers now when you select the supplier and click OK it will automatically at the top list the contact information so we know this person is into furnitures this is the contact name the email the phone and the outstanding balance so this can be useful if you could blue want to place a phone call you can quickly come in here select the supplier and it's going to enlist that information for you and at the bottom it's going to list all transactions so the dates if it's a credit or payments the order number the item you purchase unit price and so on so it does give you a brief summary which records to the transaction now equally have the ability to filter by month or year so right now all our transaction is amazed with this I might not be able to fully demonstrate this functionality but later on when you have transactions across multiple years here you can be able to select this specific year you want to see and the specific months if I want to see only transactions in May 2018 I simply select May 2018 and it's going to filter that I can do the same for April June and so on so this way you can filter by a supplier by year and by months ok so you quickly get a supplier summary now the next section is a report section and this just enables you view your procurements but on a monthly basis so it's possible you might just want to see all procurements you've made in a particular month and year so you can simply click on report it opens it up and in the same way it has a refresh first and they take a few seconds but once it opens up it works exactly the same way at the top you simply indicate the year you want to see the report for next year to get the months you want to see and a template will automatically give you a summary so it's going to tell you these are all the transactions that happened within this time frame in the same if you want to see only credit so you don't wanna see the payments it's maybe simply click on credit purchase so you know this is the total credit purchase 3.6 million a bit over 3.6 million so this is all that has all the transactions in May 2018 if you want to see all payments you've made you simply click on payments you know you've paid a total of six hundred and fifty thousand and as I said you can filter this across months and across years okay so I think we covered all the sections of the template so if you go to our dashboard we cover the supplier the procurements and the vendor summary so the vendor summary just enable to select a vendor and you quickly see their contact data and the sales summary so that's a summary of the procurement Samford