Estimating Economic Risk Using Monte Carlo Enterprise Budgets

Edward B. Rayburn, College of Agriculture, Forestry and Consumer Sciences, West Virginia University Extension Service, Morgantown, WV 26506

Abstract

Farmers often admit that they are gamblers. By using Monte Carlo enterprise budgets they can learn the odds of obtaining a positive economic return from their farming enterprises. Deterministic spread sheet budgets can be converted to Monte Carlo or stochastic budgets using commercial add-ins such as @Risk. However, relatively simple budgets can be converted into Monte Carlo budgets by using the statistical functions found in Excel or Open Office. This management guide shows how to convert a simple deterministic hay budget into a Monte Carlo budget using the statistical functions in Excel.

Farmers, Gambling, and Risk Estimation

Most farmers admit that they are gamblers since they recognize that farming is a risky business. Farmers often use electronic spread sheets to develop budgets to evaluate the profitability of an enterprise based on expected prices and productivity. These budgets are most often used with a few values based on current expectations. The purpose of a Monte Carlo budget is to help the farmer calculate the odds of winning. Based on the calculated odds, the farmer will know the probability of the enterprise being profitable or exceeding a defined minimum net income.

Monte Carlo is a major gambling city in Europe, and its name is used in reference to computer models that enable the user to calculate odds or probabilities of an outcome. Another name for these computer programs is stochastic models.

Risk is the exposure to unfavorable consequences due to uncertainty or imperfect knowledge (5). There are several types of risk associated with agricultural businesses. These include production, market, personal, and institutional risk. Production risk occurs due to variability in weather resulting in variability in crop yield and quality. Market risk occurs due to changes in price of inputs or value of outputs, especially during times of volatile markets. Personal risk occurs due to the chance of injury or illness. Institutional risk occurs due to changes in government regulations that affect agricultural production or markets. These risks also interact. When drought over a large area reduces hay yield the price of hay within the region will go up. When the government subsidizes the production of ethanol made from corn the price of corn can go up. If the price of corn goes up it can affect the price of high quality hay. Uncertainty in all of these categories and the resulting risk are fundamental to agriculture.

Complex whole-farm systems are best evaluated using sophisticated Excel add-ins such as @Risk (4). However, less complex enterprise budgets can be converted to stochastic budgets using basic statistical functions in Excel and knowledge of the distribution of input values for selected variables in the budget to give a probability distribution of the economic outcome for the enterprise.

A spread sheet budget is an economic model of a farm enterprise. Conventional enterprise budgets are deterministic in that the values entered in the budget determine the calculated net income and production breakeven price. They are static in that once the numbers are entered there is no variability. Monte Carlo budgets have one or more stochastic variables entered as a mean (average) and its standard deviation (SD). These budgets are set to run many times. On each run of the budget, different values for each stochastic variable are selected at random based on the variables’ mean and SD. For complex spread sheet models run in Excel a risk assessment add-in such as @Risk can be used to develop the stochastic variables and to summarize the output (7,8). For agricultural enterprise budgets, which are often relatively simple, these more sophisticated add-ins are not required (9,10).

How to Make a Monte Carlo or Stochastic Budget

A deterministic budget can be converted to a Monte Carlo budget when the user has a measure of the variability of one or more of the budgeted inputs. For example, we will take a deterministic no-till grass-legume hay budget and convert it to a Monte Carlo budget (Figs. 1, 2, and 3). The budget is converted to a Monte Carlo budget by using two Excel statistical functions and one or more data tables. The statistical functions are the random number generator RAND() and cumulative probability function such as the normal inverse function NORMINV().

Fig. 1. Production, legume stand life, and establishment costs have a major impact on the economics of hay production.

Fig. 2. Annual maintenance and harvesting costs are determined by forage yield, machinery rates, fertilizer prices, and number of cuts per year.

Fig. 3. A deterministic budget calculates the net return and breakeven price of production for the data entered. The Monte Carlo budget, using a 2-way data table, provides this for the mean situation (50% probability) and for the 16% and 84% probabilities as set up here. This band includes 68% of the potential occurrences.

The RAND() function calculates a random number between 0 and 1 each time the spread sheet is recalculated. The value generated by RAND() is used in the NORMINV() function to "draw" a number from the normal distribution defined by the mean yield and its SD. On each run of the budget a new "annual" hay yield is drawn at random using the inputted mean yield and SD.

In the northeastern United States the climate is such that the annual yield of cool-season forage crops follows a normal distribution. Therefore the normal inverse function NORMINV() is the appropriate function to use in this budget. For annual crops a beta or lognormal distribution may be more descriptive of yield and should be substituted when appropriate.

The NORMINV() function requires the input of a probability, a mean, and the mean’s SD in the form:

NORMINV(probability, mean, standard deviation)

The RAND() function is used to generate the probability for each run. For example, when mean yield is entered in cell C4, the yield SD is entered in cell C7, the random yield is calculated using the formula (Fig. 1):

=NORMINV(RAND(),C4,C7)

By running the budget many times a large number of random yields are calculated representing the statistical distribution defined by the mean and SD. Each run also calculates the annual net return and breakeven price. This large set of net returns and breakeven prices are then summarized to calculate the odds of achieving a given net return or breakeven price.

Finding the SD

We can measure the SD of hay yield across years by using yields measured on the farm over several years or by using regional forage variety and on-farm research trials. For our budget we are using a set of on-farm and experiment station forage management and variety trials conducted with orchardgrass, tall fescue, alfalfa, and mix pastures in the Northeast. Each trial was conducted for two to four years. The mean yield and SD were calculated for each trial. The SD was converted to the trial’s coefficient of variation (CV), expressed here as a fraction.

CV = SD / Mean Yield

This was done since the variance of the SD across trials was proportional to the mean yield. By using the CV the variance of the CV was approximately constant across mean yields. The trial CV was then regressed against the trial’s mean yield resulting in the following regression (Fig. 4) (N = 86, R² = 0.27).

CV = 0.40 – 0.036 Mean Yield

Residual analysis of the regression data showed that there was no difference due to treatments for the relation between CV and mean yield, meaning that this estimate of the CV could be used across species and grazing treatments without a systematic error.

Fig. 4. The relation between the 2 to 4-year mean forage yield and the mean’s coefficient of variation (standard deviation divided by mean yield); each data point represents a hay or pasture site harvested for 2 to 4 years.

In the budget the user enters the expected mean yield and the regression is used to calculate the CV of the yield. The SD is then calculated as (Fig. 1, cell C7):

SD = CV × Mean Yield

In general 10 or more observations are needed to get a good estimate of the SD. This regression approach is one method of averaging the SD of short term studies over many site years based on the principle of the central limit theorem (1), allowing regional research to be used as an estimate for farms within the region. Where a local SD is know for a forage system that SD should be used.

Making Multiple Runs in a Spread Sheet

To make the spread sheet run more than one time set up a two-variable or two-way data table. The data table uses dummy variables that do not affect the budget’s calculations but accumulates these values in the data table. For a data table that accumulates 30 breakeven prices, enter an equation in a cell that references the calculated breakeven price multiplied by 1 then multiplied by 1 again. Where D34 is the cell containing the calculated breakeven price and cells A47 and A48 each contain the value 1 the equation in cell C49 would be (Fig. 3):

=D34*A47*A48

To create the two way data table three 1’s are entered in the cells to the right of cell C49 and ten 1’s are entered in the cells below C49 (Fig. 3). Highlight the range from C49 across the 3 cells to the right and down the 10 cells below C49 including all of the 1’s entered. After highlighting go to the "Data" menu then the "Table" option. In the space for "Row input cell" enter cell A47 and in the space for "Column input cell" enter cell A48, or the equivalent for the spread sheet being used. New versions of Excel or other spread sheet software such as Open Office Calc (2) may have menu structures different than that used here so the user may have to use the "Help" function to learn how to set up a two-variable data table.

Calculating the Odds

The Risk Analysis section of the budget (Fig. 2, cells D37 to J47) summarizes the "Breakeven Price" and "Net Over Total Cost" data tables calculating their mean, SD, and number of runs using the following functions (ranges entered in the functions apply to the "Breakeven Price" table):

= AVERAGE(D50:F59)

= STDEV(D50:F59)

= COUNT(D50:F59)

To estimate the probability of a "Breakeven Price" use the mean and the SD. The mean is an estimate of the 50% probability (Prob) level when the population of values has a normal distribution (Fig. 2, cell E44 formula: = E39). Thus half the time the value is greater than the mean and half the time it is less than the mean. Two out of three times the value drawn will be between 1-SD above (cell E43 formula: =E39+E40) and 1-SD below the mean (cell E45 formula: =E39-E40). One out of six times it will be greater that 1-SD above the mean and one out of six times it will be less than 1-SD below the mean. The 84% and the 16% Probability values are approximately the 1-SD above and 1-SD below levels respectively. More precisely they are 0.994 of a SD above and below the mean. The user of this no-till hay budget now has a better estimate of the odds of making a given net return or the breakeven price of hay.

Interpreting the Results

The breakeven price shows the return needed to cover input costs. This value is especially useful for crops fed to livestock. It is independent of market value but valuable in determining a minimum price needed when selling hay or renting pasture. When breakeven price is too high, the manager needs to evaluate options that can be used to reduce cost without a major negative impact on yield or quality. In a forage-livestock system this may mean adding complimentary enterprises that buffer forage production (3) or ones that provide marketing diversity (6). What is too high differs from farm to farm since each manager has their own level of risk aversion based on experience, tradition, personality, and financial status (6).

The example budget only accounts for yield effect on breakeven price, ignoring the relation of price and yield. Where this is know it could be added to the budget to quantify market effect on risk. Otherwise market risk is an uncertainty. As risk assessment is refined other risks such as catastrophic yield or quality loss such as damage by unexpected thunderstorms (that are not normally distributed) could be added.

Data Needs for Stochastic Budgets

Researchers can assist by gathering and reporting data that is useful in risk analysis. When evaluating crop yield or quality, researchers should establish the statistical distribution that the measurement follows, if not a normal distribution. They should also identify if the SD is constant or proportional to site or crop yield potential. Research needs to be conducted over multiple sites within a region, with means and SD reported at the regional and local site scale. The regional scale affects large farms and marketing associations while the local scale has a greater effect on small and moderate size farms. In regions with major climatic differences at small scales (mountains and near lakes or oceans) comparison between locations may have an impact on treatment means and SD. Multiple years of data are required to get small confidence intervals about the mean and accurate estimates of the SD. In forage budgets similar to the one used here, doubling the size of the SD effectively doubles the range between the 16% and 84% probabilities. However, this may not always be the case since budgets are not necessarily linear. A stochastic assessment of sample size effect on SD: six, twelve, and twenty-four site-years provide estimates of the SD within about eleven, eight, and six percent accuracy. Given these diminishing returns and limited agricultural research dollars it is important that consideration be given to these issues prior to investing in a research protocol.

Summing Things Up

Monte Carlo or stochastic enterprise budgets enable farmers and extension advisors to better understand the odds of making the positive net income necessary to stay in business. Today many farmers use computers and develop simple to complex spread sheets including enterprise budgets. Understanding the basic use of RAND(), NORMINV(), or other appropriate statistical distribution functions, and data tables will enable them to convert deterministic budgets into stochastic budgets. For farmers not interested in developing these tools for themselves university faculty at the state and county level can develop tools to evaluate the probability of economic success.

To support farmers in risk evaluation researchers need to report treatment SD as well as treatment means. Also, applied research needs to be conducted sufficiently over time and space so that a reliable estimate can be made of both the treatment means and SD. Since farmers put their farms and families at economic risk when following university or industry recommendation they need to be given the tools for evaluating the economic risks to their enterprises.

Acknowledgment

Funding for the research contained in this management guide was provided by the Appalachian Pasture Finished Beef Project, a multi-institutional project funded through the USDA/ARS Appalachian Farming Systems Research Laboratory in conjunction with West Virginia University, Virginia Tech, and Clemson University.

Literature Cited

1. Anonymous. 2008. The Central Limit theorem: How to tame wild populations. Online. Amazing Applications of Probability and Statistics. Center for Res. in Wireless Communications, Clemson Univ., Clemson, SC.

2. Anonymous. 2008. Why OpenOffice.Org. Online. Sun Microsystems Inc., Santa Clara, CA.

3. Clapham, W. M., Fedders, J. M., Abaye, A. O., and Rayburn, E. B. 2008. Forage pasture production, risk analysis, and the buffering capacity of triticale. Agron. J. 100:128-135.

4. Evans, J. R., Sperow, M., D’Souza, G. E., and Rayburn, E. B. 2007. Stochastic simulation of pasture-raised beef production systems and implications for the Appalachian cow-calf sector. J. Sustainable Agri. 30:27-51.

5. Hardaker, J. B., Huirne, R. B. M., and Anderson, J. R. 1997. Coping with risk in agriculture. CAB Intl., New York, NY.

6. Lozier, J., Rayburn, E. B., and Shaw, J. 2006. The decision to finish cattle on pasture: An ethnographic approach. J. Sustain. Agr. 28:5-23.

7. Palisade Corp. 2003. @RISK Risk Analysis and Simulation Add-in for Microsoft Excel. Palisade Corp., Ithaca, NY.

8. Rayburn, E. B. 2003. Production risk of cool-season grasses in the Northeast United States. Proceeding American Forage and Grassland Council. Lafayette, LA. April 27-30, 2003.

9. Rayburn, E. B. 2008. Monte Carlo Enterprise Budgets. Proc. American Forage and Grassland Council. Louisville, KY. Jan 27-30, 2008.

10. Winston, W. L. 2004. Microsoft Excel Data Analysis and Business Modeling. Microsoft Press, Redmond, WA.