How to create an Inventory Management System [using Excel] in 2021
If you would like to create your own Inventory Management system using Excel spreadsheets then this short video tutorial will show you how.
The Inventory Management spreadsheet will use many of Excels functions and commands, including:
Use of Drop Down boxes to select data
Use of the SUMIF command
IF Statements to isolate and select data
The VLOOKUP command
We also introduce the CONCATENATE and MID commands
The Inventory Management spreadsheet will record Inventory transactions and will monitor Inventory Levels, Inventory Valuations and Product Line profitability.
As usual I will show you how to obtain a copy of the completed template at the end of the video.
If you need any help with the commands or functions I have used, then please leave me a comment.
I hope you enjoy watching.
Here are the links to the various Mr.SpreadSheet resources available to you.
Please help support our channel by subscribing.
Many thanks
Free Inventory Management Spreadsheet Template - www.MrSpreadsh...
You can also visit our websites at:
USA & Canada & World - www.MrSpreadSh...
UK & Europe - www.MrSpreadSh...
Or our Social channels:
FaceBook - / mrspreadsheets
Instagram - / mrspreadsheet
Twitter - / mr_spreadsheet
#MrSpreadSheet #Excel #spreadsheet #exceltutorial #msexcel #Accounting
Пікірлер: 124
Thanks for the video. It is very informative. I followed along and was able to create my very own inventory table! Many thanks
@MrSpreadSheet
2 жыл бұрын
Fantastic, well done :-)
I love your lessons Cuz they support me a lott various jobs and projects
@MrSpreadSheet
Жыл бұрын
Thanks Abdi 😊
Thanks for sharing. I created my own Inventory Management on Excel after watching this video. Please share Payroll Account in Excel.
@MrSpreadSheet
Жыл бұрын
Please follow the ‘Download’ guide in this link 😊 kzread.info/dash/bejne/n2uOyJqYhbq2d7Q.html
good job!!!! leaned a lot. Thanks
@MrSpreadSheet
3 жыл бұрын
Glad it helped!
I wish you were my teacher ☺
@MrSpreadSheet
2 жыл бұрын
Thanks :-)
❤❤
@MrSpreadSheet
18 күн бұрын
Thanks :-)
very nice thanks
@MrSpreadSheet
3 жыл бұрын
Thank You :-)
thank you!!!!
@MrSpreadSheet
2 жыл бұрын
You're welcome!
Excellent work, I appreciate. My sent you a FB message.
@MrSpreadSheet
2 жыл бұрын
Thanks DeOntae :-)
Use iferror because if will run everytime, iferror only when an error. Sheet performance.
@MrSpreadSheet
2 жыл бұрын
Thanks for your insights Lethal :-)
Great Video. Subbed and emailed you my contact info to hopefully receive a copy of the completed sheet! Cheers!
@MrSpreadSheet
3 жыл бұрын
Thanks Roy, I'll look out for your email :-)
Love the video. thanks would love a copy of this spreadsheet
@MrSpreadSheet
2 жыл бұрын
Hi Jude, Please see my earlier comments :-)
It would be great if we had a list of commands/functions used in the tutorial in the description to look at after the video. I understand you get more views everytime I have to come here and watch the video over for one function but it is a bit disheartening to have to do everytime.
@MrSpreadSheet
2 жыл бұрын
Hi Darlene, thanks for your insights :-)
I love your all videos . However, my single product is combine of multiple item, various codes. Could you please make video how to code that! Thanks
@MrSpreadSheet
3 жыл бұрын
Hi Zander, Yes, a Bill of Materials spreadsheet is planned. :-)
hi Mr. s how are you, thank you for your giving us the best knowledge I am from Pakistan, and I have a problem, there are a lot of videos for stock inventory, but I never find Stock inventory for manufacturing units, From raw materials to finish Goods, For example, I have 3 employees I am giving them raw material and they are giving me finish product how can we deduct raw material by finish goods when they give it back, and how to maintain my stock inventory left with my employee and in my warehouse hope you will resolve my issue i am much needed this time
@MrSpreadSheet
Жыл бұрын
Hi Mirza, A 'Bill of Materials' video is on out 'To do' list. Hopefully this will address your needs. :-)
Thanks for the informative video! Why is profit calculated that way?
@MrSpreadSheet
2 жыл бұрын
Hi Kendra, Good Question, there are three basic ways that profit can be calculated. They are based on different stock COST methods. FIFO, LIFO and Average Cost. No one method is correct. However, once you apply one method, then you should stick to it. Kind Regards :-)
great video. i have made the excel while following this video. it works well i just need to add a stock amendment type to it and not sure how to get it all talking any pointer?
@MrSpreadSheet
Жыл бұрын
Hi Gaz, You can check your work against the original. See the download guide video kzread.info/dash/bejne/n2uOyJqYhbq2d7Q.html 😊
@mr.spreadsheet I'm for some reason not able to get the formula correct for the purchase & sales column. For sales it's not showing the - minus sign
@MrSpreadSheet
2 жыл бұрын
Hi Shanique, are you entering Sales movements as a negative ?
@macleodfamilyfarm
Жыл бұрын
I'm having the same issue as well. We're you able to figure it out? I'm searching all over and can't figure it out. Edit: I see what's going on. The quantity needs to be entered in as a negative as it is being removed not added. Once you enter in your sale quantity as a negative the Transaction Value will show up as a negative. 😀
Hi, Thanks for teaching me the Inventory System on Excel but can you please share the video for same inventory system Excel. I want to create every think in different sheets, so want to know how the formula changes
@MrSpreadSheet
Жыл бұрын
Hi Jigae, You can check your work against the original. See the download guide video kzread.info/dash/bejne/n2uOyJqYhbq2d7Q.html 😊
Thanks for this tutorial. I have a question, though not specifically about this video. I have a list of over 1300 products. The product names have 3 to 6 words, and some having sizes (e.g 3 Strap Italian Loafer Grey 44; Double Breasted Slimfit Suit Black). I want to generate SKU Codes for these products using the first letter in each word that forms the product name and the size (if any). IS THERE A WAY I CAN EXTRACT THESE FIRST LETTER OF RACH WORD AND THE SIZE (E.G 44 ABOVE) WITHOUT HAVING TO DO IT MANUALLY??
@MrSpreadSheet
Жыл бұрын
Hi, sounds like you need a combination of TEXT formulas. I can't think of any single command that would work in isolation. :-)
Great work sir. i would like to get a copy of the file
@MrSpreadSheet
2 жыл бұрын
Hi Kalu, please refer to the guide at the end of the presentation :-)
Hi very informative video very helpful I have sent you a message on Facebook to request a copy of the template thank you 👍
@MrSpreadSheet
2 жыл бұрын
Hi Tracy, please see your FB Messages :-)
Hi, I saw your video! Very informative and well explained 👍🏽 but I am trying to update the purchase/sales section with nee items but it is not showing up in the inventory manangement!
@MrSpreadSheet
2 жыл бұрын
Hi Marie, take a look at the completed template 😊 kzread.info/dash/bejne/n2uOyJqYhbq2d7Q.html
@Skysky67567
Жыл бұрын
@@MrSpreadSheetThe vedio is private
looks good but you are not taking into consideration the beginning stock ?!
@MrSpreadSheet
Жыл бұрын
Hi Mohammed, your opening stock would be recorded as a purchase. You need qty and value and this does it for you. 😊
Seem to be missing a pick sheet to advise warehouse which items to pick from inventory for shipping to customer. How the warehouse is advised which goods to pick to match a customer's order under this system.
@MrSpreadSheet
Жыл бұрын
Hi Mark, Good call :-)
Hey there great video once again!! Wondering if I could receive spreadsheet. Will provide you with email
@MrSpreadSheet
2 жыл бұрын
Thanks Jeremy, I'm glad you liked the presentation :-)
I miss how you got the inventory copy into the Purchases and Sales Transaction Can you explain. Enjoying the video so far up to that point
@MrSpreadSheet
2 жыл бұрын
Hi Pam, message me on FB and I will forward a copy of the file :-)
How can i do Searchable dropdown list with Xlookup to return value
@MrSpreadSheet
5 ай бұрын
Hi, You can check your work against the original template. A download is available at www.MrSpreadSheet.com :-)
Love this! Could I get a copy of the spreadsheet.
@MrSpreadSheet
2 жыл бұрын
Hi Timothy, The file you have requested can be obtained by following the guide in this video kzread.info/dash/bejne/n2uOyJqYhbq2d7Q.html 😊
Is this for like 1 set time period? How do you record daily sales and purchases so that it updates inventory.
@MrSpreadSheet
8 ай бұрын
Hi, You can set whatever time period you want, just add a date range to the Purchases and Sales Table. :-)
For some unknown reason I'm not getting the the code and description (Products) to populate in the inventory purchase section (C19), Please someone help.
@MrSpreadSheet
2 жыл бұрын
Hi Wayne, message me on FB and I will forward a copy of the completed template :-)
How can I get help? I made every formula exact and nothing will populate in the “sales quantity” “sales value” and “profit” columns.
@MrSpreadSheet
11 ай бұрын
Hi, Why don't you download a copy and then check your work against the original. Go to www.MrSpreadSheet.com :-)
@user-dh8ow6nd1b
11 ай бұрын
@@MrSpreadSheet hi thank you for the download. I have all of my formulas exactly as you do yet still nothing populates in those 3 fields??!!
Hello @Mr.SpreadSheet. I have a lot more inventory information that I would like to input to into the tables. Is there a way I can add more rows? Also, I would love if I could categorize my inventory. Any suggestions??? Thank you so much for your time and help. It is greatly appreciated.
@MrSpreadSheet
Жыл бұрын
Hi, To add more rows you need to expand the various tables and copy in the correct formulae and expand the named ranges. Categories should not be a problem, you will need to set up a table with these and then incorporate this into the main table. :)
I have a question!?! From 7:30 to 7:46. I have tried to put in the formula but it doesn't work. Is there another way?
@MrSpreadSheet
2 жыл бұрын
Hi, You can check your work against the original. See the download guide video kzread.info/dash/bejne/n2uOyJqYhbq2d7Q.html 😊
I do not have the formula defined bar because im using excell online. I was stuck when i was doing the formula because the product was not defined prior to the the next step. Please advise is there another way instead of defining product
@MrSpreadSheet
3 жыл бұрын
Hi Sandra, the on line versions of Excel are 'lightweight' in their functionality. I suggest that you use the full PC versions. :-)
I need to add in different values for the sale value so my profits come up correctly can you help ?
@MrSpreadSheet
2 жыл бұрын
Hi James, You can check your work against the original. See the download guide video kzread.info/dash/bejne/n2uOyJqYhbq2d7Q.html 😊
I work for a shelter and have been asked to to create a donation tracking SS. It doesn't need to have values just item and quantity on hand as well as item and quantity being donated to guest. It need to be able to do a running daily total in and out as well as a warning of some kind when items get to a specific quantity level so we know what is needed from our donors. I did what I knew how to do with excel which was created a separate sheet for each bin and items in bins as well as in and out, after 108 sheets were created I realized that I couldn't do a running daily total. So many videos later and still no resolution. I found your videos and they are great, so is it possible to use the sheets I've already created and have each sheet do a running in and out total or do I have to start all over from scratch? Please help, too many wasted hours. Thank you in advance.
@MrSpreadSheet
2 жыл бұрын
Hi Dee, I have just sent you the link, sounds like quite a job. I hope the file helps you. :-)
@07chicobing
2 жыл бұрын
Thank you. I don't see where the link is. Email?
Hi i would like to ask what is the purpose of lead time?
@MrSpreadSheet
Жыл бұрын
Hi Kenneth, Lead time is the length of time it takes for you to place an order and receive the goods. This is important because let's say your stock level is 10, and you sell 5 a week. So you have 2 weeks worth of stock. If it takes 3 weeks lead time, then in the third week you will have no stock to sell as the new stock has yet to arrive. Not good planning ! 😊
Are major revisions required for those of us that us Numbers?
@MrSpreadSheet
2 жыл бұрын
Hi, unfortunately I have no experience of Numbers, so I cannot reply.
Please I need a help in warehouse inventory management
@MrSpreadSheet
2 жыл бұрын
Hi Nnenna, You can check your work against the original. See the download guide video kzread.info/dash/bejne/n2uOyJqYhbq2d7Q.html 😊
Why is it important to use macro
@MrSpreadSheet
Жыл бұрын
Hi Afah, there are no macros in this presentation.
Hi the video was very interesting. I like it. I would like a copy of the template. Thanks.I have send a message in Facebook.
@MrSpreadSheet
2 жыл бұрын
Thanks Julio :-)
Hi @MrSpreadsheet, I really hope this makes sense to you because Im at a loss. For some reason the SaleQty and Sale Value numbers are transferring in a negative value from the "Purchases and Sales" schedule to the "Inventory Management" spreadsheet. It's causing the InventoryQty and InventoryValue to be incorrect and is showing I have more product than what I really have. For example: I bought one plant for $20 and I sold that plant for $20. I now have zero plants. 1-1=0 and 20-20=0. However the spreadsheet shows that I have 2 plants (1- -1=2) and $40 in Inventory Value (20- -20=40) . I see on your spreadsheet the sold prices are not transferring over in a negative format like mine is. How can I fix this?
@MrSpreadSheet
Жыл бұрын
Hi, You can check your work against the original. See the download guide video kzread.info/dash/bejne/n2uOyJqYhbq2d7Q.html 😊
@macleodfamilyfarm
Жыл бұрын
@@MrSpreadSheet I figured it out. I missed puting the minus sign in front of the SUMIF formula. All is well now. Thank you so much!
Do I need to have a inventory sheet and a inventory management or just one of them?
@MrSpreadSheet
Жыл бұрын
Hi Milena, Please see our ‘Free Mr. SpreadSheet templates’ video 😊 kzread.info/dash/bejne/n2uOyJqYhbq2d7Q.html
@milenaserrato3359
Жыл бұрын
@@MrSpreadSheet Thanks for the reply, but what I meant if in order to do my inventory that has a lot of items , do I need to do both spread sheets inventory list and inventory management ? I don't need the templates. Also how I fix a #div error? thanks
I want a copy of the spreadsheet please.
@MrSpreadSheet
Ай бұрын
Pls see my previous replies :-)
I would like a copy of your template
@MrSpreadSheet
Жыл бұрын
Hi channel21060, Please follow the ‘Download’ guide in this link 😊 kzread.info/dash/bejne/n2uOyJqYhbq2d7Q.html
Great work indeed. This is quite enlightening. My email was sent to your Facebook message.
@MrSpreadSheet
3 жыл бұрын
Tanks Mwango, I'll check FB :-)
hi, how can I download the template in MS Excel Format.
@MrSpreadSheet
2 жыл бұрын
Hi Bawani, Please follow the ‘Download’ guide in this link 😊 kzread.info/dash/bejne/n2uOyJqYhbq2d7Q.html
Really stuck on the formula at 12:33 minutes into the video, it keeps coming up as #N/A even though the formula is the same. I have repeated the process 3 times now and it’s always the same result 😅
@MrSpreadSheet
2 жыл бұрын
Hi Charly, take a look at the original and compare. Please follow the ‘Download’ guide in this link 😊 kzread.info/dash/bejne/n2uOyJqYhbq2d7Q.html
kindly share the template
@MrSpreadSheet
Жыл бұрын
Hi, Downloads are available at www.MrSpreadSheet.com :-)
Why H20 is showing #VALUE? and not 0
@MrSpreadSheet
2 жыл бұрын
Hi Gian -Ro, why not download the completed template and check your formulae against the original 😊 kzread.info/dash/bejne/n2uOyJqYhbq2d7Q.html
Please sent video
@MrSpreadSheet
3 жыл бұрын
Hi, please follow the guide at the end of the video :-)
please share this template with me
@MrSpreadSheet
8 ай бұрын
Hi, Downloads are available at www.MrSpreadSheet.com
Hi, you make excellent videos and are very good at explaining things. But this was a big waste of time because you didn’t take the time to explain how to add more data or use the table in any meaningful way. I have tried to watch several other videos to figure it out but was unable to make it work. 😢
@MrSpreadSheet
6 ай бұрын
Hi, The intention is to teach/guide and let you decide on how to apply your unique requirements :-)
great video . I sent you email via Fb message hope to receive a templet
@MrSpreadSheet
3 жыл бұрын
Thanks Jolanta, I'll look out for your message :-)
Is there any other way to contact you for a copy of the file? Can I DM you through Instagram for it? Awaiting reply, Thanks.
@MrSpreadSheet
3 жыл бұрын
Hi Cham, message me on FB :-)
Can you send me this template..i've sent you a message via Facebook
@MrSpreadSheet
2 жыл бұрын
No problems :-