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.

- 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/2021 GRI Updated 8 March 2021