Effortlessly Manage Your Bill Excel Sheet for Inventory

Bill excel sheet for Inventory

in this video we'll make this inventory management system in Excel where you can track your current inventory what you need to reorder and when the estimated arrival date will be then if we navigate to the vendor sheet you'll find all your product and vendor information in case you need to contact them about any problems finally in the order summary sheet this table will autop populate depending on your inventory levels and you know exactly what to order how much it's going to cost and who to order it from all you need to make this automated customizable and userfriendly Excel file are four simple steps so let's get into it the first step is the vendor information sheet so let's take a look at the Excel file and over here you can see I have some data laid out let's suppose that we're a bookstore and these are all of our Publishers that we have right here as our vendors we also have some data on the typical order time so how long they typically take in days the reorder amount so usually when we reorder how much quantity do we reorder for each of these books and their contact details I filled this in already so you don't have to waste your time and if you want to download this file completely for free head over to the link in the description below to analyze this data better what we'll do is just convert it into a table by pressing crl T and then clicking on okay now we're ready for step two which is going to be in the inventory Tory sheet so let's go over there that's this other sheet right here and here's where we'll be able to track our live inventory so what's currently in our bookstore you can see up top we have a few kpis like the inventory value the unique products we're selling the current date and the day of the week and then down below we have some details these are products that we currently have in store we need to find out what the product name is as well as some other information let's go ahead and fill this in like before the first thing we'll do is convert this into a table by pressing contrl T it's going to select the entire table that's good by us and click on okay now for the product here we're just going to do a simple lookup so we'll go equals x lookup is the formula we'll use and we're looking for this number 101 this product ID comma and we can find it within the list of our vendors that we've just seen within this whole list over here comma and then what do we want as the return array so what do we want as the answer we want to find what the product name is so we'll select that from the column right next to it close up parenthesis and hit enter now you can see we get the corresponding products for each of these IDs we have the quantity in stock the price per unit and the sale price all of these have been added manually except this last one here we just assumed we're selling it at three times the cost for us for this price per unit to avoid confusion we can change this to a cost per unit so this is the price we body at and this is our sale price the total value is pretty simple it's just the sale price per unit multiplied by the quantity in stock hit enter there all of this fills in because we're using a table and now we know how to do this first kpi which is just the sum of all of our total value right here all of these transactions and hit enter Then for The Unique Products down below here we want to use some kind of a count so you could just use a count formula and select all of these products here the problem is these are not necessarily unique if I order the same product twice like 101 twice you'll notice here doesn't actually change so instead what we'll do is add a unique function inside as well we'll put the unique in here and then just close the parenthesis at the very end and hit enter so now if I put 101 twice you'll see the unique products goes down by one that's looking a lot better ideally we would also like to know if we need to reorder certain item and for that we have this reorder threshold if these arrows are bothering you a bit you can go to table design and untick on filter button to get rid of them so based on this reorder threshold if our quantity in stock is equals to or lower than this four here that means that we should reorder so we want to say yes or no for that we can use a simple if statement and The Logical test says that this figure over here if it's less than or equals to the reorder threshold then we want to put a yes meaning that we need to reorder comma if not we're just going to put a no both of these should be in quotations as they are text we can close up parenthesis and hit enter you can see there it automatically fills down this one right here we have a reorder threshold of six but because we already have 12 of these books in stock we don't need to reorder that's looking good and to highlight the yeses bit better we can use a conditional formatting for this we'll head over to the Home tab and under conditional formatting here highlight cell rules and we'll go to text that contains here we want to put the yes and you can see there if it contains the yes it's going to fill it in red we're happy with that so we're just going to click on okay after we get all of this data set up if you want to convert it into visuals an excellent way to do that is using chart templates like the ones hopspot is kindly providing Us by clicking the link in the description you can access a variety of excel chart templates completely for free the download includes an Excel file with instructions on how to use a templates along with a range of chart types to visualize your data you can easily modify the data Within These templates and the charts will automatically update these templates can accommodate one column of data or multiple columns depending on your needs I personally find these templates most useful for deciding which chart type goes best with my data as it's quite uncommon to see so many chart types at once so I recommend clicking on the link in the description below to download these completely free chart templates from hopspot to level up your Excel skills and thanks to them for sponsoring this video continuing with the inventory sheet we now have the ETA which is the estimated time of arrival so as we said in this vendor sheet you can see we have the typical order timeline so this is all in days and first we're going to need to calculate the current day for that we can just use the today function close the parenthesis and hit enter now when I open this tomorrow the date is going to update so it's fully Dynamic alongside that for the day of the week maybe this is useful so we'll add it as well with the text function this is the value and what we just want is to put in quotations the D four times this is going to show the actual weekday so Monday Tuesday Etc now we can get ready for the ETA here and we only really want it if the reorder says yes then we want to know when exactly it would arrive so we're going to use an if statement first The Logical test just says that if this reorder here is equals to in quotations a yes close the quotations comma then the value if true that's where we want to do the calculation and it's going to be the current date plus however many days it takes to order this particular product and we can calculate that with an X lookup inside here hit the top key and the lookup value is by ID so we know we want this exact product comma and where can we find it as the lookup array well we can find it within this list right here contrl shift down arrow comma and for the return array what's the answer we want we want the order timeline so we'll select that from here under column F close the parenthesis and hit enter it says here we're missing a part so let's go back inside and now we've done the value if true comma but we haven't quite done the value if fals so what if it's not the case and it says no under reorder then we just want to leave it empty with two quotations we can close up parenthesis and hit enter you can see here we have these dates they're not well formatted though so I'm just going to select them all and just go to control one within here I'm going to go to the date area and select a sample date this first one works for me and click on okay so you can see that this product if we order today it's going to come in 7 days and you can see all of these other ones but they're actually not looking very good and the reason is this F5 here we haven't locked into the F4 position so we should go back inside of this formula and make sure this F4 is locked so it's fixed with the dollar signs like so you just want to add dollars in front of both the F and the for there now you can see when we hit enter there it drags down automatically and all of these dates seem to make a lot more sense so now that we know which products we want to order and who to order them from we can head over to the order summary sheet over here where we would like this to be autop populated the idea is that based on the inventory and it's saying yes to the reorder here we should just see the order summary on this side automatically to automate this process we'll use a few different formulas and the first one to find the product ID and the product is to use the filter function hit the toab key here and for the array we'll navigate over to the inventory area the array is basically what you want as the answers so we actually want the product ID part and the product we'll select these two columns comma and for the include is essentially the criteria or the condition we only want to include if this reorder part right here is equals to quotations uh yes we'll close those quotations close up parentheses and hit enter you can see here we're only getting the products that have yes under the reorder then for the quantity is how many do we want to order and for that if you'll recall it's under the vendor info tab so we'll use an X Lo up to match it based on the ID here comma and for the Loa parade we can find it within our list of vendor information comma and for the return array here's the reorder amount that's the column that we want to select comma and finally for the if not found we'll just leave it empty by adding two quotations close up parenthesis and hit enter and now you can drag this down kind of as far as you want these other areas will automatically show and so will this as long as the formula is still in there then we've got the total cost which is going to be the multiplication of the quantity by the cost per unit so for this it's equals to the quantity multiplied by the cost per unit which we're going to need to find with an X lookup lookup value again this is the product ID comma and we can find that product within the inventory sheet we can find that here as the product ID the corresponding product comma and we can find the return so what's the answer we want we want the cost per unit which is this column right here we'll select that we'll close up parenthesis and hit enter that's the first one but you'll notice if we drag this all the way down towards the bottom we get all of these value errors that's because we're going to need some kind of if not found but that doesn't quite work as we have a calculation before the X lookup so what we'll do here is use an if error Instead at the very beginning we'll say if error so if the value doesn't have an error we just leave it as is comma but if there is an error we just want to leave it empty add those quotations there close the parenthesis and hit enter and we can drag this all the way down as far as you think you'll need finally the contact and the email these are quite simple from our vendor info so we'll go equals X look up there hit the tab key and the lookup value again is the product ID comma and where can we find this contact we can find it in our list of vendors we first need to match the corresponding ID here comma and the return aray we're going to select two columns so it's going to be the contact name and email address both at once we can do that just by selecting the two of them control shift down arrow and shift right comma and as the if not found we'll just put quotations there to leave it empty close up parenthesis and hit enter we have all of these contacts and again we can drag it all the way down and for a total order it's quite simple we're just going to use the sum function and it's going to be the sum of all of these total costs based on how we've set up all of these formulas it should now show automatically so if we head over to the inventory sheet here let's suppose that we want to order a new product from our vendor info like this number 118 that's not currently in here I can put 118 there it's going to show the product for us and let's suppose that we currently have nothing in stock but we know that the price is 4.99 and we know that we're going to sell it by three times the amount and we have a minimum amount that we would need of let's say two of these products now we know we need to reorder it and we know the estimated time of arrival so if we head over to the order summary it should now be showing there we can click on that and you can see down here that we have that product that's currently on our order summary and you can see how much exactly we need to fulfill all of our requirements awesome this is all coming together very well and in this part four we're going to work on the the navigation bar on the side so we're going to have something right over here hit control shift plus to add that extra column here and we're simply going to change the fill color to something like a dark blue you can add a logo up top in my case I'm going to add my company's logo just by going over here and clicking on this device as I have it currently in my computer let me quickly resize that and then to navigate across all of these separate sheets what we'll do is insert some shapes and we're going to hyperlink those shapes so we'll go over here select a shape let's say I just put it roughly around here I'm going to go and call this one the inventory and let me reformat it so I'm going to go to no fill no outline and I'm also going to try to Center this by putting it in the middle in both senses now we just want to duplicate this so contrl shift and drag it down control shift and drag it down we want to rename these to the vendor and the Ord summary but the idea is that when I click on it I'm just going to select it right here and I'm going to press contrl K this is the hyperlink area so when I click on that I want to go to the inventory sheet click on okay there and now let's suppose I'm coming right here go to inventory and it takes me to that inventory sheet so you start to get the idea there let me fast forward how I do the same for these two buttons once we have all three of these hyperlink we can just copy the whole column with crl C and then paste it over here with control shift plus you might need to resize the logo up top so just basically move it around ing to how you want it and same thing down over here let me fast forward that once you finish the navigation bar you should be able to move dynamically across all of your different sheets and for this order summary sheet if you want to be able to export this in one click as a PDF what you can do is watch this video over here where I walk through how to go about that or you can also check out our Excel course over here hit the like and that subscribe and I'll catch you in the next one