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 microcnutrients, 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.

- 1 Metric Tonne (MT) equals 1,000 Kilograms (Kg)
- 1 Kilogram (Kg) equals 1,000 Grams (gr) or 1,000,000 Milligrams (mg) or 1,000,000,000 Micrograms.(?g)
- 1 Gram (gr) equals 1,000 milligrams (mg) 0r 1,000,000 (?g)
- 1 Milligram (mg) equals 1,000 micrograms (mcg or ?g)
- 1 Microgram (mcg or ?g). This is the smallest measurement used in the model)

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."

- Energy: This is calculated by individual year. The formula is [population X energy daily requirement X 1000 /1,000,000,000]. The result of the first multiplication is multiplied by 1000 because the population data is given in thousands, the division by 1,000,000,000 is because the result is calculated in billions of kCal.
- Protein: This calculation is by individual year. The original data is in grams but the required result is in metric tonnes. The formula is [population X protein daily requirement X 1000/1,000,000] . The 1,000 in the equation is because the population is in thousands and the division by 1,000,000 converts grams to Metric Tonnes.
- Fat: This calculation is by individual year. The original data is in grams but the required result is in metric tonnes. The formula is [population X fat daily requirement X 1000/1,000,000] . The 1,000 in the equation is because the population is in thousands and the division by 1,000,000 converts grams to Metric Tonnes.
- Iron: This is calculated by individual year. The original data is in milligrams but the result is required in Metric Tonnes. The formula is [population X iron daily requirement X 1000/1,000,000,000] . The 1,000 in the equation is because the population is in thousands and the division by 1,000,000,000 converts milligrams to Metric Tonnes.
- Vitamin B12: This is calculated by individual year. The original data is in micrograms but the result is required in Metric Tonnes. The formula is [population X vitamin B12 daily requirement X 1000/1,000,000,000,000] . The 1,000 in the equation is because the population is in thousands and the division by 1,000,000,000,000 converts micrograms to Metric Tonnes.

- Energy: This is calculated by individual year. The formula is [population X energy actually available X 1000 /1,000,000,000]. The result of the first multiplication is multiplied by 1000 because the population data is given in thousands, the division by 1,000,000,000 is because the result is calculated in billions of kCal.
- Protein: This calculation is by individual year. The original data is in grams but the required result is in metric tonnes. The formula is [population X protein actually available X 1000/1,000,000] . The 1,000 in the equation is because the population is in thousands and the division by 1,000,000 converts grams to Metric Tonnes.
- Fat: This calculation is by individual year. The original data is in grams but the required result is in metric tonnes. The formula is [population X fat actually available X 1000/1,000,000] . The 1,000 in the equation is because the population is in thousands and the division by 1,000,000 converts grams to Metric Tonnes.
- Iron: This is calculated by individual year. The original data is in milligrams but the result is required in Metric Tonnes. The formula is [population X iron actually available X 1000/1,000,000,000] . The 1,000 in the equation is because the population is in thousands and the division by 1,000,000,000 converts milligrams to Metric Tonnes.
- Vitamin B12: This is calculated by individual year. The original data is in micrograms but the result is required in Metric Tonnes. The formula is [population X vitamin B12 actually available X 1000/1,000,000,000,000] . The 1,000 in the equation is because the population is in thousands and the division by 1,000,000,000,000 converts micrograms to Metric Tonnes.

- All Cells: These cells are calculated by dividing the relevant cell in the "Actually Available" section by the equivalent cell in "Target Requirements" and formatting it as a percentage. For example =c16//c11 =19.1/17.6= 1.088 or 108.8%

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.

Copyright ©1998/2015 GRI Updated 5 May 2015