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

[@abdiaziizjackwar1465]

I love your lessons Cuz they support me a lott various jobs and projects

[@MrSpreadSheet]

Thanks Abdi 😊

[@somukienibilly1456]

Thanks for the video. It is very informative. I followed along and was able to create my very own inventory table! Many thanks

[@MrSpreadSheet]

Fantastic, well done :-)

[@alejandrodejesus4121]

good job!!!! leaned a lot. Thanks

[@MrSpreadSheet]

Glad it helped!

[@ivymancia1674]

Hello, this video has been very helpful! Could you also assist me with including the daily, monthly, and yearly sales and profit calculations?

[@MrSpreadSheet]

Hi, we have many other videos covering these topics, the information and guidance in these should help. :-)

[@titacblancher6137]

I wish you were my teacher ☺

[@MrSpreadSheet]

Thanks :-)

[@nurudeenabdullateef5120]

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 😊 kzbin.info/www/bejne/o2i9l2uViryDfbM

[@wskeithan]

very nice thanks

[@MrSpreadSheet]

Thank You :-)

[@gazmills2814]

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 kzbin.info/www/bejne/o2i9l2uViryDfbM 😊

[@Mirzaiiui]

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. :-)

[@empirecloset]

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]

Hi, You can check your work against the original. See the download guide video kzbin.info/www/bejne/o2i9l2uViryDfbM 😊

[@ebriuno]

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. :-)

[@romanpelukh7559]

thank you!!!!

[@MrSpreadSheet]

You're welcome!

[@andyg-l8z]

is it possible to enable barcode reading for both receiving and selling ?

[@MrSpreadSheet]

Yes, barcode reading could introduced :-)

[@shaniquebrady5784]

@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]

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. 😀

[@TheDeliveryOnline]

Is this for like 1 set time period? How do you record daily sales and purchases so that it updates inventory.

[@MrSpreadSheet]

Hi, You can set whatever time period you want, just add a date range to the Purchases and Sales Table. :-)

[@deontaeh]

Excellent work, I appreciate. My sent you a FB message.

[@MrSpreadSheet]

Thanks DeOntae :-)

[@mohammedfahad3520]

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. 😊

[@kendragriffin1127]

Thanks for the informative video! Why is profit calculated that way?

[@MrSpreadSheet]

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 :-)

[@judewilliams7764]

Love the video. thanks would love a copy of this spreadsheet

[@MrSpreadSheet]

Hi Jude, Please see my earlier comments :-)

[@kennethporras3464]

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 ! 😊

[@jigsshah1982]

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 kzbin.info/www/bejne/o2i9l2uViryDfbM 😊

[@jamesa347]

I need to add in different values for the sale value so my profits come up correctly can you help ?

[@MrSpreadSheet]

Hi James, You can check your work against the original. See the download guide video kzbin.info/www/bejne/o2i9l2uViryDfbM 😊

[@zanderhuynh7998]

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]

Hi Zander, Yes, a Bill of Materials spreadsheet is planned. :-)

[@samuelabaiyaguo]

How can i do Searchable dropdown list with Xlookup to return value

[@MrSpreadSheet]

Hi, You can check your work against the original template. A download is available at www.MrSpreadSheet.com :-)

[@milenaserrato3359]

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 😊 kzbin.info/www/bejne/o2i9l2uViryDfbM

[@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

[@TraciRoesler]

How can I get help? I made every formula exact and nothing will populate in the “sales quantity” “sales value” and “profit” columns.

[@MrSpreadSheet]

Hi, Why don't you download a copy and then check your work against the original. Go to www.MrSpreadSheet.com :-)

[@TraciRoesler]

@@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??!!

[@royasher6268]

Great Video. Subbed and emailed you my contact info to hopefully receive a copy of the completed sheet! Cheers!

[@MrSpreadSheet]

Thanks Roy, I'll look out for your email :-)

[@phantomsellers8952]

Are major revisions required for those of us that us Numbers?

[@MrSpreadSheet]

Hi, unfortunately I have no experience of Numbers, so I cannot reply.

[@MITLogisticsMark]

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 :-)

[@nnennaawaibe1138]

Please I need a help in warehouse inventory management

[@MrSpreadSheet]

Hi Nnenna, You can check your work against the original. See the download guide video kzbin.info/www/bejne/o2i9l2uViryDfbM 😊

[@lethal2453]

Use iferror because if will run everytime, iferror only when an error. Sheet performance.

[@MrSpreadSheet]

Thanks for your insights Lethal :-)

[@07chicobing]

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]

Hi Dee, I have just sent you the link, sounds like quite a job. I hope the file helps you. :-)

[@07chicobing]

Thank you. I don't see where the link is. Email?

[@kaluanyim1453]

Great work sir. i would like to get a copy of the file

[@MrSpreadSheet]

Hi Kalu, please refer to the guide at the end of the presentation :-)

[@marieanaismeunier6712]

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]

Hi Marie, take a look at the completed template 😊 kzbin.info/www/bejne/o2i9l2uViryDfbM

[@Skysky67567]

​@@MrSpreadSheetThe vedio is private

[@timothykimble4482]

Love this! Could I get a copy of the spreadsheet.

[@MrSpreadSheet]

Hi Timothy, The file you have requested can be obtained by following the guide in this video kzbin.info/www/bejne/o2i9l2uViryDfbM 😊

[@sandraquezada3498]

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]

Hi Sandra, the on line versions of Excel are 'lightweight' in their functionality. I suggest that you use the full PC versions. :-)

[@macleodfamilyfarm]

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. :)

[@ppam4461]

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]

Hi Pam, message me on FB and I will forward a copy of the file :-)

[@murliwale9454]

❤❤

[@MrSpreadSheet]

Thanks :-)

[@tracycharles1964]

Hi very informative video very helpful I have sent you a message on Facebook to request a copy of the template thank you 👍

[@MrSpreadSheet]

Hi Tracy, please see your FB Messages :-)

[@charlyjacobs5996]

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]

Hi Charly, take a look at the original and compare. Please follow the ‘Download’ guide in this link 😊 kzbin.info/www/bejne/o2i9l2uViryDfbM

[@marvinchidi851]

I want a copy of the spreadsheet please.

[@MrSpreadSheet]

Pls see my previous replies :-)

[@bawanipoito3630]

hi, how can I download the template in MS Excel Format.

[@MrSpreadSheet]

Hi Bawani, Please follow the ‘Download’ guide in this link 😊 kzbin.info/www/bejne/o2i9l2uViryDfbM

[@seanjjpaul]

Hey there great video once again!! Wondering if I could receive spreadsheet. Will provide you with email

[@MrSpreadSheet]

Thanks Jeremy, I'm glad you liked the presentation :-)

[@wayneynot]

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]

Hi Wayne, message me on FB and I will forward a copy of the completed template :-)

[@MrSpreadSheet]

You can check your work against the original. Downloads are available from www.MrSpreadSheet.com :-)

[@darleneplummer6815]

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]

Hi Darlene, thanks for your insights :-)

[@channel21060]

I would like a copy of your template

[@MrSpreadSheet]

Hi channel21060, Please follow the ‘Download’ guide in this link 😊 kzbin.info/www/bejne/o2i9l2uViryDfbM

[@macleodfamilyfarm]

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 kzbin.info/www/bejne/o2i9l2uViryDfbM 😊

[@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!

[@afahloveline-nj9wm]

Why is it important to use macro

[@MrSpreadSheet]

Hi Afah, there are no macros in this presentation.

[@amitnavdeep]

kindly share the template

[@MrSpreadSheet]

Hi, Downloads are available at www.MrSpreadSheet.com :-)

[@gian-romarquez4882]

Why H20 is showing #VALUE? and not 0

[@MrSpreadSheet]

Hi Gian -Ro, why not download the completed template and check your formulae against the original 😊 kzbin.info/www/bejne/o2i9l2uViryDfbM

[@HillaryRooney-Fernandes]

please share this template with me

[@MrSpreadSheet]

Hi, Downloads are available at www.MrSpreadSheet.com

[@rassiejacobs087]

Please sent video

[@MrSpreadSheet]

Hi, please follow the guide at the end of the video :-)

[@tracybrasells5210]

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]

Hi, The intention is to teach/guide and let you decide on how to apply your unique requirements :-)

[@JulioRPerez]

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]

Thanks Julio :-)

[@jeffmwango]

Great work indeed. This is quite enlightening. My email was sent to your Facebook message.

[@MrSpreadSheet]

Tanks Mwango, I'll check FB :-)

[@chamfer4844]

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]

Hi Cham, message me on FB :-)

[@jolantaizabelacreations4295]

great video . I sent you email via Fb message hope to receive a templet

[@MrSpreadSheet]

Thanks Jolanta, I'll look out for your message :-)

[@kalanagunathilaka660]

Can you send me this template..i've sent you a message via Facebook

[@MrSpreadSheet]

No problems :-