How to keep Inventory using an Excel Spreadsheet - [create your own Template]

hello and welcome to the mr spreadsheet youtube channel if your business needs to keep track of inventory then why not create your own inventory listing and inventory movement schedule using an excel spreadsheet in this short video i will show you how to do both of these things and provide you with an easy to use template that you can adapt and expand to suit your business needs as usual i will show you how to get a copy of the final template later on in this video and if you need any help on the excel functions i've used then please do leave a comment below i hope you enjoy watching open up a new workbook and we will create the inventory list the first of our two tables in cell b2 type in inventory list in capitals in cell c2 type in code in c3 it's product in d3 its cost in e3 its quantity and finally in f3 type in value now select the range b2 through to f2 and from the home ribbon select and click the merge and center tool choose a background color of green a font color of white make bold and increase the font size and now select the range b3 through to f3 make bold and change the background color to light green we will now add a border to the inventory list table so with the range b2 through to f19 highlighted select the all borders option from the borders tool on the home ribbon now let's enter in our first product with a code of w101 a description of widgets gray and a cost of 21.50 you can now enter the next 12 products or indeed you can choose values more suited to your business needs please pause the video whilst you complete this routine our second table is the schedule of inventory movements now for convenience we're going to place this alongside the inventory list table so select cell h2 and type in inventory in and out schedule in cells h3 through to n3 type in the inventory schedule headings of date type code product cost quantity and finally value select the range h2 through to n2 and merge and center the contents change the background color to green the font color to white make bold and increase the font size select the range h3 through to n3 and change the background color to light green and make bold now select the range h2 to n19 and apply the all borders tool and finally from the page layout ribbon uncheck the view gridlines box okay so we've now set up our basic layout now in the inventory movements table we actually need to define the type of movement we want valid entries to be either sales or receipts or adjustments so let's type these values into cell r3 through to r5 r3 becomes sale r4 becomes receipt and r5 becomes adjustment and with these three cells highlighted select the formulas ribbon and choose the define name tool in the dialog box that opens enter the word type in the name field and then click ok now we can enter a drop down box in cell i4 so with i4 selected choose the data ribbon and then the data validation tool in the allow box choose list and in the source field enter equals type and then click ok a drop down box will now appear to the right of cell i4 from which you can select one of the three options from the type list now with i4 still active drag and copy this formula down to the last cell in the column such that all the rows for the type column contain the type list drop down box now we will create a similar drop down box routine to select the code in cell j4 the list we need to create is actually the range b4 to b19 in the inventory list table so highlight this range and from the formulas ribbon choose the define name tool and change the name field to read code and then click ok navigate back to j4 and from the data ribbon select once again the data validation tool choose list for the allow field and enter equals code in the source field click ok to save and close the data validation tool as before a drop down box appears to the right of the cell j4 inviting you to select from the code list with j4 still active copy and drag down the formula to j19 at the end of the code column now that we can select a valid code we need to enter the product name and the cost into cells k4 and l4 but before we do that let's quickly populate the first three fields of line one of our inventory movement schedule enter a date in h4 select receipt from the drop down box in cell i4 and select code w101 from j4 now we can easily use excel's vlookup function to automate this routine but first we need to name the range b4 to d19 which will make our use of the vlookup function much simpler so with b4 to b19 highlighted select the define name tool from the formulas ribbon change the name field to read details and then click ok navigate back to k4 and enter in the following formula equal vlookup open brackets point to cell j4 enter a comma then enter the lookup range name of details then another comma then enter 2 then a comma then the word false and now close the brackets the 2 in this formula represents column 2 of the details range you'll now find that cell 4 populates correctly with the value in the second column of the details range we can use the same vlookup routine to populate cell l4 our formula simply becomes equals vlookup open brackets point to j4 then a comma then details then another comma then three then comma then false and close the brackets the three actually equates to the third column in our details range and you'll find that l4 correctly populates with the value of 21.50 let's complete this first line by entering a quantity of 15 in cell m4 the value in n4 is simply the quantity in m4 multiply by the cost in l4 so enter the formula plus m4 times l4 to achieve this and that completes the first line but wait if we delete the code for this line then excel will return an error message in k4 l4 and n4 this is simply because our formulas in these cells are totally dependent on there being a correct code in the field j4 let's demonstrate this another way select the cell i4 and copy and drag down this formula to the end of column k19 the error message appears in each row where there is not a valid code we want to slightly adjust our vlookup command such that if there was no value in the code field in column j then the formula result should simply be a blank entry select k4 again the formula is shown in the function command section and we're going to add an if statement to the command so after the equal sign and before the v of vlookup enter in the following if open brackets point and click on j4 equals inverted comma inverted comma comma then inverted comma and inverted comma again followed by a comma and then enter a final bracket at the end of the formula once the formula is complete press enter and now if you delete the code value in cell j4 you do not get the na error message now copy the formula all the way down to k19 we can insert this same if command to tidy up the cost column and the value column with cell l4 selected locate your cursor after the equal sign in the command line and before the v a vlookup and enter if open brackets point to cell j4 equals inverted comma inverted comma comma inverted comma inverted comma and comma again and then finally navigate to the end of the formula and enter in a closing bracket once you press return you can copy and drag down the formula to l19 the formula in n4 now becomes equals if open brackets j4 equals inverted comma inverted comma followed by a comma then inverted comma and inverted comma again followed by a comma and then m4 multiplied by l4 and close the brackets once again copy and drag this formula down to the end of the column okay that's most of the work done in the inventory movement schedule let's add a couple of totals for quantity and value and change the number formatting select the range l4 to l19 and by holding down the control key on your keyboard select also the range n4 to n19 with both of these ranges highlighted right click your mouse and select the format cells tool choose the number category and set to two decimal places and set negative numbers to be in red preceded by a minus sign do the same for the quantity fields except set the decimals to zero navigate to m4 and enter in the formula equals subtotal open brackets 9 comma and select the range m4 through to m19 and close the brackets do the same in n4 to get the value totals so it's equals subtotal open brackets 9 comma n4 through to n19 and close the brackets now click anywhere in the inventory movements table and from the home ribbon select the sort and filter tool and choose the filter option now change the background color of the two total fields to a mid-gray and format the numbers as before let's quickly enter a few lines of data to test out the inventory movements table select cell h5 and enter a date select receipt and then w106 in the code field our product and cost fields populate correctly navigate across to m5 and enter 10 as the value the whole line works properly populate the next two lines as follows making sure that any sale item is recorded as a negative quantity let's now return to the inventory list and enter a formula to automatically calculate the running or the cumulative totals for each of our products together with a valuation for each product line to do this we'll use the sumif command which will add up both the positive and the negative values for each product based on the movements in the inventory movements schedule to help us with the sum if command it is advisable to name the ranges that the sumif command uses select the range j4 to j19 and from the formulas ribbon select the define name tool and in the name box enter in out code with no spaces and then click ok now select the range m4 to m19 and using the define name command again enter the name in out quantity again with no spaces and click ok our sum if command in cell e4 will call on both of these ranges to calculate the cumulative or the running balances for the first product so in cell e4 enter in the formula equals sum if open brackets in out code comma 0.2 b4 comma and then in out quantity and close the brackets e4 populates correctly with 35 which is the cumulative total for that product the inventory value for w101 is simply the quantity times the cost so enter the formula plus d4 times e4 in cell f4 now we can copy these two formulas down to the end of the inventory list so highlight both cells and drag and copy down to complete this let's enter a total in cell f20 to give us a complete inventory valuation select f20 and simply double click the auto sum tool on the home ribbon now from the borders tool select all borders and make bold and change the background color to mid gray select the ranges f4 and f20 together with d4 and d19 and from the format tool set the number to two decimal places with the 1000 comma separator checked and last but not least we will apply some conditional formatting to the range e4 to f19 such that zero values are not displayed so select the range e4 to f19 and from the home ribbon choose the conditional formatting tool select the highlight cell rules option and then the equal to option enter a zero in the value field and then from the with field select custom format from the drop down box and from the font color palette choose white and then click ok please fully test all of the transaction rows and the filtering routines to ensure that all the formulas are functioning correctly it is most likely that you will need many more transaction lines than shown but by using the techniques displayed you can easily expand the data tables and that completes our design work we now have a fully functioning inventory control spreadsheet that will hopefully be of value to you and your business we do hope that you enjoyed our spreadsheet video 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 this spreadsheet file then please subscribe to our channel and give this video a big thumbs up alternatively please visit one of our channels on facebook instagram or twitter now if you are a small business and want to keep your bookkeeping records in excel then why not watch our accounting spreadsheet tutorial alternatively why not view our how to keep your account in excel video this is our easy to use and inexpensive solution for your small business bookkeeping needs thank you once again for watching our spreadsheet video and oh yes please do subscribe to our channel

Channel: Mr.SpreadSheet

Views: 166,689

Rating: 4.9291897 out of 5

Keywords: Inventory in Excel, inventory management, inventory in excel, Inventory Spreadsheet, stock control system excel, stock control spreadsheet, inventory management in Excel

Id: msQoTuCsKJ4

Length: 21min 26sec (1286 seconds)

Published: Sun Nov 22 2020