Excel Inventory Management Endeavor

[adamgrier]

Hello I am a new user to the forum and I am hoping that through this network I can meet people who will be able to help me in a rather large undertaking. I will start by giving you a general idea of what I am attempting to do with excel, and then go into further detail later about specific problems that I am encountering.

I have been working for a small but rapidly growing manufacturing company for about a year now. Presently, we have no automation and/or file sharing within our departments which creates a paperwork nightmare (not to mention is horribly inefficient).

Currently we have several departments which each have pieced together small excel spreadsheets to accomplish and record their individual goals. For example, sales uses an excel template to take orders. Our production scheduler uses excel to create schedules for both production and engineering. Purchasing uses several small files to help in that arena. And shipping uses excel files to record what has been shipped.

I am trying to created a robust model that will achieve the following objectives.

-First, increase overall efficiency but eliminating redundant data entry.

-Second, improve overall management effectiveness by allowing changes to be viewed by all users of the system simultaneously (or within a matter of a few minutes).

-Finally, create a single database system that stores all of the relevant information for each job.

Also, since I would like each user to be able to edit only particular ranges I am not sure whether it would be best to do this using a shared file on a network location or a web based file.

I have given this a lot of thought over that last few months and have a really clear picture of how I would like to put this together into a useful system. The only problem is I do not know any programming language. My hope is to be able to achieve this goal using only VBA, but I am hearing that this may not be the best option.

Since we are a manufacturer we have "stock" items that we make routinely and unique items as well that are made to customer specifications. That being said, I will start with sales.

--Sales--
Customer Contact Information (Name, Phone, Email, Address, etc.)
Order Information (Model/Size/Finish/Graphics)
Additional Parts/Add-ons (Keychains, Seatposts, TShirts, etc.)
Expected Ship Date

Once information is entered into the sales form I would like the system to be able to recognize whether we have the item in stock or if we need to build the item and then respond appropriately. If the item(s) are in stock then the system should designate one of the stock items for the sales order. If the item(s) are not in stock then the system should put them on a list of things to be built.

--Accounting--
Be able to click a check box when money has been received on the order

--Scheduling--
Be able to see what items are currently in stock and which items need to be built.

--Production--
Be able to enter jobs that have started production and then check off progress as the item moves through each workcenter towards completion.

--Shipping--
Be able to see all items that are coming into the department and then have the system identify order that have all constituent parts complete and ready for shipment so that this department only spends time preparing orders for shipment which are completely ready to go. Then be able to click a box that the order is in fact completed and shipped and enter a tracking number.

Your thoughts on the best way to go about this would be greatly appreciated.

[Paul]

Hello Adam, and welcome to the forum.

Thank you for taking the time to post your well thought out plans to help improve your company. Coming from a business that has several retail and manufacturing areas, I can say that even using Excel for small business inventory/sales/accounting/production/shipping - while it can certainly be done - is probably the least efficient computerized method (other than Notepad!). Using Excel as a database is generally not in your best interests from a portability, expandability, security or data recovery perspective (amongst others).

For something this all-encompassing, I would not hesitate to look for a small inventory application that uses an inexpensive database (for example, SQL Express is free, but only allows up to 5 concurrent connections I believe). SQL Server, Oracle, or proprietary databases are also useful, but tend to cost a bit more money.

I'm not intending to scare you or lead you down an unknown path, but you'll likely spend weeks (if not months) building a system to meet your company's needs that will require constant attention as users mess up the spreadsheets, break links, delete data and need to restore files from previous backups (which hopefully you have!), etc.

If you feel you're up to it and want to go down that path, rest assured we're here to help! If you feel it may be worth it, there is even a Commercial Services forum where you can offer payment for someone to spend time working with you on an extended basis (terms would be decided between you and that person, of course). Otherwise our volunteer staff will likely be able to answer your questions in the various forums available to you as a member.

Thanks, and best of luck in your efforts!

[suzzy]

Hello Adam,

The model you are seeking seems to be a highly complicated one to be created without certain knowledge of programming. Since you stated above that you did not know any programming language, I think you should give a try to some professional software like SpreadsheetWEB.

The trick with SpreadsheetWEB is that it converts your Excel spreadsheets to web based files and renders them available to be shared with others instantly. They will also be able to make changes on files simultaneously.

The system will sustain the whole process of sales, accounting, scheduling, production and shipping of your company perfectly.

If you would like to give a look at the product for more information, you can visit the website below.

http://www.spreadsheetweb.com/

Suzzy