How to create an Inventory Management System [using Excel] in 2021

hello and welcome to the mr spreadsheet youtube channel if you would like to create and design your own inventory management system using excel spreadsheets then this short tutorial will show you how the inventory management spreadsheet will record inventory transactions and will monitor inventory levels inventory valuations and product line profitability the final template is both easy to use and to understand the inventory management spreadsheet will use many of excel's functions and commands including the use of drop down boxes to select data we employ the sum if command and retrieve data using the vlookup function we also use if commands to isolate data and introduce the concatenate and the mid commands to perform advanced data selection routines as usual i will show you how to obtain a copy of the completed template later on in this video and if you need any help on the excel functions that i've used then please do leave me a comment below i hope you enjoy watching open up a new workbook and we will start our inventory management system by entering our first 10 products select cell c3 and enter code and in d3 enter description in cells c4 through to d13 copy in my product codes and alternatively enter data more suited to your needs now highlight the range c4 through to d13 and from the formulas ribbon select the define name tool and enter the name products and then click ok our inventory management system needs to record details of inventory purchases and the cost of those purchases so let's expand the table to accommodate these in cell e3 enter purchase quantity in f3 it's purchase cost and in g3 we need the average cost now we need to include details of our sales quantities and sales values so enter sales quantity in cell h3 and sales value in i3 we would also like to calculate our profits for each inventory item so enter profit in cell j3 our final two columns in this table will record the running or cumulative inventory quantities and inventory valuations for each product line okay now that we have our inventory management table created let's apply some formatting and give the table a heading select the range c3 through to d3 and apply a background color of green select the range e3 through to g3 and apply a background color of mid green select the range h3 through to i3 and color this mid blue j3 is colored mid orange and k3 through 2 l3 is colored mid-gray now make bold the entire range from c3 through to l3 and change the font color of c3 through to d3 to white highlight the range c3 through to l3 and from the home ribbon select the borders tool and apply all borders highlight the range c2 through to l2 and use the merging center tool and enter inventory management as the heading increase the font size and apply a background color of light green highlight the range e4 through to g13 and apply a background color of light green the range h4 through to i13 becomes light blue j4 through to j13 is light orange and k4 through to l13 becomes light grey and finally from the page layout ribbon uncheck the view gridlines box to give our worksheet a crisp and clear background now that we have defined our inventory management table we can now create a new table to record our inventory purchases and our inventory sales we will create this inventory movements table in the next session we will create our inventory movements table by selecting cell c18 and entering in the name code d18 becomes description e18 is type f18 is price g18 becomes quantity and h18 is the transaction value highlight the range c18 through to h18 and apply a background color of green and a font color of white and then make bold select the range c18 through to h28 and from the home ribbon select the borders tool and apply all borders now let's give this table a title highlight the range c17 through to h17 and merge and center these cells and then enter the title as shown increase the font size and then make bold let's enter our first inventory transaction enter the product code of w101 in cell c19 we would like the description in cell d19 to populate automatically we can do this by using excel's vlookup function you will recall that earlier in this video we gave the range c4 through to d13 a name we called this range products and we'll now use this range to help us with our vlookup command so with d19 selected type in the formula equals vlookup open brackets point to cell c19 comma enter the word products comma then a 2 a comma the word false and now close the brackets the correct description of widgets gray is retrieved from the second column of the products table navigate to cell e19 we want the value in this cell to be either purchase or sale we can use excel's data validation tool to achieve this with e19 still active select the data ribbon and then pick up the data validation tool from the dialog box that opens choose list from the allow field and in the source field type in purchase followed by a comma followed by sale with no spaces and now click ok cell e19 now contains a drop down box from which you can choose which type of transaction this is please select purchase we can now complete our first transaction line enter a price of 15 and a quantity of 10 in cells f19 and g19 our transaction value is simply f19 multiplied by g19 so enter this formula into cell h19 now let's copy our formulae down to the end of the inventory movements table highlight the range d19 through to e19 and now copy and drag down these formulae to the end of the table in row 28 similarly highlight cell h19 and copy and drag this down to h28 that's great all the formulae in the inventory movements table have now been entered however all the description lines apart from d19 are showing the n a error message this is simply because the description field is totally dependent on there being a valid code in the code field as all of these are currently blank then the n a error message is returned we can correct this by using a simple if statement in front of our vlookup command in the description fields this if statement will examine the contents of the code column and if the column entry is blank then the description will also return a blank select cell d19 and in the command line place your cursor after the equal sign and before the v a vlookup and then enter the following if open brackets then point to cell c19 equals inverted comma inverted comma followed by a comma inverted comma inverted comma again followed by a comma and then navigate to the end of the formula and add in a final closing brackets now with c19 still highlighted copy and drag down the new formula to d28 hopefully the error message now disappears finally in this section delete the contents of e20 to e28 the narrative hopefully disappears but the drop down boxes will still remain now select the ranges f19 to f28 and h19 to h28 and format the number to two decimal places in the next section we will begin to update the inventory management table with the data entered in the inventory movements table in order to create our formulae for the inventory management table we need some transaction data in the inventory movement schedule so please pause the video whilst you enter the additional nine lines of data in the movements table noting that sales quantities are recorded as negative values now select cell e4 and enter in the following formula equals sum if open brackets the word purchases followed by a comma and then the command concatenate open brackets point to cell c4 enter a comma then it's inverted comma followed by a p followed by inverted comma close the brackets then another comma then the word quantity purchased and now close the brackets ignore the name error message that this formula returns and now i will explain all the elements of this formula and how it helps us to accurately complete our purchase quantity values our first formula in the inventory management table needs to collect all instances where there is a purchase of product w101 in the transaction table now we can see that there are five transactions for product w101 now of these five transactions four are purchases and one is a sale our purchase quantity formula only requires that we select the four purchases and that we ignore any sales transactions our formula therefore needs to select all instances where w101 is the product and the transaction type is purchase we can achieve this by using a sum if command which we have already entered in cell e4 but first of all we need some way to identify the instances of purchases of product w101 navigate to cell i19 we're going to create a formula that specifically identifies and matches products with their transaction type so enter the following formula into cell i19 equals concatenate open brackets point to cell c19 enter a comma and then the command mid open brackets point to e19 enter a comma then a one then a comma and then another one and then finally two closing brackets the concatenate command says join the following criteria together to form a single string c19 refers to w101 and the command of mid open bracket e19 comma 1 comma 1 close brackets says examine the contents of cell e19 which in our case is equal to the word purchase and from the word purchase return the first character which equals p concatenate or join these two together and you get the string w101 p we now have correctly analyzed the contents of the two cells and created a unique code for it which will help us in the sum if command now let's copy this formula down from i19 all the way down to the end of the table in i-28 each line picks up the relevant product code and then adds either a p or an s to it this now makes our use of the sumif command a lot easier as we now have a unique criteria range from which to aggregate our purchase quantity data our selection range of i19 to i28 needs to be given a name so that the sum if command can pick up the correct values so highlight i19 through to i28 and give this a name by selecting the define name command from the formulas ribbon enter the name purchases and click ok the second element of the sum if command in cell e4 of the inventory management table is the criteria w101 in cell c4 but we need this w101 to match a corresponding value from our purchases in the range i19 to i28 all of these will return the extended values of w101 plus either an s or a p so returning to our formula in cell e4 we need to join together or concatenate the value in c4 which is w101 and the letter p finally in the sumif command we need to identify and name the quantity purchased range this is in fact the range g19 through to g28 so highlight this range and from the formulas ribbon select the define name tool and enter quantity purchased with no spaces in the name field and now click okay hopefully your sum if command is now showing a value of 51 which is the aggregate value of all the items shown in the orange cells in the inventory movements table please don't worry if you do not get the entire process right on your first attempt the process is quite advanced but once mastered it will prove an invaluable technique going forwards let's use a similar formula to calculate the cost of all of our purchases of product w101 we will use the sumif command again and pick up all the relevant values in the inventory movements table but first let's name the transaction value range so highlight cells h19 to h28 and select the formulas ribbon from here choose the define name command and use the name of cost of purchases without any spaces and click ok navigate back to cell f4 and enter in the following formula equals sum if open brackets the word purchases then a comma then the command concatenate open brackets 0.2 c4 inverted comma followed by a p and then another inverted comma and close the bracket then another comma and then the word cost of purchases and close the bracket cell f4 should populate with 646.00 which is the aggregate value of all purchases of product w101 the average unit cost of purchases of product w101 is simply f4 divided by e4 so enter this formula into cell g4 this returns a rather ugly looking value to six decimal places and we also know that if there was no value in the purchase quantity field this formula would return a divided by zero error so let's correct both of these firstly change the formula in e4 to read if open brackets e4 equals zero comma then a zero then a comma and then f4 divided by e4 and close the brackets now if we have a nil value in e4 the formula does not return an error message now let's add the round down command such that our previous formula in g4 is nested with round down set to two decimal places the entire formula now reads equals round down open brackets if open brackets point to e4 equals zero comma then a zero then another comma and then plus f4 divided by e4 and close the brackets and then finally a comma and then two and close the final bracket our sale quantity uses exactly the same formula as our purchase quantity formula the only difference being that instead of using a p for purchase we use an s for sale so enter the formula as follows equals minus sum if open brackets purchases comma concatenate open brackets 0.2 c4 comma then inverted comma then s then inverted comma and close the brackets then a comma and then quantity purchased and close the brackets the table returns the aggregate sales quantity extracted from the inventory movement schedule we can do exactly the same for our cell value the formula becomes equals minus sum if open brackets purchases comma concatenate open brackets 0.2 cell c4 comma inverted comma s inverted comma close the brackets comma and then cost of purchases and close the brackets hopefully the formula will return the value of 300 which is the aggregate value taken from the inventory movement schedule the aggregate profit formula in cell j4 is simply the total sales value in i4 less the sales quantity multiplied by the average cost so in j4 enter equals plus i4 minus open brackets point to g4 times 0.2 h4 and close the brackets the correct profit figure of 110.10 is returned our cumulative inventory quantity is the total purchase quantity less the total sales quantity so enter the formula equals plus 4 minus h4 into cell k4 and our cumulative inventory evaluation is simply the inventory quantity in cell k4 multiplied by the unit average cost in g4 so enter the formula equals k4 times g4 into cell l4 hopefully the values of 36 and 455.76 are returned now that we have entered all our formulae for the first product w101 we can now apply these formulae to the remaining nine products so highlight the range e4 through to l4 and copy and drag the contents down to row 13. that's great it all seems to be working fine let's complete our work by adding a few totals applying various cell formats and hiding or deleting unwanted data or values let's have some totals for total purchase cost total sales value total profit and total inventory valuation select cell f15 and then simply double click the autosum tool select cells i15 j15 and l15 and repeat the autosum command now highlight the ranges f4 through to g13 i4 through to j13 and l4 through to l13 and then right click your mouse select the format cells option and then the numbers tab and then number and then set these to two decimal places with the 1000 comma separator checked finally highlight the range i19 through to i28 and change the font color to white such that these values are now hidden use the format painter tool to change the background colors of our total fields and then make these bold and finally apply some alternate line formatting to our inventory movements table and that completes our design work we now have a fully functioning inventory management system which we hope will be of use to both you and your business we do hope that you enjoyed our inventory management spreadsheet tutorial and that there was lots of content that you found both useful and informative now if you would like us to send you a copy of the completed template then please give this video a big thumbs up and subscribe to our youtube channel alternatively please visit one of our channels on either facebook instagram or twitter now if you are a small business and you want to keep your business accounts records in excel then why not watch our accounting spreadsheet tutorial alternatively why not take a look at our how to keep inventory in excel video this popular tutorial shows you how to create a more basic inventory management tool

Channel: Mr.SpreadSheet

Views: 5,454

Rating: 4.9661016 out of 5

Keywords: Inventory Management, Inventory Control, Inventory in Excel, Inventory Management Spreadsheet, How to keep Inventory, Inventory Spreadsheet, Inventory Tutorial, Stock Control in Excel, Stock Management System, Stock Management in Excel, Stock Management Spreadsheet

Id: sMCvHbGt4cE

Length: 26min 45sec (1605 seconds)

Published: Tue Jul 06 2021