Cruisers Forum
 


Join CruisersForum Today

Reply
 
Thread Tools Search this Thread Rate Thread Display Modes
Old 21-12-2016, 11:54   #1
Registered User
 
ErBrown's Avatar

Join Date: Apr 2013
Location: Austin TX
Boat: IP 40
Posts: 374
Inventory/storage location spreadsheet

Howdy Folks,

We're trying to get a handle on how to best keep track of and organize an inventory including storage locations. I've been scouring blogs for ideas but thought it would be worth posting something up here.

Anyone have a spreadsheet that works well for them, that they'd be willing to share. Any good apps out there?

Thanks again,
EB
__________________

__________________
vela-boat.com (a work in progress)
ErBrown is offline   Reply With Quote
Old 21-12-2016, 12:18   #2
Registered User
 
UNCIVILIZED's Avatar

Join Date: Jun 2014
Location: Up the mast, looking for clean wind.
Boat: Currently Shopping, & Heavily in LUST!
Posts: 5,629
Re: Inventory/storage location spreadsheet

Someone was kind enough to pass this one along to me yesterday. Though I've not had a chance to investigate it much as yet. But there's a free, downloadable spreadsheet along with instructions. https://theboatgalley.com/downloadab...g-spreadsheet/
What else have you tried to date?

Also, I posted that I needed some spreadsheet help last night, & folks have been sending me all sorts of info. Perhaps some of what's in the thread might help you. It's here --> Spreadsheet Help (Needed)!!! For me, & ultimately Newby boat buyers.
__________________

__________________

The Uncommon Thing, The Hard Thing, The Important Thing (in Life): Making Promises to Yourself, And Keeping Them.
UNCIVILIZED is offline   Reply With Quote
Old 21-12-2016, 14:43   #3
Senior Cruiser
 
StuM's Avatar

Cruisers Forum Supporter

Join Date: Nov 2013
Location: Port Moresby,Papua New Guinea
Boat: FP Belize Maestro 43
Posts: 7,310
Re: Inventory/storage location spreadsheet

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.

Who'd be interested in an MS Access one?
StuM is offline   Reply With Quote
Old 21-12-2016, 14:53   #4
Registered User
 
ErBrown's Avatar

Join Date: Apr 2013
Location: Austin TX
Boat: IP 40
Posts: 374
Re: Inventory/storage location spreadsheet

Quote:
Originally Posted by StuM View Post
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.

Who'd be interested in an MS Access one?

I'd be very interested, never been much of a DBase guy, but it probably would be the best option. I'd be interested to see it, but as a Mac user, I've never used access.

Sounds like you're onto what we're looking for. An easy way to keep track of what we store where. Thinking more along the lines of spare parts and the types of things we don't use on a regular basis. I have seen the boat galley spreadsheet linked above and think it's great for shopping purposes, but we're really more looking for something to tell us which of the crap-ton of lockers has X-widget.
__________________
vela-boat.com (a work in progress)
ErBrown is offline   Reply With Quote
Old 21-12-2016, 16:23   #5
Registered User

Join Date: Jul 2014
Posts: 136
Re: Inventory/storage location spreadsheet

I think an inventory db would be a great idea. I'd like a copy. I see many spreadsheets for this, but they seem very cumbersome. I make simple MS Access databases for all sorts of things, but I can't seem to pull together the creativity to design one for stores inventory.
scherzoja is offline   Reply With Quote
Old 21-12-2016, 18:18   #6
Senior Cruiser
 
StuM's Avatar

Cruisers Forum Supporter

Join Date: Nov 2013
Location: Port Moresby,Papua New Guinea
Boat: FP Belize Maestro 43
Posts: 7,310
Re: Inventory/storage location spreadsheet

OK, version one is up. All comments/suggestions for improvement welcome.

http://www.camcopng.com/download/BoatInventory.zip
Attached Thumbnails
Click image for larger version

Name:	Inventory.jpg
Views:	241
Size:	38.1 KB
ID:	138343  
StuM is offline   Reply With Quote
Old 21-12-2016, 19:02   #7
Registered User
 
sy_gilana's Avatar

Join Date: Jan 2005
Location: On board
Boat: Van de Stadt 50'
Posts: 1,160
Send a message via Skype™ to sy_gilana
Re: Inventory/storage location spreadsheet

Hi, several years ago we set up a system, actually in 1998. We put it on our blog, and it was adopted by the USN sailing squadron at Annapolis.

Spreadsheet is good enough, and it allows you to sort by name or location, also, if you know where something should be, on the SS you can see what else is in the compartment.

The naming conventions are important.

go to gilana.org and then on the left there is a button called "MORE" click that and then you will find the inventory system.

It has evolved since then, but there are now meds and personal items so I don't want to share the latest version.

OH in OpenCPN you can DL the PI called Logbook konni, it also has an inventory database, but as I said, its handy to look at the list, not as a report form or input form.
__________________
Tight sheets to ya.
http://gilana.org
sy_gilana is offline   Reply With Quote
Old 21-12-2016, 19:44   #8
Senior Cruiser
 
StuM's Avatar

Cruisers Forum Supporter

Join Date: Nov 2013
Location: Port Moresby,Papua New Guinea
Boat: FP Belize Maestro 43
Posts: 7,310
Re: Inventory/storage location spreadsheet

Just updated the database with an additional screen to view/sort/filter everything.
Attached Thumbnails
Click image for larger version

Name:	Inventory.jpg
Views:	198
Size:	28.8 KB
ID:	138345   Click image for larger version

Name:	Inventory2.jpg
Views:	195
Size:	29.2 KB
ID:	138346  

StuM is offline   Reply With Quote
Old 21-12-2016, 20:06   #9
Registered User

Join Date: Jul 2014
Posts: 136
Re: Inventory/storage location spreadsheet

Thanks Stu,

I like this. I'll mess around with it.
Only problem I found so far was the right column of Inventory, when I leave a new field blank, I get error stating the primary key can't be null value.

I understand that the means and that it's totally user-error, but it's an easy error to make and only way to get out of the 'can't be null' error is to close the db.

One has to enter data in that field (circled in red in attached pic), then go down to specify where it's located, qty, and description. If you add that empty field, specify loc, qty, desc, then go back and try to enter what the item is, you'll get error.

Any way to make it so we can go back up to that primary key field and enter data?

It's not a big deal, but for anyone who does not understand what the error means, it'll be really frustrating.
Attached Thumbnails
Click image for larger version

Name:	inventorydb.jpg
Views:	149
Size:	225.5 KB
ID:	138350  
scherzoja is offline   Reply With Quote
Old 21-12-2016, 20:26   #10
Senior Cruiser
 
StuM's Avatar

Cruisers Forum Supporter

Join Date: Nov 2013
Location: Port Moresby,Papua New Guinea
Boat: FP Belize Maestro 43
Posts: 7,310
Re: Inventory/storage location spreadsheet

Ok, first bit of "user proofing" done


(You didn't need to exit the application, just hitting Esc after acknowledging the error message should have done it)

Please download the new version from the link above.
Attached Thumbnails
Click image for larger version

Name:	NoItem.jpg
Views:	140
Size:	9.3 KB
ID:	138352  
StuM is offline   Reply With Quote
Old 21-12-2016, 20:32   #11
Senior Cruiser
 
StuM's Avatar

Cruisers Forum Supporter

Join Date: Nov 2013
Location: Port Moresby,Papua New Guinea
Boat: FP Belize Maestro 43
Posts: 7,310
Re: Inventory/storage location spreadsheet

Just realised that you will get a similar problem if you add a storage loc when there is no selected compartment and with item and no selected item type. (No error, just lost entries. I'll publish a fix shortly and advise when up.

Edit: OK, that one is resolved too. New version at link above.
StuM is offline   Reply With Quote
Old 21-12-2016, 20:41   #12
Registered User

Join Date: Jul 2014
Posts: 136
Re: Inventory/storage location spreadsheet

That works better, thank you Stu; the error is more understandable.

How about an export function from the "List Contents" output screen to create shopping list (instead of revealing the List Contents query to export?

Export to *.csv or *.xls (preferred) to create a "shopping list"

Maybe export when qty < x amount? Where x is a min. qty threshold for restock. Thresholds *might* be different for food vs engine spares, vs other categories. I had food in mind with < x amount

It might be better to export without conditions, then in the SS, add a column for reorder.

In that column, add formula: =IF(C2<2,"Reorder","Full") to flag any qty < 2 with Reorder, the sort by Reorder to get my shopping list

I'm not sure which will better for the general user. I'll sleep on that.

Thanks again Stu, I think this will be very useful.
scherzoja is offline   Reply With Quote
Old 21-12-2016, 21:35   #13
Senior Cruiser
 
StuM's Avatar

Cruisers Forum Supporter

Join Date: Nov 2013
Location: Port Moresby,Papua New Guinea
Boat: FP Belize Maestro 43
Posts: 7,310
Re: Inventory/storage location spreadsheet

I've added the ability to select/deselect items in the List Contents screen and to export the selected items to a spreadsheet on the Desktop.

New version is up at the link above.
Attached Thumbnails
Click image for larger version

Name:	ExportFilter.jpg
Views:	128
Size:	14.2 KB
ID:	138353  
StuM is offline   Reply With Quote
Old 22-12-2016, 08:41   #14
Registered User
 
sy_gilana's Avatar

Join Date: Jan 2005
Location: On board
Boat: Van de Stadt 50'
Posts: 1,160
Send a message via Skype™ to sy_gilana
Re: Inventory/storage location spreadsheet

Stu, that looks really good, but I won't be able to use it unfortunately, linux rules here.

One addition you could make is to create a + and - button when the individual item is listed to enable reducing or increasing inventory by one. With a SS our keystrokes are cumbersome...

Example: When we do a tomato pasta dish, we will get into locker xxx, and take one can of tomato paste. We open the SS, search for "tomato" find the item, select the qty cell, and subtract one, save and exit. (we do this for every item)

Another nice addition would be the standard EOQ and Reorder point formulas from production/inventory management science.
__________________
Tight sheets to ya.
http://gilana.org
sy_gilana is offline   Reply With Quote
Old 22-12-2016, 12:30   #15
Senior Cruiser
 
StuM's Avatar

Cruisers Forum Supporter

Join Date: Nov 2013
Location: Port Moresby,Papua New Guinea
Boat: FP Belize Maestro 43
Posts: 7,310
Re: Inventory/storage location spreadsheet

Quote:
Originally Posted by sy_gilana View Post
Stu, that looks really good, but I won't be able to use it unfortunately, linux rules here.

One addition you could make is to create a + and - button when the individual item is listed to enable reducing or increasing inventory by one. With a SS our keystrokes are cumbersome...

Example: When we do a tomato pasta dish, we will get into locker xxx, and take one can of tomato paste. We open the SS, search for "tomato" find the item, select the qty cell, and subtract one, save and exit. (we do this for every item)

Another nice addition would be the standard EOQ and Reorder point formulas from production/inventory management science.

Changed "List contents of all Storage Locs" to "List All Inventory Items" (previous version didn't show items in list if you didn't have them allocated to any storage location)

Added buttons to List screen to change sort order between "Locations" and "Items".

Added +/- buttons for each item storage loc.
Added "Min Qty" field for each item.
Added "Select Items Below Minimum Quantity" to List screen.
(I think EOQ and ReOrder point are a bit overkill for this simple application )

New version up at the link above.
__________________

StuM is offline   Reply With Quote
Reply

Tags
storage

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are Off
Pingbacks are Off
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Inventory and Provisioning Spreadsheet The Boat Galley Provisioning: Food & Drink 7 16-01-2011 06:37
Excel Provisioning Spreadsheet MV Provisioning: Food & Drink 52 27-07-2009 15:14
Spreadsheet for Boat-Buying Comparison? outthere09 The Sailor's Confessional 7 25-03-2009 14:03
Homework using a spreadsheet Redbull addict Monohull Sailboats 6 15-06-2007 10:03



Copyright 2002- Social Knowledge, LLC All Rights Reserved.

All times are GMT -7. The time now is 09:05.


Google+
Powered by vBulletin® Version 3.8.8 Beta 1
Copyright ©2000 - 2018, vBulletin Solutions, Inc.
Social Knowledge Networks
Powered by vBulletin® Version 3.8.8 Beta 1
Copyright ©2000 - 2018, vBulletin Solutions, Inc.

ShowCase vBulletin Plugins by Drive Thru Online, Inc.