How to create an Inventory Management System [using Excel] in 2021
Vložit
- čas přidán 5. 09. 2024
- 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
![How to keep Inventory using an Excel Spreadsheet - [create your own Template]](http://i.ytimg.com/vi/msQoTuCsKJ4/mqdefault.jpg)
![How to keep Inventory using an Excel Spreadsheet - [create your own Template]](/img/tr.png)
Thanks for sharing. I created my own Inventory Management on Excel after watching this video. Please share Payroll Account in Excel.
Please follow the ‘Download’ guide in this link 😊 czcams.com/video/m7Zf8_UWPGQ/video.html
I love your lessons
Cuz they support me a lott various jobs and projects
Thanks Abdi 😊
Thanks for the video. It is very informative. I followed along and was able to create my very own inventory table! Many thanks
Fantastic, well done :-)
@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
Hi Shanique, are you entering Sales movements as a negative ?
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 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
Hi Mirza, A 'Bill of Materials' video is on out 'To do' list. Hopefully this will address your needs. :-)
I wish you were my teacher ☺
Thanks :-)
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?
Hi Gaz, You can check your work against the original. See the download guide video czcams.com/video/m7Zf8_UWPGQ/video.html 😊
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
Hi Zander, Yes, a Bill of Materials spreadsheet is planned. :-)
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
Hi Jigae, You can check your work against the original. See the download guide video czcams.com/video/m7Zf8_UWPGQ/video.html 😊
good job!!!! leaned a lot. Thanks
Glad it helped!
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.
Hi Mark, Good call :-)
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??
Hi, sounds like you need a combination of TEXT formulas. I can't think of any single command that would work in isolation. :-)
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!
Hi Marie, take a look at the completed template 😊 czcams.com/video/m7Zf8_UWPGQ/video.html
@@MrSpreadSheetThe vedio is private
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.
Hi Darlene, thanks for your insights :-)
Use iferror because if will run everytime, iferror only when an error. Sheet performance.
Thanks for your insights Lethal :-)
Thanks for the informative video! Why is profit calculated that way?
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 :-)
Is this for like 1 set time period? How do you record daily sales and purchases so that it updates inventory.
Hi, You can set whatever time period you want, just add a date range to the Purchases and Sales Table. :-)
looks good but you are not taking into consideration the beginning stock ?!
Hi Mohammed, your opening stock would be recorded as a purchase. You need qty and value and this does it for you. 😊
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
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
Hi, You can check your work against the original template. A download is available at www.MrSpreadSheet.com :-)
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.
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. :)
How can I get help? I made every formula exact and nothing will populate in the “sales quantity” “sales value” and “profit” columns.
Hi, Why don't you download a copy and then check your work against the original. Go to www.MrSpreadSheet.com :-)
@@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??!!
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?
Hi, You can check your work against the original. See the download guide video czcams.com/video/m7Zf8_UWPGQ/video.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
Hi Sandra, the on line versions of Excel are 'lightweight' in their functionality. I suggest that you use the full PC versions. :-)
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.
Hi Dee, I have just sent you the link, sounds like quite a job. I hope the file helps you. :-)
Thank you. I don't see where the link is. Email?
very nice thanks
Thank You :-)
❤❤
Thanks :-)
thank you!!!!
You're welcome!
I need to add in different values for the sale value so my profits come up correctly can you help ?
Hi James, You can check your work against the original. See the download guide video czcams.com/video/m7Zf8_UWPGQ/video.html 😊
Great Video. Subbed and emailed you my contact info to hopefully receive a copy of the completed sheet! Cheers!
Thanks Roy, I'll look out for your email :-)
Love the video. thanks would love a copy of this spreadsheet
Hi Jude, Please see my earlier comments :-)
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.
Hi Wayne, message me on FB and I will forward a copy of the completed template :-)
You can check your work against the original. Downloads are available from www.MrSpreadSheet.com :-)
Excellent work, I appreciate. My sent you a FB message.
Thanks DeOntae :-)
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?
Hi, You can check your work against the original. See the download guide video czcams.com/video/m7Zf8_UWPGQ/video.html 😊
@@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!
Hi i would like to ask what is the purpose of lead time?
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 ! 😊
I want a copy of the spreadsheet please.
Pls see my previous replies :-)
Love this! Could I get a copy of the spreadsheet.
Hi Timothy, The file you have requested can be obtained by following the guide in this video czcams.com/video/m7Zf8_UWPGQ/video.html 😊
Are major revisions required for those of us that us Numbers?
Hi, unfortunately I have no experience of Numbers, so I cannot reply.
Please I need a help in warehouse inventory management
Hi Nnenna, You can check your work against the original. See the download guide video czcams.com/video/m7Zf8_UWPGQ/video.html 😊
Hey there great video once again!! Wondering if I could receive spreadsheet. Will provide you with email
Thanks Jeremy, I'm glad you liked the presentation :-)
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 😅
Hi Charly, take a look at the original and compare. Please follow the ‘Download’ guide in this link 😊 czcams.com/video/m7Zf8_UWPGQ/video.html
Great work sir. i would like to get a copy of the file
Hi Kalu, please refer to the guide at the end of the presentation :-)
Do I need to have a inventory sheet and a inventory management or just one of them?
Hi Milena, Please see our ‘Free Mr. SpreadSheet templates’ video 😊 czcams.com/video/m7Zf8_UWPGQ/video.html
@@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
Why is it important to use macro
Hi Afah, there are no macros in this presentation.
hi, how can I download the template in MS Excel Format.
Hi Bawani, Please follow the ‘Download’ guide in this link 😊 czcams.com/video/m7Zf8_UWPGQ/video.html
I would like a copy of your template
Hi channel21060, Please follow the ‘Download’ guide in this link 😊 czcams.com/video/m7Zf8_UWPGQ/video.html
Why H20 is showing #VALUE? and not 0
Hi Gian -Ro, why not download the completed template and check your formulae against the original 😊 czcams.com/video/m7Zf8_UWPGQ/video.html
kindly share the template
Hi, Downloads are available at www.MrSpreadSheet.com :-)
Please sent video
Hi, please follow the guide at the end of the video :-)
please share this template with me
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. 😢
Hi, The intention is to teach/guide and let you decide on how to apply your unique requirements :-)
Hi very informative video very helpful I have sent you a message on Facebook to request a copy of the template thank you 👍
Hi Tracy, please see your FB Messages :-)
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.
Thanks Julio :-)
Great work indeed. This is quite enlightening. My email was sent to your Facebook message.
Tanks Mwango, I'll check FB :-)
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.
Hi Cham, message me on FB :-)
great video . I sent you email via Fb message hope to receive a templet
Thanks Jolanta, I'll look out for your message :-)
Can you send me this template..i've sent you a message via Facebook
No problems :-