Create Your Free Invoice Template Excel for Inventory Effortlessly

Free invoice template excel for Inventory

in this video we're going to create an inventory management system inside microsoft excel it's going to contain four tables one for the opening stock then a stock in table a stock out table and finally a closing stock table in the description of this video you will find two files the first of which is this working template and the other is a demo file that you can use to follow along with this video tutorial i'm now in the demo file and the first thing to do is to create official excel tables for opening stock stock in stock out and closing stock official excel tables will create a dynamic range which means as over time we add new products all our drop down lists and our formulas will automatically pick up those new items so i'll start with the opening stock table i'm going to select the column headings and then go to the insert tab my ribbon and go to the table button it'll say where is the date of your table and then just confirm the range that you've selected and we need to say that our table has headers in fact the tables currently only have headers click on ok then you want to give the table a name so you'll notice that there's a new table design tab on your ribbon when you're in a table and on the left side there is a table name box we're going to call this table opening underscore stock press enter to confirm i'm putting in an underscore because you can't have a space in a table name i'm going to change the default formatting that the table gives us and we can do that in the table styles gallery if i go to the more button there i'm actually going to select this second table style now i've got to do the same thing for stock in so i select the headings and i go to insert table or i can use this shortcut key ctrl t let's use that select the column headings ctrl t tick this little box that says my table as headers click on ok give the table a name we'll call this one stock underscore in press enter to confirm and then we'll change the look of the table we'll do exactly the same stock out and closing stock scroll to the left so i can see my opening stock table i'm going to freeze panes to enable me to see the opening stock table and the closing stock table on the screen at the same time to do that i'm going to click into g1 i'm going to go to the view tab on my ribbon go to freeze panes and then freeze panes and now if i scroll to the right i can see my opening stock and closing stop tables on the screen at the same time okay so i've prepared the tables now i need some data in my opening stock table now i'm going to copy the data from the inventory management file and remember that's also available in the description of this video what i want to do is copy the product number to opening stock columns all the way down to the bottom here and then paste those values into our opening stock table in the demo workbook so ctrl v to paste in i didn't copy the value column because i want to show you the formula that we're going to use to calculate the value and that'll essentially be unit price times opening stock equals unit price now notice that the formula doesn't reference that cell with its cell address d3 it references that cell with the name of the column unit price and it does so because we're in an excel table multiplied by opening stock and again it references that cell with the column name rather than a cell address if i press enter it calculates the value and it copies the formula down the rest of the column the next step is to copy over the stock information that's currently in the opening stock table over into the closing stock table and i want to copy with a link so that if i make any changes over here it will automatically update the closing stock table i'll scroll over to the right so i can see the closing stock table and i'm going to copy these four columns product number to opening stock control c to copy i'm going to go over to the first cell in the closing stock table go to paste and paste link so what this means is for example if i change the unit price of the chef's knife to 42 pounds it's going to automatically update it over here in the closing stock table i'm going to format those unit prices as currency okay next step let's deal with the stock in table now in the date field we're just going to put a date for when we receive stock so i'll put today's date control semicolon for today's date and the product number field needs to have a drop down list of product numbers that appear in our opening stock table now we can do this with data validation i go to the data tab on my ribbon in the data tools group i go to data validation i allow a list then i click in the source box and i select all the product numbers in my opening stop table and then i click on ok and now you'll see i have all my product numbers let's for the moment select product 2 and when i select product 2 i want that product description and unit price to automatically appear in columns j and k and to do that we can use a vlookup formula or if you're on excel 365 you could use x lookup i'm going to use vlookup just to include as many people as possible equals v lookup the first argument is lookup value so what am i looking up in my opening stock table i'm looking up the product number comma table array where am i looking up my product number well that's in the opening stock table so if i select that table you can see it says opening stock in my formula comma call index number which column do i want to borrow values from within the opening stock table well i want to borrow values from the product description column and it's after its index number so its position within the opening stop table and it's in the second column so the call index number is two comma last argument asks whether you're doing an exact or approximate match we want to do an exact match so i double click there and it will say false in that last argument close the bracket press enter you can see it returns the product description for product 2. if i change this to product three you can see product three over here is j oliver cutting board fish and that's what i have here now i also need to do this for the unit price and the formula's going to be almost exactly the same i'm going to copy that formula go over here and paste it in and change the col index number to three because unit price is the third column within my opening stock table i need to format that value as currency now i put a stock in value let's say we get 50 items the value of those items would be the unit price times the stock out of interest let's see what happens if i add an additional product i'm going to copy this product number down that automatically gives me product 21 and we're putting the basics cheese board they sell at 25 pounds apiece opening stock 10. automatically gives me the value so it copies the formula down within a table now we have this new product in our opening stock table let's say we get some more stock for the cheese boards if i go to this drop-down list you'll see that it automatically includes product 21. now it does that because our opening stock is in an official excel table if it wasn't in an official excel table this list wouldn't have updated with the new product so now i'm going to say get 60 more cheese boards you can see it automatically works out the value of those cheese boards for me let's move over to the stock out table again i'm going to put in today's date product number we need a drop down list of these product numbers just as we did within the stock in table we go to data data tools group data validation allow list source is our product numbers in the opening stock table click on ok i'll choose a product product six and here again we use our vlookup looking up the product i've chosen in this row within our opening stock table call index number is 2 for product description and we're doing an exact match then i can copy this formula paste it into unit price and just change the call index number to three format that as currency stock out value we'll say is 20 and the value would be unit price times the stock now with this value field although it's already got the currency format and let's pick that up from the calculation if you want the currency format to be copied down to subsequent records it's always worth reapplying that format just to be sure that when we create a new record the value is always formatted as we want it to be so if i add a new stock out record let's go for our cheese boards we sold 10 cheese boards you can see it automatically gives me my value formatted in the correct way final step is to complete the closing stock table first of all we're going to deal with stock in we want to sum up the stocking values for each of our products starting with product one and to do this we can use a function called sum if equals sum if the first argument in the sum if function is range now range is the column that you're applying a criteria to our criteria is going to be this product number so the range is the product number column in the stock in table this column here comma and we're up in the formula bar now our criteria is going to be the product number in our closing stock table comma and our sum range is going to be the stock column in the stock in table close the bracket there press enter you can see that it's done a calculation for product three where we have a stop in value of 50 and product 21 where we have a stock in value of 60. if i go over to my stock in table you can see that that tallies with the values that we can see here i'm now going to add another stock in record also for product 3 i'm going to say that we bought in 30 items of the fish cutting board you can see now that the stock level has gone up by 80 in total and if i scroll over to my closing stock table you can see that it's calculated that amount for me now we can do a similar thing for the stock out column equals sum if my range is the product column in the stock out table comma my criteria is the product number within the closing stock table comma and my sum range is the stock column within the stock out table close the bracket press enter you can see i get two values here the stock out value of 20 for product 6 and 10 for product 21 and that tallies with what we have in the stock out table i'm going to add a further record for product 21 with a stock value of 60. so we should now have a total stock out value of 70 for product 21. you can see that that is indeed the case closing stock that calculation would be the opening stock plus the stock in minus the stock out the value will be the closing stock multiplied by the unit price and we can see the math is working here for example for our product 21 basics cheese board we had an opening stock value of 10 we increased the stock level by 60 then we sold 70 which left us with a closing stock level of zero okay that is the closing stock table finished and indeed the end of this video so hopefully that is useful if it is please subscribe give me a thumbs up and i'll see you next video [Music]