Job Summary

The objective of the consultancy is the review of Excel sheets and structure for the evaluation of agroforestry system and the estimation of carbon content and the creation of a set of macros to automate CarbonSAF and PlantSAF excel tools scoring and result generation to facilitate its completion by non-specialists.

Job Description

ICRAF currently has two modules:

CarbonSAF

For the estimation of the mitigation potential of agroforestry practices, implemented as an Excel sheet and an Access database for basic statistical information, as part of a tool to support the diagnosis and design of agroforestry systems.

PlantSAF

For the diagnostic of farm level and plot level indicators to assess farmers socio-economic and biophysical circumstances and inform the process of co-design, implemented as an Excel sheet database for basic scoring and ranking.

To make both modules available to as many people as possible, the consultant will have to automatize the tools by creating a set of macros in Excel that will allow for:

• Standardization and normalization the entry of information - For this purpose, data validations and the creation of lists of possible options to be selected by means of combos should be created. The data that are not selection lists must have data type validations (number, text, date...) and ranges including normalization, aggregation of data into combined indicators.
• Selection of the calculation model. Depending on the information available to the user, it should be possible to select different calculation models. The system must allow to select each of them from already defined formulas, parameters, and coefficients or to create new coefficients, parameters, and formulas.
• Result presentation tabs. Tabs will be created for the presentation of results. In these tabs the results of the module will be shown by means of labels, indicators, tables, and graphs. Macros must also be provided to fill them in automatically or on demand.

The consultant is requested to develop the following side products:

• Administrator and user manuals for both tools (as separate objects). Note that the administrator should be able to modify the macros and validations performed, and that the
• manuals must include the instruction for these processes.
• A short training module for ICRAF technicians on the use of the tools. This training will be the opportunity to test the initial version of the tools and collect feedback for their
• improvement before the final submission of the tools and of the manuals.
• Given the complexity of the tasks, the consultant will have to hold continuous meetings with ICRAF technicians to fine-tune each of the input parameters, calculation elements and result indicators.

Requirements

• Master’s in systems engineering or similar.
• Excel VBA programming experience.
• Proficiency with Visual Basic for Application (VBA)
• Comprehensive knowledge of MS Excel application – Pivot Tables, Formulas.
• Understanding of OOP language.
• Keen analytical, logic, and troubleshooting skills.
• Experience with data analytics, BI reporting.
• Good sense of responsibility and quality communication skills.
• Ideally, knowledge in project management, SQL, and interface inheritance.
• Demonstrable experience applied to the analysis of rural socio-economic data analysis.