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:
=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.0I 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!
