Inventory Management Tool

Big Monk

Big Monk

Well-known member
I asked Robert to re-activate my account because I’ve been working on something that I think could be of great use to the forum.

Ordering and Inventory Management is the fucking bane of my existence. I’ve actively put off batches in recent months, as well as held off on personal projects because I have not been happy with my current ordering process.

I decided right after filing my taxes this year that I wanted to come up with a dynamic inventory and ordering system.

I’m not a programmer in any sense of the word so I decided to use Excel. I’m pretty close to it being fully functional and when it is, I can post it for download to all the members here.

It’s tailored to my setup but is BoM based so I think with a little setup, anyone could apply it to tracking inventory and ordering parts for projects on a project to project basis.

I’ll post up some visuals over the weekend but the basic process flow is as follows:

1.) There are 5 Tabs: Entry, Order, Database, Inventory and BoMs.

Entry can be used as a standalone sheet for restocking. It can also export to the Order sheet, to the Database sheet and receive import from the BoM sheet. There are a number of macro buttons that execute export to the Order tab, export to the Database with coincident clearing of the Entry Tab and a simple clear of the Entry tab without any export.

The Order sheet accepts export from the Entry sheet and multiple BoMs. It has multiple macro buttons. It can accept Raw Order data (multiple BoMs with no component sorting) and has a button for sorting all similar component classes and ID numbers to give consolidated quantities.

The Database tab is the permanent record of “Stock In” and “Stock Out” transactions. It adds and pulls from the Master Inventory once transactions are export and saved there. It has a single button for clearing the database in the event you need to refresh your inventory counts.

The Inventory tab houses all the Inventory and Component IDs, Vendor IDs, etc.

The BoM tab allows you to save BoMs for commonly made pedals or batch pedals as well as custom BoMs for one off or DIY. I think we can probably come up with a system for importing files into BoMs and storing them here. Each BoM has an export button for sending them to the Entry sheet.

I’m still developing some of the formatting and the process for using it but so far I’m very pleased and I think it could be something useful for everyone.

I’ll post more this weekend as I have a rare Saturday and Sunday off from youth basketball.
 
Awesome good luck!

I wish I had time to help- I used to program estimating and BOM software for national production home builders. It can get complicated, but if everything gets linked and synched properly, could be a powerful tool.

I’ve basically resorted to the “buy 1,000 of everything” and replenish when I run out method 🤣
 
Well I am a professional programmer, retired, and congratulations you are a programmer now too!

My programming was in Unidata which is a multi dimensional database structure. In fact it’ll do as many dimensions as you want 3,4,5,6…. 100 no sweat.

Say you had a widget that goes into a box and those boxes go into a ship pack and those ship packs go into a case in those cases go on a pallet and the pallet goes into a storage position in the warehouse and there are multiple warehouses in cities all over the country

That’s where multidimensional comes in handy
 
RetiredUnit1 said:
Well I am a professional programmer, retired, and congratulations you are a programmer now too!

My programming was in Unidata which is a multi dimensional database structure. In fact it’ll do as many dimensions as you want 3,4,5,6…. 100 no sweat.

Say you had a widget that goes into a box and those boxes go into a ship pack and those ship packs go into a case in those cases go on a pallet and the pallet goes into a storage position in the warehouse and there are multiple warehouses in cities all over the country

That’s where multidimensional comes in handy
Click to expand...

The tricky part with Excel is not making the sheet fully functional but more along the lines of making sure others can use it.

One thing you always run into is that not everyone uses Excel and tjere are always compatibility issues.

The other is trying to make sure the process flow makes sense to others. I’ll spend weeks and weeks n something and know it back and forth and then have to put down some sort of procedure to make it make sense to others!
 
Big Monk said:
The tricky part with Excel is not making the sheet fully functional but more along the lines of making sure others can use it.

One thing you always run into is that not everyone uses Excel and tjere are always compatibility issues.

The other is trying to make sure the process flow makes sense to others. I’ll spend weeks and weeks n something and know it back and forth and then have to put down some sort of procedure to make it make sense to others!
Click to expand...
Yes the nemesis of every programmer. Documentation…!

I was once assigned the task of writing a how to on how to write how to’s!!!!!
 
This is a pretty stable prototype of what I have whipped up:


Use the following procedure to test it's functions:

1.) Go to the BoM tab and export 3 2 instances of Pompeii (Standard). Two batches worth of this pedal will export to the Entry Tab.

2.) Go to the Entry tab. Click Send to Order Tab, then click Clear Entry Tab. This will send the raw order data to the Order Tab.

3.) Go to the Order Tab. Click Organize All Raw Data. This will consolidate the multiple BoM instances and output consolidate and total quantities for each item. This consolidate order is your order sheet. Click Export to Entry and Clear. This sends the consolidated order data back to the entry tab, which you would do when you got the order in.

4.) Go to the Entry Tab again. Click Export to Database and Clear. You've now logged this order.

5.) Go to the Inventory Tab and those items will show in your Inventory.

6.) To simulate building a pedal, go back to the BoM Tab and change Order quantity to 1. By clicking Export Pompeii Standard, I'm saying i'll be building 1 of them.

7.) Go back to the Entry Tab. Notice that now we have these parts in Inventory, the transaction type is "Stock Out", so the Database logs it as a pull instead of requiring restock, i.e. "Stock In". Click Export to Database and Clear and it will pull these components out of inventory.

8.) Repeat Step 6 and 7 and you'll see Inventory levels have changed.

Note: You can use the Entry sheet for an Initial Count of your Inventory as well, either manually entering items or making a special instance of a BoM that contains your existing Inventory.
 
NO! Haha! I'm a reluctant computer user and don't really enjoy much software. Dyed in the wool Mac-user. Anything "sensible" or to do with accounting/inventory/spreadsheets makes me sweat profusely and run away. I have Logic but struggle to even start a file. I can use Photoshop and some image conversion programmes whose names I don't remember and they seem to make sense to me, and I've become quite good with DIYLC. But anything that normal people use makes my head spin. Seems my head doesn't work like a computer.

My brother, on the other hand... His brain works just like a computer. But he can't play in time at all. He gave up guitar because of it.
 
You must log in or register to reply here.
Back
Top