2014 Farm Bill Analyzer: Data and Methods
Note: This article describes the “ISU Farm Bill Analyzer”, an educational tool for analyzing the decision points for corn and soybean producers in the 2014 Farm Bill. This AgDM Decision Tool is available to download for free.
The ISU Farm Bill Analyzer (Excel file) is an educational tool to help Iowa corn and soybean farmers make informed decisions regarding:
1) the reallocation of base acres and
2) the ELECTION of a commodity program for the life of the Farm Bill.
Using historical information and expectations about prices and yields, the ISU Farm Bill Analyzer (Excel file) calculates expected payments at the single-farm level for corn and soybean producers under all (41) possible combinations of programs. A detailed discussion of the new programs introduced by the 2014 Farm Bill is available in AgDM File A1-32, New Safety Net: PLC, ARC-CO, ARC-IC.
The ISU Farm Bill Analyzer (Excel file) reports two sets of expected payments: one calculated across all (500) simulations, and the other one calculated across the bottom 10% of total crop revenues in each year. By comparing the two sets of expected payments, the user can visualize the importance of commodity programs as farm risk management tools.
To analyze an individual’s situation across the multiple combination of programs in the 2014 Farm Bill, the ISU Farm Bill Analyzer (Excel file) requests the user to provide just 42 data points: 18 data points for each crop and 6 data points for the whole farm. From those data points, 9 can be chosen from drop-down menus and 33 must be entered manually.
The workbook is organized into six worksheets or “Tabs”. The first worksheet is a “Read Me” page with instructions that walk through the steps.
Step 1 - Farm Data
Worksheet “Step 1 - Farm Data” requires the user to manually enter 33 data points into the spreadsheet and choose from six drop-down menus. The cells that require information are highlighted in yellow in columns D and E. Instructions for each line of information requested are available in column F.
Projected county yields for 2014 (row 63 in “Step 1 - Farm Data”) are calculated as the product of county yields in 2013 times the ratio of state yields in 2014 to state yields in 2013 (equal to 112.12% for both corn and soybeans). Projected county yields for 2015-2018 are calculated as the average county yields over 2009-2013 multiplied by the Trend Adjustment factor from crop insurance times the difference between the year for which the county yield is being calculated and 2011. If a projected county yield for a particular year is lower than 70% of the County Transitional Yield (T-Yield), then the low county yield is replaced by 70% T-Yield in that year.
County yields from 2009-2013 are sourced from USDA FSA, except for years when FSA uses a 70% plug to calculate ARC-CO county yields, in which case those county yields are replaced by yields sourced from USDA NASS. The logic behind this substitution is to use actual county yields in the calculation of projected county yields.
Projected farm yields for 2014-2018 (rows 72-76 in “Step 1 - Farm Data”) are calculated as the product of the projected county yield times the average ratio over 2008-2013 of farm yields to county yields. If no farm yield data is available, then farm yields are assumed equal to county yields and the average ratio takes the value of 1.
County yields can be manually modified. If county yields are modified, then projected farm yields will adjust to the new projected county yields. Farm yields can also be manually modified. But doing so will not affect county yields.
Step 2 - Expected Payments
Worksheet “Step 2 - Expected Payments” requires the user to choose a set of Marketing Year Average (MYA) price projections and an expected level of volatility in futures prices with respect to MYA prices for each crop from drop-down lists. The expected level of volatility in futures prices is used to calculate annual Projected and Harvest prices.
Three available sets of MYA price projections are listed in the drop-down menu: USDA price projections (as of October 2014), FAPRI price projections (as of August 2014), and futures prices (which update automatically to the last settlement price published on the CME website every time the Excel file is opened).
The projected MYA price from futures prices for soybeans is calculated as the simple average of future soybean prices of the September, November, January, March, May, July, and August contracts in a marketing year; while the projected MYA price from futures prices for corn is calculated as the simple average of future corn prices of the September, December, March, May, and July contracts in a marketing year. For 2018, since no future contract is available yet, the MYA crop prices are calculated applying the average growth rate of FAPRI and USDA prices between 2017 and 2018 to the 2017 MYA prices calculated with futures prices.
Three available sets of expected volatility scenarios are available: average, high and low. Each level of volatility is associated with a pair of ratios: 1) a ratio of projected prices to MYA prices and 2) a ratio of harvest prices to MYA prices. The ratios associated with expected average volatility are calculated as the average ratios of projected and harvest prices to MYA prices over 2000-2013. To calculate the ratios associated with high and low volatility, the 14 years of data are split in half according to the ranking of the volatility measure of futures prices stemming from insurance policies. For corn, volatility in 2006-2012 was higher than in 2000-2005 and 2013 (0.278 versus 0.201, on average). For soybeans, volatility in 2004-2006, 2008-2009, and 2011 was higher than in the other years in the sample (0.247 versus 0.173, on average).
The resulting MYA, Projected, and Harvest prices are reported in Box 3, rows 19-23 for corn and 28-32 for soybeans. All projected prices shaded in green can be manually modified. If MYA prices are manually modified, then Projected and Harvest prices will automatically adjust to new levels. However, if Projected or Harvest prices are manually adjusted, then MYA prices will not adjust to new levels.
Using Monte Carlo simulation techniques on the mean projected values of MYA price, planting price, harvest price, county yield and farm yield for 2014 through 2018, expected payments are calculated for all possible combinations of programs across crops. In particular, 500 random draws from a multivariate normal distribution are used to create a probability distribution around the annual mean projected values of the five simulated variables. Then, program payments and net indemnities are calculated for each simulated set of prices and yields. Simulated prices and yields in one year enter the calculation of program revenue benchmarks and affect the Actual Production History1 (APH) used in the calculation of COMBO insurance indemnities for the following years. Consequently, the evaluation of program payments is based on 500 different paths of simulated prices and yields through time.
Box 4 reports the expected crop revenue by year for 2014-2018, and their net present value in 2014 dollars.
The program with highest expected payments is highlighted in Box 5, along with its net present value and the percentage of whole farm revenue it represents. All expected payments for all possible combinations of programs are reported in Box 6, along with their associated net present values and their ranking among programs.
Expected Payments Chart
Worksheet “Expected Payments Chart” helps visualize the differences between expected payments across programs. The chart summarizes the information listed in Box 6 (Step 2 - Expected Payments).
Step 3 - Low Revenue
Worksheet “Step 3 - Low Revenue” reports the expected crop revenues and payments associated with the bottom 10% of total (corn and soybean) crop revenues in each year. This information is intended to highlight the role of different programs in protecting farmers against low yields and/or low prices that result in very low revenues. Box 1 reports the expected crop revenues in this low revenue scenario. Box 2 highlights the program with highest expected payment in this low revenue scenario, along with its net present value and the percentage of whole farm revenue it represents. All expected payments for all possible combinations of programs are reported in Box 3, along with their associated net present values and their ranking among programs.
Low Revenue Scenario
Worksheet “Low Revenue Scenario” helps visualize the differences between expected payments across programs. The chart summarizes the information listed in Box 3 (Step 3 - Low Revenue).
Step 4 - Revise Reallocation
Worksheet “Step 4 - Revise Reallocation” is intended to quantify the impact of choosing to retain the allocation of base acreage as reported in the FSA letter sent to farmers in August 2014 versus choosing to reallocate base acreage according to recent production history. After filling out the yellow shaded cells, the user will be able to visually compare payments for a chosen program under both possible acreage allocations in the chart “Expected Payments”. Detailed information about base acreage reallocation is available in AgDM File A1-35, Base Acreage Reallocation and Payment Yield Update.
1 Once the APH yield is established for 2014, then all other APH yields build upon that one. Consequently, all years after 2014 have at least 4 observations in the calculation of APH yields.
Alejandro Plastina, extension economist, 515-294-6160, firstname.lastname@example.org