Food Storage Inventory Spreadsheet

Posted: 11th January 2010 by gigaJack in Food & Water, Storage

Click here to view videos.

Instead of pounds per person method the LDS church used for calculating our food storage needs I came up with a calorie methodology. The Google spreadsheet can be found at The best way you can use this methodology is to get yourself a copy of the spreadsheet – click the “File | Download as | Excel or Open Office”. Once you have a copy, you can edit the sheets with your own data.

While the pounds per person method is good for your long term storage items (#10 cans & 5 gallon buckets), we have found it doesn’t work well when you have 9 shelving units full of every day food items.

The items on the shelves are items we keep on hand that we can use up before they expire. You can view the post and video here. Even if we don’t use all the items before they expire it is nice to know we had them and didn’t need them. We have been doing the store-what-you-eat & eat-what-you-store method of food storage for about a year now. The only items we have had expire so far are dry milk and crackers. My wife is still using the dry milk for bread every couple of days to make bread and it is still going strong. The crackers still taste fine 6 months after their expiration date also. As I don’t have a laboratory I cannot account for their nutritional value. I figure that is what the vitamins I take are for. 😉

Up to a couple of months ago we have always used Sam’s Club to buy our items in bulk. While we still use the club we have been using The Grocery Game.

Here is a video for it on the ol’ YouTuber…

The spreadsheet below simply counts the items on hand, the product, how many servings the can/box states and the calories per serving. All the items below are long term items with the exception of the entry “Food on hand”. We have choose not to inventory every-day-eats in because it will take to long to keep an accurate count on what we have. We have thought of procuring the IntelliScanner with the shopping list/inventory system ($250) to keep everything organized. The idea we came up with is when we are finished with and item we add it to a shopping list. And because we have 1-2 years worth of the item in storage there is not real hurry to stock back up. Every so often for the “Food on hand” I go through each of the items on the shelf and call out servings, calories & count. My wife has a laptop and enters each item on a new line in a spreadsheet. At the end I calculate the total calories and enter the numbers to our storage food spreadsheet.

Weekly(ish) we have been using the grocery game to find great deals on items to stock-up on. This is where we will look at our shopping list for great deals. We are still in the acquiring mode of every day items but has been slowly changing to maintain stock levels.

Here is the fun part. I calculate the amount of calories needed based on the Basal Metabolic Rate, BMR is the base rate of metabolism in a human body. BRM represents the number of calories you would need to consume everyday if you did nothing but sleep. Of course, nobody sleeps for 24 hours everyday, so naturally your daily caloric needs will be higher than your BMR value. But, BMR is the starting point for calculating your calorie needs.

The spreadsheet below calculates the amount of calories a person needs based on the amount of activity (none, light, moderate & athletic). The (days, months & years) of food decreases with an increase in the level of activity. So currently you can say we have between 1.89 – 1.10 years of food.

The “Grams of Protein / Carbohydrates / Fat” columns will show you how much of each you need daily based on the activity.

The next spreadsheet will show liquid. I thought the 1 gallon per person per day was a bit crude. Because we store liquid that we can only drink (soda, Gatorade & juices) I wanted to figure out how long the drinks would last. I took how much we drink on a daily basis now to calculate how long the drinks will last and it currently is 90 days. Now this is not including any drinking water. I added another section to calculate how long water will last. The columns (dog, cat, guinea pig) are how much that animal consumes a day. I took a worst case and assumed that we would have to rehydrate food for each meal from our dehydrated foods stores for the cooking column. Washing body would be at a minimum with using a wash cloth. Washing hair would be done once a week with a gallon of water for each of us (4). Washing dishes would need to be adjusted after we use up our months worth of paper plates/bowls/cups/silverware. We would mostly wash our undergarments twice a week. We bought hundreds of 13 gallon trash bags that can be used inside of the toilet and taken out and buried. We also have the option to use a Vietnam toilet where we could burn the waste. Brushing teeth uses little water. I am going to add rain barrels for watering the garden. The garden can also use all the gray water after washings. I added coffee to the water section and not the drinks section to keep them isolated so I could get an accurate measure of drinks. We have green coffee beans that can be roasted up after our k-cups are consumed.

Overall you can see that the 1 gallon per person per day actually holds pretty close to the estimate. The gallons per day for drinks is 0.94 and the water per day is 3.87 giving me a total of 4.81 / 4 people = 1.2025. I would say that I overestimated the amount of water needed for cooking and that puts us at/around 1 gallon per person per day.

I have worked out the food calculation for the animals in the same manner. The guinea pig has months of food in the basement but it can also eat hay/grasses and scraps from our fruit & vegetables. Thus, the pig doesn’t get a spreadsheet.

I found animals are tough to figure out their food requirements due to the pet food manufactures don’t make it easy to figure out their calories. There should be a small print on how many calories per cup on the packaging material. Once you find it you just have to figure out how much you feed your pet. We usually don’t do a very good job keeping adequate food storage for our animals as we get lazy and don’t want to go to PetSmart to get the monstrous bags for our dog. We rationalize this by saying the dog could eat our leftovers if needed.

We have also just finished a new layout that will allow us to fit 4 more 18″ x 48″ shelving units downstairs. We will use single stacks of food boxes (6 #10 cans in each box) instead of the pinwheel stacking. We will also take the shelf that we have used as the bottom shelf and move it up so we can use the ground space under for totes and buckets.