AnsweredAssumed Answered

Physical Inventory Management?

Question asked by Daen Hendrickson on Oct 29, 2009
Latest reply on Mar 25, 2014 by Steven Dod

This is not an inquiry about SW or EPDM specifically. Rather I am interested in the software tools and processes people use to manage the physical inventory in their companies. Any good books on methods for managing this part of business? And also the general processes followed from the initiation of design to delivering to the customer.


This post is LONG, so best to print it out and take it with you on your next trip to the bathroom.

Our company has grown from 4-5 to 20+. Many of the informal processes that were adequate before are now falling flat on their faces. We are nearing the end of this current project and I finally have a chance to gasp for air... And review. And we sorely need to adjust/define many of our processes.

We are a small company that is project oriented in the light manufacturing and assembly realm. We design and build high fidelity flight simulators. The prototype is the SW model and production quantities range from 1 to 4 units. The simulators have thousands of parts. Although we aim to commonize subsystems, each contract is typically for a unique design. The parts can be grouped as internally designed parts, standard hardware, and COTS modules like computers and other electronic black boxes and cables.

Our design process is relatively stable. We use SW2008 and PDMWW (probably will be upgrading when this project is done to SW2009 SP5). However, we have an in-house Access DB to manage our inventory. The boss hired a friend's kid who was "real good with that sorta thing" to write the DB as a summer project. Two years later (hired him full time...) of continual work and it is still a very special experience to use. So much so I am driven to write this post!

Google tells me there are myriads of inventory software products out there. But none of the handful I reviewed seems to do quite what I think we want. The asset tracking type software seems geared more towards fixed asset control. Warehouse inventory seems more geared to handling lots of parts in and lots of parts out. We will typically get four or five parts in, or a box of 50 screws, or a computer with a SN. These get located on a shelf. Then we need to capture the assembly. These parts are combined with some of the screws in that box and become a new part number (assembly number). Repeat that until you have the top level assembly. This assembly then will have its location changed to the final installation place for the customer. We will need to capture this final installation information for follow-on maintenance / warrantee / upgrade reasons.

Our Inventory DB - although "special" as noted above - seemed to be functional. Until now! The DB hasn't changed, but what I am asking of it has. My job mostly involves the hardware side of things. Now that the simulators are built and running the biggest workload has been shifted to the software people. So my crew is now going back and doing the things left undone - which is a lot of filling in of the database.

What I am realizing is that capturing the parent/child information into the database is redundant work. It already exists in our model. But the database is where the money tracking takes place so the boss says its got to be accurate. So we are recreating the same hierarchy of assembly. We also have many parts that are not in the database but need to be. We have a dedicated fab shop contracted to us (not in-house). As we begin building physical parts, we deliver a kit to the fab shop for a particular sub assembly. The kit consists of the purchased hardware and the drawings for "our" fabricated parts. We get back the subassembly. That subassembly is received into the database but not the parts it is comprised of. Well, not all. The purchased parts had to be entered in the DB to buy them but the parts created at the fab shop are not. And the partial quantity of the purchased parts are not associated with the subassembly. So with 1500+ parts in this thing, you can appreciate the amount of whining from my engineers to re-enter this information.

On the front end of things is the issue of generating part numbers. We have a part numbering scheme. It is semi-intelligent in that all the parts of a particular subassembly have a common number grouping. When we were small I created a shared Excel spreadsheet to organize part numbers. This is a recognized area of "NEEDS Improvement".

But the new realization to me is that I have all these different un-linked systems. Manually create a part number in the spreadsheet. Create the parts / assemblies in SW, capture the design in the vault. Then purchase materials which involves a manual review of a particular sub assembly and creating entries in the database of parts that are generally in SW, or the Vault, or Toolbox, or Library. Receive the parts, kit the parts, then request fabrication from our shop. Lots of re-entry of the same stuff.

What I would like to see is an integrated system to generate / assign a part number. The generation of this part number would create an entry in the database and possibly create an empty model structure (anyone played with SW Labs Treehouse). Once designed in SW, a screen image of the model and some of the custom property info would be transferred to the DB. The parent / child structure from SW would also be transferred to the db.

Still with me????

So what products are you folks using for some or all of your steps for the total engineering and manufacturing steps. What part of this can / should EPDM do?

Thanks for your patience in wading through my muddling. I know there isn't one piece of magical software to fix it all, but I am truly interested in what has worked for you. And I am interested in any good reads on what to consider when putting these processes together.