Google Sheets - Inventory Management System Template

okay so in this video I'm gonna show you how I can create a basic inventory system so it's not gonna be too basic but it's also not gonna be probably way too complicated so we'll start by creating a simple product list so I'll just name this worksheet like items you can call it anything you want obviously and here I'll do the ID of the item or you know whatever you want to call this and then we'll do the name of the product or description and I'll also do the pack size here and probably that's it you can add more things here if you see fit I'm gonna stick with this so my item IDs so I'll just do some random stuff here you okay so that should do it so be our items so I'll create another worksheet so this will be where we receive new inventory this is just items so I'll just copy this headers from here we'll add a couple of other things like QT why something like this so here I want a drop-down of different item SKUs that we have available here so I'll just go here and select pretty much as long as you think this is gonna go I'll just select all the way down so I'm gonna press here command shift down select this entire area go under data data validation and here lists from a range if that's not selected go ahead and select that click in this box click in this little icon go to items and basically just select this list and again you want to select more than what it is just for future as you keep adding to this you can either the warning or reject any other items if you want I'm just gonna do reject anything else hit save and what should happen now let's just go on top here if I go to here it's gonna add me these dropdowns where I'm gonna be able to select one of the items that are coming from this particular list right so if I'm trying to receive one of these items like this one I want to auto populate at least whatever information I can from here which would be description and pack size so I'm gonna go here and do a vlookup formula and I'm gonna do lookup this comma and I'm looking at that up in this items table starting from this a column and going down so I'm gonna select this much I'm gonna press f4 key to lock the table you can also add to dollar signs manually it doesn't matter I'm gonna drop this end for a reference to just shift this all the way down so that's gonna be the table of R a comma and then assuming this was description it will be column number two and then comma zero for exact match make this bigger a little bit so now if I take this formula and I drag this formula down well it's gonna give me n A's for everywhere that it's not selected if I select something it will autofill the description for the item now I don't want to say this an ace though so I'm gonna try to clear those so it doesn't say any so what we'll do we'll just put this inside of an if statement so we'll say if this equals two double quotes meaning blank then we want to leave it blank otherwise we're gonna do this vlookup so I'm gonna go here closed parenthesis hit enter and now if I drag this down it should just be blank and if I select something it should just Auto populate whatever it should be and you should drag this formula it's pretty much all the way down and I'm gonna do that by doing command shift down or control shift down and then command D or control D to populate that formula down so now this should all be pre-populated with that formula so as you go select items here it should just populate now I need the same four pack size so I'm gonna take this formula copy that control C command C hit escape go back here and paste now the only difference here I want the pack size so that's gonna be column number three from our source data which is items so column number three so I have videos covering all this functions vlookup if ranges all of them separately in separate videos if you want to understand in more detail what's happening here go ahead and watch this videos otherwise this should do so I'm gonna double click here I think that went all the way yep it did good so that's the pack size the same way we have the description so that's gonna be populated automatically now I want to do some definition that shows that like these you select and these are Auto populated so I'll just do a little bit of background color for these columns so quantity we are getting like 7 of this 4 of these 3 of this 9 of that so every time we receive new items to our inventory we'll just do this so this will quantity expended so what we'll do we'll just take the pack size and multiply it by this so that it will be the pack basically quantity and if you are just buying by piece you can just do pack size one for everything here so that's quantity expanded I'm gonna double click and again that's gonna drag that down apparently that dragged it again up to here now I need this to go all the way so again if I drag this formula down that just gives me zero for all of these which is fine I'm gonna stay with that so zero is okay and yet again I'm gonna highlight that with a color so I know that that's auto-populated cost per piece so we're getting seven of this let's say we're buying the six pack of that for two dollars so if your cost for everything is always gonna be the same then you could incorporate the cost here too but most of the time it's probably not so you're gonna get your items received sometimes at different costs as you buy them so that's why I kept it here I'm gonna select this column and change it to currency so we have this and finally total cost is gonna be so I'm gonna do this times this I guess I did this cost per piece not cost per pack but this should probably be cause pack not piece that makes probably more sense again drag that down all the way that formula from the corner and now we have this so this is a formula I'm gonna highlight this too so basically here we'll be entering the SKU our quantity impacts and then the cost per pack and this should basically just give you your total cost so if you want to also get cost for piece you can add that here too and just take your total cost and divide it by quantity expended again drag that down so because this is a division we're gonna get an error when you're trying to divide by zero so we should handle that so I'm just gonna put this inside of an if error function and I'm gonna say if that's an error comma then we're gonna leave it blank I think we don't even have to do that just like this should work in Google sheets in Excel you would have to do double quotes so again ctrl shift down ctrl D so now we have this and if I take this and convert it to currency that should look prettier and if you want it you could also add some borders and stuff like that to make it look nice maybe do some borders in a bottom that are thicker in here something like this so this so white ones are the ones we're entering and the rest should pretty much auto-populate by itself over time see we'll be getting the same item multiple times here like here and then you'll probably buy the same thing all over again see it pre-populates here and if you go here and do like four of these and maybe this time it's 210 or something it should just populate the rest and we should be fine I'm also gonna do another one for this coca-cola thing so let's say this is three of those and it's cheaper or something so now we're gonna do our sales side I guess so a lot of days is gonna be the same so I think what I'm gonna do I'm just gonna delete this and take this receive and just duplicate this and just call this one sales so this will be the receiving side when we get new product and this will be when we sell products we basically ship them up you can call it receive or maybe ship or whatever you're gonna call it so when we ship our items out most likely we'll be shipping by piece instead of shipping by pack but who knows I guess it depends on your business so you have to adjust based on what you do so I'm gonna do sales now this part is gonna be the same we're gonna take the item that we sold whatever it's gonna be description is gonna again are we gonna get the description we're gonna get maybe we don't need the pack size in this case and if you wanted to you could keep it but I think I don't need it so I'm going to delete that now the quantity we'll be the items that you sold so let's say we sold 45 of this and 32 of that and whatever numbers that would be it doesn't matter you could still do the pack thing if it was per pack you would just keep the same thing but how much we're selling this items for assuming that's gonna be per piece I'm just gonna type some numbers this will be price per piece and this is basically the same it's just this times this that's total and then cost per piece we already have that so we don't really need this thing so this will be my sales side again I just keep white for whatever we're entering and the rest is gonna be in a color meaning it's gonna be pre-populated with a formula it's just gonna work out so we have our receiving side we have our sales side now I'm gonna do my inventory side so I'll just take this copy paste it and you would probably also have a date in here someplace by the way so date let's actually just do a little column here use the format painter do this and here for your dates you could again select this area I'm gonna do again ctrl shift down command shift down to select it data and then do data validation and choose date it's a valid date and reject any other input its save so now this will be like a date picker so you can select the date and we should probably do the same thing for sales side so I'm gonna copy that go to sales and paste that column over here so now we have sales date as well now our inventory we need to again get a list of our items in here somehow so for that we'll probably have to get you know whatever lists we have received I think populate unique list out of this so I'm gonna go here and do unique function and go to receive side and basically just select starting from this well we don't want the a1 in here we want to start from a to the second row and then all the way down so I'm gonna drop the end reference close parenthesis and hit enter so that should populate a unique list from whatever you have here because your real item list might be a lot bigger than your actual inventory list now with that we also want to get the description as usual so that will be this same formula pretty much so I'm gonna just copy this hit escape go back to here and paste and then drag that down again control shift down control D or command D if you're on a Mac and the pack size is gonna be very similar I think I already had it here so I'm going to just copy hit escape go back to this and paste and drag it down okay so we need total quantity received for each so I'm gonna do received so the way we're gonna do that we would have to basically just sum up all of these received and maybe also all of these so I need to sum up for that water one here here here and I'm gonna do that using some ifs function so I'm gonna go back to this let me zoom out a tiny bit so I'm gonna do some ifs so I want to sum from this receive spreadsheet this quantity column and I'll probably not do the whole column but start from here and drop the end reference and I'm gonna do again f4 to lock this range with dollar signs or you can again type the dollar signs manually comma I want to do criteria range would be my items here so again f4 to lock it and drop the end reference comma and I want to do this for I'm gonna go back to inventory and do it for this item so that should give me the total for water so see it says 20 which means from here it's basically doing 7 plus 9 plus 4 which ends up being 20 so that's correct and then we're gonna take all of these and again drag this formula down and I think I can leave with those zeros that's fine so that's our quantity received we also want to get received expended so we get by piece and it's gonna be very similar I'm just gonna use the other column so I'm gonna do some ifs go to receive show this expanded column as my some column f4 to lock this throughout the end reference comma then I want to do it by again item again f4 and drop the nth reference to send this all the way down comma and then I'm gonna go back to inventory and click on the item again command shift down command B or control shift down control D that would be that so we have the number received now we also want to get the number sold for each one of those hopefully you have better column names for your columns than I do but it doesn't really matter so I'll do some ifs again this time I'm gonna go to sales select this salt quantity LOC remove the end reference comma and then the criteria range is gonna be again this f4 to lock it go back to this comma and then finally go back to inventory and click on the item ID again command shift down command D that should do this so that's sold that's received right so then we can do the difference so apparently here we sold more than we received which hopefully doesn't happen but who knows so we'll do I don't know variance difference whatever you want to call this I guess variance would be not exactly that but so this is gonna be simple I'm gonna do the received quantity - sold quantity and then drag that formula down I guess this will be quantity on hand so let's go receive a little more of this so we have positive stock so if I go receive add this and then do ten more cases and let's say it's $2 anyway doesn't matter so now we have C quantity on hand updated we have received we have sold and we have the number basically left and as usual these are formula columns so I'm gonna go ahead and select all of these I think these are also formula all of this is formula columns as a matter of fact so we should just go ahead and highlight all of them so we don't touch this this is Auto populating so we might also want to calculate I guess the cost so let's see how we're gonna calculate the cost for us so the cost is gonna be for this basically the total cost which will basically just sum the total cost for inventory received so let's do that so that would be again and out if function and this time I'm gonna go to receive and do total cost again f-for get rid of the end reference comma and that's gonna be by item id f4 drop the end reference comma go to inventories click on this and drag that formula down and this should be probably currency and we should make it the same color as everything else so I also want to do an estimated cost for quantity on hand now for that we should probably get an average price for each item you would probably do this in many different ways depending on your situation possible to go buy the latest price for the item that's one way to go maybe that would probably make the most sense when I'm thinking about this maybe get the latest purchase item price for that item and based on that estimate the cost instead of doing the average so I was thinking maybe do the average over time and see what the average is but now that I'm thinking about it maybe just get the latest price and multiply by quantity on hand I think that's what I'm gonna do so now I need to get the latest price for this water one well let's see how we're gonna do this I'm gonna take this this a column from received and lock that and check if that equals two from this inventory this item just for clarity I'm gonna put this in parentheses and I'm gonna multiply that by the row formula and I'll go to receive and again select that same column f4 again to lock it close parentheses and then I'm gonna make this an array function by doing control shift enter command shift enter didn't want to do that so I'm gonna have to do this again there this if nothing else works you can also just type a rate formula function in the beginning and closed parenthesis so what this is giving me is eros for everything else he says two five six eight so if I go from here in this list see it's basically water - we have five we have six and eight that gives us which rows have this water one in them now I want to just grab the latest one which is gonna be the H so I need to basically get the highest number the eight out of here so I'm gonna do that by just putting this whole thing inside of a max function like this and that should give me eight which is basically the latest row that this water one was in now what that's gonna help me do now I can get cost which is gonna be the latest cost and we should probably change this so we can actually see the difference 32 cents so that's what we need to get so I'm gonna do index from that so I'm gonna take this formula what I have not the array formula just a max function without the array formula part I'm gonna cut that which is basically copy and delete and then I'm gonna do index function here so in index function let me delete this part the first part is gonna be the column where we need the values from so it's gonna be this cost column I'm gonna select the whole thing f4 to lock it comma and then I need the position so the position is gonna be what the formula was so I'm gonna paste that max function close parenthesis again hit enter and there it is 32 that's the latest received price at least per piece for that item so now we should be able to drag again this formula down let's see what happens when we go below okay good basically we get nothing so that's good so I'm gonna do a command shift down and command D to drag that formula down now we got not the average but latest cost I'm gonna call it or current cost I guess that's better so now that we have the current cost we can also estimate the value which is going to be our current cost multiplied by quantity on hand drag that formula down so that's estimated value for that particular product or item whatever you want to call it now I'm gonna add a couple of rows here select all of this and clear the formatting now all of these should be also in that color I should probably do this colors in the end but here I am so the reason for this I just want to have some place here to do the total so we'll do some select this drop the end reference close parentheses and that will be total estimated value and any other totals you may want to do you could do them here because putting your totals in the bottom is a horrible idea now the only thing I want to add to this I think is just average item cost you could just do the average right and do average ifs and get the cost from here basically I want to get the total cost for the item and divided by total quantity so that would be I think what we call a weighted average if I'm not mistaken but somebody will probably correct me on that so if you just wanted to do regular average you would do average ifs and then you would go to receive and basically just select this cost column and f4 to lock it remove this end reference comma and then select this column and f4 to lock it and remove the end reference for that column to comma and then go to inventory and click on your number but instead what I want to do I want to do this I want to do some ifs and go to receive and add the total cost for this item which is going to be this column and lock would have four comma and then select this column again lock it and remove the end reference comma and then go back to inventory and click on this and maybe we already have done that didn't we that was our total cost received yeah we have so we don't even have to do that part we already have it here that's good we also have receive expended which is I guess the other one which is summing the quantities in it let's just make sure that's doing what it's supposed to do E and a so E is this a is this yeah so that's exactly what it is so we don't even have to do anything too complicated we just have to take our total cost received and divided by receive expended and that's I think pretty much it we have to remove this division by zero error so I'm gonna do if error around this whole thing again command shift down command D to drag that formula down and we should have a cleaner column now maybe Center your headers or whatever but keep in mind anything I did in this tab is basically a formula we shouldn't be touching any of this pretty much at this point so I should be able to now go here and add a new item I'm receiving from this items list let's do that 16-ounce bottles like this and then I'm gonna do 40 of that and then it's let's say $6 or whatever that should calculate the rest and if I go to inventory see it's already here we have our totals we have our quantity on hand we have everything else and if we go to sales and or shipping whatever you want to call this if you add this amount it should basically just automatically target this one as well and I suppose sometimes you'll do some real inventory and find out that your numbers in your stock are not exactly the same as what shows up here so let me add another worksheet for this I'm gonna call this one inventory check so here I'm just gonna have go here and do this this column I'm gonna copy this a column paste it here maybe we'll also copy this description column and paste it here to quantity I'm gonna call it quantity offset so let's say we did an inventory and we found out that for this item it shows on hand 56 but we really have 54 or something like that so we have less - so it would be like negative 2 for this or maybe you found out for something else you have more so maybe this water 16 8 more apparently this column is not tracked down so I'm gonna make sure I do that so basically we'll want to have these numbers somehow affecting our inventory numbers here we'll probably have a date on this as well so I'll just copy and paste that column which it's gonna be that date that you can select like when you did this and okay so let's see how we're gonna add all of that to this and that's really quantity on hand so right now we're just taking this minus that so whatever we receive minus whatever is sold now to debt we want to offset our inventory check quantity so we need to add all this up now one other thing we have to remember is that you could do this inventory over time again and then the same item could appear here multiple times so maybe here you have negative two at some point so now I'm gonna basically just have to sum up these totals by this and add it to our quantity on hand so I'm gonna take this and plus I'm gonna do some ifs go to inventory check select all of that so LOC quantity offsets remove the end reference comma and then select ID numbers f4 again remove the end reference comma and then go back to inventory and click on the item number hit enter and make sure I drag that formula down so now we have this so we have 54 items because we just did an offset of negative 2 so if I go and change this to positive 2 that should go to 58 because it was 56 and we add 2 so 58 and now we can do stuff like this too so negative too so go back here it's 54 and overtime if we come back and do this again for this item so maybe this time we found out we have like eight more of these so if we go to this here we have it in our inventory so basically inventory tab is all auto-completed for us we'll be working on all this other tabs so I'm just gonna move this here inventories auto-completed we do the rest in receive sales inventory and if we want to do more items that we can work with we just add it to our items list and I think that's good enough for this video thanks for watching please subscribe and I'll see you next one

Channel: Learn Google Spreadsheets

Views: 327,206

Rating: 4.9314756 out of 5

Keywords: Google Sheets, Inventory, Management, System, Template

Id: nAcNgmYPA-4

Length: 34min 14sec (2054 seconds)

Published: Thu Apr 09 2020