## Services on Demand

## Journal

## Article

## Indicators

- Cited by SciELO
- Access statistics

## Related links

- Cited by Google
- Similars in SciELO
- Similars in Google

## Share

## Engenharia Agrícola

##
*Print version* ISSN 0100-6916*On-line version* ISSN 1809-4430

### Eng. Agríc. vol.28 no.3 Jaboticabal July/Sept. 2008

#### https://doi.org/10.1590/S0100-69162008000300009

**SCIENTIFIC PAPERS SOIL AND WATER ENGINEERING**

**Computational modeling for irrigated agriculture planning. Part II: risk analysis ^{1}**

**Modelagem computacional para planejamento em agricultura irrigada. Parte II - Análise de risco**

**João C. F. Borges Júnior ^{I}; Paulo A. Ferreira^{II}; Camilo L. T. Andrade^{III}; Bettina Hedden-Dunkhorst^{IV}**

^{I}Agricultural Eng., D.Sc., Aux. Prof., Academic Unit of Garanhuns, UFRPE, Garanhuns - PE, Brazil, Phone: (0XX87) 3761.0969, jcborges@uag.ufrpe.br

^{II}Agricultural Eng., Ph.D., Titular Prof. , Agricultural Engineering Dept., UFV, Viçosa - MG, Brazil

^{III}Agricultural Eng., Ph.D., Researcher, Embrapa Milho e Sorgo, Sete Lagoas - MG, Brazil

^{IV}Agricultural Economist, Ph.D., Federal Agency for Nature Conservation, Bonn, Germany

**ABSTRACT**

Techniques of evaluation of risks coming from inherent uncertainties to the agricultural activity should accompany planning studies. The risk analysis should be carried out by risk simulation using techniques as the Monte Carlo method. This study was carried out to develop a computer program so-called P-RISCO for the application of risky simulations on linear programming models, to apply to a case study, as well to test the results comparatively to the @RISK program. In the risk analysis it was observed that the average of the output variable total net present value, U, was considerably lower than the maximum U value obtained from the linear programming model. It was also verified that the enterprise will be front to expressive risk of shortage of water in the month of April, what doesn't happen for the cropping pattern obtained by the minimization of the irrigation requirement in the months of April in the four years. The scenario analysis indicated that the sale price of the passion fruit crop exercises expressive influence on the financial performance of the enterprise. In the comparative analysis it was verified the equivalence of P-RISCO and @RISK programs in the execution of the risk simulation for the considered scenario.

**Keywords:** risk simulation, Monte Carlo method, irrigation requirement.

**RESUMO**

Técnicas de avaliação de riscos procedentes de incertezas inerentes à atividade agrícola devem acompanhar os estudos de planejamento. A análise de risco pode ser desempenhada por meio de simulação, utilizando técnicas como o método de Monte Carlo. Neste trabalho, teve-se o objetivo de desenvolver um programa computacional, denominado P-RISCO, para utilização de simulações de risco em modelos de programação linear, aplicar a um estudo de caso e testar os resultados comparativamente ao programa @RISK. Na análise de risco, observou-se que a média da variável de saída, valor presente líquido total (U), foi consideravelmente inferior ao valor máximo de U obtido no modelo de programação linear. Constatou-se, também, que o empreendimento estará frente a expressivo risco de escassez de água no mês de abril, o que não ocorre para o padrão de cultivo obtido com a minimização do requerimento de irrigação nos meses de abril dos quatro anos. A análise de cenário indicou que o preço de venda para a cultura do maracujá exerce expressiva influência sobre o desempenho financeiro do empreendimento. Na avaliação comparativa, verificou-se equivalência dos programas P-RISCO e @RISK na condução da simulação de risco para o cenário considerado.

**Palavras-chave:** simulação de risco, método de Monte Carlo, requerimento de irrigação.

**INTRODUCTION**

Uncertainties regarding several factors are inherent to agricultural activities. Instability regarding the financial return is consequence of variability of price of agricultural products, costs, productivity, climate factors, among others. Techniques that allow evaluate *a priori* the risks from these uncertainties, should follow the planning studies. The risk analysis can be performed by using techniques of sensitivity analysis and risk simulation. (FRIZZONE & SILVEIRA, 1996), among others.

Sensitivity analysis consists in the application of variations in input variable or parameters values and verification of effects on output variables. As the concomitant variation of all input variables and parameters of a model would result in a large number of combinations, making the analysis difficult, it is convenient to choose some input variables and parameters, varying one at a time (FRIZZONE & SILVEIRA, 1996). Usually, the procedure is applied in analysis of post-optimization of mathematical programming models, in which the solution stability is assessed, under a *ceteris paribus* condition, whereby the effect a change in a single coefficient is considered, while all the other coefficients are held constant (HAZELL & NORTON, 1986). Solver tool (Frontline Systems, Inc.), applied to obtain the solution of linear programming models implemented in Excel^{®} spreadsheets (Microsoft Corporation), also generates reports with the sensibility analysis, as present on Part I of this work.

On the other hand, risk simulation involves simultaneous variation of random input variables of a model, observing the probability distribution of output variables, using sampling techniques as Monte Carlo, Hypercube Latino or Descriptive Sampling (SALIBY, 1997; BORGES JÚNIOR et al., 2003; HARDAKER et al., 2004). Essentially, Monte Carlo method is applicable to simulate the pattern of processes depending on random factors (HARR, 1987). The correlation between input variables and parameters, stochastic-dependent, should be considered in their variation. Opposite from what happens for the sensitivity analysis; software to risk simulation performance are not available as free accessories along with electronic spreadsheets.

It is observed that techniques of sensibility analysis and risk simulation are complementary in risk analysis. On the first technique, the focus is on input variables and parameters, i.e., it is verified separately the impact of a variation in each variable or parameter upon a determined output variable. Differently, when risk simulation is applied, the focus is on the output variable, seeking to generate, based on the probability distribution of input variables and parameters and in correlations between these variables and parameters, the cumulative distribution function for the output variable.

At irrigated agriculture, the uncertainties regarding irrigation requirement are particularly important. Mostly, a strong positive correlation is verified among technical coefficients of irrigation or irrigation requirements each month. This implies that, in certain months, total irrigation requirement can reach values considerably above average. Counterpart, the volume of water available for irrigation, in this month, may be below average, because, in general, the irrigation requirement and water available for irrigation are correlated in a negative way. This stress the importance of considering the variability on technical coefficients of irrigation and evaluating the risks of water shortage occurrence for farming patterns obtained from linear programming.

In this Part II of the work, the purpose was to develop a computer program in order to apply risk simulations in linear programming models, according to what has been described on Part I; to apply this program to the model described on Part I and to test the results through comparison with the @RISK program (PALISADE CORPORATION, 2002).

**MATERIAL AND METHODS**

Linear programming model (MPL) presented on Part I of this work has constraints as monthly and annual water availability, represented, respectively, by equations(1) and (2), to be considered in the objective function for maximization of total net present value, U (R$), and minimizing total irrigation requirement during the entire analysis period or in critical periods. This second objective function is presented in equation (3), stressing that it can be structured to be applied in specific periods, especially those with water shortage risk.

where,

N_{ym }- total number of activities in year y and month m;

y - integer number representing the year;

m - integer number representing the month;

w_{jym }- monthly irrigation requirement for activity j, year y, and month m, m^{3 }ha^{-1};

j - integer number representing activity;

X_{j} - j-th level of activity or cropped area, ha;

V_{ym} - monthly constraint regarding water available for irrigation, in year y and month m, m^{3};

na - total number of years in the model (planning horizon);

N_{y }- total number of activities in year y;

Vt_{y} - annual constraint regarding water available for irrigation, in year y, m^{3}, and

Wt - total irrigation requirement during the entire period of analysis, m^{3}.

Technical irrigation coefficient or monthly irrigation requirements, represented by w_{jym} on equations (1) and (3), are subject to the expressive variability. The main sources of this variability are instabilities in the quantity and rain frequency, evapotranspiration demand and upward flow arising from water table, in which depends, among other factors, on the depth of the water table. In addition to the variability in irrigation requirements, the variability in crop yield Y (kg ha^{-1}), product price P (R$ kg^{-1}), variable costs and annual discount rate DR (%), among others are also relevant to the planning of irrigated agricultural.

Risk analysis technique, emphasized herein, is based on risk simulations, more specifically, Monte Carlo simulations (HARDAKER et al., 2004). For the processing of these simulations, it has been developed a computerized program in Delphi (Borland Corporation), named P-RISCO, in which the input variables (monthly cropping irrigation requirement, crop yield, prices of products, variable costs and annual discount rate), every ones or those defined by the user, are considered in stochastic form. With these simulations, information is obtained about the probability distribution of output variables, i.e., total net present value and monthly and annual total irrigation requirement, RMI and RAI (m^{3 }ha^{-1}), respectively. This information is applied in risk evaluations in which the cropping patterns are subject, in terms of financial viability and water shortage, in situations in which water resources for irrigation are limited.

The primary idea for the Monte Carlo simulation method is to obtain output random variables values, from input random variables values. Input random variable values are generated according to its cumulative distribution function, obtained from its probability density function (PDF).

For the monthly irrigation requirement, P-RISCO operates with PDF of standard normal distribution, truncated in extremities, on probability levels to be defined by the user (the program default is 5% and 95%). For other input variables, it is allowed to work in the present version with PDFs of the following distributions: normal, normal truncated, triangular and uniform.

It is considered that irrigation monthly requirements have correlation coefficient equal to one. This is done by obtaining, at each month, from the procedure inherent to Monte Carlo method, a value for the standard normal variable and, then, calculating the values for irrigation monthly requirements through equation (4):

where,

wr_{jym }- values obtained for w_{jym} equivalent to generated values of z, m^{3 }ha^{-1};

SD_{w} - standard deviation of w_{jym}, m^{3}ha^{-1}, and

z - standard normal random variable.

It is observed that monthly mean values of w_{jym} are used in equations (1) to (3). SD_{w} value is obtained by multiplying the ratio of the standard deviation to the mean, RDPM (coefficient of variation), which should be informed by the user, by w_{jym}. This ratio can be obtained from applications of the MCID program (BORGES JÚNIOR, 2004; FERREIRA et al., 2006).

RDPM varies each month. On the other hand, RDPM values in each month differ from RDPM in a yearly basis. Based on simulations performed with MCID, it has been verified that RDPM is, usually, less for the months in which the irrigation requirement is higher, approaching RDPM in a yearly basis. The highest interest to analyze the variability of irrigation requirement occurs in the months in which such requirement is higher, and the probability of water shortage for irrigation is also higher. Due to this aspect, RDPM should be provided in annual terms. This makes adequate, not only the irrigation analysis for the months in which the irrigation requirement is higher, but also the annual irrigation variability analysis. Thus, the RDPM value demand for each month is avoided, that would not provide expressive qualitative advantage for analysis and would make the procedure for obtaining and handling of those data harder.

As follows, a procedure implemented at P-RISCO is shown, considering stochastic the input variables: monthly irrigation requirement, crop yield, price of products, variable costs and annual discount rate.

*Begin*

Repeat

nitimes

Begin- Obtain, ∀ j, y and m, from Monte Carlo method, a group of values for w

_{jym}, Y_{j}, P_{j}, C_{j}and DR;- Calculate and store, ∀ j, y and m, values of the output variables U, RMI e RAI, i.e.:

Ur = U(wr

_{jym}, Yr_{j}, Pr_{j}, Cr_{j}, DR)RMIr

_{ym}= RMI_{my}(wr_{jym})RAIr

_{y}= RAI_{y}(wr_{jym})

*End*

Calculate distribution statistics of *ni *outputs of U, RMI and RAI

*End*

where,

ni - number of iterations;

Y_{j} - crop yield of j-th activity, kg ha^{-1};

P_{j} - present value of price of a product for the j-th activity, R$ kg^{-1};

C_{j} - present value of costs, per area unit, for the j-th activity, R$ ha^{-1};

DR - yearly discount rate, %

Yr_{j }- calculated value for Y_{j}, kg ha^{-1};

Pr_{j }- calculated value for P_{j}, R$ ha^{-1};

Csir_{j }- calculated value for Csi_{j}, R$ ha^{-1};

Ur - calculated value for U (R$) upon wr_{jym}, Yr_{j}, Pr_{j}, Cr_{j}, DR;

RMIr_{ym} - calculated value for total monthly irrigation requirement for the year y and month m, m^{3 }ha^{-1}, function of wr_{jym}, and

RAI_{y} - calculated value for total annual irrigation requirement for the year y, m^{3}ha^{-1}, function of wr_{jym}.

The number of iterations should be defined by the user, reaching, at a maximum, 10,000 in the present version of the program. As results, the distribution of output variables is obtained, also being reported the mean, standard deviation, maximum and minimum values, as well as percentiles of the cumulative distribution of each output variable.

Another procedure related to risk simulation implemented on P-RISCO is the scenario analysis that identifies input variable combinations that result in specific values of output variables. On P-RISCO, this analysis can be led to output variable total net present value, aiming to get answers for questions such as: which variables contribute to a total net present value below zero or which input variables are significant so the total net present value can be superior to the value equivalent to a specific percentile.

The scenario analysis is based on a conditional median analysis. The procedure embraces the following steps:

a) Median and standard deviation to each input variable are calculated for all iterations;

b) A subset is created, containing only the iterations in which the total net present value (output variable) achieves the target defined by the user in the scenario analysis;

c) Median is calculated for the subsets of input variables attached to the output variable subset, mentioned on item b;

d) For each input variable, the difference between the median for all iterations (step a) and the subset median (step c) is calculated and compared to the standard deviation of input variables (obtained on step a). If the absolute value of the difference in medians is greather than 1/2 standard deviation, such input variable is, then, named "significant". Otherwise, the input variable is ignored in the scenario analysis, and

e) Each significant input variable (step d) is listed in a table, containing results of scenario analysis.

P-RISCO program was applied to the linear programming model described on the Part I of the work. Annual discount rate, irrigation requirement, crop yields and prices of products were considered as stochastic variables, according to Table 1.

As output variables, the total net present value (U), yearly irrigation requirement and irrigation requirement in April months were considered. April has been chosen due to the fact that this month has been pointed out as critical in which refers to water available for irrigation, according to results obtained with the linear programming model (Part I). The simulations involved 10,000 iterations.

Scenario analysis has been conducted in order to define which input variables are determining factors for the output variable total net present value in two subintervals: total net present value inferior to the corresponding to the 25th percentile and superior to the corresponding to the 90th percentile, i.e., U < U_{25%} and U > U_{90%}.

Aiming to verify the performance of P-RISCO program in the conduction of risk analysis, results generated by this program were compared to those obtained with the @RISK program version 4.0 (PALISADE CORPORATION, 2002). Output variables, in which a comparative test has been performed, were total net present value, irrigation requirement for year 1 and irrigation requirement for April of the first year. Results from scenario analysis obtained with two programs were also compared.

**RESULTS AND DISCUSSION **

The results from simulation performed for considered output variables, obtained with P-RISCO program, are presented on Table 2. For output variable total net present value (U), it is observed that all values resulting from simulations were positive. However, the mean of R$ 215,033.00 was considerably inferior to the maximum U value obtained in the linear programming model, equal to R$ 372,723.00 (Part I of the work). This happened due to the distribution of probabilities accentually asymmetric for price and discount rate, as indicated on Table 1, observing that the price and discount rate values considered in the linear programming model are given as moda and not mean, in the respective probability distributions. The value of U corresponding to 10% of probability is R$ 166,193.13, i.e., it is verified a probability of 90% in obtaining U higher or equal to this value. Standard deviation of R$ 37,463.55 has been obtained.

It is observed in Table 2, that a mean of 132,071 m^{3 }was obtained, for the annual irrigation requirement for Year 1, and maximum value of 147,663 m^{3}, inferior to the constraint imposed in the linear programming model of 150,000 m^{3}. With standard deviation equals to 4,234 m^{3}, coefficient of variation was only of 3.2%, being low, as expected for probability distribution to annual irrigation requirement.

Also on Table 2, it is observed that, in monthly irrigation requirement for April of every year, values near constraint of the linear programming model equal to 15.000 m^{3}, on the percentile 50th were obtained. These results were expected, considering this constraint has been limiting in the linear programming model and was considered normal probability distributions for monthly irrigation requirement (w_{jym}). Thus, in case there is implemented a cropping pattern determined in the linear programming model relative to the maximum total net present value (Parte I from work), the farm would be ahead of an expressive risk of water shortage, which could compromise the financial performance.

It is possible to apply the linear programming model to minimize the irrigation requirement at different total net present values (see Figure 3 on Part I of the work), varying only levels of activities that affect the irrigation requirement in April, in similar procedure as the one described by BORGES JÚNIOR et al. (2003).

On Figure 1 it is shown cumulative probability distributions for the requirement of irrigation in April, of year 1, for two cropping patterns: April-1A, related to the maximum total net present value of R$ 372,732.00, and April-1B, related to the cropping pattern obtained with minimization of the irrigation requirement in April, on the four years considered, fixing the total net present value in R$ 370,000.00 (0.7% inferior to maximum value). It is also stressed that, for the second cropping pattern, in spite of an undermost reduction in the total net present value, there was an expressive decrease of 19% of total irrigation requirement in April, in four years. In the second case, it is observed a non-indication of risk that the irrigation requirement in April of year 1 exceeds the constraint of 15,000 m^{3}. Similar results are obtained for April in the years 2; 3 and 4.

In Table 3, the results from scenario analysis were presented, obtained through P-RISCO program. It is observed that, for the interval U < U_{25%} and interval U > U_{90%}, critical input variables were the selling price for passion fruit crop, presented in the optimum solution of linear programming model, in the first and second cropping, in years 2 and 4, respectively. This result indicates selling price for passion fruit has and expressive influence over the financial performance of the enterprise.

The comparison of simulation results obtained with P-RISCO and @RISK programs is presented in Tables 4 to 6.

In Table 4, it is observed that, with the exception of extreme values (minimum and maximum), the absolute values of percentage differences obtained for the output variable total net present value were inferior to 0.6%. By dealing with the simulation results using the Monte Carlo method, the results are not expected to be equal. Most differences in the extremes are also expected, since the sampling process in the Monte Carlo method is completely random (opposite to techniques as the Latin Hypercube and the Descriptive Sampling) and eventual discrepancies in the extreme may occur inclusive in results obtained with the same program in two different simulations.

In Table 5, it is verified that the highest percentage contrast, in module, among the results generated by P-RISCO and @RISK, for irrigation requirement in year 1, occurred for standard deviation, being equal to -0,706%. It is verified that, in all percentiles, the values obtained with P-RISCO were superior to @RISK results, in which, probably, was due to differences in the truncation procedures applied in the standard normal truncated distribution of irrigation requirement input variable (Table 1). However, in percentage, the highest contrast was equal to 0.142%.

For the irrigation requirement in April of year 1, according to Table 6, with the exception of the result obtained by the standard deviation, absolute values of percentage difference among results obtained with P-RISCO and @RISK were inferior to 0.6%. The percentage difference for standard deviation was 1.457%. In percentiles, the difference ranged from -0.421 to 0.388%.

The scenario analysis results obtained with @RISK are presented in Table 7. Comparing these results with those obtained with P-RISCO, presented in Table 3, it is verified that the same input variables were indicated as significant, in both U subintervals considered (U < U_{25%} and U > U_{90%}). The parameters probability equivalent of input variable (Percentile, %), input variable value and the ratio of the median difference to the standard deviation (MD/SD), obtained with the two programs, were also near. Percentiles differences varied from -1.48 to 0.93%, while, the values ranged from -1.89 to 0.17%. These differences were expected due to the fact that were results obtained from simulations based on random sampling by Monte Carlo method. It should be stressed that, in P-RISCO results, MD/SD parameter is given in absolute value. Obviously, the values are negative for the interval U < U_{25%}, according to what was indicated by @RISK.

The small differences presented in Tables 4 to 6 and the results related to scenario analysis, presented in Tables 3 and 7, indicate the equivalence between two programs to perform the risk analysis based on simulations from the linear programming model considered.

**CONCLUSIONS **

In the risk analysis, it was observed that all generated values, resulting from simulations, were positive for the total net present value (U), indicating the non-expectation of a financial failure risk. However, the mean R$ 215,033.00 was considerably inferior to the maximum U value of R$ 372,723.00 obtained in the linear programming model. It has been verified that the enterprise would be ahead the expressive water shortage risk in April, which does not occur for the cropping pattern obtained with the minimization of irrigation requirement in April of four years.

Scenario analysis indicated that the selling price for passion fruit farming has expressive influence over the development financial performance.

Based on the comparison of cumulative probability distribution of output variables total net present value, irrigation requirement for year 1 and irrigation requirement for April of the first year, as well as in the comparison of scenario analysis results, an equivalence of P-RISCO and @RISK programs were verified in the conduction of risk simulation for the scenario considered.

**ACKNOWLEDGEMNTS**

To CNPq, Capes and DAAD, for the financial support. To the Center for Development Research - ZEF, Germany, to Embrapa Milho e Sorgo and to UFV, for the support to works development.

**REFERENCES**

BORGES JÚNIOR, J.C.F.; HEDDEN-DUNKHORST, B.; FERREIRA, P.A. Decision support based on bio-economic simulations for irrigated agriculture. In: DEUTSCHER TROPENTAG – CONFERENCE ON INTERNATIONAL AGRICULTURAL RESEARCH FOR DEVELOPMENT. Göttingen, 2003. Disponível em: http://www.tropentag.de/2003/abstracts/full/224.pdf. Acesso em: 23 jan. 2007. [ Links ]

BORGES JÚNIOR, J.C.F. *Modelo computacional para tomada de decisão em agricultura irrigada*. 2004. 226 f. Tese (Doutorado em Recursos Hídricos e Ambientais) - Universidade Federal de Viçosa, Viçosa, 2004. [ Links ]

FERREIRA, P.A.; BORGES JÚNIOR, J.C.F.; HEDDEN-DUNKHORST, B.; ANDRADE, C.L.T. Modelo computacional para suporte à decisão em áreas irrigadas. Parte II: Testes e aplicação. *Revista Brasileira de Engenharia Agrícola e Ambiental*, Campina Grande, v.10, n.4, p.783-91, 2006. [ Links ]

FRIZZONE, J.A.; SILVEIRA, S.F.R.* Análise de viabilidade econômica de projetos hidroagrícolas*. Brasília: Associação Brasileira de Educação Agrícola Superior, 1996. 89 p. [ Links ]

HARDAKER, J.B.; HUIRNE, R.B.M.; ANDERSON, J.R.; LIEN, G. *Coping with risk in agriculture*. 2^{nd} ed. London: CABI Publishing, 2004. 352 p. [ Links ]

HARR, M.E. *Reliability based design in civil engineering*. New York: Mcgraw-Hill, 1987. 290 p. [ Links ]

HAZELL, P.B.R.; NORTON, R.D. *Mathematical programming for economic analysis in agriculture*. New York: Macmillan Publishing Company, 1986. 400 p. [ Links ]

PALISADE CORPORATION. *@Risk advanced risk analysis for spreadsheets*. Newfield: Palisade Corporation, 2002. 499 p. [ Links ]

SALIBY, E. Descriptive sampling: an improvement over Latin Hypercube sampling. In: Winter Simulation Conference, 1997, Atlanta. *Proceedings*... p.230-3. [ Links ]

Received by Editorial Board on May, 14, 2007

Approved by Editorial Board on September, 2, 2008

1 Abstracted from first author's doctorate thesis. Financial support: CNPq, Capes, DAAD.