4.0T1 Training Materials: Video "FABLE Calculator model structure" (original) (raw)
Video: "FABLE Calculator model structure"
In the following link you may find the video on "FABLE Calculator model structure":
Welcome to this video on the FABLE Calculator model structure. Our objective is to explore how the FABLE Calculator works as a model, including which are the most common Excel functions used, how to read formulas, and how to recognize and fix errors.
Remembering from previous videos, the FABLE Calculator follows various calculation steps. In step 1, the model computes the targeted human consumption, as this will be used to compute the targeted livestock production and the targeted crop production. The numbering of the calculation worksheets in the FABLE Calculator reflects this sequence of calculation steps. Inside each of the calculation worksheets, there are also different tables with columns, which themselves have one formula per column, as we will see in the next videos for each calculation step.
For this reason, it is important to be familiar with the most commonly used Excel functions, and how they combine to form different formulas. In the Fable calculator website that you can access with this URL address, and in Appendix 2 you can find a description of these Excel functions, as well as some examples of how they are used. Most of the operations carried out in the model relate to recalling values from other tables, or other worksheets in the Excel file, and to combine these values in simple arithmetic operations.
Every formula of the model utilizes an Excel function, the name of a table, the name of a column and some criteria to match the specific value to be recalled. Let us look at a formula, for an example!! At first, and if you have little previous knowledge of Excel functions, the formula could look very intimidating! :). Do not worry! At a closer look, and breaking down the components, we see that the operation is actually quite simple. What this formula is actually doing is just bringing a value from another place of the model, namely a table called “DietScenDef” and from a column named "Shifter", value which must match the criteria that is listed afterwards, for the same row:
DietScenDef[DietScen],[@[DIET_SCEN]] = … + this condition in the same row, both in the “DietScenDef” and in the @current tables, condition which in this case is a specific scenario for diets…
DietScenDef[PROD_GROUP],[@prodgroup] = + this condition in the same row, in both tables, which in this case is a specific group of agricultural goods…
DietScenDef[Year],[@year] = + this condition in the same row from both tables, which in this case is a specific year…
For this reason, another useful tool that Excel allows is to find a Table, you can use the Excel tool for finding, as is now shown…. Let's look for the Table "DietScenDef". I copy its name, look for the function "Find and Select", I paste the table name that I want to find in the entire workbook, and I ask Excel to find all formulas with this table name. After some seconds, we get a lot of formulas, but most importantly, we get a link to the Table DietScenDef. I click this link and Excel takes me directly to where this table is found.
Reading formulas in this way and understanding what each table and column does, is essential to understanding the FABLE Calculator. We will review the tables for each of the main calculations steps in the next videos, to help you in this task. Sooner than later, you will be changing formulas to adapt the model to your own country! Do you dare to take the chance? :)
To do this and become an advanced FABLE Calculator user, the next milestone is to learn where to look for possible errors or infeasibilities in the calculator. In this slide we enlist some of the most common errors encountered by the current users of the calculator:
• Table is named incorrectly • Duplicated rows when adding data • Columns inserted in the wrong place, which affects an arithmetic operation done by adjacent columns • Values are inserted by mistake when one clicks inadvertently in an Excel cell • Or a formula is not copied in all rows of a column
To identify some of these possible errors, a QA tool and a Reference Calculator have been created and are continually updated, for all members of the FABLE Consortium.
An advanced FABLE Calculator user should also monitor more complex issues, which would require longer troubleshooting sessions. Some of these issues can be identified with several built-in checks in the calculator:
Here we enlist some of the most common built-in checks, that will be reviewed with more detail in the next quizzes and exercises. See you there!
• Table 2.D: chk_herd • Table 2.J: Chk_animproducts • Table 3.D: ChkCrops • Total land must always be constant, in Tables ResultsLand and calc_land_cor • Target values must always be equal to feasible values • Values calculated for 2000-2015 must always be close to historical values • Negative numbers that are not valid in the current column (i.e. production of crops can never be negative, but land use could)
For more information on the FABLE Calculator:
• https://www.abstract-landscapes.com/fable-calculator
• https://github.com/FABLE-Github/Fable-Calculator-Documentation-2020/wiki
For more information on the FABLE Consortium:
• https://www.foodandlandusecoalition.org/fable/
The 2020 FABLE Report:
• http://pure.iiasa.ac.at/id/eprint/16896/
----------------------------------- Content ------------------------------------
0:00 - Welcome
0:30 - Review: First three calculation steps -> Worksheets
1:05 - Familiarizing oneself with commonly used Excel functions
1:53 – Example of a FABLE Calculator formula
2:51 - How to find a Table in the FABLE Calculator?
3:46 – List of common errors
4:29 – Useful checks built into the FABLE Calculator
4:55 – Links to FABLE Websites (to click on them, also included in the video description)