Life Support - Menu planner

{Update: values presented here are quite wrong thanks to a pair of errors recently discovered.}

 As part of a larger effort, I have produced a spreadsheet that can be used to see the nutritional values of a menu on a weekly average basis. For a number of reasons detailed below it will be of limited usefulness to most people, but it can be adapted to track one's own diet with some effort.

I release the sheet as CC-SA, free to use but without any warranty express or implied. This does not constitute medical advice. No attribution is necessary, since anyone wanting to use it would have to put in some effort to make it presentable. Fine print after the jump.

Update 25 November 2015:
Broke the menu section into men, women and children (using 90th percentile height / weight).
Added a separate section detailing space and feed inputs for fish and livestock (fish still in progress).
Current version is nutritionally complete, though the fat balance is heavily weighted toward saturated fats as a consequence of using dairy.

Headline 1 from this is that it takes only 9.5m² per person using stacked grow racks. I expect this to increase slightly as I introduce less efficient grains and a wider variety of fruits and greens, but should easily stay within 12m².
Headline 2 is that the animal feed was within 15% of the agricultural waste already being produced, meaning the nutrients added from animal sources requires almost no additional hydroponic support. The difference can be made up with Spirulina from the waste processing system and excess milk.

The downside is that you cannot reasonably include pork or beef unless your population is at least 5,000 or so.
Also, fish turned out to be less space-efficient than chicken after considering pond space for breeders.
Further, far more milk is produced than is necessary; this could be powdered and used in animal feed or could be polymerized into PLA plastic.
------------------------------------------------------------------



 The sheet takes as input the number of 100-gram (3.5oz) servings of each item consumed per person per week, then calculates the totals for each nutrient and compares to a reference diet. This includes the full amino acid profile.
 This is incorporated into the grid with other production values (hydroponic parameters like yield, PAR, preferred temp, days to harvest, etc.) and also generates a total value for the amount of floor space required. It is definitely a work in progress.
 Nutrition data is taken from the USDA. The reference diet is taken from the Institute of Medicine. I use numbers for an adult male, 1.8m / 5'11", 79kg / 174lb; although that is near the high end of the reference guide it is closer to the average in the US. In my experience people doing heavy construction and similar work tend to be physically large; an argument could be made that people sent into space would be chosen in part due to smaller stature for food efficiency reasons, but I prefer to design for the upper end of the spectrum. A comprehensive nutrition planning program would consider a complete population cross-section. To tailor this grid to a different reference diet, see this reference.

 There are many food items missing; the list will grow over time. I've reduced things to the most basic form I can. For example, all wheat-based products are entered as wheat; the other components of a baked item would be accounted for under other items like butter. This makes sense for my purposes because as long as dietary needs are met in the end I only care about how much space is used to grow wheat, not the bagels to cookies ratio.

 My specified diet provides for two animal protein servings per day, three servings of grain (currently all wheat but I'd like to include barley and oats), just under one serving of dairy per day (as cheese or butter at 100g, alternatively as milk at 1L) and the remainder is vegetables and fruits. Baked goods are expected to use sweet potato flour / syrup as an add-in where appropriate. Sweet potato and wheat are the staple starches, with some shelled beans as supplement. Snap beans, snap peas and squash (zucchini / courgette) are the staple vegetables, with a variety of supplementals including radish and peppers for flavor. Lettuce in several varieties is the staple leafy green.

 Near-term work will be looking at other greens (beet tops, spinach, chard, collards, etc.), other grains (barley, oats, possibly qinoa or amaranth) and a broader range of fruits. I need to get a better balance of nutrients, but there's little point in optimizing that until I have more foods in the list. I'd also like to do several menu sets covering adult men and women and children in two age ranges so the menu can take into account the station's composition. I'd also like to compare menus with and without animal protein and one using fish as the sole animal protein to see what that does to other requirements. Lots and lots of work.

 In the long term, I'd really like to see a tool that works as a front-end to one or more databases. Manually entering all this data is time-consuming. It will be helpful for me to get a good idea of the mix of foods to be grown, but for serious use such a planning tool should be adaptive. I would also like to see a comprehensive database of yields gathered from published research, preferably with enough detail to be able to group by experimental characteristics and focus in on high-yield hydroponic numbers. A database like that would be useful for meta analysis studies; if carefully constructed it could eventually encourage researchers to provide certain data in a somewhat standard form. That's an oblique way of saying I'd like to see researchers report their best repeatable yield numbers so they can be used to compare studies. It would be useful to be able to compare two or three strawberry papers by their max yields as a quick indicator of significant differences in their setups. It would also be useful to have an 'average best yield' based on results from several researchers under comparable conditions.