April 28, 2011

The Microsoft® Excel® spreadsheets used to calculate the Nebraska Crop Budgets are now available so users can customize a budget to fit their operation. (Also see 2011 Nebraska Crop Budgets (EC872) or CW budget story.)

Individuals downloading these files should be advised that they originally were created for extension personnel creating crop budgets for a publication so user friendliness was not a consideration in their design.

The following information is to help those wanting to modify these budgets. The headings in these instructions correspond to tabs in the spreadsheets.

Crop Budgets

The various inputs for each crop budget spreadsheet come from different places in the file.

• Yellow cells – Enter your information from the keyboard.
• Blue cells -- Click on an item from a drop-down menu.
• Noncolored cells -- Contain information or formulas. Changing these cells may cause the spreadsheet to lose functionality. These cells are locked and the spreadsheets are protected to avoid having users change these cells inadvertently. The protect feature is not password protected so users may change all cells if they desire.

Because everything in these spreadsheets may be changed, users are strongly advised to verify the validity of each budget prior to relying on its results as there are no guarantees, expressed or otherwise, that the calculations used in these budgets are correct.

Both the Field Operations and the Materials & Services sections of each budget contain 20 rows for data entry. The Hide feature has been used on some of these rows to make a given budget more attractive. When rows are hidden, their row numbers on the left side of the spreadsheet cannot be seen. To unhide these rows, left click on the row number above the missing rows and move the cursor to the row number below while holding down on the mouse’s left button. Right click the mouse when both the row above and the row below are highlighted. This will bring up a menu with UnHide as one option. Clicking the UnHide option will cause the hidden rows to be visible.

Columns B and D in the Field Operations section have blue backgrounds, indicating data in these cells are entered via a drop-down menu. The choices available for Column B come from the Operations tab. Values calculated in the Operations tab are multiplied by the value in Column C, (Times or Quantity) to fill in Columns E through J.

The drop-down menu in Column D (Unit) allows the user to enter either acre, ai (acre-inch), or the unit of production entered in Cell B3. Entries in this column are not used in any calculations.

Column B in the Materials & Services section also uses drop-down menus to enter inputs. The choices for these cells come from the Materials tab. The Materials tab is explained in greater detail below. Column F (Operations Index) is used to show what field operation is associated with that input and is not used in any calculations. Column G (Percent Acres Applied) is multiplied times Column H (Rate) which is then multiplied times the price calculated in the Material & Services tab to yield a value for Column K (Total). If a material is applied once to each acre, such as seed, the value in Column G should be 100. If a material is applied more than once the value will be greater than 100. If a material is applied to only part of the acres or not every year, the value will be less than 100.

Cell C60 is the final entry in the individual budget spreadsheet. It uses a drop-down menu to select the type of land used. Land value is based on type of land and is entered in Cell F60. Land types and values are entered in the General Variables tab in Cell Range E3:F9. Users who want to change land prices should do so in this range.

General Variables

The General Variables tab is where users can change Wage Rates and Diesel Price. These are the main things most users will want to change but any cell with a yellow background can be changed in this tab. Changes made on this tab will be reflected in every crop budget.

Materials

Users wanting to modify the price of a material or service should do so in the Materials tab. The names of materials or services are entered in Column B (Material). These are the names that appear in the drop-down menus in the budget spreadsheets described above.
The entry in the Category column is carried forward to Column C in a budget’s Materials & Services section.

Purchase price is converted to applied price in the materials tab. Column D (Purchase Price) is the cost for an Applied Unit of that material. For example, if you pay $300 per ton for fertilizer, 300 should be entered in Column D and ton should be entered in Column E. If that fertilizer is applied by the pound (i.e. 80 pounds per acre) then pound should be entered in Column F (Applied Unit). Column G is a conversion factor that is calculated by dividing applied units by purchased units. In our example there are 2,000 pounds (applied units) in a ton (purchased units) so the calculation is 2000/1 or 2000. The Materials tab then divides the $300 purchase price by 2000 (conversion factor) and the result is $0.15 per pound which is the value of Column H (Applied Price). Individual crop budget spreadsheets use this applied price to calculate their Column K (Total).

Operations and Power Units

Entries made in both the Operations and the Power Units tabs are used to calculate labor, fuel, repairs, and ownership expenses. These estimates are calculated using formulas from ASABE Standards, an annual publication of the American Society of Agricultural and Biological Engineers. Describing how these calculations work is beyond the scope of this website. Inquiries about details in these tabs should be addressed to rwilson6@unl.edu.

Roger Wilson
Farm Managerment / Enterprise Budget Analyst