This page will demonstrate the use of a spreadsheet to calculate nutritional requirements at the country level for a specific year based on based on population and nutritional requirements.Estimates of available nutrition will then be compared to estimates of available nutrition.The model will be kept simple and data will be entered manually. Data from Kenya will be used for the analysis and it will be calculated for 1980, 1990, 2000 and 2009 (the last year for which estimates of nutrition levels from the FAO's food balance sheets. Nutritional requirements will be calculated for energy (kilo calories), protein and fat, which are the three components of the Food Balance Sheet results, as well estimates will be calculated for two micro-nutrients, iron where quantities are measured in milligrams (mg) and Vitamin B12 where quantities are measured in micrograms (mcg). Final quantities will be given in billions of kcal for energy and in metric tonnes for the other component-- this will provide an example for calculating requirements for the different weight standards used. For users unfamiliar with spreadsheets the calculations may initially appear complicated but after you build one or two similar small spreadsheets the procedures will become routine.
Measuring Energy: Energy, when used in a nutritional context, is normally measured in kilocalories (kcal) is the energy required to heat one kilogram of water by one degree centigrade. For more information see http://en.wikipedia.org/wiki/Calorie at Wikipedia.
The metric systems of weights: The model will use the metric system of weights in its calculations. Metric weight measurements are traditionally used to define nutritional requirements for protein, fat and micro nutrients (vitamins and minerals in the diet.) This model will use the gram as it's basic weight. The list below lists the various weights which will be used and their relationship to each other.
For more information on metric weights to to http://en.wikipedia.org/wiki/Metric_weights_and_measures. in Wikipedia.
Sources of Data: The data on nutritional requirements is from the World Food Programme's (WFP) Food Aid Information System (FAIS) available at http://www.wfp.org/fais/nutritional-reporting/requirements. The data is only available in pdf format. An unofficial copy of the data can be downloaded here in xls format which can be used either with Excel or LibreOffice Calc. The data on population is downloaded from the United Nations World Population Prospects http://esa.un.org/unpd/wpp/Excel-Data/population.htm. The data on achieved provision of food by nutritional categories was downloaded from the Food and Agriculture Organization of the United Nations (FAO) FAOSTAT database at http://faostat3.fao.org/faostat-gateway/go/to/download/C/CC/E.
Sample Spreadsheet:The nutritional requirements for Kenya have been calculated for 1961, 1980, 2000 and 2009 for energy, protein, fat, fat, iron and vitamin B12. These are then compared to nutritional resources actually available although data on availability is not available for iron and vitamin B12 which have been included only to demonstrate how requirements are calculated for supplements measured in milligrams and micrograms. The spreadsheet explained below can be downloaded here.
There are two worksheets in the table "Data" and "Analysis" the "Data" work sheet includes only the original data and the table layout while the "Analysis" worksheet contains the completed spreadsheet--the worksheet selection tabs are shown below.
Data Worksheet: The "Data" table is shown below. It shows the nutritional data parameters including population entered into the final design of the layout of the spreadsheet. No calculations have been carried out. The "Daily Requirement" row is taken from the WFP estimated nutritional requirements table while the "Daily Supply" is taken from FAOSTAT data on food supplies and population data from the UN population data base.
Analysis Worksheet: All of the calculations are carried out in the analysis worksheet. If you are unfamiliar with spreadsheets and have downloaded the spreadsheet you will find cell addresses including "$" such as $a$6 or a$6 or $a6. The "$" is used to freeze cells when you are copying data from one cell to another. If you car not familiar with this type of cell notation review spreadsheet training links here.
The sections below detail the form of the equations for calculating the columns in the three sections "Target Requirements". "Actually Available" and "Available%Req."
The graphic below shows the finished spreadsheet. This particular model was developed using Excel 2010 running on Windows7).
Analysis: Far more analysis than this simple table would be required for policy making, however there is enough data to suggest that there is a serious food security problem in Kenya. The data in the "Available%Req" section shows the percentage of estimated nutritional requirements that are met by the identified supply. Because we are dealing with estimated data, it is necessary to be cautious. However, the data suggests that the food supply situation in Kenya has deteriorated to the point that by 2000 Kenya was not meeting its minimum energy needs at the national level even assuming no consumer waste and distribution of food based on nutritional needs not income. However, minimum protein and fat needs were being met.