Inventory/storage location spreadsheet

[sy_gilana]

Excellent work, just wish I could see it in action, however I decided some time ago to never have Microsoft on any of the machines I actually own. Still ahead.

Incidentally the choice you have about location sort, vs. item sort is really important. Heres why... When you are doing a stock-up you usually pack locker by locker, and when you are consuming, you choose by item not location.

Agree that EOQ etc are overkill, but min qty is a good alt.

Can access do a predictive search?

Another idea, perhaps put to a poll, is the addition of photos, I have links in my SS to pictures of certain items like spare parts, pumps, etc, obviously not jelly jars and beercans. Not a necessity.

VERY important for all users is the reverse notation, for users who have not read the intro on our web page. You enter stock backwards, so Pink Rubber Gloves MUST be entered Gloves Rubber Pink. Shirt working blue, Fruit Mixed Syrup Can, Fruit Mixed Syrup Jar, Pump Jabsco Water Puppy 24v 1/2" NPT, Pump March 220v A/C.
The reasons are obvious when a sort is done.

NICE WORK StuM

[scherzoja]

Thanks Stu.

Good and simple.

I'll bang away at it and see what pops up.

nice job.

[rwidman]

Originally Posted by StuM

If you're trying to cross link stores and equipment in various categories over various locations, a simple database would be much better than a spreadsheet..........

It takes just one click of the mouse to corrupt a "database" created in a spreadsheet. That's not what a spreadsheet is made fore.

An actual database program is what should be used. The downside of course is that few people understand the concept of a database so they don't make effective use of the software.

[rwidman]

Originally Posted by sy_gilana

Spreadsheet is good enough, and it allows you to sort by name or location, ...............

And if you make the simple mistake of not including all the columns in the sort, the spreadsheet is corrupted. That's the point I tried to make in my post above.

[rwidman]

The real problem with any sort of system is convincing the users to actually use it and use it in real time. How many times have you gone to a vendor and the guy says "The computer says we have one but the bin is empty."

I have a hard time believing that someone is going to start a computer, open a database program and record one can of tomato sauce used. It might happen if the computer and database were running 24/7 and was bar code capable.

There was a time many years ago when I developed a database to record the activities of a maintenance staff of over 300 people maintaining over 200 public school buildings. This recorded and reported on time and materials. The database worked fine. The employees did not. Time and materials did not get entered correctly and sometimes were not entered at all.

If you want to do it and can convince everyone involved to follow procedures, fine. We just "wing it".

[StuM]

Originally Posted by rwidman

The real problem with any sort of system is convincing the users to actually use it and use it in real time. How many times have you gone to a vendor and the guy says "The computer says we have one but the bin is empty."

Earlier this year in a hardware store, I took something off a shelf to the checkout and was told that I couldn't buy it because the computer said there were none in stock.

I have a hard time believing that someone is going to start a computer, open a database program and record one can of tomato sauce used. It might happen if the computer and database were running 24/7 and was bar code capable.

Me too If you are going to track consumables such as food to that degree, I'd do an inventory check periodically and/or update it daily with items used. (well, actually I personally wouldn't do the latter - I'm just not that organised )

There was a time many years ago when I developed a database to record the activities of a maintenance staff of over 300 people maintaining over 200 public school buildings. This recorded and reported on time and materials. The database worked fine. The employees did not. Time and materials did not get entered correctly and sometimes were not entered at all.

If you want to do it and can convince everyone involved to follow procedures, fine. We just "wing it".

Me too, that's why I built one for this thread, I didn't have one already.

But I think I might use it now for things other than food and clothing that tend to sit in out of the way places for months or years.

[sy_gilana]

Well, some might find it hard to believe, but we actually DO open the spreadsheet and subtract one can of Tomatoes, or indeed anything, when it is used, every time without fail. Going on 19 years now, it works and we know what we have and how old it is.

[Pelagic]

An important thing to remember is that your inventory/location and linked receipt/disbursement worksheets must be easy to follow for those who are learning to use it

That usually means linear entries of set columns in reverse notations as mentioned, so that it is easy to sort and present in whatever order you wish from the data entered.

Excel has worked well for me in teaching Superyacht officers how to manage their inventories

i separate into 4 identical Sheets ..Hotel / Deck / Catering / Engineering and use the same codes for Location / Consumables / Spares/ Capital Expenditures / and the Generic name in one column for Alphabetical sorting.

It is easy to maintain the inventory because you tend to move popular consumable stock from one deep storage location to a daily cupboard, so we just adjust inventory as what remains in the Deep location and only add what is left in the daily location, when ready to go shopping.

By reducing the sheets down to 4 departments, all with the same format, it is easy to find and update smaller individual worksheets.

[Stu Jackson]

Originally Posted by rwidman

And if you make the simple mistake of not including all the columns in the sort, the spreadsheet is corrupted. That's the point I tried to make in my post above.

That's true, and a reason for maintaining a discipline of keeping the last update as a separate file before you move on.

If folks who are running (complicated) sailboats that require a certain amount of discipline for both seakeeping and safety, and who choose to develop any kind of inventory, then this should be the easy part, because once you save the spreadsheet, the sort "comes with it," doesn't it?

I have maintained both a fuel log AND an inventory for the past 18 years. My inventory includes part numbers, (but not locations) which I shared and posted on the separate "spreadsheet" thread earlier this week. Yup, it takes time to do it.

I chose to use a spreadsheet because I know how to work them, even though I've used databases in my work. I just personally find them too cumbersome, but Stu seems to be as quick with a database as I am with a spreadsheet.

But when my autopilot belt needed to be replaced in the middle of Monterey Bay one time, I was able to look up the part number, call it in and have it delivered to my next port of call. Even the Raymarine manual didn't have the West Marine part number in it, so was essentially useless for purchasing. My inventory did have it.

Which reminds me: I have to update the inventory with all the stuff we bought on our six week trip up the coast last summer.

Bummer...

[Tafa]

This is amazing.......

Thank you very much

Tafa

[chuckr]

We do a complete boat inventory about every other year - just did one

we use Microsoft spreadsheet

here is our last one

[Meanderthal]

I'm new (<2yrs) to cruising life but absolutely started an inventory. The process dragged along until I realized how much money is saved without compromising refit quality by knowing where to find (and the part numbers of) the affordable alternatives to manufacturer-direct gaskets and expensive bits and pieces. (Bless internet forums and differential diagnosis&#129303. I (and we) also now keep a super-detailed work log, especially on topics related to propulsion.

I keep having to relearn the value of activity logs and inventories--every time I switch lifestyles or big hobbies, it seems. Pretty handy for hiking, rving, car trips, international travel, business, and farm life too😍

[Matt White]

StuM - this is great! Just what I was looking for. Is there an easy way to remove the "sample inventory" that you have in the download? I tried, but quickly blew up the DB (whoops)

Matt

[Matt White]

Originally Posted by Matt White

StuM - this is great! Just what I was looking for. Is there an easy way to remove the "sample inventory" that you have in the download? I tried, but quickly blew up the DB (whoops)

Matt

Also - I need to change/add/delete storage locations. Any words of caution are welcome!

Thanks again for this - very cool!

Matt

[RickG]

We have a spreadsheet and intend to keep it up... I need to try printing it out, marking usage and batch updating.

Cheers, RickG