Thursday, October 25, 2012

EXCEL FOOD PREPPER LIST

    The other day I made a post and Garand Girl posted a reply and showed interest in what the EXCEL spreadsheet was all about. Instead of sending individual responses to eveyone in the blogisphere, I decided to just do a post and explain what I have done in my humble little world. As I reposted to GG, if you would like a blank copy, email me and I can send you a copy, or you can build you own in EXCEL. I will make this statement, I made it my way and it is tailored for me, so if you want to change stuff around or move things here or there, that is completly up to you. Way I look at it, keeping an inventory of what you have stocked up can be a pain, so my motto, "Work Smarter, not Harder" Right? These numbers are not my exact list, remember, I live by OPSEC!!! The items are things we eat so those items are correct. Since the recommended daily caloric intake per person is 2000 CPD, that is how I calculated my total number of days in stock.

 
 
If you look in the picture, you will see I have canned goods and dry goods seperate. Why, just did it that way. The canned goods also have columns labeled "cans, calories, servings, calories per can and total calories". First column is Corn, put the total number of cans you have on your shelf. If you havent figured out that nutrition labels on food items, they are all pretty much standard and should be almost identical on every brand name. Next column lists the calories-per-serving in each can. Next column is calories-per-can. Final colum is total calories of all the Corn. The Excel formula for this line item is: "Click on column "H", row "2". H2 is where you want the total calories for canned corn to be displayed. Column "H" is the SUM column
Example                      
=D2*G2, translated as, =(SUM) Column "D", row "2" X(Multiply)  Column "G", row "2" 
 
Each item below Corn has the same formula except for the row number, it will be different. So the tenth row would be =D10*G10
 
Dry goods are setup simularly except I only use the pounds(LBS) to tell me how much weight I have in stock. Since most dry goods come in various sizes of product compared a base size for a canned good on the store shelve, I take the total number of servings per package and add them to the exisiting serving number I have in the list and multiply it by the calories per serving to get the total number of calories for that item. Formula for dry oatmeal is:
"Click on column "P", row "2". P2 is where you want the total calories for oatmeal to be displayed. Column "P" is the SUM column 
 
Example
=N2*O2, translated as, =(SUM) Column N, row "2" X(Multiply) Column "O", row "2"
 
Same as before, each item below oatmeal will have same column, just different number.
 
The totals at the bottom reflect total calories for canned goods which are 495,111 and dry goods are 1,717,248 which equals 2,212,359 and would last one person 1106.2 days on a 2000 calorie diet.
 

 
 This next picture shows basically the same as what I have written above, except it is for Dehydrated/Freeze Dried and for Ready Made Meals such as pouches from Mountain House, Wise Foods, Etc, Etc. Same formula principle applies, total calories multipled by servings equals total calories. When looking at the multi-colored section, I added both dehydrated/freeze dried and Ready-made-meals together for the final row.
 
    The multi-color section is for daily caloric intake based on a family size of four. Looking at the dark green  is depicts 8000 CPD for a family of four. Now, I have two small boys, they do not eat 2000 calories a day, even when they pig out. But if they did, it would show I have 320.8 days of food in stock. Another issue to consider is I personally dont eat 2000 calories a day. I operate just fine on one meal a day, been doing it for along time. So if we all only eat 4000 calories a day, we have enough for 641.6 days. The formula in this section is the totals of all the canned goods, dry goods, dehydrate/freeze dryed and ready-made meals divided by the caloric intake (8000-3000).
 
This section's formulas are more elaborate to write it out, if you click on, say the canned goods number under 8000, and look to the top line (formula bar, a.k.a "fx") if will show you the formula.
 
For people who like pretty pictures of bar graphs, here is a brief visual of what you inverntory is saying.
 

 
 
The final picture is of the Food Deduction Sheet. This sheet is important. If you rotate items out of you inverntory, you need to account for them. "IMPORTANT"!!!, if you want your inventory to be accurate as possible, you have to do this. I have held a DRB with JUGM over this, she now has it posted on the Fridge. If you remove many items from your list, well then you will have to do a count on your inventory. That sucks, trust me. I have been storing food for about 6 years now, I will not do that again. All you have to do is list the item you take, number of items and the date. When I update my list, I save the list with the date I last updated it. Example, FOODSTORAGE_OCT12012. Next update say would be FOODSTORAGE_NOV12012.
 
 
 
    If you have Microsoft Excel and have never used it, trust me, it is easy to learn. It is helpful for many things, budgets, Food inventory, Statistics, phone numbers. DO NOT BE INTIMINATED BY IT. If you would like a copy of this, shoot me an email and I will send you a blank copy with instructions of how to add stuff or delete stuff.
 
Senior 


 
 
 
 



33 comments:

  1. Nicely done, sending this link to some friends who do the same thing.

    ReplyDelete
  2. Please send it out and share the process.

    ReplyDelete
    Replies
    1. Senior Can I get a copy of this spreadsheet. It is outstanding. tstrodtbeck35@clearwire.net

      Delete

      Delete
  3. I cannot for the life of me find your email address. I've gone so far as to search the page for "email" "contact" etc and nada is showing up even though I'm positive that I'm just being blind. *sigh*
    You can email me at garandgal at yahoo dot com.

    ReplyDelete
    Replies
    1. Will send a copy to you today.

      Delete
    2. Senior Can I get a copy of this spreadsheet. It is outstanding. tstrodtbeck35@clearwire.net

      Delete
  4. I would like a copy of it as well. Thank you for taking time and offering such a great idea. BLPen163@gmail.com

    ReplyDelete
    Replies
    1. Sent last night while in class, let me know if you need help.

      Delete
  5. Senior please send me a copy,,robsrangers11@yahoo.com

    When you find time...thanks.

    ReplyDelete
  6. Holy moly this is awesome!!! I have never used Excel, but TSM is skilled. This is a great post!

    ReplyDelete
  7. Also don't see a way to contact you on the site. Good post. Found you via A Girl and Her Gun and am now following you on my RSS reader. Please email me a copy of this at epost9 "the at symbol" gmail.com

    Thanks!

    Groundhog

    ReplyDelete
  8. Moseyed over here from AGirl's site. Funny thing is, the wife and I were talking about something similar to this just this past weekend, while taking those first tentative steps to get our 3-day truck-carry-kits and BOB's prepared. Not the first time that something we've talked about (relating to being prepared for an emergency) has popped up on a blog or link or something within the next three days or so. I'm starting to wonder if God is prodding us....

    Thanks for posting this! Gonna go back and read your older posts, too, but if its not terribly inconvenient, could we get a copy of the spreadsheet, as well? lowrykirk at yahoo dot com Thanks!!!

    ReplyDelete
    Replies
    1. Thanks for stopping by, file sent via email.

      Delete
  9. I found you from A Girl and Her Gun. Would you send me a copy? tick123tock 'at' gmail.com

    ReplyDelete
  10. can u please send me the file on kgrixti@gmail.com

    ReplyDelete
  11. cna you send me a copy to tech@southernindianapreppers.com

    ReplyDelete
  12. Hello, could you send me a copy as well at sawan_202@yahoo.com?
    Many thanks
    Sawan

    ReplyDelete
  13. Hello, could you send me a copy at rdbertch@mchsi.com Thanks

    ReplyDelete
  14. Please send a copy to cjrowelldallas@gmail.com

    Thank you

    ReplyDelete
  15. Please send a copy to cjrowelldallas@gmail.com

    Thank you

    ReplyDelete
  16. Please send a copy to cjrowelldallas@gmail.com

    Thank you

    ReplyDelete
  17. i would like a copy please. Very cool. fueltrac.dean@gmail.com

    ReplyDelete
  18. I would like a copy please. Very cool. fueltrac.dean@gmail.com

    ReplyDelete
  19. The only addition I would add is a place to record expiration dates.. to keep better track of rotation

    ReplyDelete
    Replies
    1. I had thought about adding a column for that but the things we buy are used often and all I do is put newer items to the rear and move older items to the front. I try and keep items that I know the whole family will eat. Thanks for the post.

      Delete
  20. Hey Senior, Master here. Glad to find your blog, I'll be following you. I'd be very interested in a copy of your food prepper list. Lot's of thought in this one!!!

    ReplyDelete
  21. Hey Senior loved reading this can you send me a copy of this spreadsheet please.. chrisdraven@sky.com

    many Thanks

    ReplyDelete
  22. Could you please send me a copy of the spreadsheet to ccmedic76@comcast.net Thanks

    ReplyDelete
  23. I love this! Can you please send me a copy? judoprepper@gmail.com

    ReplyDelete
  24. I was looking for a food inventory worksheet and your's looks to fill the bill. Could you send me a copy? Thanks, charles@dcmccollum.com

    ReplyDelete