LUCTOR: Land Use Crop
Technical Coefficient
Generator; version 4.0
H. Hengsdijk
05/16/99 11:39
Table of Contents
page
1Revised LUCTOR version 4.0
1.1Structure of LUCTOR version 4.0
1.2Changed concepts and conventions
1.3Operation of LUCTOR 4.0
1.4New calculation rules and data
Appendix I: Files used in LUCTOR 4.0 and their content
Appendix II: Identification codes of land use systems
Appendix III Data parameters in LUCTOR files
1
1Revised LUCTOR version 4.0
Since the documented version 2.0 of LUCTOR (Hengsdijk et al., 1998), some important changes were made concerning its structure and automation procedures. Also new information was incorporated and calculation procedures adjusted. This resulted in LUCTOR version 4.0 in which there is a better distinction among data, formulas and informative comments making LUCTOR user-friendlier. New cropping systems have been added and the old EXCEL macro language is completely replaced by macros based on Visual Basic which is better adapted to newer Windows versions and is more language-proof; Version 2.0 using the old EXCEL macro language only worked with Windows software with an English configuration. This problem is overcome using Visual Basic macros.
LUCTOR version 2.0 supported the generation of TCs for two LP-models, the model developed by REPOSA (see Chapter 6 and 7 of book) and the model of UNA/DLV (see Chapter 8 of book). Version 4.0 only supports generation of technical coefficients for the REPOSA-model (REALM). However, LUCTOR-users may define other technical coefficients based on many of the generated characteristics of cropping systems in LUCTOR in the cell range that is used for the REALM model (see end of section 1.3)
This document describes the major changes made compared to version 2.0 and is therefore an update of Chapter 3 of the LUCTOR-document describing version 2.0 (Hengsdijk et al., 1998) to which no further reference in the text is made. In section 1.4 some remarks are made concerning calculation procedures and new data.
1.1Structure of LUCTOR version 4.0
The type of files used in LUCTOR version 4.0 are limited to (i) files with data, (ii) files with calculation rules and (iii) output files. Some file names have been changed, LUST.XLS has been renamed ANN_CROP.XLS, CROPS.XLS has been renamed PER_CROP.XLS, PRIX.XLS has been renamed PRICE.XLS and BIOCIDE.XLS has been renamed PROTECT.XLS. The XLM-files are expired and not any longer required. In stead, Visual Basic macros and user forms are integrated with the file LIST.XLS. The macros can be made visible by pressing [Alt+F11] after having opened LIST.XLS. See Appendix I for a complete overview of the files in LUCTOR 4.0. These file names must NOT be changed since LUCTOR is file name oriented!!!
For the output files ANN_IO.XLS, PER_IO.XLS, PIN_IO.XLS and FOR_IO.XLS new files may be created: While the relevant calculation file (ANNUAL.XLS for ANN_IO.XLS, etc.) and LIST.XLS are open, a new EXCEL-workbook should be opened and saved with the relevant output file name. Subsequently, the macro ‘name_head’ must be run which is available in LIST.XLS (under Tools, Macro, Macros). This macro will generate the required range names in the new output file and the column headings of the technical coefficients. However, the most save way is to copy the old output file to a new file name while continue working with the old one.
Formulas in the calculation files (ANNUAL.XLS, PINA.XLS, PERENIAL.XLS and FORESTRY.XLS) are regrouped in separate worksheets which names can be seen in the tabs at the bottom of the screen:
crop protection:calculations for the type and amount of crop protection agents used.
labour:labour and implement calculations.
main: definition of the cropping systems.
nutrient: nutrient calculations.
production: calculation of biophysical production and distribution over crop parts.
TCs:containing the Technical Coefficients (TCs) for the REALM model.
The generation of technical coefficients for pineapple, timber plantation and perennial systems requires an additional worksheet named ‘annuity’ in which technical coefficients are discounted. The three calculation files in version 2.0 with annuity calculations (PINNUI.XLS, PERNUI.XLS and FORNUI.XLS) are therefore no longer used. Annuity calculations are only done for technical coefficients relating to yield, costs and labor requirements.
Also information of some data files is regrouped to improve the transparency of data used in LUCTOR. The files ANN_CROP.XLS and PER_CROP.XLS each contain four worksheets:
crop: data on labor requirements and physical crop production parameters.
crop residues:parameters on biomass distribution over crop parts required for calculation of crop residue strategies.
efficiency:nutrient efficiency parameters, which in version 2.0 were stored in BASIC.XLS.
mechanisation: Definition of implements for weed and pest management as a function of respectively herbicide, biocide and mechanisation level and crop type. This feature is new compared to version 2.0 (see also section 1.4).
nutrients:minimum and maximum nutrient content of crop parts (see also section 1.4).
The file SOIL.XLS has two worksheets:
soil data: includes soil parameters, soil suitability for crops and harvest parameters for timber plantation systems.
wood production:includes the annual biomass production of timber plantations (melina and teak). This feature is new compared to version 2.0 (see section 1.4).
Within worksheets data is grouped which is indicated with a number in the first column making referencing easier. Cells containing data have a green color indicating that the user can change these cell values in general without problems. Cells with a gray color contain formulas and will in general only appear in worksheets of the four calculation files. General comments and explanation in LUCTOR are indicated with an asterix in the first column. Compared to version 2.0 the number of comments is considerably increased and more detailed. Cellnotes contain data source references and are indicated with a red dot in the upper right corner of cells. Most data source references refer to publications of REPOSA as presented in Chapter 10 of the CD-ROM. Also the file lmanual.doc (Hengsdijk et al., 1998) on the CD-ROM contains an extensive list of references used in LUCTOR.
Users of LUCTOR who want to make structural changes have to disentangle the relationships incorporated in the calculation files. It looks perhaps difficult but in reality it is quite simple; one should only know something about the functions and programming possibilities that EXCEL offers. LUCTOR is a model with no more than 8 variables (for annuals and perennials), 9 variables for pineapple and 3 variables for timber plantation systems. These are the definition criteria that guide which data in the calculation files are retrieved from the data files and they determine jointly the outcome of the calculations (the final technical coefficients). With the INDIRECT-function of EXCEL values (range names) are assigned to these variables in the calculation files that coordinate which data are retrieved. Understandig this function will solve most difficulties. Data retrieval is completely based on names of parameters that refer to cell ranges. The defined parameter names in a file can be viewed by pressing [F5], and can be traced by clicking one of the parameter names or typing the parameter name in the available input box. In all files range names are written in italic and can in general be found just above or next to the range to which they refer. Range names are used to retrieve data from the data files. In general data is stored in a table form. To retrieve data from a table the intersection method is applied as described in the manual of version 2.0. In Appendix III all the parameter names are alphabetically shown for the most important data files. Three types of parameter can be distinguished based on their cell range reference:
1Data stored in a single cell indicated with ‘PC’ in Appendix III. These data can be changed very easily because they are used in the calculation files as a single parameter such as some of the input costs as defined in PRICE.XLS.
2.Data in a range of cells, indicated with ‘PR’ in Appendix III. These types of data can be used as an array in calculation files. To retrieve these data one has to refer to a similar number of cells in the calculation files. Examples of this type of data are the defined nutrient contents in ANN_CROP.XLS and PER_CROP.XLS (worksheet ‘nutrients’).
3Data in tables are the most frequented form of data storage. In Appendix III these types of data are indicated with ‘TV’ (parameter with cell range vertical) or with ‘TH’ (parameter with cell range horizontal). To retrieve these data, the intersection method should be used which allows to retrieve any data you want form a table. The data in worksheet ‘crop’ of ANN_CROP.XLS and PER_CROP.XLS are an example of this type of data storage.
1.2Changed concepts and conventions
Crop codes have been changed according to the crop codes used in the REALM model. See Appendix II for a complete overview of the identification codes of cropping systems.
The start of the growing season is no longer a definition criteria but declared as a parameter value in ANN_CROP.XLS for annuals and PER_CROP.XLS for perennials (start_oper).
The length of crop cycles of perennial systems is also no longer a definition criteria, but a parameter value in PER_CROP.XLS (cycle_long).
Two new cropping systems have been added: rice to the annuals, and natural forest to the timber plantations.
1.3Operation of LUCTOR 4.0
Many message boxes have been added to LUCTOR 4.0 with instructions how to activate certain events. In the following some of the events are described in more detail.
First, files of LUCTOR have to be copied from the CD-ROM to any prefered directory on your hard disk. Subsequently, LUCTOR can be started by opening LIST.XLS. This file contains the macros that guide the opening and closing of files which was carried out by TCG.XLM in LUCTOR version 2.0. After opening of LIST.XLS the directory for LUCTOR and its output files can be changed in the same way as in version 2.0 (with pressing [Ctrl+d]). When LIST.XLS is opened for the first time LUCTOR will recognize whether the directory settings in LIST.XLS correspond with the actual directory in which LIST.XLS and other LUCTOR files are stored. When they differ, LUCTOR automatically pops up with a dialogue box to set the directory of LUCTOR and the directory of the output files for the REALM model. After having defined both directories the new settings will be saved.
To open other LUCTOR files press [Ctrl+t], which activates a dialogue box with user-options. After selection of the preferred option press the button [Just do it!]. The files will be opened. Although the layout of the dialogue has been changed, the procedure is similar as the one in version 2.0. The use of Visual Basic macros allowed to improve the layout of the dialogue boxes in LUCTOR 4.0. This is even more obvious when technical coefficients are automatically generated. This procedure can be started after opening of the required files by pressing [Ctrl+s]. Buttons on the dialogue boxes for definition criteria allow to go back to the previous definition criteria or to exit before all the definition criteria are selected. While using LUCTOR no other EXCEL files may be open.
During the automatic calculation of a batch of cropping systems the user is informed about the progress with dialogue boxes. Also, when the procedure is finished a dialogue box will appear indicating that LUCTOR has finished. Click the OK-button to proceed. In case no feasible definition criteria have been selected LUCTOR will return with a message to inform the user. Enter a new set of selection criteria.
A feasibility check is performed which means that for manual generation of technical coefficients a warning is given (just below the identification code of the generated land use system in sheet ‘TCs’) in case an infeasible combination is selected, and that for the automatic generation procedure these type of combinations are simply not generated. For manual generation of technical coefficients, checks for infeasible combinations of definition criteria can be found in the worksheet ‘TCs’. Be aware that the automatic generation procedure uses feasibility checks that are integrated in the Visual Basic macros. In the current setting these checks are similar to those in the worksheet ‘TCs’. However, changing checks in the worksheet ‘TCs’ will NOT affect the feasibility checks in macros, and vice versa. See Table 1.1 which feasibility checks are performed.
Table 1.1Criteria for which feasibility checks are performed.
Feasibility checks1. Soil is suitable to growth a crop as defined in SOIL.XLS
2. Target yield level of 10% for alternative activities not feasible
3. Actual activities only with 100% production level feasible
4. Low biocide option combined with banana, plantain or palmheart not feasible
5. Option of harvesting crop residues not feasible
6. Alternative activities for natural forest option infeasible
In ANNUAL.XLS (worksheet TCs) two additional data checks are incorporated. One concerns the data on crop protection agents and the other the labor balance. The former checks whether labor requirements are defined in case crop protection agents are defined. Improper definition of labor requirements in ANN_CROP.XLS results in a FALSE-statement. The labor balance check is of importance when the user changes the date of the start of the growing season (start_oper in ANN_CROP.XLS) and the end of the growth cycles of the crop exceeds two years. In that case LUCTOR may calculate labor requirements incorrectly and a FALSE-statement is shown.
The customizing procedure of combining output files for the REALM model in ASCI-files has been made more robust and flexible. First, one should press [Ctrl+t] to select the option ‘combine all XLS-output files’ which closses files not required for the customizing procedure and opens all output files. When the output files have been opened, one should press [Ctrl+c] to start the customizing procedure. In version 4.0 it is no longer required that output files contain data, i.e. it is e.g. possible to customize technical coefficients of annual cropping systems only, the output files of perennials, pineapple and timber plantation systems can remain empty. The procedure in version 2.0 also gave an error when output files contained technical coefficients of just one cropping system. This problem is solved in version 4.0. Be aware that also in version 4.0 output files will be deleted in the output directory after generation of new output files for the REALM model!
In case users want to generate other technical coefficients of cropping systems and still want to use the automatic generation procedure, the most convenient way is to replace the cell contents in the range techcoeff in the calculation file (worksheet ‘TCs’) with the required characteristics of cropping systems.
1.4New calculation rules and data
This Appendix only discusses the most important changes in calculation procedures and data. Most cells with data contain notes with references, and also the most important calculation rules are explained in LUCTOR.
One major improvement compared to version 2.0 is that the user is able to define the implements used for weed and pest management. In ANN_CROP.XLS and PER_CROP.XLS, for annual and perennial cropping systems respectively, a worksheet named ‘mechanisation’ is available in which the type of implement can be defined as a function of herbicide, biocide and mechanisation level and crop type. For weed management e.g., a boomspray, knapsack or knife can be chosen. For biocide applications a boomspray, knapsack or airplane (only perennials) can be chosen. In Table 1.2 an example is shown of such a table. See also worksheet ‘mechanisation’.
Table 1.2Example of mechanisation table for maize grain in ANN_CROP.XLS for weed and pest management
mechanisation option for maize grain (zm)high mechanisation / low mechanisation
Herbicide level / TCG-code: / zm_hm / zm_lm
High herbicide level / hh / knapsack / knapsack
Low herbicide level / lh / knife / knife
Biocide level
High biocide level / hb / knapsack / knapsack
Low biocide level / lb / knapsack / knapsack
Grey cells in the table are green in LUCTOR indicating data values that may be changed by the user.
These implements are used for calculating TCs of both actual and alternative activities. In the worksheet ‘crop’ the standard task times (=labour requirement per operation) and the number of operations that has to be carried out with each implement is defined. Notice that the standard task time of an implement does not effect TC-calculations as long as the implement is not defined in the worksheet ‘mechanisation’. The most appropriate way to change data is to start with the definition of the implements and subsequently adjust the number of operations and standard task times in the worksheet ‘crop’. For implements that are not defined in the worksheet ‘mechanisation’ no standard task times are required but otherwise, they do not affect the calculations.