Streamline Your Invoicing with Our Invoice Template Drive for Inventory

Invoice template drive for Inventory

Hello friends welcome back my name is PK in this video you will learn how to create an inventory management system in Google seats as you are seeing my screen I have created this template to manage Warehouse inventory as well as you can search the location within the warehouse or within the store also where you have kept your product here we have taken some dummy product ID and the product name which can be replaced with your original product name okay so the first page is the product search you can search the any specific product to check the availability of the stock and the location of that product within the store and whether you should reorder that or not here we have the different product ID which you can search from the drop- down list or you can search it from here also like one0 and then let's say put six and just enter the name of the product is product six which can be replaced with your original name of the product currently we have the 71 items available and it is available in D6 box in your store and the reorder label of this product which has been defined initially is 33 that means we don't need to reorder this product as of now I'll quickly show you the summary of this so this is the summary here we have all product IDs the product name and this is the today's date which you can change as per your requirement by default it will be today this is the opening balance for today in item for today for that particular product this is out and now this is closing balance or we can say the available stock as of today here we are showing the reorder label of that product and then we are showing whether we should reorder that product or not okay so here we can see there are some yes we can put a filter on this table from here filter and then we can filter here only yes Okay click on okay so we have got the list of the product which has to be reordered all right I'm just removing the filter from here remove filter now I'll show you the input seat wherein you have to enter the data so this is input seat and here we have date then the product ID product name will come automatically and you have to select the transaction type in or out you can select it from here then you have to put the transaction quantity that how many in or how many out okay so this is manual entry seat then we have here product Master which is onetime activity we have product ID product name location within store and then here we have the reorder label all right now I'll show you how we can create this in Google sheet step by step so let's add a new spreadsheet here so let's go to the file new and let's click on spreadsheet so we have one blank spreadsheet here so let's rename this as product master so first we will create the product Master Okay and here we can take product ID then product name and the location wherein you are keeping it inside your store okay then we are taking reorder label you can decide the reorder label ing to the sale of the product that how many you should keep every time in your store okay for now we will use the same product master so let's go back here and we can copy this as it is just copy and come back here and let's paste this okay go to the view and so and and check the grid lines all right so this is product Master now we will create the input seet rename it as input seet okay so here user will enter the data first of all we will take here date then we will take product ID product name and then we will take transaction type in or out okay and here we will take the quantity transaction quantity all right so let's select this range so for now I'm taking this range you can add more rows so let's align this Center align this middle reduce the size you can take the header color let's say some dark color so let's say I'm taking this color font color I'm taking white and make it bold okay for date we will take a calendar if I will double click the calendar should appear here to select the date so let's select this range right click go to the drop down and here just select is valid date click on done if I will double click here it will show this calendar now okay so let's say I'm taking here first December product ID let's say I'm taking 1 1 for product name we will use here V lookup V lookup select this product ID comma go to the product master and select these two range comma 2 comma 0 just close the bracket so we will get the product name automatically but what if you enter the incorrect product ID it will return the error so we have to prevent this error so what we will do we will use the if error function before the vook up so let's use here if error put the bracket and in the last put comma and let's so blank and just close theet all right now transaction type we will take in or out so let's select this range and add a new rule to this range okay and here we will take in the option two we will take out let's take some color for in I'm taking this green color for out I'm taking this red color click on done so we can select here in make sure for each product first entry we will do in to show some opening balance okay and let's put here 100 for now for this range I'm filling it down okay just to make sure the formula is filled here and I'm I'm just making some different color background color here so that let's say I'm taking this gray color now it is indicating that we have put the formula here now we just need to put here second entry 1 0 2 product two automatically will appear double click here to enter the date let's select this let's take here in let's put the quantity for now 100 okay similarly we can do the entry for each product so let's say here I'm selecting all product IDs and I'm pasting that here and I'm filling the same date for now and in to do the opening balance here okay select this range and use contr D to fill the formula and the data validation all right so our input sheet is ready now we will add the new seat and that will be the inventory summary rename it as summary or inventory summary okay here we will show a summary table for our inventory all right so let's take here date as of which date you want to show that summary so by default I'm taking here today so so that it will always show the latest information okay let's take here this color and here take the light color let's say this one make this B the white font align them Center all right now from here I'm taking heading take first product ID then the product name and here I will take the opening Valance opening Valance then take in out and finally closing Valance and here we will show the reorder label then reorder required or not okay now we will quickly format this or let's put the product ID first from the product Master just select and copy and paste on the summary sheet okay now we will select this range align this Center align this middle reduce the font size and we will make the header in the different color so let's take some different colors let's say I'm taking this color with the white font make this bold okay now I will select this range again and I'll go to the border and let's choose some color here let's say I'm taking this color and click on all borders go to the view so and uncheck the grd lines all right to get the product name we will use the V lookup function this one V lookup select product ID comma go to the product Master select column A 2 B comma 2 comma 0 close theck we getting the product name but still we will use here if error to prevent any error and and let's put here blank let's close the B it hit the enter okay to auto fill it you can use control enter all right for opening Valance I'm taking different color let's say I'm taking this dark yellow one and here we will put a formula and I will change the color for this range Also let's take this color okay for opening Valance we will take the data from input seet let's move this summary seet here in the input seet we will put a formula to get the sum of all in of that particular product before the date available here okay minus out so we will use the sum IF function here so let's use the sum ifs we will put the multiple condition okay sum ifs first thing we will provide the sum range go to this input seet and select this we will get the sum of quantity okay press F4 to freeze this comma then the next criteria we will select this one press F4 to freeze this and we will take the sum of in okay the next criteria we will take the product ID select this range press F4 to freeze this comma the product ID we will take this one okay A6 and put the dollar before column comma the next or the last criteria is the date so select the date column here this one press F4 to freeze this comma we will take in the inverted comma less than and we will select the date here come to the summary seat and select this one press F4 to freeze this just close the bracket and hit the enter we are getting the sum of in only but we have to minus the out also so we will select this same formula copy this and minus and paste that formula and make it slightly bigger to see this and here in the second formula we will take out and rest formula will remain same and just hit the enter all right now if I will go to the input seat and I will make a new entry here so let's take a new entry and that entry I'm making 1 0 1 and on the second the symbol and make it out and let's take 30 now if I will come to the summary I can see the opening balance is only 70 all right now to enter in in and out we will use the same formula which we have taken here for in just copy and let's paste that formula here equal to and paste let's hit the enter now the only difference is here we have taken the less than date but just remove this we will take the equal to date or only date and just hit the enter so it is showing zero because we are not adding any quantity on this date just copy and paste this formula the only difference is we will use out in place of in okay so this is also zero now if I will make any entry on Seventh so let's double click and make the entry on Seventh let's take here 1 1 and make an entry out on this date and let's say I'm making it out as 50 now let's come here in the summary the opening balance is still 70 we have made out 50 on this state so the closing balance should be opening balance plus in minus out that is 20 okay now here we will change the color for in let's take this green color for out let's take this red color for closing balance let's take this blue color okay now we will change the color here also select this range for in let's choose this color for out let's choose this color and for the closing Valance we will choose the theme color this select this range and choose this color all right now we will fill these all formula down okay for reorder label use the V lookup function V lookup take the product ID here comma go to the product master and select this range a to d comma 4 comma 0 just close theet so this is our reorder label double click to fill this down and you can use here error also to prevent any error and put here blank and just close fill this down so it is filled now reorder required or not we will use a IF function here if the closing balance is less than or equal to reorder label this one then we will put here yes else we will put here no just close the bracket hit the enter okay and just fill this down all right now select this range and we'll go to the format conditional formatting and here we will take is equal to this one and put here yes and the background color we will take this one the font color we will take white and done just close this so if theorder is required then it will show here yes all right so the summary is done on the top you can put the heading if you want let's make it slightly bigger select this range use Mark cells align this Center align this middle and you can type here inventory summary you can make the font size slightly bigger if you want to change the font you can take different font let's say this one one make this bold background color you can choose little darker color let's say this one and font color let's say this one all right so inventory summary is ready we can add one more seat that is product search to search the immediate of a product so add a new seat and move this here rename this as product search okay now here we will take product ID and then we can take the product name and available stocks location within a store and then reorder label and reorder required all right so we have taken these all things and here we can create a drop down right click go to the drop down in the criteria select drop down from range and select the range here go to the product master and select the range of product IDs click on okay and you can choose any product ID let's say this one for the product name use the vup select this value comma go to the product master then select these two comma 2 comma 0 close the bracket use the if error function before the we look up if error and if not found then put this Dash just close to get the available stock we can put the V up from summary seat close this so this is closing balance is available is stock reorder label also we can get it from here and reorder required we can get it from here so we can put a v lookup here V look up select this cell freeze this press F4 comma come to the summary seat and let's select from 8 column F G H okay just freeze this press F4 put here 6 comma 0 to get the available stock and we can copy the same formula and we can paste that here and paste it here so here we will change it as Sav in reorder label and we can change it eight okay location within store we can put the vup from product master so use vup this cell comma product Master select this range comma 3 comma 0 okay that is location within a store so that is D1 all right we can put the if error everywhere so here we have already done so let's put the if error here also if error in the last just add this task close and here also we can copy this formula again and paste here and here also so we will take here s and take here eight all right so this is done now quickly we will put a border here all border and align this Center and middle increase the size of the row height okay and we can fill some background color here so let's say I'm taking this color and make this bold and here I'm taking some different color this light color for input cell we can take some different colors line This Center Line This middle so let's take here this color and here you can take little lighter color this one all right go to the view and so uncheck the CR lines so this is our product search if I will select another ID let's say this one so this is working if I will select product ID 1 0 1 here it is showing yes okay so what we can do go to the summary and copy this cell and here just go to the edit paste special and paste conditional form meting only okay so it is in red now for two it is not in red all right friends so friends that is how you can create this inventory management system in Google seat you can use this for your daily work friends I hope you have enjoyed this video If you like this video please hit the like button and please subscribe my channel to get the regular update of my videos thank you so much for watching