Hardware Inventory Stock and Organization

[Cyberphil]

I have decided to take on the daunting task of monitoring, organizing, and keeping track of our stock (in all forms) and I am not really sure where to start with an inventory spreadsheet. I am pretty content with our part and tooling organization in the shop, but we seem to often run out of fasteners, metal stock, and many other things during build season, wasting loads of time.

Here is what I would like to do:

A central google sheet with all items in our inventory and the current stock. There would be a sheet in our stock room that people would write in when they take something and how many they took. Weekly or so I would take the sheet and make the necessary subtractions and make an order if are running low on anything in particular. I know this would not be perfect and not everybody would follow this, but it would hopefully give us a better chance of not running out of something.

Categories and sub categories I would like to have:
Fasteners (Type, Size, Length,wood/machine,etc.)
Common Sheet Stock (Plexi, aluminum, steel, of various thicknesses)
Electronics (connectors, wire, relays, speed controllers, etc)
Pneumatics (connectors, solenoids, tubing)
Power Transfer (Gears (tooth count + type), chain, sprockets, etc)
Actuators (Motors, linear actuators, possibly pistons?)
Tooling (Mill, lathe, router, saw blades, etc)

The main problem I am having is the organization of the fastener section. I could do this fairly easily with a 3-D array, but trying to simplify it in excel is proving to be a bit of a data organization challenge. Any and all help would be greatly appreciated!

How does your team keep track of these things? Or is it more of a whack-a-mole type of system.

[Andrew_Schreiber]

We’re standing up https://partkeepr.org at our local space. Initial futzing, it’s interesting. Might fit well here.

[Cyberphil]

@Andrew Schreiber

We’re standing up https://partkeepr.org at our local space. Initial futzing, it’s interesting. Might fit well here.

This looks awesome! Ill be checking this out for sure.

[pchild]

We’re using PartKeepr as well. We scraped AndyMark, Vex, and West Coast Products to get an initial parts listing, got a used barcode scanner to use with parts and storage locations, and built a photobox to take pictures of parts as they come in.

All of the parts that we’re stocking come from a master spreadsheet that can be updated year-to-year based on what we actually use.

[Cyberphil]

@pchild

We’re using PartKeepr as well. We scraped AndyMark, Vex, and West Coast Products to get an initial parts listing, got a used barcode scanner to use with parts and storage locations, and built a photobox to take pictures of parts as they come in.

All of the parts that we’re stocking come from a master spreadsheet that can be updated year-to-year based on what we actually use.

This sounds like a pretty impressive system. Mind sharing how you use the bar-code scanner? Do you copy the bar code for every item you receive and then use that number in your spreadsheet?

[Billfred]

@Cyberphil

This sounds like a pretty impressive system. Mind sharing how you use the bar-code scanner? Do you copy the bar code for every item you receive and then use that number in your spreadsheet?

I can’t say how they do their setup, but I’ve had good results counting things using Scanner for iOS. It’ll scan barcodes (even ones that aren’t UPCs, so you could use your own system with something like Dymo labels) and keep a tally, even if they’re mixed up, and give you a CSV file you can email to yourself or stick in Dropbox. It’s a promo for Vend’s point-of-sale system, but it’s genuinely useful and free.

[pchild]

@Cyberphil

This sounds like a pretty impressive system. Mind sharing how you use the bar-code scanner? Do you copy the bar code for every item you receive and then use that number in your spreadsheet?

PartKeepr has support for barcode scanners built in. We’re printing Code 128 barcode with a text description on mailing labels for part numbers and storage locations. Part barcodes represent part numbers but are not unique, so every CIM motor in our system is part number P-217-2000. We add red stickers for parts that are used and then returned to inventory. When a part barcode is scanned Partkeepr brings up a stock level adjustment window if the part is registered, and brings up a part registration window if the scanned part isn’t registered (this shouldn’t happen in our use case). Storage locations are pretty similar to parts, drawers get encoded as LDrawer A1 for Drawer A1, etc. When a storage location is scanned PartKeepr reports a list of the parts that are supposed to be in that location.

The master list really only controls the initial list of which parts we buy in and then tracks the part number assigned to each part (to make sure we don’t end up with P-217-2000 and P-am-0255 for CIMs for example). After the master list of parts is set for a year we import any new part data into the PartKeeper system. I’m not entirely satisfied with how we’re handling printing barcodes right now- we have to export a sheet from the master list and then use the Mail Merge feature in Word to print it.

[Cyberphil]

@pchild

PartKeepr has support for barcode scanners built in. We’re using Code 128 barcode with a text description below printed on mailing labels to encode part numbers and storage locations. Part barcodes represent part numbers but are not unique, so every CIM motor in our system is part number P-217-2000. We add red stickers for parts that are used and then returned to inventory. When a part barcode is scanned Partkeepr brings up a stock level adjustment window if the part is registered, and brings up a part registration window if the scanned part isn’t registered (this shouldn’t happen in our use case). Storage locations are pretty similar to parts, drawers get encoded as LDrawer A1 for Drawer A1, etc. When a storage location is scanned PartKeepr reports a list of the parts that are supposed to be in that location.

The master list really only controls the initial list of which parts we buy in and then tracks the part number assigned to each part (to make sure we don’t end up with P-217-2000 and P-am-0255 for CIMs for example). After the master list of parts is set for a year we import any new part data into the PartKeeper system. I’m not entirely satisfied with how we’re handling printing barcodes right now- we have to export a sheet from the master list and then use the Mail Merge feature in Word to print it.

So you have a bar-code for each location and a code for each item. When you scan the location it shows you what items are in that location. That seems quite useful.

Also, you scan a bar-code and if you have the item you can just adjust the inventory number, and if you don’t have it you can create a new item in the inventory.

I assume you just use the manufacturers bar-codes then? Or do you generate your own?

[Cyberphil]

@Billfred

I can’t say how they do their setup, but I’ve had good results counting things using Scanner for iOS. It’ll scan barcodes (even ones that aren’t UPCs, so you could use your own system with something like Dymo labels) and keep a tally, even if they’re mixed up, and give you a CSV file you can email to yourself or stick in Dropbox. It’s a promo for Vend’s point-of-sale system, but it’s genuinely useful and free.

This option looks decent, but we have a mixed bag of android and iphone users. I would prefer something that is interchangeable between all phones or uses a dedicated scanner. A dedicated scanner would be ideal because we have both students and mentors that take items from inventory. With a wireless scanner or something in the room we would easily be able to grab the scanner whenever anyone takes anything out of the room.

I also looked at a few app-based solutions and didn’t find too many that were cross-platform, and most that were had a VERY small number of parts before it became a full on paid service. I’m leaning towards PartKeeper, I just need to set up a server and database for that system, which is something I have never done before.

[pchild]

@Cyberphil

So you have a bar-code for each location and a code for each item. When you scan the location it shows you what items are in that location. That seems quite useful.

Also, you scan a bar-code and if you have the item you can just adjust the inventory number, and if you don’t have it you can create a new item in the inventory.

Yep, this is all correct!

I assume you just use the manufacturers bar-codes then? Or do you generate your own?

We generate our own, the leading character in the part number tells PartKeepr if the barcode is a part or a location.

I just need to set up a server and database for that system, which is something I have never done before.

PartKeepr has decent instructions on getting started, if you have any issues PM me!

[Billfred]

@Cyberphil

This option looks decent, but we have a mixed bag of android and iphone users. I would prefer something that is interchangeable between all phones or uses a dedicated scanner. A dedicated scanner would be ideal because we have both students and mentors that take items from inventory. With a wireless scanner or something in the room we would easily be able to grab the scanner whenever anyone takes anything out of the room.

I also looked at a few app-based solutions and didn’t find too many that were cross-platform, and most that were had a VERY small number of parts before it became a full on paid service. I’m leaning towards PartKeeper, I just need to set up a server and database for that system, which is something I have never done before.

Agreed, I probably wouldn’t use this system as the entire basis of an inventory system–even modern iPhone cameras, fast as they are, aren’t usually as fast as a proper laser scanner. But if you ever get into a situation where you need a lot of people counting quickly (someone knocks over the shelf, doing a big inventory count after an event, etc.), you could certainly do worse.

[Michael_Corsetto]

For inventory, I’m a fan of the “count-once-a-year” system we have on 1678. We just have a few master lists by category, what our target amount is, and what we currently have in stock (as of counting in November). Then we order everything in late November/early December and we have it in-stock by the time the season starts. We obviously order other things in-season, but all of our basics are covered beforehand, every year.

For organization, we love flat tackle-box style for bolts and other small parts, and the small-size clear single bins for larger parts (wheels, 1000’s of zip ties, motors, etc).

Scan in/out systems seem like a headache, especially in a program with students and high turn over. I think you’ll find that either A) the inventory count becomes inaccurate quickly and needs frequent manual adjustment or B) way too much time is spent on training and using the system than effectively building robots.

I’ve worked at companies with strict inventory systems and you can generally expect 5-10% of your workforce to be committed to inventory in once sense or another (aka a full time job). Without 5-10% of your students/mentors doing inventory all meeting, every meeting, I doubt a strict inventory management can blend well with a competitive robotics program.

Just my thoughts.

-Mike

[waialua359]

@Michael Corsetto

For inventory, I’m a fan of the “count-once-a-year” system we have on 1678. We just have a few master lists by category, what our target amount is, and what we currently have in stock (as of counting in November). Then we order everything in late November/early December and we have it in-stock by the time the season starts. We obviously order other things in-season, but all of our basics are covered beforehand, every year.

For organization, we love flat tackle-box style for bolts and other small parts, and the small-size clear single bins for larger parts (wheels, 1000’s of zip ties, motors, etc).

Scan in/out systems seem like a headache, especially in a program with students and high turn over. I think you’ll find that either A) the inventory count becomes inaccurate quickly and needs frequent manual adjustment or B) way too much time is spent on training and using the system than effectively building robots.

I’ve worked at companies with strict inventory systems and you can generally expect 5-10% of your workforce to be committed to inventory in once sense or another (aka a full time job). Without 5-10% of your students/mentors doing inventory all meeting, every meeting, I doubt a strict inventory management can blend well with a competitive robotics program.

Just my thoughts.

-Mike

Mike,
we are with you on this. Inventory changes just way too much during build season and we dont want to get caught up with it when we have a robot to build.
Once we had our system in place, all we do is replace what we use. i.e. keep track of what we consume on the Bill of Materials including spare parts for the most part.
If new components come out, then we purchase in the Fall and add to our overall inventory of that category of components.

-Glenn

[Cyberphil]

@Michael Corsetto

For inventory, I’m a fan of the “count-once-a-year” system we have on 1678. We just have a few master lists by category, what our target amount is, and what we currently have in stock (as of counting in November). Then we order everything in late November/early December and we have it in-stock by the time the season starts. We obviously order other things in-season, but all of our basics are covered beforehand, every year.

For organization, we love flat tackle-box style for bolts and other small parts, and the small-size clear single bins for larger parts (wheels, 1000’s of zip ties, motors, etc).

Scan in/out systems seem like a headache, especially in a program with students and high turn over. I think you’ll find that either A) the inventory count becomes inaccurate quickly and needs frequent manual adjustment or B) way too much time is spent on training and using the system than effectively building robots.

I’ve worked at companies with strict inventory systems and you can generally expect 5-10% of your workforce to be committed to inventory in once sense or another (aka a full time job). Without 5-10% of your students/mentors doing inventory all meeting, every meeting, I doubt a strict inventory management can blend well with a competitive robotics program.

Just my thoughts.

-Mike

Thanks for your input Mike. If we created a dedicated system like this it would likely only be used for a few months out of the year. Though it might work for us, it would require lots of time and energy to keep track of, and training for each new student that uses it.

I am definitely leaning towards a resupply during the preseason now that I am thinking of all of the logistical issues.

Would it be possible to get those master lists you have? I started generating a list, but if other teams already have them and are willing to share, I would save a lot of time. I might even throw in a team shirt for you for your troubles. :]

[Cyberphil]

@waialua359

Mike,
we are with you on this. Inventory changes just way too much during build season and we dont want to get caught up with it when we have a robot to build.
Once we had our system in place, all we do is replace what we use. i.e. keep track of what we consume on the Bill of Materials including spare parts for the most part.
If new components come out, then we purchase in the Fall and add to our overall inventory of that category of components.

-Glenn

Using the BOM of the bot sounds like a good idea on paper, but I know we use so many more items than what eventually ends on the bot. I imagine we would come up way short on inventory if it was based on that alone.

I mean heck, I make a few fixtures every year with all sorts of stock, bolts, and various things.