Purdue Crop Guide

Spreadsheet Instructions

 

Dan Settlage and Craig Dobbins

Department of Agricultural Economics

Purdue University

 

January 2001

 


 

Purdue Crop Guide Spreadsheet

 

 

            The Purdue Crop Guide Spreadsheet was composed to simplify answering "what if" questions regarding price, yield, and cost assumptions for corn, soybean, and wheat budgets for the state of Indiana. The spreadsheet runs in Microsoft Excel and contains seven different sheets. Individual sheets include: an input sheet of commodity prices, input prices, and other budget data; budget sheets for continuous and rotational corn, single and double crop soybeans, second-year soybeans, and wheat; and a budget summary.

 

Description of Spreadsheet

 

            Moving between sheets is accomplished by clicking on the various sheet tabs located at the bottom of the spreadsheet window. All blue cells contain information that the user is required to provide. These cells are unlocked, allowing the user to enter the appropriate data. All other cells are locked to prevent inadvertent changes being made to the spreadsheet formulas. If it becomes necessary to modify a locked cell, the locked cells may be unlocked on a sheet by sheet basis by selecting Tools –> Protection –> Unprotect Sheet. This unlocks all cells and allows embedded formulas to be changed or overwritten. Be aware that a change in one cell may lead to a change in many other cells that the are linked to the original cell. Hence any modification of the spreadsheet should be undertaken with caution and only after fully understanding all of the cell linkages. It is recommended that a backup of the original spreadsheet be kept in an unmodified form to prevent unrecoverable errors.

 

Inputs

 

            The ‘User Provided Inputs’ contains a list of commodity prices and input costs. It also contains information about other input requirements, government program payments, and annual overhead costs. This data is used throughout the remaining sheets in budget calculations. The inputs needed for the spreadsheet are illustrated in Figure 1.

 

Budgets

 

            The next six sheets ‘Continuous Corn’, ‘Rotation Corn', ‘Rotation Soybeans’, 'Second-Year Beans', ‘Wheat’, and ‘Double-Crop Soybeans' correspond to the budgets that are generated by the spreadsheet. Each sheet generates three budgets corresponding to low (Miami), medium (Crosby), and high (Brookston) productivity soil types. In all sheets, the blue cells are the user entries. The last sheet ‘Summary’ provides a summary of all generated budgets, and contains no user required entries. This sheet can be used to facilitate comparisons across different enterprises, crops, or soil types.

 

            The budget sheets for each crop combine the quantities of each input with its cost to estimate the expenses for each crop. This section also estimates revenue for each crop. If the crop is one that historically received price supports or receives a payment as part of the Oil Seed Payment Program, the budget will estimate the government payment income. While the income from the government AMTA payment is attributed to a particular crop, only the Oil Seed Payment Program requires that a particular crop be grown to obtain these payments.

 

            Overhead charges are included in each budget. These charges are allocated equally to each crop acre. This means that the total annual cost for each overhead item is divided by the total crop acres and is then included in the per acre budget. Figure 2 presents an example of the budget form used for each crop.

 

Summary

 

            The returns and direct costs are summarized across all crops and land types in the summary sheet. This sheet provides a summary of total revenues and direct costs of production. The information provided in this sheet can be used to help select those crops that provide the largest contribution to the payment of overhead or profits. Both the contribution margin without government payments and with government payments is reported. Since the size of the government AMTA payment does not depend on the crops produced, the contribution margin excluding government payments is the return that should be used in selecting the most profitable crops to produce. Figure 3 presents a sample of the summary sheet.

 

Technical Details

 

            The spreadsheet contains several important formulas in generating the budgets. These technical calculations are described in this section.

 

GOVERNMENT PAYMENTS

 

Government AMTA payments for corn and wheat are calculated as:

Gov. payments($/ac) = price support yield (bu./ac.) x payment rate($/bu.) x base acres as a % of total acres(%) x 0.85.

 

The base acres as a percent of total acres for corn and wheat are calculated as:

(corn base acres/ total corn acres)*100 or (wheat base acres/ total wheat acres)*100

 

Government Oil Seed Payment Program

Gov. payments ($/ac) = yield (bu/ac) x payment rate of $0.14 per bushel.

 


FERTILIZER REQUIREMENTS: ALL FERTILIZER REQUIREMENTS ASSUME A MAINTENANCE PROGRAM FOR FERTILITY AND ARE BASED ON THE TRI-STATE FERTILIZER RECOMMENDATIONS.

 

Nitrogen rates for continuous corn are calculated as:

Nitrogen (lbs/ac) = 110 + 1.36*(corn yield(bu./ac.) - 100) - lbs. of nitrogen provided with starter.

 

Nitrogen rates for rotational corn are calculated as:

Nitrogen (lbs/ac) = 80 + 1.36*(corn yield (bu./ac.) - 100) - lbs. of nitrogen provided with the starter.

 

Phosphate rates for continuous corn are calculated as:

Phosphate (lbs/ac) = 0.37*corn yield (bu./ac.) - lbs. of phosphate provided with the starter.

 

Potash rates for continuous and rotation corn are calculated as:

Potash (lbs/ac) = 20 + 0.27*corn yield (bu./ac.) - lbs. of potash provided with the starter.

 

Lime rates for continuous and rotation corn are calculated as:

Lime (lbs/ac) = 3*nitrogen applied (lbs).

 

Phosphate rates for rotation and double-crop soybeans are calculated as:

Phosphate (lbs/ac) = 0.80*soybean yield (bu./ac.).

 

Potash rates for rotation and double-crop soybeans are calculated as:

Potash (lbs/ac) = 1.40*soybean yield (bu./ac.) + 20.

 

Nitrogen rates for wheat are calculated as:

Nitrogen (lbs/ac) = 40 + 1.75*(wheat yield (bu./ac.) - 50).

 

Phosphate rates for wheat are calculated as:

Phosphate (lbs/ac) = 0.63*wheat yield (bu./ac.).

 

Potash rates for wheat are calculated as:

Potash (lbs/ac) = 20 + 0.37*wheat yield (bu./ac.).

 

Lime rates for wheat are calculated as:

Lime (lbs/ac) = 3*nitrogen applied(lbs).

 

OTHER COST ESTIMATES

 

Drying fuel quantity(gal/ac) for corn are calculated as:

Dryer fuel (gal/ac) = Dryer fuel (gal/bu)*corn yield (bu./ac.).

 

Capital investment for corn and soybeans expenses is calculated as:

Principal ($/ac) = (fertilizer + lime + seed + chemical costs)*(9/12) + (0.5*machinery fuel + 0.5*machinery repairs + miscellaneous)*(6/12).

 

Capital investment for wheat expenses is calculated as:

Principal ($/ac) = (fertilizer + lime + seed)*(9/12) + (0.5*machinery fuel + 0.5*machinery repairs + miscellaneous)*(6/12).


 

 

Home

Farmland References

Farmland Values

Financial Performance

General Managers

Farmland Values

 Farm & Family Connections

More Resources

  Contacts