Author Topic: Donaldj's Prep Inventory in Excel  (Read 11130 times)

Offline donaldj

  • Dedicated Contributor
  • ******
  • Posts: 1382
  • Karma: 87
    • Keep and Bear, LLC
Donaldj's Prep Inventory in Excel
« on: January 09, 2010, 07:56:23 PM »
I mentioned in the Product Ideas thread that an inventory management program built with preppers in mind might be a cool idea. In support of this, I offer how I’ve been doing it in Excel. It is still a work in progress so please pardon some functional deficiencies.

First, each item in my preps needs some type of identification to track it. I want to be able to know where it is and how much of it I have. To track it, I chose an “identifier” based on when I obtained it, in the format YYYYMMDD_XX where XX is simply a counter. If you tend to bulk shop and need more than 99 items logged at a time, then add as many digits to the counter as you need. Most things will need that number written on them, but some may not.

Example:I have 4 inverters. If one goes bad, I can note which one with this number, and remove it from my inventories until fixed/replaced. For some things, like a firearm, you may just want to use the item's pre-assigned serial number.

As an example, here are some dehydrated banana chips I bagged up today. I will have 2 bags total, so my identifier is 20100109_01 and 20100109_02. My trusty Sharpee marker is used to identify the bag.



Now, I go to my prep area with all my goodies and find a container to put them in. In this case, I found a 5 gallon bucket that I have labeled #17. All my buckets and bins have numbers, and in a minute you’ll see why. If I have a lot to do, I’ll take some scrap paper and jot notes as to what went where. In this case it was just 2 bags of banana chips so I didn’t.



(Not related to this topic, my preps shown here are the buckets I've managed to fill with rice, dehydrated potatoes, apples, pears, mangos, etc. Each has between 4-6 mylar bags and O2 absorbers in them. The soda bottles are all water. The one bin on the 3rd shelf is ammo. I still have some comic books and Christmas stuff to remove from this area too...)

Next, I mosey upstairs to my computer. I do not need to mosey. I can trudge, meander, walk, or if I'm so inclined, sashay. I usually never sashay. I opened my Excel program with my preps.  I have one worksheet (tab) called “Line Item Preps”.  My current columns are Item, Medium, ID#, Container#, Container Type, Category, Qty in entry, Units, Store Date, Expiration Date, Notes.

Thus, my new entries will look like this:



This page is very important, as it tells me what I have and where things are.  I can sort by container, thus giving me an inventory per bucket/bin (which I print and keep on the shelf in the basement), and I can also sort by item type. In my Expiration Date column, I use conditional formatting for these dates. If it’s 31 days before the expiration, Excel will turn the cell yellow. If it is over the Expiration Date, Excel will turn the cell red. This lets me know I need to use something before I think it will go bad.  Also by doing this, I can see everything by date and adhere to a First In First Out queue by identifying the item and location. So, if Mrs. Donaldj says “Baby, I need a packet of rice.” I sort, find my oldest packet, and know right where to go get it.  One caution on doing this is to enter each type of food item exactly the same way every time you enter it. So, “Rice, white” needs to be written that way each time. Not “White Rice”, “white rice”, “rice, dried white”, or anything else. This way, when Excel looks for stuff, it knows what to add up.

I can add any type of item to the Line Item Inventory. I need to enter my BOB stuff in there, and it would look like:

Can O'Whoop-Ass     n/a     n/a     BOB     Backpack     Weapon     1     ea     n/a     n/a


As you use items, DELETE their row in the spreadsheet.


In another tab, which I have labeled “Summary Sheet”, it lists the item I have (exactly the way written before), and sums up the quantity I have. This is meant as a summary, not the “nuts and bolts” of adding to and removing from, my inventory.

The formula I use to add from my “Line Item Inventory” page is this:

Code: [Select]
=SUMIF('Line Item Inventory'!A:A,A82,'Line Item Inventory'!G:G)

So, whatever the text in Column A is, it will do a quantity summation of what you have in the Line Item Inventory.

My partially completed summary readout looks like this:




The things I need to add are actual values for target quantity for most of my stuff, and various events I’m prepping for. I also need "Reorder" quantities.




In the Product Ideas thread:

http://thesurvivalpodcast.com/forum/index.php?topic=10578.0

I mentioned a way to check various events, and the spreadsheet would then determine how your inventories meet those needs, for what duration, and for how many people. The way I would like to see the program working is you have an "Items" page. This would be a very, very long list of every item commonly thought of as prep items. Each line would also have a field for customization. In Excel I am planning on managing this with an item name, and "item specific detail" column.  In columns C-whatever, you have each type of situation you can think of, and can add your own.  So, in Column C, you would have TEOTWAWKI. You then go down column C, and for each item you enter a 1 or 0 if the item  applies to that scenario.  Column D might be a power failure, and you'd customize your 1's and 0's to meet that regarding each item. You can add your own columns, such as the Great Texas Armadillo Famine, and put 1's and 0's for whatever items you think you need to get through this very serious situation.

Then, on another page, you simply add everything you have by line, like my Line Item Inventory tab. The program would then go in and meter your stuff to projected quantities needed.

Lastly, it would act as an inventory system, flagging low items, expiration dates, etc.


Hope this helps someone!

Offline Cool Blue

  • Survivalist Mentor
  • *****
  • Posts: 917
  • Karma: 18
Re: Donaldj's Prep Inventory in Excel
« Reply #1 on: January 09, 2010, 08:21:45 PM »
wow, pretty impressive!

Unfortunately I'm much lazier than you...

Offline donaldj

  • Dedicated Contributor
  • ******
  • Posts: 1382
  • Karma: 87
    • Keep and Bear, LLC
Re: Donaldj's Prep Inventory in Excel
« Reply #2 on: January 09, 2010, 08:26:19 PM »
Well, I've only been prepping for a few months, and wanted to get an inventory system in place so I could see where I was at vs my expectations. I thought it was too important a thing to have a false sense of readiness over.

I also thought starting early and developing a forward-thinking robust system would mean I didn't have a major project ahead of my if my inventories got to a substantial level before I decided to quantify everything.

So, yeah, I'm lazy. This is my way of heading it off at the pass so I can continue to be lazy.  =)


Offline P_Coltrane

  • Survivalist Mentor
  • *****
  • Posts: 716
  • Karma: 35
Re: Donaldj's Prep Inventory in Excel
« Reply #3 on: January 09, 2010, 09:54:04 PM »
You might consider adding purchase price and purchase date to track inflation. Some times prepping can cost you, some times it can save you. ;)

Offline KYdoomer

  • Dedicated Contributor
  • ******
  • Posts: 1976
  • Karma: 71
  • Zen Gardener
Re: Donaldj's Prep Inventory in Excel
« Reply #4 on: January 09, 2010, 11:34:50 PM »
Donald

It is eerie how several of us here think along the same paths.

Here's what I use.  A little bit less fancy than yours.  I just use mine for food.  I have a formula at the bottom where I can punch in ration calories for our family and get a feedback on number of days.  I can sort by expiration date which helps me rotate.  I also have a formula set up that calculates the total calories based on servings and calories per serving. 



J

Offline gigaJack

  • Senior Survivalist
  • ****
  • Posts: 206
  • Karma: 68
  • Click the website icon to view my survial summary
    • The Survival Summary
Re: Donaldj's Prep Inventory in Excel
« Reply #5 on: January 11, 2010, 01:27:47 PM »
You showed me yours now I'll show you mine.

http://survivalsummary.wordpress.com/2010/01/11/food-storage-inventory-spreadsheet/

I have only linked to my food storage spreadsheet before but now I have made an in dept blog post at "The Survival Summary" with pretty pictures for all to see.

gigaJack

Offline donaldj

  • Dedicated Contributor
  • ******
  • Posts: 1382
  • Karma: 87
    • Keep and Bear, LLC
Re: Donaldj's Prep Inventory in Excel
« Reply #6 on: January 11, 2010, 01:30:42 PM »
You showed me yours now I'll show you mine.

You're implying I haven't already looked at your a hundred times and have learned a lot and been very impressed.   ;D

I just started into the preparedness about 6 months ago, so yours was overwhelming at the time. That's when I started doing mine my way. Each time I read yours it comes a little clearer. Just gotta wait for us noobs to catch up. 

Thank you for sharing!

D

Offline idelphic

  • I Zgjuari I Dynjasë
  • Dedicated Contributor
  • ******
  • Posts: 1903
  • Karma: 44
  • Theoretical Conceptualist - Avatar by Ada
Re: Donaldj's Prep Inventory in Excel
« Reply #7 on: January 11, 2010, 03:22:06 PM »
I've been working on my inventory sheet for a bit also,.. while I don't have it to share, I do have a question....

Cal vs ounce..  Does it really matter on the total cal?  For some the #10 can is a good idea, but not for my house.  50% or more would go bad before we used it... not to mention some of what is in the #10 can would not be in the house to begin with (freeze dried etc)...


Offline cartpusher

  • Survivalist Mentor
  • *****
  • Posts: 821
  • Karma: 32
    • Survival Gear Bags
Re: Donaldj's Prep Inventory in Excel
« Reply #8 on: January 24, 2010, 11:23:09 AM »
DonaldJ - I am not a spreadsheet pro and I can't get this code to work:

=SUMIF('Line Item Inventory'!A:A,A82,'Line Item Inventory'!G:G)

In my head I know the "code" I want to write, but I don't know the actually code, and I can't get your example to work.  So say I have 3 different entries for White Rice on my line item inventory page, and then on the summary page I want a total for White Rice... how do I make that happen?

Or can you post a blank version of your spreadsheet that we could download?

Thanks



Offline donaldj

  • Dedicated Contributor
  • ******
  • Posts: 1382
  • Karma: 87
    • Keep and Bear, LLC
Re: Donaldj's Prep Inventory in Excel
« Reply #9 on: January 24, 2010, 01:34:34 PM »
I'll be glad to email it to you. It is still in progress though. PM me an email addy you'd like me to send you, along with the version of Excel you're using.

Ensure in your sheet your tabs are named Line Item Inventory and Summary Sheet with no misspellings, or that the code has been modified to reflect the tabs you're using.

Ensure that every time you enter the name of an item in the Line Item Inventory tab that you type it in exactly the same way. Thus, "Rice, White" should always read just like that, or Excel will think it 2 different things.

In the summary sheet, type in "Rice, White" on column A, then put that formula in column D.

Code: [Select]
=SUMIF('Line Item Inventory'!A:A,A82,'Line Item Inventory'!G:G)

This is essentially what the code is doing:

=sumif(range to look through, for this item, add the value here)

So,

"for this item" should be the column and row you're referring to. If Widgets are in Summary Sheet cell A2, and you wanted to tally Widgets, it would read =sumif('Line Item Inventory'!A:A, A2, 'Line Item Inventory'!G:G).

"range to look through" is where to look for the item you're after. In this case, it's 'Line Item Inventory', all of column A.

"add the value here" is what it adds when it finds it. In this case, it's quantity, listed in all of column G.

So, it's looking at Summary Sheet A2 for the item, scanning all of column A on the Line Item Inventory for that item, and if it finds it, it adds the value in cell G to its ongoing sum.


On your spreadsheet, ensure you're looking at the right column for that particular entry, in my case it is column G on Line Item Inventory.

Where it says A82 in the code, make sure that line refers to the row you're on, and is a variable, not a fixed reference.  A82 just refers to Column A Row 82.

Offline teton traveler

  • Senior Survivalist
  • ****
  • Posts: 217
  • Karma: 14
Re: Donaldj's Prep Inventory in Excel
« Reply #10 on: January 24, 2010, 04:41:48 PM »
That is really well done. We have done something like that, but not that well organized. One idea I had that I don't know if you are doing, is when you use something, instead of deleting the item, you could cut and paste it into a tab that is a list of things to buy. That whay when you want to go to the store or buy bulk food, you know what you have used and what you need to replace first.

Offline donaldj

  • Dedicated Contributor
  • ******
  • Posts: 1382
  • Karma: 87
    • Keep and Bear, LLC
Re: Donaldj's Prep Inventory in Excel
« Reply #11 on: January 24, 2010, 05:15:41 PM »
That is really well done. We have done something like that, but not that well organized. One idea I had that I don't know if you are doing, is when you use something, instead of deleting the item, you could cut and paste it into a tab that is a list of things to buy. That whay when you want to go to the store or buy bulk food, you know what you have used and what you need to replace first.

That there is a good idea.

I had originally thought of tackling it from another approach: On the summary sheet I was going to incorporate a Reorder quantity and a "Maximum" quantity (reorder to this quantity), and thus allow my inventory to oscillate within a min/max range.

However, your idea of putting used line items on another sheet also allows me to track usage, so I can measure rates of consumption as well!  So, I'll be doing both when I get this thing up and running with all my inventories.

Thanks for the suggestion!
Don

Offline teton traveler

  • Senior Survivalist
  • ****
  • Posts: 217
  • Karma: 14
Re: Donaldj's Prep Inventory in Excel
« Reply #12 on: January 24, 2010, 06:09:01 PM »
No problem. You have given me some good ideas on how to track our supplies. I think what I will do when I get mine organized is print out everything and have it easily accessible. Then if I take something out, I can just mark it on the paper, or if I buy something I can just write it down like what Jack suggests. Then, when I have time I can take those sheets and just update my spread sheet and periodically print out new ones. It just makes it so I don't have to open my computer and go to the file every time I do some little thing with the supplies. Keep us updated on your progress

Offline donaldj

  • Dedicated Contributor
  • ******
  • Posts: 1382
  • Karma: 87
    • Keep and Bear, LLC
Re: Donaldj's Prep Inventory in Excel
« Reply #13 on: January 24, 2010, 06:13:07 PM »
I had one PM asking me about how to incorporate a checklist into this. In my opinion, the summary sheet IS a checklist. So, if you're using a version of this, everything on your checklist should go on the summary sheet, for any and all categories of things you consider part of your preps. If you want to make a tab for each category in your preps, it can still easly reference the Line Item Inventory tab using that same SUMIF formula. The advantage there would be very printable versions of each checklist category, with using what I think is the setup's strength, the Line Item Inventory.


Hope tis helps!
Don

Offline BerserkerPrime

  • Ice Humping Polar Bear
  • Moderator On Leave
  • Survival Demonstrator
  • *
  • Posts: 2074
  • Karma: 75
  • God, Guns and Oil!
Re: Donaldj's Prep Inventory in Excel
« Reply #14 on: January 25, 2010, 09:36:04 AM »
+1!!  Damn, that is a great product Donald!  I too was a bit intimidated by GigaJacks's list(no office Jack-your just very, very thorough! ;D). When I label my 5 gl buckets, I don't put calories, just how many servings of product per person.  This allows me to easly count out how many "meals" per person I have on hand.  What is your thoughts (anyone) on changing the calories to servings tab?

Thanks for a great product!

BP

Offline donaldj

  • Dedicated Contributor
  • ******
  • Posts: 1382
  • Karma: 87
    • Keep and Bear, LLC
Re: Donaldj's Prep Inventory in Excel
« Reply #15 on: January 25, 2010, 09:53:43 AM »
Thank you for the praise!

I don't have calories as my units, I have pounds. And to be clear, my units are "wet pounds" before dehydrating. So, if I buy 20 pounds of apples, I list it as 20 pounds and not a post-dehydration weight.

It would be a simple matter to convert in Excel, to display the units as calories. Just add a column, and multiply the pounds by a calories per pound value (probably easily found on the internet), to get a calorie value.

The exception is rice, which comes in a dry state.

Take the spreadsheet and Make It Your Own.   ;D  If you have good ideas, I hope you'll post your mods to it for the rest of us!

Don

Offline inthego

  • Survivalist Mentor
  • *****
  • Posts: 762
  • Karma: 18
  • No man is free who is not master of himself..
    • My Biz web site
Re: Donaldj's Prep Inventory in Excel
« Reply #16 on: January 25, 2010, 07:47:14 PM »
Thanks!  +1, more if I could...

 ;D

Offline Lowdown3

  • Survivor
  • ***
  • Posts: 126
  • Karma: 15
Re: Donaldj's Prep Inventory in Excel
« Reply #17 on: January 26, 2010, 08:18:43 AM »
Old fashioned pen and paper work well also. Use a wire bound notebook so that you aren't losing pages. Some sort of inventory is important as people tend to think they have more than what they actually have. It took me years to get around to doing a full inventory. When I did I found I had way too much beans to rice- like 10 to 1 ratio! I "ass"umed that I had XXXX lbs. of beans when in reality I only had XXX number of beans. Embarrassing now, more than just embarrassing if I hadn't of caught it till later.

BTW, looking at the pics and assuming you have water in those soda bottles- you want to put those on the ground or on the bottom shelf. Plastics of that type will bio degrade over time, not as quick as milk or water jugs but they will bio degrade never the less. Looked like buckets below them, which are probably pretty go to go as far as keeping stuff dry, but nevertheless, STUFF HAPPENS.

I could tell you about the two hundred pounds of rice I had to throw out one time because of some water jugs stored above them.... That sucked.

Stupid stuff happens to things in storage, BTDT, seen the broken buckets, waterlogged gear, etc.

Offline donaldj

  • Dedicated Contributor
  • ******
  • Posts: 1382
  • Karma: 87
    • Keep and Bear, LLC
Re: Donaldj's Prep Inventory in Excel
« Reply #18 on: January 26, 2010, 08:37:34 AM »
Thank you for the advice Lowdown! I started with pen n paper in a ledger book, but wanted greater data accessibility, hence the spreadsheet. I definitely keep a hardcopy on hand!

For the water, I chose that shelf so I didn't have to bend down to get the water.  ;D   The water is a lot heavier (most of the 2 litre bottles are in pallets) than most of the 5 gallon buckets. The buckets are sealed, and each contain multiple individual 1 gallon mylar bags of various things, so water ingress is not a terrible issue. I wanted the water to be conveniently reachable so if there's a Bug Out, I could optimize what my wife would be able to handle as well.

Also, this is in my basement, and there is no daylight entering (I have the windows blocked because it's also my photo studio down there). The only lights are incandescent bulbs, and at most a couple hours a week. I figure I have 10 years or so with these bottles before I return them for 10 cents each (MI deposit). I'll start rotating them out in 5-8 years.

My biggest concern is my little boy. As he gets older, he may become fascinated with this stuff and get into it. I might be sealing off that area with some drywall and a locking door in a few years. So, your "stupid stuff happens to things in storage" is well heeded!   :D
« Last Edit: January 26, 2010, 08:40:29 AM by donaldj »

Offline Nexquietus

  • Prepper
  • **
  • Posts: 17
  • Karma: 0
Re: Donaldj's Prep Inventory in Excel
« Reply #19 on: January 26, 2010, 11:47:38 AM »
Man, look long enough and what you have put off someone has already done.  I have been thinking about putting together a Excel sheet for my preps, but had no idea where or how to start.  Great ideas.  I especially like the summary page.  I have messed about with excel making multiple pages that look at each other and such, but this is simple and efficient.  I must admit I intend to steal this... as soon as I am as organized as you... ;D

jim

Offline CountryRootsCityJob

  • Survivalist Mentor
  • *****
  • Posts: 491
  • Karma: 10
  • Yeehaw :D (Unicycles require little maintenance)
    • Building Rome... as fast as I can!
Re: Donaldj's Prep Inventory in Excel
« Reply #20 on: May 17, 2011, 11:05:49 AM »
Just wanted to say AWESOME!  I like it... I'll have to see if I can't get something like this going soon!
~CRCJ