Guidance using FAO’s Supply Utilization Account & Food Balance Sheet Excel software

Guidance using FAO’s Supply Utilization Account & Food Balance Sheet Excel software STATISTICS v.4 Updated Oct 2011

Presentation Outline Section 1: An overview of the SUA spreadsheet software Section 2: A step-by-step guide to creating a new SUA year Section 3: An overview of the FBS spreadsheet software Section 4: A step-by-step guide to creating a FBS

Supply Utilization Account (SUA) workbook: Overview • What is it? • The SUA Excel workbook contains a series of worksheets containing SUA data over a number of years. • Most of the data for each SUA year is self-contained within the respective year’s worksheet. • The SUA spreadsheet has been designed to capture all of the data required to compile a complete SUA that can be used to produce a Food Balance Sheet. • What does it do? • Users can input data for commodities; adjust elements such as extraction rates and seeding rates, and edit country-specific nutritional factors. • The spreadsheet also allows users to carry out a series of data validation checks to minimise the risk of errors when compiling the SUA. • Notes to users: • The spreadsheet is a simplified version of the FAO’s FAOSTAT database. As a result, some values may differ from FAOSTAT figures published online.

Supply Utilization Account (SUA) workbook: Overview SUA worksheets are identical for each year, with each year’s worksheet divided into several sections and which are coloured appropriately to distinguish their different function.

Layout of SUA worksheet • Population (yellow): • used to calculate calories/fat/protein per capita. • FAOSTAT commodity list: • Origin of processed products: • Domestic Utilization: • Country specific nutritional factors: • Data Input: • Data processing (protected cells):

Layout of SUA worksheet • Population: • FAOSTAT commodity list (grey): • includes primary and derived commodities, derived as well as all food and non-food items. • Origin of processed products: • Domestic Utilization: • Country specific nutritional factors: • Data Input: • Data processing (protected cells):

Layout of SUA worksheet • Population: • FAOSTAT commodity list: • Origin of processed products (orange): • identifies derived products, their origin commodity, and % sent to processing. • Can be manually adjusted by users. • Domestic Utilization: • Country specific nutritional factors: • Data Input: • Data processing (protected cells):

Layout of SUA worksheet • Population: • FAOSTAT commodity list: • Origin of processed products: • Domestic Utilization (green): • allows users to define extraction rates, seeding rates, % sent to waste/feed/food; also controls which SUA element is assigned to residual utilization (or ‘B’ balance). • Country specific nutritional factors: • Data Input: • Data processing (protected cells):

Layout of SUA worksheet • Population: • FAOSTAT commodity list: • Origin of processed products: • Domestic Utilization: • Country nutritional factors (yellow): • users can adjust calorie/fat/protein nutritional factors, as well as compare with the world standard nutritional factors for reference. • Data Input: • Data processing (protected cells):

Layout of SUA worksheet • Population: • FAOSTAT commodity list: • Origin of processed products: • Domestic Utilization: • Country specific nutritional factors: • Data Input (grey): • the main section where users input • official, semi-official, of estimated • data for each of the SUA elements. • Data processing (protected cells):

Layout of SUA worksheet • Population: • FAOSTAT commodity list: • Origin of processed products: • Domestic Utilization: • Country specific nutritional factors: • Data Input: • Data processing (blue): • compiles all input data to calculate • the final SUA results. Data values here • are protected from editing and used • by the standardization procedure • to create the Food Balance Sheet.

Data Display Control Panel • The ‘Data Display Control Panel’ is intended to help users easily navigate around the different areas of the SUA worksheet. • Accessed by clicking on the radio buttons down the left hand side. • Follows the same worksheet structure as previously described (e.g., Data Input, Data Processing areas)

Data Validation Control Panel • The ‘Data Validation Control Panel’ runs a series of data quality checks on the SUA spreadsheet to ensure: • that data for reported commodities are complete; • that FAOSTAT symbols entered in the ‘Data Input’ section have been entered in their correct format; • that origin and processing quantities have been entered for derived commodities. • If an error is found, users are given detailed instructions on which cell, the type of issue (e.g., missing data), and the suggested solution. • Users should frequently run the data validation procedures to minimise the risk of errors in the spreadsheet.

SUA: Commodity ‘Profile’ worksheet • The ‘Profile’ worksheet generates commodity profiles for individual FAOSTAT commodities. • Provides a quick and easy way to compare and verify data entered over a number of years. • Click on the commodity name and select the commodity of interest from the drop-down box.

Guidance on inputting data in the SUA worksheet • Data entered in the Data Input section (columns ‘U’ to ‘AU’) consists of two parts: • 1.) The actual numerical data for SUA elements (e.g., production, exports, etc.). • 2.) FAOSTAT symbol to indicate the TYPE of data (e.g., official or estimated data). • Data entry validation rules • No blank or zero data values should be entered: • if no data exists for a commodity, ensure all SUA elements are set to ‘-’. • All percentage values should be entered as integers (e.g., 89% as 89, or 4% as 4). • Ensure any data entered is in the correct unit value for the SUA element : • (e.g., Mt for Area Harvested, or ‘000 animals for No. of Animals). • All official/semi-official/estimate data entered in the ‘Data Input’ section must be accompanied by the correct FAOSTAT symbol. • List of FAOSTAT symbols • (blank) = official data • - = missing data, or no data • * = data from non-official sources • F = estimated data • T = trend data • C = calculated data

STEP 1:Open the Excel spreadsheet containing the latest complete year of SUA data. STEP 2: Click the tab along the bottom of the spreadsheet containing the latest year of SUA data (e.g., worksheet ‘2007’) to select the latest SUA worksheet. STEP 3: Press the grey button titled “Generate SUA for new year” located down the left hand side of the worksheet, in column A (see below).

STEP 4: The procedure to create a new SUA year will now begin. STEP 5: A window will shortly appear asking the user to enter the year of the new SUA to be created (e.g., enter “2008”) (see below). STEP 6: After entering the new year, click the “Ok” button.

STEP 7:The next window to appear is the “File Save” window, which asks the user to enter a name for the new SUA spread sheet to be created (see below). • As a precaution, users are asked to save the workbook under a different file name rather than overwrite the existing SUA spread sheet (step A. below). • Also ensure the file is saved in ‘Excel 97-2003 Workbook format’ (*.xls) in the Save as type option box (step B. below), in order to be read by earlier versions of Excel. A. B.

STEP 8: The procedure continues, saving the new SUA spreadsheet. Users are advised to wait until the procedure is complete, which can take up to several minutes depending on the size of the file and the speed of the computer. STEP 9: Once the procedure is complete the following message appears: “New year created! Click to continue”. Press the “OK” button to continue.

STEP 9:You can now view the data for the new SUA year, as well as: • begin working on the new year SUA; updating shares and extraction rates as necessary, and overwriting all trend data with official, semi-official or estimated data as it becomes available; • enter a population figure in cell E1 to enable calculation of the calorie/fat/protein per capita figures.

Food Balance Sheet (FBS) workbook: Overview • The FBS spreadsheet is called “standardization.xls” and has two functions: • (1.) conversion of SUA food commodity quantities back to their primary commodity equivalent (e.g., flour of wheat is converted back to wheat grain); • (2.) compilation of the standardized commodities into a Food Balance Sheet. • Unlike the SUA workbook, the FBS software is fully automated. Users should not be required to edit or add data to the FBS workbook.

STEP 1: Open the current SUA spreadsheet and select the worksheet tab (along the bottom of the spreadsheet) of the year to be standardized. STEP 2: Ensure that a population figure has been entered in cell E1, required to calculate the calorie/fat/protein per capita (step A. below). STEP 3: Press the “Calculate Food Balance Sheet for current year” to run the Food Balance Sheet procedure (step B. below). A. B.

STEP 4: A window will appear titled “Load standardization.xls file”, asking you to load the Excel file called “standardization.xls” (see below). IMPORTANT: The “standardization.xls” file contains the reference template and formulae required to calculate the Food Balance Sheet. Each time you create a Food Balance Sheet, you will be asked to load the same “standardization.xls” file.

STEP 5: Eventually the following window will appear asking the user to save the new Food Balance Sheet (see below). IMPORTANT: Do not overwrite the ‘standardization.xls’ file, but save the new FBS under a different filename (e.g., ‘Thailand_FBS_2007.xls’) (step A. below), and in “Excel 97-2003 Workbook” (.xls) format (step B. below). A. B.

STEP 6: The procedure will continue for up to several minutes, until the following message appears (see below) to advise the user that the standardization procedure is now complete and the new Food Balance Sheet file has been created. STEP 7: Press “OK” to continue.

STEP 8: The new file containing the new Food Balance Sheet can now be opened and the results viewed. Go to the worksheet “Quantity_FBS” to view the FBS results (see below). Beneath the Balance Sheet, users will also find summary statistics, including self-sufficiency ratios, import, trade and stock dependency.

Further reading • For more details on using the SUA and FBS spreadsheet software, please see the following documents: • Quick start guide.doc • Contains a step-by-step instructions on how to use the main features of the SUA and FBS spreadsheet (such as create a new year, or generate a FBS). Suitable for first time users of the spreadsheet software. • SUA & FBS Guidance.doc • Contains a full description of the functionality of the SUA and FBS spreadsheet software, including trouble-shooting potential problems using the software. Suitable for more advanced users wishing to understand the software in more detail.

Guidance using FAO’s Supply Utilization Account & Food Balance Sheet Excel software