Collaborate on Simple Receipt Template Excel for Inventory with Ease Using airSlate SignNow

Simple receipt template excel for Inventory

Track inventory stock in and stock out  automatically on your inventory list in   Excel. To automate stock tracking, we're going to  build on the basic inventory list that we created   in another video. We're going to create a  stock transactions tracker that ties back   to your master inventory list. Then, we're  also going to include a variance tracker so   that you can take a physical count of your  inventory. This way, you'll have a better,   more comprehensive inventory  management system in Excel. If you want to get a jump start, you can  purchase this template. I'll include a link   in the description below, or you can  visit my website, SharonSmithHR.com. I hope you like this video. Let's get started! Here we have our Master Inventory List  that we created in our other video,   which was called "Create and Track a Basic  Inventory List in Excel." What we're going   to do is focus on this quantity in stock column.  So, I'm going to go ahead and highlight this a   different color. Instead of having a static  amount that we update here in this column,   we're going to create a Stock Tracker sheet  in our workbook that's going to track the   stock in and stock out transactions, and will  dynamically update this column automatically. Let's go down. I'm going to shorten the name  of my tab down here. I'm going to double-click   and rename this "Master Inventory," and  I want to shorten this so that it'll be   easier to reference in formulas. Now, let's  go ahead and create our new sheet. I'm going   to click the plus button, and we're going  to create a sheet called "Stock Tracker." Now, to save time for this video, I'm  going to go ahead and prepopulate this tab,   and then I'll walk you through all of the  headers. Okay, I have some sample data here,   but I want to walk you through what you're  going to want to put on your Stock Tracker   tab. We want to have a column for transaction  ID, and then we want to have the inventory ID.   This is going to exactly match the inventory ID  that we have on our Master Inventory List. We're   going to have a transaction date and then a  transaction type. This column will be where   we track stock in or stock out. Then, we're  going to have the quantity and a notes column. Once you have this populated with some data, I'm  going to go ahead and click anywhere in that table   and hit Control + T as a shortcut. This is going  to select the entire range of data and turn this   into a table. So, I'm going to click OK, and  the reason we want to have this formatted as   a table is so that when we add new rows here,  it will automatically be included in the table. Now, one thing we want to do is control what  goes into the transaction type field. We want   it to either be "Stock In" or "Stock Out." So,  to do that, we're going to create a drop-down   list selection, and to do that in Excel, we  create what's called a data validation. Now,   on this particular workbook, this template  already has a tab that's hidden that has   another drop-down list that we created on the  Master Inventory List for a Yes/No dropdown. So,   I'm going to come down to any of my tabs. I'm  going to right-click and select "Unhide." I   can see that "Data Validation List" tab  that I had hidden. I'm going to select it   and click OK. Now, that unhides this tab  where I had my Yes/No dropdown selection. Now, I'm going to click anywhere—I can input  this anywhere on this sheet that I want—but I'm   going to type "Stock In" and "Stock Out"  for that drop-down list selection. Now,   I'm going to come back to my Stock  Tracker tab, select the first cell   under "Transaction Type," come up to my  Data tab, and in the Data Tools area,   drop down your data validation. We're going  to go ahead and click on the "Settings" tab   under "Allow," drop down and select "List."  Then, for your source, put your cursor there,   navigate back to your Data Validation List tab,  and select your "Stock In" and "Stock Out." If   you want to put an input message or error alert,  you can. Then, when you're done, just click OK. Now, you have a drop-down list  selection for Stock In and Stock Out,   and you can hit Control + C to copy that  and paste it all the way down in your table. Now that we have our table set up with  our Stock In and Stock Out transactions,   let's set up a pivot table. We're going  to click anywhere inside of the table,   go up to Insert, drop down "Pivot Table,"  and select "From Table or Range." It'll   automatically select your table for you.  We can put this in the existing workbook,   and then in the location, just select a cell  where we want to place it and then click OK. Now, over in the Pivot Table Fields area,  let's click on the inventory transaction   date and drag that down to rows. Let's drag  inventory ID down under rows as well. Then,   we're going to take the transaction type,  drag that to columns, and then we're going to   take the quantity and drag that under values.  That's going to put the sum of the quantity. Now, while our pivot table is selected, let's  come up to the Design tab. We're going to turn   off the subtotals. Go to "Subtotals," drop down  and say "Do Not Show Subtotals." Same thing with   grand totals—drop down and say "Off for Rows and  Columns." Under "Report Layout," drop down and   click on "Show in Tabular Form." That's going to  put the inventory ID in another column for us. It   makes it easier to visualize. Now, I'm going  to close out of the Pivot Table Fields area,   and I'm going to go back to the Pivot Table  Analyze tab and I'm going to insert a slicer.   This slicer is going to be for the inventory  ID. We're going to check that box and click OK. Now, this is a filter that will let us  select any of our inventory ID items if   we want to look at one item at a time. So,  for item number one, we can select that. If   we want to look at multiple items, we can hit  Control and select multiple items. When we want   to clear that filter and see everything,  all we have to do is hit "Clear Filter." Now, we can also add a timeline. Select  anywhere inside your pivot table, go back   up to Pivot Table Analyze, and then go ahead and  click on "Insert Timeline." We're going to select   the transaction date and click OK. We can drag  this over, and now we can select any period of   time that we want to look at our data. So, if  we want to just see for the month of August,   we can select that. We can drag and select  different date ranges. You can even drop down   and select days, quarters, or years after you have  more stock transaction data to take a look at. Now that we have our pivot table set up,  let's go back to our Master Inventory List   and let's see how we can set up the quantity  in stock to dynamically update based on all   of the Stock In and Stock Out transactions  that we record on the Stock Tracker tab. So,   I'm going to go ahead and delete all of  these numbers in my column. They were   just static numbers that we created for  data to have on our inventory list. Now,   what I'm going to do is put a formula in  this cell. I'm going to paste the formula   in—I'll include it in the description below—and  we'll take a look at what this formula is doing. This formula is using SUMIF, and what it's doing  is it's taking the inventory ID number from cell   A4, and it's looking for that inventory ID number  over on the Stock Tracker. Then, it's also looking   for anything that says "Stock In" related to  that inventory ID number. It's going to add or   sum up all of those Stock In transactions,  and then it's going to subtract all of the   Stock Out transactions for that same inventory  ID number. So, you'll have cumulative Stock In   and cumulative Stock Out that gets subtracted  to give you your actual quantity in stock. So,   I'm going to go ahead and navigate to the end  of this formula and hit Enter, and my chart   automatically flash fills that formula all the way  down. If yours did not flash fill, all you have   to do is copy that formula and paste it down so  that the whole column will have that information. Now, let's take a look at inventory item  number one. We have a quantity in stock of 7.   Let's go back to our Stock Tracker tab and take  a look. Let's go over to our slicer and select   inventory ID number one. Notice we have an initial  stock in of 10. Subtract eight gives us two,   and then add back 15 will give us 17. So, back  on our Master Inventory List, that value is 17. On our Stock Tracker, let's say that we have some  stock out on that item. We're going to go ahead,   select our last row, drag it down so it will  automatically update a new transaction ID   number for us. We're going to say inventory item  number one, and we're going to put today's date.   A shortcut for today's date in Excel is to  hit Control + Semicolon. Then, we're going   to call this "Stock Out," and we're going to put  three, and we're going to put "Sold to Customer." Now, let's go back to our Master Inventory,  and we can now see that our quantity in   stock for that item is 14. If we want  to go ahead on the Stock Tracker tab,   scroll back up to our table for our pivot  table, we're going to go to Pivot Table   Analyze and click on Refresh, and that  will automatically refresh the pivot   table with all of the new information  that you've added on your Stock Tracker. Now that we have our Stock Tracker  tab that will automatically update   the Master Inventory List with all of  our Stock In and Stock Out transactions,   let's go ahead and create a tab for  a variance tracker so that we can do   a physical inventory count and check for  any discrepancies. I'm going to go ahead   and click plus to add a new tab, and we'll  call this "Variance Tracker." To save time,   I'm going to go ahead and automatically populate  my headers, and we'll come back and fill this in. Now, on the Variance Tracking tab, let's go  ahead and copy down all of our inventory items   from our Master Inventory List. Starting with  item number one all the way through item 20,   I'm going to copy those over to the  Variance Tracker. Now, for date,   let's say that we do our physical inventory  count today. So, I'm going to put my shortcut   for today's date—we're going to hit Control  + Semicolon and hit Enter. I'm going to hit   Control + C to copy that date, and we're  going to copy that all the way down. Now, under "Expected Quantity," this  is where we want this value to equal   whatever the quantity in stock is on the Master  Inventory tab. So, we're going to type equals,   navigate to Master Inventory, select our quantity  in stock for item number one, and hit Enter. Then,   we're going to select that cell and double-click  to copy that formula down. Now, our expected   quantity will always equal what is the actual  quantity in stock on the Master Inventory List. Now, for the variance formula, we're going  to type equals, and then we're going to take   the actual quantity minus the expected quantity  and hit Enter. Then, we're going to also select   that cell and double-click on the bottom  right corner to copy that down. Now,   we want to turn this into a table. We'll select  anywhere inside of our data and hit Control + T,   and then click OK. This will  turn our data into a table. Now, I'm going to go ahead and put another  header at the top here. I'm going to merge   and center my cells, and I'm going to call  it "Physical Inventory Audit Count." Now,   I'm going to format this just a little  bit. We'll make it a little bigger,   increase the font size, make it bold, center  align it, and make it a different fill color. Now, I can go and actually take a physical  count of my inventory. So, I'm going to put   that value in the actual count. Let's just say  that I only saw 13 of this first item. I did   see 45 of the second, and let's say I saw 55 of  the next item. Notice that our variance tracker   will automatically show us what's happening  for the variance. So, if we're missing an item,   it'll show up as a negative number, and we can  put a note that this item might be missing,   and we might need to go research that. If  it's just an inventory check, then it should   be zero. If we have extra items, it might be a  returned item. Now, you'll have a way to track   your physical inventory and notice if there are  any discrepancies that you need to research. Now that we have Stock Tracker and Variance  Tracker on our Excel Master Inventory List,   the next thing we want to do is start to  analyze our data for trends or patterns   that we see on a daily, weekly, or monthly  basis. Be sure to check out my next video.   You'll see how to analyze your data and create  dashboards so you can start to see those trends. Be sure and share this video with your friends!  You can give it a like and subscribe to my channel   for more videos like this. You can visit my  website, SharonSmithHR.com, for more resources. Thanks so much for watching,  and I'll see you next time!